Excel 效能:最佳化效能阻礙的秘訣Excel performance: Tips for optimizing performance obstructions

適用於: Excel |Excel 2013 |Office 2016 |VBAApplies to: Excel | Excel 2013 | Office 2016 | VBA

請遵循這些最佳化許多常出現在 Excel 中的效能阻礙的秘訣。Follow these tips for optimizing many frequently occurring performance obstructions in Excel.

了解如何改善效能相關的參照及連結類型。Learn how to improve performance related to types of references and links.

請勿使用向前參照及向後參照Do not use forward referencing and backward referencing

為增加明確性並避免錯誤,請設計公式,使其不要向前參照 (向右或向下) 其他公式或儲存格。To increase clarity and avoid errors, design your formulas so that they do not refer forward (to the right or below) to other formulas or cells. 向前參照通常不會影響計算效能,除非是在活頁簿第一次計算的極端狀況下,如果有很多公式需要延遲其計算,可能會花比較長的時間來建立合理的計算順序。Forward referencing usually does not affect calculation performance, except in extreme cases for the first calculation of a workbook, where it might take longer to establish a sensible calculation sequence if there are many formulas that need to have their calculation deferred.

最小化使用與反覆運算的循環參照Minimize use of circular references with iteration

計算與反覆運算的循環參照很慢,因為需要多個計算,以及這些計算方法是單一執行緒。Calculating circular references with iterations is slow because multiple calculations are needed, and these calculations are single-threaded. 經常您可以 「 unroll 「 循環參照,因此不再需要反覆運算使用代數。Frequently you can "unroll" the circular references by using algebra so that iterative calculation is no longer needed. 例如,在現金流量及感興趣的計算,嘗試計算利息現金流量、 計算利息的再計算包括利息的現金流量。For example, in cash flow and interest calculations, try to calculate the cash flow before interest, calculate the interest, and then calculate the cash flow including the interest.

Excel 計算循環參照工作表的-工作表不考慮到 「 等待相依性。Excel calculates circular references sheet-by-sheet without considering dependencies. 因此,如果您的循環參照跨多個工作表,計算速度通常會很慢。Therefore, you usually get slow calculation if your circular references span more than one worksheet. 請嘗試將循環計算移到單一工作表上,或最佳化工作表計算順序,以避免不必要的計算。Try to move the circular calculations onto a single worksheet or optimize the worksheet calculation sequence to avoid unnecessary calculations.

在反覆運算開始之前,Excel 必須重新計算活頁簿,以識別所有循環參照及其相依項。Before the iterative calculations start, Excel must recalculate the workbook to identify all the circular references and their dependents. 此程序等於兩次或三次反覆運算。This process is equal to two or three iterations of the calculation.

在識別循環參照及其相依項之後,每次反覆運算都需要 Excel 不只計算循環參照中的所有儲存格,也要計算相依於循環參照鏈中之儲存格的任何儲存格,以及動態儲存格及其相依項。After the circular references and their dependents are identified, each iteration requires Excel to calculate not only all the cells in the circular reference, but also any cells that depend on the cells in the circular reference chain, together with volatile cells and their dependents. 如果您有相依於循環參照中之儲存格的複雜計算,則將其隔離至已關閉的個別活頁簿,在循環計算收斂之後,再將它開啟以重新計算,這樣會比較快。If you have a complex calculation that depends on cells in the circular reference, it can be faster to isolate this into a separate closed workbook and open it for recalculation after the circular calculation has converged.

減少循環計算中的儲存格數目以及這些儲存格所花費的計算時間是很重要的。It is important to reduce the number of cells in the circular calculation and the calculation time that is taken by these cells.

避免活頁簿之間的連結時,它會可能;它們可能會變慢、 容易中斷,而且不一定可以輕易找出並修正。Avoid inter-workbook links when it is possible; they can be slow, easily broken, and not always easy to find and fix.

使用較少的較大活頁簿通常 (但不是一定) 會比使用很多較小的活頁簿好。Using fewer larger workbooks is usually, but not always, better than using many smaller workbooks. 這部分例外狀況可能會有許多會所以很少重新計算的合理將它們放在不同的活頁簿中的前端計算時或當您有足夠的 RAM。Some exceptions to this might be when you have many front-end calculations that are so rarely recalculated that it makes sense to put them in a separate workbook, or when you have insufficient RAM.

請嘗試使用在關閉的活頁簿上運作之簡單直接的儲存格參照。Try to use simple direct cell references that work on closed workbooks. 這麼做可以避免在重新計算「任何」** 活頁簿時,重新計算「所有」** 連結的活頁簿。By doing this, you can avoid recalculating all your linked workbooks when you recalculate any workbook. 此外,您可以看到 Excel 具有讀取關閉的活頁簿,請務必經常進行偵錯及稽核活頁簿中的值。Also, you can see the values Excel has read from the closed workbook, which is frequently important for debugging and auditing the workbook.

如果您無法避免使用連結的活頁簿,請嘗試讓它們全部開啟,而不要關閉,並且先開啟連結目標活頁簿,再開啟連結來源活頁簿。If you cannot avoid using linked workbooks, try to have them all open instead of closed, and open the workbooks that are linked to before you open the workbooks that are linked from.

使用很多工作表可以讓您的活頁簿更易於使用,但是計算對其他工作表的參照,通常會比計算工作表中的參照慢。Using many worksheets can make your workbook easier to use, but generally it is slower to calculate references to other worksheets than references within worksheets.

最小化使用的範圍Minimize the used range

若要儲存的記憶體,並減少檔案大小,Excel 會嘗試用在工作表上儲存只將區域的相關資訊。To save memory and reduce file size, Excel tries to store information about only the area on a worksheet that was used. 這就叫做「使用的範圍」**。This is called the used range. 有時候各種編輯和格式化作業會延伸使用的範圍,並大幅超出您目前考慮使用的範圍。Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. 這樣會導致效能阻礙及檔案大小阻礙。This can cause performance obstructions and file-size obstructions.

您可以使用 Ctrl + End 來檢查工作表上的顯示已用的範圍。You can check the visible used range on a worksheet by using Ctrl+End. 其中,這是過多,您應該考慮刪除所有的列和資料行下方和右邊的使用您實際最後一個儲存格,並將儲存活頁簿。Where this is excessive, you should consider deleting all the rows and columns below and to the right of your real last used cell, and then saving the workbook. 請先建立備份複本。Create a backup copy first. 如果您有公式的範圍延伸至或參照所刪除區域,這些範圍就會降低大小或變更為 [#N/A]****。If you have formulas with ranges that extend into or refer to the deleted area, these ranges will be reduced in size or changed to #N/A.

允許額外的資料Allow for extra data

如果您經常新增資料列或欄至工作表,則需要找出一個方法,讓您的 Excel 公式自動參照新的資料區域,而不要每次都嘗試尋找及變更您的公式。When you frequently add rows or columns of data to your worksheets, you need to find a way of having your Excel formulas automatically refer to the new data area, instead of trying to find and change your formulas every time.

若要這麼做,您可以在公式中使用大幅超出目前資料邊界的大範圍。You can do this by using a large range in your formulas that extends well beyond your current data boundaries. 然而,這樣會導致在某些情況下計算的效率不佳,而且也很難維護,因為刪除列和欄會在您不注意的情況下縮減範圍。However, this can cause inefficient calculation under certain circumstances, and it is difficult to maintain because deleting rows and columns can decrease the range without you noticing.

從 Excel 2007 開始,您可以使用結構化的資料表參照,這會自動擴展和收縮當所參照之資料表的大小來增加或減少。Starting in Excel 2007, you can use structured table references, which automatically expand and contract as the size of the referenced table increases or decreases.

此解決方案有幾項優點:This solution has several advantages:

  • 較少的效能缺點存在比整欄參照和動態範圍的替代方案。Fewer performance disadvantages exist than the alternatives of whole column referencing and dynamic ranges.

  • 若要在單一工作表上有多個資料表的資料,會很容易。It is easy to have multiple tables of data on a single worksheet.

  • 內嵌在資料表中的公式也會隨資料擴充或縮減。Formulas that are embedded in the table also expand and contract with the data.

或者,使用整欄及列參照Alternatively, use whole column and row references

另一個方法是使用整欄參照,例如 $ a: $AAn alternative approach is to use a whole column reference, for example $A:$A. 此參考資料傳回的所有資料列中 a 欄。因此,您可以新增的資料量,而且參照永遠會包含它。This reference returns all the rows in Column A. Therefore, you can add as much data as you want, and the reference will always include it.

這個解決方案有優點也有缺點:This solution has both advantages and disadvantages:

  • 很多 Excel 內建函數 (SUMSUMIF) 都會有效率地計算整欄參照,因為它們會自動辨識該欄中上次使用的列。Many Excel built-in functions (SUM, SUMIF) calculate whole column references efficiently because they automatically recognize the last used row in the column. 然而,陣列計算函數 (如 SUMPRODUCT) 既無法處理整欄參照,也無法計算欄中的所有儲存格。However, array calculation functions like SUMPRODUCT either cannot handle whole column references or calculate all the cells in the column.

  • 使用者定義的函數執行動作不會自動辨識] 欄中的最後一個使用資料列並,因此,經常計算整欄參照無效率。User-defined functions do not automatically recognize the last-used row in the column and, therefore, frequently calculate whole column references inefficiently. 不過,很容易程式使用者定義的函數,讓他們辨識上次使用的資料列。However, it is easy to program user-defined functions so that they recognize the last-used row.

  • 當單一工作表上有多個資料表的資料時,會很難使用整欄參照。It is difficult to use whole column references when you have multiple tables of data on a single worksheet.

  • 在 Excel 2007 及更新版本,陣列公式可以處理整個欄參照,但這麼做會強制計算] 欄中,包括空白儲存格的所有儲存格。In Excel 2007 and later versions, array formulas can handle whole-column references, but this forces calculation for all the cells in the column, including empty cells. 這樣計算會很慢,尤其是計算 1 百萬列時。This can be slow to calculate, especially for 1 million rows.

或者,使用動態範圍Alternatively, use dynamic ranges

使用OFFSET索引COUNTA函數定義中的某個具名的範圍,您可以讓指定的範圍參照動態擴展和收縮的區域。By using the OFFSET or INDEX and COUNTA functions in the definition of a named range, you can make the area that the named range refers to dynamically expand and contract. 例如,建立已定義的名稱,使用下列其中一個下列公式:For example, create a defined name using one of the following formulas:

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

當您在公式中使用動態範圍名稱時,它會自動擴充,以包含新項目。When you use the dynamic range name in a formula, it automatically expands to include new entries.

使用動態範圍的索引公式最好通常位移公式因為位移的變動函式,將會在每個重新計算計算缺點。Using the INDEX formula for a dynamic range is generally preferable to the OFFSET formula because OFFSET has the disadvantage of being a volatile function that will be calculated at every recalculation.

因為內動態範圍公式COUNTA函數還必須檢查多個資料列,則會降低效能。Performance decreases because the COUNTA function inside the dynamic range formula must examine many rows. 您可以減到最小此效能降低儲存在不同的儲存格內公式的COUNTA一部分,或定義名稱,然後參照到的儲存格或儲存格動態範圍中的名稱:You can minimize this performance decrease by storing the COUNTA part of the formula in a separate cell or defined name, and then referring to the cell or name in the dynamic range:

 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函數來建構動態範圍,但間接是動態,一律會計算單一執行緒。You can also use functions such as INDIRECT to construct dynamic ranges, but INDIRECT is volatile and always calculates single-threaded.

動態範圍具有下列優缺點:Dynamic ranges have the following advantages and disadvantages:

  • 動態範圍很適合用來限制陣列公式所執行的計算數量。Dynamic ranges work well to limit the number of calculations performed by array formulas.

  • 將多重動態範圍,在單一欄需要特殊用途的計數函數。Using multiple dynamic ranges within a single column requires special-purpose counting functions.

  • 使用很多動態範圍會降低效能。Using many dynamic ranges can decrease performance.

改善查閱計算時間Improve lookup calculation time

在 Office 365 1809 版及更新版本中,Excel 針對未排序的資料進行完全符合的 VLOOKUP、HLOOKUP 和 MATCH,在查詢相同資料表範圍中的多個資料行 (使用 HLOOKUP 時為資料列) 時較以往的速度更快。In Office 365 version 1809 and later, Excel's VLOOKUP, HLOOKUP, and MATCH for exact match on unsorted data is much faster than ever before when looking up multiple columns (or rows with HLOOKUP) from the same table range.

也就是說,舊的 Excel 版本中,針對查閱持續處於經常大量的計算阻礙。That said, for earlier Excel versions, Lookups continue to be frequently significant calculation obstructions. 幸好有很多方式可以改善查閱計算時間。Fortunately, there are many ways of improving lookup calculation time. 如果您使用完全相符選項,則函數的計算時間與找到相符項之前所掃描的儲存格數目成正比。If you use the exact match option, the calculation time for the function is proportional to the number of cells scanned before a match is found. 若是大範圍的查閱,這個時間會很可觀。For lookups over large ranges, this time can be significant.

在已排序資料上使用 VLOOKUPHLOOKUPMATCH 的大約符合選項,查閱時間會很快,而且不會因為您查閱的範圍長度而大幅增加。Lookup time using the approximate match options of VLOOKUP, HLOOKUP, and MATCH on sorted data is fast and is not significantly increased by the length of the range you are looking up. 特性與二進位搜尋相同。Characteristics are the same as binary search.

了解查閱選項Understand lookup options

請確定您了解在比對VLOOKUPHLOOKUP中的相符項目類型和 range-lookup 選項。Ensure that you understand the match-type and range-lookup options in MATCH, VLOOKUP, and HLOOKUP.

下列程式碼範例顯示 MATCH 函數的語法。The following code example shows the syntax for the MATCH function. 如需詳細資訊,請參閱 WorksheetFunction 物件的 Match 方法。For more information, see the Match method of the WorksheetFunction object.

  MATCH(lookup value, lookup array, matchtype)
  • Matchtype = 1會傳回最大值時,符合小於或等於查閱值查閱陣列為遞增排序起 (大約符合)。Matchtype=1 returns the largest match less than or equal to the lookup value when the lookup array is sorted ascending (approximate match). 如果查閱陣列不遞增排序,相符項目會傳回錯誤的答案。If the lookup array is not sorted ascending, MATCH will return an incorrect answer. [預設] 選項的近似相符項目排序遞增The default option is approximate match sorted ascending.

  • Matchtype=0 要求完全相符,並假設資料已排序。Matchtype=0 requests an exact match and assumes that the data is not sorted.

  • 如果查閱陣列為遞減排序,則 Matchtype=-1 會傳回大於或等於查閱值的最小相符項 (大約符合)。Matchtype=-1 returns the smallest match greater than or equal to the lookup value if the lookup array is sorted descending (approximate match).

下列程式碼範例顯示 VLOOKUPHLOOKUP 函數的語法。The following code example shows the syntax for the VLOOKUP and HLOOKUP functions. 如需詳細資訊,請參閱 WorksheetFunction 物件的 VLOOKUPHLOOKUP 方法。For more information, see the VLOOKUP and HLOOKUP methods of the WorksheetFunction object.

  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=TRUE returns the largest match less than or equal to the lookup value (approximate match). 此為預設選項。This is the default option. 資料表陣列必須遞增排序。Table array must be sorted ascending.

  • Range-lookup=FALSE 要求完全相符,並假設資料已排序。Range-lookup=FALSE requests an exact match and assumes the data is not sorted.

請儘可能避免在未排序的資料上執行查閱,因為會很慢。Avoid performing lookups on unsorted data where possible because it is slow. 如果排序您的資料,但您想要完全相符,請參閱 <使用兩個查閱含遺失值的排序資料。If your data is sorted, but you want an exact match, see Use two lookups for sorted data with missing values.

使用索引及相符項目或而不是 VLOOKUP 位移Use INDEX and MATCH or OFFSET instead of VLOOKUP

請嘗試使用 INDEXMATCH 函數,而不要使用 VLOOKUPTry using the INDEX and MATCH functions instead of VLOOKUP. 雖然VLOOKUP會稍微快一點 (大約 5%更快速地)、 更簡單,並使用較少的記憶體超過相符項目索引,或位移,額外的彈性的組合,比對索引優惠通常可讓您大幅節省時間。Although VLOOKUP is slightly faster (approximately 5 percent faster), simpler, and uses less memory than a combination of MATCH and INDEX, or OFFSET, the additional flexibility that MATCH and INDEX offer often enables you to significantly save time. 例如,您可以將完全 MATCH 的結果儲存在儲存格中,然後在數個 INDEX 陳述式中重複使用。For example, you can store the result of an exact MATCH in a cell and reuse it in several INDEX statements.

INDEX 函數很快,而且是靜態函數 (可加速重新計算)。The INDEX function is fast and is a non-volatile function, which speeds up recalculation. OFFSET函數也是 fast;不過,它是變動函式,與計算鏈的處理程序所花費的時間,有時會大幅增加。The OFFSET function is also fast; however, it is a volatile function, and it sometimes significantly increases the time taken to process the calculation chain.

VLOOKUP 轉換成 INDEXMATCH 很簡單。It is easy to convert VLOOKUP to INDEX and MATCH. 下列兩個陳述式會傳回相同的答案:The following two statements return the same answer:

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

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

加速查閱Speed up lookups

因為完全相符查閱會很慢,所以請考慮使用下列選項來增進效能:Because exact match lookups can be slow, consider the following options for improving performance:

  • 使用一個工作表。Use one worksheet. 將查閱和資料保存在相同工作表中會比較快。It is faster to keep lookups and data on the same sheet.

  • 在可能的情況下,先 SORT 資料 (SORT 很快),並使用大約符合。When you can, SORT the data first (SORT is fast), and use approximate match.

  • 當您必須使用完全相符查閱時,請將所要掃描的儲存格範圍限制在最少的情況。When you must use an exact match lookup, restrict the range of cells to be scanned to a minimum. 使用資料表及結構化的參照或動態範圍名稱,而非大量資料列或欄的參照。Use tables and structured references or dynamic range names rather than referring to a large number of rows or columns. 有時候您可以預先計算查閱的範圍下限及範圍上限。Sometimes you can pre-calculate a lower-range limit and upper-range limit for the lookup.

使用兩個查閱含遺失值的排序資料Use two lookups for sorted data with missing values

兩個近似的相符項目會大幅比一個完全相符查閱透過多個資料列。Two approximate matches are significantly faster than one exact match for a lookup over more than a few rows. (Breakeven 點是關於 10-20 個資料列。)(The breakeven point is about 10-20 rows.)

如果您可以將資料排序,但仍無法使用大約符合因為您無法確定所查閱的值存在於查閱範圍,您可以使用此公式:If you can sort your data but still cannot use approximate match because you cannot be sure that the value you are looking up exists in the lookup range, you can use this formula:

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

在查閱欄本身執行大約查閱時,公式的第一部分有效。The first part of the formula works by doing an approximate lookup on the lookup column itself.

  VLOOKUP(lookup_val ,lookup_array,1,True)

您可以檢查如果來自查閱欄的答案與查閱值 (在這種情況中您必須完全符合) 相同使用下列公式:You can check if the answer from the lookup column is the same as the lookup value (in which case you have an exact match) by using the following formula:

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

此公式會傳回 True,如果您已經找到完全相符,因此您可以再次執行大約查閱,但這段時間,請從您想要的資料行傳回答案。If this formula returns True, you have found an exact match, so you can do the approximate lookup again, but this time, return the answer from the column you want.

  VLOOKUP(lookup_val, lookup_array, column, True)

如果來自查閱欄的答案與查閱值不相符,您有遺失值,而且此公式會傳回"notexist"。If the answer from the lookup column did not match the lookup value, you have a missing value, and the formula returns "notexist".

請注意,如果您查閱的值小於清單中的最小值,則會收到錯誤。Be aware that if you look up a value smaller than the smallest value in the list, you receive an error. 若要處理此錯誤,您可以使用 IFERROR,或新增一個小測試值至清單。You can handle this error by using IFERROR, or by adding a small test value to the list.

含遺失值的未排序資料所使用 IFERROR 函數Use IFERROR function for unsorted data with missing values

如果您必須使用完全相符查閱上未排序的資料,而且您無法確定是否存在於查閱值,您通常必須處理如果找到不相符,則傳回 # n/A。If you must use exact match lookup on unsorted data, and you cannot be sure whether the lookup value exists, you often must handle the #N/A that is returned if no match is found. 從 Excel 2007 開始,您可以使用IFERROR函數,也就是簡易且快速。Beginning with Excel 2007, you can use the IFERROR function, which is both simple and fast.

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

在舊版中,有一個簡單但很慢的方法,就是使用包含兩個查閱的 IF 函數。In earlier versions, a simple but slow way is to use an IF function that contains two lookups.

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

如果您使用一次完全 MATCH,將結果儲存在儲存格中,然後先測試結果,再執行 INDEX,就可以避免雙重完全查閱。You can avoid the double exact lookup if you use exact MATCH once, store the result in a cell, and then test the result before doing an INDEX.

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

如果您無法使用兩個儲存格,請使用 COUNTIFIf you cannot use two cells, use COUNTIF. 這通常會比完全相符查閱快。It is generally faster than an exact match lookup.

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

使用多個資料行上的完全相符查閱相符項目和索引Use MATCH and INDEX for exact match lookups on multiple columns

您可以常常多次重複使用所儲存的完全 MATCHYou can often reuse a stored exact MATCH many times. 例如,如果您在多個結果欄上執行完全查閱,您可以使用一個 MATCH 和很多個 INDEX 陳述式,而不要使用很多個 VLOOKUP 陳述式,以節省時間。For example, if you are doing exact lookups on multiple result columns, you can save time by using one MATCH and many INDEX statements rather than many VLOOKUP statements.

新增額外資料欄的相符項目以儲存結果 (stored_row),並針對每個結果欄使用下列項目:Add an extra column for the MATCH to store the result (stored_row), and for each result column use the following:

  INDEX(Lookup_Range,stored_row,column_number)

或者,您也可以在陣列公式中使用 VLOOKUPAlternatively, you can use VLOOKUP in an array formula. (必須將陣列公式輸入使用 Ctrl +-Shift + enter 鍵。(Array formulas must be entered by using Ctrl+-Shift+Enter. Excel 會將新增 {和} 若要顯示這是一個陣列公式)。Excel will add the { and } to show you that this is an array formula).

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

使用一組連續列或欄的索引Use INDEX for a set of contiguous rows or columns

您也可以從一項查閱作業傳回很多儲存格。You can also return many cells from one lookup operation. 若要查閱數個連續欄,您可以在陣列公式中使用 INDEX 函數,一次傳回多欄 (使用 0 為欄數)。To look up several contiguous columns, you can use the INDEX function in an array formula to return multiple columns at once (use 0 as the column number). 您也可以使用 INDEX 函數一次傳回多列。You can also use the INDEX function to return multiple rows at one time.

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

這會從先前 MATCH 陳述式所建立的儲存列傳回欄 A 到欄 J。This returns column A to column J from the stored row created by a previous MATCH statement.

使用可傳回矩形區塊的儲存格的相符項目Use MATCH to return a rectangular block of cells

您可以使用 MATCHOFFSET 函數來傳回矩形區塊的儲存格。You can use the MATCH and OFFSET functions to return a rectangular block of cells.

二維查閱使用比對和索引Use MATCH and INDEX for two-dimensional lookup

有效率地用途的二維資料表查閱使用個別查閱表格中的欄與列上,使用INDEX函數以及兩個內嵌MATCH函數,一個用於列,另一個資料行。You can efficiently do a two-dimensional table lookup by using separate lookups on the rows and columns of a table by using an INDEX function with two embedded MATCH functions, one for the row and one for the column.

子集範圍用於多索引查閱Use a subset range for multiple-index lookup

在大型工作表中,您可能經常需要使用多個索引,例如查閱國家/地區中的產品磁碟區查閱。In large worksheets, you may frequently need to look up by using multiple indexes, such as looking up product volumes in a country. 若要這麼做,您可以使用串連的查閱值來串連索引並執行查閱。To do this, you can concatenate the indexes and perform the lookup by using concatenated lookup values. 不過這很沒有效率,有兩個原因:However, this is inefficient for two reasons:

  • 串連字串是需要大量計算的作業。Concatenating strings is a calculation-intensive operation.

  • 查閱會涵蓋很大的範圍。The lookup will cover a large range.

更有效率 (例如,藉由尋找第一個及最後一列的國家/地區],然後查閱該子集範圍內的產品) 計算查閱的範圍子集。It is often more efficient to calculate a subset range for the lookup (for example, by finding the first and last row for the country, and then looking up the product within that subset range).

請考慮三維查閱選項Consider options for three-dimensional lookup

除了列和欄之外,若要查閱所要使用的資料表,您可以使用下列技巧,將重點放在如何進行 Excel 查閱或選擇資料表。To look up the table to use in addition to the row and the column, you can use the following techniques, focusing on how to make Excel look up or choose the table.

如果您想要查閱 (第三維) 的每個表格儲存為一組具名結構化資料表、 範圍名稱,或文字字串,代表範圍的表格,您可以使用選擇INDIRECT函數。If each table that you want to look up (the third dimension) is stored as a set of named structured tables, range names, or as a table of text strings that represent ranges, you might be able to use the CHOOSE or INDIRECT functions.

  • 使用 CHOOSE 及範圍名稱會是個有效率的方法。Using CHOOSE and range names can be an efficient method. CHOOSE 為非動態,但最適合相對較小的資料表數目。CHOOSE is not volatile, but it is best-suited to a relatively small number of tables. 此範例會動態使用TableLookup_Value若要選擇哪個範圍名稱 (TableName1, TableName2, ...) 要用於查閱表格。This example dynamically uses TableLookup_Value to choose which range name (TableName1, TableName2, ...) to use for the lookup table.

      INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
      MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
    
  • 下列範例會使用INDIRECT函數和TableLookup_Value以動態方式建立要用於查閱表格的工作表名稱。The following example uses the INDIRECT function and TableLookup_Value to dynamically create the sheet name to use for the lookup table. 此方法的優點是很簡單,而且能夠處理大量資料表。This method has the advantage of being simple and able to handle a large number of tables. 由於間接是動態的單一執行緒函數,查閱是單一執行緒計算在每次計算,即使沒有資料已經變更。Because INDIRECT is a volatile single-threaded function, the lookup is single-thread calculated at every calculation even if no data has changed. 使用此方法會變慢。Using this method is slow.

      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 函數將結果文字轉換成範圍。You could also use the VLOOKUP function to find the name of the sheet or the text string to use for the table, and then use the INDIRECT function to convert the resulting text into a range.

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

另一個技巧是將您所有的資料表彙集成一個巨大的資料表,且該資料表有另一欄可識別個別資料表。Another technique is to aggregate all your tables into one giant table that has an additional column that identifies the individual tables. 然後您可以針對先前範例中顯示的多索引查閱來使用這些技巧。You can then use the techniques for multiple-index lookup shown in the previous examples.

使用萬用字元查閱Use wildcard lookup

MATCHVLOOKUPHLOOKUP函數可讓您使用萬用字元 The MATCH, VLOOKUP, and HLOOKUP functions allow you to use the wildcard characters ? (任何單一字元) 和*****(無字元或任何數目的字元) 上按字母排序的完全相符項目。(any single character) and * (no character or any number of characters) on alphabetical exact matches. 有時候您可以使用這個方法來避免有多個相符項目。Sometimes you can use this method to avoid multiple matches.

最佳化陣列公式及 SUMPRODUCTOptimize array formulas and SUMPRODUCT

陣列公式及 SUMPRODUCT 函數的功能很強,但必須小心處理。Array formulas and the SUMPRODUCT function are powerful, but you must handle them carefully. 單一陣列公式可能需要多次計算。A single array formula might require many calculations.

最佳化陣列公式計算速度的關鍵,就是確定在陣列公式中評估的儲存格和運算式儘可能是最小的。The key to optimizing the calculation speed of array formulas is to ensure that the number of cells and expressions that are evaluated in the array formula is as small as possible. 請記住,陣列公式是有點像動態公式: 如果任一它參考的儲存格已經變更,是動態的或已重新計算,在陣列公式計算公式中的所有儲存格,並評估執行 ca 所需的所有虛擬儲存格lculation。Remember that an array formula is a bit like a volatile formula: if any one of the cells that it references has changed, is volatile, or has been recalculated, the array formula calculates all the cells in the formula and evaluates all the virtual cells it needs to do the calculation.

若要最佳化陣列公式的計算速度,請執行下列作業:To optimize the calculation speed of array formulas:

  • 將運算式和範圍參照從陣列公式取出至個別的協助程式欄和列。Take expressions and range references out of the array formulas into separate helper columns and rows. 以加速 Excel 中智慧重新計算的程序。This makes much better use of the smart recalculation process in Excel.

  • 請勿參照完整列或超出您需求的列和欄。Do not reference complete rows, or more rows and columns than you need. 陣列公式會被強制計算公式中的所有儲存格參照,即使儲存格是空的或未使用也一樣。Array formulas are forced to calculate all the cell references in the formula even if the cells are empty or unused. 從 Excel 2007 開始,若有 1 百萬可用列,參照整欄的陣列公式計算速度會極為緩慢。With 1 million rows available starting in Excel 2007, an array formula that references a whole column is extremely slow to calculate.

  • 從 Excel 2007 開始,請儘可能使用結構化參照,將陣列公式評估的儲存格數目維持在最小程度。Starting in Excel 2007, use structured references where you can to keep the number of cells that are evaluated by the array formula to a minimum.

  • 在 [版本早於 Excel 2007 中,儘可能使用動態範圍名稱。In versions earlier than Excel 2007, use dynamic range names where possible. 雖然這些名稱是可變更的,但是值得這麼做,因為它們會將範圍縮至最小。Although they are volatile, it is worthwhile because they minimize the size of the ranges.

  • 請小心處理同時參照列和欄的陣列公式:這會強制計算矩形範圍。Be careful with array formulas that reference both a row and a column: this forces the calculation of a rectangular range.

  • 請儘可能使用 SUMPRODUCT;它會比同等陣列公式稍微快一點。Use SUMPRODUCT if possible; it is slightly faster than the equivalent array formula.

請考慮使用多重條件陣列公式 SUM 選項Consider options for using SUM for multiple-condition array formulas

您應一律使用SUMIFSCOUNTIFSAVERAGEIFS函數而不使用陣列公式儘可能因為它們是計算速度快多了。You should always use the SUMIFS, COUNTIFS, and AVERAGEIFS functions instead of array formulas where you can because they are much faster to calculate. Excel 2016 引進 fast MAXIFSMINIFS函式。Excel 2016 introduces fast MAXIFS and MINIFS functions.

在版本早於 Excel 2007 中,陣列公式通常可用來計算含多重條件的總和。In versions earlier than Excel 2007, array formulas are often used to calculate a sum with multiple conditions. 這相對較容易執行,尤其是當您在 Excel 中使用 [條件式加總精靈]**** 時,但也常常很慢。This is relatively easy to do, especially if you use the Conditional Sum Wizard in Excel, but it is often slow. 通常會有快很多的方法可以取得相同的結果。Usually there are much faster ways of getting the same result. 如果您只有少數幾個多重條件 SUM,則可使用 DSUM 函數,這比同等陣列公式快很多。If you have only a few multiple-condition SUMs, you may be able to use the DSUM function, which is much faster than the equivalent array formula.

如果您必須使用陣列公式,將其加速的一些好方法如下:If you must use array formulas, some good methods of speeding them up are as follows:

  • 使用動態範圍名稱或結構化的資料表參照儲存格數目降至最低。Use dynamic range names or structured table references to minimize the number of cells.

  • 分成出多個條件 helper 公式的欄,傳回TrueFalse的每一列中,,,然後參考SUMIF或陣列公式中的協助程式資料行。Split out the multiple conditions into a column of helper formulas that return True or False for each row, and then reference the helper column in a SUMIF or array formula. 這可能不會顯示以減少的單一陣列公式; 的計算數量不過,大多數情況下它會啟用智慧重新計算程序來重新計算在協助程式] 欄中,需要重新計算的公式。This might not appear to reduce the number of calculations for a single array formula; however, most of the time it enables the smart recalculation process to recalculate only the formulas in the helper column that need to be recalculated.

  • 請考慮將所有條件串連在一起,成為單一條件,然後使用 SUMIFConsider concatenating together all the conditions into a single condition, and then using SUMIF.

  • 如果可以排序資料,計算一組資料列,並限制陣列公式查看子集群組。If the data can be sorted, count groups of rows and limit the array formulas to looking at the subset groups.

優先順序多重條件 SUMIFS、 COUNTIFS 及其他 IFS 家庭函數Prioritize multiple-condition SUMIFS, COUNTIFS, and other IFS family functions

這些函數會評估每個從左到右的條件依次。These functions evaluate each of the conditions from left to right in turn. 因此,它是更有效率的方法將最嚴格的條件放在前面,以便後續條件只需要查看 [最小的列數。Therefore, it is more efficient to put the most restrictive condition first, so that subsequent conditions only need to look at the smallest number of rows.

請考慮使用 sumproduct 來計算多重條件陣列公式選項Consider options for using SUMPRODUCT for multiple-condition array formulas

從 Excel 2007 開始,您應一律使用SUMIFSCOUNTIFSAVERAGEIFS函數,並在 Excel 2016 MAXIFSMINIFS函數,而不使用SUMPRODUCT公式儘可能。Starting in Excel 2007, you should always use the SUMIFS, COUNTIFS, and AVERAGEIFS functions, and in Excel 2016 MAXIFS and MINIFS functions, instead of SUMPRODUCT formulas where possible.

在舊版本中,使用 SUMPRODUCT 而不使用 SUM 陣列公式有幾個優點:In earlier versions, there are a few advantages to using SUMPRODUCT instead of SUM array formulas:

  • SUMPRODUCT不一定要輸入陣列使用 Ctrl + Shift + enter 鍵。SUMPRODUCT does not have to be array-entered by using Ctrl+Shift+Enter.

  • SUMPRODUCT 通常會比較快一點 (百分之 5 到 10)。SUMPRODUCT is usually slightly faster (5 to 10 percent).

您可以使用sumproduct 來計算多重條件陣列公式,如下所示:You can use SUMPRODUCT for multiple-condition array formulas as follows:

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

在這個範例中,Condition1Condition2例如是條件運算式$A$1:$A$10000<=$Z4In this example, Condition1 and Condition2 are conditional expressions such as $A$1:$A$10000<=$Z4. 因為條件化運算式會傳回 TrueFalse 而不是數字,必須在 SUMPRODUCT 函數中強制將其變成數字。Because conditional expressions return True or False instead of numbers, they must be coerced to numbers inside the SUMPRODUCT function. 您可以使用兩個減號 (--),或藉由新增 0 (+ 0),或藉由將乘以 1 (x1)。You can do this by using two minus signs (--), or by adding 0 (+0), or by multiplying by 1 (x1). 使用-- + 0x1比稍微快。Using -- is slightly faster than +0 or x1.

請注意,在條件化運算式及要加總的範圍中,所使用之範圍或陣列的大小和圖案必須相同,而且不能包含整個欄。Note that the size and shape of the ranges or arrays that are used in the conditional expressions and range to sum must be the same, and they cannot contain entire columns.

您可以也會直接乘以SUMPRODUCT內的字詞,而將它們以逗號區隔:You can also directly multiply the terms inside SUMPRODUCT rather than separate them by commas:

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

這通常是稍微低於將逗號語法,以及如果要加總的範圍包含一個文字值,它會提供錯誤。This is usually slightly slower than using the comma syntax, and it gives an error if the range to sum contains a text value. 然而,這會比較有彈性一點,因為當條件只有一欄時,要加總的範圍可能會有 (例如) 多欄。However, it is slightly more flexible in that the range to sum may have, for example, multiple columns when the conditions have only one column.

使用 SUMPRODUCT 來乘以並加上範圍及陣列Use SUMPRODUCT to multiply and add ranges and arrays

在像加權平均計算這類的情況中,您需要將某數字範圍乘以另一個數字範圍,並加總結果。將逗號語法用於 SUMPRODUCT 會比輸入陣列的 SUM 快百分之 20 到 25。In cases like weighted average calculations, where you need to multiply a range of numbers by another range of numbers and sum the results, using the comma syntax for SUMPRODUCT can be 20 to 25 percent faster than an array-entered SUM.

  {=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 另外兩個公式的計算時間。These three formulas all produce the same result, but the third formula, which uses the comma syntax for SUMPRODUCT, takes only about 77 percent of the calculation time that the other two formulas need.

請注意的潛在的陣列及函數計算阻礙Be aware of potential array and function calculation obstructions

Excel 中的計算引擎已最佳化,可利用參照範圍的陣列公式和函數。The calculation engine in Excel is optimized to exploit array formulas and functions that reference ranges. 然而,有時這些公式和函數會有一些不尋常的排列方式,而導致計算時間大幅增加。However, some unusual arrangements of these formulas and functions can sometimes, but not always, cause significantly increased calculation time.

如果您發現與陣列公式和範圍函數相關的計算阻礙,您應尋找下列項目:If you find a calculation obstruction that involves array formulas and range functions, you should look for the following:

  • 部分重疊的參照。Partially overlapping references.

  • 參照在另一個陣列公式或範圍函數中計算之部分儲存格區塊的陣列公式和範圍函數。Array formulas and range functions that reference part of a block of cells that are calculated in another array formula or range function. 在時間序列分析中經常發生這種情況。This situation can frequently occur in time series analysis.

  • 一組公式依列參照,而第二組公式依欄參照第一組。One set of formulas referencing by row, and a second set of formulas referencing the first set by column.

  • 涵蓋欄區塊的一大組單列陣列公式,而每欄底部都有 SUM 函數。A large set of single-row array formulas covering a block of columns, with SUM functions at the foot of each column.

有效率地使用函數Use functions efficiently

函數可大幅擴充 Excel 的 power,但是您使用的方法常會影響計算時間。Functions significantly extend the power of Excel, but the way in which you use them can often affect calculation time.

避免單一執行緒函數Avoid single-threaded functions

最原生 Excel 函數適用於多執行緒計算。Most native Excel functions work well with multi-threaded calculation. 不過,有可能,請避免使用下列的單一執行緒函數:However, where possible, avoid using the following single-threaded functions:

  • VBA 和自動化使用者定義函數 (Udf),但是 XLL 型 Udf 可以是多執行緒VBA and Automation user-defined functions (UDFs), but XLL-based UDFs can be multi-threaded
  • PHONETICPHONETIC
  • 有使用 format 或 address 引數的 CELLCELL when either the "format" or "address" argument is used
  • INDIRECTINDIRECT
  • GETPIVOTDATAGETPIVOTDATA
  • CUBEMEMBERCUBEMEMBER
  • CUBEVALUECUBEVALUE
  • CUBEMEMBERPROPERTYCUBEMEMBERPROPERTY
  • CUBESETCUBESET
  • CUBERANKEDMEMBERCUBERANKEDMEMBER
  • CUBEKPIMEMBERCUBEKPIMEMBER
  • CUBESETCOUNTCUBESETCOUNT
  • 地址的位置的第五個參數 ( sheet_name) 提供ADDRESS where the fifth parameter (the sheet_name) is given
  • 樞紐分析表是指任何資料庫函數 (DSUM、 DAVERAGE,依此類推)Any database function (DSUM, DAVERAGE, and so on) that refers to a PivotTable
  • ERROR.TYPEERROR.TYPE
  • HYPERLINKHYPERLINK

使用表格進行處理範圍的函式Use tables for functions that handle ranges

針對 SUMSUMIFSUMIFS 這些處理範圍的函數,計算時間與您加總或計數的已使用儲存格數目成正比。For functions like SUM, SUMIF, and SUMIFS that handle ranges, the calculation time is proportional to the number of used cells you are summing or counting. 不會檢查未使用的儲存格方式,讓整欄參照相對較有效率,但最好是以確保您未包含比實際需要更多使用的儲存格。Unused cells are not examined, so whole column references are relatively efficient, but it is better to ensure that you do not include more used cells than you need. 請使用資料表,或計算子集範圍或動態範圍。Use tables, or calculate subset ranges or dynamic ranges.

減少動態函數Reduce volatile functions

動態函數會減緩重新計算的速度,因為每次計算都會增加必須重新計算的公式數量。Volatile functions can slow recalculation because they increase the number of formulas that must be recalculated at each calculation.

通常,您可以減少動態函數的數量,方法是使用 INDEX 而非 OFFSET,並使用 CHOOSE 而不使用 INDIRECTYou can often reduce the number of volatile functions by using INDEX instead of OFFSET, and CHOOSE instead of INDIRECT. 不過,位移就是 fast 函式,而且通常用於提供 fast 計算的創意方法。However, OFFSET is a fast function and can often be used in creative ways that give fast calculation.

使用 C 或 c + + 的使用者定義函式Use C or C++ user-defined functions

使用者定義的函數,C 或 c + + 中編寫和,通常使用 C API (XLL 增益集功能) 的執行速度勝於使用 VBA 或自動化 (XLA 或自動化增益集) 所開發的使用者定義函數。User-defined functions that are programmed in C or C++ and that use the C API (XLL add-in functions) generally perform faster than user-defined functions that are developed by using VBA or Automation (XLA or Automation add-ins). 如需詳細資訊,請參閱開發 Excel 2010 XllFor more information, see Developing Excel 2010 XLLs.

VBA 使用者定義函數的效能容易受其程式設計及呼叫方式的影響。The performance of VBA user-defined functions is sensitive to how you program and call them.

使用更快的 VBA 使用者定義函數Use faster VBA user-defined functions

使用 Excel 公式計算及工作表函數通常會比使用 VBA 使用者定義函數快。It is usually faster to use the Excel formula calculations and worksheet functions than to use VBA user-defined functions. 這是因為用於每個使用者定義函數呼叫的額外負荷很小,而將資訊從 Excel 傳輸至使用者定義函數的額外負荷很大。This is because there is a small overhead for each user-defined function call and significant overhead transferring information from Excel to the user-defined function. 但是設計良好並呼叫的使用者定義函數會比複雜的陣列公式快很多。But well-designed and called user-defined functions can be much faster than complex array formulas.

請確定您已經將工作表儲存格的所有參照都放在使用者定義函數輸入參數中,而不是放在使用者定義函數的主體中,這樣就可以避免新增不必要的 Application.VolatileEnsure that you have put all the references to worksheet cells in the user-defined function input parameters instead of in the body of the user-defined function, so that you can avoid adding Application.Volatile unnecessarily.

如果您必須具有許多使用使用者定義函數的公式,請確定您是在手動計算模式中,並計算會起始從 VBA。If you must have many formulas that use user-defined functions, ensure that you are in manual calculation mode, and that the calculation is initiated from VBA. 如果「不是」** 從 VBA 呼叫計算 (例如,在自動模式下,或是當您在手動模式下按 F9 鍵時),VBA 使用者定義函數的計算速度會慢很多。VBA user-defined functions calculate much more slowly if the calculation is not called from VBA (for example, in automatic mode or when you press F9 in manual mode). 在 Visual Basic 編輯器 (Alt + F11) 已開啟,或目前的 Excel 工作階段中已開啟時,這是特別是,則為 true。This is particularly true when the Visual Basic Editor (Alt+F11) is open or has been opened in the current Excel session.

您可以設陷 F9 並將它重新導向至 VBA 計算副程式,如下所示。You can trap F9 and redirect it to a VBA calculation subroutine as follows. 將此副程式新增至Thisworkbook模組。Add this subroutine to the Thisworkbook module.

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

將此副程式新增至標準模組。Add this subroutine to a standard module.

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

自動增益集中的使用者定義函數 (Excel 2002 及較新版本) 不會招致 Visual Basic 編輯器額外負荷,因為它不使用整合的編輯器。User-defined functions in Automation add-ins (Excel 2002 and later versions) do not incur the Visual Basic Editor overhead because they do not use the integrated editor. 自動增益集中 Visual Basic 6 使用者定義函數的其他效能特性類似 VBA 函數。Other performance characteristics of Visual Basic 6 user-defined functions in Automation add-ins are similar to VBA functions.

如果您的使用者定義函數處理範圍內的每個儲存格,請將輸入宣告為範圍、將其指派給包含陣列的變異,並且在其上重複。If your user-defined function processes each cell in a range, declare the input as a range, assign it to a variant that contains an array, and loop on that. 如果您想要有效率地處理整欄參照,則必須建立輸入範圍的子集、在其與已使用範圍的交集處將它分開,如下列範例所示。If you want to handle whole column references efficiently, you must make a subset of the input range, dividing it at its intersection with the used range, as in this example.

  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 傳輸至使用者定義函數有效率。If your user-defined function is using worksheet functions or Excel object model methods to process a range, it is generally more efficient to keep the range as an object variable than to transfer all the data from Excel to the user-defined function.

  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 your user-defined function is called early in the calculation chain, it can be passed as uncalculated arguments. 內部使用者定義的函數,您可以偵測尚未計算的儲存格包含公式的空白儲存格使用下列測試:Inside a user-defined function, you can detect uncalculated cells by using the following test for empty cells that contain a formula:

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

每次呼叫使用者定義的函數和每次傳輸的資料從 Excel 到 vba 之中,存在於時間的額外負荷。A time overhead exists for each call to a user-defined function and for each transfer of data from Excel to VBA. 有時候一個多儲存格陣列公式使用者定義函數,就可以幫助您最小化這些額外負荷,它會將多個函數呼叫合併至單一函數,並且有多儲存格輸入範圍,會傳回答案範圍。Sometimes one multi-cell array formula user-defined function can help you minimize these overheads by combining multiple function calls into a single function with a multi-cell input range that returns a range of answers.

最小化 SUM 及 SUMIF 參照的儲存格的範圍Minimize range of cells that SUM and SUMIF reference

Excel SUMSUMIF 函數經常會用於大量儲存格。The Excel SUM and SUMIF functions are frequently used over a large number of cells. 這些函數的計算時間與所涵蓋的儲存格數目成正比,所以請嘗試最小化函數參照的儲存格範圍。Calculation time for these functions is proportionate to the number of cells covered, so try to minimize the range of cells that the functions are referencing.

使用萬用字元 SUMIF、 COUNTIF、 SUMIFS、 COUNTIFS 及其他 IFS 函數Use wildcard SUMIF, COUNTIF, SUMIFS, COUNTIFS, and other IFS functions

您可以使用萬用字元 You can use the wildcard characters ? (任何單一字元) 和***(無字元或任何數目的字元) 中依字母順序排列範圍一部分SUMIF**、 COUNTIFSUMIFSCOUNTIFS及其他IFS函數的準則。(any single character) and * (no character or any number of characters) in the criteria for alphabetical ranges as part of the SUMIF, COUNTIF, SUMIFS, COUNTIFS, and other IFS functions.

選擇期間的日期及累計 Sum 方法Choose method for period-to-date and cumulative SUMs

有兩種方法可以執行日期區間及累計 SUM。There are two methods of doing period-to-date or cumulative SUMs. 假設您要累計 SUM 的數字在欄 A,而您想要欄 B 包含累計總和;您可以執行下列其中一項:Suppose the numbers that you want to cumulatively SUM are in column A, and you want column B to contain the cumulative sum; you can do either of the following:

  • 您可以建立欄 B 中的公式如下=SUM($A$1:$A2)就必須將它拖曳向下。You can create a formula in column B such as =SUM($A$1:$A2) and drag it down as far as you need. SUM 的開始儲存格錨定在 A1 中,但是因為完成儲存格有相對的列參照,所以會為每一列自動增加。The beginning cell of the SUM is anchored in A1, but because the finishing cell has a relative row reference, it automatically increases for each row.

  • 您可以建立公式,例如:=$A1儲存格 B1 中和=$B1+$A2中儲存格 B2 並拖曳它向下儘您需要。You can create a formula such as =$A1 in cell B1 and =$B1+$A2 in cell B2 and drag it down as far as you need. 這會將此列的數字新增到上一個累計 SUMThis calculates the cumulative cell by adding this row's number to the previous cumulative SUM.

若有 1,000 列,第一個方法會使 Excel 執行約 500,000 個計算,但是第二個方法會使 Excel 只執行約 2,000 個計算。For 1,000 rows, the first method makes Excel do about 500,000 calculations, but the second method makes Excel do only about 2,000 calculations.

計算子集加總Calculate subset sums

當您有多個排序的索引至資料表 (例如,區域內的網站) 您通常可以動態計算要SUMSUMIF函數中使用資料列 (或欄) 子集範圍的地址可以節省大量的計算時間。When you have multiple sorted indexes to a table (for example, Site within Area) you can often save significant calculation time by dynamically calculating the address of a subset range of rows (or columns) to use in the SUM or SUMIF function.

若要計算列或資料行的子集範圍的位址:To calculate the address of a subset range of row or columns:

  1. 計算每個子集區塊的列數。Count the number of rows for each subset block.

  2. 累計新增每個區塊的計數,以決定其開始列。Add the counts cumulatively for each block to determine its start row.

  3. 使用 OFFSET 搭配開始列和計數,以傳回子集範圍至只涵蓋列子集區塊的 SUMSUMIFUse OFFSET with the start row and count to return a subset range to the SUM or SUMIF that covers only the subset block of rows.

使用小計已篩選清單Use SUBTOTAL for filtered lists

使用SUM 篩選清單的 SUBTOTAL 函數。Use the SUBTOTAL function to SUM filtered lists. SUBTOTAL 函數很有用,因為它不同於 SUM,它會忽略下列事項:The SUBTOTAL function is useful because, unlike SUM, it ignores the following:

  • 從篩選清單得到的隱藏列。Hidden rows that result from filtering a list. 從 Excel 2003 開始,您也可以使 SUBTOTAL 忽略所有隱藏列,而不只是篩選列。Starting in Excel 2003, you can also make SUBTOTAL ignore all hidden rows, not just filtered rows.

  • 其他 SUBTOTAL 函數。Other SUBTOTAL functions.

使用彙總函數Use the AGGREGATE function

彙總函數是功能強大且有效率的方式計算 19 各種不同的方法的彙總資料 (例如SUMMEDIAN百分位數)。The AGGREGATE function is a powerful and efficient way of calculating 19 different methods of aggregating data (such as SUM, MEDIAN, PERCENTILE and LARGE). 彙總具有忽略隱藏或篩選資料列、 錯誤值和巢狀SUBTOTAL彙總函數的選項。AGGREGATE has options for ignoring hidden or filtered rows, error values, and nested SUBTOTAL and AGGREGATE functions.

避免使用任何Avoid using DFunctions

DFunction DSUMDCOUNTDAVERAGE 等等比同等陣列公式快非常多。The DFunctions DSUM, DCOUNT, DAVERAGE, and so on are significantly faster than equivalent array formulas. DFunction 的缺點是準則必須在個別範圍中,在許多情況下會不好使用及維護。The disadvantage of the DFunctions is that the criteria must be in a separate range, which makes them impractical to use and maintain in many circumstances. 從 Excel 2007 開始,您應使用 SUMIFSCOUNTIFSAVERAGEIFS 函數來替代 DFunction。Starting in Excel 2007, you should use SUMIFS, COUNTIFS, and AVERAGEIFS functions instead of the DFunctions.

建立更快的 VBA 巨集Create faster VBA macros

若要建立更快的 VBA 巨集使用下列秘訣。Use the following tips to create faster VBA macros.

在程式碼正在執行時關閉 essentials 以外的所有內容Turn off everything but the essentials while code is running

若要改善效能 VBA 巨集,明確關閉您的程式碼執行時,則不需要的功能。To improve performance for VBA macros, explicitly turn off the functionality that is not required while your code executes. 通常,重新計算一個或一個重繪您的程式碼執行後是所有是必要的可提升效能。Often, one recalculation or one redraw after your code runs is all that is necessary and can improve performance. 您的程式碼執行之後,將功能還原為其原始狀態。After your code executes, restore the functionality to its original state.

VBA 巨集執行時,下列功能通常可以關閉:The following functionality can usually be turned off while your VBA macro executes:

  • Application.ScreenUpdating關閉螢幕更新。Application.ScreenUpdating Turn off screen updating. 如果Application.ScreenUpdating設為False,Excel 不會不重繪螢幕。If Application.ScreenUpdating is set to False, Excel does not redraw the screen. 雖然您的程式碼執行時,螢幕更新快速,且通常不必要的使用者在看到每個更新。While your code runs, the screen updates quickly, and it is usually not necessary for the user to see each update. 一次,更新螢幕之後的程式碼執行時,, 可改善效能。Updating the screen once, after the code executes, improves performance.

  • Application.DisplayStatusBar關閉狀態列。Application.DisplayStatusBar Turn off the status bar. 如果Application.DisplayStatusBar設為False,Excel 不會顯示狀態列。If Application.DisplayStatusBar is set to False, Excel does not display the status bar. [狀態] 列中設定為不同於更新設定,讓您仍然可以顯示目前的作業的狀態,即使不更新螢幕畫面。The status bar setting is separate from the screen updating setting so that you can still display the status of the current operation even while the screen is not updating. 不過,如果您不需要顯示的每一項作業的狀態,關閉 [狀態] 列,也執行程式碼時提升效能。However, if you do not need to display the status of every operation, turning off the status bar while your code runs also improves performance.

  • Application.Calculation切換成手動計算。Application.Calculation Switch to manual calculation. 如果Application.Calculation設為xlCalculationManual,Excel 只會計算活頁簿時使用者明確初始化計算。If Application.Calculation is set to xlCalculationManual, Excel only calculates the workbook when the user explicitly initiates the calculation. 在自動計算模式中,Excel 會判斷何時要計算。In automatic calculation mode, Excel determines when to calculate. 例如,每次儲存格的值與公式相關的變更,Excel 會重新計算公式。For example, every time a cell value that is related to a formula changes, Excel recalculates the formula. 如果您切換計算模式為手動,您可以等到與公式關聯的所有儲存格會更新之前先計算活頁簿。If you switch the calculation mode to manual, you can wait until all the cells associated with the formula are updated before recalculating the workbook. 只計算活頁簿在必要時,您的程式碼執行時,您可以改善效能。By only recalculating the workbook when necessary while your code runs, you can improve performance.

  • Application.EnableEvents關閉事件。Application.EnableEvents Turn off events. 如果Application.EnableEvents設為False,Excel 不會引發事件。If Application.EnableEvents is set to False, Excel does not raise events. 如果有增益集的 Excel 事件接聽,這些增益集會耗用資源的電腦上,為他們記錄事件。If there are add-ins listening for Excel events, those add-ins consume resources on the computer as they record the events. 如果您不需要增益集來記錄您的程式碼執行時,就會發生的事件,以關閉事件提升效能。If it is not necessary for the add-in to record the events that occur while your code runs, turning off events improves performance.

  • ActiveSheet.DisplayPageBreaks關閉分頁符號。ActiveSheet.DisplayPageBreaks Turn off page breaks. 如果ActiveSheet.DisplayPageBreaks設為False,Excel 不會顯示分頁符號。If ActiveSheet.DisplayPageBreaks is set to False, Excel does not display page breaks. 您不需要重新計算] 頁面上符號時您的程式碼會執行,並計算分頁符號之後的程式碼執行改善效能。It is not necessary to recalculate page breaks while your code runs, and calculating the page breaks after the code executes improves performance.

重要

程式碼執行之後,記得將此功能還原為原始狀態。Remember to restore this functionality to its original state after your code executes.

下列範例顯示您可以在 VBA 巨集執行時關閉的功能。The following example shows the functionality that you can turn off while your VBA macro executes.

  ' 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

讀取和寫入在單一作業中的大型資料區塊Read and write large blocks of data in a single operation

明確減少在 Excel 與程式碼之間傳輸資料的次數,以最佳化程式碼。Optimize your code by explicitly reducing the number of times data is transferred between Excel and your code. 不要一次循環查看一個儲存格來取得或設定值,而是使用包含二維陣列的變異,依需要儲存值,以在一行中的整個儲存格範圍內取得或設定值。Instead of looping through cells one at a time to get or set a value, get or set the values in the entire range of cells in one line, using a variant containing a two-dimensional array to store values as needed. 下列程式碼範例會比較這兩種方法。The following code examples compare these two methods.

下列程式碼範例顯示非最佳化程式碼,其一次循環查看一個儲存格來取得及設定儲存格 A1:C10000 的值。The following code example shows non-optimized code that loops through cells one at a time to get and set the values of cells A1:C10000. 這些儲存格不包含公式。These cells do not contain formulas.

  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 的所有值。The following code example shows optimized code that uses an array to get and set the values of cells A1:C10000 all at the same time. 這些儲存格不包含公式。These cells do not contain formulas.

  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 範圍讀取資料時的文字Use .Value2 rather than .Value or .Text when reading data from an Excel range

  • .文字會傳回儲存格的格式化的值。.Text returns the formatted value of a cell. 這是很慢,可以傳回 # # # 如果使用者拉,並可能會遺失精確度。This is slow, can return ### if the user zooms, and can lose precision.
  • .值傳回 VBA 貨幣或 VBA 日期] 變數,如果該範圍已格式化為日期或貨幣。.Value returns a VBA currency or VBA date variable if the range was formatted as Date or Currency. 這很慢、 可能會遺失精確度,及呼叫工作表函數時可能會造成錯誤。This is slow, can lose precision, and can cause errors when calling worksheet functions.
  • .Value2快速且不會更改正在從 Excel 擷取資料。.Value2 is fast and does not alter the data being retrieved from Excel.

避免選取和啟動物件Avoid selecting and activating objects

選取和啟動物件比直接參照物件更需要大量處理。Selecting and activating objects is more processing intensive than referencing objects directly. 藉由直接參照例如RangeShape物件,您可以改善效能。By referencing an object such as a Range or a Shape directly, you can improve performance. 下列程式碼範例會比較這兩種方法。The following code examples compare the two methods.

下列程式碼範例顯示非最佳化程式碼會選取現用工作表上的每個圖案,並將文字變更為"Hello"。The following code example shows non-optimized code that selects each Shape on the active sheet and changes the text to "Hello".

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

下列程式碼範例顯示直接參照每個圖案,並將文字變更為"Hello"的最佳化程式碼。The following code example shows optimized code that references each Shape directly and changes the text to "Hello".

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

使用這些額外的 VBA 效能最佳化Use these additional VBA performance optimizations

下列是您可以在 VBA 程式碼中使用的其他效能最佳化清單:The following is a list of additional performance optimizations you can use in your VBA code:

  • 直接指派陣列至 Range,以傳回結果。Return results by assigning an array directly to a Range.

  • 宣告含明確類型的變數,以避免在程式碼執行期間判定資料類型的額外負荷,可能一個循環好幾次。Declare variables with explicit types to avoid the overhead of determining the data type, possibly multiple times in a loop, during code execution.

  • 針對您經常在程式碼中使用的簡單函數,自己在 VBA 中實作函數,而不要使用 WorksheetFunction 物件。For simple functions that you use frequently in your code, implement the functions yourself in VBA instead of using the WorksheetFunction object. 如需詳細資訊,請參閱 <使用更快 VBA 使用者定義函數。For more information, see Use faster VBA user-defined functions.

  • 使用 Range.SpecialCells 方法來縮小範圍,以減少程式碼要互動的儲存格數目。Use the Range.SpecialCells method to scope down the number of cells with which your code interacts.

  • 如果您使用 C API XLL SDK 中實作您的功能,請考慮提升效能。Consider the performance gains if you implemented your functionality by using the C API in the XLL SDK. 如需詳細資訊,請參閱 < Excel 2010 XLL SDK 文件For more information, see the Excel 2010 XLL SDK Documentation.

請考慮效能及大小的 Excel 檔案格式Consider performance and size of Excel file formats

從 Excel 2007 開始,與舊版相較,Excel 包含了各式各樣的檔案格式。Starting in Excel 2007, Excel contains a wide variety of file formats compared to earlier versions. 忽略巨集],[範本,增益集、 PDF、 和 XPS 檔案格式變化,三種主要的格式是 XLS、 XLSB 和 XLSX。Ignoring the Macro, Template, Add-in, PDF, and XPS file format variations, the three main formats are XLS, XLSB, and XLSX.

  • XLS 格式XLS format

    XLS 格式是與舊版相同的格式。The XLS format is the same format as earlier versions. 當您使用此格式時,您受限於 256 欄及 65,536 列。When you use this format, you are restricted to 256 columns and 65,536 rows. 當您以 XLS 格式儲存 Excel 2007 或 Excel 2010 活頁簿時,Excel 會執行相容性檢查。When you save an Excel 2007 or Excel 2010 workbook in XLS format, Excel runs a compatibility check. 檔案大小幾乎與舊版本一樣 (可能會儲存一些其他資訊),而效能稍微比舊版慢一點。File size is almost the same as earlier versions (some additional information may be stored), and performance is slightly slower than earlier versions. Excel 未依儲存格計算順序來執行的任何多執行緒最佳化,都不會以 XLS 格式儲存。Any multi-threaded optimization Excel does with respect to cell calculation order is not saved in the XLS format. 因此,以 XLS 格式儲存活頁簿、關閉再重新開啟活頁簿之後,活頁簿的計算會比較慢。Therefore, calculation of a workbook can be slower after saving the workbook in the XLS format, closing, and re-opening the workbook.

  • XLSB 格式XLSB format

    從 Excel 2007 開始,XLSB 是二進位格式。XLSB is the binary format starting in Excel 2007. 它的結構化為包含許多二進位檔案的壓縮資料夾。It is structured as a compressed folder that contains many binary files. 更簡潔比為 XLS 格式,但壓縮量取決於活頁簿的內容。It is much more compact than the XLS format, but the amount of compression depends on the contents of the workbook. 例如,10 個活頁簿顯示的大小縮減係數範圍從 2 到 8,平均縮減係數為 4。For example, ten workbooks show a size reduction factor ranging from two to eight with an average reduction factor of four. 從 Excel 2007 開始,開啟和儲存效能只比 XLS 格式慢一點點。Starting in Excel 2007, opening and saving performance is only slightly slower than the XLS format.

  • XLSX 格式XLSX format

    從 Excel 2007 開始,XLSX 是 XML 格式,而且從 Excel 2007 開始是預設格式。XLSX is the XML format starting in Excel 2007, and is the default format starting in Excel 2007. XLSX 格式是包含許多 XML 檔案的壓縮的資料夾 (如果您變更.zip 副檔名,您可以開啟 [壓縮] 資料夾,檢查其內容)。The XLSX format is a compressed folder that contains many XML files (if you change the file name extension to .zip, you can open the compressed folder and examine its contents). 通常 XLSX 格式建立的檔案會比 XLSB 格式大 (平均大 1.5 倍),但還是比 XLS 檔案小非常多。Typically, the XLSX format creates larger files than the XLSB format (1.5 times larger on average), but they are still significantly smaller than the XLS files. 您應預期開啟和儲存時間會比 XLSB 檔案稍微長一點。You should expect opening and saving times to be slightly longer than for XLSB files.

開啟、 關閉,並儲存活頁簿Open, close, and save workbooks

您可能會發現開啟、關閉及儲存活頁簿比加以計算慢很多。You may find that opening, closing, and saving workbooks is much slower than calculating them. 有時候這只是因為您有大型活頁簿,但也可能是其他原因。Sometimes this is just because you have a large workbook, but there can also be other reasons.

如果您有一或多個活頁簿開啟及關閉的速度慢到不合理的地步,可能是下列問題所造成。If one or more of your workbooks open and close more slowly than is reasonable, it might be caused by one of the following issues.

  • 暫存檔Temporary files

    暫存檔可以累積在您\Windows\Temp 目錄 (在 Windows 95、 Windows 98 和 Windows ME),或您\Documents and Settings\使用者名稱\本機設定\Temp 目錄 (在 Windows 2000 和Windows XP)。Temporary files can accumulate in your \Windows\Temp directory (in Windows 95, Windows 98, and Windows ME), or your \Documents and Settings\User Name\Local Settings\Temp directory (in Windows 2000 and Windows XP). Excel 會建立這些檔案的活頁簿和開啟的活頁簿所使用的控制項。Excel creates these files for the workbook and for controls that are used by open workbooks. 軟體安裝程式也會建立暫存檔。Software installation programs also create temporary files. 如果 Excel 因故停止回應,您可能需要刪除這些檔案。If Excel stops responding for any reason, you might need to delete these files.

    太多暫存檔可能會造成問題,所以您應該有時會清除它們。不過,如果您已安裝的軟體,需要您重新啟動電腦,以及您還不尚未這麼做,您應該重新啟動之前刪除的暫存檔。Too many temporary files can cause problems, so you should occasionally clean them out. However, if you have installed software that requires that you restart your computer, and you have not yet done so, you should restart before deleting the temporary files.

 一個開啟暫存檔的簡單方法,就是從 Windows 的 [開始]**** 功能表進行:按一下 [開始]****,然後按一下 [執行]****。An easy way to open your temp directory is from the Windows Start menu: Click Start, and then click Run. 在文字方塊中,輸入 %temp%,然後按一下 [確定]****。In the text box, type %temp%, and then click OK.

  • 共用活頁簿中的追蹤修訂Tracking changes in a shared workbook

    追蹤共用活頁簿中的變更會使活頁簿檔案大小快速增加。Tracking changes in a shared workbook causes your workbook file-size to increase rapidly.

  • 分散的分頁檔案Fragmented swap file

    請確定 Windows 分頁檔案位在有很多空間的磁碟上,而且您會定期重組磁碟。Be sure that your Windows swap file is located on a disk that has a lot of space and that you defragment the disk periodically.

  • 具有受密碼保護結構的活頁簿Workbook with password-protected structure

    受密碼保護結構的活頁簿 (工具功能表 >保護 > 保護活頁簿> 輸入選用性密碼) 開啟及關閉遠低於一個受保護而選用密碼。A workbook that has its structure protected with a password (Tools menu > Protection > Protect Workbook > enter the optional password) opens and closes much slower than one that is protected without the optional password.

  • 已使用的範圍問題Used range problems

    過大的已使用範圍會導致開啟緩慢,並增加檔案大小,尤其如果是由具有非標準高度或寬度的隱藏列或欄所導致的話。Oversized used ranges can cause slow opening and increased file size, especially if they are caused by hidden rows or columns that have non-standard height or width. 如需使用的範圍問題的詳細資訊,請參閱 <最小化使用範圍。For more information about used range problems, see Minimize the used range.

  • 大量的工作表上的控制項Large number of controls on worksheets

    工作表上的大量控制項 (核取方塊、超連結等等) 會因為所使用的暫存檔數目,而減緩開啟活頁簿的速度。A large number of controls (check boxes, hyperlinks, and so on) on worksheets can slow down opening a workbook because of the number of temporary files that are used. 這也可能會在 WAN (甚至 LAN) 上開啟或儲存活頁簿時造成問題。This might also cause problems opening or saving a workbook on a WAN (or even a LAN). 如果您有這個問題,則應考慮重新設計活頁簿。If you have this problem, you should consider redesigning your workbook.

  • 大量連結至其他活頁簿Large number of links to other workbooks

    若有可能,請先開啟您要連結的活頁簿,再開啟包含連結的活頁簿。If possible, open the workbooks that you are linking to before you open the workbook that contains the links. 通常開啟活頁簿會比從關閉的活頁簿讀取連結快。Often it is faster to open a workbook than to read the links from a closed workbook.

  • 病毒掃描器設定Virus scanner settings

    有些病毒掃描器設定會導致開啟、關閉或儲存時發生問題或速度變慢,尤其是在伺服器上。Some virus scanner settings can cause problems or slowness with opening, closing, or saving, especially on a server. 如果您認為這可能是問題所在,請嘗試暫時關閉病毒掃描器。If you think that this might be the problem, try temporarily switching the virus scanner off.

  • 慢導致慢速開啟的計算,並將儲存Slow calculation causing slow open and save

    在某些情況下,Excel 會在開啟或儲存活頁簿時重新計算。Under some circumstances, Excel recalculates your workbook when it opens or saves it. 如果計算時間的活頁簿過長,而造成問題,確保您具有計算設為手動,並考慮關閉計算之前儲存選項 (工具 > 選項 > 計算)。If the calculation time for your workbook is long and is causing a problem, ensure that you have calculation set to manual, and consider turning off the calculate before save option (Tools > Options > Calculation).

  • 工具列檔案 (.xlb)Toolbar files (.xlb)

    檢查工具列檔案大小。Check the size of your toolbar file. 一般工具列檔案介於 10 KB 到 20 KB。A typical toolbar file is between 10 KB and 20 KB. 您可以藉由搜尋來尋找 XLB 檔案*.xlb使用 Windows 搜尋。You can find your XLB files by searching for *.xlb by using Windows search. 每個使用者都有唯一的 XLB 檔案。Each user has a unique XLB file. 新增、變更或自訂工具列會增加 toolbar.xlb 檔案的大小。Adding, changing, or customizing toolbars increases the size of your toolbar.xlb file. 刪除檔案會移除所有工具列自訂內容 (將它更名為 "toolbar.OLD" 比較安全)。Deleting the file removes all your toolbar customizations (renaming it "toolbar.OLD" is safer). 您下次開啟 Excel 時,就會建立新的 XLB 檔案。A new XLB file is created the next time you open Excel.

進行其他效能最佳化Make additional performance optimizations

您可以在下列區域進行的效能改進。You can make performance improvements in the following areas.

  • PivotTablesPivotTables

    樞紐分析表提供摘要大量資料的有效方法。PivotTables provide an efficient way to summarize large amounts of data.

    • 總計為最後結果Totals as final results. 如果您需要在活頁簿的最後結果中產生總計和小計,請嘗試使用樞紐分析表。If you need to produce totals and subtotals as part of the final results of your workbook, try using PivotTables.

    • 總計為中間結果Totals as intermediate results. 樞紐分析表是產生摘要報表很棒的方法,但是建立公式時,請嘗試避免將樞紐分析表結果用作計算鏈中間的總計和小計,除非您可以確定下列狀況:PivotTables are a great way to produce summary reports, but try to avoid creating formulas that use PivotTable results as intermediate totals and subtotals in your calculation chain unless you can ensure the following conditions:

    • 樞紐分析表已在計算期間正確重新整理。The PivotTable has been refreshed correctly during the calculation.

    • 樞紐分析表尚未變更,所以還看得到資訊。The PivotTable has not been changed so that the information is still visible.

    如果您還是想要使用樞紐分析表做為中間結果,請使用 GETPIVOTDATA 函數。If you still want to use PivotTables as intermediate results, use the GETPIVOTDATA function.

  • 條件化格式和資料驗證Conditional formats and data validation

    條件化格式及資料驗證是很有用的功能,但是大量使用會大幅降低計算速度。Conditional formats and data validation are great, but using a lot of them can significantly slow down calculation. 顯示儲存格時,每次計算並顯示包含條件化格式的儲存格的重新整理時,會評估每一個條件化格式的公式。If the cell is displayed, every conditional format formula is evaluated at each calculation and when the display of the cell that contains the conditional format is refreshed. Excel 物件模型有 Worksheet.EnableFormatConditionsCalculation 屬性,所以您可以啟用或停用條件化格式的計算。The Excel object model has a Worksheet.EnableFormatConditionsCalculation property so that you can enable or disable the calculation of conditional formats.

  • 已定義的名稱Defined names

    已定義的名稱是 Excel 中最強的功能之一,但確實會花費額外的計算時間。Defined names are one of the most powerful features in Excel, but they do take additional calculation time. 使用參照其他工作表的名稱會增加計算程序的複雜度。Using names that refer to other worksheets adds an additional level of complexity to the calculation process. 此外,您也應該嘗試避免使用巢狀名稱 (參照其他名稱的名稱)。Also, you should try to avoid nested names (names that refer to other names).

    因為每次計算有參照名稱的公式時,就會計算該名稱,所以應避免將計算密集的公式或函數放在已定義的名稱中。Because names are calculated every time a formula that refers to them is calculated, you should avoid putting calculation-intensive formulas or functions in defined names. 在這些案例中,將計算密集的公式或函數放在某處的備用儲存格中,並改為參照該儲存格 (直接參照或使用名稱),會快非常多。In these cases, it can be significantly faster to put your calculation-intensive formula or function in a spare cell somewhere and refer to that cell instead, either directly or by using a name.

  • 僅偶爾使用的公式Formulas that are used only occasionally

    很多活頁簿包含的大量公式和查閱,都有關將輸入資料放進適當圖案以供計算,或是要用作防止資料大小或圖案變更的防範措施。Many workbooks contain a significant number of formulas and lookups that are concerned with getting the input data into the appropriate shape for the calculations, or are being used as defensive measures against changes in the size or shape of the data. 如果您有僅偶爾使用的公式區塊,可以複製並貼上特殊值,以暫時消除公式,或是將其放在個別、極少開啟的活頁簿中。When you have blocks of formulas that are used only occasionally, you can copy and paste special values to temporarily eliminate the formulas, or you can put them in a separate, rarely opened workbook. 由於工作表錯誤常常都是因為沒注意到公式已轉換成值而導致,所以個別的活頁簿方法會比較好。Because worksheet errors are often caused by not noticing that formulas have been converted to values, the separate workbook method may be preferable.

  • 使用足夠記憶體Use enough memory

    32 位元版本的 Excel 可以使用最多 2 GB 的 RAM 或,4 GB 的 RAM 供大型的地址注意 32 位元版本的 Excel 2013 和 2016年。The 32-bit version of Excel can use up to 2 GB of RAM or up to 4 GB of RAM for Large Address Aware 32-bit versions of Excel 2013 and 2016. 不過,執行 Excel 的電腦也需要記憶體資源。However, the computer that is running Excel also requires memory resources. 因此,如果您只有在您的電腦上有 2 GB 的 RAM,Excel 無法利用完整的 2 GB 因為部分記憶體配置給作業系統,以及執行其他程式。Therefore, if you only have 2 GB of RAM on your computer, Excel cannot take advantage of the full 2 GB because a portion of the memory is allocated to the operating system and other programs that are running. 若要最佳化 Excel 32 位元電腦上的效能,建議的電腦必須至少 3 GB 的 RAM。To optimize the performance of Excel on a 32-bit computer, we recommend that the computer have at least 3 GB of RAM.

    64 位元版本的 Excel 沒有 2 GB 或 up 為 4 GB 限制。The 64-bit version of Excel does not have a 2 GB or up to 4 GB limit. 如需詳細資訊,請參閱 「 大型資料集和 64 位元版本的 Excel 」 一節中Excel 效能: 改良效能與限制For more information, see the "Large data sets and the 64-bit version of Excel" section in Excel performance: Performance and limit improvements.

總結Conclusion

本文涵蓋最佳化 Excel 功能,例如連結、 查閱功能、 公式、 函數和 VBA 程式碼,以避免常見障礙並提升效能。This article covered ways to optimize Excel functionality such as links, lookups, formulas, functions, and VBA code to avoid common obstructions and improve performance.

請參閱See also

支援和意見反應Support and feedback

有關於 Office VBA 或這份文件的問題或意見反應嗎?Have questions or feedback about Office VBA or this documentation? 如需取得支援服務並提供意見反應的相關指導,請參閱 Office VBA 支援與意見反應Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.