今天，我們將使用幾種不同的公式在Excel中進(jìn)行vlookup，測量其計算速度并評估一致性，以便您可以選擇獲勝者。

在不同表中找到信息是Excel中最常見的任務(wù)之一。遺憾的是，在處理能力和靈活性方面，經(jīng)典的Vlookup功能是臭名昭著的。難怪多年來，Excel用戶提出了自己的解決方案，例如索引匹配公式。幸運(yùn)的是，微軟終于意識到Vlookup有太多的弱點(diǎn)，并發(fā)布了更強(qiáng)大的繼任者Xlookup功能。那么，哪一個最適合使用？

如果您的桌子只有幾十行，那么很可能有任何方法都足夠快，您不會注意到差異。但是，如果以數(shù)千分的數(shù)量測量行的數(shù)量，則該功能的正確選擇至關(guān)重要 - 性能的差異可能超過10倍！因此，讓我們開始計時器，看看誰是最快的:)

源數(shù)據(jù)

在所有示例中，我們都使用以下數(shù)據(jù)集：

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

我們的目標(biāo)是匹配項(xiàng)目名稱，并將價格從查找表中提取到主桌上。

對于每種方法，我們將輸入C2中的公式，并通過C500001將其降低，以衡量計算50萬單元格所需的時間。

自然，結(jié)果將取決于許多因素，例如您的CPU性能，RAM大小，Excel版本等。在本實(shí)驗(yàn)中，重要的不是絕對數(shù)字。比較了解每個公式的性能，它們的優(yōu)勢和缺點(diǎn)更為重要。

我們的示例假設(shè)您對功能具有基本知識，并且我們不會對其語法進(jìn)行太多的態(tài)度。為了您的方便，包括指向深度教程的鏈接。

所有測試均在我的Dell筆記本電腦（Intel Core i5-8250U; RAM 16 GB）上進(jìn)行，并使用Microsoft 365應(yīng)用程序進(jìn)行業(yè)務(wù)； Excel 32位，2011年版，Build 13415，Beta頻道。我的同事在Excel 64位上也進(jìn)行了相同的測試，一些結(jié)果截然不同！

vlookup公式

在查找和檢索Excel中的匹配數(shù)據(jù)時，想到的第一個功能是舊的Vlookup。實(shí)際上，這并不是那么好，但是我們稍后再談:)

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

要將價格從查找表（E2：F501）提取到主表（A2：B500001），我們?yōu)槲覀兊腣lookup公式定義了以下論點(diǎn)：

• Lookup_value ：B2-在查找表中搜索的值。
• table_array ：$ e $ 3：$ f $ 501-請注意，我們用$符號鎖定參考文獻(xiàn)，以便它們在復(fù)制公式時不會移動。
• col_index_num ：2-數(shù)據(jù)將從查找表的²列中檢索。
• range_lookup ：false-查找確切的匹配。

完整的公式是：

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

上面的公式轉(zhuǎn)到C2，然后我們雙擊右下角的加號，以在整列上復(fù)制公式。在雙擊時刻，我們進(jìn)行了秒表，并看到此計算需要6.6秒。

在實(shí)際工作表中進(jìn)行Vookup時，許多人為Table_array提供了整個列，以適應(yīng)未來可能的添加：

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

它會影響性能嗎？是的，確實(shí)如此。計算整列需要14.2秒。這很難相信，所以我進(jìn)行了仔細(xì)檢查。相同的結(jié)果 - 不到范圍的速度一半。

除了迅速，您的配方還需要堅固耐用，對嗎？遺憾的是，Vlookup無法夸耀可靠性和彈性。

vlookup限制

如前所述，Excel Vlookup具有許多令人討厭的約束。最重要的是：

• 看不到它的左邊。 Vlookup函數(shù)只能在表數(shù)組的最左邊列中查看，并從右側(cè)返回信息。
• 無法在色譜柱插入或缺失中幸存下來。由于返回列被指定為索引號，因此一旦將新列添加到或從表數(shù)組中刪除后，vlookup公式就會停止工作。
• 查找值限制為255個字符。

計算時間：范圍-6.6秒；整列-14.2秒。

深度教程：初學(xué)者的Excel Vlookup示例

索引匹配公式

對于許多用戶而言，Excel中的高級查找形式是神奇的索引匹配公式。以通用形式，看起來像這樣：

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

對于我們的數(shù)據(jù)集，公式采用此表格：

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

與vlookup一樣，請記住使用絕對參考進(jìn)行查找和返回范圍，以確保公式正確復(fù)制到下面的單元格。

在同一張紙上兩張表的情況下，索引匹配的執(zhí)行速度比Vlookup慢得多（在6.6秒中為8.9）。

但是，如果我們將查找表移至另一個工作表，則該公式開始工作得更快（?5秒），這比Vlookup更好。

無論如何，索引匹配構(gòu)成了很多重要的好處。

索引匹配優(yōu)勢

• 可以從右到左看。是的，一個索引匹配公式不在乎查找列的位置，因?yàn)榕cVlookup不同，它明確定義了查找范圍而不是表數(shù)組。
• 免疫柱插入和刪除。使用索引匹配，您可以安全地添加和刪除列，因?yàn)槟付朔祷胤秶?，而不是索引號?/li>
• 查找值的大小沒有限制。雖然Vlookup限制為255個字符，但索引匹配在處理更長的字符串方面沒有問題。
• 可以使用上面鏈接的示例中的多個標(biāo)準(zhǔn)執(zhí)行vlookup。
• 可以進(jìn)行二維查找，并在特定行和列的交點(diǎn)處返回值。

計算時間：范圍-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-是返回列偏移量，它確定要移動到返回列的列數(shù)量。

在我們的情況下，查找列偏移量（n）為0，因?yàn)槲覀冊诘谝涣兄兴阉?，因此不需要偏移。返回列偏移量（M）為1，因?yàn)槠ヅ漤?xiàng)在第二列中，我們需要向右移動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位中，結(jié)果并不令人印象深刻 - 大約7.5秒。為什么？對Microsoft家伙的好問題:)

偏移匹配優(yōu)勢

除了速度外，該公式還有其他一些優(yōu)點(diǎn)：

• 可以執(zhí)行左側(cè)的Vlookup和上部Hlookup。
• 可以根據(jù)列和行值進(jìn)行雙向查找。
• 當(dāng)插入或從查找表中刪除列時，不會破裂。

偏移匹配缺陷

復(fù)雜的語法。

計算時間：范圍-2.9秒；整列-3.5秒。

深入的教程：

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

Xlookup公式

Microsoft 365訂戶提供了一個新的功能更強(qiáng)大的功能，可以在其工作表中查找信息：

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

出于我們的目的，最后3個參數(shù)的默認(rèn)值正常工作，因此我們僅指定所需的前3個參數(shù)。參數(shù)的名稱是直觀的，我相信您可以理解該公式而沒有其他解釋：

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

Xlookup優(yōu)勢

與傳統(tǒng)的Vlookup相比，Xlookup功能具有許多改進(jìn)，例如：

• 簡化，更有意義的語法
• 能夠在任何方向上垂直和水平查找：右，左，底部或向上。
• 對于排序數(shù)據(jù)，它具有特殊的二進(jìn)制搜索模式，該模式比常規(guī)搜索快得多。
• 以相反的順序搜索以獲取最后一次發(fā)生。
• 能夠返回多個值的能力。
• 處理多個條件，如具有多個標(biāo)準(zhǔn)的Excel Xlookup中所述。
• IN構(gòu)建如果錯誤功能。

Xlookup缺陷

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

現(xiàn)在，讓我們看看這個新功能有多迅速。 11.2秒 - 非常令人失望:(

如果我們使用列參考而不是范圍怎么辦？ =XLOOKUP(B2, E:E, F:F)

24.5秒。沒有言語……幾乎是Vlookup慢的兩倍。

計算時間：范圍-11.2秒；整列-24.1秒。

綜合教程：Excel Xlookup功能與示例

excel表中的vlookup

您可能知道，可以通過使用表和列名代替單元格地址來引用Excel表中的數(shù)據(jù)。這稱為結(jié)構(gòu)化參考，我想知道它是否對計算速度有任何影響。

要檢查一下，讓我們將范圍轉(zhuǎn)換為表并重新測試我們的公式。

為方便起見，我們的表被命名為main_table （A1：C500001）和Lookup_table （E1：F5001）。

要創(chuàng)建表參考，請在第一個單元格（C2）中鍵入公式，選擇要引用的單元格和范圍，Excel將自動插入結(jié)構(gòu)化的引用。

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

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

Excel表的一個重要特征是，一旦您進(jìn)入一個單元格中的公式，它就會立即在同一列中的所有其他單元格中填充。此外，表本質(zhì)上是動態(tài)的，并且會自動擴(kuò)展，以包括您在表旁邊輸入的任何新數(shù)據(jù)。

在我們的表中，Vookup公式在2.3秒內(nèi)計算出，索引在2.6秒內(nèi)匹配，在2.7秒內(nèi)取消和匹配，以及Xlookup在3.3秒內(nèi)。如您所見，與范圍相比，計算速度顯著增加。

該公式在下面列出以供您參考：

=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])

有趣的是，即使有常規(guī)參考， Excel表也非?？臁Ｒ簿褪钦f，如果您僅將第一個范圍（A1：C500001）轉(zhuǎn)換為表，并使用普通的Vlookup公式從查找范圍中提取數(shù)據(jù)，則主表中的整列將在大約2.5秒內(nèi)計算出來！

計算速度：根據(jù)公式從2.3到3.3秒。

端到端教程：

• 帶有示例的excel表
• Excel表中的結(jié)構(gòu)化參考

帶有動態(tài)陣列的vlookup

2020年1月發(fā)生的Excel 365計算引擎的開創(chuàng)性變化增加了對所謂動態(tài)陣列的支持。簡而言之，這些是可重大的陣列，它們根據(jù)在一個單元中輸入的公式自動計算并返回多個單元格。

關(guān)于動態(tài)陣列的最好的事情之一是，它們幾乎可以與任何傳統(tǒng)的Excel功能一起使用。對于我們的vlookup公式，看起來像這樣：

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

與經(jīng)典的Vlookup函數(shù)的不同之處在于，您為第一個參數(shù)提供了整個查找陣列，而不是單個查找值。因?yàn)閮H在一個單元格中輸入公式，因此您不必?fù)?dān)心用絕對引用鎖定范圍。

至于性能，動態(tài)陣列的工作速度甚至比Excel表更快！幾乎立即填充了一百萬個細(xì)胞：1.8秒 - 非常令人印象深刻！

其他結(jié)果如下列出：

索引匹配 - 4.4秒

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

Xlookup -7.3秒

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

嗯…Xlookup應(yīng)該通過設(shè)計動態(tài)的Xlookup比舊功能差。詭異的！

計算速度：從1.8到7.3秒，具體取決于公式。

深度教程：Excel動態(tài)陣列，功能和公式

用電源查詢拉比賽

為了完整，讓我們對我們的任務(wù) - 功率查詢進(jìn)行更多可能的解決方案。當(dāng)然，將公式的計算與更新查詢進(jìn)行比較并不是很正確的，但是我只是好奇它更快:)

使用電源查詢的詳細(xì)步驟在下面提到的單獨(dú)教程中描述。在這里，我們將評估結(jié)果：

合并的表已在8.5秒內(nèi)從電源查詢編輯器加載到Excel中。與公式不同，查詢不會自動更新。在源數(shù)據(jù)中進(jìn)行每次更改之后，您必須通過單擊數(shù)據(jù)或查詢選項(xiàng)卡上的“刷新”按鈕手動更新結(jié)果表。我們的500,000行在大約7秒內(nèi)刷新。還不錯，但是Excel公式可以做得更好?？紤]到設(shè)置查詢遠(yuǎn)非是一個單擊的過程，這可能是我要使用的最后一種方法，只有在沒有其他作用的情況下。

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

深入的教程：

• 如何將桌子與Excel Power查詢相結(jié)合
• 如何在Excel中使用電源查詢 - 實(shí)際示例
• 初學(xué)者的Excel教程中的Power查詢

好吧，該工具花了大約3秒鐘才能完成。對于半百萬唱片來說還不錯！

仔細(xì)查看上面的消息，您可能會注意到并非找到所有匹配項(xiàng)。但是，這并不意味著該工具存在缺陷。它只是讓您知道查找表中的某些項(xiàng)目（查找值）不存在。在這種情況下，vlookup函數(shù)返回＃n/a錯誤，而合并表格向?qū)粝乱粋€單元格空白。

性能：3.2秒

更多信息：在Excel中合并兩個表

摘要和結(jié)論

如果您仔細(xì)閱讀所有示例，那么您很可能已經(jīng)得出了自己的結(jié)論。如果您跳過了詳細(xì)信息，則可以在此比較表中找到一個快速摘要：

功能	計算速度以秒為單位
	范圍	整列	桌子	動態(tài)數(shù)組
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

下面，我根據(jù)測試結(jié)果進(jìn)行了一些觀察。也許，它們也對您有用。

• 盡管有所有局限性和缺點(diǎn)，Vlookup的表現(xiàn)也很好，尤其是在動態(tài)陣列的情況下。
• 索引匹配不如預(yù)期。對我來說，這似乎很奇怪，因?yàn)樗幚砹藛蝹€列，而不是像Vlookup這樣的表格數(shù)組。
• Xlookup具有許多驚人的功能，但在巨大的數(shù)據(jù)集上比Vlookup和索引匹配慢。希望Microsoft能夠在將來的版本中提高其性能。
• 偏移匹配是Excel 32位最快的匹配。但是由于其復(fù)雜的語法，有很大的機(jī)會犯錯。另外，它與動態(tài)陣列無法使用，至少我無法強(qiáng)迫它。
• 除非絕對必要，否則沒有任何意義來計算整個列。這使得公式慢了兩次。
• Excel Tables Rock！為了充分利用您的Excel，請盡可能使用它們。
• 動態(tài)陣列是未來。

請記住，這些觀察結(jié)果是基于我在Dynamic Excel 365中的測試，我沒有機(jī)會在其他版本中進(jìn)行測試。如果您這樣做，歡迎您的評論，將不勝感激！

練習(xí)工作簿下載

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

以上是excel中的vlookup-哪個公式最快？的詳細(xì)內(nèi)容。更多信息請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！