使用电子表格中的INDEX-MATCH处理数据

通常, 当初次学习Excel时, 很有可能会将VLOOKUP引入作为数据整理的主要方法。 VLOOKUP确实是一个强大的功能, 可以处理许多与数据处理相关的任务, 如电子表格中使用VLOOKUP进行数据整理中所示。在同一教程中, 我们还介绍了INDEX-MATCH组合的基础知识, 这是另一种执行垂直表查找的方法, 与VLOOKUP相比, 它具有多个优点, 但需要付出更复杂的公式。

这些优点包括以下功能：

• 动态列引用：这些允许你从数据集中安全地插入和删除列, 而不会影响查找功能。
• 大型数据集中的更高处理速度：如果表规模很大, 由于INDEX-MATCH只关心要检索的列和查找列, 因此你可能会注意到INDEX-MATCH比VLOOKUP快得多。
• 查找值的位置：VLOOKUP不能查找表范围内所选第一列左侧的任何值, 但是INDEX-MATCH也可以水平查找, 因此没有此限制。
• 限制的查找值大小：VLOOKUP不能查找超过255个字符的值, 但是INDEX-MATCH可以查找。

此外, INDEX-MATCH还允许基于多列的值进行匹配以及区分大小写的匹配, 这在你必须处理某些基于大小写字母而不同的ID时非常有用。这就是为什么本教程旨在深入研究INDEX-MATCH组合的高级功能, 并展示一些如何方便使用这些功能的示例。

INDEX-MATCH的基础

以最简单的形式, INDEX-MATCH可以与VLOOKUP相同的方式使用, 以基于公共键执行简单的垂直表查找。公式的基本结构如下所示：

= INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))

• column_range：要检索其值的列的范围。
• lookup_value：你要查找的值(键)。
• lookup_column_range：包含要匹配的键的列的范围。
• match_type：0或1。其中0表示精确匹配, 而1表示近似匹配。它类似于VLOOKUP中的FALSE / TRUE标志。

接下来, 让我们用两个玩具桌说明INDEX-MATCH的工作原理。第一个表包含一些名称和一些唯一键。第二个表包含相同的键(尽管不一定以相同的顺序)和一些描述。因此, 任务是使用INDEX-MATCH将第二个表中的描述与第一个表中的名称连接起来。这些表格如下所示：

如你所见, 第一个表缺少描述。现在让我们使用INDEX-MATCH将名称及其描述连接起来：

容易吧？正如你在视频中看到的那样, 第一步是选择一个column_range, 其中包含我们要在INDEX函数中检索的数据。在这种情况下, 即为描述(M2：M8)。第二步是选择MATCH函数内部的lookup_value, 即单元格B2。最后, 我们在MATCH函数内添加lookup_column_range(L2：L8), 由于我们希望lookup_value的精确匹配, 因此将match_type选择为0。剩下的只是按ENTER键并向下拖动公式以最终将名称与描述结合在一起。

INDEX-MATCH也可以用于连接单独工作表上的表, 例如在VLOOKUP中。下面是一个示例视频：

注意：你可能已经注意到, 我在视频中选择的列范围往往被$符号包围。这样做是为了选择在垂直或水平拖动公式时不会更改的固定范围。我将在本教程中继续使用该表示法。

到目前为止, 所有这些行为都非常像一个简单的VLOOKUP, 但是麻烦更多。但是, 如本教程开头所述, 如果你采用INDEX-MATCH路线, 则会有多种优势。动态列引用是最有用的方法之一。也就是说, 允许你将列添加到要查找值的表范围中, 而不会影响查找本身。

如果我们在玩具示例中添加了新列, 并且正在使用VLOOKUP, 则必须重写左表中的公式以引用描述, 该描述将变为第3列。但是, 如果你使用的INDEX-MATCH具有动态列引用, 则不需要。在下面的视频中可以看到这种比较：

上面的示例说明了当添加包含太空飞船类型的新列而使用VLOOKUP的列未添加时, 如何调整使用INDEX-MATCH执行查找的列。与VLOOKUP相比, INDEX-MATCH的这一优势确实体现在涉及在大型Excel表(例如业务销售报告)中进行查找的任务中。换句话说, 假设你有几个用Excel内置的业务仪表板, 并且所有这些仪表板都引用了一个包含所有数据的表。如果你正在使用VLOOKUP, 并且有一天有必要在中间插入一列以添加其他功能, 则必须在依赖于该表的所有仪表板中更改VLOOKUP函数调用。但是, 如果你使用INDEX-MATCH作为查找方法构建仪表板, 则不必面对此类问题。

先进的INDEX-MATCH：区分大小写的匹配

动态列引用非常有用, 但这并不是INDEX-MATCH必须提供的所有优点。例如, 可以增强INDEX-MATCH的基本语法以执行区分大小写的查找。特别是, 我们将在MATCH函数内添加一个EXACT函数来完成此操作。详细的语法如下所示：

{= INDEX(数据范围, MATCH(TRUE, EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}

• data_range：感兴趣的表范围, 包括查找列和要检索的所需列。
• lookup_value：你要查找的值(键)
• lookup_column_range：包含要匹配的键的列的范围
• match_type：0或1。其中0表示精确匹配, 而1表示近似匹配。它类似于VLOOKUP中的FALSE / TRUE标志
• required_column_number：与VLOOKUP中的列索引号相同。这是包含你要获取的数据的列索引。

当匹配包含字母且由这些字母的大小写区分的键时, 使用此修改后的INDEX-MATCH组合区分大小写的匹配非常有用(也就是说你有两个不同的键, 一个是” 00567UUp”, 另一个是” 00567UUP” )。例如, 当尝试匹配帐户ID或销售机会ID时, 可能会在广泛使用的Salesforce CRM内部遇到这些情况。目前, 虽然我们将不使用任何Salesforce数据集, 但将在下面的视频中使用玩具表的修改版本, 以说明上述公式如何起作用。

注意：以上功能组合用大括号括起来。这意味着这是一个数组公式, 必须按Cntr + Shift + Enter输入。

如你所见, 该公式的语法可能很长, 但是结果却与你期望的一样。它会像正常的INDEX-MATCH一样查找值, 但要考虑字符的大小写。魔术是通过EXACT函数完成的, 该函数返回TRUE和FALSE的数组。只要此数组中的TRUE为真, 就可以说存在一个具有完全相同大小写的值。之后, MATCH函数负责收集第一个TRUE出现的位置。

请注意, 在最终编写公式时, 按Cntrl + Shift + Enter至关重要, 否则你将得到一个＃N / A错误, 难以诊断。

先进的INDEX-MATCH：基于多种功能的匹配

Excel中区分大小写的查询非常酷, 但是其中一项功能更好。考虑多个条件的查找。考虑到众多标准, 通常将其作为条件计数或求和函数(如COUNTIFS或SUMIFS)的一部分来完成, 但是将其视为Excel中查找函数的一部分则更加奇特。然而, 在某些情况下它可能是有利的。例如, 如果你有一个HR Excel文件, 并且你想根据给定雇员的名字和姓氏查找工资, 这可能是一种方法。如果仅考虑两列中的一列, 则可能会得到错误的Employee的薪水, 因为可能有许多人具有相同的名字或姓氏。但是, 如果你同时根据姓和名检索值, 则很可能会找到正确的人。

话虽如此, 让我们现在考虑一下多个功能, 看看INDEX-MATCH的结构：

{= INDEX(column_range, MATCH(1, (lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)* …(lookup_value_n = lookup_column_range_n), match_type))}

在大多数情况下, 该公式具有与普通INDEX-MATCH完全相同的组成部分, 但是现在它可以考虑$ n $查找值及其各自的查找范围数。如果你需要复习这些组件的内容, 则可以向上滚动至基本的INDEX-MATCH语法。我们在MATCH函数中使用” 1″的原因是, 此公式创建了1和0的数组, 它们表示每个所需条件的匹配项。然后, 它使用MATCH函数检索找到的第一个1的位置。

与区分大小写的公式一样, 该公式也用花括号括起来, 这意味着在完成编写时必须按Cntr + Shift + Enter, 因为这是一个数组公式。

让我们来看一个示例, 通过尝试选择属于给定种族并具有给定最大扭曲速度的太空飞船, 此公式如何在下面的视频中起作用：

瞧！现在, 你已经拥有了基于多个键来匹配参数的工具。请记住, 在这个小玩具示例中, 我仅使用了两个, 即关键点和最大扭曲速度, 但是从理论上讲, 如果愿意, 可以继续添加标准。回到我们的人力资源示例, 你可以根据名字和姓氏以及职称进行匹配, 以最大程度地减少获得错误员工薪水的机会。

总结

恭喜你！你现在已经对INDEX-MATCH以及除简单的垂直查找之外可以用它完成的各种事情熟悉。如果某个时候你要负责处理复杂的Excel报告, 则此功能组合可能会成为你最好的朋友。像许多人一样, 我在学校里曾教过VLOOKUP, 但是当我认真地开始使用Excel进行诸如区分大小写的Salesforce帐户ID查找之类的任务时, 我意识到从长远来看, INDEX-MATCH是一个更好的选择, 尽管容易出错, 可以写公式。我鼓励你下次必须在Excel工作中进行垂直查找时尝试一下。谁知道, 也许你会像我一样喜欢它。

如果你想学习更多, 请随时阅读srcmini的”带有电子表格的数据分析”课程。