INDEX 函数

hercules028 2022-10-28 发布于四川

展开全文

END

基础语法

INDEX函数有两个基础语法，分别为：

INDEX(array,row_num,[column_num])

INDEX(reference,row_num,[column_num],[area_num])

参数row_num，row是行的意思，表示行号，同理，column_num表示列号。第一个语法：对于一个多行多列的数组或区域，指定它的行号、列号，即可提取它对应的位置。

第二个语法：第一个参数有多个区域，可以通过第4个参数来选定其中的第几个区域，然后再在这个区域中指定行列信息。公式如下：=INDEX((A1:B3,D2:G8,B6:B20),4,3,2)

这个公式表示从(A1:B3,D2:G8,B6:B20)这3个区域中选择第2个区域，即D2:G8区域，然后提取此区域的第4行第3列的值，也就是F5单元格的信息。

提取纵向、横向及二维区域中的值

本节将讲解INDEX函数在一维和二维区域中的引用方式。

1．对于纵向区域引用

如图25-1所示，D11:D18单元格区域为基础数据源。在F12单元格中输入公式：

=INDEX(D11:D18,3)

ENTERTITLE

图25-1对于纵向区域引用

得到D11:D18单元格区域中的第3行的值，结果为D13单元格的“黄承彦”。

注意，INDEX是提取第1个参数中的第n个值，而不是提取第1个参数中不为空部分的第n个值。例如，公式“=INDEX(D9:D18,5)”，虽然D9、D10单元格全都是空值，但它的结果不会从D11单元格开始往下数第5个值。而是从选取的区域D9:D18中取第5个值，也就是返回D13单元格的值。

前面是对于单元格区域的引用，当第1个参数是数组时是否也可以呢？在G12单元格中输入公式：

=INDEX({'许褚';'张飞';'黄承彦';'徐庶';'郭嘉';'黄权';'马超';'庞统'},2)

在一个数组中，英文状态下的分号分隔表示纵向排列，英文状态下的逗号分隔表示横向排列。我们可以看到，最终的结果是提取该数组中的第2行的值，返回结果为“张飞”。

提示：当需要从单元格区域中引用值，并把它们变成数组形式的时候，不需要一个个手动输入，尤其在数据量多的时候。可以先引用单元格区域，然后选中该参数后按【F9】键执行一次计算，如图25-2所示。

ENTERTITLE

图25-2将区域转化为数组

2．对于横向区域引用如图25-3所示，D21:K21单元格区域为一个横向的基础数据源。在F24单元格中输入公式：

=INDEX(D21:K21,1)

ENTERTITLE

图25-3对于横向区域引用

返回结果为“许褚”，是这个区域中的第一个值，现在一切正常，我们继续计算，在F25单元格中输入公式：

=INDEX(D21:K21,7)、

返回结果为“马超”，计算到这里，发现问题了吗？

INDEX的第2个参数明明是row_num，表示的是第几行，而D21:K21这个区域只有1行，我们提取第7行的结果应该得到错误值。、

我们再看一下Excel的帮助文件，其中有这么一句话：“如果数组只包含一行或一列，则相对应的参数row_num或column_num为可选参数。”

这句话可以换个角度理解，如果只有一行或一列，我们可以只指定一个参数，而这个参数代表的是一个序列数。就像上体育课站队，如果只站成一列纵队，老师会说第3位同学出来，那么“黄承彦”就走出来了。没有必要说第1列第3位同学，因为只有一列。同样，如果站成一行横队，老师喊第7位同学出来，那么“马超”就走出来了，而没必要说第1行第7位同学。

横向的数组是否也可以使用同样的引用？我们操作试试，在G24单元格中输入公式：

=INDEX({'许褚','张飞','黄承彦','徐庶','郭嘉','黄权','马超','庞统'},4)

返回的结果是横向数组中的第4个值“徐庶”，操作正确。

3．对于二维区域引用

如图25-4所示，D28:G36单元格区域是二维数据区域。在I29单元格中输入公式，即可得到数据区域中的第2行第4列的值，即3000。=INDEX($D$29:$G$36,2,4)

ENTERTITLE

图25-4对于二维区域引用

INDEX还可以引用多行多列的数组，在J29单元格中输入公式：

=INDEX({'一组','马岱',42403,4000;'一组','黄月英',42403,3000;'一组','黄忠',42422,3000;'一组','黄盖',42451,6000;'二组','孙乾',42403,8000;'二组','许褚',42424,5000;'二组','张飞',42437,7000;'二组','黄承彦',42438,5000},3,1)

从这个数组中提取第3行第1列的数据是“一组”。

一个多行多列的数组是先按行来排列，一行排列完再排列下一行，所以看到的都是先逗号后分号。

4．对于整行或整列的引用

这时再仔细读读Excel的帮助信息，其中有一句话是“如果将row_num或column_num设置为0（零），函数INDEX则分别返回整个列或行的数组数值”。

还以图25-4中的数据为例，编写公式：=SUM(INDEX(D29:G36,0,4))

公式返回结果为“41000”，INDEX函数中row_num参数为0，说明选择了第0行。第0行到底是第几行呢？这里没有明确的指定，所以INDEX就把全部的行都引用过来。column_num参数为4，所以返回结果为D29:G36单元格区域的第4列，即G29:G36的销售金额列。

在引用时，一整列的内容无法在一个单元格中完整地展示出来，但这并不影响在公式最外面套一个SUM函数，因此整个公式表示对销售金额的求和，即“41000”。这个SUM函数仅用于辅助大家理解公式的结果。

公式可编写为：

=SUM(INDEX(D29:G36,5,0))

公式返回结果为“50403”，表示对区域中的第5行的引用，即D33:G33区域。由于日期的本质就是数字，2016/2/3相当于数字42403，因此该区域求和为42403+8000=50403。

对整行或整列引用的知识了解即可，构造区域一般习惯性地使用OFFSET函数，有时会用到INDIRECT函数。

案例：制作工资条

下面使用INDEX函数来做一个工资条。在实际工作中，如学生分数、员工工资等都是对他人保密的，所以发纸质明细时，需每个人一条信息。如果用A4纸打印，每人一张，明显太浪费，每个工资条只需两行就够了。我们把所有人的信息都打印在一张纸上，中间留出空行，打印出来之后分别裁剪就行了，如图25-5所示，A1:G9单元格区域是基础数据，我们根据此基础数据信息变成右侧打印版的形式。

ENTERTITLE

图25-5工资条效果

工资条具体怎么制作呢？

有的人利用重复编号然后排序的方案，这种操作方法每次都要做一遍，会重复工作。

有的人使用VBA的技术，能一键完成，那没有VBA基础的人要怎样制作呢？我们用函数的方法来搞定。

首先观察规律：右侧的第1，4，7，10，…行都是引用基础数据源的第1行数据，可以在I列的相应行位置标注上数字1；右侧的第3，6，9，12，…行都是空白行，可以在I列相应行位置放一个较大的数字，如999，这个数字大于原始数据的总行数即可；右侧的第2，5，8，11，…行分别引用基础数据源的第2，3，4，5，…行，可以在I列相应行位置依次输入数字2，3，4，5，…

至此在I列各单元格依次输入数字：1，2，999，1，3，999，1，4，999，1，5，999，…

这些数字有什么作用呢？它们实际就是即将引用的基础数据的第几行。在J1单元格中输入公式：=INDEX(A:A,$I1)并将公式向右向下复制，如图25-6所示。

ENTERTITLE

图25-6基础思路分解

至此整个思路都讲完了，此时对公式是否有了基本的理解？我们开始实质性地构造I列的数字列，取代刚才的手工输入。

首先是处理1，4，7，10，…行，它们是公差为3的等差数列。进一步说，它们除以3的余数都是1，那么I1单元格的公式为：

=IF(MOD(ROW(),3)=1,1,0)

利用之前讲的IF函数并列的思路，让每一个IF公式只做一件事情，这里判断当前行是否为1，4，7，10，…行，如果是就标记为1，不是就为0。

其次第3，6，9，12，…行的特点是除以3的余数都为0，于是完善公式：=IF(MOD(ROW(),3)=1,1,0)+IF(MOD(ROW(),3)=0,999,0)

最后处理第2，5，8，11，…行，先输入公式：=IF(MOD(ROW(),3)=1,1,0)+IF(MOD(ROW(),3)=0,999,0)+IF(MOD(ROW(),3)=2,???,0)

整体的结构是和前面一致的，我们在最关键的部分暂时先写上“???”，如何从2，5，8，11，…变成2，3，4，5，…呢？

日常工作中，大家也会遇到这种处理有规律的数字的情况，这里教大家一个放之四海皆准的秘籍：无论你的数字序列是什么样的，都先把它还原到1，2，3，4，…的基准序列。

实际操作一下。2，5，8，11，…它们的公差是3，要变成1，2，3，4，…首先要缩小3倍，我们先写下“=???/3”。

那么几除以3等于1呢？当然是3除以3等于1，所以公式变为“=3/3”。继续操作。被除数3是怎么得出的？当前行位于第2行，所以2+1=3，即“=(ROW()+1)/3”。

上一步我们可以进行验证，当位于第5行时，=(5+1)/3=2，当位于第8行时，=(8+1)/3=3。成功变成1，2，3，4，…了，那怎么变成2，3，4，5，…呢？可以直接+1，所以这部分的公式合成为：

=(ROW()+1)/3+1

将它组合进最终的公式：=IF(MOD(ROW(),3)=1,1,0)+IF(MOD(ROW(),3)=0,999,0)+IF(MOD(ROW(),3)=2,(ROW()+1)/3+1,0)

为了增加公式的可读性，将它进行适当的换行排版，如图25-7所示。

ENTERTITLE

图25-7公式排版

面就可以套入INDEX函数完成最后的步骤了，在J1单元格中输入以下公式，并向右复制到P1单元格，向下复制到第n行，得到的效果如图25-8所示。=INDEX(A:A,IF(MOD(ROW(),3)=1,1,0)+IF(MOD(ROW(),3)=0,999,0)+IF(MOD(ROW(),3)=2,(ROW()+1)/3+1,0))

ENTERTITLE

图25-8完成效果1

这里发现第3，6，9，12，…行的结果并不是空白，都是数字0。这是因为引用的相应单元格，如A999单元格是空白的，所以为0。屏蔽掉所以这些行的数字0，将原来的公式稍微变化一下：=INDEX(A:A,IF(MOD(ROW(),3)=1,1,0)+IF(MOD(ROW(),3)=0,999,0)+IF(MOD(ROW(),3)=2,(ROW()+1)/3+1,0))&''

只是在原有公式的最后增加了“&''”，将原来公式的结果连接一个空文本，3，6，9，12，…行就变成空白了，如图25-9所示。

ENTERTITLE

图25-9完成效果2

这是否意味着只要是0&''结果就返回空白？我们来做个试验，如图25-10所示。

ENTERTITLE

图25-10空白连接示意

在A2单元格中输入公式“=0&''”，可以看到结果为0，而且在单元格中是默认左对齐的，说明这个结果是文本型的数字0，所以0&''返回空白的想法是错的。

在A3单元格中输入公式“=C3&''”，其中C3是空单元格，这时候连接上一个空文本，得到的结果也就是空。

在A4单元格中输入公式“=C4”，C4也是空单元格，返回结果为数字0，这并不是说C4单元格是0，而是说它是空白的。

提示;使用“&''”方式屏蔽空白的方法诞生于Excel2003及之前版本，那时候没有IFERROR函数，很多时候需要通过ISERROR等函数做出判断后再二次返回结果，导致公式很长。高手们就在摸索中发现了这种连接空白的方式减少字符。

只是通过这种方式得到的结果，都是文本型的值，包括文本型数字，在需要二次计算的时候会有一定的麻烦，请谨慎使用。

公式都完成了，最后利用“格式刷”做一下整体的格式调整。