excel中的vlookup-哪个公式最快？

今天，我们将使用几种不同的公式在Excel中进行vlookup，测量其计算速度并评估一致性，以便您可以选择获胜者。

在不同表中找到信息是Excel中最常见的任务之一。遗憾的是，在处理能力和灵活性方面，经典的Vlookup功能是臭名昭著的。难怪多年来，Excel用户提出了自己的解决方案，例如索引匹配公式。幸运的是，微软终于意识到Vlookup有太多的弱点，并发布了更强大的继任者Xlookup功能。那么，哪一个最适合使用？

如果您的桌子只有几十行，那么很可能有任何方法都足够快，您不会注意到差异。但是，如果以数千分的数量测量行的数量，则该功能的正确选择至关重要 - 性能的差异可能超过10倍！因此，让我们开始计时器，看看谁是最快的:)

• 源数据
• Vlookup功能
• 索引匹配公式
• 偏移匹配公式
• Xlookup功能
• excel表中的vlookup
• 带有动态阵列的vlookup
• 用电源查询拉比赛
• 额外的奖金：合并桌子向导
• 摘要和结论

源数据

在所有示例中，我们都使用以下数据集：

• 主表包含500,000行
• 查找表包含500行

我们的目标是匹配项目名称，并将价格从查找表中提取到主桌上。

对于每种方法，我们将输入C2中的公式，并通过C500001将其降低，以衡量计算50万单元格所需的时间。

自然，结果将取决于许多因素，例如您的CPU性能，RAM大小，Excel版本等。在本实验中，重要的不是绝对数字。比较了解每个公式的性能，它们的优势和缺点更为重要。

我们的示例假设您对功能具有基本知识，并且我们不会对其语法进行太多的态度。为了您的方便，包括指向深度教程的链接。

所有测试均在我的Dell笔记本电脑（Intel Core i5-8250U; RAM 16 GB）上进行，并使用Microsoft 365应用程序进行业务； Excel 32位，2011年版，Build 13415，Beta频道。我的同事在Excel 64位上也进行了相同的测试，一些结果截然不同！

vlookup公式

在查找和检索Excel中的匹配数据时，想到的第一个功能是旧的Vlookup。实际上，这并不是那么好，但是我们稍后再谈:)

vlookup（lookup_value，table_array，col_index_num，[range_lookup]）

要将价格从查找表（E2：F501）提取到主表（A2：B500001），我们为我们的Vlookup公式定义了以下论点：

• Lookup_value ：B2-在查找表中搜索的值。
• table_array ：$ e $ 3：$ f $ 501-请注意，我们用$符号锁定参考文献，以便它们在复制公式时不会移动。
• col_index_num ：2-数据将从查找表的²列中检索。
• range_lookup ：false-查找确切的匹配。

完整的公式是：

=VLOOKUP(B2, $E$3:$F$501, 2, FALSE)

上面的公式转到C2，然后我们双击右下角的加号，以在整列上复制公式。在双击时刻，我们进行了秒表，并看到此计算需要6.6秒。

在实际工作表中进行Vookup时，许多人为Table_array提供了整个列，以适应未来可能的添加：

=VLOOKUP(B2, E:F, 2, FALSE)

它会影响性能吗？是的，确实如此。计算整列需要14.2秒。这很难相信，所以我进行了仔细检查。相同的结果 - 不到范围的速度一半。

除了迅速，您的配方还需要坚固耐用，对吗？遗憾的是，Vlookup无法夸耀可靠性和弹性。

vlookup限制

如前所述，Excel Vlookup具有许多令人讨厌的约束。最重要的是：

• 看不到它的左边。 Vlookup函数只能在表数组的最左边列中查看，并从右侧返回信息。
• 无法在色谱柱插入或缺失中幸存下来。由于返回列被指定为索引号，因此一旦将新列添加到或从表数组中删除后，vlookup公式就会停止工作。
• 查找值限制为255个字符。

计算时间：范围-6.6秒；整列-14.2秒。

深度教程：初学者的Excel Vlookup示例

索引匹配公式

对于许多用户而言，Excel中的高级查找形式是神奇的索引匹配公式。以通用形式，看起来像这样：

index（ return_column ，match（ lookup_value ， lookup_column ，0））

对于我们的数据集，公式采用此表格：

=INDEX($F$2:$F$501, MATCH(B2, $E$2:$E$501, 0))

与vlookup一样，请记住使用绝对参考进行查找和返回范围，以确保公式正确复制到下面的单元格。

在同一张纸上两张表的情况下，索引匹配的执行速度比Vlookup慢得多（在6.6秒中为8.9）。

但是，如果我们将查找表移至另一个工作表，则该公式开始工作得更快（〜5秒），这比Vlookup更好。

无论如何，索引匹配构成了很多重要的好处。

索引匹配优势

• 可以从右到左看。是的，一个索引匹配公式不在乎查找列的位置，因为与Vlookup不同，它明确定义了查找范围而不是表数组。
• 免疫柱插入和删除。使用索引匹配，您可以安全地添加和删除列，因为您指定了返回范围，而不是索引号。
• 查找值的大小没有限制。虽然Vlookup限制为255个字符，但索引匹配在处理更长的字符串方面没有问题。
• 可以使用上面链接的示例中的多个标准执行vlookup。
• 可以进行二维查找，并在特定行和列的交点处返回值。

计算时间：范围-8.9秒；整列-17.7秒；从另一张纸-5.2秒。

深度教程：Excel中的索引匹配公式

偏移匹配公式

这是在Excel中垂直查找的另一个公式，这是Vookup的许多局限性：

offset（ lookup_table ，match（ lookup_value ，offset）（ lookup_table ，0， n ，lows（ lookup_table ），1），0），0）-1， m ，1，1）

在哪里：

• n-是查找列偏移量，它指定了从表的开头移动到查找列的多少列。
• M-是返回列偏移量，它确定要移动到返回列的列数量。

在我们的情况下，查找列偏移量（n）为0，因为我们在第一列中搜索，因此不需要偏移。返回列偏移量（M）为1，因为匹配项在第二列中，我们需要向右移动1列以获取它们：

=OFFSET($E$2:$F$501, MATCH(B2, OFFSET($E$2:$F$501, 0, 0, ROWS($E$2:$F$501), 1), 0) -1, 1, 1, 1)

与以前的解决方案相比，公式太麻烦了，对吗？但是，它比Vlookup或索引匹配快得多。计算50万行需要不到3秒！在整个列上，偏移量较慢-3.5秒。

=OFFSET(E:F, MATCH(B2, OFFSET(E:F, 0, 0, ROWS(E:F), 1), 0) -1, 1, 1, 1)

但是，在Excel 64位中，结果并不令人印象深刻 - 大约7.5秒。为什么？对Microsoft家伙的好问题:)

偏移匹配优势

除了速度外，该公式还有其他一些优点：

• 可以执行左侧的Vlookup和上部Hlookup。
• 可以根据列和行值进行双向查找。
• 当插入或从查找表中删除列时，不会破裂。

偏移匹配缺陷

复杂的语法。

计算时间：范围-2.9秒；整列-3.5秒。

深入的教程：

• 用公式示例的偏移功能
• 如何在Excel中使用匹配功能

Xlookup公式

Microsoft 365订户提供了一个新的功能更强大的功能，可以在其工作表中查找信息：

Xlookup（lookup_value，lookup_array，return_array，[match_mode]，[search_mode]，[if_not_found]）

出于我们的目的，最后3个参数的默认值正常工作，因此我们仅指定所需的前3个参数。参数的名称是直观的，我相信您可以理解该公式而没有其他解释：

=XLOOKUP(B2, $E$2:$E$501, $F$2:$F$501)

Xlookup优势

与传统的Vlookup相比，Xlookup功能具有许多改进，例如：

• 简化，更有意义的语法
• 能够在任何方向上垂直和水平查找：右，左，底部或向上。
• 对于排序数据，它具有特殊的二进制搜索模式，该模式比常规搜索快得多。
• 以相反的顺序搜索以获取最后一次发生。
• 能够返回多个值的能力。
• 处理多个条件，如具有多个标准的Excel Xlookup中所述。
• IN构建如果错误功能。

Xlookup缺陷

Xlookup仅在Excel 365和2021中可用。在Excel 2019，Excel 2016及更早版本中，不支持它。

现在，让我们看看这个新功能有多迅速。 11.2秒 - 非常令人失望:(

如果我们使用列参考而不是范围怎么办？ =XLOOKUP(B2, E:E, F:F)

24.5秒。没有言语……几乎是Vlookup慢的两倍。

计算时间：范围-11.2秒；整列-24.1秒。

综合教程：Excel Xlookup功能与示例

excel表中的vlookup

您可能知道，可以通过使用表和列名代替单元格地址来引用Excel表中的数据。这称为结构化参考，我想知道它是否对计算速度有任何影响。

要检查一下，让我们将范围转换为表并重新测试我们的公式。

为方便起见，我们的表被命名为main_table （A1：C500001）和Lookup_table （E1：F5001）。

要创建表参考，请在第一个单元格（C2）中键入公式，选择要引用的单元格和范围，Excel将自动插入结构化的引用。

例如，以下是vlookup公式的样子：

=VLOOKUP([@Item], Lookup_table, 2, FALSE)

Excel表的一个重要特征是，一旦您进入一个单元格中的公式，它就会立即在同一列中的所有其他单元格中填充。此外，表本质上是动态的，并且会自动扩展，以包括您在表旁边输入的任何新数据。

在我们的表中，Vookup公式在2.3秒内计算出，索引在2.6秒内匹配，在2.7秒内取消和匹配，以及Xlookup在3.3秒内。如您所见，与范围相比，计算速度显着增加。

该公式在下面列出以供您参考：

=INDEX(Lookup_table[Price], MATCH([@Item], Lookup_table[Item], 0))

=OFFSET(Lookup_table, MATCH([@Item], OFFSET(Lookup_table, 0, 0, ROWS(Lookup_table), 1), 0) -1, 1, 1, 1)

=XLOOKUP([@Item], Lookup_table[Item], Lookup_table[Price])

有趣的是，即使有常规参考， Excel表也非常快。也就是说，如果您仅将第一个范围（A1：C500001）转换为表，并使用普通的Vlookup公式从查找范围中提取数据，则主表中的整列将在大约2.5秒内计算出来！

计算速度：根据公式从2.3到3.3秒。

端到端教程：

• 带有示例的excel表
• Excel表中的结构化参考

带有动态阵列的vlookup

2020年1月发生的Excel 365计算引擎的开创性变化增加了对所谓动态阵列的支持。简而言之，这些是可重大的阵列，它们根据在一个单元中输入的公式自动计算并返回多个单元格。

关于动态阵列的最好的事情之一是，它们几乎可以与任何传统的Excel功能一起使用。对于我们的vlookup公式，看起来像这样：

=VLOOKUP(B2:B500001, E2:F500001, 2, FALSE)

与经典的Vlookup函数的不同之处在于，您为第一个参数提供了整个查找阵列，而不是单个查找值。因为仅在一个单元格中输入公式，因此您不必担心用绝对引用锁定范围。

至于性能，动态阵列的工作速度甚至比Excel表更快！几乎立即填充了一百万个细胞：1.8秒 - 非常令人印象深刻！

其他结果如下列出：

索引匹配 - 4.4秒

=INDEX(F2:F501, MATCH(B2:B500001, E2:E501, 0))

Xlookup -7.3秒

=XLOOKUP(B2:B500001, E2:E501, F2:F501)

嗯…Xlookup应该通过设计动态的Xlookup比旧功能差。诡异的！

计算速度：从1.8到7.3秒，具体取决于公式。

深度教程：Excel动态阵列，功能和公式

用电源查询拉比赛

为了完整，让我们对我们的任务 - 功率查询进行更多可能的解决方案。当然，将公式的计算与更新查询进行比较并不是很正确的，但是我只是好奇它更快:)

使用电源查询的详细步骤在下面提到的单独教程中描述。在这里，我们将评估结果：

合并的表已在8.5秒内从电源查询编辑器加载到Excel中。与公式不同，查询不会自动更新。在源数据中进行每次更改之后，您必须通过单击数据或查询选项卡上的“刷新”按钮手动更新结果表。我们的500,000行在大约7秒内刷新。还不错，但是Excel公式可以做得更好。考虑到设置查询远非是一个单击的过程，这可能是我要使用的最后一种方法，只有在没有其他作用的情况下。

性能：加载到Excel 8.5秒；刷新7.6秒

深入的教程：

• 如何将桌子与Excel Power查询相结合
• 如何在Excel中使用电源查询 - 实际示例
• 初学者的Excel教程中的Power查询

额外的奖金：合并桌子向导

我们的Ultimate Suite的用户在其Excel工具箱中使用了另一个工具，可以根据一个共同的列合并两个表。让我们看看它与Excel的比较。

要运行合并表向导，请单击“ ABLE”数据选项卡上的合并两个表按钮。然后，只需遵循向导的步骤即可，它将带您完成整个过程。

好吧，该工具花了大约3秒钟才能完成。对于半百万唱片来说还不错！

仔细查看上面的消息，您可能会注意到并非找到所有匹配项。但是，这并不意味着该工具存在缺陷。它只是让您知道查找表中的某些项目（查找值）不存在。在这种情况下，vlookup函数返回＃n/a错误，而合并表格向导会留下一个单元格空白。

性能：3.2秒

更多信息：在Excel中合并两个表

摘要和结论

如果您仔细阅读所有示例，那么您很可能已经得出了自己的结论。如果您跳过了详细信息，则可以在此比较表中找到一个快速摘要：

功能	计算速度以秒为单位
	范围	整列	桌子	动态数组
vlookup	6.6	14.2	2.3	1.8
索引匹配	8.9	17.7	2.6	4.4
偏移匹配	2.9	3.5	2.7	-
xlookup	11.2	24.1	3.3	7.3
电源查询	8.5
合并表	3.2

下面，我根据测试结果进行了一些观察。也许，它们也对您有用。

• 尽管有所有局限性和缺点，Vlookup的表现也很好，尤其是在动态阵列的情况下。
• 索引匹配不如预期。对我来说，这似乎很奇怪，因为它处理了单个列，而不是像Vlookup这样的表格数组。
• Xlookup具有许多惊人的功能，但在巨大的数据集上比Vlookup和索引匹配慢。希望Microsoft能够在将来的版本中提高其性能。
• 偏移匹配是Excel 32位最快的匹配。但是由于其复杂的语法，有很大的机会犯错。另外，它与动态阵列无法使用，至少我无法强迫它。
• 除非绝对必要，否则没有任何意义来计算整个列。这使得公式慢了两次。
• Excel Tables Rock！为了充分利用您的Excel，请尽可能使用它们。
• 动态阵列是未来。

请记住，这些观察结果是基于我在Dynamic Excel 365中的测试，我没有机会在其他版本中进行测试。如果您这样做，欢迎您的评论，将不胜感激！

练习工作簿下载

Excel中的最快vlookup公式（.xlsx文件， 74MB ）

以上是excel中的vlookup-哪个公式最快？的详细内容。更多信息请关注PHP中文网其他相关文章！