《梦醒蝶飞：释放Excel函数与公式的力量》5.3 index函数

5.3 index函数

Excel中的INDEX函数是一种强大的函数，它允许你从一个表格或范围中返回特定行和列的单元格的值或引用，它主要有两种使用形式：数组形式和引用形式。

5.3.1  INDEX函数的基本语法：

=INDEX(区域或数组, 行号, [列号])

区域或数组：这是你想要检索数据的单元格范围或数组。

行号：这是你想要检索的行的相对位置，从1开始计数。

列号：这是可选的参数，用于指定你想要检索的列的相对位置，同样从1开始计数。

5.3.2 INDEX函数的功能

INDEX函数主要用于查找和返回特定单元格范围内的值。它的功能包括：

1）查找特定行或列的数值，例如：

从A1:C3这个区域中检索第2行第3列的单元格值。公式可以写成：

=INDEX(A1:C3, 2, 3)。

A1:C3是我们要检索数据的区域。

2是行号，表示我们想要检索第2行的数据。

3是列号，表示我们想要检索第3列的数据。

按下enter键，显示如下结果：

2）可以跨越不同的工作表或工作簿进行数据检索。

INDEX函数还可以用于不同工作表或工作簿之间的数据查找。例如：

在sheet2工作表中输入=INDEX(Sheet1!A1:C3, 3, 2)。这个公式返回Sheet1工作表中范围A1到C3区域中第三行第二列的值。

3）可以用于动态地引用数据，尤其是在使用数组公式或与其他函数结合使用时。例如：

假设我们想要在其他列动态地引用上述数据区域中的姓名、年龄和职位。我们在D2单元格中输入：=INDEX($A$2:$A$4, ROW(A2)-ROW(A1)+1)

在单元格E2输入以下公式来引用年龄：=INDEX($B$2:$B$4, ROW(B2)-ROW(B1)+1)。

在单元格F2输入以下公式来引用职位：=INDEX($C$2:$C$4, ROW(C2)-ROW(C1)+1)。

5.3.3 INDEX函数的应用场景

1）数据分析

在数据分析中，INDEX函数可以用于查找和返回特定数据点。例如，假设我们有一份销售数据表，需要查找特定产品在特定月份的销售额。

我们要查找产品3在5月的销售额，我们可以使用以下公式：

=INDEX(B2:F11, MATCH("5月", A2:A11, 0), MATCH("产品3", B1:F1, 0))

各部分解释：

INDEX(区域, 行号, 列号)：返回在给定区域中，指定行号和列号交叉点的单元格的值或引用。

这里的区域是B2:F11，表示数据查找的范围是从第二行第二列到第十一行的第六列。

MATCH函数：

MATCH(查找值, 查找范围, 匹配类型)：返回查找值在查找范围中的相对位置。第一个MATCH函数查找"5月"在A列的位置：MATCH("5月", A2:A11, 0)。这里的0表示精确匹配。第二个MATCH函数查找"产品3"在第一行的位置：MATCH("产品3", B1:F1, 0)。同样，0表示精确匹配。

公式工作流程：

确定月份位置：MATCH("5月", A2:A11, 0)首先在A2:A11范围内查找"5月"，返回"5月"在该范围内的相对行号（从1开始计数）。

确定产品位置：MATCH("产品3", B1:F1, 0)然后在B1:F1范围内查找"产品3"，返回"产品3"在该范围内的相对列号。

返回对应值：INDEX(B2:F11, 行号, 列号)使用上述两个MATCH函数返回的行号和列号，从B2:F11数据区域中检索对应的销售额。

2）报表生成

在生成报表时，可以使用INDEX函数来动态获取和显示数据。例如，在汇总报表中显示每个区域的销售额。

步骤1：创建销售数据表

打开Excel，选择一个新的工作表，例如命名为“销售数据”。

在单元格A1开始输入数据，创建一个包含区域、产品和销售额的数据表：

步骤2：创建汇总报表在新的工作表中，例如命名为“汇总报表”，创建报表的标题和列标题

步骤3：使用INDEX和MATCH函数动态获取数据

在“汇总报表”工作表的B2单元格输入以下公式以动态显示区域名称：

=INDEX(销售数据!$B$2:$B$5, ROW(B2)-1)

在C2单元格输入以下公式以动态获取产品1的销售额：

=INDEX(销售数据!$C$2:$C$5, MATCH(B2, 销售数据!$B$2:$B$5, 0))

以此类推，为其他产品销售额和总销售额填充公式。例如，在D2、E2、F2单元格分别输入以下公式：

=INDEX(销售数据!$D$2:$D$5, MATCH(B2, 销售数据!$B$2:$B$5, 0))

=INDEX(销售数据!$E$2:$E$5, MATCH(B2, 销售数据!$B$2:$B$5, 0))

=INDEX(销售数据!$F$2:$F$5, MATCH(B2, 销售数据!$B$2:$B$5, 0))

5.3.4 INDEX函数与其他函数的联合使用

INDEX函数常与MATCH函数结合使用，以实现更灵活的数据查找和索引功能。例如，查找某个产品在某个月份的销售额。

比如说我们要查找产品2在2月份的销售额，月份查找值在单元格J1。

产品查找值在单元格I1。那么我们输入这样的公式：

公式解释：

使用MATCH函数找到“2月”在月份列中的位置（假设月份从第2行开始）：MATCH(J1, $A$2:$A$13, 0)

使用MATCH函数找到“产品2”在产品列标题中的位置：MATCH(I1, $B$1:$G$1, 0)

将上述MATCH函数的结果作为INDEX函数的参数来获取销售额：=INDEX($B$2:$G$13, MATCH(J1, $A$2:$A$13, 0), MATCH(I1, $B$1:$G$1, 0))

5.3.5常见错误和解决方案

#REF! 错误

原因：指定的行号或列号超出范围。

解决方案：确保行号和列号在指定范围内。

#VALUE! 错误

原因：行号或列号不是数字。

解决方案：确保行号和列号为有效的数字。

#N/A 错误

原因：使用MATCH函数时未找到匹配项。

解决方案：检查MATCH函数中的查找值和查找数组是否正确。

5.3.6高级应用和技巧

使用INDEX和COUNTA函数组合来动态获取列中最后一个非空单元格的值是一个高级应用技巧。以下是创建这个案例的步骤和解释：

步骤1：准备数据

打开Excel，选择一个新的工作表。

在列A中输入一些数据，数据可以是任意的文本或数字。

例如：

在这个例子中，我们有几种水果的名称在列A中，其中第4行是空的。

步骤2：创建动态命名范围

1）首先，我们需要创建一个动态命名范围，这可以通过使用NAMED RANGES功能来实现。

2）选择列A的数据区域，假设是A1:A5（确保包括最后一个非空单元格）

。

3）转到“公式”选项卡，点击“定义名称”（或在某些Excel版本中是“创建名称”）。

4)在弹出的对话框中，输入名称，例如“DynamicLastCell”。

5)确保引用位置是=OFFSET(A1, 0, 0, COUNTA(A:A), 1)，这将创建一个动态范围，始终引用列A中从第一个单元格到最后一个非空单元格的区域。

6)点击“确定”。

步骤3：使用INDEX函数引用动态命名范围

现在我们可以使用INDEX函数来引用这个动态命名范围，并获取最后一个非空单元格的值。

在工作表的某个单元格中，输入以下公式：

=INDEX(DynamicLastCell, COUNTA(DynamicLastCell))

这个公式中DynamicLastCell是我们刚刚创建的动态命名范围。

步骤4：理解公式

COUNTA(DynamicLastCell)计算动态命名范围中的非空单元格数量。

INDEX(DynamicLastCell, COUNTA(DynamicLastCell))使用这个数量作为行号参数来从动态命名范围中检索最后一个非空单元格的值。

步骤5：测试公式

按下Enter键，单元格将显示列A中最后一个非空单元格的值。

如果你添加更多的数据到列A，公式将自动更新以显示新的最后一个非空单元格的值。

步骤6：扩展使用

这个技巧可以用于处理动态增长的数据，例如日志文件或实时数据源。

你可以将这个公式用于条件格式化、数据验证、图表或其他基于动态数据的分析。

通过这个案例，你可以看到如何使用INDEX和COUNTA函数创建动态引用，这在处理不断变化的数据集时非常有用。