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中文網其他相關文章！