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

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

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

源數(shù)據(jù)

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

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

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

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

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

我們的示例假設(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。實際上，這並不是那麼好，但是我們稍後再談:)

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

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

• 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，然後我們雙擊右下角的加號，以在整列上複製公式。在雙擊時刻，我們進(jìn)行了秒錶，並看到此計算需要6.6秒。

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

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

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

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

vlookup限制

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

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

計算時間：範(fàn)圍-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àn)圍，以確保公式正確複製到下面的單元格。

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

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

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

索引匹配優(yōu)勢

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

計算時間：範(fà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，因為我們在第一列中搜索，因此不需要偏移。返回列偏移量（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位中，結(jié)果並不令人印象深刻 - 大約7.5秒。為什麼？對Microsoft傢伙的好問題:)

偏移匹配優(yōu)勢

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

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

偏移匹配缺陷

複雜的語法。

計算時間：範(fàn)圍-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慢的兩倍。

計算時間：範(fàn)圍-11.2秒；整列-24.1秒。

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

excel表中的vlookup

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

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

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

要創(chuàng)建表參考，請在第一個單元格（C2）中鍵入公式，選擇要引用的單元格和範(fàn)圍，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)。如您所見，與範(fàn)圍相比，計算速度顯著增加。

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

=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，如果您僅將第一個範(fàn)圍（A1：C500001）轉(zhuǎn)換為表，並使用普通的Vlookup公式從查找範(fàn)圍中提取數(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ù)提供了整個查找陣列，而不是單個查找值。因為僅在一個單元格中輸入公式，因此您不必?fù)?dān)心用絕對引用鎖定範(fà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ì)步驟在下面提到的單獨教程中描述。在這裡，我們將評估結(jié)果：

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

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

深入的教程：

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

額外的獎金：合併桌子嚮導(dǎo)

我們的Ultimate Suite的用戶在其Excel工具箱中使用了另一個工具，可以根據(jù)一個共同的列合併兩個表。讓我們看看它與Excel的比較。

要運行合併表嚮導(dǎo)，請單擊“ ABLE”數(shù)據(jù)選項卡上的合併兩個表按鈕。然後，只需遵循嚮導(dǎo)的步驟即可，它將帶您完成整個過程。

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

仔細(xì)查看上面的消息，您可能會注意到並非找到所有匹配項。但是，這並不意味著該工具存在缺陷。它只是讓您知道查找表中的某些項目（查找值）不存在。在這種情況下，vlookup函數(shù)返回＃n/a錯誤，而合併表格嚮導(dǎo)會留下一個單元格空白。

性能：3.2秒

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

摘要和結(jié)論

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

功能	計算速度以秒為單位
	範(fàn)圍	整列	桌子	動態(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)行了一些觀察。也許，它們也對您有用。

• 儘管有所有局限性和缺點，Vlookup的表現(xiàn)也很好，尤其是在動態(tài)陣列的情況下。
• 索引匹配不如預(yù)期。對我來說，這似乎很奇怪，因為它處理了單個列，而不是像Vlookup這樣的表格數(shù)組。
• Xlookup具有許多驚人的功能，但在巨大的數(shù)據(jù)集上比Vlookup和索引匹配慢。希望Microsoft能夠在將來的版本中提高其性能。
• 偏移匹配是Excel 32位最快的匹配。但是由於其複雜的語法，有很大的機(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)文章！