提高Excel的運算效率 - 李高成 - 職業日誌 - 價值中國網：中國領先的財經商業新媒...

l 計算次序

excel並不是按行列或固定的方式來計算的，相反，它是根據關係鏈來動態地決定其計算順序。

計算次序分為兩步：1.創建關係樹（鏈），並標記「未計算」單元格；2.確定關係鏈的計算順序，計算公式，並記住計算順序，以便下次重算。

關於關係樹（鏈）：

如果一個單元格的計算結果要依賴於另一單元格，則稱二者有從屬關係。

舉例來說，A1=A2+5, A2=A3+1, A3=2 顯然計算次序應是：A3—A2—A1，我們稱A3向前引用A2，A2向前引用A1。（反之，也可說A1向後引用A2……，也稱父關係、子關係，A3的父關係是A2，A2的子關係是A3)

單元格或名稱改變後，即使重算方式設定為手動，excel也將重新創建關係鏈（或稱關係樹）。如果關係樹都是在本工作表內的，其速度將大大快於跨工作表關係樹的創建。不過在這方面excel2002比2000和97版有重大改進。

EXCEL在自動重算時，並不是對全部單元格進行計算，而是進行「聰明」的計算，即只選擇對那些需要進行重算的單元格、公式、名稱進行計算（包括剛剛輸入或更改的單元格及其關係樹上的所有單元格）。

但以下有例外：

1. 2000以上版本打開時默認為自動重算，但當打開低版本的工作表時，會進行全部重算。

2. 易失函數全部重算

3. 按下「全部重算」鍵，強制重算（ctrl＋Alt＋F9）

4. 如果關係鏈上的單元格大於65536

如果大量使用OFFSET, INDEX, LOOKUP之類的函數，且其引用範圍較大，可能會導致關係鏈增大。

5. 如果在代碼中使用了worksheet.enablecalculation

6. 沒有使用的名稱不會計算

7. 如果名稱所引用的單元格重算了，名稱也會重算

l 如何控制計算

點擊菜單－工具－選項－重新計算，我們看到了幾種重算的方式。

需要注意的是：這裡設置的選項，有些會使得以後打開的工作簿都具有相同的選項，見下面的說明（「關於設置的級別」）。

－自動重算：就是當單元格有變化時，自動進行所有工作表的計算（包括打開工作簿時）。如果是用不同版本EXCEL保存的工作簿，打開時會重算，因為它們的計算引擎是不同的。

－手工重算（F9或Ctrl＋Alt＋F9）：只有按下鍵時才進行計算，或保存時重算（如果勾選）。當excel認為需要重算時，會在狀態欄出現「計算」的字樣。

－除模擬運算表外自動重算：

全部計算是指對全部公式進行計算（按Ctrl＋Alt＋F9），重算是指對所有的單元格、名稱、易失函數公式及其從屬單元格的新建、改變後的計算（按F9）。通常情況下，重算要比全部計算快。在excel2002版本中，按Ctrl-Alt-Shift-F9可以進行帶重建關係樹的全部計算。

――關於設置的級別

－應用級設置：包括重算方式的設置、迭代方式等。他們都將影響後續打開的工作簿。這些設置保存在每一個工作簿中。儘管如此，還是要注意：所有後續打開的工作簿的此類設置均服從最先打開的工作簿的設置，而忽略本身的原設置。

－工作簿級設置：這些設置為工作簿級設置，只對本工作簿起作用，而對同時打開的其他工作簿不起作用。包括：遠程更新引用；以顯示值為準；1904日期系統；保存外部鏈接；接受公式標誌

l 易失函數

見 ： http://club.excelhome.net/dispbbs.asp?BoardID=3&ID=108514

在該帖中，我們已經知道，OFFSET, INDIRECT, TODAY, NOW, RAND,CELL, INFO均屬於易失函數；而根據測試結果，微軟公布的易失函數ROWS, COLUMNS, AREAS, WEEKNUM卻並不具有易失性；至於INDEX，在97版之後，就不再是易失函數。

除此之外，還有一些操作屬於易失性操作，這些操作也都會觸發自動重算。包括：

－自動篩選：進行自動篩選時，會把篩選區域的所有公式標誌為「未計算」；

－單擊或雙擊改變行高、列寬： 在自動計算的模式下，單擊或雙擊行列分割線改變行高、列寬的，將引發重新計算。但手動改變行高列寬不會引發重算。在「手工重算」模式下，也不會標記工作表為「未計算」

－插入或刪除行、列、單元格。同時，那些引用了本工作表或其他工作表的名稱以及引用其他工作表或被其他工作表引用的公式，也會被標誌為「未計算」。

－增加名稱定義，或改變、刪除已定義的名稱

－工作表改名或改變工作表的位置【註：這一點可能在xp以上版本不是問題？】

－此外，在Excel2003版中，隱藏行，或取消隱藏行。這是因為excel2003中的subtotal函數的新特性所致。但對列的隱藏或取消隱藏不屬於易失操作。

l 查找函數

查詢函數在工作表中經常被大量使用。所以探討他們的效率問題具有重大意義。

我們已經知道，關於查找函數幫助中說到：

MATCH(lookup_value, lookup_array, match_type)

Match_type=1 默認選項，數組升序排列。返回等於或小於查找值的最大數值

Match_type=0 返回精確匹配

Match_type=-1 數組降序排列，返回等於或大於查找值的最小數值

VLOOKUP(lookup_value, table_array, colnum, range_lookup)

Range_lookup=TRUE 默認選項，數組升序排列。返回等於或小於查找值的最大數值

Range_lookup=FALSE 返回精確匹配

與MATCH+INDEX或OFFSET相比，VLOOKUP的速度略快(約5%），使用內存略少，公式更簡單。但如果MATCH+INDEX用得好，可以比VLOOKUP節省大量的時間。

以下兩公式是等價的：

VLOOKUP(A1, Data!$A$2:$F$1000,3,False)INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)

要提高查詢速度，建議：

1. 只要有可能，先對數據區排序

2. 盡量減少查找區域的大小。或者可採用動態區域名稱

3. 盡量使用排過序的數據加近似匹配，而不要使用未排序加精確匹配，前者的速度比後者要快得多，而且與查找區域的大小關係不大。

4. 如果已經排序，但不知道被查數值是否在表中，因而無法使用近似匹配，則可使用以下公式：

IF(lookup_val=Index(lookup_array,MATCH(lookup_val,lookup_list),1) ,Index(lookup_array,MATCH(lookup_val,lookup_array), colnum),「notexist」)

在大範圍的數據查找中，兩個近似匹配也比一個精確匹配要快！

如果數據未排序，且當被查找數據不在範圍內時，會出現#/N/A的提示，為避免此問題，通常採用ISNA()函數進行處理，如：IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,

VLOOKUP (lookupval,table,2, FALSE))但這是一個影響速度的方法。因為它要進行兩次查找。可以採用輔助單元格的辦法來解決：

如，在A1中輸入：=MATCH(lookupvalue,lookuparray,0)，

而在B1中輸入：=IF(ISNA(A1),0,INDEX(table,A1,colnum))

如果不想使用輔助單元格，也可用以下公式，速度也不差：

IF (COUNTIF(lookuparray,lookupvalue)=0, 0, VLOOKUP(lookupval, table, 2 FALSE))

從多列中返回精確匹配：

使用一個輔助列來儲存MATCH的結果(stored_row)，

對每一列使用公式INDEX(Lookup_Range,stored_row,column_number)

也可使用數組公式：{VLOOKUP(lookupvalue,Lookup_Range,{4,2},FALSE)}

行列雙向查詢：

使用一個INDEX加兩個MATCH來解決，如下例：

INDEX($B$2:$Z$1000,MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

三維查詢：

一個辦法是使用函數CHOOSE()，不過只適用於表格數量不多的情況：

INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3,TableName4),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

另一個辦法是：

INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"), MATCH(RowLookup_ Value, $A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

也可以：

INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

還有一個辦法是，不使用那麼多的工作表，而是把他們都集中到一個大表中。

在MATCH,VLOOKUP和HLOOKUP中都允許使用通配符?和*

l 關於動態查找範圍

把下列公式定義到名稱中，在公式中引用，就會動態地決定查找的範圍，從而避免無謂的大範圍查找：

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)，也可以使用INDIRECT 函數

但這個公式也有點問題。主要是OFFSET是個易失函數，而COUNTA又要計算大量的行。為此可以使用輔助單元格：Counts!z1=COUNTA(Sheet1!$A:$A)而動態範圍=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)

l 求和與條件求和

SUM和SUMIF ：運行時間正比於其數據範圍。所以如果數據區是排過序的，就可以適當地選擇計算區域來減少計算時間。

SUMIF 和COUNTIF可以使用通配符?和*

DSUM：速度很快，但使用不大方便

l 多條件求和

數組公式的一個經常使用的地方就是用SUM數組進行多條件求和。但它通常很慢。

優化的辦法是：

－用SUMPRODUCT代替。

它比SUM數組快約5%－10%，而且還能處理文本和空值。

{=SUM($D$2:$D$10301*$E$2:$E$10301)}

=SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)

=SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)

這三個公式結果相同，但第三個公式比前兩個約快77%

用SUMPRODUCT條件求和，其形式如下：

SUMPRODUCT(--(條件1)，--（條件2)，（求和區域））

其中兩個減號(--)的作用是把條件的邏輯值轉變為數值以便計算，也可用+0和*1代替，但(--)比+0,*1都略快些。

但注意其中的各部分區域範圍必須相同，且不能是同一列。

也可使用下式：

SUMPRODUCT((條件1)*（條件2)*（求和區域））

但這個公式略慢於用逗號分割的公式，而且其求和區域內不能有文本值。

－另一個辦法是用DSUM代替SUM數組公式

如果多條件求和的數組公式不多，也可用DSUM來代替，它也比數組公式略快。但其缺點是必須有單獨的區域來存放條件，所以使用不大方便。

l 數組公式

數組公式的優缺點：

優點：

－簡潔。一個公式就可以代替多行或多列的普通公式

－功能強大。

－可減小文件體積（但不一定減小內存佔用）

－可以有一定的保護作用。多單元格數組公式必須選定全部公式所在單元格才能修改，一定程度上防止了新手誤操作。

缺點：

－可讀性差。新手不易理解。

－許多情況下速度略慢

－參與操作的幾個參數必須具有相同的尺寸，這可能導致一些並不需要計算的單元格也參與計算。特別是在多條件求和的數組公式中。

要想提高數組運行的速度，應：

－避免引用整行，盡量減小引用範圍

－盡量使用動態數據範圍

－避免行列交叉，這會返回一個矩形區域

－可能時，使用SUMPRODUCT來代替數組，他的速度略快於數組

l 工作表和工作簿之間的連接

不同工作表、工作簿之間的關聯會影響速度，應盡量避免，特別是對於較低版本的excel

EXCEL97和EXCEL2000按照工作表和工作簿的字母序列順序進行計算，而excel2002和2003版會將所有打開的工作簿及其全部工作表建成一個關係樹來決定計算的順序。所以其計算速度快得多。