办公室最应该学习的几大Excel函数公式!快收藏

迎曼说Excel 2024-05-26 01:00:02

文章最后有彩蛋!好礼相送!

Excel秘籍大全,前言

行政人事人员常用的函数公式大全,直接套用,快速提高工作效率。

Excel秘籍大全,正文开始

一、员工信息表

1、求状态(在职/离职)

=IF(D3="","在职","离职")

说明:用IF函数判断一个条件是否满足,如果满足返回一个值,不满足则返回另一个值。

2、求出生日期

=TEXT(MID(B3,7,8),"00!/00!/00")

说明:身份证第7位至第14位数代表出生年月日,先用MID函数提取数字,再用TEXT把数字转换为日期格式。

3、求性别

=IFERROR(IF(MOD(MID(B3,17,1),2)=0,"女","男"),"")

说明:身份证第17位数若为偶数,代表性别为女,否则为男;先用MID函数提取数字,再用MOD函数返回它与2相除的余数,余数为0,代表偶数;接着用IF函数进行条件判断,最后是用IFERROR函数将错误值返回空值。

4、求年龄

=IFERROR(YEAR(TODAY())-MID(B3,7,4),"")

说明:TODAY函数返回当前的日期,YEAR函数提取日期中的年份,MID函数提取身份证中的出生年份,IFERROR函数将错误值返回空值。

5、求退休日期

=IFERROR(EDATE(F3,IF(G3="女",660,720)),"")

说明:男,60岁退休,60*12个月/年=720个月;女,55岁退休,55*12个月/年=660个月;先用IF函数判断,女就返回660个月,接着用EDATE函数计算指定日期相隔指示的月份数后的日期。

6、求籍贯

=IFERROR(VLOOKUP(--MID(B3,1,2),代码!$A:$B,2,0),"")

说明:身份证前面两位数代表省份,先用MID函数提取,提取出的数字由于是文本格式,需要加--,再用VLOOKUP在“代码”表中查找数据。

7、按年计算工龄

=DATEDIF(C3,TODAY(),"Y")&"年"

说明:DATEDIF函数中的"Y"代表计算两个日期间隔的年数;"YM":忽略年和日,计算间隔月份数。

8、按年、月计算工龄

=DATEDIF(C3,TODAY(),"Y")&"年"&DATEDIF(C3,TODAY(),"YM")&"月"

9、统计男女人数

=COUNTIF(G:G,"男")

说明:COUNTIF函数计算区域中满足单个指定条件的单元格个数。

10、统计部门人数

=COUNTIF(G:G,"人事部")

11、统计某个时间段离职人数(2021年11月离职人数)

=COUNTIFS(D:D,">=2021/11/1",D:D,"<=2021/11/30")

说明:COUNTIFS函数计算区域中满足多个指定条件的单元格个数。

12、统计某个年龄段的人数 (大于等于20,小于30岁)

=COUNTIFS(H:H,">=20",H:H,"<30")

二、合同到期提醒

=IF(TODAY()-D2>0,"到期","未到期")

说明:先用TODAY函数返回当前的日期,再用IF函数进行判断。

三、计算全勤奖

=IF(C2>=22,300,0)

说明:出勤天数大于等于22天,奖励300元,否则为0元。

还觉得IF函数简单?不想出丑就了解下它的10个高阶用法!

2024-04-21 16:53·Excel秘籍大全

文章最后有彩蛋!好礼相送!

熟悉Excel的小伙伴们都知道,IF函数是Excel中最基础的条件判断函数,其用法也很简单:=IF(判断条件,真值,[假值]),第一参数判断条件可以是数值或表达式,真值TRUE为满足判断条件时返回的值,假值FALSE为不满足判断条件时返回的值,第三参数可省略。IF函数除了应用于基础的条件判断外,还有很多高级用法,很多小伙伴觉得IF函数很简单,看了以下用法,你还这样觉得吗?

1.IF函数基础用法

先来让大家回顾一下IF函数的基础用法,如图1所示,工龄大于等于3年,可享受薪资的5%作为提成,现在要求取满足条件的每名员工的提成金额,在G2单元格输入公式:=IF(D2>=3,F2*$I$2,"无提成"),并向下填充。

图1

2.逆向查询

VLOOKUP函数是目前职场应用频率最高的查询函数,但它仍然有不少不足之处,比如说,它本身并不支持逆向查询,需要搭配IF函数才可以。如图2所示,根据薪资查询对应的员工姓名,其公式为:=VLOOKUP(H2,IF({1,0},F1:F8,A1:A8),2,0)。

IF函数的第一个参数为数值{1,0},当第一个参数为1时,它表示真值,返回IF函数的第二个参数F1:F8构建的数据区域,当第一个参数为0时,它表示假值,返回IF函数的第三个参数A1:A8构建的数据区域,将0和1返回的结果结合起来,IF函数会构建成F1:F8&A1:A8联合起来的两列数据区域,A1:A8为姓名所在的第二列数据区域,对应VLOOKUP函数的第三参数2。IF函数的作用就是将原本逆向的数据区域让其顺向排列,进而搭配VLOOKUP函数进行数据查询。

图2

3.多条件判断——并关系

如图3所示,当工龄大于等于3年,年龄大于等于30,两个条件都满足时可获得其薪资的5%作为提成,要计算满足两个条件的员工的提成金额,在G2单元格输入公式:=IF(AND($B$2:$B$8>=30,$D$2:$D$8>=3),F2*$J$2,"无提成"),并向下填充,也可以写成:=IF(($B$2:$B$8>=30)*($D$2:$D$8>=3),F2*$J$2,"无提成")。

AND是Excel中的逻辑值函数,当每个参数判断条件都成立时AND函数返回TRUE时,AND($B$2:$B$8>=30,$D$2:$D$8>=3)和($B$2:$B$8>=30)*($D$2:$D$8>=3)意义一样,表示“并”的关系,即要求两个条件都满足。$B$2:$B$8>=30返回逻辑值组成的数组{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE},$D$2:$D$8>=3返回数组{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE},TRUE为满足条件返回的值,FALSE为不满足条件返回的值,在Excel四则运算中,逻辑值TRUE可以视为1,FALSE视为0,当两个数组相乘时,返回数组{0;1;1;0;0;0;1},1表示TRUE*TRUE即1*1,即两个条件都满足,当数组{0;1;1;0;0;0;1}为1时,返回IF函数的第二参数,为0返回IF第三参数。

图3

4.多条件判断——或关系

如图4所示,当满足工龄大于等于3或年龄大于等于30任意一个条件时,获得提成金额,要求满足条件的员工的提成金额,在G2单元格输入公式:=IF(($B$2:$B$8>=30)+($D$2:$D$8>=3),F2*$J$2,"无提成"),也可写成=IF(OR($B$2:$B$8>=30,$D$2:$D$8>=3),F2*$J$2,"无提成")。

逻辑值函数OR表示当其参数其中有一个返回TRUE,OR函数返回TRUE,($B$2:$B$8>=30)+($D$2:$D$8>=3)和OR($B$2:$B$8>=30,$D$2:$D$8>=3)意义一样,都表示“或”的关系。当逻辑值相加时,只有其中有一个是TRUE,那么结果就为TRUE。($B$2:$B$8>=30)+($D$2:$D$8>=3)返回数组{0;1;1;0;1;0;2},当为1或2时表示真值返回IF第二参数,为0表示假值返回IF函数第三参数。

图4

5.判断数据是否存在重复

如图5所示,判断姓名是否重复,其公式为:=IF(COUNTIF($A$2:$A$10,A8)>1,"是","否")。

用COUNTIF函数计数,如果有重复就会返回大于1的数值,IF函数的第一个参数如果是大于0的数值,那么它表示真值就会返回第二参数,如果为0即为假值会返回第三参数。

图5

6.忽略错误值和文本求和

如图6所示,忽略文本和错误值统计总销量,其公式为:=SUM(IF(ISNUMBER(C2:C10),C2:C10,0)),并同时按下Ctrl+Shift+Enter。

图6

先用逻辑值函数ISNUMBER对数据区域C2:C10进行判断,当为数值时返回TRUE,否则返回FALSE,ISNUMBER(C2:C10)返回的结果为数组{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE},IF函数第二参数返回数组{121;"未统计";132;132;#VALUE!;211;166;#NAME?;138},第一参数数组中对应的元素TRUE会返回第二参数的数组对应的元素,IF函数最终的值为{121;0;132;132;0;211;166;0;138},再用SUM函数对这个数组进行求和。

7.多条件求和

如图7所示,统计薪资7500以上的男性员工的总工资,其公式为:=SUM(IF((C2:C8=H2)*(F2:F8>7500),F2:F8,0)),并同时按下Ctrl+Shift+Enter。

图7

(C2:C8=H2)*(F2:F8>7500)为逻辑值组成的数组相乘,结果为{0;0;1;0;0;0;1},为1时返回IF函数的第二参数,为0时返回IF的第三参数,最终IF函数返回的结果为数组{0;0;8000;0;0;0;8500},再用SUM函数对这个数组进行求和。

8.对不规则的重复值排序

如图8所示,对部门进行排序,其公式为:=IF(B2<>B1,1,A1+1),对相邻的两个上下单元格进行判断,等不等于的时候,返回第二参数1,当等于时,返回第三参数,第三参数值为上个序号+1。

图8

9.IF+ISERROR处理错误值

IF+ISERROR处理错误值处理错误值比较常见 如图9所示,用VLOOKUP函数查找“小包”对应的线上销量,“小包”并不存在于数据区域中,也就是查找值不存在,当查找值不存在时VLOOKUP会返回#N/A错误值,这个时候就需要借助IF+ISERROR来将错误值转化为空值或者自行设定的文本,公式为:=IF(ISERROR(VLOOKUP(E2,A1:C10,3,FALSE)),"查找到此人",VLOOKUP(E2,A1:C10,3,FALSE))或者写:=IF(ISERROR(VLOOKUP(E2,A1:C10,3,FALSE)),"查找到此人",VLOOKUP(E2,A1:C10,3,FALSE))。

图9

10.IF多判断嵌套

IF多判断进行嵌套也是比较常见的情况,新版的EXCEL或者WPS支持IFS函数来代替IF函数的多条件嵌套,这里还是来给大家介绍原始的IF函数嵌套。如图10所示,判断成绩等级,其公式为:=IF(D2>=90,"优秀",IF(D2>=80,"良好",IF(D2>=60,"及格","不及格"))),也可以用IFS函数,其公式为:=IFS(D2>=90,"优秀",D2>=80,"良好",D2>=60,"及格",D2<60,"不及格")。

图10

以下《900套高逼格工作模板.xls 》免费下载,不收一分钱!

常用Excel

用Excel玩好报表

是必不可缺的技能

要知道一张好的图表

可以做到一图胜千言!

今天推荐的超实用干货是

《900套高逼格工作模板.xls 》

3.2G高逼格Excel可视化模板

制作精美 可直接套用

适合自用和内部培训使用

领取方式

关注我们

私信发送关键字:900

即可免费领取

资料来源于网络,公益分享,如有侵权,联系删除

0 阅读:1

迎曼说Excel

简介:感谢大家的关注