整理了40个Excel函数公式(最新版),职场必备,建议收藏!条件统计

今天给大家整理一期常用的Excel函数公式!

包括求和类、查找类、判断类、统计类、拆分类!

一、求和公式

(包括单条件求和、多条件求和、按日期区间求和、合并单元格求和、按月份求和、动态求和)

1、单条件求和

计算出西瓜的总销量数据,在F2单元格写入公式=SUMIF(B:B,E2,C:C),回车确定。

2、多条件求和

在G2单元格中写入公式=SUMIFS(C:C,B:B,F2,A:A,E2)

3、按照日期区间求和

在H2单元格中写入公式=SUMIFS(C:C,A:A,E2,A:A,F2,B:B,G2)

4、利用通配符模糊求和

我们需要查找地区是“*西部”的对应利润,实际地区中有“西部”、“中西部”。

在B10单元格中写入公式=SUMIF(A2:A7,A10,B2:B7)

5、合并单元格的求和

6、按月份求和

输入公式=SUM((MONTH(A2:A19)=D2)*B2:B19)

7、根据月份自动汇总1-N月份之和

选取1月,显示1月份的值

选取5月,汇总1-5月之和

选取12月,汇总1-12月之和

输入公式=SUM(OFFSET($B2,,,,MATCH($N$1,$B$1:$M$1,0)))

二、查找类公式

(屏蔽错误值查找、反向查找、多条件查找、借用COLUMN、MATCH实现多个查找,多表查询)

1、VLOOKUP+IFERROR——消除查找错误值的影响

如果不想看到查找错误值,就可以借助IFERROR函数来隐藏错误值。

公式为:=IFERROR(VLOOKUP($K2,$A:$I,3,0),"工号有误")

2、反向查找数据

通过姓名来匹配公号,可以输入公式:=INDEX(A:A,MATCH(D2,B:B,0))

3、多条件查找

VLOOKUP的第一参数还支持用&连接多个单元格的内容,用于多条件查询:

4、借用COLUMN,搞定有序变化的返回列

如图所示,要找出A、B、D产品在1、2、3月的销量。

=VLOOKUP($A14,$A$1:$G$10,COLUMN(B2),FALSE)

5、MATCH——自动识别返回列

如果是1月、3月、5月的序列。

=VLOOKUP($A14,$A$2:$G$10,MATCH(B$13,$A$1:$G$1,0),FALSE)

6、借助INDIRECT函数可以实现多表查询数据

如下图所示:

三、判断类公式

(单条件判断、多条件判断、)

1、单条件判断

当商品库存大于等于30时,在“库存提示”处显示“充足”,小于“30”时,显示“补货”。

=IF($D5>=30,"充足","补货")

2、多条件判断

年会摸奖,摸到红色条,奖励微波炉,摸到绿色条奖励自行车,摸到蓝色条奖励数码相机。

C2中输入公式:

=IF(B2="红色","微波炉",IF(B2="绿色","自行车","数码相机"))

3、且字判断

成为优秀青年有两个并列的条件:年龄小于30,成绩大于90。这个时候我们就需要使用AND函数把两个条件并列在一起。

在D2中输入公式:

=IF(AND(B2<30,C2>90),"优秀青年","")

4、或字判断

譬如:根据销量或盈利金额判定产品是否畅销。只有销量大于1000或者盈利大于10000的产品属于畅销产品。

用OR函数把条件组在一起。D2中输入公式:

=IF(OR(B2>1000,C2>10000),"畅销","不畅销")

1、根据出生日期计算年龄

公式:=DATEDIF(D2,TODAY,"y")

2、根据身份证号码计算年龄

3、根据入职日期计算员工工龄

4、转换日期格式

输入公式:=TEXT(C2,"e年mm月dd日aaaa")

5、计算指定日期所在月份的工作日天数(不含周末)

输入公式=NETWORKDAYS(EOMONTH(A2,-1)+1,EOMONTH(A2,0))

6、计算员工转正日期

在D2单元格中输入公式=EDATE(B2,C2),并向下复制到D10单元格。

五、统计类公式

1、基本的统计公式

2、通配符模糊统计

比如,当需要查找王姓员工数量,可以输入公式=COUNTIF(A2:A14,"王*"),然后回车。

如果要查找姓名为三个字的员工数量,可以输入公式=COUNTIF(A2:A14,""),然后回车。

3、条件中可以嵌入函数公式

比如统计业绩大于平均值的人数,输入公式:

=COUNTIF(D2:D14,">="&AVERAGE(D2:D14))即可。

4、多个条件进行统计

如上图所示,{"销售一部","销售二部"}是数组,作为COUNTIF的条件,返回两个值{5,4},然后再用SUM函数进行相加,得到结果。

5、按类别或组别编号

譬如按部门编号,如下:

6、多条件计数

譬如统计业绩>5000,<10000的人数,输入公式=SUM(COUNTIF(D24:D36,{">5000",">10000"})*{1,-1})即可。

THE END
1.表格常用的9类,Excel表格,公式用法我们帮你整理好了!查找公式 1、查找数据(LOOKUP)示例公式:=LOOKUP(D2,B2:B8,A2:A8)解析:D2 是要查找的值,B2:B8 是查找的区域,A2:A8 是查找结果所在的区域。注意:使用该函数也可进行模糊查找。视频教程:《巧用LOOKUP函数,快速查找数据》2、跨表查找匹配数据(VLOOKUP)示例公式:=VLOOKUP(B3,被查询表!B2:C13,2,https://bbs.wps.cn/topic/38331
2.Excel函数公式:20个常用公式和示例,人力和行政办公效率必备!如果想提取详细的籍贯信息,可以到网上下载六位的区域代码表,并把公式中,提取前两位数字,改成提取前六位即可。 6、生肖公式(K列) 公式=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(G3)-4,12)+1,1)。 注:一共有12个生肖,因此将年龄除以“12(函数“MOD(年龄,12)”)得到的数字相同的就是同一个生肖。如https://www.meipian.cn/55fjankh
3.Excel公式大全与实战演示excel文本公式应用举例:在C11单元格中输入公式:=COLUMN(B11),确认后显示为2(即B列)。 特别提醒:如果在B11单元格中输入公式:=COLUMN(),也显示出2;与之相对应的还有一个返回行标号值 的函数——ROW(reference)。 5、CONCATENATE函数 函数名称:CONCATENATE 主要功能:将多个字符文本或单元格中的数据连接在一起,显示在一个单元https://blog.csdn.net/2401_87254608/article/details/142418755
4.Excel函数公式大全(图文详解)我把每个函数的中文名都制作成了目录,通过目录能够快速定位到相应的函数。如果这篇文章对你有帮助,记得点赞关注哟。 求和 SUM函数是一个求和函数,以将单个值、单元格引用或是区域相加,或者将三者的组合相加。 语法:SUM(number1,[number2],) number1 (必需参数)要相加的第一个数字。 可以是具体数字,也可以http://www.360doc.com/content/12/0121/07/26717439_1020997469.shtml