為了計算時間,我依靠CalcTimer.xls工作簿,這在使用Excel VBA測試報告計算時間中討論。
我在壹臺運行頻率為1.60 GHz的舊筆記本電腦上做了這些測試。妳的結果可能更快,但和我的大致成正比。
計算開銷時間
Excel需要花費壹些時間來顯示A列中顯示的隨機選擇的代碼..雖然這個時間對於所有測試都是壹致的,但它可能會扭曲兩個測試之間的比率。
因此,我首先測試了在不使用任何查找公式的情況下計算25次所需的時間。以下所有內容?凈計算時間?值減去下面的計算時間。
計算時間:0.141秒。
用Excel求未排序數據的時間。
VLOOKUP和INDEX-MATCH都可以用於無序數據。在下面的總結中,我提供了?關鍵公式?。在所有情況下,根據需要將它們復制到報告的其余部分。
那麽,讓我們看看這些查找方法是如何執行的。
未分類數據
該試用版中的公式依賴於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.7438+0
壹個公式中的索引匹配,未分類數據。
本實驗中的公式同時使用了索引和匹配:
= INDEX(引用,行編號,列編號,區域編號)
= MATCH(查找值,查找數組,匹配類型)
MATCH的MATCH _ type參數是可選的。它可以有三個值:
0:完全匹配。可用於無序數據。
1:近似匹配。(默認值。)必須用於按升序排序的數據。
-1:近似匹配。必須用於按降序排序的數據。
INDEX和MATCH函數可以在壹個公式中使用,也可以在單獨的公式中使用。該測試使用壹個公式:
B3:= INDEX(數據,匹配($ A3,代碼,0),B $ 1)
請註意,以下凈計算時間與VLOOKUP大致相同。
凈計算時間:253.453
兩個公式中的索引匹配,未排序的數據。
與VLOOKUP不同,索引匹配方法可以分為兩個公式。如圖所示,當幾個公式需要從數據庫的同壹行或同壹列返回數據時,這個函數非常有用。通過這種設計,匹配公式可以完成緩慢的工作,而任何指數公式都可以完成快速的工作。這大大加快了計算速度。
以下是主要公式:
B3: = index (data,$ G3,B $ 1)
G3: =匹配($ a3,代碼,0)
在Lotus 1-2-3和Excel的早期,我們被告知,當公式引用公式上方和左側的單元格時,計算速度會更快。所以,如果妳是我這樣的老朋友,妳可能想知道,如果妳放上圖?好嗎?將列移動到?數據1?在列的左側,是否可以更快地計算此報告。我嘗試了這個設計,計算時間完全不受影響。
從下面的計算時間可以看出,這種設計代表了未排序數據的最佳實踐。因為我們使用的搜索公式數量是前兩種方法的五分之壹,所以我們的計算時間大約是原來的五分之壹。
凈計算時間:52.234
用Excel求排序數據的時間。
當VLOOKUP和MATCH處理排序後的數據時,它們可以被設置為使用二分搜索法方法,這比上面討論的方法要快得多。
不幸的是,這個方法返回兩個函數之間的近似匹配。這很不幸,因為根據我的經驗,大多數搜索都需要精確匹配。換句話說,如果我們尋找壹個數據中不存在的值,我們需要使用壹個公式返回錯誤的值。我們不希望他們回到最佳猜測。
幸運的是,有壹個簡單的方法可以解決這個問題,如下所示。雖然這種解決方案的計算時間幾乎增加了壹倍,但提高的精度值得付出高昂的代價。
VLOOKUP,排序數據
這個測試的關鍵公式簡短易懂,可以用兩種方式書寫:
B3: = vlookup ($ a3,data,B $ 1)
B3: = vlookup ($ a3,data,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。
使用公式進行索引匹配,對數據進行排序。
這個版本的關鍵公式有兩種寫法:
B3: = index(數據,匹配($ A3,代碼,1),B $ 1)。
B3: = index(數據,匹配($ 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
兩個公式中的索引匹配,排序數據。
最後,實驗對索引和匹配使用不同的公式:
B3: = index (data,$ G3,B $ 1)
G3: =匹配($ a3,代碼,1)
這裏,我們可以修改單元格G3以提供精確匹配:
G3:= IF(INDEX(Code,MATCH($ A3,Code,1))= $ A3,MATCH($ A3,Code,1),NA())
換句話說,對排序後的數據使用兩種形式的索引匹配方法是最佳實踐,這可能比使用VLOOKUP或壹種形式的索引匹配技術要快得多。
近似匹配
凈計算時間:0.391精確匹配的凈計算時間:0.438
了解如何查找測試結果
這是我總結這些結果的方式:
如果要從表中的壹行返回多個值,請使用MATCH在壹個公式中查找該行,然後使用INDEX在其他公式中返回值。這總是最快的方法。
如果您的數據已排序,請使用上述排序數據版本。這樣做可以將計算時間縮短幾個數量級。
如果您需要對數據中的精確匹配進行排序,不要依賴近似匹配。相反,請始終使用雙重查找方法來確保Excel確實找到了您的lookup_value。
在最壞的情況下,索引匹配方法幾乎和VLOOKUP壹樣快。最好的情況下,速度快很多。
有關VLOOKUP和索引匹配的詳情,請參閱:Excel的VLOOKUP和索引匹配函數。