EXCEL中常用的嵌套函数

Candice
Candice
Candice
96
文章
0
评论
2020-04-1803:05:00 评论 151 1516字
摘要

你会在EXCEL中使用嵌套函数吗?EXCEL功能的强大离不开嵌套函数的存在。但是当你看到各种复杂的嵌套函数是否望而却步呢?本文带你揭秘如何使用嵌套函数,并提供最常用的嵌套函数供收藏~

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

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

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

1. Index函数 & Match函数

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

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

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

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

EXCEL中常用的嵌套函数

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

EXCEL中常用的嵌套函数

在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))

EXCEL中常用的嵌套函数

比如想要获取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)

EXCEL中常用的嵌套函数

3. Index函数 & Small函数 & If函数

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

EXCEL中常用的嵌套函数

在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。

4. Sum函数 & Sumif函数

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

在S3单元格中输入‘=SUM(SUMIF(A:A,R3:R6,G:G)),按ctrl+shift+enter以数组公式输入。

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

EXCEL中常用的嵌套函数

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

End.

作者:Candice

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: