Excel 效能：改善計算效能

  • 發行項

適用版本： Excel | Excel 2013 | Excel 2016 | VBA

在 Office Excel 2016，最大欄數可達 16,000 欄，最大列數亦高達 1 百萬列，再加上許多其他限制加大，您可以建立的工作表大小比起舊版 Excel 大上許多。 Excel 中單一工作表可容納的儲存格數是舊版的 1,000 倍以上。

在舊版 Excel 中，許多使用者建立的工作表計算速度緩慢，而大型工作表的計算速度通常又比小型的慢上許多。 隨著 Excel 2007 中所加入的欄列數上限，效能問題就變得更加重要了。 像是排序、篩選等計算與資料處理工作過於緩慢，將導致使用者更難以專心處理手邊的工作，而一旦分心就更容易出錯。

近期的 Excel 版本推出幾項新功能，有助於您因應此大幅增加的容量，例如可同時運用多個處理器進行計算，以及如重新整理、排序和開啟活頁簿等常用資料集作業的能力。 多執行緒計算可實際縮短工作表計算時間。 但真正會影響 Excel 計算速度最重要的因素，仍取決於您工作表的設計與建立方式。

只要針對計算最遲緩的工作表進行修改，即可讓計算速度提升數十、上百甚至千倍。 透過找出工作表中的計算障礙並加測量，進而改善計算效能，您可以加快計算的速度。

計算速度的重要性

計算速度遲緩會降低生產力，且導致使用者出錯機率大增。 當回應時間變長，使用者的專注力和生產力便會隨之下降。

Excel 有兩種計算模式，可讓您控制何時進行計算：

  • 自動計算 - 每次進行變更時，便會自動重新計算公式。

  • 手動計算 - 只有在您要求時 (例如按下 F9)，才會重新計算公式。

計算時間少於十分之一秒時，使用者會感覺系統是立即回應的。 甚至可以在一邊輸入資料的同時，一邊自動計算。

計算時間介於十分之一秒到一秒之間時，即使使用者注意到回應時間有延遲，使用者仍可以成功保持思緒暢通。

倘若計算時間更長 (通常介於 1 到 10 秒之間)，使用者在輸入資料時，就必須切換成手動計算。 使用者出錯的頻率將會增加，且開始覺得煩躁，尤其對於重覆性的工作，要保持流暢的思緒變得十分困難。

計算時間若長達 10 秒以上，使用者會失去耐性，而且通常在等待期間乾脆切換到其他工作。 如果該計算是一系列工作的其中一部分，而使用者因此錯亂失序的話，麻煩就大了。

認識 Excel 的計算方法

若要改善 Excel 的計算效能，您必須先認識兩種可用的計算方法，並學會如何操控。

完整計算與重新計算相依性

Excel 的智慧重算引擎嘗試縮短計算時間的方法是，持續追蹤每個公式的前導參照和相依性 (公式參照的儲存格)，以及追蹤自從上次計算之後是否有任何變更。 在下一次重新計算時，Excel 只會重新計算以下項目：

  • 已變更或是標示為需要重新計算的儲存格、公式、值或名稱。

  • 需要重新計算的公式、名稱、值或與其他儲存格相依的儲存格。

  • 動態函數和可見的條件式格式。

Excel 會持續計算與先前計算過的儲存格相依之儲存格，即使先前計算過的儲存格值在計算時並未改變。

在大多數的情況下，由於您在每次計算時只變更一部分輸入資料或一些公式，因此這個智慧重算只需要完整計算所有公式所需時間的幾分之一時間。

在手動計算模式下，您只要按下 F9 就可以啟動這個智慧重算。 按下 CTRL+ALT+F9 就會強制執行完整計算所有公式，或者您也可以按下 SHIFT+CTRL+ALT+F9，強制完全重建相依性並執行完整計算。

計算處理流程

參照其他儲存格的 Excel 公式可置放於被參照的儲存格之前或之後 (向前參照或向後參照)。 這是因為 Excel 計算儲存格的順序不是固定的，也不是依欄或列加以計算。 相反地，Excel 會依照所有待計算公式的清單 (稱為計算鏈) 以及每個公式的相依性資訊，動態地決定計算順序。

Excel 的計算處理流程分為幾個階段：

  1. 建立初始計算鏈並決定計算開始處。 這個階段出現在當活頁簿載入記憶體時。

  2. 追蹤相依性、標記出尚未計算的儲存格，然後更新計算鏈。 即使是在手動計算模式時，每次輸入儲存格項目或儲存格變更時，就會執行這個階段。 通常這個階段的執行速度會快到您無法察覺，但是在複雜的情況下，回應可能會很慢。

  3. 計算所有公式。 在計算處理流程中，Excel 會重新調整計算鏈的順序與結構，最佳化未來的重新計算程序。

  4. 更新 Excel 視窗的可見部分。

每次計算或重新計算時，都會執行第三個階段。 Excel 會嘗試依計算鏈中公式的順序，計算每個公式，但如果有某個公式，其相依的一或多個公式尚未計算過，則該公式將向後排到計算鏈尾端，稍後再加以計算。 也就是說，在每次重新計算時，同一個公式可能會計算多次。

第二次計算活頁簿的速度通常比首次執行快上許多。 發生的原因有幾個：

  • Excel 通常只會重新計算已變更的儲存格以及其相依參照。

  • Excel 會儲存最近的計算順序並重複使用，如此一來便可省下大部份用來決定計算順序的時間。

  • Excel 在多核心電腦上執行時，會參考前次計算的結果，嘗試最佳化分散於各核心上進行計算的方式。

  • 在 Excel 工作階段期間，Windows 和 Excel 兩者都會將最近使用過的資料與程式存入快取中，以便進行快速存取。

計算活頁簿、工作表和範圍

您可以透過使用不同的 Excel 計算方法，控制要進行計算的項目。

計算所有開啟中活頁簿

每次重新計算和完整計算時，都會計算目前所有開啟中活頁簿，解析活頁簿和工作表本身之內和兩者之間的所有相依關係，然後將所有先前未計算 (已變更) 的儲存格重設為已計算。

計算所選的工作表

您也可以使用 Shift+F9，只重新計算選取的工作表。 這會解析工作表的相依性，並重設所有先前未計算 (已變更) 單元格的計算方式。

在舊版 Excel 中，行為不同，而且在計算完成之後，不會將中途單元格設定為計算結果。 如果使用者定義函式依賴此行為，則應改為將這些函式設為動態函式，如本文的 Volatile 函 式一節所述。

計算儲存格範圍

Excel 也允許使用 Visual Basic for Applications (VBA) 方法 Range.CalculateRowMajorOrder 和 Range.Calculate，計算儲存格範圍：

  • Range.CalculateRowMajorOrder 計算範圍由左至右由上至下，忽略所有相依性。

  • Range.Calculate 計算範圍，含範圍內解析的所有相依性。

由於 CalculateRowMajorOrder 不會解析計算範圍內的任何相依性，所以通常會明顯比 Range.Calculate 快得多。 但是您必須小心使用，因為計算結果和 Range.Calculate 可能不相同。

Range.Calculate 是 Excel 中對於效能最佳化最有用的工具之一，因為您可以用於計時並比較不同公式的計算速度。

如需詳細資訊，請參閱 Excel 效能：改良效能與限制。

動態函數

動態函數在每次重新計算時一律重新計算，即使沒有任何已變更的前導參照也一樣。 使用多個動態函數會減緩每次重新計算的速度，但是對於完整計算則不會有任何差異。 您可以在函數程式碼中加入 Application.Volatile，將使用者定義函數變為動態函數。

在 Excel 中，有些內建函數很明顯是動態函數：RAND()、NOW()、TODAY()。 其他較不明顯的動態函數有：OFFSET()、CELL()、INDIRECT()、INFO()。

有些函數先前被記錄為動態函數，但實際上不是動態函數，這些函數是：INDEX()、ROWS()、COLUMNS()、AREAS()。

動態動作

動態動作是指會觸發重新計算的動作，這些動作包括：

  • 在自動模式下，按一下列或欄分隔線。
  • 在工作表上插入或刪除列、欄或儲存格。
  • 新增、變更或刪除已定義之名稱。
  • 在自動模式下，重新命名工作表或變更工作表的位置。
  • 篩選、隱藏或取消隱藏列。
  • 在自動模式下，開啟活頁簿。 如果該活頁簿前次使用另一個版本的 Excel 計算，則開啟該活頁簿通常就會進行完整計算。
  • 在手動模式下，儲存活頁簿 (如果已選取 [存檔前自動計算] 選項)。

公式和名稱的評估情況

當您執行以下操作時，即使是在手動計算模式下，公式或一部分的公式會立即進行評估 (計算)：

  • 輸入或編輯公式。
  • 使用函數精靈輸入或編輯公式。
  • 在函數精靈中，將公式輸入作為引數。
  • 從資料編輯列中選取公式，然後按下 F9 (按下 ESC 取消並還原公式)，或按一下 [評估公式]。

當公式參照到 (依存於) 的儲存格或公式有下列情況之一時，公式會標示為未計算：

  • 已輸入。
  • 已變更。
  • 其列於自動篩選清單中，且已啟用準則下拉式清單。
  • 其標示為未計算。

當包含有公式的工作表、活頁簿或 Excel 執行個體進行計算或重新計算時，被標示為未計算的公式會進行評估。

導致已定義之名稱進行評估的情況與儲存格內公式的情況不同：

  • 每次有參照到已定義之名稱的公式進行評估時，已定義之名稱就會進行評估，因此，在多個公式中使用名稱會導致名稱進行許多次評估。
  • 沒有被任何公式參照到的名稱不會進行計算，即使是完整計算也一樣。

運算列表

Excel 數據表 (數據索引標籤 >Data Tools 群組 >What-If Analysis>Data Table) 不應與數據表功能混淆 (主標籤>式群組>格式化為數據表，或是插入索引標籤>群組>數據表) 。 Excel 運算列表會針對活頁簿進行多次重新計算，分別由列表中不同的值所驅動。 Excel 首先會以一般方式計算活頁簿。 接著會替換每組欄與列的值，進行單一執行緒的重新計算，最後將結果儲存在運算列表中。

運算列表的重新計算一律只用單一處理器進行。

您可利用運算列表輕鬆計算多種變化，並檢視與比較各組變化的結果。 使用計算選項 [除資料表外，自動重算] 可停止 Excel 在每次計算時自動觸發多重計算，但仍然會計算所有運算列表以外的相依公式。

控制計算選項

Excel 擁有許多選項可讓您操控計算的方式。 只要使用功能區中 [公式] 索引標籤的 [計算] 群組，即可變更 Excel 中最常使用的選項。

圖 1： [公式] 索引標籤上的計算群組

[公式] 索引標籤的 [計算] 選項

若要查看更多 Excel 計算選項，在 [檔案] 索引標籤，按一下 [選項]。 在 [Excel 選項] 對話方塊，按一下 [公式] 索引標籤。

圖 2： [Excel 選項] 中 [公式] 索引標籤的 [計算] 選項

Backstage 檢視中的計算選項

許多計算選項 ([自動]、[除運算列表外，自動重算]、[手動]、[儲存活頁簿前自動重算]) 和反覆運算設定 ([啟用反覆運算]、[最高次數]、[最大誤差]) 是在應用程式層級操作，而不是活頁簿層級 (對所有開啟中活頁簿來說，這些選項都是相同的)。

若要查看進階計算選項，在 [檔案] 索引標籤，按一下 [選項]。 在 [Excel 選項] 對話方塊，按一下 [進階]。 在 [公式] 區段下方，設定計算選項。

圖 3： 進階計算選項

Backstage 檢視中的進階計算選項

當您開啟 Excel 時，或是當 Excel 在沒有開啟任何活頁簿的情況下執行時，初始的計算模式和反覆運算設定，都會採用您開啟的第一個非範本且沒有增益集的活頁簿之設定。 這表示之後開啟的活頁簿中，其中的計算設定將會忽略，但當然您也可以隨時在 Excel 中變更這項設定。 儲存活頁簿時，也會一併將目前的計算設定儲存在活頁簿中。

自動計算

自動計算模式是指每次有變更或每次開啟活頁簿時，Excel 都會自動重新計算所有開啟中活頁簿。 通常當您以自動模式開啟活頁簿而 Excel 進行重新計算時，您不會看見重新計算，這是因為從上次儲存活頁簿至今，未進行任何變更。

若您使用比上次計算該活頁簿時的版本更新的 Excel 開啟活頁簿時 (比方說先用 Excel 2013 再用 Excel 2016)，您就會注意到計算的情形。 由於 Excel 計算引擎的不同，每當 Excel 開啟一個以舊版 Excel 儲存的活頁簿時，就會執行一次完整計算。

手動計算

手動計算模式是指只有當您按下 F9 或 CTRL+ALT+F9 要求時，或當您儲存活頁簿時，Excel 才會重新計算所有開啟中活頁簿。 對於需要耗費較多時間重新計算的活頁簿，您必須將計算設為手動模式，以免每次進行變更時都要等待延遲的時間。

在手動模式下，當活頁簿需要重新計算時，Excel 會在狀態列顯示 [計算] 告知您。 如果您的活頁簿含有循環參照，且已選取反覆運算選項，狀態列也會顯示 [計算]。

反覆運算設定

如果您的活頁簿中含有刻意存在的循環參照，您可以利用反覆運算設定，控制活頁簿重新計算 (反覆運算) 的次數上限以及收斂條件 (最大誤差：何時停止)。 請清空反覆運算方塊，萬一出現意外的循環參照時，Excel 會先警告您，而不會嘗試解決循環參照。

活頁簿的 ForceFullCalculation 屬性

當您將這個活頁簿屬性設定為 True 時，Excel 的智慧重算會關閉，每次重新計算都會重新計算所有開啟中活頁簿的所有公式。 若是一些複雜的活頁簿，耗費在建立和維護智慧重算所需依存性樹狀結構的時間，將遠多於透過智慧重算所節省下的時間。

如果您的活頁簿必須耗費極長的時間才能開啟，或是即使是在手動模式下，進行小幅變更卻要耗費很長的時間，這種情形值得嘗試使用 ForceFullCalculation。

如果活頁簿的 ForceFullCalculation 屬性已設定為 True，狀態列會出現 [計算]。

您可以使用 VBE (ALT+F11)，選取 [專案總管] (Ctrl+R) 的 ThisWorkbook，然後顯示 [屬性視窗] (F4)，控制這個設定。

圖 4： 設定 Workbook.ForceFullCalculation 屬性

設定 ForceFullCalculation

讓活頁簿計算得更快

利用接下來的步驟和方法，讓您的活頁簿計算得更快。

處理器速度與多核心

對於大多數的 Excel 版本而言，擁有一顆更快的處理器，理所當然可以讓 Excel 計算得更快。 Excel 2007 中引進的多執行緒計算引擎，可讓 Excel 充分發揮多處理器系統的優勢，明顯提升多數活頁簿的效能。

對大多數的大型活頁簿而言，多處理器規模的計算效能增益與實體處理器的數量幾乎成正比。 然而，實體處理器的超執行緒產生的效能增益卻不大。

如需詳細資訊，請參閱 Excel 效能：改良效能與限制。

RAM

建立虛擬記憶體分頁檔的分頁過程很緩慢。 您必須擁有足夠的實體 RAM 供作業系統、Excel 及活頁簿使用。 若計算期間您的硬碟活動量比平常大，而且您沒有執行會觸發磁碟活動的使用者自訂函數，代表您需要更多的 RAM。

如前所述，近期版本的 Excel 會有效運用大量的記憶體，32 位元版 Excel 2007 和 Excel 2010 可以處理佔用高達 2 GB 活頁簿記憶體的單一活頁簿或多個活頁簿。

根據安裝的 Windows 版本，使用「大型位址感知」 (LAA) 功能的 32 位版本 Excel 2013 和 Excel 2016 最多可以使用 3 或 4 GB 的記憶體。 Excel 的 64 位元版本可以處理更大的活頁簿。 如需詳細資訊，請參閱 Excel 效能：改良效能與限制的＜大型資料集、LAA 和 64 位元版 Excel＞一節。

有效運算的約略準則是，擁有足夠的 RAM 可以容納您必須在同時間開啟的最大活頁簿集合，再加上 1 至 2 GB 供 Excel 和作業系統使用，再加上額外的 RAM 供任何其他執行的應用程式使用。

測量計算時間

若要更快速地計算活頁簿，您必須能夠精確地測量計算時間。 您需要比 VBA 的 Time 函數更快速且更精確的計時器。 下列程式碼範例中顯示的 MICROTIMER() 函數使用 Windows API 呼叫系統的高精度計時器。 其測量時間間隔可小至百萬分之一秒。 請注意，由於 Windows 是一個多工處理的作業系統，也因為第二次計算某個項目的速度會比第一次來得快，所以您測得的時間結果通常不會一樣。 若要測得最準的結果，最好是多測幾次，然後再取平均值。

如需有關 Visual Basic Editor 如何大幅影響 VBA 使用者定義函數的效能，請參閱 Excel 效能：最佳化效能阻礙的秘訣的＜較快的 VBA 使用者定義函數＞一節。

#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

若要測量計算時間，您必須先呼叫適合的計算方法。 下列副程式可為您測出一個範圍的計算時間、一個工作表或所有開啟中活頁簿的重新計算時間，或是所有開啟中活頁簿的完整計算時間。

請將所有這些副程式和函數全部複製到標準 VBA 模組。 若要開啟 VBA 編輯器，請按下 ALT+F11。 在 [插入] 功能表，選取 [模組]，然後將程式碼複製到模組。


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

若要在 Excel 中執行副程式，按下 ALT+F8。 選取您想要的副程式，然後按一下 [執行]。

圖 5： Excel 的 [巨集] 視窗中顯示計算計時器

Excel 的巨集視窗

找出計算障礙並排定優先順序

大多數計算緩慢的活頁簿，只有幾個問題區域或障礙就耗費了大半的計算時間。 如果您還不知道障礙在哪裡，請參考本章節所述的向下切入的做法，找出障礙所在。 如果您已知問題所在，您必須測量每個障礙所耗費的計算時間，因此您可以調整工作的優先順序，消除障礙。

找出障礙的循序向下鑽研方法

此循序向下鑽研做法是先測量活頁簿的計算時間，再測量每個工作表的計算時間，再測量慢速工作表上的公式區塊。 請依序完成每個步驟，並記錄下計算時間。

使用循序向下鑽研方法找出障礙

  1. 請確認只開啟一份活頁簿，沒有其他正在執行中的工作。

  2. 將計算設定為手動。

  3. 建立活頁簿的備份副本。

  4. 開啟包含有計算計時器巨集的活頁簿，或是將巨集新增至活頁簿。

  5. 輪流在每個工作表上按下 CTRL+END，查看使用的範圍。

    這會顯示最後一個使用的儲存格位置。 如果位置出現在您意料之外的地方，請考慮刪除多餘的欄與列，並儲存活頁簿。 如需詳細資訊，請參閱 Excel 效能：最佳化效能阻礙的秘訣的＜最小化使用範圍＞一節。

  6. 執行 FullCalcTimer 巨集。

    計算活頁簿中所有公式的時間通常是最差案例的時間。

  7. 執行 RecalcTimer 巨集。

    完整計算後立即重新計算通常是最佳案例的時間。

  8. 將活頁簿揮發性計算為重新計算時間對完整計算的比例。

    這會測量障礙程度，其中的障礙是動態公式和計算鏈的評估。

  9. 啟動工作表並依序執行 SheetTimer 巨集。

    因為只要重新計算活頁簿，您就能得到每個工作表的重新計算時間。 這樣應該可以讓您判斷哪些是問題工作表。

  10. 在選取的公式區塊上執行 RangeTimer 巨集。

  11. 針對每個問題工作表，將欄或列分成小量區塊。

  12. 依序選取每個區塊，然後在區塊上執行 RangeTimer 巨集。

  13. 如有需要進一步向下鑽研，將每個區塊再細分成更小量區塊。

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

加快計算與減少障礙

消耗計算時間的不是公式的數量或活頁簿的大小。 而是儲存格參照和計算作業的數量，以及使用的函數效率。

因為大部分的活頁簿是由混合絕對參照和相對參照的複製公式建構而成，這些活頁簿通常包含有大量的公式，這些公式又包含有重複或相同的計算和參照。

請避免使用複雜的巨型公式和陣列公式。 一般來說，較佳的作法是使用較多的欄與列以及較少的複雜計算。 因為如此可以讓 Excel 的智慧重算與多執行緒計算更良好地發揮最佳化計算的能力。 這樣也會比較容易理解及偵錯。 下列有一些規則可協助您加速活頁簿計算。

規則一：移除相同、重複和不必要的計算

找出相同、重複和不必要的計算，然後估算 Excel 大約需要多少儲存格參照和計算，才能計算出此障礙的結果。 再思考如何使用更少的參照和計算就能得出相同的結果。

這通常需要下列一或多個步驟：

  • 減少每個公式中的參照數量。

  • 將重複的計算移至一或多個輔助儲存格，然後從原始公式參照輔助儲存格。

  • 使用額外的列與欄一次計算和儲存中間結果，因此您可以在其他公式中重複使用這些中間結果。

規則二：儘可能使用最有效率的函數

當您找到一個和函數或陣列公式相關的障礙時，請想想看有沒有更有效率的方式可以達成相同的結果。 例如：

  • 在已排序資料上查閱的效率高於在未排序資料查閱千百倍。

  • VBA 使用者定義函數通常比 Excel 內建函數慢 (雖然某些精心撰寫的 VBA 函數也可能很快)。

  • 將 SUM 和 SUMIF 等函數中使用的儲存格數量減到最少。 計算時間和使用的儲存格數量成正比 (會忽略未使用的儲存格)。

  • 請考慮以使用者定義函數取代緩慢的陣列公式。

規則三：善用智慧重算和多執行緒計算

愈能充分發揮 Excel 的智慧重算和多執行緒計算，每次 Excel 重新計算的處理量就愈少，因此建議您：

  • 儘可能避免使用動態函數，例如 INDIRECT 和 OFFSET，除非這些函數比其他選擇更具效率 (OFFSET 如使用設計得當，通常可以很快)。

  • 將陣列公式和函數中使用的範圍最小化。

  • 將陣列公式和巨型公式斷開成個別的輔助欄與列。

  • 避免使用單一執行緒函數：

    • PHONETIC
    • 有使用 format 或 address 引數的 CELL
    • INDIRECT
    • GETPIVOTDATA
    • CUBEMEMBER
    • CUBEVALUE
    • CUBEMEMBERPROPERTY
    • CUBESET
    • CUBERANKEDMEMBER
    • CUBEKPIMEMBER
    • CUBESETCOUNT
    • 已指定第五參數 (sheet_name) 的 ADDRESS
    • 任何參照到樞紐分析表的資料庫函數 (DSUM、DAVERAGE 等等)
    • ERROR.TYPE
    • HYPERLINK
    • VBA 和 COM 增益集的使用者定義函數

  • 避免重複使用運算列表和循環參照：這兩個一律使用單一執行緒進行計算。

規則四：計時並測試每項變更

某些變更的結果可能會讓您大吃一驚，算出的結果可能不如預期，或是計算速度比原先預期的更慢。 因此，您必須計時並測試每項變更，方法如下：

  1. 使用 RangeTimer 巨集，計時您要變更的公式。

  2. 進行變更。

  3. 使用 RangeTimer 巨集，計時已變更的公式。

  4. 確認已變更的公式仍會給出正確的答案。

規則範例

下列各節提供如何使用規則加快計算的範例。

日期區間加總

舉例來說，您想計算某一欄的日期區間加總，而這一欄含有 2,000 筆數字。 假設欄 A 含有數字，而欄 B 和欄 C 應含有日期區間加總的結果。

您可以使用有效率的函數 SUM 撰寫公式。

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

圖 6： 日期區間 SUM 公式範例

日期區間 SUM 公式範例

將公式向下複製到 B2000。

SUM 一共要相加多少儲存格參照？ B1 參照到一個儲存格，而 B2000 參照到 2,000 個儲存格。 平均每個儲存格有 1000 筆參照，因此總共有 2 百萬筆參照。 選取這 2,000 個公式並使用 RangeTimer 巨集，可告訴您欄 B 中的 2,000 個公式需花費 80 毫秒來計算。 這些計算多半重複了許多次：從 B2:B2000 的每個公式中，SUM 都會將 A1 加到 A2。

如果您撰寫如下所示的公式，您可以消除這個相同的部分。

  C1=A1
  C2=C1+A1

將這個公式向下複製到 C2000。

現在，一共要相加多少儲存格參照？ 除了第一個公式，每個公式使用兩個儲存格參照。 因此，總共是 1999*2+1=3999。 這減少了 500 倍的儲存格參照。

RangeTimer 告訴我們，欄 C 中的 2,000 個公式計算時間為 3.7 毫秒，比欄 B 中的 80 毫秒快多了。因此這項變更使得效能只提升了 80/3.7=22 倍，而不是 500 倍，這是因為每個公式都會額外消耗一點時間。

錯誤處理

如果您有一個計算密集的公式，您希望當公式發生錯誤時，結果顯示為零 (完全相符的查閱經常發生這種情形)，這樣的公式有許多撰寫方式。

  • 您可以撰寫為單一公式，但是速度緩慢：

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

  • 您可以撰寫兩個公式，速度較快：

    A1=time expensive formula

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

  • 或者，您可以使用 IFERROR 函數，此函數設計成既快速又簡單，而且還是單一公式：

    B1=IFERROR(time expensive formula,0)

動態計算唯一值

圖 7： 計算唯一值的資料清單範例

計算唯一資料範例

如果您的清單在欄 A 中共有 11,000 列資料，而且這些資料經常變動，而您需要一個公式可以動態計算清單中具唯一性的項目總數，且不計空白，以下是幾種可能的做法。

  • 陣列公式 (使用 CTRL+SHIFT+Enter)；RangeTimer 指出計算時間是 13.8 秒。

    {=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}

  • SUMPRODUCT 的計算速度通常比同等陣列公式還快。 這個公式需花費 10.0 秒，比前者快了 13.8/10.0=1.38 倍，雖然好一點，但還不夠令人滿意。

    =SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&amp;""))

  • 使用者定義函數。 以下程式碼範例示範一個 VBA 使用者定義函數，這個函數運用了「指向集合的索引必須具唯一性」這個特點。 如需一些使用到的技巧詳細說明，請參閱 Excel 效能：最佳化效能阻礙的秘訣中＜有效率地使用函數＞一節內的使用者定義函數。 這個公式 =COUNTU(A2:A11000) 只需要 0.061 秒。 速度改善 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
             colUniques.Add vCell, CStr(vCell)
        End If
    End If
    vLcell = vCell
    Next vCell

    COUNTU = colUniques.Count
End Function

  • 新增一欄公式。 看看先前的資料範例，您會發現資料已排序完成 (Excel 耗費 0.5 秒排序 11,000 列)。 您可以利用這點，新增一欄可檢查本列資料是否和前一列相同的公式。 如果不同，公式傳回 1。 否則，傳回 0。

    將這個公式新增到儲存格 B2。

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

    複製公式，然後新增加總欄 B 的公式。

      =SUM(B2:B11000)

    完整計算所有這些公式需花費 0.027 秒。 速度改善 13.8/0.027=511 倍。

總結

您可利用 Excel 有效率地管理大上許多的工作表，相較於早期版本，在計算速度方面有大幅的效能提升。 當我們建立大型工作表時，很容易不小心設計成計算緩慢的結構。 計算緩慢的工作表會提高出錯的風險，因為使用者難以在計算期間保持專注。

藉由使用一系列簡單明瞭的技巧，便可以讓原本計算緩慢的工作表加快 10 到 100 倍。 下次您在設計和建立工作表時，不妨運用這些技巧，有效提升計算速度。

另請參閱

支援和意見反應

有關於 Office VBA 或這份文件的問題或意見反應嗎？ 如需取得支援服務並提供意見反應的相關指導，請參閱 Office VBA 支援與意見反應。