返回目录:excel表格制作
在实际工作中,我们经常会碰到一些复杂的问题,这些问题往往只用一个函数的话是解决不了的,这时就需要用函数的进阶用法--“嵌套”来解决了。
最后会介绍几个经典并且常用的函数嵌套组合。
那到底什么是“嵌套”呢?
所谓“嵌套”,就是让多个函数一起合作来解决问题,也就是把一个函数的返回值当做另一个函数的参数来使用。就像上图中的螺丝披一样,不同型号的螺丝就用不同的披头,握把是一个函数,披头就是另一个函数,一起合作就能解决单一函数解决不了的问题。
好了,废话不多说,上干货。
函数嵌套实战:IF+IF+IF
我们从最简单的IF函数的嵌套说起。IF函数是EXCEL中很实用也很强大的一个函数,我们先来熟悉一下IF函数的用法。
IF函数有三个参数:
Logical_test:任何可以计算为TRUE或FALSE的数值或表达式。
Value_if_true:当表达式或数值的计算结果为TRUE时的返回值。
Value_if_false:当表达式或数值的计算结果为FALSE时的返回值。
IF函数最多可以嵌套7层。
没错,你们有看错,IF函数自己就可以嵌套自己,而且最多还可以嵌套7层。
上图中,我们需要根据分数对学生的成绩进行评级。一共有四个级别,分别为“甲,乙,丙,丁”,如果只用一层的IF函数是不可能解决这个问题的,只有多个IF函数一起合作才有可能解决这个问题。
上图中,为了解决这个问题,我用了3个IF函数的来嵌套使用:
第一层:=IF(B2>=90,"甲",???),如果B2大于等于90,则返回值为“甲”,否则返回第二层的IF返回值。
第二层:=IF(B2>=80,"乙",???),如果B2大于等于80,则返回值为“乙”,否则返回第三层的IF返回值。
第三层:=IF(B2>60,"丙","丁"),如果B2大于60则返回“丙”,否则返回“丁”。
这个公式的执行顺序为:
- 最先执行第一层的表达式,结果为TRUE,则直接返回“甲”,结束计算;结果为FALSE时,转第2步;
- 继续计算第二层IF的表达式,结果为TRUE时,返回“乙”,结束计算;结果为FALSE时,转第3步;
- 继续计算第三层的表达式,结果为为TURE时,返回“丙”,为FALSE时返回“丁”,结束计算。
函数嵌套实战:VLOOKUP+MATCH
先看图,下图中演示的就是:VLOOKUP函数与MATCH函数合作的案例。
图中的组合公式为:=VLOOKUP($G2,$A$1:$D$7,MATCH(H$1,$A$1:$D$1,0),0)。
我用MATCH(H$1,$A$1:$D$1,0)的返回值,作为VLOOKUP函数的第三个参数,这样就解决了VLOOKUP函数匹配值位置确定的问题(列数过多的话这种方法就很方便了)。
函数嵌套实战:INDEX+MATCH+MATCH
看图,下图中,演示的是INDEX函数与两个MATCH合作的效果。
公式为:=INDEX($A$1:$D$7,MATCH($G2,$A$1:$A$7,0),MATCH(H$1,$A$1:$D$1,0))。
在公式中,我用两个MATCH函数分别代替了INDEX函数的第二和第三参数。
第一个MATCH函数,用于确定行的位置。
第二个MATCH函数,用于确定列的位置。
这样组合后,这个公式就达到了查找与引用的效果。
函数嵌套实战:INDEX+SMALL+IF
这个组合就很厉害了,在这里可以实现1对多的查询。
上图中的完整公式为:
=IFERROR(INDEX($B$1:$B$14,SMALL(IF($A$1:$A$14=$E2,ROW($A$1:$A$14),4^8),COLUMN(A1)))&"","")
是不是看到这么长的公式,头皮都有点发麻了,不要紧,我们把它拆开来看就行了。
公式解析:
这个公式中的关键点是:IF($A$1:$A$14=$E2,ROW($A$1:$A$14),4^8)。
- 这是一个数组公式,他的计算结果是一组数据而不是一个数据。
- 这个数组函数得到的其实是如上图所示的一组数据。它是把A1:A14的数据分别与E2的数据进行比对,如果相等则返回行号,如果不相等则返回4的8次方(在这里就是返回一个很大的数字,写成10的10次方也行,比你的数据范围大就可以),然后把这些返回的数值储存在内存中。
- SMALL函数则是返回一组数据中指定的第几小的数值。
- 在这里就是指返回IF函数返回的数组中的 COLUMN(A1) 小的数值,这里的COLUMN(A1)的返回值是1,所以就是返回最小的值。然后我们把这个函数网右拖动后会顺序变大,就会分别取出IF数组中的最小的值、第2小的值、第3小的值。。。。。。,这个值就是对应的行号。
- INDEX函数这根据SMALL函数取得的行号在指定的数据区域中取值。
- 最后的 &"",则是把任何值都转换成字符串。
我在最后又用一个IFERROR函数进行了去错处理,防止返回错误值 #REF 。
友情提示:因为这个公式是一个数组公式,所输入完后一定要用Ctrl +Shift+回车 的组合键结束输入。
最后,希望大家顺手点个关注,谢谢~!!