来源:俊红的数据分析之路
提到Excel,估计职场人都不会陌生,毕竟很大一票人都会在简历上写着"熟练使用Excel"。职场必备技能排行榜上,Excel绝对地位显赫。不过有多少人只是把Excel当作简单的数据录入工具和简单统计工具呢?这里不妄加评论。
此文章是复盘我日常工作中使用最多的Excel函数,供初入职场或者想要提高工作效率的职场人士做参考,如果你能够吸收完这一篇文章,可以抵上一年以上的工作经验。日常工具最重要的是使用场景,需要用时才会去找使用方法,然后在反复使用过程中逐渐熟练。我说的这个一年经验,就是避免你瞎找、不系统的找、不全面的吸收,等等……
1 IF类函数
1.1 IF函数
IF函数是日常工作中使用Excel时最常用的函数之一,IF函数承载着"如果......那么......否则......"这组关联词的作用。IF函数共有3个参数,每个参数扮演不同的角色,只有参数设置正确,Excel才会明白你的意图。
=IF(判断条件,条件ture返回值,条件false返回值)
函数中有3个参数,均写在括号中,参数间用逗号分割。第1个参数为判断条件,当返回TRUE时,则返回值1,否则返回值2
1.1.1 单条件判断
如下图所示,要根据D列的学生分数判断该学生某学科的分数是否及格。
E3单元格输入以下公式:
=IF(D3<60,"不及格","及格")
![](https://i-blog.csdnimg.cn/blog_migrate/19bdcfce27bafe3589e15d2f2d2abfc1.png)
1.1.2 多重条件判断
如下图所示,如果我们的条件为"物流"部门的"车辆管理员"才可能领取交通补贴,那么该如何筛选出符合条件的员工呢?这里就需要用到if的多重条件判断。
F18单元格输入以下公式:
=IF(D18='物流',IF(E18="车辆管理员","有","无"),"无")
![](https://i-blog.csdnimg.cn/blog_migrate/940c5f9bab073897f33a3e488c291d1c.png)
以上函数还实现了IF多层嵌套的逻辑。
1.2 SUMIF和SUMIFS函数
SUM是最常用的求和函数,当需要对报表范围中符合指定条件的值求和时,需要用到SUMIF和SUMIFS,它们两者的区别是:
SUM是直接求和,没有任何条件
SUMIF是单条件求和,相当于条件筛选一次后求和
SUMIFS是多条件求和,相当于条件筛选多次后求和
1.2.1 SUM函数
如下图所示,是某单位食堂的采购记录表,使用以下公式,即可计算所有采购物品的总数量。
=SUM(F42:F54)
![](https://i-blog.csdnimg.cn/blog_migrate/27e09c1302276fd64645691bbc32c084.png)
1.2.2 SUMIF函数
如果要按指定条件求和,那就要请出SUMIF函数了。
这个函数的用法是:
=SUMIF(条件区域,指定的条件,求和区域)
如下图所示,要计算职工食堂的物资采购总数量,公式为:
=SUMIF( 42: 54,I42, 42: 54)
公式的意思是,如果D39:D51单元格区域中等于I39指定的部门"职工食堂",就对F39:F51单元格区域对于的数值进行求和。
![](https://i-blog.csdnimg.cn/blog_migrate/2398c97cc5728b80221b1dad67f48158.png)
1.2.3 SUMIFS函数
SUMIFS函数的作用是多条件求和,这个函数的用法是:
=SUMIFS(求和区域,条件区域1,指定条件1,条件区域2,指定的条件2,......)
第一个参数指定的是求和区域,后面是一一对应的条件区域和指定条件,多个条件之间是同时符合的意思。
如下图所示,要计算部门是职工食堂,单价在1元以下的物资采购总量。
公式为:
=SUMIFS( 61: 73, 61: 73, 61, 61: 73, 61)
![](https://i-blog.csdnimg.cn/blog_migrate/2028fe495d7e2a599bca9244163285e8.png)
公式的意思是,如果D39:D51单元格区域中等于I39指定的部门"职工食堂",并且G39:G51单元格区域中等于指定的条件"<1",就对F39:F51单元格区域中对应的数值求和。
同样的,类似于SUMIF和SUMIFS函数,AVERGE/AVERGEIF/AVERAGEIFS函数是用来求算术平均值函数和有条件的求平均值函数。而COUT/COUNTIF/COUNTIFS函数是用来统计单元格区域内的数量和有条件的统计单元格数量。
1.3 IFERROR函数
函数公式为:IFERROR(value, value_if_error),表示判断value的正确性,如果value正确则返回正确结果,否则返回value_if_error。作用是用来将错误值修改为特定值,常见value的错误格式有#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL等。
通常我们使用时,都是在IFERROR中嵌套了其他函数,例如:
=IFERROR(VLOOKUP(......),0)
上面的函数意思是说,当VLOOKUP()出现错误值时,单元格显示为0。
如下两幅图,上图在单独使用VLOOKUP函数时,出现错误值#N/A,假如人工进行二次去删除错误值,数据量较大时会影响工作效率,此时配套IFERROR函数,可以事半功倍,如第二幅图。
![](https://i-blog.csdnimg.cn/blog_migrate/05ffb3cb045f211d14cf885331900db0.png)
![](https://i-blog.csdnimg.cn/blog_migrate/1701b9b8834b4bbca3784800dcef8a2d.png)
2 VLOOKUP函数
VLOOKUP函数是Excel中的一个纵向查找函数,它与LOOKUP函数和HLOOKUP函数属于一类函数,在工作中都有广泛应用。比如,当有多张表时,如何将一个excel表格的数据匹配到另外一个表中?这时候就需要使用VLOOKUP函数。
函数的语法为:
=VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)
第一个参数是要查询的值。
第二个参数是需要查询的单元格区域,这个区域中的首列必须是要包含查询值,否则公式将会返回错误值。如果查询区域中包含多个符合条件的查询,VLOOKUP函数只能返回第一个查找到的结果。
第三个参数用户指定返回查询区域中的第几列的值。
第四个参数是决定函数的查找方式,如果为0,则是精确匹配方式。如果为1,则是近似匹配方式。
2.1 常规查询
如图,需要从74:83的单元格区域中,根据H74单元格的编号查询对应的职务:
公式为:
=VLOOKUP(H96, 96: 105,3,0)
![](https://i-blog.csdnimg.cn/blog_migrate/2dae785325bed887381ab65de9c066b9.png)
提示:VLOOKUP函数第三个参数中的列号,不能理解为工作表中的实际的列号,而是指定要返回查询区域中的第几列的值。
2.2 带通配符查询
如下图,假如我们需要通过记录的部分SIM卡号,来获取到对应的手机号,这时直接使用
=VLOOKUP(F111, 111: 114,2,0)
是无法正常获取的,此时就需要利用通配符来进行补充F89,并用"&"符号连接。第一个"*"补充的是F89前方数据,后边"*"补充的是SIM卡号后边的数据。
=VLOOKUP("*"&F111&"*", 111: 114,2,0)
![](https://i-blog.csdnimg.cn/blog_migrate/f27dbe798f91e0a757a430b04c499547.png)
2.3 近似查询
在实际应用中,我们往往用到的是FALSE精确匹配,无须顾虑表格是否为升序排列(Truth近似匹配容易受此影响),万一没有查询到目标,也能迅速查找原因。那参数TRUE近似匹配有什么用武之地?
如下图,我们要对学生的成绩做评级。
0-60分,为不合格;60-80分,为合格;80-90分,为良好;90分以上,为优秀。
=VLOOKUP(E120, 121: 124,2,1)
![](https://i-blog.csdnimg.cn/blog_migrate/ba2a9cd1010981419796d83172d32e32.png)
提示:VLOOKUP函数第四参数为TRUE时,在近似匹配模式下返回查询之的精确匹配值或者近似匹配值。如果找不到精确匹配值,则返回小于查询值的最大值。使用近似匹配时,查询区域的首列必须按升序排序,否则无法得到正确的结果。
2.4 逆向查询
我们使用VLOOKUP进行查询的时候,通常是从左到右进行查询,但是当查询的结果在查询条件的左边时,单纯的使用VLOOKUP函数是没有办法完成的。可以借助CHOOSE函数组合使用来解决。
如下图,假如想查询部门为"销售"的员工编号。
=VLOOKUP(H132,CHOOSE({1,2},E132:E141,D132:D141),2,0)
![](https://i-blog.csdnimg.cn/blog_migrate/07b130efd17c8174c680d8d081ace315.png)
3 SUMPRODUCT函数
SUMPRODUCT函数是用于在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
方法1:
=SUMPRODUCT(数组1:数组2:数组3)
方法2:
=SUMPRODUCT(数组1*数组2)
两种方法区别在于,SUMPRODUCT函数的两个参数之间的连接符号不同,方法1用逗号连接,方法2用乘号连接。假如两个数组全部是数值,两种方法返回的结果是一致的,当有包含文本数据时,第一种方法可以返回正确结果,第二种方法会返回错误值#VALUE。
3.1 常规乘积求和
如下图,这便是一个简单的SUMPRODUCT函数,公式如下:
=SUMPRODUCT(C5:C8,D5:D8)
它的运算过程是:C5:C8和D5:D8两个区域数组内的元素对应相乘。
![](https://i-blog.csdnimg.cn/blog_migrate/15fbd3ca92cf08efc98bda92253755bf.png)
3.2 多条件计数
25岁及以下女性的人数:
=SUMPRODUCT(( 16: 22<=25)*( 16: 22="女"))
![](https://i-blog.csdnimg.cn/blog_migrate/707708384d5b82db9ba69ade42f29c32.png)
3.3 多条件求和
25岁及以下女性的业绩:
=SUMPRODUCT(( 16: 22<=25)*( 16: 22="女"), 29: 35)
![](https://i-blog.csdnimg.cn/blog_migrate/a717e6b7f7918032d3774099f87ca2b7.png)
3.4 二维区域求和
销售1部的所有业绩:
=SUMPRODUCT(( 42: 46="销售1部")* 42: 46)
![](https://i-blog.csdnimg.cn/blog_migrate/1c08a2c1bc4583aea6f251c7cc2d1f31.png)
3.5 二维区域多条件求和
销售1部3月的业绩:
=SUMPRODUCT(( 42: 46="销售1部")*( 53: 53="3月"), 54: 58)
![](https://i-blog.csdnimg.cn/blog_migrate/fd9edd2ca2b09a143b5d0c5eda826317.png)
4 MATCH函数组合
MACTH函数是EXCEL中使用较为广泛的一个函数,MATCH函数的功能就是在指定区域内搜索特定内容,然后返回这个内容在指定区域里面的相对位置。通俗的来讲,就是返回指定值在数值的位置,如果在数组中没有找到该值则返回#N/A。
=MATCH(查找的内容,查找的区域,匹配类型)
其中匹配类型包含1,0,-1
1或省略,查找小于或等于指定内容的最大值,而且指定区域必须按升序排序
0,查找等于指定内容的第一个数值
-1,查找大于或等于指定内容的最小值,而且指定区域必须按降序排序
如下图,想要在126:129区域内找出一个等于"100"的数值为第几个,按顺序找到D128在126:129区域内排第3,所以结果显示3。
=MATCH(100, 148: 151,1)
![](https://i-blog.csdnimg.cn/blog_migrate/2debbce0273e96fe5170f40387abb97e.png)
如下图,想要在137:140区域内查找出小于或者等于80的数值,按顺序找到E137和E138单元格的数值都小于"80",选择其中最大的数值,即E138的数值,区域内排第2,所以结果显示2。
=MATCH(80, 159: 162,1)
![](https://i-blog.csdnimg.cn/blog_migrate/84e8a786dfc268d1611812feb105b4ae.png)
如下图,想要在,148:151区域内查找出大于或等于"90"的数值,按顺序找到E148:E151单元格的数值都大于等于"90",选择其中最小的数值,即E149的数值,区域内排第2,所以结果显示4。
=MATCH(90, 170: 173,-1)
![](https://i-blog.csdnimg.cn/blog_migrate/122ce0a808b817b23991b7bfbeff5fd5.png)
以上就是MACTH函数的一些基本用法,假如只掌握MATCH函数,可能并不会觉得它有什么威力,若把它跟其他函数结合起来使用,就可以解决很多问题。
4.1 MATCH与OFFSET函数组合
说组合函数之前,先唠唠OFFSET这个函数,刚接触这个函数的时候,只知道这货是根据参考值进行偏移的函数,而且感觉没多大用处。但凡EXCEL玩的比较深的同学,都知道这货和其他函数匹配起来,特别好用。最常见的就是OFFSET+MATCH这个经典组合了。
OFFSET的作用是以指定的引用为参照系,通过给定偏移量得到新的引用。
=OFFSET(指定参照单元格,偏移行,偏移列)
偏移行,正数为向下偏移,负数为向上偏移
偏移列,正数向右偏移,负数向左偏移
如下图,这是OFFSET的一个最基本的使用方法,以A1为参考系,向下移动3行,向右移动3列,则得到D4的值。
![](https://i-blog.csdnimg.cn/blog_migrate/c81e23ba50c18faf7d6e9020b21c8a5f.png)
说完OFFSET函数,就可以聊聊OFFSET+MATCH的组合。MATCH函数的用法作用为返回指定数值在指定数组区域中的位置。
=OFFSET(参照值,MATCH(),MATCH())
第一个MATCH(),用来确定偏移的行数
第二个MATCH(),用来确定偏移的列数
如下图,首先 第一个MATCH()用来确定编号的位置,第二个MATCH()用来确定产品类型的位置。
=OFFSET( 181,MATCH(J183, 182: 193,0),MATCH(K183, 181: 181,0))
![](https://i-blog.csdnimg.cn/blog_migrate/4b21248b9ee8b4faf50fc8026e8b41cb.png)
4.2 MATCH与VLOOKUP函数组合
如何根据姓名和月份查找相应的销售量?利用VLOOKUP函数查找姓名,返回的列数为指定的月份所在的值;由于月份是变化的,所以想用一条公式就可以解决,就必须用其他公式确定月份的位置,这里就用到MATCH函数。
=VLOOKUP(I2, 1: 11,MATCH(J2, 1: 1,0),0)
可实现动态查询
![](https://i-blog.csdnimg.cn/blog_migrate/7248979f6bf888b35fa978c3e709dbaa.png)
4.3 MATCH与INDEX函数组合
先来说说INDEX函数的作用:
INDEX函数用于在一个区域中,根据指定的行和列号来返回内容。
=INDEX(单元格区域,指定的行数,指定的列数)
例如,以下公式,用于返回11:15单元格区域第3行和第4列交叉处的单元格值,即D13单元格。
=INDEX( 11: 15,3,4)
![](https://i-blog.csdnimg.cn/blog_migrate/56e9802f4a57f66fe0f4dbd2ed1c3c5f.png)
4.3.1 正向查询
如下图所示,根据D24单元格中的员工编号,在B列查询对应的职务。
公式为:
=INDEX( 24: 33,MATCH(D24, 24: 33,0))
先用MATCH函数,查找D24单元格的"M10004"在A列中所处的位置,得到结果为4,然后使用INDEX函数,在B列中返回第8个元素的内容,结果就是"秘书"
![](https://i-blog.csdnimg.cn/blog_migrate/edb8b15c75eca6ccc7215b7b10e50ca9.png)
4.3.2 逆向查询
如下图所示,根据D39单元格中的职务,在A列查询对应的员工编号。
公式为:
=INDEX( 39: 48,MATCH(D39, 39: 48,0))
先用MATCH函数,查找D39单元格的"秘书"在B列中所处的位置,得到结果为4,然后使用INDEX函数,在A列中返回第8个元素的内容,结果就是"M10004"
![](https://i-blog.csdnimg.cn/blog_migrate/7c132f9b12561036230e12e9a37a0f31.png)
两个不同方向的查询,使用的公式套路完全一样,如果有兴趣,你可以试试上下方向的查找公式怎么写。
4.3.3 多条件查询
除了常规的单条件查找,这两个搭档还可以完成多条件的查询。
如下图所示,需要根据F55(职务)和G55(年龄)信息,在A-C列单元格区域中,查找职务为"秘书",年龄为"56"所对应的员工编号。
公式为:
=INDEX( 39: 48,MATCH(F55&G55, 55: 64& 55: 64,0))
![](https://i-blog.csdnimg.cn/blog_migrate/539f154682d759dfc7f70dec7b501c69.png)
注意这里是一个数组公式,输入完成后需要将光标放到编辑框中,按照Ctrl+Shift不放,再按回车完成。
先使用连接符&,将F55和G55的职务和年龄合并成一个新的条件,再使用连接符将B列和C列的信息合并成一个新的查询区域。然后使用MATCH函数,查询出职务&年龄再查询区域中所处的位置为8。
最后用INDEX函数,得到A列第8个元素的内容,最终完成两个条件的数据查询。
以上,就是我这复盘的Excel函数,希望对大家有所帮助。