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