當前位置:成語大全網 - 英語詞典 - 快速查找所需的Excel函數-查找Excel的最快方法-Excel學習網

快速查找所需的Excel函數-查找Excel的最快方法-Excel學習網

我在這裏?易變工作簿?報告工作簿在中討論,以測試Excel查找方法的計算時間。此工作簿包含壹個有五列的報表,每列包含1,000行公式。每個公式都從50,000行的數據庫中返回數據。

為了計算時間,我依靠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和索引匹配函數。