Excel中如何活用INDEX和MATCH函数-Excel学习网
发布网友
发布时间：2022-04-22 02:25
共1个回答
热心网友
时间：2023-08-23 10:31
大多数Excel用户需要在工作簿中查找数据。但是最好的查找方法是什么？
Excel提供了两种主要的查找方法： VLOOKUP和 INDEX - MATCH。尽管两种方法相似，但INDEX-MATCH的功能更强大。
但是，我怀疑VLOOKUP的知名度更高，使用更广泛。可能是因为如果您要查找某些内容，则可以使用名称中带有“ lookup”的功能。
在本文中，我将解释VLOOKUP和INDEX-MATCH。在此过程中，我将解释这两种方法的优缺点。
当然，还有两个附加的查找功能：
HLOOKUP的工作方式与VLOOKUP相同，但它是水平而不是垂直。因此，我所说的关于VLOOKUP的所有内容也适用于HLOOKUP。
LOOKUP旨在与Lotus 1-2-3兼容。和1-2-3的@LOOKUP函数旨在与VisiCalc兼容。因此，LOOKUP的功能不如VLOOKUP。但是，它确实比VLOOKUP具有一个优势，我将在本系列的后续文章中对此进行讨论。
VLOOKUP函数具有以下参数：
= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）
range_lookup参数是可选的，通常会被忽略，但非常有用。这是因为range_lookup确定VLOOKUP返回近似匹配还是精确匹配。
尽管Excel实际上使用了另一种技术，但是以下是考虑近似匹配如何工作的简单方法：
Excel沿第一列向下移动，直到找到大于查找值的值。然后，它备份一行以返回其结果。如果查找值大于列中的最后一个数字，则它将提供最后一个结果。
什么时候应该使用近似匹配？
只有当您确实需要近似匹配时，才应该使用近似匹配。如果需要精确匹配，并且想要精确结果，则切勿使用近似匹配。
默认情况下，VLOOKUP返回一个近似匹配。这是不幸的，有两个原因。首先，以我的经验，大多数Excel用户在大多数情况下都需要完全匹配。其次，近似匹配是有风险的，因为它们可能返回错误的结果。
该图显示了需要近似匹配的典型情况。
该图显示了数量折扣的时间表。如果购买数量在1-4个之间，则不提供折扣。购买5到24个单位可获得5％的折扣，依此类推。购买200个或更多单位可获得20％的折扣。
近似匹配功能使我们可以查找任何大小订单的折扣。例如，单元格E4显示，购买7个单位的折扣为5％。
这是计算公式：
E4：= VLOOKUP（E $ 3，$ A $ 3：$ B $ 7,2，TRUE）
（最后一个参数是可选的range_lookup值。由于该参数的默认值为TRUE，因此可以省略。）
上图中的单元格E7中的公式相似。它显示了VLOOKUP对于大值可以按预期工作。也就是说，任何超过最大数量的购买都将获得最大数量的折扣。
请注意，图中的单位是按升序排序的。这是一个绝对的要求。如果您的数据未排序，则可能得到错误的结果。
如何从Excel查找功能获取错误结果
使用近似匹配时，必须对数据进行排序。对于所有返回近似匹配项的Excel查找函数，都是如此。
达到此要求的原因是，Excel不仅会沿一列或跨行进行近似匹配。而是，Excel使用二进制搜索技术。即，Excel检查列表的中间。如果该值小于查找值，则跳至下一部分的中间；否则，将跳转到下一部分。如果该值较小，则跳到上一节的中间。它将距离减半，直到找到正确的结果。
此技术比向下搜索一行或一列要快得多，但是如果不对数据进行排序，它可能会产生意外结果。
通常，如果您对未排序的数据进行近似匹配，则Excel查找功能将返回错误值。这是最好的结果，因为它可以提醒您问题所在。
近似匹配，未排序的数据但是不幸的是，当Excel对未排序的数据执行近似匹配时，它会产生错误的结果。例如，此图说明了两种错误的结果。
SKU＃1示例搜索表中未包含的值。示例中的两个公式是：
F3： = VLOOKUP（F $ 2，$ A $ 4：$ C $ 8,2，TRUE）
F4： = VLOOKUP（F $ 2，$ A $ 4：$ C $ 8,3，TRUE）
VLOOKUP函数具有以下参数：
= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）
由于在单元格F3和F4中range_lookup值为TRUE，因此这些公式正在寻找近似匹配项。
从图中可以看出，这两个公式将返回SKU代码A101的结果。这是一个重要的问题，因为Excel返回错误的结果，这很危险。
SKU＃2示例使用类似的公式来搜索表中IS的值。但是，由于未对SKU列进行排序，因此它还会返回SKU代码A101的值，而不是正确的结果。
因此，两个主要结论是显而易见的：
1.仅在有充分理由时才使用近似匹配。
2.确定，当您使用近似匹配时，您的数据将被排序。
如何通过Excel的VLOOKUP和HLOOKUP函数使用完全匹配
VLOOKUP和HLOOKUP的默认行为是返回近似匹配。
要了解为什么Microsoft可能会做出此选择，请考虑至少在20年前编写了Excel的四个查找功能（LOOKUP，VLOOKUP，HLOOKUP和MATCH）。那时，计算机比现在慢得多。那时，使用完全匹配搜索会导致计算明显变慢。
因此，Excel团队将最快的方法（近似匹配方法）设置为默认搜索方法。
但是今天，对于大多数目的而言，精确匹配和近似匹配之间的计算时间没有明显差异。因此，今天，由于至少两个原因，Excel的默认搜索方法很不幸。首先，Excel用户通常比完全匹配更需要完全匹配。其次，近似匹配会产生错误的结果。
让我们看看精确匹配如何与典型数据一起工作……
F2和F3单元格中的公式返回正确的结果。而且由于单元格F6和F7中的公式会查找不存在的数据，因此它们也会返回正确的结果。
两个示例显示正确使用VLOOKUP公式。
AC列显示了一个简单的SKU（库存单位）数据库及其说明和价格。
F列显示了两组示例。
请注意，A列中的SKU数据未排序。使用完全匹配时，排序顺序无关紧要。
SKU＃1示例使用以下公式搜索有关SKU代码B19的信息：
F2： = VLOOKUP（F $ 1，$ A $ 3：$ C $ 7,2，FALSE）
F3： = VLOOKUP（F $ 1，$ A $ 3：$ C $ 7,3，FALSE）
请记住，VLOOKUP函数具有以下参数：
= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）
由于这些公式中的range_lookup值为FALSE，因此它们正在寻找精确匹配。并且由于表中存在SKU代码B19，因此公式可以找到并返回正确的结果。
这真是个好消息……我们得到了正确的结果！
SKU＃2示例提供了更好的消息。以下是其公式：
F6： = VLOOKUP（F $ 5，$ A $ 3：$ C $ 7,2，FALSE）
F7： = VLOOKUP（F $ 5，$ A $ 3：$ C $ 7,3，FALSE）
在这里，当我们搜索不在列表中的数据时会得到错误值。那是更好的消息，因为我们没有收到虚假数据。也就是说，当我们仅使用精确匹配时，如果我们搜索不存在的数据，则可以依靠获得错误值。
VLOOKUP和HLOOKUP的局限性
VLOOKUP公式使用以下语法：
??= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）
让我们使用此函数从这个小数据库中查找一些值。
要在SKU列中查找“ G23”并返回其描述，我们将使用：
= VLOOKUP（“ G23”，$ A $ 3：$ C $ 7,2，FALSE）
要在SKU中查找“ G23”并返回其价格，我们将使用：
= VLOOKUP（“ G23”，$ A $ 3：$ C $ 7,3，FALSE）
要在“项目”列中查找“外套”并返回其价格，我们将使用：
= VLOOKUP（“大衣”，$ B $ 3：$ C $ 7,2，FALSE）
没关系。但是，我们如何使用VLOOKUP在“项目”列中查找“鞋子”并返回其SKU？
我们不能。
原因很简单。VLOOKUP只能在table_array的第一列中查找值，而我们只能从table数组中返回值。因此，如果我们想在一个列中查找一个值，然后将值返回到该列的左侧，那么我们就不走运了。
（不，如果我们尝试对col_index_num使用负值，则将无法使用。）
要返回我们需要的值，您会认为我们可以使用具有以下格式的LOOKUP函数：
= LOOKUP（lookup_value，lookup_vector，result_vector）
问题在于，LOOKUP的lookup_vector列必须按升序排序。因此，如果我们的数据库按Item列排序，我们可以返回其SKU，这是VLOOKUP无法做到的。
但是因为数据没有排序，所以我们很不走运……至少对于LOOKUP，VLOOKUP和HLOOKUP。
不幸的是，这不是这三个功能的唯一*。局限性是为什么自1990年以来我就不再使用这三个函数。相反，我仅使用INDEX-MATCH。
Excel的最佳查找方法：INDEX-MATCH
在Excel中查找数据的最强大，最灵活的方法是INDEX - MATCH方法。它依赖于两个Excel函数：
= INDEX（参考，row_num，column_num）
参考 —单元格范围
row_num —参考中要从中返回数据的行。
column_num-参考中要从中返回数据的列。
如果引用是一行或一列，则 INDEX函数可以使用以下语法：= INDEX（reference，cell_num）
= MATCH（lookup_value，lookup_array，match_type）
lookup_value-在lookup_array中匹配的值。
lookup_array-包含数据的单元格范围。
match_type-指定 Excel如何将lookup_value与lookup_array中的值匹配。对于完全匹配，请始终将此参数使用0。
下图显示了正在使用的INDEX-MATCH方法…
B11： = MATCH（$ A11，$ A $ 3：$ A $ 7,0）
该公式告诉我们，在SKU列的第三行中找到了文本“ G23”。复制到B12的公式表示在第二行中找到“ A101”。
请注意，由于我们需要精确匹配，因此单元格B11中的最后一个参数的值为零。
C11： = INDEX（$ B $ 3：$ B $ 7，$ B11）
单元格C11的此公式返回由单元格B11中的值指定的单元格编号的项目。
D11： = INDEX（$ C $ 3：$ C $ 7，$ B11）
并且该单元格D11的公式返回单元格B11中指定的单元格编号的价格。
当然，我们可以将INDEX和MATCH合并为一个公式，如单元格E11中所示：
E11： = INDEX（$ C $ 3：$ C $ 7，
MATCH（$ A11，$ A $ 3：$ A $ 7,0））
此组合的工作方式与单元格F11中的VLOOKUP函数类似：
F11： = VLOOKUP（$ A11，$ A $ 3：$ C $ 7,3，FALSE）
到目前为止，INDEX-MATCH和VLOOKUP具有相同的功能。但是现在考虑单元格B16和C16中的公式：
B16： = MATCH（$ A16，$ B $ 3：$ B $ 7,0）
C16： = INDEX（$ A $ 3：$ A $ 7，$ B16）
在这两个公式中，我们在“项目”列中查找“领带”，并从SKU列中返回其SKU。如上所述，这是VLOOKUP无法产生的结果。
更常见的是，我们将这些公式组合为一个：
E16： = INDEX（$ C $ 3：$ C $ 7，MATCH（$ A16，$ B $ 3：$ B $ 7,0））
使用此方法时，您会发现INDEX-MATCH方法还有许多其他优点。
如果您没有使用INDEX-MATCH进行查找，则可能会丢失很多功能。试试看。
