蓬莱小课：数据分析中的EXCEL怎么用才有水平吗？

你会在Excel中使用嵌套函数吗？Excel功能的强大离不开嵌套函数的存在。但是当你看到各种复杂的嵌套函数是否望而却步呢？今天小编就带你揭秘如何使用嵌套函数，并分享最常用的嵌套函数供收藏~

所谓的嵌套使用函数，指的是多个函数同时使用，其目的就是为实现某种复杂的统计功能。一个函数中通常有参数和常数，常数不能用其他的方式替换，而参数则可以嵌套进去另外一个函数，这样参数中的值就是由另外一个函数生成的。

函数可以一层又一层的嵌套，但是在实际工作中，不要过于追求函数和复杂程度，解决问题才是关键，解决方式当然是越简单越好。

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

01

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函数的嵌套实现了一个动态的效果。

02

Vlookup函数&Match函数

Vlookup和Match的组合与第一组Index和Match组合类似，但是用vlookup函数不能查找姓名列前面的列的信息，在本文的示例中，用vlookup就不能查看学员的班级信息，除非把班级一列放在姓名列的后面。

在M3单元格所用公式

 ‘=VLOOKUP(L3,B2:G16,MATCH(M2,B1:G1,0),0)

03

Index函数&Small函数&If函数

index+small+if实现一对多查询：

在O3单元格输入班级，在P列显示该班级所有学员的姓名。

在P3单元格输入

‘=INDEX(B:B,SMALL(IF($A$2:$A$16=O$3,ROW(B$2:B$16),4^8),ROW(A1)))&" "后，按ctrl+shift+enter以数组公式输入，最后将P3单元格向下拖动填充就可以了。&“ “是为了防止出现0。

04

Sum函数&Sumif函数

Sum和Sumif组合可以多条件求和

在S3单元格中输入

‘=SUM(SUMIF(A:A,R3:R6,G:G))，按ctrl+shift+enter以数组公式输入。

这样就可以对R3:R6单元格中的班级求总分啦。

Excel 的嵌套函数就介绍到这里，函数的使用重点在于自己的理解，刚开始的时候可以写下自己的思路和逻辑，慢慢就理清楚了。