职场最常用的31个函数公式，纯干货，赶紧收藏！

关注Office自学网的小伙伴们，可点击下方链接，领取学习课程

这七大类，31个常用公式，工作中经常遇到，希望大家能得到收获。

 

温馨提示：文章较长，可先收藏，闲时慢慢看。

 

目录：

一、求和公式

1、单条件求和

2、单条件模糊求和

3、多条件求和

4、隔列求和

5、多表相同位置求和

6、按产品和规格求和

7、合并单元格求和

二、查找与引用公式

1、单条件查找公式

2、双向查找公式

3、多条件查找

4、区间查找

5、逆向查找

6、横向查找

三、日期计算

1、工作日天数

2、计算员工转正日期

3、两日期间隔天、月、年数计算

四、统计公式

1、人数统计

2、快速标注重复数据

3、多条件统计

五：判断公式

1、单条件判断

2、多条件判断

3、多区间判断

4、多条件并列判断

5、字符查找判断

6、一对多查找判断

六：数字处理

1、加单位

2、日期格式

七：字符处理公式

1、多单元格字符串合并
2、截取除后3位之外的部分
3、截取-前的部分
4、截取字符串中任一段的公式

 

一、求和公式

 

1、单条件求和

在F2输入公式：=SUMIF(A:A,“苹果”,C:C)

2、单条件模糊求和

 

 

在G2输入公式=SUMIF(B:B,”2017*”,E:E)

说明：进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如”*A*”就表示a前和后有任意多个字符，即包含A。

 

3、多条件求和

 

求：沈伊杰销售数量合计：

在G18输入公式为：=SUMIFS(D:D,B:B,”沈伊杰”,C:C,”壁挂空调”)

 

4、隔列求和

 

在L2输入公式为：=SUM(VLOOKUP(9^9,B2:K2,{1,3,5,7,9})

5、多表相同位置求和

 

在B2输入公式：=SUM(Sheet1:Sheet4!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

6、按产品和规格求和

 

说明：SUMPRODUCT可以完成多条件求和

 

7、合并单元格求和

 

 

选中D列的这些单元格，输入公式：=SUM(C2:C11)-SUM(D3:D12)，按CTRL+回车，即可。

 

二、查找与引用公式

 

1、单条件查找

 

 

在E2输入公式=VLOOKUP(D2,A1:B12,2,0)

 

公式原理：VLOOKUP基本用法，不会的同学补课哟~

 

2、多条件查找

 

在I4输入公式=LOOKUP(1,0/(C4:C11=G4)/(D4:D11=H4),E4:E11)

3、多项查找

在B2中输入公式：

=VLOOKUP($A2,$H:$L,COLUMN(B:B),0)

然后再向右向下复制填充。

4、区间查找

 

 

在C2输入公式=VLOOKUP(B2,$E$2:$F$5,2,1)

公式说明：VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，成绩列的数字一定要升序排列。

 

5、指定区域最新日期查询

 

在K4输入公式=LOOKUP(1,0/(D4:J4<>””),(D4:J4))

 

下拉填充公式即可。

三、日期计算 

1、计算指定日期所在月份的工作日天数（不含周末）

 

 

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

 

2、计算员工转正日期

 

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

PS：一些时间计算公式：

相隔多少天？=datedif(A1,B1,”d”) 

相隔多少月? =datedif(A1,B1,”m”) 

相隔多少年? =datedif(A1,B1,”Y”) 

参数说明：“Y” 时间段中的整年数。”M” 时间段中的整月数。”D” 时间段中的天数。

四、统计公式 

 

1、人数统计

 

输入公式=COUNTIF(B:B,G2)

 

公式说明：B:B就是统计区域，G2是条件，结果表示B列中为“女”的数据有14个。

2、快速标注重复数据

输入公式为=IF(COUNTIF(A:A,A2)=1,””,”重复”)

 

公式说明：首先使用COUNTIF(A:A,A2)计算出每个姓名出现的次数，当结果大于1就表示姓名重复，进而使用IF函数得到最终的结果。

3、多条件统计

 

输入公式=COUNTIFS(B:B,G2,C:C,G3)

共有两组条件，B列是对性别进行判断，C列是对学历进行判断。

 

五：条件判断

1、单条件判断

 

 

输入公式=IF(B2=”男”,”男士”,”女士”)

公式原理：B2为“男”，则条件成立的返回的值为“男士”，否则条件不成立时返回的值为“女士”。

 

2、多重条件判断

如下图所示，专业代号理工对应代号LG 、文科对应代号WK、财经对应代号CJ，如何判断？

输入公式=IF(B2=”理工”,”LG”,IF(B2=”文科”,”WK”,”CJ”))

 

公式原理：条件B2为理工，则条件成立，返回值为“LG”，如果条件B2为文科，返回值为“WK”, 否则条件不成立，返回值为“CJ”。

 

3、多区间判断

 

如下图，600分以上为第一批，400-600为第二批，400以下为落榜。这种录取情况，如何判断？

输入公式=IF(B2>=600,”第一批”,IF(B2>=400,”第二批”,”落榜”))

公式原理：如果条件B2大于等于600，则条件成立，返回值为“第一批”；如果条件B2大于等于400，则条件成立，返回值为“第二批”, 否则条件不成立，返回值为“落榜”。

4、多条件并列判断

 

对60岁以上（含）的男性员工给予1000元奖金补助，该如何进行判断？

输入公式=IF(AND(A2=”男”,B2>=60),1000,0)

公式原理：条件A2性别为男，且B2年龄大于等于60，则条件成立，返回值为“1000”,否则条件不成立，返回值为“0”。()表示括号内的多个条件且同时成立。

 

5、字符查找判断

B2入公式：=IF(COUNT(FIND(“苹果”,A2))=0,”否”,”是”)

 

说明: FIND查找成功，返回字符的位置，否则返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是否成功。

六：数字处理

1、加单位

输入公式：=TEXT(D2,”¥0元”)

2、转换日期格式

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

七：字符处理

1、多单元格字符串合并

公式：c2

=PHONETIC(A2:A7)

说明：Phonetic函数只能对字符型内容合并，数字不可以。

2、截取除后3位之外的部分

公式：=LEFT(D1,LEN(D1)-3)

说明：LEN计算出总长度,LEFT从左边截总长度-3个

3、截取-前的部分

输入公式:B2=Left(A1,FIND(“-“,A1)-1)

说明：用FIND函数查找位置，用LEFT截取。

 

4、截取字符串中任一段的公式

公式:B1=TRIM(MID(SUBSTITUTE($A1,” “,REPT(” “,20)),20,20))

说明:公式是利用强插N个空字符的方式进行截取

 

微信扫一扫加关注，Office自学网官方微信公众号，

专注分享软件安装和视频教程，让你一号在手，办公无忧！