Excel 效能- 改善計算效能

也就是說，在每次重新計算時，同一個公式可能會計算多次。

第二次計算活頁簿的速度通常比首次執行快上許多。

發生的原因有幾個：. Excel 通常只 ... 跳到主要內容 已不再支援此瀏覽器。

請升級至MicrosoftEdge，以利用最新功能、安全性更新和技術支援。

下載MicrosoftEdge 其他資訊 目錄 結束焦點模式 閱讀英文 儲存 目錄 閱讀英文 儲存 編輯 Twitter LinkedIn Facebook 電子郵件 WeChat 目錄 Excel效能：改善計算效能 發行項 04/11/2022 1位參與者 此頁面有所助益嗎？ Yes No 還有其他意見反應嗎？ 系統會將意見反應傳送給Microsoft：按下[提交]按鈕，您的意見反應將用來改善Microsoft產品和服務。

隱私權原則。

送出 謝謝。

本文內容 適用版本：Excel|Excel2013|Excel2016|VBA 在OfficeExcel2016，最大欄數可達16,000欄，最大列數亦高達1百萬列，再加上許多其他限制加大，您可以建立的工作表大小比起舊版Excel大上許多。

Excel中單一工作表可容納的儲存格數是舊版的1,000倍以上。

在舊版Excel中，許多使用者建立的工作表計算速度緩慢，而大型工作表的計算速度通常又比小型的慢上許多。

隨著Excel2007中所加入的欄列數上限，效能問題就變得更加重要了。

像是排序、篩選等計算與資料處理工作過於緩慢，將導致使用者更難以專心處理手邊的工作，而一旦分心就更容易出錯。

近期的Excel版本推出幾項新功能，有助於您因應此大幅增加的容量，例如可同時運用多個處理器進行計算，以及如重新整理、排序和開啟活頁簿等常用資料集作業的能力。

多執行緒計算可實際縮短工作表計算時間。

但真正會影響Excel計算速度最重要的因素，仍取決於您工作表的設計與建立方式。

只要針對計算最遲緩的工作表進行修改，即可讓計算速度提升數十、上百甚至千倍。

透過找出工作表中的計算障礙並加測量，進而改善計算效能，您可以加快計算的速度。

計算速度的重要性 計算速度遲緩會降低生產力，且導致使用者出錯機率大增。

當回應時間變長，使用者的專注力和生產力便會隨之下降。

Excel有兩種計算模式，可讓您控制何時進行計算： 自動計算-每次進行變更時，便會自動重新計算公式。

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

計算時間少於十分之一秒時，使用者會感覺系統是立即回應的。

甚至可以在一邊輸入資料的同時，一邊自動計算。

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

倘若計算時間更長(通常介於1到10秒之間)，使用者在輸入資料時，就必須切換成手動計算。

使用者出錯的頻率將會增加，且開始覺得煩躁，尤其對於重覆性的工作，要保持流暢的思緒變得十分困難。

計算時間若長達10秒以上，使用者會失去耐性，而且通常在等待期間乾脆切換到其他工作。

如果該計算是一系列工作的其中一部分，而使用者因此錯亂失序的話，麻煩就大了。

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

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

在下一次重新計算時，Excel只會重新計算以下項目： 已變更或是標示為需要重新計算的儲存格、公式、值或名稱。

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

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

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

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

在手動計算模式下，您只要按下F9就可以啟動這個智慧重算。

按下CTRL+ALT+F9就會強制執行完整計算所有公式，或者您也可以按下SHIFT+CTRL+ALT+F9，強制完全重建相依性並執行完整計算。

計算處理流程 參照其他儲存格的Excel公式可置放於被參照的儲存格之前或之後(向前參照或向後參照)。

這是因為Excel計算儲存格的順序不是固定的，也不是依欄或列加以計算。

相反地，Excel會依照所有待計算公式的清單(稱為計算鏈)以及每個公式的相依性資訊，動態地決定計算順序。

Excel的計算處理流程分為幾個階段： 建立初始計算鏈並決定計算開始處。

這個階段出現在當活頁簿載入記憶體時。

追蹤相依性、標記出尚未計算的儲存格，然後更新計算鏈。

即使是在手動計算模式時，每次輸入儲存格項目或儲存格變更時，就會執行這個階段。

通常這個階段的執行速度會快到您無法察覺，但是在複雜的情況下，回應可能會很慢。

計算所有公式。

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

更新Excel視窗的可見部分。

每次計算或重新計算時，都會執行第三個階段。

Excel會嘗試依計算鏈中公式的順序，計算每個公式，但如果有某個公式，其相依的一或多個公式尚未計算過，則該公式將向後排到計算鏈尾端，稍後再加以計算。

也就是說，在每次重新計算時，同一個公式可能會計算多次。

第二次計算活頁簿的速度通常比首次執行快上許多。

發生的原因有幾個： Excel通常只會重新計算已變更的儲存格以及其相依參照。

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

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

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

計算活頁簿、工作表和範圍 您可以透過使用不同的Excel計算方法，控制要進行計算的項目。

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

計算所選的工作表 您也可以使用SHIFT+F9只重新計算選取的工作表。

這樣不會解析工作表之間任何的相依性，也不會將已變更的儲存格重設為已計算。

計算儲存格範圍 Excel也允許使用VisualBasicforApplications(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的運算列表([資料]索引標籤>[資料工具]群組>[模擬分析]>[運算列表])與表格功能([常用]索引標籤>[樣式]群組>[格式化為表格]，或是[插入]索引標籤>[表格]群組>[表格])混淆。

Excel運算列表會針對活頁簿進行多次重新計算，分別由列表中不同的值所驅動。

Excel首先會以一般方式計算活頁簿。

接著會替換每組欄與列的值，進行單一執行緒的重新計算，最後將結果儲存在運算列表中。

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

您可利用運算列表輕鬆計算多種變化，並檢視與比較各組變化的結果。

使用計算選項[除資料表外，自動重算]可停止Excel在每次計算時自動觸發多重計算，但仍然會計算所有運算列表以外的相依公式。

控制計算選項 Excel擁有許多選項可讓您操控計算的方式。

只要使用功能區中[公式]索引標籤的[計算]群組，即可變更Excel中最常使用的選項。

圖1：[公式]索引標籤的[計算]群組 若要查看更多Excel計算選項，在[檔案]索引標籤，按一下[選項]。

在[Excel選項]對話方塊，按一下[公式]索引標籤。

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

若要查看進階計算選項，在[檔案]索引標籤，按一下[選項]。

在[Excel選項]對話方塊，按一下[進階]。

在[公式]區段下方，設定計算選項。

圖3：進階計算選項 當您開啟Excel時，或是當Excel在沒有開啟任何活頁簿的情況下執行時，初始的計算模式和反覆運算設定，都會採用您開啟的第一個非範本且沒有增益集的活頁簿之設定。

這表示之後開啟的活頁簿中，其中的計算設定將會忽略，但當然您也可以隨時在Excel中變更這項設定。

儲存活頁簿時，也會一併將目前的計算設定儲存在活頁簿中。

自動計算 自動計算模式是指每次有變更或每次開啟活頁簿時，Excel都會自動重新計算所有開啟中活頁簿。

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

若您使用比上次計算該活頁簿時的版本更新的Excel開啟活頁簿時(比方說先用Excel2013再用Excel2016)，您就會注意到計算的情形。

由於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屬性 讓活頁簿計算得更快 利用接下來的步驟和方法，讓您的活頁簿計算得更快。

處理器速度與多核心 對於大多數的Excel版本而言，擁有一顆更快的處理器，理所當然可以讓Excel計算得更快。

Excel2007中引進的多執行緒計算引擎，可讓Excel充分發揮多處理器系統的優勢，明顯提升多數活頁簿的效能。

對大多數的大型活頁簿而言，多處理器規模的計算效能增益與實體處理器的數量幾乎成正比。

然而，實體處理器的超執行緒產生的效能增益卻不大。

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

RAM 建立虛擬記憶體分頁檔的分頁過程很緩慢。

您必須擁有足夠的實體RAM供作業系統、Excel及活頁簿使用。

若計算期間您的硬碟活動量比平常大，而且您沒有執行會觸發磁碟活動的使用者自訂函數，代表您需要更多的RAM。

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

使用大量位址提示(LAA，LargeAddressAware)的32位元版Excel2013和Excel2016，視安裝的Windows版本而定，可以使用高達3GB或4GB的記憶體。

Excel的64位元版本可以處理更大的活頁簿。

如需詳細資訊，請參閱Excel效能：改良效能與限制的＜大型資料集、LAA和64位元版Excel＞一節。

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

測量計算時間 若要更快速地計算活頁簿，您必須能夠精確地測量計算時間。

您需要比VBA的Time函數更快速且更精確的計時器。

下列程式碼範例中顯示的MICROTIMER()函數使用WindowsAPI呼叫系統的高精度計時器。

其測量時間間隔可小至百萬分之一秒。

請注意，由於Windows是一個多工處理的作業系統，也因為第二次計算某個項目的速度會比第一次來得快，所以您測得的時間結果通常不會一樣。

若要測得最準的結果，最好是多測幾次，然後再取平均值。

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

#IfVBA7Then PrivateDeclarePtrSafeFunctiongetFrequencyLib"kernel32"Alias_ "QueryPerformanceFrequency"(cyFrequencyAsCurrency)AsLong PrivateDeclarePtrSafeFunctiongetTickCountLib"kernel32"Alias_ "QueryPerformanceCounter"(cyTickCountAsCurrency)AsLong #Else PrivateDeclareFunctiongetFrequencyLib"kernel32"Alias_"QueryPerformanceFrequency"(cyFrequencyAsCurrency)AsLong PrivateDeclareFunctiongetTickCountLib"kernel32"Alias_ "QueryPerformanceCounter"(cyTickCountAsCurrency)AsLong #EndIf FunctionMicroTimer()AsDouble ' 'Returnsseconds. DimcyTicks1AsCurrency StaticcyFrequencyAsCurrency ' MicroTimer=0 'Getfrequency. IfcyFrequency=0ThengetFrequencycyFrequency 'Getticks. getTickCountcyTicks1 'Seconds IfcyFrequencyThenMicroTimer=cyTicks1/cyFrequency EndFunction 若要測量計算時間，您必須先呼叫適合的計算方法。

下列副程式可為您測出一個範圍的計算時間、一個工作表或所有開啟中活頁簿的重新計算時間，或是所有開啟中活頁簿的完整計算時間。

請將所有這些副程式和函數全部複製到標準VBA模組。

若要開啟VBA編輯器，請按下ALT+F11。

在[插入]功能表，選取[模組]，然後將程式碼複製到模組。

SubRangeTimer() DoCalcTimer1 EndSub SubSheetTimer() DoCalcTimer2 EndSub SubRecalcTimer() DoCalcTimer3 EndSub SubFullcalcTimer() DoCalcTimer4 EndSub SubDoCalcTimer(jMethodAsLong) DimdTimeAsDouble DimdOvhdAsDouble DimoRngAsRange DimoCellAsRange DimoArrRangeAsRange DimsCalcTypeAsString DimlCalcSaveAsLong DimbIterSaveAsBoolean ' OnErrorGoToErrhandl 'Initialize dTime=MicroTimer 'Savecalculationsettings. lCalcSave=Application.Calculation bIterSave=Application.Iteration IfApplication.Calculation<>xlCalculationManualThen Application.Calculation=xlCalculationManual EndIf SelectCasejMethod Case1 'Switchoffiteration. IfApplication.Iteration<>FalseThen Application.Iteration=False Endif 'Maxisusedrange. IfSelection.Count>1000Then SetoRng=Intersect(Selection,Selection.Parent.UsedRange) Else SetoRng=Selection EndIf 'Includearraycellsoutsideselection. ForEachoCellInoRng IfoCell.HasArrayThen IfoArrRangeIsNothingThen SetoArrRange=oCell.CurrentArray EndIf IfIntersect(oCell,oArrRange)IsNothingThen SetoArrRange=oCell.CurrentArray SetoRng=Union(oRng,oArrRange) EndIf EndIf NextoCell sCalcType="Calculate"&CStr(oRng.Count)&_ "Cell(s)inSelectedRange:" Case2 sCalcType="RecalculateSheet"&ActiveSheet.Name&":" Case3 sCalcType="Recalculateopenworkbooks:" Case4 sCalcType="FullCalculateopenworkbooks:" EndSelect 'Getstarttime. dTime=MicroTimer SelectCasejMethod Case1 IfVal(Application.Version)>=12Then oRng.CalculateRowMajorOrder Else oRng.Calculate EndIf Case2 ActiveSheet.Calculate Case3 Application.Calculate Case4 Application.CalculateFull EndSelect 'Calculateduration. dTime=MicroTimer-dTime OnErrorGoTo0 dTime=Round(dTime,5) MsgBoxsCalcType&""&CStr(dTime)&"Seconds",_ vbOKOnly+vbInformation,"CalcTimer" Finish: 'Restorecalculationsettings. IfApplication.Calculation<>lCalcSaveThen Application.Calculation=lCalcSave EndIf IfApplication.Iteration<>bIterSaveThen Application.Iteration=bIterSave EndIf ExitSub Errhandl: OnErrorGoTo0 MsgBox"UnabletoCalculate"&sCalcType,_ vbOKOnly+vbCritical,"CalcTimer" GoToFinish EndSub 若要在Excel中執行副程式，按下ALT+F8。

選取您想要的副程式，然後按一下[執行]。

圖5：Excel的[巨集]視窗中顯示計算計時器 找出計算障礙並排定優先順序 大多數計算緩慢的活頁簿，只有幾個問題區域或障礙就耗費了大半的計算時間。

如果您還不知道障礙在哪裡，請參考本章節所述的向下切入的做法，找出障礙所在。

如果您已知問題所在，您必須測量每個障礙所耗費的計算時間，因此您可以調整工作的優先順序，消除障礙。

找出障礙的循序向下鑽研方法 此循序向下鑽研做法是先測量活頁簿的計算時間，再測量每個工作表的計算時間，再測量慢速工作表上的公式區塊。

請依序完成每個步驟，並記錄下計算時間。

使用循序向下鑽研方法找出障礙 請確認只開啟一份活頁簿，沒有其他正在執行中的工作。

將計算設定為手動。

建立活頁簿的備份副本。

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

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

這會顯示最後一個使用的儲存格位置。

如果位置出現在您意料之外的地方，請考慮刪除多餘的欄與列，並儲存活頁簿。

如需詳細資訊，請參閱Excel效能：最佳化效能阻礙的秘訣的＜最小化使用範圍＞一節。

執行FullCalcTimer巨集。

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

執行RecalcTimer巨集。

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

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

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

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

因為只要重新計算活頁簿，您就能得到每個工作表的重新計算時間。

這樣應該可以讓您判斷哪些是問題工作表。

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

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

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

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

排列障礙的優先順序。

加快計算與減少障礙 消耗計算時間的不是公式的數量或活頁簿的大小。

而是儲存格參照和計算作業的數量，以及使用的函數效率。

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

請避免使用複雜的巨型公式和陣列公式。

一般來說，較佳的作法是使用較多的欄與列以及較少的複雜計算。

因為如此可以讓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增益集的使用者定義函數 避免重複使用運算列表和循環參照：這兩個一律使用單一執行緒進行計算。

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

因此，您必須計時並測試每項變更，方法如下： 使用RangeTimer巨集，計時您要變更的公式。

進行變更。

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

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

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

日期區間加總 舉例來說，您想計算某一欄的日期區間加總，而這一欄含有2,000筆數字。

假設欄A含有數字，而欄B和欄C應含有日期區間加總的結果。

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

B1=SUM($A$1:$A1) B2=SUM($A$1:$A2) 圖6：日期區間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(timeexpensiveformula),0,timeexpensiveformula) 您可以撰寫兩個公式，速度較快： A1=timeexpensiveformula B1=IF(ISERROR(A1),0,A1) 或者，您可以使用IFERROR函數，此函數設計成既快速又簡單，而且還是單一公式： B1=IFERROR(timeexpensiveformula,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&"")) 使用者定義函數。

以下程式碼範例示範一個VBA使用者定義函數，這個函數運用了「指向集合的索引必須具唯一性」這個特點。

如需一些使用到的技巧詳細說明，請參閱Excel效能：最佳化效能阻礙的秘訣中＜有效率地使用函數＞一節內的使用者定義函數。

這個公式=COUNTU(A2:A11000)只需要0.061秒。

速度改善13.8/0.061=226倍。

PublicFunctionCOUNTU(theRangeAsRange)AsVariant DimcolUniquesAsNewCollection DimvArrAsVariant DimvCellAsVariant DimvLcellAsVariant DimoRngAsRange SetoRng=Intersect(theRange,theRange.Parent.UsedRange) vArr=oRng OnErrorResumeNext ForEachvCellInvArr IfvCell<>vLcellThen IfLen(CStr(vCell))>0Then colUniques.AddvCell,CStr(vCell) EndIf EndIf vLcell=vCell NextvCell COUNTU=colUniques.Count EndFunction 新增一欄公式。

看看先前的資料範例，您會發現資料已排序完成(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倍。

下次您在設計和建立工作表時，不妨運用這些技巧，有效提升計算速度。

另請參閱 Excel效能：改良效能與限制 Excel效能：最佳化效能阻礙的秘訣 Excel開發人員入口網站 支援和意見反應 有關於OfficeVBA或這份文件的問題或意見反應嗎？如需取得支援服務並提供意見反應的相關指導，請參閱OfficeVBA支援與意見反應。

本文內容