整理的90个公式,学完你就能碾压99%的同事

      全套总共90个案例,超级全面。今天先分享15个过100个。
整理的90个公式,学完你就能碾压99%的同事

1.统计总销售量(SUM函数)

这是某集团公司2014年各个分公司每月销售量,如何统计总销售量?

=SUM(C2:C11)
整理的90个公式,学完你就能碾压99%的同事

原理分析

本例中的求和区域是C2:C11,使用SUM函数对销售量进行求和。SUM函数语法如下:

SUM(数字1,[数字2],……)

参数中数字可以是 666 之类的数字,C2 之类的单元格引用或 C2:C11 之类的单元格范围,参数1为必须参数,参数2为可选参数。

2.

统计每个公司的销售量(SUMIF函数)

现在想知道每个公司的销售量,如何统计呢?

=SUMIF(B:B,E2,C:C)
整理的90个公式,学完你就能碾压99%的同事

原理分析

本例中的条件区域为B列,条件为E2,求和区域为C列,借助SUMIF函数进行单条件求和,求出每个公司的销售量。

SUMIF函数语法如下:

SUMIF(条件区域,条件,[求和区域])

假如现在要对C列大于500的销售量进行求和,正常用下面的公式:

=SUMIF(C:C,">500",C:C)

这时会发现一个问题,如果条件区域跟求和区域是一样的,在这种情况下,求和区域可以省略,可将公式改成:

=SUMIF(C:C,">500")

3.

统计销售量在

500至700的和(SUMIFS函数)

前面说过借助SUMIF函数可以求销售量大于500的和,现在是500至700这个区间,该如何做呢?

=SUMIFS(C:C,C:C,">=500",C:C,"<=700")
整理的90个公式,学完你就能碾压99%的同事

原理分析

本例中借助SUMIFS函数可以实现多条件求和的特点,设置条件1为">=500"和条件2为"<=700",对C列的销售量进行求和。SUMIFS函数语法如下:

SUMIFS(求和区域,条件区域1,条件1,[条件区域2],[条件2],……)

SUMIFS跟SUMIF的参数顺序有点不同,SUMIFS求和区域在第一参数,而SUMIF函数求和区域在第三参数,这个需要特别注意一 下。

4.

统计总销售金额(

SUMPRODUCT函数)

根据销售明细表如何统计总销售金额?

=SUMPRODUCT(D2:D11,E2:E11)
整理的90个公式,学完你就能碾压99%的同事

原理分析

借助SUMPRODUCT函数对两个区域先乘积,后求和。SUMPRODUCT函数语法:

SUMPRODUCT(区域1, 区域2, ……)

5.

统计上班天数(COUNTA函数)

这是某公司考勤记录表,有上班就打勾(√),没上班就空着,如何统计每个人的上班天数?

=COUNTA(B3:C33)/2
整理的90个公式,学完你就能碾压99%的同事

原理分析

借助COUNTA统计非空单元格个数,因为每天上班包含上下午,一个√算半天,所以要求出总数再除以2。

COUNTA函数语法如下:

COUNTA(值1,[值2],……)

COUNTA函数语法跟SUM函数一样。

6.

考勤统计(

COUNTIF函数)

上一个例子的考勤表就只有上班跟空白两种情况,而实际上可以细分为正常上班、迟到、事假等等,现在如何一次统计每个人正常、迟到、事假的次数?

=COUNTIF($B2:$I2,J$1)
整理的90个公式,学完你就能碾压99%的同事

原理分析

COUNTIF函数就是条件计数函数,跟SUMIF函数有点类似,就是少了一个求和区域而已。

COUNTIF函数语法:

COUNTIF(条件区域,条件)

7.

统计性别为男、学历为大专人数(COUNTIFS 函数)

下图为员工信息表,如何统计性别为男、学历为大专人数?

=COUNTIFS(C2:C11,"男",E2:E11,"大专")
整理的90个公式,学完你就能碾压99%的同事

原理分析

COUNTIFS函数可以实现多条件计数,函数语法如下:

COUNTIFS(条件区域1,条件1, 条件区域2,条件2,……)

8.

猴子分桃(IF函数)

宋国有一个养猴的老人,喜欢猴子, 把它们成群养着,他可以理解猴子的意思,猴子也可以理解老人的心意。养猴的老人宁可减少他与家人的食物也要满足猴子的需求。不久,他家里的粮食缺乏了,他将限定猴子的食物的数量。但又怕猴子不顺从自己,就先欺骗猴子说:“给你们桃子,早上三个,晚上四个,够吗?”猴子们都站了起来并且十分恼怒。他又说:“给你们桃子,早上四个,晚上三个,够了吧?”猴子都非常高兴然后一个个都趴在地上。从这里就可以看到2个判断:

1.如果是早上就给3个桃子,否则(是晚上)就给4个

2.如果是早上就给4个桃子,否则(是晚上)就给3个

将数据输入Excel中,我们就可以借助IF 函数替我们判断。

在单元格C2输入如下公式, 并向下复制,即可得到每个时间段应该分多少桃子。

=IF(B2="早上",3,4)=IF(B2="早上",4,3)
整理的90个公式,学完你就能碾压99%的同事

原理分析

IF函数的作用就是进行逻辑判断,语法如下:

IF(条件,条件符合时返回的值,条件不符合时返回的值)

如第1个公式,条件就是判断是否等于早上,满足了就显示3,否则(等于晚上)就显示4。因为只是早晚需要分桃问题而已,不是早上,必然是晚上。

9.

游必备条件(

AND函数)

还没参加工作的时候,有的是时间,总想到处去玩,去云南、北京、西安…… 但这时因为没钱,只能是想想而已。参加工作以后,钱就有了,可是天天上班,没有时间出去玩。也就是说要出去外面玩这种机会还是比较少,需要同时满足2个条件:1、有钱;2、有时间。

现在只有同时满足有钱有时间就出去外面玩,否则就不玩。

在单元格C2输入公式,并向下复制。

=IF(AND(A2="有钱",B2="有时间"),"出去玩","不玩")
整理的90个公式,学完你就能碾压99%的同事

原理分析

AND函数的作用就是同时满足的意思,语法如下:

AND(条件1,条件2,……)

只有当所有条件同时都满足的情况下才显示TRUE,只要其中一个不满足都显示FALSE。

10.

让女朋友开心的条件(OR函数)

现实生活中一定会发现经常遇到女朋友没有理由的生气,或者感觉女朋友无理取闹,那么在这个时候你应该怎么办呢?是晾着不管还是想办法哄她开心呢。这个时候如果你选择不管,那一定会进一步激化矛盾,所以你最好的办法就是尽快找到合适的方法哄她开心。

下面的方法都可以让女朋友开心:吃大餐、送礼物、安慰她、讲趣事、去散心、陪伴……

哄女朋友开心的方法有很多,但只要你用了1种或者多种女朋友都会开心,而如果你什么都不做,女友肯定会很不开心。

在G2输入公式,并向下复制。

=IF(OR(A2="吃大餐",B2="送礼物",C2="安慰她",D2="讲趣事",E2="去散心",F2="陪伴"),"开心","不开心")
整理的90个公式,学完你就能碾压99%的同事

原理分析

OR函数就是或者的意思,语法如下:

OR(条件1,条件2,……)

只要其中有一个条件满足就显示TRUE,全部不满足就显示FALSE。

11.

根据姓名查询快递单号(VLOOKUP函数)

现在我们买东西都是在网上购买,经常都会收到各种快递,通过快递单号查询物件什么时候到。现在有一份快递清单,因为姓名的顺序乱了,如何根据姓名查找韵达快递单号?

在单元格E2输入公式,并向下复制公式。

=VLOOKUP(D2,A:B,2,0)&""
整理的90个公式,学完你就能碾压99%的同事

原理分析

本例借助VLOOKUP函数查找D2在区域A:B两列中的第2列的对应值,也就是韵达单号。函数语法如下:

VLOOKUP(查找值,查找区域,返回查找区域的第几列,精确还是模糊查找)

参数4:如果为0就是精确查找,1就是模糊查找。

后面&"",就是将单号变成文本格式,防止显示科学计数法。

12.

据客户号逆向查询客户姓名(

LOOKUP函数)

现在有一份客户姓名跟客户号清单,想根据客户号查找客户姓名。

=LOOKUP(1,0/($B$2:$B$34=D2),$A$2:$A$34)
整理的90个公式,学完你就能碾压99%的同事

原理分析

LOOKUP函数经典查找模式通用公式:

LOOKUP(1,0/((条件1)*(条件2)*……*(条件n)),返回区域)

有了这个通用公式,即使是多条件查询对应值也可以轻易做到,这就比VLOOKUP函数好用多了。

13.

查询生肖的排位(

MATCH函数)

这里有一份12生肖对应表,要根据生肖,查询自己的排位。

=MATCH(C2,A2:A13,0)
整理的90个公式,学完你就能碾压99%的同事

原理分析

MATCH函数的语法如下:

MATCH(查找值,查找的区域,精确或者模糊查找)

第三参数0的时候为精确查找,1为模糊查找。

14.

路程导航(OFFSET函数)

我们去陌生的地方就经常会问朋友说我现在在某某地方,如何去某某地方。或者用百度地图查询。比如我现在起点是中国银行,终点是潮州市政府。

这时先往后走630m到潮州大道跟枫春路这个十字路口,再往右边走200m就到潮州市政府。

整理的90个公式,学完你就能碾压99%的同事

现在卢子要去潇子家,该怎么走呢?

整理的90个公式,学完你就能碾压99%的同事

有2种方案:

1.往右边走3站到姐姐家,再往下边走5站到潇子家。

2.往下边走5站到班长家,再往右边走3站到潇子家。只往右边走3站,不向下走。

不管是哪种方案走,都必须向下5站,向右3站。

=OFFSET(B2,5,3) 

原理分析

OFFSET函数的语法如下:

OFFSET(起点,向下多少行,向右多少列)

向下(右)用正数表示,向上(左)用负数表示。虽然两点之间的距离最短,但OFFSET并不支持对角线走,因为对角线没路可走。

比如现在潇子要去卢子家,就是向上5行,向左3行。

=OFFSET(E7,-5,-3)

15.

间接引用多个表的合计(

INDIRECT函数)

1月到4月机台的产量,4个表格格式一模一样,如何引用所有表格的合计呢?

整理的90个公式,学完你就能碾压99%的同事

使用公式:

=INDIRECT(A2&"!B11")
整理的90个公式,学完你就能碾压99%的同事

 

原原理分析

INDIRECT函数的语法如下:

INDIRECT(单元格引用)

公式中的&就是将字符连接起来,变成了"1月!B11"也就是变成工作表名!单元格的格式。

这个&就相当于月老的红绳,可以将相爱的两个人牵在一起,如将小红跟小明撮合在一起。

版权声明:天骄 发表于 2021-12-27 18:56:48。
转载请注明:整理的90个公式,学完你就能碾压99%的同事 | 艺财驿

暂无评论

暂无评论...