实战之路《Excel 数据处理与分析实战宝典_第2版》

实战之路《Excel 数据处理与分析实战宝典_第2版》_8.4 INDEX 函数

【摘要】本书摘自《Excel 数据处理与分析实战宝典_第2版》一书中第8章，第4节，耿勇著。

8.4 INDEX 函数

8.4.1INDEX 函数的基本用法

实战之路《Excel 数据处理与分析实战宝典_第2版》_8.4 INDEX 函数

INDEX 函数是一个非易失性的、非常灵活的并且用途广泛的函数。INDEX 可以返回一个值或者一组值，可以返回对某个单元格的引用或者对单元格区域的引用，还可以实现返回整行或整列、查找、与其他函数结合实现求和、创建动态区域等功能。

INDEX 函数的语法格式有如下两种。其中 INDEX 函数的数组形式应用较广泛，将在以下章节中做重点介绍。

★数组形式=INDEX (array,row_num,column_num)

=INDEX (数据区域，行数，列数)

★ 引用形式=INDEX(reference,row_num,column_num,area_num)

=INDEX (一个或多个单元格区域的引用，行数，列数，从第几个选择区域内引用)

其中，row_num 和 column_num 必须指向数组中的某个单元格；否则INDEX 函数出错，返回#REF!错误值。

8.4.3 执行双向查找

INDEX 函数常常与MATCH 函数组合使用，这能够完成类似于VLOOKUP 函数和 HLOOKUP 函数的查找功能，并且可以实现逆向查询，即从右向左或从下向上进行查询。

在A2 、B2 、C2单元格中分别设置数据验证，在D2 单元格中定义如下数组公式：

=INDEX(H3:K9,MATCH(A26B2,SFS2:SF996SGS2:SGS9,0)-1,MATCH(C2,H2:K2,))

凡是需要通过行号(或列标)或者按行号和列标同时进行查找的，都可按这种模式进行。

8.4.4 创建动态区域

我们用于创建动态区域常用的是 OFFSET、INDIRECT 函数，这两个函数都是易失性函数，这意味着每次工作表中有改变时，包含这些函数的公式都将重新计算。INDEX 也可实现动态区域的创建，它不仅仅是非易失性的，而且比 OFFSET 或 INDIRECT 更快。OFFSET 或 INDIRECT 会在函数里创建动态区域，而使用 INDEX 会在区域操作符(冒号)的一侧(有时是两侧)产生动态区域。以下实例是在连续区域中创建动态区域的实例。

对比一下使用“=数据源!$A$2:$A$51” 定义名称“城市”。显然这种公式是硬编码、静态的引用，而在当前数据范围中 INDEX (数据源!$A:$A,COUNTA (数据源!$A:SA))一样可以返回$A$51 这个单元格地址。尽管二者指向的数据在当前数据范围中是相同的区域，但当数据范围进行扩张或者收缩时INDEX(数据源!$A:$A,COUNTA(数据源!SA:SA))会返回数据范围中A 列最后的非空单元格地址。很显然，硬编码、静态的引用远没有上述INDEX 函数创建的动态区域引用灵活。

公式解析：这个公式是非易失性的、动态的区域，随着列表中城市数量的变化而扩展或收缩。本实例定义名称中INDEX 函数的这种写法正是“INDEX 函数可以返回对某个单元格的引用或者单元格区域的引用”的体现。这种用法只是返回满足某种条件的值所在单元格的地址，不是返回值本身。

注意，代替“指向”使用 INDEX 建立的动态区域的命名公式(城市)的值之前，需要使用绝对引用，否则在使用名称时会导致使用名称计算时出现循环引用错误提示。如上述引用改为“= 数据源!SB$2:INDEX(数据源!B:B,COUNTA(数据源!B:B))” 引用时就会出现提示，导致结算结果错误。忽略循环引用错误后，在定义名称中的计算公式时，名称中无“$”符号固定的列号会偏移到一个新的列位置，这是因为不带绝对引用，就是针对活动单元格的引用。

可以使用相同的方法对这张表创建二维动态区域。出于统计需要可能会增加其他统计指标，因此列数也是动态的。

说明：INDEX 可以很好地结合3个引用操作符(即冒号、空格和逗号)使用。当在3个引用操作符的任意一个的一侧或两侧使用函数时，在工作簿打开时总会重新计算结果公式。虽然 INDEX 是非易失性的，但是当用于动态区域时它会变成半易失性的。不过，这比易失性好，因此利用该函数可以创建动态区域。

在不连续区域中创建动态区域

当处理非连续单元格区域时，INDEX 提供了如下公式中的第4个参数(m≤n, 且 m 为自然数)来从所提供的非连续的输入区域中选择区域块。这些区域块通过整数按在输入单元格区域中出现的顺序来引用。