EXCEL中嵌套函数的设计思路

电脑

EXCEL软件

EXCEL中为了达到某种目的，常常需要多个函数一起使用，某个（些）函数的结果作为另一个函数的参数，而这个函数又可能作为其它函数的参数，就这样一层一层的设计出公式，这种用法称为嵌套，版本不同，嵌套函数的允许层数也不同，2003版及以前最多只能有7层，此后的版本允许64层，超过7层的公式比较常见，但达到64层的几乎看不到，一般嵌套得比较多的也就十几层吧。

嵌套函数是以某函数的结果作为它函数的参数，所以这个函数的结果必须满足它函数的参数类型，否则就会出错。比如要返回单价，VLOOKUP函数的第三个参数应该用2，但现在用的是COLUMN(A:A)，结果为1，所以返回值并不是单价。

将第三参数改为COLUMN(B:B)，即公式改为：=VLOOKUP(D2,A:B,COLUMN(B:B),0)，即可得到正确结果。

嵌套函数的编辑，可以从外向内，也可以从内向外，或者内外结合。最常见最简单的嵌套函数要数IF函数的嵌套了，比如要将A列的分数改为等级，当分数小于60返回“不及格”、大于等于60小于80返回“及格”，大于等于80小于90返回“良”，大于等于90返回“优”，就可以使用IF函数来做。

先判断第一层，分数是否小于60，小于返回“不及格”、否则返回“及格”：=IF(A2<60,'不及格','及格')

但分数大于等于60时，是有多种情况的，所以再在此范围内进行判断，就形成了第二层函数，IF(A2<80,'及格','良')作为第一层的第三参数，当条件不成立，即分数不小于60时，返回IF(A2<80,'及格','良')的结果：=IF(A2<60,'不及格',IF(A2<80,'及格','良'))

同样的，当分数大于等于80时，还需要细分，用到第三层函数，IF(A2<90,'良','优')作为第二层IF函数的第三参数，当条件不成立，即分数不小于80时，返回IF(A2<90,'良','优')的结果：=IF(A2<60,'不及格',IF(A2<80,'及格',IF(A2<90,'良','优')))，利用三层嵌套函数完成公式，再向下填充。

再比如，现在的销售记录是记在一个单元格内，需要将每种水果的销售数量提取出来，这里以“香蕉”为例。

从表中可以看到，每天的销售中，各水果的销售量是不定的，销售的品种也是不定的，无法统一从某种字符开始提取，所以需要先判断当天销售中有没有“香蕉”这个品种，有的话再想办法提取，没有的话，直接返回空值或0，因此要先判断有没有“香蕉”，判断某个文本中有没有某个字符（串）常用FIND函数：=FIND(C$1,$B2)，这里为了便于提取出其它水果的数量，公式中都采用了绝对引用符“$”加以限定，公式可以向右向下填充。

再来确定这个5究竟应该是什么数，由表中可以看到，提取的数量是在“：”和“，”之间的数字，所以要判断“，”的位置才能确定要提取几个字符，为了不影响原公式，可以在旁边辅助列中先判断，然后再将这部分公式复制到原公式中。直接用：=FIND('，',$B2)，查找得到的结果是不对的，因为其中有多个“，”，返回的是第一个“，”出现的位置，因此需要从“香蕉”后面查找，就要用到FIND的嵌套：=FIND('，',$B2,FIND(C$1,$B2)+1)

知道了“香蕉”和“，”的位置就可以知道要返回几个字符：=FIND('，',$B2,FIND(C$1,$B2)+1)-FIND(C$1,$B2)-3，再从编辑栏中将此公式中“=”后的部分复制到原来公式中替换掉临时写的5：=MID(B2,FIND(C$1,$B2)+3,FIND('，',$B2,FIND(C$1,$B2)+1)-FIND(C$1,$B2)-3)，就可提取出相应的数量，但提取出来的数量是文本格式，为了变成常规数值，可以在前面加上“--”，也就是乘上两次负号，负负得正，这是文本格式的数字转为数值最常用的方法：=--MID(B2,FIND(C$1,$B2)+3,FIND('，',$B2,FIND(C$1,$B2)+1)-FIND(C$1,$B2)-3)

有了公式的框架，再来完善公式，一种是找不到“香蕉”会出现错误值，一种是“香蕉”后没有“，”都会出错，对于后者，可以在公式中加上逗号：=--MID(B2,FIND(C$1,$B2)+3,FIND('，',$B2&'，',FIND(C$1,$B2)+1)-FIND(C$1,$B2)-3)

对于前者，最外层再加个IFERROR函数，当出现错误值时，返回指定的值，比如空值：=IFERROR(--MID(B2,FIND(C$1,$B2)+3,FIND('，',$B2&'，',FIND(C$1,$B2)+1)-FIND(C$1,$B2)-3),'')

从上面可以看到，要设计出合理的嵌套函数，首先要熟悉各个函数的用法，再一层一层的使用函数，不能一步到位时，可以先写出部分，不确定的参数可以先用某数据代替，然后再研究此参数的写法，最终得到完整的公式。