Excel 效能：最佳化效能阻礙的秘訣

發行項
04/07/2023

適用于： Excel |Excel 2013 |Office 2016 |Vba

請遵循下列秘訣，在 Excel 中優化許多經常發生的效能阻礙。

優化參考和連結

瞭解如何改善與參考類型和連結相關的效能。

請勿使用正向參考和回溯參考

若要提高清晰度並避免錯誤，請設計您的公式，使其不會將 (往右或下方參考) 到其他公式或儲存格。向前參照通常不會影響計算效能，除非是在活頁簿第一次計算的極端狀況下，如果有很多公式需要延遲其計算，可能會花比較長的時間來建立合理的計算順序。

將迴圈參考與反復專案的使用最小化

使用反復專案計算迴圈參考的速度很慢，因為需要多次計算，而且這些計算是單一執行緒計算。您經常可以使用代數「取消註冊」迴圈參考，以便不再需要反復計算。例如，在現金流量和利息計算中，嘗試計算利息之前的現金流量、計算利息，然後計算包括利息在內的現金流量。

Excel 會逐工作表計算迴圈參考，而不考慮相依性。因此，如果您的循環參照跨多個工作表，計算速度通常會很慢。請嘗試將循環計算移到單一工作表上，或最佳化工作表計算順序，以避免不必要的計算。

在反覆運算開始之前，Excel 必須重新計算活頁簿，以識別所有循環參照及其相依項。此程序等於兩次或三次反覆運算。

在識別循環參照及其相依項之後，每次反覆運算都需要 Excel 不只計算循環參照中的所有儲存格，也要計算相依於循環參照鏈中之儲存格的任何儲存格，以及動態儲存格及其相依項。如果您有相依於循環參照中之儲存格的複雜計算，則將其隔離至已關閉的個別活頁簿，在循環計算收斂之後，再將它開啟以重新計算，這樣會比較快。

請務必減少迴圈計算中的儲存格數目，以及這些儲存格所花費的計算時間。

避免活頁簿之間的連結

盡可能避免活頁簿間連結;它們可能很慢、容易中斷，而且不一定易於尋找和修正。

使用較少的較大活頁簿通常 (但不是一定) 會比使用很多較小的活頁簿好。有些例外狀況可能是當您有許多前端計算很少重新計算，因此將它們放在不同的活頁簿中，或是 RAM 不足時。

請嘗試使用在關閉的活頁簿上運作之簡單直接的儲存格參照。這麼做可以避免在重新計算「任何」活頁簿時，重新計算「所有」連結的活頁簿。此外，您也可以看到 Excel 從關閉的活頁簿讀取的值，這對偵錯和稽核活頁簿來說經常很重要。

如果您無法避免使用連結的活頁簿，請嘗試讓它們全部開啟，而不要關閉，並且先開啟連結目標活頁簿，再開啟連結來源活頁簿。

將工作表之間的連結最小化

使用很多工作表可以讓您的活頁簿更易於使用，但是計算對其他工作表的參照，通常會比計算工作表中的參照慢。

最小化已使用的範圍

為了儲存記憶體並減少檔案大小，Excel 會嘗試只在使用的工作表上儲存區域的相關資訊。這就叫做「使用的範圍」。有時候各種編輯和格式化作業會延伸使用的範圍，並大幅超出您目前考慮使用的範圍。這樣會導致效能阻礙及檔案大小阻礙。

您可以使用 Ctrl+End 檢查工作表上可見的已使用範圍。如果這個動作過多，您應該考慮刪除實際最後使用之儲存格右側下方和右邊的所有資料列和資料行，然後儲存活頁簿。請先建立備份複本。如果您有公式的範圍延伸至或參照所刪除區域，這些範圍就會降低大小或變更為 [#N/A]。

允許額外的資料

如果您經常新增資料列或欄至工作表，則需要找出一個方法，讓您的 Excel 公式自動參照新的資料區域，而不要每次都嘗試尋找及變更您的公式。

若要這麼做，您可以在公式中使用大幅超出目前資料邊界的大範圍。然而，這樣會導致在某些情況下計算的效率不佳，而且也很難維護，因為刪除列和欄會在您不注意的情況下縮減範圍。

使用結構化資料表參考 (建議)

從 Excel 2007 開始，您可以使用結構化資料表參考，這會隨著參考資料表的大小增加或減少而自動展開和縮減。

此解決方案有幾項優點：

相較于整個資料行參考和動態範圍的替代方案，效能缺點較少。
在單一工作表上有多個資料表很容易。
內嵌在資料表中的公式也會隨資料擴充或縮減。

或者，使用整個資料行和資料列參考

替代方法是使用整個資料行參考，例如 $A：$A。此參考會傳回資料行 A 中的所有資料列。因此，您可以視需要新增更多資料，而且參考一律會包含它。

這個解決方案有優點也有缺點：

很多 Excel 內建函數 (SUM、SUMIF) 都會有效率地計算整欄參照，因為它們會自動辨識該欄中上次使用的列。然而，陣列計算函數 (如 SUMPRODUCT) 既無法處理整欄參照，也無法計算欄中的所有儲存格。
使用者定義函數不會自動辨識資料行中最後使用的資料列，因此經常會以效率低下的方式計算整個資料行參考。不過，您可以輕鬆地設計使用者定義函數的程式，以便辨識上次使用的資料列。
當您在單一工作表上有多個資料表時，很難使用整個資料行參考。
在 Excel 2007 和更新版本中，陣列公式可以處理整欄參照，但這會強制計算資料行中的所有儲存格，包括空白儲存格。這樣計算會很慢，尤其是計算 1 百萬列時。

或者，使用動態範圍

藉由在具名範圍的定義中使用 OFFSET 或 INDEX 和 COUNTA 函數，您可以讓具名範圍所參照的區域動態展開和合約。例如，使用下列其中一個公式建立已定義的名稱：

  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
  =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+ROW(Sheet1!$A$1) - 1,1)

當您在公式中使用動態範圍名稱時，它會自動擴充，以包含新項目。

使用動態範圍的 INDEX 公式通常比 OFFSET 公式更適合，因為 OFFSET 的缺點是要在每次重新計算時計算的變動性函式。

效能會降低，因為動態範圍公式內的 COUNTA 函數必須檢查許多資料列。您可以將公式的 COUNTA 部分儲存在個別的儲存格或已定義的名稱中，然後參考動態範圍中的儲存格或名稱，以將此效能降低降至最低：

 Counts!z1=COUNTA(Sheet1!$A:$A)
 OffsetDynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)
 IndexDynamicRange=Sheet1!$A$1:INDEX(Sheet1!$A:$A,Counts!$Z$1+ROW(Sheet1!$A$1) - 1,1)

您也可以使用 INDIRECT 之類的函式來建構動態範圍，但 INDIRECT 是動態的，而且一律會計算單一執行緒。

動態範圍具有下列優缺點：

動態範圍很適合用來限制陣列公式所執行的計算數量。
在單一資料行內使用多個動態範圍需要特殊用途的計數函數。
使用很多動態範圍會降低效能。

改善查閱計算時間

在 Office 365 1809 版及更新版本中，Excel 針對未排序的資料進行完全符合的 VLOOKUP、HLOOKUP 和 MATCH，在查詢相同資料表範圍中的多個資料行 (使用 HLOOKUP 時為資料列) 時較以往的速度更快。

也就是說，對於舊版 Excel，查閱會持續成為經常顯著的計算障礙。幸好有很多方式可以改善查閱計算時間。如果您使用完全相符選項，則函數的計算時間與找到相符項之前所掃描的儲存格數目成正比。若是大範圍的查閱，這個時間會很可觀。

在已排序資料上使用 VLOOKUP、HLOOKUP 和 MATCH 的大約符合選項，查閱時間會很快，而且不會因為您查閱的範圍長度而大幅增加。特性與二進位搜尋相同。

瞭解查閱選項

請確定您瞭解 MATCH、VLOOKUP和HLOOKUP中的比對類型和範圍查閱選項。

下列程式碼範例顯示 MATCH 函數的語法。如需詳細資訊，請參閱 WorksheetFunction 物件的 Match 方法。

  MATCH(lookup value, lookup array, matchtype)

Matchtype=1 會傳回當查閱陣列以遞增方式排序時，小於或等於查閱值的最大比對 (近似相符) 。如果查閱陣列未遞增排序，MATCH 會傳回不正確的答案。預設選項大約是 以遞增方式排序的相符專案。
Matchtype=0 要求完全相符，並假設資料已排序。
如果查閱陣列為遞減排序，則 Matchtype=-1 會傳回大於或等於查閱值的最小相符項 (大約符合)。

下列程式碼範例顯示 VLOOKUP 和 HLOOKUP 函數的語法。如需詳細資訊，請參閱 WorksheetFunction 物件的 VLOOKUP 和 HLOOKUP 方法。

  VLOOKUP(lookup value, table array, col index num, range-lookup)
  HLOOKUP(lookup value, table array, row index num, range-lookup)

Range-lookup=TRUE 會傳回小於或等於查閱值的最大相符項 (大約符合)。此為預設選項。資料表陣列必須遞增排序。
Range-lookup=FALSE 要求完全相符，並假設資料已排序。

請儘可能避免在未排序的資料上執行查閱，因為會很慢。如果您的資料已排序，但您想要完全相符，請參閱針對具有遺漏值的已排序資料使用兩個查閱。

使用 INDEX 和 MATCH 或 OFFSET，而不是 VLOOKUP

請嘗試使用 INDEX 和 MATCH 函數，而不要使用 VLOOKUP。雖然 VLOOKUP 的速度稍快， () 快大約 5%，但比 MATCH和 INDEX或 OFFSET的組合更簡單且使用較少的記憶體， 但 MATCH 和 INDEX 提供的額外彈性通常可讓您大幅節省時間。例如，您可以將完全 MATCH 的結果儲存在儲存格中，然後在數個 INDEX 陳述式中重複使用。

INDEX 函數很快，而且是靜態函數 (可加速重新計算)。 OFFSET 函式也很快;不過，它是變動性函式，有時會大幅增加處理計算鏈結所花費的時間。

將 VLOOKUP 轉換成 INDEX 和 MATCH很容易。下列兩個語句會傳回相同的答案：

 VLOOKUP(A1, Data!$A$2:$F$1000,3,False)

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

加速查閱

因為完全相符查閱會很慢，所以請考慮使用下列選項來增進效能：

使用一個工作表。將查閱和資料保留在同一張工作表上會比較快。
在可能的情況下，先 SORT 資料 (SORT 很快)，並使用大約符合。
當您必須使用完全相符查閱時，請將所要掃描的儲存格範圍限制在最少的情況。使用資料表和結構化參考或動態範圍名稱，而不是參考大量的資料列或資料行。有時候您可以預先計算查閱的範圍下限及範圍上限。

針對具有遺漏值的已排序資料使用兩個查閱

兩個近似相符專案遠比查閱數個以上資料列的完全相符專案快得多。 (中斷點大約是 10-20 個數據列。)

如果您可以排序資料，但仍然無法使用近似相符專案，因為您無法確定您要查閱的值存在於查閱範圍中，您可以使用下列公式：

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
      VLOOKUP(lookup_val, lookup_array, column, True), "notexist")

在查閱欄本身執行大約查閱時，公式的第一部分有效。

  VLOOKUP(lookup_val ,lookup_array,1,True)

您可以使用下列公式，檢查查閱資料行的答案是否與查閱值 (相同，在此情況下，您) 完全相符：

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,

如果此公式傳回 True，您已找到完全相符的專案，因此您可以再次執行近似的查閱，但這次會從您想要的資料行傳回答案。

  VLOOKUP(lookup_val, lookup_array, column, True)

如果查閱資料行的答案與查閱值不符，則您有遺漏值，且公式會傳回「notexist」。

請注意，如果您查閱的值小於清單中的最小值，則會收到錯誤。若要處理此錯誤，您可以使用 IFERROR，或新增一個小測試值至清單。

針對含有遺漏值的未排序資料使用 IFERROR 函式

如果您必須在未排序的資料上使用完全相符的查閱，而且無法確定查閱值是否存在，則通常必須處理找不到相符專案時所傳回的 #N/A。從 Excel 2007 開始，您可以使用簡單又快速的 IFERROR 函式。

  IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)

在舊版中，有一個簡單但很慢的方法，就是使用包含兩個查閱的 IF 函數。

  IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
      VLOOKUP(lookupval,table,2,FALSE))

如果您使用一次完全 MATCH，將結果儲存在儲存格中，然後先測試結果，再執行 INDEX，就可以避免雙重完全查閱。

  In A1 =MATCH(lookupvalue,lookuparray,0)
  In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))

如果您無法使用兩個儲存格，請使用 COUNTIF。它通常比完全相符查閱快。

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

在多個資料行上使用 MATCH 和 INDEX 進行完全相符的查閱

您可以常常多次重複使用所儲存的完全 MATCH。例如，如果您在多個結果欄上執行完全查閱，您可以使用一個 MATCH 和很多個 INDEX 陳述式，而不要使用很多個 VLOOKUP 陳述式，以節省時間。

為 MATCH 新增額外的資料行，以將結果儲存 (stored_row) ，並針對每個結果資料行使用下列專案：

  INDEX(Lookup_Range,stored_row,column_number)

或者，您也可以在陣列公式中使用 VLOOKUP。 (必須使用 Ctrl+-Shift+Enter 輸入陣列公式。Excel 會新增 { 和 } ，以顯示這是) 的陣列公式。

  {VLOOKUP(lookupvalue,{4,2},FALSE)}

針對一組連續的資料列或資料行使用 INDEX

您也可以從一項查閱作業傳回很多儲存格。若要查閱數個連續欄，您可以在陣列公式中使用 INDEX 函數，一次傳回多欄 (使用 0 為欄數)。您也可以使用 INDEX 函數一次傳回多列。

  {INDEX($A$1:$J$1000,stored_row,0)}

這會從先前 MATCH 陳述式所建立的儲存列傳回欄 A 到欄 J。

使用 MATCH 傳回矩形儲存格區塊

使用 MATCH 和 OFFSET 函式可傳回儲存格的矩形區塊。

使用 MATCH 和 INDEX 進行二維查閱

您可以使用具有兩個內嵌MATCH函式的INDEX函式，在資料表的資料列和資料行上使用個別的查閱，有效率地執行二維資料表查閱，一個用於資料列，另一個用於資料行。

使用子集範圍進行多重索引查閱

在大型工作表中，您可能經常需要使用多個索引進行查閱，例如查閱國家/地區的產品磁片區。若要這麼做，您可以使用串連的查閱值來串連索引並執行查閱。不過這很沒有效率，有兩個原因：

串連字串是需要大量計算的作業。
查閱會涵蓋很大的範圍。

例如，藉由尋找國家/地區的第一個和最後一個資料列，然後在該子集範圍內查閱產品) ，計算查閱 (的子集範圍通常更有效率。

考慮三維查閱的選項

除了列和欄之外，若要查閱所要使用的資料表，您可以使用下列技巧，將重點放在如何進行 Excel 查閱或選擇資料表。

如果您想要查閱的每個資料表 (第三個維度) 會儲存為一組具名結構化資料表、範圍名稱，或是作為代表範圍的文字字串資料表，您或許可以使用 CHOOSE 或 INDIRECT 函數。

使用 CHOOSE 及範圍名稱會是個有效率的方法。 CHOOSE 為非動態，但最適合相對較小的資料表數目。此範例會動態使用 TableLookup_Value 來選擇要用於查閱表格的範圍名稱 (TableName1, TableName2, ...) 。
```
  INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
下列範例會使用 INDIRECT 函數，並 TableLookup_Value 動態建立要用於查閱表格的表名。此方法的優點是很簡單，而且能夠處理大量資料表。由於 INDIRECT 是變動性單一執行緒函式，因此即使沒有任何資料變更，每次計算都會計算單一執行緒的查閱。使用這個方法的速度很慢。
```
  INDEX(INDIRECT("Sheet" &amp; TableLookup_Value &amp; "!$B$2:$Z$1000"), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
您也可以使用 VLOOKUP 函數來尋找工作表的名稱或要用於資料表的文字字串，然後使用 INDIRECT 函數將結果文字轉換成範圍。
```
  INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```

另一個技巧是將您所有的資料表彙集成一個巨大的資料表，且該資料表有另一欄可識別個別資料表。然後您可以針對先前範例中顯示的多索引查閱來使用這些技巧。

使用萬用字元查閱

MATCH、VLOOKUP和HLOOKUP函式可讓您使用萬用字元？ (任何單一字元) ，並且 * (任何字元或任意數目的字元) 依字母順序完全相符。有時候您可以使用這個方法來避免有多個相符項目。

優化陣列公式和 SUMPRODUCT

陣列公式及 SUMPRODUCT 函數的功能很強，但必須小心處理。單一陣列公式可能需要進行許多計算。

最佳化陣列公式計算速度的關鍵，就是確定在陣列公式中評估的儲存格和運算式儘可能是最小的。請記住，陣列公式有點像是揮發性公式：如果參照的任何一個儲存格已變更、是動態或已重新計算，則陣列公式會計算公式中的所有儲存格，並評估執行計算所需的所有虛擬儲存格。

若要最佳化陣列公式的計算速度，請執行下列作業：

將運算式和範圍參照從陣列公式取出至個別的協助程式欄和列。以加速 Excel 中智慧重新計算的程序。
請勿參照完整列或超出您需求的列和欄。陣列公式會被強制計算公式中的所有儲存格參照，即使儲存格是空的或未使用也一樣。從 Excel 2007 開始，若有 1 百萬可用列，參照整欄的陣列公式計算速度會極為緩慢。
從 Excel 2007 開始，請儘可能使用結構化參照，將陣列公式評估的儲存格數目維持在最小程度。
在 Excel 2007 之前的版本中，盡可能使用動態範圍名稱。雖然這些名稱是可變更的，但是值得這麼做，因為它們會將範圍縮至最小。
請小心處理同時參照列和欄的陣列公式：這會強制計算矩形範圍。
請儘可能使用 SUMPRODUCT；它會比同等陣列公式稍微快一點。

考慮針對多重條件陣列公式使用 SUM 的選項

您應該一律使用 SUMIFS、 COUNTIFS和 AVERAGEIFS 函數，而不是您可以使用的陣列公式，因為它們的計算速度更快。 Excel 2016引進快速的 MAXIFS和MINIFS函式。

在 Excel 2007 之前的版本中，陣列公式通常用來計算具有多個條件的總和。這相對較容易執行，尤其是當您在 Excel 中使用 [條件式加總精靈] 時，但也常常很慢。通常會有快很多的方法可以取得相同的結果。如果您只有少數幾個多重條件 SUM，則可使用 DSUM 函數，這比同等陣列公式快很多。

如果您必須使用陣列公式，將其加速的一些好方法如下：

使用動態範圍名稱或結構化資料表參考，將儲存格數目降至最低。
將多個條件分割成每個資料列傳回 True 或 False 的協助程式公式資料行，然後參考 SUMIF 或陣列公式中的 Helper 資料行。這可能無法減少單一陣列公式的計算數目;不過，大部分時候，它會讓智慧型重新計算程式只重新計算協助程式資料行中需要重新計算的公式。
請考慮將所有條件串連在一起，成為單一條件，然後使用 SUMIF。
如果可以排序資料，請計算資料列群組，並將陣列公式限制為查看子集群組。

排定多重條件 SUMIFS、COUNTIFS 和其他 IFS 系列函式的優先順序

這些函式會依序從左至右評估每個條件。因此，先放置最嚴格的條件會更有效率，因此後續條件只需要查看最少的資料列數目。

考慮針對多重條件陣列公式使用 SUMPRODUCT 的選項

從 Excel 2007 開始，您應該一律使用SUMIFS、COUNTIFS和AVERAGEIFS函式，以及Excel 2016 MAXIFS和MINIFS函式，而不是盡可能使用SUMPRODUCT公式。

在舊版本中，使用 SUMPRODUCT 而不使用 SUM 陣列公式有幾個優點：

SUMPRODUCT 不需要使用 Ctrl+Shift+Enter 進行陣列輸入。
SUMPRODUCT 通常會比較快一點 (百分之 5 到 10)。

針對多重條件陣列公式使用 SUMPRODUCT ，如下所示：

  SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)

在此範例中， Condition1 和 Condition2 是條件運算式，例如 $A$1:$A$10000<=$Z4 。因為條件化運算式會傳回 True 或 False 而不是數字，必須在 SUMPRODUCT 函數中強制將其變成數字。若要這麼做，您可以使用兩個減號 (--) ，或新增 0 (+0) ，或乘以 1 (x1) 。使用 -- 比 +0 或 x1稍微快一點。

請注意，在條件化運算式及要加總的範圍中，所使用之範圍或陣列的大小和圖案必須相同，而且不能包含整個欄。

您也可以直接將 SUMPRODUCT 中的字詞相乘，而不是以逗號分隔：

  SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)

這通常比使用逗號語法稍微慢一點，而且如果要加總的範圍包含文字值，則會產生錯誤。然而，這會比較有彈性一點，因為當條件只有一欄時，要加總的範圍可能會有 (例如) 多欄。

使用 SUMPRODUCT 來相乘及新增範圍和陣列

在像加權平均計算這類的情況中，您需要將某數字範圍乘以另一個數字範圍，並加總結果。將逗號語法用於 SUMPRODUCT 會比輸入陣列的 SUM 快百分之 20 到 25。

  {=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)

這三個公式都會產生相同的結果，但第三個公式使用 SUMPRODUCT的逗號語法，只需要其他兩個公式所需的大約 77% 計算時間。

請留意潛在的陣列和函數計算阻礙

Excel 中的計算引擎已最佳化，可利用參照範圍的陣列公式和函數。然而，有時這些公式和函數會有一些不尋常的排列方式，而導致計算時間大幅增加。

如果您發現與陣列公式和範圍函數相關的計算阻礙，您應尋找下列項目：

部分重疊的參照。
參照在另一個陣列公式或範圍函數中計算之部分儲存格區塊的陣列公式和範圍函數。在時間序列分析中經常發生這種情況。
一組公式依列參照，而第二組公式依欄參照第一組。
涵蓋欄區塊的一大組單列陣列公式，而每欄底部都有 SUM 函數。

有效率地使用函式

函式會大幅擴充 Excel 的功能，但您使用這些函式的方式通常會影響計算時間。

避免單一執行緒函式

大部分的原生 Excel 函式都適用于多執行緒計算。不過，可能的話，請避免使用下列單一執行緒函式：

VBA 和自動化使用者定義函式 (UDF) ，但 XLL 型 UDF 可以是多執行緒
PHONETIC
有使用 format 或 address 引數的 CELL
INDIRECT
GETPIVOTDATA
CUBEMEMBER
CUBEVALUE
CUBEMEMBERPROPERTY
CUBESET
CUBERANKEDMEMBER
CUBEKPIMEMBER
CUBESETCOUNT
提供) (第五個參數的 sheet_name 位址
任何參照到樞紐分析表的資料庫函數 (DSUM、DAVERAGE 等等)
ERROR.TYPE
HYPERLINK

針對處理範圍的函式使用資料表

針對 SUM、SUMIF 和 SUMIFS 這些處理範圍的函數，計算時間與您加總或計數的已使用儲存格數目成正比。不會檢查未使用的儲存格，因此整個資料行參考相當有效率，但最好確保您不會包含比您需要更多使用的儲存格。請使用資料表，或計算子集範圍或動態範圍。

減少變動性函式

動態函數會減緩重新計算的速度，因為每次計算都會增加必須重新計算的公式數量。

通常，您可以減少動態函數的數量，方法是使用 INDEX 而非 OFFSET，並使用 CHOOSE 而不使用 INDIRECT。不過， OFFSET 是一個快速的函式，而且通常可以用在提供快速計算的創意方式中。

使用 C 或 C++ 使用者定義函數

以 C 或 C++ 程式設計且使用 C API (XLL 增益集函式的使用者定義函式，通常) 比使用 VBA 或自動化 (XLA 或自動化增益集開發的使用者定義函式) 更快執行。如需詳細資訊，請參閱，＜Developing Excel 2010 XLLs＞。

VBA 使用者定義函數的效能容易受其程式設計及呼叫方式的影響。

使用更快的 VBA 使用者定義函數

使用 Excel 公式計算和工作表函數通常比使用 VBA 使用者定義函數快。這是因為用於每個使用者定義函數呼叫的額外負荷很小，而將資訊從 Excel 傳輸至使用者定義函數的額外負荷很大。但是設計良好並呼叫的使用者定義函數會比複雜的陣列公式快很多。

請確定您已經將工作表儲存格的所有參照都放在使用者定義函數輸入參數中，而不是放在使用者定義函數的主體中，這樣就可以避免新增不必要的 Application.Volatile。

如果您必須有許多使用使用者定義函數的公式，請確定您處於手動計算模式，而且計算是從 VBA 起始。如果「不是」從 VBA 呼叫計算 (例如，在自動模式下，或是當您在手動模式下按 F9 鍵時)，VBA 使用者定義函數的計算速度會慢很多。當 Visual Basic 編輯器 (Alt+F11) 已開啟或已在目前的 Excel 會話中開啟時，尤其如此。

您可以設陷 F9 並將它重新導向至 VBA 計算副程式，如下所示。將此副程式新增至 Thisworkbook 模組。

  Private Sub Workbook_Open()
      Application.OnKey "{F9}", "Recalc"
  End Sub

將此副程式新增至標準模組。

  Sub Recalc()
      Application.Calculate
      MsgBox "hello"
  End Sub

自動化增益集 (Excel 2002 和更新版本中的使用者定義函式) 不會產生 Visual Basic 編輯器額外負荷，因為它們不會使用整合式編輯器。自動增益集中 Visual Basic 6 使用者定義函數的其他效能特性類似 VBA 函數。

如果您的使用者定義函數處理範圍內的每個儲存格，請將輸入宣告為範圍、將其指派給包含陣列的變異，並且在其上重複。如果您想要有效率地處理整欄參照，則必須建立輸入範圍的子集、在其與已使用範圍的交集處將它分開，如下列範例所示。

  Public Function DemoUDF(theInputRange as Range)
      Dim vArr as Variant
      Dim vCell as Variant
      Dim oRange as Range
      Set oRange=Union(theInputRange, theRange.Parent.UsedRange)
      vArr=oRange
      For Each vCell in vArr
          If IsNumeric(vCell) then DemoUDF=DemoUDF+vCell
      Next vCell
  End Function

如果您的使用者定義函數使用工作表函數或 Excel 物件模型方法來處理範圍，將範圍保持為物件變數，通常會比將所有資料從 Excel 傳輸至使用者定義函數有效率。

  Function uLOOKUP(lookup_value As Variant, lookup_array As Range, _
                   col_num As Variant, sorted As Variant, _
                   NotFound As Variant)
      Dim vAnsa As Variant
      vAnsa = Application.VLookup(lookup_value, lookup_array, _
                                  col_num, sorted)
      If Not IsError(vAnsa) Then
          uLOOKUP = vAnsa
      Else
          uLOOKUP = NotFound
      End If
  End Function

如果您的使用者定義函數在計算鏈結中提早呼叫，則可以將它當做未計算的引數傳遞。在使用者定義函數內，您可以針對包含公式的空白儲存格使用下列測試來偵測未計算的儲存格：

  If ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then

每次呼叫使用者定義函數，以及每次將資料從 Excel 傳輸至 VBA 時，都會有一個時間額外負荷。有時候一個多儲存格陣列公式使用者定義函數，就可以幫助您最小化這些額外負荷，它會將多個函數呼叫合併至單一函數，並且有多儲存格輸入範圍，會傳回答案範圍。

最小化 SUM 和 SUMIF 參考的儲存格範圍

Excel SUM 及 SUMIF 函數經常會用於大量儲存格。這些函數的計算時間與所涵蓋的儲存格數目成正比，所以請嘗試最小化函數參照的儲存格範圍。

使用萬用字元 SUMIF、COUNTIF、SUMIFS、COUNTIFS 和其他 IFS 函式

使用萬用字元？ (任何單一字元) ，並且 * 在 SUMIF、 COUNTIF、 SUMIFS、 COUNTIFS和其他 IFS 函數的一部分，在字母範圍準則中) 不 (任何字元或任意數目的字元。

針對最新期間和累計 SUM 選擇方法

有兩種方法可以執行日期區間及累計 SUM。假設您要累計 SUM 的數字在欄 A，而您想要欄 B 包含累計總和；您可以執行下列其中一項：

您可以在 B 資料行中建立公式，例如 =SUM($A$1:$A2) ，並視需要將它向下拖曳。 SUM 的開始儲存格錨定在 A1 中，但是因為完成儲存格有相對的列參照，所以會為每一列自動增加。
您可以在儲存格 B1 和 =$B1+$A2 儲存格 B2 中建立公式 =$A1 ，並視需要將其向下拖曳。這會將此列的數字新增到上一個累計 SUM。

若有 1,000 列，第一個方法會使 Excel 執行約 500,000 個計算，但是第二個方法會使 Excel 只執行約 2,000 個計算。

計運算元集總和

例如，當您在資料表 (有多個排序索引時，[區域) 內的月臺] 通常可以動態計算要在SUM 或 SUMIF函式中使用的資料列 (或) 資料行子集範圍的位址，以節省大量的計算時間。

若要計算資料列或資料行子集範圍的位址：

計算每個子集區塊的列數。
累計新增每個區塊的計數，以決定其開始列。
使用 OFFSET 搭配開始列和計數，以傳回子集範圍至只涵蓋列子集區塊的 SUM 或 SUMIF。

針對篩選的清單使用 SUBTOTAL

使用SUM 篩選清單的 SUBTOTAL 函數。 SUBTOTAL 函數很有用，因為它不同於 SUM，它會忽略下列事項：

從篩選清單得到的隱藏列。從 Excel 2003 開始，您也可以使 SUBTOTAL 忽略所有隱藏列，而不只是篩選列。
其他 SUBTOTAL 函數。

使用 AGGREGATE 函數

AGGREGATE 函式是一種強大且有效率的方式，可計算匯總資料 (的 19 種不同方法，例如 SUM、 MEDIAN、 PERCENTILE 和 LARGE) 。 AGGREGATE 有選項可忽略隱藏或篩選的資料列、錯誤值，以及巢狀 SUBTOTAL 和 AGGREGATE 函數。

避免使用 DFunctions

DFunction DSUM、DCOUNT、DAVERAGE 等等比同等陣列公式快非常多。 DFunction 的缺點是準則必須在個別範圍中，在許多情況下會不好使用及維護。從 Excel 2007 開始，您應使用 SUMIFS、COUNTIFS 及 AVERAGEIFS 函數來替代 DFunction。

建立更快的 VBA 宏

使用下列秘訣來建立更快速的 VBA 宏。

在程式碼執行時關閉基本資訊以外的所有專案

若要改善 VBA 宏的效能，請明確關閉程式碼執行時不需要的功能。通常，在程式碼執行之後重新計算或重繪是必要的，而且可以改善效能。在您的程式碼執行之後，將功能還原為其原始狀態。

VBA 巨集執行時，下列功能通常可以關閉：

Application.ScreenUpdating 關閉畫面更新。如果 Application.ScreenUpdating 設定為 False，Excel 就不會重新繪製畫面。當您的程式碼執行時，畫面會快速更新，而且使用者通常不需要查看每個更新。在程式碼執行之後更新螢幕一次，可改善效能。
Application.DisplayStatusBar 關閉狀態列。如果 Application.DisplayStatusBar 設定為 False，Excel 就不會顯示狀態列。狀態列設定與螢幕更新設定不同，因此即使螢幕未更新，您仍然可以顯示目前作業的狀態。不過，如果您不需要顯示每個作業的狀態，在程式碼執行時關閉狀態列也會改善效能。
Application.Calculation 切換為手動計算。如果 Application.Calculation 設定為 xlCalculationManual，Excel 只會在使用者明確起始計算時計算活頁簿。在自動計算模式中，Excel 會決定何時要計算。例如，每當與公式相關的儲存格值變更時，Excel 就會重新計算公式。如果您將計算模式切換為手動，您可以等到與公式相關聯的所有儲存格都更新之後，再重新計算活頁簿。只要在程式碼執行時必要時重新計算活頁簿，您就可以改善效能。
Application.EnableEvents 關閉事件。如果 Application.EnableEvents 設定為 False，Excel 就不會引發事件。如果有增益集接聽 Excel 事件，這些增益集會在記錄事件時取用電腦上的資源。如果增益集不需要記錄程式碼執行時發生的事件，關閉事件可改善效能。
ActiveSheet.DisplayPageBreaks 關閉分頁符號。如果 ActiveSheet.DisplayPageBreaks 設定為 False，Excel 就不會顯示分頁符號。您不需要在程式碼執行時重新計算分頁符號，而且在程式碼執行之後計算分頁符號可改善效能。

重要事項

程式碼執行之後，記得將此功能還原為原始狀態。

下列範例顯示您可以在 VBA 巨集執行時關閉的功能。

  ' Save the current state of Excel settings.
  screenUpdateState = Application.ScreenUpdating
  statusBarState = Application.DisplayStatusBar
  calcState = Application.Calculation
  eventsState = Application.EnableEvents
  ' Note: this is a sheet-level setting.
  displayPageBreakState = ActiveSheet.DisplayPageBreaks 

  ' Turn off Excel functionality to improve performance.
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ' Note: this is a sheet-level setting.
  ActiveSheet.DisplayPageBreaks = False

  ' Insert your code here.

  ' Restore Excel settings to original state.
  Application.ScreenUpdating = screenUpdateState
  Application.DisplayStatusBar = statusBarState
  Application.Calculation = calcState
  Application.EnableEvents = eventsState
  ' Note: this is a sheet-level setting
  ActiveSheet.DisplayPageBreaks = displayPageBreaksState

在單一作業中讀取和寫入大型資料區塊

明確減少在 Excel 與程式碼之間傳輸資料的次數，以最佳化程式碼。不要一次循環查看一個儲存格來取得或設定值，而是使用包含二維陣列的變異，依需要儲存值，以在一行中的整個儲存格範圍內取得或設定值。下列程式碼範例會比較這兩種方法。

下列程式碼範例顯示非最佳化程式碼，其一次循環查看一個儲存格來取得及設定儲存格 A1:C10000 的值。這些儲存格不包含公式。

  Dim DataRange as Range
  Dim Irow as Long
  Dim Icol as Integer 
  Dim MyVar as Double 
  Set DataRange=Range("A1:C10000") 

  For Irow=1 to 10000 
      For icol=1 to 3
          ' Read the values from the Excel grid 30,000 times.
          MyVar=DataRange(Irow,Icol) 
          If MyVar > 0 then 
              ' Change the value.
              MyVar=MyVar*Myvar 
              ' Write the values back into the Excel grid 30,000 times.
              DataRange(Irow,Icol)=MyVar
          End If 
      Next Icol 
  Next Irow

下列程式碼範例顯示最佳化程式碼，其使用陣列來同時取得及設定儲存格 A1:C10000 的所有值。這些儲存格不包含公式。

  Dim DataRange As Variant
  Dim Irow As Long 
  Dim Icol As Integer 
  Dim MyVar As Double 
  ' Read all the values at once from the Excel grid and put them into an array.
  DataRange = Range("A1:C10000").Value2 

  For Irow = 1 To 10000 
      For Icol = 1 To 3 
          MyVar = DataRange(Irow, Icol) 
          If MyVar > 0 Then 
              ' Change the values in the array.
              MyVar=MyVar*Myvar 
              DataRange(Irow, Icol) = MyVar 
          End If 
      Next Icol 
  Next Irow 
  ' Write all the values back into the range at once.
  Range("A1:C10000").Value2 = DataRange

使用。Value2 而非。值或。從 Excel 範圍讀取資料時的文字

.Text 會傳回儲存格的格式化值。速度很慢，如果使用者縮放，可能會傳回 ### ，而且可能會失去精確度。
.如果 範圍格式化為日期或貨幣，則值會傳回 VBA 貨幣或 VBA 日期變數。這速度很慢、可能會失去精確度，而且在呼叫工作表函式時可能會造成錯誤。
.Value2 速度很快，而且不會改變從 Excel 擷取的資料。

避免選取和啟用物件

選取和啟動物件比直接參照物件更需要大量處理。藉由直接參考 Range 或 Shape 等物件，您可以改善效能。下列程式碼範例會比較這兩種方法。

下列程式碼範例顯示非優化的程式碼，會選取使用中工作表上的每個 Shape，並將文字變更為「Hello」。

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).Select
      Selection.Text = "Hello"
  Next i

下列程式碼範例示範直接參考每個 Shape 並將文字變更為「Hello」的優化程式碼。

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
  Next i

使用這些額外的 VBA 效能優化

下列是您可以在 VBA 程式碼中使用的其他效能最佳化清單：

直接指派陣列至 Range，以傳回結果。
宣告含明確類型的變數，以避免在程式碼執行期間判定資料類型的額外負荷，可能一個循環好幾次。
針對您經常在程式碼中使用的簡單函數，自己在 VBA 中實作函數，而不要使用 WorksheetFunction 物件。如需詳細資訊，請參閱使用更快的 VBA 使用者定義函數。
使用 Range.SpecialCells 方法來縮小範圍，以減少程式碼要互動的儲存格數目。
如果您在 XLL SDK 中使用 C API 來實作您的功能，請考慮效能提升。如需詳細資訊，請參閱＜Excel 2010 XLL SDK 文件＞。

考慮 Excel 檔案格式的效能和大小

從 Excel 2007 開始，與舊版相較，Excel 包含了各式各樣的檔案格式。忽略宏、範本、增益集、PDF 和 XPS 檔案格式變化，三個主要格式為 XLS、XLSB 和 XLSX。

XLS 格式

XLS 格式是與舊版相同的格式。當您使用此格式時，您受限於 256 欄及 65,536 列。當您以 XLS 格式儲存 Excel 2007 或 Excel 2010 活頁簿時，Excel 會執行相容性檢查。檔案大小幾乎與舊版本一樣 (可能會儲存一些其他資訊)，而效能稍微比舊版慢一點。 Excel 未依儲存格計算順序來執行的任何多執行緒最佳化，都不會以 XLS 格式儲存。因此，以 XLS 格式儲存活頁簿、關閉再重新開啟活頁簿之後，活頁簿的計算會比較慢。
XLSB 格式

從 Excel 2007 開始，XLSB 是二進位格式。其結構化為包含許多二進位檔案的壓縮資料夾。它比 XLS 格式更精簡，但壓縮量取決於活頁簿的內容。例如，10 個活頁簿顯示的大小縮減係數範圍從 2 到 8，平均縮減係數為 4。從 Excel 2007 開始，開啟和儲存效能只比 XLS 格式慢一點點。
XLSX 格式

從 Excel 2007 開始，XLSX 是 XML 格式，而且從 Excel 2007 開始是預設格式。 XLSX 格式是包含許多 XML 檔案的壓縮資料夾 (如果您將副檔名變更為 .zip，您可以開啟壓縮的資料夾，並檢查其內容) 。通常 XLSX 格式建立的檔案會比 XLSB 格式大 (平均大 1.5 倍)，但還是比 XLS 檔案小非常多。您應預期開啟和儲存時間會比 XLSB 檔案稍微長一點。

開啟、關閉和儲存活頁簿

您可能會發現開啟、關閉及儲存活頁簿比加以計算慢很多。有時候這只是因為您有大型活頁簿，但也可能是其他原因。

如果您有一或多個活頁簿開啟及關閉的速度慢到不合理的地步，可能是下列問題所造成。

暫存檔

暫存檔可以在 Windows 95、Windows 98 和 Windows ME) 中的 \Windows\Temp 目錄 (中累積，或在 Windows 2000 和 Windows XP) 中累積 \Documents and Settings\User Name\Local Settings\Temp 目錄 (。 Excel 會為活頁簿和開啟的活頁簿所使用的控制項建立這些檔案。軟體安裝程式也會建立暫存檔。如果 Excel 因故停止回應，您可能需要刪除這些檔案。

太多暫存檔案可能會造成問題，因此您應該偶爾將它們清除。不過，如果您已安裝需要重新開機電腦的軟體，但尚未重新開機，則應該在刪除暫存檔之前重新開機。

一個開啟暫存檔的簡單方法，就是從 Windows 的 [開始] 功能表進行：按一下 [開始]，然後按一下 [執行]。在文字方塊中，輸入 %temp%，然後按一下 [確定]。

追蹤共用活頁簿中的變更

追蹤共用活頁簿中的變更會使活頁簿檔案大小快速增加。
分散的交換檔

請確定 Windows 分頁檔案位在有很多空間的磁碟上，而且您會定期重組磁碟。
具有密碼保護結構的活頁簿

使用密碼 ([工具] 功能表 > [保護> 活頁簿] 來保護其結構的活頁簿> 會輸入選擇性密碼，) 開啟並關閉速度比沒有選擇性密碼受保護的密碼慢很多。
已使用的範圍問題

過大的已使用範圍會導致開啟緩慢，並增加檔案大小，尤其如果是由具有非標準高度或寬度的隱藏列或欄所導致的話。如需已使用範圍問題的詳細資訊，請參閱最小化已使用的範圍。
工作表上大量的控制項

工作表上的大量控制項 (核取方塊、超連結等等) 會因為所使用的暫存檔數目，而減緩開啟活頁簿的速度。這也可能會在 WAN (甚至 LAN) 上開啟或儲存活頁簿時造成問題。如果您有這個問題，則應考慮重新設計活頁簿。
其他活頁簿的大量連結

若有可能，請先開啟您要連結的活頁簿，再開啟包含連結的活頁簿。通常開啟活頁簿會比從關閉的活頁簿讀取連結快。
病毒掃描器設定

有些病毒掃描器設定會導致開啟、關閉或儲存時發生問題或速度變慢，尤其是在伺服器上。如果您認為這可能是問題所在，請嘗試暫時關閉病毒掃描器。
計算緩慢導致開啟和儲存速度變慢

在某些情況下，Excel 會在開啟或儲存活頁簿時重新計算。如果您活頁簿的計算時間很長且造成問題，請確定您已將計算設定為手動，並考慮關閉計算，再儲存選項， (工具>選項>計算) 。
工具列檔案 (.xlb)

檢查工具列檔案大小。一般工具列檔案介於 10 KB 到 20 KB。您可以使用 Windows 搜尋搜尋來尋找 XLB 檔案 *.xlb 。每個使用者都有唯一的 XLB 檔案。新增、變更或自訂工具列會增加 toolbar.xlb 檔案的大小。刪除檔案會移除所有工具列自訂內容 (將它更名為 "toolbar.OLD" 比較安全)。您下次開啟 Excel 時，就會建立新的 XLB 檔案。

進行其他效能優化

您可以在下欄區域中改善效能。

PivotTables

樞紐分析表提供摘要大量資料的有效方法。
- 做為最終結果的總計。如果您需要在活頁簿的最後結果中產生總計和小計，請嘗試使用樞紐分析表。
- 做為中繼結果的總計。樞紐分析表是產生摘要報表很棒的方法，但是建立公式時，請嘗試避免將樞紐分析表結果用作計算鏈中間的總計和小計，除非您可以確定下列狀況：
- 樞紐分析表已在計算期間正確重新整理。
- 樞紐分析表尚未變更，所以還看得到資訊。
如果您還是想要使用樞紐分析表做為中間結果，請使用 GETPIVOTDATA 函數。
條件式格式和資料驗證

條件化格式及資料驗證是很有用的功能，但是大量使用會大幅降低計算速度。如果顯示儲存格，則會在每次計算時，以及重新整理包含條件化格式的儲存格顯示時，評估每個條件式格式公式。 Excel 物件模型有 Worksheet.EnableFormatConditionsCalculation 屬性，所以您可以啟用或停用條件化格式的計算。
定義的名稱

已定義的名稱是 Excel 中最強的功能之一，但確實會花費額外的計算時間。使用參照其他工作表的名稱會增加計算程序的複雜度。此外，您也應該嘗試避免使用巢狀名稱 (參照其他名稱的名稱)。

因為每次計算有參照名稱的公式時，就會計算該名稱，所以應避免將計算密集的公式或函數放在已定義的名稱中。在這些案例中，將計算密集的公式或函數放在某處的備用儲存格中，並改為參照該儲存格 (直接參照或使用名稱)，會快非常多。
只偶爾使用的公式

很多活頁簿包含的大量公式和查閱，都有關將輸入資料放進適當圖案以供計算，或是要用作防止資料大小或圖案變更的防範措施。如果您有僅偶爾使用的公式區塊，可以複製並貼上特殊值，以暫時消除公式，或是將其放在個別、極少開啟的活頁簿中。由於工作表錯誤常常都是因為沒注意到公式已轉換成值而導致，所以個別的活頁簿方法會比較好。
使用足夠的記憶體

32 位版本的 Excel 最多可以使用 2 GB 的 RAM 或最多 4 GB 的 RAM，用於 Excel 2013 和 2016 的大型位址感知 32 位版本。不過，執行 Excel 的電腦也需要記憶體資源。因此，如果您的電腦上只有 2 GB 的 RAM，Excel 就無法利用完整的 2 GB，因為記憶體的一部分會配置給作業系統和其他正在執行的程式。若要將 32 位電腦上的 Excel 效能優化，建議電腦至少有 3 GB 的 RAM。

64 位版本的 Excel 沒有 2 GB 或最多 4 GB 的限制。 For more information, see the "Large data sets and the 64-bit version of Excel" section in Excel performance: Performance and limit improvements.

總結

本文涵蓋了優化 Excel 功能的方法，例如連結、查閱、公式、函式和 VBA 程式碼，以避免常見的阻礙並改善效能。

請參閱

支援和意見反應

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

共用方式為