二维码 购物车
部落窝在线教育欢迎您!

INDEX:函数中的精确制导导弹,最强大的瘸子

 

作者:壹仟伍佰万、小雅来源:部落窝教育发布时间:2019-01-31 22:03:33点击:1645

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

这是部落窝函数课堂第3课,不容错过,因为你将认识INDEX函数中的精确导弹,最强大的瘸子!

 

 

上回书说到函数哲学家MATCH函数。今天我们主要说说INDEX函数!INDEX也是查找家族的一员,由于他强大的坐标定位功能,有时候VLOOKUP都是要靠边站呢!

 

一、认识INDEX函数

 

Index函数:在给定的单元格区域中,返回特定行列交叉处单元格的值或引用。

函数结构:index(单元格区域,行号,列号)

区域,行号,列号,很像通过坐标瞄准打靶呀。就像下面动图这样,找到列,找到行,单击就打中了!

 

 

我们想找到“囡”,可以看到其坐标是行4,列3

 

 

所以公式:=INDEX(B2:G11,4,3)就能得到“囡”。

 

 

二、INDEX函数基础用法

 

1.单行、单列中提取数值:只需一个坐标值

 

如果给定的区域是单行或者单列,那坐标就不需要两个数字了,只需要一个即可。

譬如我们现在需要在F17中从A17:A21中获得“李惠”。

输入公式:=INDEX(A17:A21,2)即可。

 

 

又譬如我们需要在G17中从A18:D18中取得李惠的基本工资。

输入公式:= INDEX(A18:D18,4)即可。

 

 

2.从一个多行多列区域提取数值:必须行列两个坐标值

 

这点就不列举了。前方找“囡”字就是这样的。

 

从上面的例子可以看出,INDEX通过坐标返回数值,像精确制导的导弹,指哪打哪(返回哪)。不过,纯粹的人工查坐标再输入坐标,太不符合“现代化”了。实际操作中,数据往往都是几十列,几十行甚至上万行的都有,这个时候我们再根据需要人工去查坐标输入坐标,就太不现实了。所以INDEX需要助手,需要组团才能打天下。

 

三、INDEX实战用法

 

1.与小助手COLUMNROW组团:实现半自动查找取值

 

1)与COLUMN组团可以连续返回同行多个数据

譬如我们需要从表中连续获取工号C23的姓名、年龄、入职时间。

 

 

在数据区域A17:E21中,工号C23位于第3行,姓名、年龄、入职时间的列数从左到右是分别是234。我们可以用COLUMNB1)来取代234实现半自动效果。公式如下:

=INDEX($A17:$E21,3,COLUMN(B1))

 

然后右拉填充即可。

 

得到的入职时间是数字,修改格式为短日期即可。

 

2)与ROW组团可以连续返回同列多个数据

 

譬如下面,我们用公式:=INDEX(A$17:E$21,ROW(A3),2)下拉填充获得三个工号的姓名。

 

 

3)与COLUMNROW同时组团

 

譬如我们可以用公式:=INDEX($A$17:$E$21,ROW(A3),COLUMN(B1))右拉下拉填充获得工号C23C08C10的姓名、年龄、入职时间。

 

 

通过与COLUMNROW组团,实现了半自动效果。只要是连续、有规律的取值,都可以用INDEX+ROW+COLUMN实现。

譬如我们需要隔行隔列取值,获得工号C15C23C10的姓名、入职时间。公式是:

=INDEX($A$17:$E$21,ROW(A1)*2-1,COLUMN(A1)*2)

然后右拉下拉填充即可。

 

 

半自动比完全的人工查坐标输入坐标简便多了,但之所以叫半自动那就是还需要人工去寻找数据的规律。如果取值的数据规律复杂或者没有规律,我们就无法半自动了。这个时候,就需要与大助手MATCH组团进行全自动工作。

 

2.与大助手MATCH组团:实现全自动查找取值

 

1INDEX+MATCH组团

 

下面的数据查找规律是乱的,我们不用自己去找规律,把一切都交给MATCH就好了。

 

 

C28中输入公式:

=INDEX($A$17:$E$21,MATCH($B28,$A$17:$A$21,0),MATCH(C$27,$A$16:$E$16,0))

然后右下下拉填充公式即可。

 

MATCH函数根据条件在固定区域中查询行、列位置完全取代了人工查找坐标或者数据规律,实现了全自动。对MATCH函数陌生的伙伴可以查看部落窝教育教程《MATCH:函数哲学家,找巨人做伴。新出道必学!》。

 

2INDEX+MATCHVLOOKUP+MATCH的区别

 

还记得《MATCH:函数哲学家,找巨人做伴。新出道必学!》中INDEX+MATCHVLOOKUP+MATCH的比较问题吗?

上面的查询我们也可以用VLOOKUP+MATCH实现。输入公式:

=VLOOKUP($B28,$A$17:$E$21,MATCH(C$27,$A$16:$E$16,0),0)

右拉下拉填充即可。

 

 

从公式长度来说,VLOOKUP+MATCHINDEX+MATCH简洁。那我们为何还需要INDEX+MATCH呢?原因就在于INDEX函数只要收到行列坐标值就可以查到数据,根本不存在什么正向查找、反向查找的区别。VLOOKUP就不行了,默认情况下它只能实现正向查找,也就是在查找区域里只能是从左往右查找,而不能从右往左查找。VLOOKUP要想实现从右往左的反向查找,就需要借助IF函数或者CHOOSE函数构建新的查找区域。

 

譬如我们需要通过姓名查工号,如下:

 

 

采用INDEX+MATCH组合直接写公式:=INDEX(A$17:B$21,MATCH(G17,B$17:B$21,0),1),然后下拉即可。

 

如果用VLOOKUP查找,因为是反向查找,就需要用IF函数重新构建查找区域,公式就变成:

=VLOOKUP(G17,IF({1,0},B$17:B$21,A$17:A$21),2,0)

 

 

所以,比较起来,正向查找的时候,用INDEX+MATCHVLOOKUP+MATCH都可以,VLOOKUP+MATCH相对更简洁;反向查找的时候,则用INDEX+MATCH最简洁,尤其是反向查找区域有三列、四列数据的时候,INDEX+MATCH是最佳选择。

 

好了,回答了函数课堂2中的问题后,我们继续看INDEX的实用组团。

 

3.与特邀嘉宾SMALLIF加上大小助手共同组团:实现一对多查找

 

组团后的公式格式是=INDEX(查找区域,SMALL(IF(),ROW()),MATCH())

譬如下方的动图所展示的那样:

 

 

公式很长:

=INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0))

 

套上防错的IFERROR函数,就更长了:

=IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0)),"")

 

这样的组合公式,我们又常称它为万金油公式,主要用于一对多的查找。公式的详细解析请看部落窝教育教程《Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读》。

 

OkINDEX的实战用法我们就介绍这么多。INDEX函数具有利用坐标精确取值的优势,但自身缺少根据条件自动查找坐标的功能,是个瘸子,所以实战中它需要助手协助来查找坐标。它是函数中的精确制导导弹,它是瘸子,一个强大的瘸子!

 

部落窝教育函数课堂,期待你的参与,我们节后再见!

 

 

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

http://www.itblw.com/ewebeditor/uploadfile/20180914164059384001.png

相关推荐:

部落窝函数课堂1《IF函数:剥洋葱》

部落窝函数课堂2《MATCH:函数哲学家,找巨人做伴。新出道必学!

一对多查找万金油公式Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读》

让公式更灵活的COLUMN函数《会用Column吗? 它让公式不那么笨》

VLOOKUP函数应用实例Vlookup快速核对人员的薪资变动