EXCEL中常用的嵌套函数

永不放弃

所谓的嵌套使用函数，指的是多个函数同时使用，其目的就是为实现某种复杂的统计功能。

一个函数中通常有参数和常数，常数不能用其他的方式替换，而参数则可以嵌套进去另外一个函数，这样参数中的值就是由另外一个函数生成的。函数可以一层又一层的嵌套，但是在实际工作中，不要过于追求函数和复杂程度，解决问题才是关键，解决方式当然是越简单越好。


本文以四组EXCEL中最常用的嵌套函数为例，帮大家理清使用嵌套函数的思路。



1. Index函数 & Match函数



MATCH(lookup_value,lookup_array,[match_type])：返回符合特定值特定顺序的项在数组中的相对位置。

INDEX(array,row_num,[coulumn_num])：在给定的单元格区域中，返回特定行列交叉处单元格的值或引用。



Index和Match组合可以根据条件查找字段，并且可以在条件改变后自动更新结果。除了可以实现VLOOKUP函数的查找功能外，还可以实现逆向查找，从右向左查找数据也轻松搞定。



我们来举个例子。下方是一张学员成绩表


现在想要实现，根据学员姓名查找其他相关信息，例如

在I3单元格输入学员姓名，在J2单元格任意输入你想查找的信息。上图所示，输入Excel，J3单元格就会显示Bob的Excel的成绩96分，如果J2单元格选择班级，则J3单元格就会输出Bob所在的班级一班。

这是怎么实现的呢？第一反应可能是用vlookup函数，后来发现我们无法用vlookup实现。这时就要用到嵌套函数了。
这里是在J3单元格输入了函数‘=INDEX(A2:G16,MATCH(I3,B2:B16,0),MATCH(J2,A1:G1,0))

比如想要获取Bob的Excel成绩，就要J3单元格返回到D3单元格的值，可以用Index函数返回到A2:G16中的第2行第4列的值，即‘=INDEX(A2:G16,2,4)。
可是我们需要行列的值是变动的，就要用到了Match函数的嵌套，MATCH(I3,B2:B16,0)表示I3单元格中的数据在B2:B16中处于第几行；MATCH(J2,A1:G1,0)表示J2单元格中的数据在A1:G1中处于第几列。
这样J3单元格就利用index函数和match函数的嵌套实现了一个动态的效果。

2. Vlookup函数 & Match函数
Vlookup和Match的组合与第一组Index和Match组合类似，但是用vlookup函数不能查找姓名列前面的列的信息，在本文的示例中，用vlookup就不能查看学员的班级信息，除非把班级一列放在姓名列的后面。
在M3单元格所用公式‘=VLOOKUP(L3,B2:G16,MATCH(M2,B1:G1,0),0)


3. Index函数 & Small函数 & If函数
index+small+if实现一对多查询

在O3单元格输入班级，在P列显示该班级所有学员的姓名。在P3单元格输入‘=INDEX(B:B,SMALL(IF($A$2A$16=O$3,ROW(B$2:B$16),4^8),ROW(A1)))&" "后，按ctrl+shift+enter以数组公式输入，最后将P3单元格向下拖动填充就可以了。&“ “是为了防止出现0。

4. Sum函数 & Sumif函数
Sum和Sumif组合可以多条件求和
在S3单元格中输入‘=SUM(SUMIF(A:A,R3:R6,G:G))，按ctrl+shift+enter以数组公式输入。
这样就可以对R3:R6单元格中的班级求总分啦。