# Excel 效能：改善計算效能Excel performance: Improving calculation performance

## 計算速度的重要性The importance of calculation speed

Excel 有兩種計算模式，可讓您控制何時進行計算：Excel has two main calculation modes that let you control when calculation occurs:

• 自動計算 - 每次進行變更時，便會自動重新計算公式。Automatic calculation - Formulas are automatically recalculated when you make a change.

• 手動計算 - 只有在您要求時 (例如按下 F9)，才會重新計算公式。Manual calculation - Formulas are recalculated only when you request it (for example, by pressing F9).

## 認識 Excel 的計算方法Understanding calculation methods in Excel

### 完整計算與重新計算相依性Full calculation and recalculation dependencies

Excel 的智慧重算引擎嘗試縮短計算時間的方法是，持續追蹤每個公式的前導參照和相依性 (公式參照的儲存格)，以及追蹤自從上次計算之後是否有任何變更。The smart recalculation engine in Excel tries to minimize calculation time by continuously tracking both the precedents and dependencies for each formula (the cells referenced by the formula) and any changes that were made since the last calculation. 在下一次重新計算時，Excel 只會重新計算以下項目：At the next recalculation, Excel recalculates only the following:

• 已變更或是標示為需要重新計算的儲存格、公式、值或名稱。Cells, formulas, values, or names that have changed or are flagged as needing recalculation.

• 需要重新計算的公式、名稱、值或與其他儲存格相依的儲存格。Cells dependent on other cells, formulas, names, or values that need recalculation.

• 動態函數和可見的條件式格式。Volatile functions and visible conditional formats.

Excel 會持續計算與先前計算過的儲存格相依之儲存格，即使先前計算過的儲存格值在計算時並未改變。Excel continues calculating cells that depend on previously calculated cells even if the value of the previously calculated cell does not change when it is calculated.

### 計算處理流程Calculation process

Excel 的計算處理流程分為幾個階段：Excel has distinct calculation phases:

1. 建立初始計算鏈並決定計算開始處。Build the initial calculation chain and determine where to begin calculating. 這個階段出現在當活頁簿載入記憶體時。This phase occurs when the workbook is loaded into memory.

2. 追蹤相依性、標記出尚未計算的儲存格，然後更新計算鏈。Track dependencies, flag cells as uncalculated, and update the calculation chain. 即使是在手動計算模式時，每次輸入儲存格項目或儲存格變更時，就會執行這個階段。This phase executes at each cell entry or change, even in manual calculation mode. 通常這個階段的執行速度會快到您無法察覺，但是在複雜的情況下，回應可能會變得緩慢。Ordinarily this executes so fast that you do not notice it, but in complex cases, response can be slow.

3. 計算所有公式。Calculate all formulas. 在計算處理流程中，Excel 會重新調整計算鏈的順序與結構，最佳化未來的重新計算程序。As a part of the calculation process, Excel reorders and restructures the calculation chain to optimize future recalculations.

4. 更新 Excel 視窗的可見部分。Update the visible parts of the Excel windows.

• Excel 通常只會重新計算已變更的儲存格以及其相依參照。Excel usually recalculates only cells that have changed, and their dependents.

• Excel 會儲存最近的計算順序並重複使用，如此一來便可省下大部份用來決定計算順序的時間。Excel stores and reuses the most recent calculation sequence so that it can save most of the time used to determine the calculation sequence.

• Excel 在多核心電腦上執行時，會參考前次計算的結果，嘗試最佳化分散於各核心上進行計算的方式。With multiple core computers, Excel tries to optimize the way the calculations are spread across the cores based on the results of the previous calculation.

• 在 Excel 工作階段期間，Windows 和 Excel 兩者都會將最近使用過的資料與程式存入快取中，以便進行快速存取。In an Excel session, both Windows and Excel cache recently used data and programs for faster access.

## 計算活頁簿、工作表和範圍Calculating workbooks, worksheets, and ranges

### 計算儲存格範圍Calculate a range of cells

Excel 也允許使用 Visual Basic for Applications (VBA) 方法 Range.CalculateRowMajorOrderRange.Calculate，計算儲存格範圍：Excel also allows for the calculation of a range of cells by using the Visual Basic for Applications (VBA) methods Range.CalculateRowMajorOrder and Range.Calculate:

• Range.CalculateRowMajorOrder 計算範圍由左至右由上至下，忽略所有相依性。Range.CalculateRowMajorOrder calculates the range left to right and top to bottom, ignoring all dependencies.

• Range.Calculate 計算範圍，含範圍內解析的所有相依性。Range.Calculate calculates the range resolving all dependencies within the range.

Range.Calculate 是 Excel 中對於效能最佳化最有用的工具之一，因為您可以用於計時並比較不同公式的計算速度。Range.Calculate is one of the most useful tools in Excel for performance optimization because you can use it to time and compare the calculation speed of different formulas.

### 動態動作Volatile actions

• 在自動模式下，按一下列或欄分隔線。Clicking a row or column divider when in automatic mode.
• 在工作表上插入或刪除列、欄或儲存格。Inserting or deleting rows, columns, or cells on a sheet.
• 新增、變更或刪除已定義之名稱。Adding, changing, or deleting defined names.
• 在自動模式下，重新命名工作表或變更工作表的位置。Renaming worksheets or changing worksheet position when in automatic mode.
• 篩選、隱藏或取消隱藏列。Filtering, hiding, or un-hiding rows.
• 在自動模式下，開啟活頁簿。Opening a workbook when in automatic mode. 如果該活頁簿前次使用另一個版本的 Excel 計算，則開啟該活頁簿通常就會進行完整計算。If the workbook was last calculated by a different version of Excel, opening the workbook usually results in a full calculation.
• 在手動模式下，儲存活頁簿 (如果已選取 [存檔前自動計算]**** 選項)。Saving a workbook in manual mode if the Calculate before Save option is selected.

### 公式和名稱的評估情況Formula and name evaluation circumstances

• 輸入或編輯公式。Enter or edit the formula.
• 使用函數精靈輸入或編輯公式。Enter or edit the formula by using the Function Wizard.
• 在函數精靈中，將公式輸入作為引數。Enter the formula as an argument in the Function Wizard.
• 從資料編輯列中選取公式，然後按下 F9 (按下 ESC 取消並還原公式)，或按一下 [評估公式]****。Select the formula in the formula bar and press F9 (press Esc to undo and revert to the formula), or click Evaluate Formula.

• 已輸入。It was entered.
• 已變更。It was changed.
• 列於自動篩選清單中，且已啟用準則下拉式清單。It is in an AutoFilter list, and the criteria drop-down list was enabled.
• 標示為未計算。It is flagged as uncalculated.

• 每次有參照到已定義之名稱的公式進行評估時，已定義之名稱就會進行評估，因此，在多個公式中使用名稱會導致名稱進行許多次評估。A defined name is evaluated every time that a formula that refers to it is evaluated so that using a name in multiple formulas can cause the name to be evaluated multiple times.
• 沒有被任何公式參照到的名稱不會進行計算，即使是完整計算也一樣。Names that are not referred to by any formula are not calculated even by a full calculation.

## 控制計算選項Controlling calculation options

Excel 擁有許多選項可讓您操控計算的方式。Excel has a range of options that enable you to control the way it calculates. 只要使用功能區中 [公式]**** 索引標籤的 [計算]**** 群組，即可變更 Excel 中最常使用的選項。You can change the most frequently used options in Excel by using the Calculation group on the Formulas tab on the Ribbon.

## 讓活頁簿計算得更快Making workbooks calculate faster

### 測量計算時間Measuring calculation time

``````#If VBA7 Then
Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
Private Declare Function getFrequency Lib "kernel32" Alias _                                            "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
Function MicroTimer() As Double
'

' Returns seconds.
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0

' Get frequency.
If cyFrequency = 0 Then getFrequency cyFrequency

' Get ticks.
getTickCount cyTicks1

' Seconds
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function
``````

``````Sub RangeTimer()
DoCalcTimer 1
End Sub
Sub SheetTimer()
DoCalcTimer 2
End Sub
Sub RecalcTimer()
DoCalcTimer 3
End Sub
Sub FullcalcTimer()
DoCalcTimer 4
End Sub

Sub DoCalcTimer(jMethod As Long)
Dim dTime As Double
Dim dOvhd As Double
Dim oRng As Range
Dim oCell As Range
Dim oArrRange As Range
Dim sCalcType As String
Dim lCalcSave As Long
Dim bIterSave As Boolean
'
On Error GoTo Errhandl

' Initialize
dTime = MicroTimer

' Save calculation settings.
lCalcSave = Application.Calculation
bIterSave = Application.Iteration
If Application.Calculation <> xlCalculationManual Then
Application.Calculation = xlCalculationManual
End If
Select Case jMethod
Case 1

' Switch off iteration.

If Application.Iteration <> False Then
Application.Iteration = False
End if

' Max is used range.

If Selection.Count > 1000 Then
Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
Else
Set oRng = Selection
End If

' Include array cells outside selection.

For Each oCell In oRng
If oCell.HasArray Then
If oArrRange Is Nothing Then
Set oArrRange = oCell.CurrentArray
End If
If Intersect(oCell, oArrRange) Is Nothing Then
Set oArrRange = oCell.CurrentArray
Set oRng = Union(oRng, oArrRange)
End If
End If
Next oCell

sCalcType = "Calculate " & CStr(oRng.Count) & _
" Cell(s) in Selected Range: "
Case 2
sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
Case 3
sCalcType = "Recalculate open workbooks: "
Case 4
sCalcType = "Full Calculate open workbooks: "
End Select

' Get start time.
dTime = MicroTimer
Select Case jMethod
Case 1
If Val(Application.Version) >= 12 Then
oRng.CalculateRowMajorOrder
Else
oRng.Calculate
End If
Case 2
ActiveSheet.Calculate
Case 3
Application.Calculate
Case 4
Application.CalculateFull
End Select

' Calculate duration.
dTime = MicroTimer - dTime
On Error GoTo 0

dTime = Round(dTime, 5)
MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
vbOKOnly + vbInformation, "CalcTimer"

Finish:

' Restore calculation settings.
If Application.Calculation <> lCalcSave Then
Application.Calculation = lCalcSave
End If
If Application.Iteration <> bIterSave Then
Application.Iteration = bIterSave
End If
Exit Sub
Errhandl:
On Error GoTo 0
MsgBox "Unable to Calculate " & sCalcType, _
vbOKOnly + vbCritical, "CalcTimer"
GoTo Finish
End Sub
``````

## 找出計算障礙並排定優先順序Finding and prioritizing calculation obstructions

### 找出障礙的循序向下鑽研方法Drill-down approach to finding obstructions

#### 使用循序向下鑽研方法找出障礙To find obstructions using the drill-down approach

1. 請確認只開啟一份活頁簿，沒有其他正在執行中的工作。Ensure that you have only one workbook open and no other tasks are running.

2. 將計算設定為手動。Set calculation to manual.

3. 建立活頁簿的備份副本。Make a backup copy of the workbook.

4. 開啟包含有計算計時器巨集的活頁簿，或是將巨集新增至活頁簿。Open the workbook that contains the Calculation Timers macros, or add them to the workbook.

5. 輪流在每個工作表上按下 CTRL+END，查看使用的範圍。Check the used range by pressing Ctrl+End on each worksheet in turn.

這會顯示最後一個使用的儲存格位置。This shows where the last used cell is. 如果位置出現在您意料之外的地方，請考慮刪除多餘的欄與列，並儲存活頁簿。If this is beyond where you expect it to be, consider deleting the excess columns and rows and saving the workbook. 如需詳細資訊，請參閱 Excel 效能：最佳化效能阻礙的秘訣的＜最小化使用範圍＞一節。For more information, see the "Minimizing the used range" section in Excel performance: Tips for optimizing performance obstructions.

6. 執行 FullCalcTimer 巨集。Run the FullCalcTimer macro.

計算活頁簿中所有公式的時間通常是最差案例的時間。The time to calculate all the formulas in the workbook is usually the worst-case time.

7. 執行 RecalcTimer 巨集。Run the RecalcTimer macro.

完整計算後立即重新計算通常是最佳案例的時間。A recalculation immediately after a full calculation usually gives you the best-case time.

8. 將活頁簿揮發性計算為重新計算時間對完整計算的比例。Calculate workbook volatility as the ratio of recalculation time to full calculation time.

這會測量障礙程度，其中的障礙是動態公式和計算鏈的評估。This measures the extent to which volatile formulas and the evaluation of the calculation chain are obstructions.

9. 啟動工作表並依序執行 SheetTimer 巨集。Activate each sheet and run the SheetTimer macro in turn.

因為只要重新計算活頁簿，您就能得到每個工作表的重新計算時間。Because you just recalculated the workbook, this gives you the recalculate time for each worksheet. 這樣應該可以讓您判斷哪些是問題工作表。This should enable you to determine which ones are the problem worksheets.

10. 在選取的公式區塊上執行 RangeTimer 巨集。Run the RangeTimer macro on selected blocks of formulas.

11. 針對每個問題工作表，將欄或列分成小量區塊。For each problem worksheet, divide the columns or rows into a small number of blocks.

12. 依序選取每個區塊，然後在區塊上執行 RangeTimer 巨集。Select each block in turn, and then run the RangeTimer macro on the block.

13. 如有需要進一步向下鑽研，將每個區塊再細分成更小量區塊。If necessary, drill down further by subdividing each block into a smaller number of blocks.

14. 排列障礙的優先順序。Prioritize the obstructions.

### 加快計算與減少障礙Speeding up calculations and reducing obstructions

#### 規則一：移除相同、重複和不必要的計算First rule: Remove duplicated, repeated, and unnecessary calculations

• 減少每個公式中的參照數量。Reduce the number of references in each formula.

• 將重複的計算移至一或多個輔助儲存格，然後從原始公式參照輔助儲存格。Move the repeated calculations to one or more helper cells, and then reference the helper cells from the original formulas.

• 使用額外的列與欄一次計算和儲存中間結果，因此您可以在其他公式中重複使用這些中間結果。Use additional rows and columns to calculate and store intermediate results once so that you can reuse them in other formulas.

#### 規則二：儘可能使用最有效率的函數Second rule: Use the most efficient function possible

• 在已排序資料上查閱的效率高於在未排序資料查閱千百倍。Lookups on sorted data can be tens or hundreds of times more efficient than lookups on unsorted data.

• VBA 使用者定義函數通常比 Excel 內建函數慢 (雖然某些精心撰寫的 VBA 函數也可能很快)。VBA user-defined functions are usually slower than the built-in functions in Excel (although carefully written VBA functions can be fast).

• SUMSUMIF 等函數中使用的儲存格數量減到最少。Minimize the number of used cells in functions like SUM and SUMIF. 計算時間和使用的儲存格數量成正比 (會忽略未使用的儲存格)。Calculation time is proportional to the number of used cells (unused cells are ignored).

• 請考慮以使用者定義函數取代緩慢的陣列公式。Consider replacing slow array formulas with user-defined functions.

#### 規則三：善用智慧重算和多執行緒計算Third rule: Make good use of smart recalculation and multithreaded calculation

• 儘可能避免使用動態函數，例如 INDIRECTOFFSET，除非這些函數比其他選擇更具效率 Avoid volatile functions such as INDIRECT and OFFSET where you can, unless they are significantly more efficient than the alternatives. (OFFSET 如使用設計得當，通常可以很快)。(Well-designed use of OFFSET is often fast.)

• 將陣列公式和函數中使用的範圍最小化。Minimize the size of the ranges that you are using in array formulas and functions.

• 將陣列公式和巨型公式斷開成個別的輔助欄與列。Break array formulas and mega-formulas out into separate helper columns and rows.

• PHONETICPHONETIC
• 有使用 format 或 address 引數的 CELLCELL when either the "format" or "address" argument is used
• INDIRECTINDIRECT
• GETPIVOTDATAGETPIVOTDATA
• CUBEMEMBERCUBEMEMBER
• CUBEVALUECUBEVALUE
• CUBEMEMBERPROPERTYCUBEMEMBERPROPERTY
• CUBESETCUBESET
• CUBERANKEDMEMBERCUBERANKEDMEMBER
• CUBEKPIMEMBERCUBEKPIMEMBER
• CUBESETCOUNTCUBESETCOUNT
• 已指定第五參數 (sheet_name) 的 ADDRESSADDRESS where the fifth parameter (the sheet_name) is given
• 任何參照到樞紐分析表的資料庫函數 (DSUM、DAVERAGE 等等)Any database function (DSUM, DAVERAGE, and so on) that refers to a pivot table
• ERROR.TYPEERROR.TYPE
• VBA 和 COM 增益集的使用者定義函數VBA and COM add-in user-defined functions
• 避免重複使用運算列表和循環參照：這兩個一律使用單一執行緒進行計算。Avoid iterative use of data tables and circular references: both of these always calculate single-threaded.

#### 規則四：計時並測試每項變更Fourth rule: Time and test each change

1. 使用 RangeTimer 巨集，計時您要變更的公式。Time the formula that you want to change by using the RangeTimer macro.

2. 進行變更。Make the change.

3. 使用 RangeTimer 巨集，計時已變更的公式。Time the changed formula by using the RangeTimer macro.

4. 確認已變更的公式仍會給出正確的答案。Check that the changed formula still gives the correct answer.

### 規則範例Rule examples

#### 日期區間加總Period-to-date sums

``````  B1=SUM(\$A\$1:\$A1)
B2=SUM(\$A\$1:\$A2)
``````

SUM 一共要相加多少儲存格參照？How many cell references are added up by SUM in total? B1 參照到一個儲存格，而 B2000 參照到 2,000 個儲存格。B1 refers to one cell, and B2000 refers to 2,000 cells. 平均每個儲存格有 1000 筆參照，因此總共有 2 百萬筆參照。The average is 1,000 references per cell, so the total number of references is 2 million. 選取這 2,000 個公式並使用 RangeTimer 巨集，可告訴您欄 B 中的 2,000 個公式需花費 80 毫秒來計算。Selecting the 2,000 formulas and using the RangeTimer macro shows you that the 2,000 formulas in column B calculate in 80 milliseconds. 這些計算多半重複了許多次：從 B2:B2000 的每個公式中，SUM 都會將 A1 加到 A2。Most of these calculations are duplicated many times: SUM adds A1 to A2 in each formula from B2:B2000.

``````  C1=A1
C2=C1+A1
``````

RangeTimer 告訴我們，欄 C 中的 2,000 個公式計算時間為 3.7 毫秒，比欄 B 中的 80 毫秒快多了。因此這項變更使得效能只提升了 80/3.7=22 倍，而不是 500 倍，這是因為每個公式都會額外消耗一點時間。RangeTimer indicates that the 2,000 formulas in column C calculate in 3.7 milliseconds compared to the 80 milliseconds for column B. This change has a performance improvement factor of only 80/3.7=22 instead of 500 because there is a small overhead per formula.

#### 錯誤處理Error handling

• 您可以撰寫為單一公式，但是速度緩慢：You can write it as a single formula, which is slow:

`B1=IF(ISERROR(time expensive formula),0,time expensive formula)`

• 您可以撰寫兩個公式，速度較快：You can write it as two formulas, which is fast:

`A1=time expensive formula`

`B1=IF(ISERROR(A1),0,A1)`

• 或者，您可以使用 IFERROR 函數，此函數設計成既快速又簡單，而且還是單一公式：Or you can use the IFERROR function, which is designed to be fast and simple, and is a single formula:

`B1=IFERROR(time expensive formula,0)`

#### 動態計算唯一值Dynamic count unique

• 陣列公式 (使用 CTRL+SHIFT+Enter)；RangeTimer 指出計算時間是 13.8 秒。Array formulas (use Ctrl+Shift+Enter); RangeTimer indicates that this takes 13.8 seconds.

``````{=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}
``````
• SUMPRODUCT 的計算速度通常比同等陣列公式還快。SUMPRODUCT usually calculates faster than an equivalent array formula. 這個公式需花費 10.0 秒，比前者快了 13.8/10.0=1.38 倍，雖然好一點，但還不夠令人滿意。This formula takes 10.0 seconds, and gives an improvement factor of 13.8/10.0=1.38, which is better, but not good enough.

``````=SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&amp;""))
``````
• 使用者定義函數User-defined functions. 以下程式碼範例示範一個 VBA 使用者定義函數，這個函數運用了「指向集合的索引必須具唯一性」這個特點。The following code example shows a VBA user-defined function that uses the fact that the index to a collection must be unique. 如需一些使用到的技巧詳細說明，請參閱 Excel 效能：最佳化效能阻礙的秘訣中＜有效率地使用函數＞一節內的使用者定義函數。For an explanation of some techniques that are used, see the section about user-defined functions in the "Using functions efficiently" section in Excel performance: Tips for optimizing performance obstructions. 這個公式 `=COUNTU(A2:A11000)` 只需要 0.061 秒。This formula, `=COUNTU(A2:A11000)`, takes only 0.061 seconds. 速度改善 13.8/0.061=226 倍。This gives an improvement factor of 13.8/0.061=226.

``````Public Function COUNTU(theRange As Range) As Variant
Dim colUniques As New Collection
Dim vArr As Variant
Dim vCell As Variant
Dim vLcell As Variant
Dim oRng As Range

Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
vArr = oRng
On Error Resume Next
For Each vCell In vArr
If vCell <> vLcell Then
If Len(CStr(vCell)) > 0 Then
End If
End If
vLcell = vCell
Next vCell

COUNTU = colUniques.Count
End Function
``````
• 新增一欄公式Adding a column of formulas. 看看先前的資料範例，您會發現資料已排序完成 (Excel 耗費 0.5 秒排序 11,000 列)。If you look at the previous sample of the data, you can see that it is sorted (Excel takes 0.5 seconds to sort the 11,000 rows). 您可以利用這點，新增一欄可檢查本列資料是否和前一列相同的公式。You can exploit this by adding a column of formulas that checks if the data in this row is the same as the data in the previous row. 如果不同，公式傳回 1。If it is different, the formula returns 1. 否則，傳回 0。Otherwise, it returns 0.

將這個公式新增到儲存格 B2。Add this formula to cell B2.

``````  =IF(AND(A2<>"",A2<>A1),1,0)
``````

複製公式，然後新增加總欄 B 的公式。Copy the formula, and then add a formula to add up column B.

``````  =SUM(B2:B11000)
``````

完整計算所有這些公式需花費 0.027 秒。A full calculation of all these formulas takes 0.027 seconds. 速度改善 13.8/0.027=511 倍。This gives an improvement factor of 13.8/0.027=511.