Excel Index函数的使用方法及与Match、Small、If配合返回行列对应的多个值和一对多、多对多查找

在 excel 中，Index函数用于返回数组或单元格区域的值或值的引用，它分为数组形式和引用形式；二者的区别在于，数组形式只能引用一个连续的区域，引用形式除可以引用一个连续的区域外还可以引用多个不连续的区域，并还可以设置返回哪个区域。

Index函数常与 Match、Small、If 、 Row 等函数配合使用，其中 Index 与 Match 配合，可以实现返回行列对应的多个值和查找满足多个条件的值；Index 与 Small、If 、 Row 配合，可以实现一对多查找和满足多个条件的多对多查找。

一、Excel Index函数语法

（一）数组形式

1、表达式：INDEX(Array, Row_Num, [Column_Num])

中文表达式：INDEX(数组或单元格区域, 返回行号, [返回列号])

2、说明：

A、如果 Row_Num 和 Column_Num 都大于 0，Index函数返回 Row_Num 和 Column_Num 交叉处的单元格值；如果 Column_Num 大于 0，Row_Num 为 0，返回整列；如果 Row_Num 大于 0，Column_Num 为 0，返回整行。

B、如果要以数组形式返回值，行要以水平单元格区域形式输入，列要以垂直单元格区域形式输入；例如：{2,3,5;6,9,12}，“2,3,5;”为一行，该行分为三列。另外，必须按 Ctrl + Shift + Enter 才能返回数组。

C、Row_Num 和 Column_Num 必须指向同一单元格，否则将返回引用错误 #REF!；另外，在 Excel Web App 中，不能使用数组公式。

（二）引用形式

1、表达式：INDEX(Reference, Row_Num, [Column_Num], [Area_Num])

中文表达式：INDEX(对单元格区域的引用, 返回行号, [返回列号], [返回区域号])

2、说明：

A、如果 Reference 为引用一个不连续的区域，必须用括号括起来；例如：(A1:C3,D4:E9)。

B、Area_Num 为可选项，若把它设置为 1，表示返回第一个区域；若把它设置为 2，返回第二个区域；其它的以此类推；若省略 Area_Num，则默认返回第一个区域。

C、根据公式的需要，Index函数返回一个引用或数值；例如公式 =SUM(INDEX(D2:E8,3,2)) 中的 Index函数返回对 E4 的引用；而公式=5*INDEX(D2:E8,3,2) 返回 E4 单元格中的数值，具体见下面的实例。

D、除以上几点外，其它的与数组形式相同。

二、Excel Index函数数组形式的使用方法及实例

（一）返回行号与列号交叉单元格的实例

1、双击 D9 单元格，把公式 =INDEX(D2:E8,3,2) 复制到 D9，按回车，返回 528；操作过程步骤，如图1所示：

图1

2、公式中 D2:E8 为引用单元格区域，因为Index函数的数组形式参数 Array 既可以是数组又可以是单元格区域，第二个参数 3 为返回行号，第三个参数 2 为返回列号，由于返回行号和列号都不为 0，因此返回它们交叉单元格的值，正好是 E4；注意：返回行号与列号都从选定区域开始算起。

（二）Array为数组常量的实例

1、双击 A1 单元格，把公式 =INDEX({3,8,11;4,7,9},2,3) 复制到 A1，按回车，返回 9；操作过程步骤，如图2所示：

图2

2、公式中 {3,8,11;4,7,9} 是一个两行三列的数组，行与行之间用半角分号（;）隔开；返回行号和列号分别为 2 和 3，也就是返回第二行第三列对应的值，恰好是 9。

（三）返回引用错误值 #REF! 的实例

1、双击 D9 单元格，把公式 =INDEX(D2:E8,2,3) 复制到 D9，按回车，返回引用错误值 #REF!；操作过程步骤，如图3所示：

图3

2、公式引用区域 D2:E8 只有两行两列，而返回列号为 3，也就是引用了不存在的列，因此返回引用错误。

三、Excel Index函数引用形式的使用方法及实例

（一）引用多个不连续区域的实例

1、双击 B10 单元格，把公式 =INDEX((B2:C4,D6:E9),2,1,2) 复制到 B10，按回车，返回 921；操作过程步骤，如图4所示：

图4

2、公式中的单元格引用区域 (B2:C4,D6:E9) 为两个不连续的区域，因此要用括号括起来；返回区域号参数 Area_Num 为 2，即返回第二个区域，返回行号和列号分别为 2 和 1，即返回 D6:E9 区域的第二行第一列，也就是 D7。

（二）省略参数 Area_Num 的实例

1、双击 B10 单元格，把公式 =INDEX((B2:C4,D6:E9),2,1) 复制到 B10，按回车，返回 571；再次双击 B10，把“,D6:E9) 和一个左括 (”去掉，公式变为 =INDEX(B2:C4,2,1)，按回车，仍然返回 571；操作过程步骤，如图5所示：

图5

2、公式 =INDEX((B2:C4,D6:E9),2,1) 省略了参数 Area_Num，默认选择第一个区域 B2:C4，再返回该区域第二行第一列对应的单元格 B3；而公式 =INDEX(B2:C4,2,1) 与数组形式一样。

（三）返回对单元格引用的实例

1、双击 E9 单元格，把公式 =SUM(INDEX(D2:E8,3,2)) 复制到 E9，按回车，返回 528；按住 Alt，依次按一次 M、V，打开“公式求值”窗口，单击“求值”，公式变为 SUM($E)，说明Index函数返回的是对 E4 的引用；操作过程步骤，如图6所示：

图6

2、SUM($E) 表示对 E4 求和，$E 表示对 E4 单元格的绝对引用。

（四）返回引用单元格的值实例

1、双击 E9 单元格，把公式 =5*INDEX(D2:E8,3,2) 复制到 E9，按回车，返回 2640；按住 Alt，依次按一次 M、V，打开“公式求值”窗口，按回车“求值”，公式变为 5 * 528，说明Index函数返回引用单元格的值；操作过程步骤，如图7所示：

图7

2、从以上两例可知，Index函数可以根据公式的实际需求返回对单元格引用或单元格中的值。

（五）Index + Sum函数组合返回整行或整列的实例

1、把公式 =SUM(INDEX(B2:E9, 2, 0)) 复制到 E10 单元格，按回车，返回 1601；按住 Alt，依次按一次 M、V，打开“公式求值”窗口，按回车“求值”，公式变为 SUM($B$3:$E$3)，B3:E3 正是选定区域的第二行。双击 E10，把公式改为 =SUM(INDEX(B2:E9, 0, 3))，按回车，返回 5123，同样方法打开“公式求值”窗口，按回车，公式变为为 SUM($D$2:$D$9)， D2:D9 正是选定区域的第三列；操作过程步骤，如图8所示：

图8

2、Index函数返回整行或整列通常根据公式需要返回，仅输入单一的Index公式通常无法返回。

四、Excel Index函数的扩展应用

（一）Index + Match函数组合返回行列对应的多个值

1、假如要返回每个月各类服装的销量。双击 B12 单元格，把公式 =INDEX($A$1:$E$9, MATCH($A12,$A$1:$A$9,), MATCH(B$11,$A$1:$E$1,)) 复制到 B12，按回车，返回 567；选中 B12，把鼠标移到 B12 右下角的单元格填充柄上，鼠标变为十字架后，按住左键，往右拖，一直拖到 E12，则所经过单元格都返回相应销量；把鼠标移到 E12 右下角的单元格填充柄上，往下拖，则返回“3月和6月”的服装销量；操作过程步骤，如图9所示：

图9

2、公式 =INDEX($A$1:$E$9,MATCH($A12,$A$1:$A$9,),MATCH(B$11,$A$1:$E$1,)) 说明：

A、$A$1 是对行和列的绝对引用，往下拖，A1 不会变为 A2、A3、……；往右拖，A1 不会变为 B1、C1、……。$A$1:$A$9 以数组形式返回 A1 至 A9 的数据。

B、MATCH($A12,$A$1:$A$9,) 在 A1 至 A9 中查找 A12（1月），返回 A12 在 A1:A9 中的位置 2；Match函数省略了最后一个参数，默认选择 1，即返回小于等于查找值的最大值。

C、MATCH(B$11,$A$1:$E$1,) 在 A1 至 E1 中查找 B11（T恤），返回位置也为 2。

D、则公式变为 =INDEX($A$1:$E$9,2,2)，即在 A1:E9 中返回第二行第二列对应的单元格 B2 中的值，即 567。

提示：如果拖出的数据相同，按 Ctrl + C 保存即可。

（二）Index + Match函数组合实现查找满足多个条件的值

1、假如要查找“分类”为“男装”且“价格为 80 的服装名称。双击 C11 单元格，把公式 =INDEX(B2:D8,MATCH(A11&B11,C2:C8&D2:D8,),1) 复制到 C11，按 Ctrl + Shift + 回车，返回“黑色T恤”；操作过程步骤，如图10所示：

图10

2、公式 =INDEX(B2:D8,MATCH(A11&B11,C2:C8&D2:D8,),1) 说明：

A、公式是一个数组公式，因此要按 Ctrl + Shift + 回车。

B、A11&B11 是把 A11 与 B11 连接起来，C2:C8&D2:D8 是把 C2:C8 和 D2:D8 每行对应的值连结起来，例如 C2&D2、C3&D3。

C、则 MATCH(A11&B11,C2:C8&D2:D8,) 变为 MATCH("男装80",{"女装38.8";"女装55.8";"女装79.9";"女装80";"女装82";"男装80";"男装198"},)，接着在数组中查找“男装80”，找到后返回它在数组中的位置 6。

D、则公式变为 =INDEX(B2:D8,6,1)，最后在 B2:D8 中返回第六行第一列交叉的单元格中的值，即 B7 中的“黑色T恤”。

（三）Index + Small + IF + Row函数组合实现一对多查找

1、假如要查找“销售部”的所有员工姓名和职务。双击 F2 单元格，把公式 =IFERROR(INDEX(A:A,SMALL(IF(B$2:B$8=$E$2,ROW($2:$8),4^8),ROW(A1))),"") 复制到 F2，按 Ctrl + Shift + 回车，返回“林语彤”；选中 F2，往下拖，按 Ctrl + S 保存，则返回“销售部”的其他员工姓名；双击 G2，把上面的公式复制到 G2，并把公式中的 A:A 改为 C:C，按 Ctrl + Shift + 回车，返回“员工”，同样用往下拖的方法返回其他员工职务；操作过程步骤，如图11所示：

图11

2、公式 =IFERROR(INDEX(A:A,SMALL(IF(B$2:B$8=$E$2,ROW($2:$8),4^8),ROW(A1))),"") 说明：

A、B$2:B$8 以数组形式返回 B2 至 B8 中的元素，即 {"销售部";"行政部";"销售部";"财务部";"行政部";"销售部";"财务部"}；$E$2 为“销售部”；则 B$2:B$8=$E$2 变为 {"销售部";"行政部";"销售部";"财务部";"行政部";"销售部";"财务部"}="销售部"，接着，用数组中的每个元素与“销售部”比较，如果相等返回 True，否则返回 False，最后返回 {TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}。

B、ROW($2:$8) 以数组形式返回 2 到 8 之间的数值，即 {2;3;4;5;6;7;8}；4^8 是求 4 的 8 次方，结果为 65536（是 Excel 支持的最大值）。

C、则IF(B$2:B$8=$E$2,ROW($2:$8),4^8) 变为 IF({TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},{2;3;4;5;6;7;8},65536)，接着，从条件数组中取出第一个元素 TRUE，由于它为真，所以返回 If 第二个参数中的值，即返回数组 {2;3;4;5;6;7;8} 中与条件数组对应的元素 2；第二次从条件数组中取出第二个元素 FALSE，由于它为假，所以返回 If 第三个参数，即返回 65536；最后返回 {2;65536;4;65536;65536;7;65536}。

D、ROW(A1) 返回 A1 的行号 1；则 SMALL(IF(B$2:B$8=$E$2,ROW($2:$8),4^8),ROW(A1)) 变为 SMALL({2;65536;4;65536;65536;7;65536},1)，接着，用 Small 在数组中查找第 1 个小值，也就是 2。

E、则公式变为 =IFERROR(INDEX(A:A,2),"")，A:A 表示引用 A 列，最后在 A 列中返回第二行的值，即返回 A2 中的值“林语彤”；IfError 用于错误处理，如果 INDEX(A:A,2) 返回错误，则返回空，否则返回 INDEX(A:A,2) 的返回值。

提示：如果要查找其它部门的员工，把 E2 中的“销售部”改为其它部门按回车即可，如果没有变化，只需按 Ctrl + S 保存。如果要把返回的值全显示到一行，例如把“销售部”的员工显示到一行，只需把公式中的 ROW(A1) 改为 COLUMN(A1)，则公式变为 =IFERROR(INDEX(A:A,SMALL(IF(B$2:B$8=$E$2,ROW($2:$8),4^8),COLUMN(A1))),"")，往右拖即可。

（四）Index + Small + IF + Row函数组合实现多条件多对多查找

1、假如要查找所有女装且价格为 80 的服装名称。双击 C11 单元格，把公式 =IFERROR(INDEX(B:B,SMALL(IF((C$2:C$8=$A$11)*(D$2:D$8=$B$11),ROW($2:$8),4^8),ROW(A1))),"") 复制到 C11，按 Ctrl + Shift + 回车，返回“粉红衬衫”；用往下拖的方法返回其它符合条件的服装名称；操作过程步骤，如图12所示：

图12

2、公式与上面的 Index + Small + IF + Row函数组合公式一样，所不同的只是 If 的条件由多个表达式组成，每个表达式之间用星号（*）连接，表示“与”的关系，即要同时满足多个条件。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。