我在“揮發性工作簿”中討論了報告工作簿，以測試Excel查找方法的計算時間。該工作簿包含一個包含五列的報告，每列包含1,000行公式。每個公式從50,000行數據庫返回數據。為了對計算進行計時，我依靠CalcTimer.xls工作簿，該工作簿在使用Excel VBA測試報告計算時間中進行了討論。我在運行1.60 GHz的舊筆記本電腦上進行了這些測試。您的結果可能會更快，但大約與我的成正比。計算間接費用時間Excel需要花費一定的時間來顯示A列中顯示的隨機選擇的代碼。盡管該時間對于所有測試都是一致的，但它可能會扭曲兩個測試之間的比率。
因此，我首先測試了在不使用任何查找公式的情況下計算25次所需的時間。以下所有“凈計算時間”值均減去以下計算時間。計算時間：0.141秒。使用未排序數據的Excel查找時間VLOOKUP和INDEX-MATCH均可用于未排序的數據。在以下摘要中，我提供了“關鍵公式”。在所有情況下，請根據需要將它們復制到報告的其余區域。因此，讓我們看看這些查找方法是如何執行的。VLOOKUP，未分類的數據
此試用版中的公式依賴于VLOOKUP，其格式如下：= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）range_lookup參數是可選的。如果它的值為FALSE，則VLOOKUP返回一個完全匹配項，可用于未排序的數據。如果為TRUE或省略，則返回近似匹配項，并且必須僅對已排序的數據使用。關鍵公式是：B3：= VLOOKUP（$ A3，Data，B $ 1，FALSE）下面的凈計算時間似乎很長，大約260秒。但是請記住，該報告具有5000個查找公式，我們計算了25次。因此，這125,000次查詢中的每一次僅花費大約.0011秒。凈計算時間：260.781在一式中進行INDEX-MATCH，未分類的數據
此試驗中的公式同時使用INDEX和MATCH：= INDEX（參考，row_num，column_num，area_num）= MATCH（lookup_value，lookup_array，match_type）MATCH的match_type參數是可選的。它可以具有三個值：0：完全匹配。可以用于未排序的數據。1：近似匹配。（默認值。）必須用于按升序排序的數據。-1：近似匹配。必須用于按降序排序的數據。INDEX和MATCH函數可以在一個公式中或在單獨的公式中使用。該測試使用了一個公式：B3：= INDEX（Data，MATCH（$ A3，Code，0），B $ 1）請注意，以下凈計算時間與VLOOKUP大致相同。凈計算時間：253.453兩個公式中的INDEX-MATCH，未排序的數據
與VLOOKUP不同，INDEX-MATCH方法可以分為兩個公式。如圖中所示，當幾個公式需要從數據庫的同一行或同一列返回數據時，此功能非常有用。通過這種設計，一個MATCH公式可以完成較慢的工作，而任何INDEX公式都可以進行較快的工作。這大大加快了計算速度。以下是主要公式：
B3：= INDEX（數據，$ G3，B $ 1）G3：= MATCH（$ A3，代碼，0）在Lotus 1-2-3和Excel的早期，我們被告知，當公式引用公式上方和左側的單元格時，公式將更快地進行計算。因此，如果您是像我這樣的老朋友，您可能想知道，如果將上圖中的“行”列移到“數據1”列的左側，此報告是否可以更快地計算出。我嘗試了該設計，并且計算時間完全沒有受到影響。從下面的計算時間可以看出，該設計代表了未排序數據的最佳實踐。因為我們使用的是前兩種方法使用的查找公式的數量的五分之一，所以我們的計算時間大約是原來的五分之一。凈計算時間：52.234使用排序數據的Excel查找時間當VLOOKUP和MATCH處理排序的數據時，可以將它們設置為使用二進制搜索方法，該方法比上面討論的方法快得多。不幸的是，此方法返回兩個函數的近似匹配。這很不幸，因為根據我的經驗，大多數查找都需要完全匹配。也就是說，如果我們尋找數據中不存在的值，則需要使用公式來返回錯誤值。我們不希望他們返回最佳猜測。幸運的是，有一種簡單的方法可以解決此問題，如下所示。盡管該解決方案的計算時間幾乎增加了一倍，但提高的精度值得不菲的代價。VLOOKUP，排序數據
該試驗的關鍵公式簡短易懂，可以用兩種方式編寫：B3：= VLOOKUP（$ A3，數據，B $ 1）B3：= VLOOKUP（$ A3，數據，B $ 1，TRUE）注意，通過對數據進行排序并使用二進制搜索技術（由range_lookup參數確定），我們將計算時間從大約260秒減少到大約半秒。實際上，搜索時間如此之短，以至于如果我們將查找次數加倍，則只會將總計算時間增加約半秒。這使我們可以自由修改搜索公式，以使我們與排序后的數據完全匹配：B3：= IF（VLOOKUP（$ A3，Data，1）= $ A3，VLOOKUP（$ A3，Data，B $ 1），NA（））在這里，我們首先查找代碼，然后返回找到的代碼。如果返回的代碼等于原始代碼，則我們完全匹配。因此，我們再次查找代碼并返回我們實際想要的值。否則，如果沒有完全匹配，則返回＃N / A。近似匹配的凈計算時間：0.594 完全匹配版本的凈計算時間：0.781用一個公式進行INDEX-MATCH，排序數據
此版本的密鑰公式可以用兩種方式編寫：B3：= INDEX（數據，MATCH（$ A3，代碼，1），B $ 1）B3：= INDEX（數據，MATCH（$ A3，代碼），B $ 1）與VLOOKUP一樣，我們可以修改此公式以提供完全匹配的內容：B3：= IF（INDEX（Code，MATCH（$ A3，Code，1））= $ A3，INDEX（Data，MATCH（$ A3，Code，1），B $ 1），NA（））近似匹配的凈計算時間：0.453 精確匹配版本的凈計算時間：0.688兩個公式中的INDEX-MATCH，已排序的數據
最后，該試驗對INDEX和MATCH使用單獨的公式：B3：= INDEX（數據，$ G3，B $ 1）G3：= MATCH（$ A3，代碼，1）在這里，我們可以修改單元格G3以提供完全匹配：G3：= IF（INDEX（Code，MATCH（$ A3，Code，1））= $ A3，MATCH（$ A3，Code，1），NA（））換句話說，對排序后的數據使用兩種形式的INDEX-MATCH方法可能比使用VLOOKUP或一種形式的INDEX-MATCH技術要快得多，這是最佳實踐。近似匹配的凈計算時間：0.391 精確匹配的凈計算時間：0.438了解查找測試結果這是我總結這些結果的方法：如果要從表的一行返回多個值，請在一個公式中使用MATCH查找該行，然后在其他公式中使用INDEX返回值。這始終是最快的方法。如果您的數據已排序，請使用上面說明的排序數據版本。這樣做可以將計算時間縮短幾個數量級。如果您需要排序數據中的精確匹配，請不要依賴近似匹配。相反，請始終使用雙重查找方法來確保Excel實際上已經找到了您的lookup_value。在最壞的情況下，INDEX-MATCH方法的速度與VLOOKUP差不多。在最好的情況下，速度要快得多。有關VLOOKUP和INDEX-MATCH的更多信息，請參見：Excel的VLOOKUP與INDEX-MATCH函數。
