新资讯:Excel函数学习之查找函数INDEX()的使用方法

时间:2023-03-13 20:07:04       来源:转载

本篇文章带大家了解一下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.与小助手COLUMN和ROW组团:实现半自动查找取值

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

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

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

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

然后右拉填充即可。

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

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

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

(3)与COLUMN和ROW同时组团

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

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

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

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

然后右拉下拉填充即可。

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

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

(1)INDEX+MATCH组团

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

在C28中输入公式:

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

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

用MATCH函数根据条件在固定区域中查询行、列位置完全取代了人工查找坐标或者数据规律,实现了全自动。

(2)INDEX+MATCH与VLOOKUP+MATCH的区别

输入公式:

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

右拉下拉填充即可。

从公式长度来说,VLOOKUP+MATCH比INDEX+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+MATCH和VLOOKUP+MATCH都可以,VLOOKUP+MATCH相对更简洁;反向查找的时候,则用INDEX+MATCH最简洁,尤其是反向查找区域有三列、四列数据的时候,INDEX+MATCH是最佳选择。

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

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

组团后的公式格式是=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)),"")

这样的组合公式,我们又常称它为万金油公式,主要用于一对多的查找。

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

相关学习推荐:excel教程

以上就是Excel函数学习之查找函数INDEX()的使用方法的详细内容,更多请关注php中文网其它相关文章!

关键词: