办公小技巧:用好Excel 2019新函数为办公提速

CFan 电脑爱好者 2021-05-17 08:26应用 标签:技巧 办公

大家知道Excel函数可以极大地提高我们的工作效率,而从Excel 2019开始中又新增了多个函数,下面就让我们一起来看看这些新函数有什么功能。

不惧多条件——IFS函数和SWITCH函数

当我们借助IF函数对多个数据进行条件判断的时候,往往需要嵌套多个IF函数,这样函数写起来较为复杂,而且不容易理解。比如老师们在判断学生成绩好坏时要用优、良、中、不及格等来表示,常规方法是通过“=IF(A1>=90,"优",IF(A1>=80,"良",IF(A1>=60,"中","不及格")))”来进行判断(图1)。

201923xhs1

图1 传统IF函数多级嵌套

Excel 2019新增的IFS函数就简单多了,它的格式是“IFS(条件1,结果1,[条件2],[结果2],…[条件127],[结果127])”。因此对于上述多个条件的判断,现在只要在C2单元格中输入函数“=IFS(B2<60,"不及格",B2<80,"中",B2<90,"良",B2>=90,"优")”,即可完成判断了(图2)。

201923xhs2

图2 IFS函数判断

当然在实际工作中还有很多类似的多条件判断,比如服装型号是由S、M、L、XL、XXL表示从到大,为了方便顾客识别,现在需要在原来型号后面自动添加备注,表示各自的中文型号(图3)。

201923xhs3

图3 示例数据

对于这种固定名称的多条件判断,借助新增的SWITCH函数可以快速实现。在B2单元格输入函数“=SWITCH(MID(A2,FIND("/",A2)+1,FIND("-",A2)-1-FIND("/",A2)),"S","小号(S)","M","中号(M)","L","大号(L)","XL","大号(+)","XXL","大号(++)","XXXL","大号(+++)")”,然后向下填充即可(图4)。

201923xhs4

图4 SWITCH函数多条件判断

小提示:SWITCH函数可以将多个表达式并列进行判断,在示例中可以看到要在B列自动进行判断,这里涉及S→XXL五个条件,而且需要从品名中间提取对应型号并添加中文说明。因此这里先使用MID函数提取型号字符中“/”和“-”之间的字符(即类似S、L字符),字符提取则通过嵌套的Find函数实现,后面"S","小号(S)"则表示并列多条件,实际内容根据备注要求填写的。

数据串联更简单——TEXTJOIN函数

在日常操作中,由于统计的需要,我们常常需要将多个数据整合在一起。比如在年段成绩统计的时候,作为段长经常要将每个班级优秀分数段人数统计出来,并且需要将这些名单汇聚在一起,方便老师们统计宣读,效果类似下列的表格(图5)。

201923xhs5

图5示例表格

从示例数据可以看到,这里首先条件是筛选80分(含)以上的同学,同时还要根据班级进行分列,最后使用“、”将他们汇总在一个单元格中。常规的方法是先使用VLOOKUP函数提取人名,再使用COUNTIF对范围内的数据统计,最后使用“&”连字符进行连接,操作起来非常繁琐。现在借助TEXTJOIN 函数嵌套IF函数可以很快求出答案。

定位到E2单元格,输入公式“=TEXTJOIN("、",TRUE,IF(($B$2:$B$10=D2)*($C$2:$C$10)>=80,$A$2:$A$10,""))”,输入完成后按Ctrl+Shift+Enter键确认输入,可以看到函数会自动将每个班级符合要求的同学列出,并且使用“、”进行分割,下拉函数可以完成其他班级数据的统计(图6)。

201923xhs6

图6 TEXTJOIN函数连接字符

小提示:第一个参数“、”表示联合字符的连接符号,第二个参数“TRUE”表示忽略空值。第三参数则是嵌套的IF函数,其第一参数“($B$2:$B$10=D2)”表示当D2单元格条件在B2:B10存在时(即属于一班同学),同时使用“($C$2:$C$10)>=80”进行判断,如果分数符合条件,那么姓名就显示在E2单元格(否则显示为空),最后使用“、”联合在一个单元格中。

极值求取更高效——MAXIFS/MINIFS函数

日常工作中经常要对数据求满足一定条件的最大值或最小值,但是之前版本并没有直接函数可以求取。比如公司为了提高技术员待遇,现在需要在工资表中找出职称为“助理工程师”,工资最低的员工,看看助工最低工资,以便进行调整。对于这类条件极值求取,之前大多使用MIN嵌套IF函数进行求取,操作比较复杂,现在利用MINIFS函数可以快速获取。定位到D2单元格,输入公式“=MINIFS(C2:C8,B2:B8,"助理工程师")”,表示在工资列中,按照职称为助理工程师条件在B列中查找,并显示出最小值。如果要求最大值,则将MINIFS更换为MAXIFS即可(图7)。

201923xhs7

图7 MINIFS求极值

QQ图片20210420165237