为什么 INDEX 和 MATCH 在 Excel 中优于 VLOOKUP

我们之前已经给初学者讲解过如何使用VLOOKUP的基本功能（英文VLOOKUP，缩写代表“垂直查找功能”）。 并且向有经验的用户展示了几个更复杂的公式。

在本文中，我们将尝试提供有关使用垂直搜索的另一种方法的信息。

您可能想知道：“为什么需要这样做？”。 为了显示所有可能的搜索方法，这是必要的。 此外，许多 VLOOKUP 限制通常会阻止获得所需的结果。 在这方面，INDEX( ) MATCH( ) 功能更加丰富多样，而且它们的限制也更少。

索引匹配

由于本指南的目的是展示此功能有多好，我们 让我们看一下有关其操作原理的基本信息。 我们将展示示例，并考虑为什么它比 VLOOKUP() 更好。

INDEX 函数语法和用法

此功能有助于根据列或行号在指定的搜索区域中找到所需的值。 语法：

=INDEX（数组，行号，列号）：

  • 数组——搜索将发生的区域；
  • 行号——要在指定数组中搜索的行号。 如果行号未知，则必须指定列号；
  • 列号——要在指定数组中找到的列号。 如果值未知，则需要行号。

一个简单公式的例子：

=索引（A1：S10,2,3）

该函数将在 A1 到 C10 的范围内进行搜索。 数字显示要显示所需值的行 (2) 和列 (3)。 结果将是单元格 C2。

很简单，对吧？ 但是，当您使用真实文档时，您不太可能拥有有关列号或单元格的信息。 这就是 MATCH() 函数的用途。

MATCH 函数语法和用法

MATCH() 函数搜索所需的值，并在指定的搜索区域显示其近似值。

searchpos() 语法如下所示：

=MATCH（要查找的值，要查找的数组，匹配类型）

  • 搜索值——要找到的数字或文本；
  • 搜索数组——搜索将发生的区域；
  • 匹配类型——指定是查找确切的值还是最接近它的值：
    • 1（或未指定值）——返回等于或小于指定值的最大值；
    • 0 – 显示与搜索值完全匹配。 在组合 INDEX() MATCH() 中，您几乎总是需要完全匹配，所以我们写 0；
    • -1 – 显示大于或等于公式中指定值的最小值。 排序按降序进行。

例如，在 B1:B3 范围内注册了纽约、巴黎、伦敦。 下面的公式将显示数字 3，因为伦敦在列表中排名第三：

=EXPOSE(伦敦,B1:B3,0)

如何使用 INDEX MATCH 函数 

您可能已经开始理解构建这些功能的联合工作的原理。 简而言之，那么 INDEX() 在指定的行和列中搜索所需的值。 MATCH() 显示这些值的数量：

=INDEX（返回值的列，MATCH（要搜索的值，要搜索的列，0））

仍然很难理解它是如何工作的？ 也许一个例子会更好地解释。 假设您有一份世界首都及其人口的列表：

为了找出某个首都的人口规模，例如日本的首都，我们使用以下公式：

=INDEX(C2:C10, MATCH(日本, A2:A10,0))

说明：

  • MATCH() 函数在数组 A2:A10 中查找值“Japan”并返回数字 3，因为 Japan 是列表中的第三个值。 
  • 这个数字去电话号码”在 INDEX() 公式中，并告诉函数从该行打印一个值。

所以上面的公式就变成了标准公式 索引（C2：C10,3）。 该公式从 C2 到 C10 单元格搜索，并从该范围内的第三个单元格（即 C4）返回数据，因为倒计时是从第二行开始的。

不想在公式中指定城市名称？ 然后将其写入任何单元格，例如 F1，并将其用作 MATCH() 公式中的参考。 最后你会得到一个动态搜索公式：

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

为什么 INDEX 和 MATCH 在 Excel 中优于 VLOOKUP

重要！ 中的行数 排列 INDEX() 必须与中的行数相同 考虑数组 在 MATCH() 中，否则会得到错误的结果。

等一下，为什么不直接使用 VLOOKUP() 公式？

=VLOOKUP(F1, A2:C10, 3, 假)

 浪费时间试图弄清楚 INDEX MATCH 的所有这些复杂性有什么意义？

在这种情况下，使用哪个函数并不重要。 这只是了解 INDEX() 和 MATCH() 函数如何协同工作的示例。 其他示例将展示这些功能在 VLOOKUP 无能为力的情况下的功能。 

索引匹配或 VLOOKUP

在决定使用哪种搜索公式时，许多人同意 INDEX() 和 MATCH() 比 VLOOKUP 优越得多。 但是，很多人仍然使用 VLOOKUP()。 首先，VLOOKUP() 更简单，其次，用户并不完全理解使用 INDEX() 和 MATCH() 的所有优点。 如果没有这些知识，没有人会同意花时间研究一个复杂的系统。

以下是 INDEX() 和 MATCH() 相对于 VLOOKUP() 的主要优势：

 

  • 从右到左搜索。 VLOOKUP() 不能从右向左搜索，所以要查找的值必须始终在表的最左侧列中。 但是 INDEX() 和 MATCH() 可以毫无问题地处理这个问题。 本文将告诉你它在实践中的样子：如何在左侧找到所需的值。

 

  1. 安全添加或删除列。 VLOOKUP() 公式在删除或添加列时显示不正确的结果，因为 VLOOKUP() 需要准确的列号才能成功。 自然，当添加或删除列时，它们的数量也会改变。 

并且在 INDEX() 和 MATCH() 公式中，指定了列的范围，而不是单个列。 因此，您可以安全地添加和删除列，而无需每次都更新公式。

  1. 搜索量没有限制. 使用 VLOOKUP() 时，搜索条件的总数不得超过 255 个字符，否则您将获得 #VALUE！ 因此，如果您的数据包含大量字符，则 INDEX() 和 MATCH() 是最佳选择。
  2. 处理速度高。 如果您的表相对较小，那么您不太可能注意到任何差异。 但是，如果表包含成百上千行，并且相应地有成百上千个公式，则 INDEX() 和 MATCH() 将比 VLOOKUP() 处理得快得多。 事实上，Excel 将只处理公式中指定的列，而不是处理整个表格。 

如果您的工作表包含大量公式，如 VLOOKUP() 和 SUM()，则 VLOOKUP() 的性能影响将特别明显。 解析数组中的每个值需要单独检查 VLOOKUP() 函数。 因此 Excel 必须处理大量信息，这大大减慢了工作速度。

公式范例 

我们已经弄清楚了这些功能的用处，所以我们可以进入最有趣的部分：知识在实践中的应用。

从右到左搜索的公式

如前所述，VLOOKUP 无法执行这种形式的搜索。 所以，如果想要的值不在最左边一列，VLOOKUP() 就不会产生结果。 INDEX() 和 MATCH() 函数更通用，值的位置对它们的工作没有太大的作用。

例如，我们将在表格的左侧添加一个排名列，并尝试找出我国首都在人口方面的排名。

在单元格 G1 中，我们写入要查找的值，然后使用以下公式在 C1:C10 范围内搜索，并从 A2:A10 返回对应的值：

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

为什么 INDEX 和 MATCH 在 Excel 中优于 VLOOKUP

迅速的。 如果您打算将此公式用于多个单元格，请确保使用绝对寻址来修复范围（例如， $A$2：$A$10 和 $C$2：4C$10).

指数 更多曝光 更多曝光  在列和行中搜索

在上面的示例中，我们使用这些函数作为 VLOOKUP() 的替代，以从预定义的行范围内返回值。 但是，如果您需要进行矩阵或双边搜索怎么办？

听起来很复杂，但是这种计算的公式与标准的 INDEX() MATCH() 公式相似，只有一个区别： MATCH() 公式必须使用两次。 第一次获取行号，第二次获取列号：

=INDEX(array, MATCH(垂直搜索值, 搜索列, 0), MATCH(水平搜索值, 搜索行, 0))

我们看下表，试着做一个公式 索引（） 快递（） 快递（） 以显示特定国家/地区在选定年份的人口统计数据。

目标国家位于单元格 G1（垂直查找）中，目标年份位于单元格 G2（水平查找）中。 公式将如下所示：

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

为什么 INDEX 和 MATCH 在 Excel 中优于 VLOOKUP

这个公式如何运作

与任何其他复杂的公式一样，通过将它们分解为单独的方程，它们更容易理解。 然后您可以了解每个单独的功能的作用：

  • 比赛（G1，A2：A11,0，XNUMX） – 在 A1:A2 范围内查找值 (G11) 并显示该值的编号，在我们的示例中为 2；
  • 搜索（G2，B1：D1,0，XNUMX） – 在 B2:D1 范围内查找值 (G1)。 在这种情况下，结果为 3。

将找到的行号和列号发送到 INDEX() 公式中的相应值：

=索引（B2：D11,2,3）

结果，我们在 B2:D3 范围内的 2 行和 11 列的交叉点处的单元格中有一个值。 该公式显示了所需的值，该值位于单元格 D3 中。

使用 INDEX 和 MATCH 按多个条件搜索

如果您已阅读我们的 VLOOKUP() 指南，您可能已经尝试过多种搜索公式。 但是这种搜索方法有一个明显的限制——需要添加一个辅助列。

但好消息是 使用 INDEX() 和 MATCH() 您可以搜索多个条件，而无需编辑或更改工作表。

以下是 INDEX() MATCH() 的通用多条件搜索公式：

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

那个笔记： 此公式必须与键盘快捷键一起使用 CTRL+SHIFT+ENTER。

假设您需要根据 2 个条件找到您要查找的值： 买方 и 产品。

这需要以下公式：

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

在这个公式中，C2:C10 是搜索发生的范围， F1 – 这种情况， A2：A10 — 是比较条件的范围， F2 – 条件 2， V2:V10 – 比较条件 2 的范围。

不要忘记在工作结束时使用公式按下组合 CTRL+SHIFT+ENTER – Excel 会自动用大括号关闭公式，如示例所示：

为什么 INDEX 和 MATCH 在 Excel 中优于 VLOOKUP

如果您不想在工作中使用数组公式，则在公式中添加另一个 INDEX() 并按 ENTER，它将如下例所示：

为什么 INDEX 和 MATCH 在 Excel 中优于 VLOOKUP

这些公式是如何工作的

此公式的工作方式与标准 INDEX() MATCH() 公式相同。 要搜索多个条件，您只需创建多个 False 和 True 条件来表示正确和不正确的单个条件。 然后这些条件适用于数组的所有相应元素。 该公式将 False 和 True 参数分别转换为 0 和 1，并输出一个数组，其中 1 是在字符串中找到的匹配值。 MATCH() 将找到第一个匹配 1 的值并将其传递给 INDEX() 公式。 反过来，它将从所需列的指定行中返回已经所需的值。

没有数组的公式取决于 INDEX() 自己处理它们的能力。 公式中的第二个 INDEX() 匹配 falsy (0)，因此它将具有这些值的整个数组传递给 MATCH() 公式。 

这是对该公式背后逻辑的相当冗长的解释。 欲了解更多信息，请阅读文章“多个条件的索引匹配“。

INDEX 和 MATCH 中的 AVERAGE、MAX 和 MIN

Excel 有自己的特殊功能，用于查找平均值、最大值和最小值。 但是，如果您想从与这些值关联的单元格中获取数据怎么办？ 在这种情况下 AVERAGE、MAX 和 MIN 必须与 INDEX 和 MATCH 一起使用。

索引匹配和最大值

要在 D 列中找到最大值并将其显示在 C 列中，请使用以下公式： 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

索引匹配和最小值

要在 D 列中找到最小值并将其显示在 C 列中，请使用以下公式：

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

搜索索引和 SERPENT

要在 D 列中找到平均值并在 C 中显示该值：

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

根据数据的写入方式，MATCH() 的第三个参数是 1、0 或 -1：

  • 如果列是按升序排序的，设置1（那么公式会计算最大值，小于等于平均值​​）；
  • 如果排序是降序，则-1（公式会输出大于等于平均值​​的最小值）；
  • 如果查找数组包含一个完全等于平均值​​的值，则将其设置为 0。 

 在我们的示例中，总体按降序排序，因此我们输入 -1。 结果是东京，因为人口值 (13,189) 最接近平均值 (000)。

为什么 INDEX 和 MATCH 在 Excel 中优于 VLOOKUP

VLOOKUP() 也可以执行此类计算，但只能作为数组公式：VLOOKUP with AVERAGE, MIN 和 MAX.

索引匹配和 ESND/IFERROR

您可能已经注意到，如果公式找不到所需的值，则会引发错误 ＃N / A. 您可以用更多信息替换标准错误消息。 例如，在公式中设置参数 在XNUMXth:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

使用此公式，如果您输入的数据不在表格中，表格将为您提供指定的消息。

为什么 INDEX 和 MATCH 在 Excel 中优于 VLOOKUP

如果你想捕获所有错误，那么除了 在XNUMXth 可以使用 IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “出了点问题！”)

但请记住，以这种方式掩盖错误并不是一个好主意，因为标准错误会报告公式中的违规行为。

我们希望您发现我们的使用 INDEX MATCH() 函数的指南对您有所帮助。

发表评论