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

適用版本: Excel | Excel 2013 | Excel 2016 | VBAApplies to: Excel | Excel 2013 | Excel 2016 | VBA

在 Office Excel 2016,最大欄數可達 16,000 欄,最大列數亦高達 1 百萬列,再加上許多其他限制加大,您可以建立的工作表大小比起舊版 Excel 大上許多。The "Big Grid" of 1 million rows and 16,000 columns in Office Excel 2016, together with many other limit increases, greatly increases the size of worksheets that you can build compared to earlier versions of Excel. Excel 中單一工作表可容納的儲存格數是舊版的 1,000 倍以上。A single worksheet in Excel can now contain over 1,000 times as many cells as earlier versions.

在舊版 Excel 中,許多使用者建立的工作表計算速度緩慢,而大型工作表的計算速度通常又比小型的慢上許多。In earlier versions of Excel, many people created slow-calculating worksheets, and larger worksheets usually calculate more slowly than smaller ones. 隨著 Excel 2007 中所加入的欄列數上限,效能問題就變得更加重要了。With the introduction of the "Big Grid" in Excel 2007, performance really matters. 像是排序、篩選等計算與資料處理工作過於緩慢,將導致使用者更難以專心處理手邊的工作,而一旦分心就更容易出錯。Slow calculation and data manipulation tasks such as sorting and filtering make it more difficult for users to concentrate on the task at hand, and lack of concentration increases errors.

近期的 Excel 版本推出幾項新功能,有助於您因應此大幅增加的容量,例如可同時運用多個處理器進行計算,以及如重新整理、排序和開啟活頁簿等常用資料集作業的能力。Recent Excel versions introduced several features to help you handle this capacity increase, such as the ability to use more than one processor at a time for calculations and common data set operations like refresh, sorting, and opening workbooks. 多執行緒計算可實際縮短工作表計算時間。Multithreaded calculation can substantially reduce worksheet calculation time. 但真正會影響 Excel 計算速度最重要的因素,仍取決於您工作表的設計與建立方式。However, the most important factor that influences Excel calculation speed is still the way your worksheet is designed and built.

只要針對計算最遲緩的工作表進行修改,即可讓計算速度提升數十、上百甚至千倍。You can modify most slow-calculating worksheets to calculate tens, hundreds, or even thousands of times faster. 透過找出工作表中的計算障礙並加測量,進而改善計算效能,您可以加快計算的速度。By identifying, measuring, and then improving the calculation obstructions in your worksheets, you can speed up calculation.

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

計算速度遲緩會降低生產力,且導致使用者出錯機率大增。Poor calculation speed affects productivity and increases user error. 當回應時間變長,使用者的專注力和生產力便會隨之下降。User productivity and the ability to focus on a task deteriorates as response time lengthens.

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

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

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

計算時間少於十分之一秒時,使用者會感覺系統是立即回應的。For calculation times of less than about a tenth of a second, users feel that the system is responding instantly. 甚至可以在一邊輸入資料的同時,一邊自動計算。They can use automatic calculation even when they enter data.

計算時間介於十分之一秒到一秒之間時,即使使用者注意到回應時間有延遲,使用者仍可以成功保持思緒暢通。Between a tenth of a second and one second, users can successfully keep a train of thought going, although they will notice the response time delay.

倘若計算時間更長 (通常介於 1 到 10 秒之間),使用者在輸入資料時,就必須切換成手動計算。As calculation time increases (usually between 1 and 10 seconds), users must switch to manual calculation when they enter data. 使用者出錯的頻率將會增加,且開始覺得煩躁,尤其對於重覆性的工作,要保持流暢的思緒變得十分困難。User errors and annoyance levels start to increase, especially for repetitive tasks, and it becomes difficult to maintain a train of thought.

計算時間若長達 10 秒以上,使用者會失去耐性,而且通常在等待期間乾脆切換到其他工作。For calculation times greater than 10 seconds, users become impatient and usually switch to other tasks while they wait. 如果該計算是一系列工作的其中一部分,而使用者因此錯亂失序的話,麻煩就大了。This can cause problems when the calculation is one of a sequence of tasks and the user loses track.

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

若要改善 Excel 的計算效能,您必須先認識兩種可用的計算方法,並學會如何操控。To improve the calculation performance in Excel, you must understand both the available calculation methods and how to control them.

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

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

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

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

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

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

在大多數的情況下,由於您在每次計算時只變更一部分輸入資料或一些公式,因此這個智慧重算只需要完整計算所有公式所需時間的幾分之一時間。Because you change only part of the input data or a few formulas between calculations in most cases, this smart recalculation usually takes only a fraction of the time that a full calculation of all the formulas would take.

在手動計算模式下,您只要按下 F9 就可以啟動這個智慧重算。In manual calculation mode, you can trigger this smart recalculation by pressing F9. 按下 CTRL+ALT+F9 就會強制執行完整計算所有公式,或者您也可以按下 SHIFT+CTRL+ALT+F9,強制完全重建相依性並執行完整計算。You can force a full calculation of all the formulas by pressing Ctrl+Alt+F9, or you can force a complete rebuild of the dependencies and a full calculation by pressing Shift+Ctrl+Alt+F9.

計算處理流程Calculation process

參照其他儲存格的 Excel 公式可置放於被參照的儲存格之前或之後 (向前參照或向後參照)。Excel formulas that reference other cells can be put before or after the referenced cells (forward referencing or backward referencing). 這是因為 Excel 計算儲存格的順序不是固定的,也不是依欄或列加以計算。This is because Excel does not calculate cells in a fixed order, or by row or column. 相反地,Excel 會依照所有待計算公式的清單 (稱為計算鏈) 以及每個公式的相依性資訊,動態地決定計算順序。Instead, Excel dynamically determines the calculation sequence based on a list of all the formulas to calculate (the calculation chain) and the dependency information about each formula.

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

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

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

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

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

每次計算或重新計算時,都會執行第三個階段。The third phase executes at each calculation or recalculation. Excel 會嘗試依計算鏈中公式的順序,計算每個公式,但如果有某個公式,其相依的一或多個公式尚未計算過,則該公式將向後排到計算鏈尾端,稍後再加以計算。Excel tries to calculate each formula in the calculation chain in turn, but if a formula depends on one or more formulas that have not yet been calculated, the formula is sent down the chain to be calculated again later. 也就是說,在每次重新計算時,同一個公式可能會計算多次。This means that a formula can be calculated multiple times per recalculation.

第二次計算活頁簿的速度通常比首次執行快上許多。The second time that you calculate a workbook is often significantly faster than the first time. 發生的原因有幾個:This occurs for several reasons:

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

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

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

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

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

您可以透過使用不同的 Excel 計算方法,控制要進行計算的項目。You can control what is calculated by using the different Excel calculation methods.

計算所有開啟中活頁簿Calculate all open workbooks

每次重新計算和完整計算時,都會計算目前所有開啟中活頁簿,解析活頁簿和工作表本身之內和兩者之間的所有相依關係,然後將所有先前未計算 (已變更) 的儲存格重設為已計算。Each recalculation and full calculation calculates all the workbooks that are currently open, resolves any dependencies within and between workbooks and worksheets, and resets all previously uncalculated (dirty) cells as calculated.

計算所選的工作表Calculate selected worksheets

您也可以使用 SHIFT+F9 只重新計算選取的工作表。You can also recalculate only the selected worksheets by using Shift+F9. 這樣不會解析工作表之間任何的相依性,也不會將已變更的儲存格重設為已計算。This does not resolve any dependencies between worksheets, and does not reset dirty cells as calculated.

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

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

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

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

由於 CalculateRowMajorOrder 不會解析計算範圍內的任何相依性,所以通常會明顯比 Range.Calculate 快得多。Because CalculateRowMajorOrder does not resolve any dependencies within the range that is being calculated, it is usually significantly faster than Range.Calculate. 但是您必須小心使用,因為計算結果和 Range.Calculate 可能不相同。However, it should be used with care because it may not give the same results as Range.Calculate.

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

如需詳細資訊,請參閱 Excel 效能:改良效能與限制For more information, see Excel performance: Performance and limit improvements.

動態函數Volatile functions

動態函數在每次重新計算時一律重新計算,即使沒有任何已變更的前導參照也一樣。A volatile function is always recalculated at each recalculation even if it does not seem to have any changed precedents. 使用多個動態函數會減緩每次重新計算的速度,但是對於完整計算則不會有任何差異。Using many volatile functions slows down each recalculation, but it makes no difference to a full calculation. 您可以在函數程式碼中加入 Application.Volatile,將使用者定義函數變為動態函數。You can make a user-defined function volatile by including Application.Volatile in the function code.

在 Excel 中,有些內建函數很明顯是動態函數:RAND()NOW()TODAY()Some of the built-in functions in Excel are obviously volatile: RAND(), NOW(), TODAY(). 其他較不明顯的動態函數有:OFFSET()CELL()INDIRECT()INFO()Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO().

有些函數先前被記錄為動態函數,但實際上不是動態函數,這些函數是:INDEX()ROWS()COLUMNS()AREAS()Some functions that have previously been documented as volatile are not in fact volatile: INDEX(), ROWS(), COLUMNS(), AREAS().

動態動作Volatile actions

動態動作是指會觸發重新計算的動作,這些動作包括:Volatile actions are actions that trigger a recalculation, and include the following:

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

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

當您執行以下操作時,即使是在手動計算模式下,公式或一部分的公式會立即進行評估 (計算):A formula or part of a formula is immediately evaluated (calculated), even in manual calculation mode, when you do one of the following:

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

當公式參照到 (依存於) 的儲存格或公式有下列情況之一時,公式會標示為未計算:A formula is flagged as uncalculated when it refers to (depends on) a cell or formula that has one of these conditions:

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

當包含有公式的工作表、活頁簿或 Excel 執行個體進行計算或重新計算時,被標示為未計算的公式會進行評估。A formula that is flagged as uncalculated is evaluated when the worksheet, workbook, or Excel instance that contains it is calculated or recalculated.

導致已定義之名稱進行評估的情況與儲存格內公式的情況不同:The circumstances that cause a defined name to be evaluated differ from those for a formula in a cell:

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

運算列表Data tables

請勿將 Excel 的運算列表 ([資料]**** 索引標籤 > [資料工具]**** 群組 > [模擬分析]**** > [運算列表]****) 與表格功能 ([常用]**** 索引標籤 > [樣式]**** 群組 > [格式化為表格]****,或是 [插入]**** 索引標籤 > [表格]**** 群組 > [表格]****) 混淆。Excel data tables (Data tab > Data Tools group > What-If Analysis > Data Table) should not be confused with the table feature (Home tab > Styles group > Format as Table, or, Insert tab > Tables group > Table). Excel 運算列表會針對活頁簿進行多次重新計算,分別由列表中不同的值所驅動。Excel data tables do multiple recalculations of the workbook, each driven by the different values in the table. Excel 首先會以一般方式計算活頁簿。Excel first calculates the workbook normally. 接著會替換每組欄與列的值,進行單一執行緒的重新計算,最後將結果儲存在運算列表中。For each pair of row and column values, it then substitutes the values, does a single-threaded recalculation, and stores the results in the data table.

運算列表的重新計算一律只用單一處理器進行。Data table recalculation always uses only a single processor.

您可利用運算列表輕鬆計算多種變化,並檢視與比較各組變化的結果。Data tables give you a convenient way to calculate multiple variations and view and compare the results of the variations. 使用計算選項 [除資料表外,自動重算]**** 可停止 Excel 在每次計算時自動觸發多重計算,但仍然會計算所有運算列表以外的相依公式。Use the Automatic except Tables calculation option to stop Excel from automatically triggering the multiple calculations at each calculation, but still calculate all dependent formulas except tables.

控制計算選項Controlling calculation options

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

圖 1:[公式] 索引標籤的 [計算] 群組Figure 1. Calculation group on the Formulas tab

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

若要查看更多 Excel 計算選項,在 [檔案]**** 索引標籤,按一下 [選項]****。To see more Excel calculation options, on the File tab, click Options. 在 [Excel 選項]**** 對話方塊,按一下 [公式]**** 索引標籤。In the Excel Options dialog box, click the Formulas tab.

圖 2:[Excel 選項] 中 [公式] 索引標籤的 [計算] 選項Figure 2. Calculation options on the Formulas tab in Excel Options

Backstage 檢視中的計算選項

許多計算選項 ([自動]****、[除運算列表外,自動重算]****、[手動]****、[儲存活頁簿前自動重算]****) 和反覆運算設定 ([啟用反覆運算]****、[最高次數]****、[最大誤差]****) 是在應用程式層級操作,而不是活頁簿層級 (對所有開啟中活頁簿來說,這些選項都是相同的)。Many calculation options (Automatic, Automatic except for data tables, Manual, Recalculate workbook before saving) and the iteration settings (Enable iterative calculation, Maximum Iterations, Maximum Change) operate at the application level instead of at the workbook level (they are the same for all open workbooks).

若要查看進階計算選項,在 [檔案]**** 索引標籤,按一下 [選項]****。To find advanced calculation options, on the File tab, click Options. [Excel 選項] 對話方塊,按一下 [進階]****。In the Excel Options dialog box, click Advanced. 在 [公式]**** 區段下方,設定計算選項。Under the Formulas section, set calculation options.

圖 3:進階計算選項Figure 3. Advanced calculation options

Backstage 檢視中的進階計算選項

當您開啟 Excel 時,或是當 Excel 在沒有開啟任何活頁簿的情況下執行時,初始的計算模式和反覆運算設定,都會採用您開啟的第一個非範本且沒有增益集的活頁簿之設定。When you start Excel, or when it is running without any workbooks open, the initial calculation mode and iteration settings are set from the first non-template, non-add-in workbook that you open. 這表示之後開啟的活頁簿中,其中的計算設定將會忽略,但當然您也可以隨時在 Excel 中變更這項設定。This means that the calculation settings in workbooks opened later are ignored, although, of course, you can manually change the settings in Excel at any time. 儲存活頁簿時,也會一併將目前的計算設定儲存在活頁簿中。When you save a workbook, the current calculation settings are stored in the workbook.

自動計算Automatic calculation

自動計算模式是指每次有變更或每次開啟活頁簿時,Excel 都會自動重新計算所有開啟中活頁簿。Automatic calculation mode means that Excel automatically recalculates all open workbooks at every change and when you open a workbook. 通常當您以自動模式開啟活頁簿而 Excel 進行重新計算時,您不會看見重新計算,這是因為從上次儲存活頁簿至今,尚未有任何變更。Usually when you open a workbook in automatic mode and Excel recalculates, you do not see the recalculation because nothing has changed since the workbook was saved.

若您使用比上次計算該活頁簿時的版本更新的 Excel 開啟活頁簿時 (比方說先用 Excel 2013 再用 Excel 2016),您就會注意到計算的情形。You might notice this calculation when you open a workbook in a later version of Excel than you used the last time that the workbook was calculated (for example, Excel 2016 versus Excel 2013). 由於 Excel 計算引擎的不同,每當 Excel 開啟一個以舊版 Excel 儲存的活頁簿時,就會執行一次完整計算。Because the Excel calculation engines are different, Excel performs a full calculation when it opens a workbook that was saved using an earlier version of Excel.

手動計算Manual calculation

手動計算模式是指只有當您按下 F9 或 CTRL+ALT+F9 要求時,或當您儲存活頁簿時,Excel 才會重新計算所有開啟中活頁簿。Manual calculation mode means that Excel recalculates all open workbooks only when you request it by pressing F9 or Ctrl+Alt+F9, or when you save a workbook. 對於需要耗費較多時間重新計算的活頁簿,您必須將計算設為手動模式,以免每次進行變更時都要等待延遲的時間。For workbooks that take more than a fraction of a second to recalculate, you must set calculation to manual mode to avoid a delay when you make changes.

在手動模式下,當活頁簿需要重新計算時,Excel 會在狀態列顯示 [計算]**** 告知您。Excel tells you when a workbook in manual mode needs recalculation by displaying Calculate in the status bar. 如果您的活頁簿含有循環參照,且已選取反覆運算選項,狀態列也會顯示 [計算]****。The status bar also displays Calculate if your workbook contains circular references and the iteration option is selected.

反覆運算設定Iteration settings

如果您的活頁簿中含有刻意存在的循環參照,您可以利用反覆運算設定,控制活頁簿重新計算 (反覆運算) 的次數上限以及收斂條件 (最大誤差:何時停止)。If you have intentional circular references in your workbook, the iteration settings enable you to control the maximum number of times the workbook is recalculated (iterations) and the convergence criteria (maximum change: when to stop). 請清空反覆運算方塊,萬一出現意外的循環參照時,Excel 會先警告您,而不會嘗試解決循環參照。Clear the iteration box so that if you have accidental circular references, Excel will warn you and will not try to solve them.

活頁簿的 ForceFullCalculation 屬性Workbook ForceFullCalculation property

當您將這個活頁簿屬性設定為 True 時,Excel 的智慧重算會關閉,每次重新計算都會重新計算所有開啟中活頁簿的所有公式。When you set this workbook property to True, Excel's Smart Recalculation is turned off and every recalculation recalculates all the formulas in all the open workbooks. 若是一些複雜的活頁簿,耗費在建立和維護智慧重算所需依存性樹狀結構的時間,將遠多於透過智慧重算所節省下的時間。For some complex workbooks, the time taken to build and maintain the dependency trees needed for Smart Recalculation is larger than the time saved by Smart Recalculation.

如果您的活頁簿必須耗費極長的時間才能開啟,或是即使是在手動模式下,進行小幅變更卻要耗費很長的時間,這種情形值得嘗試使用 ForceFullCalculation。If your workbook takes an excessively long time to open, or making small changes takes a long time even in manual calculation mode, it may be worth trying ForceFullCalculation.

如果活頁簿的 ForceFullCalculation 屬性已設定為 True,狀態列會出現 [計算]****。Calculate will appear in the status bar if the workbook ForceFullCalculation property has been set to True.

您可以使用 VBE (ALT+F11),選取 [專案總管]**** (Ctrl+R) 的 ThisWorkbook,然後顯示 [屬性視窗]**** (F4),控制這個設定。You can control this setting using the VBE (Alt+F11), selecting ThisWorkbook in the Project Explorer (Ctrl+R) and showing the Properties Window (F4).

圖 4:設定 Workbook.ForceFullCalculation 屬性Figure 4. Setting the Workbook.ForceFullCalculation property

設定 ForceFullCalculation

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

利用接下來的步驟和方法,讓您的活頁簿計算得更快。Use the following steps and methods to make your workbooks calculate faster.

處理器速度與多核心Processor speed and multiple cores

對於大多數的 Excel 版本而言,擁有一顆更快的處理器,理所當然可以讓 Excel 計算得更快。For most versions of Excel, a faster processor will, of course, enable faster Excel calculation. Excel 2007 中引進的多執行緒計算引擎,可讓 Excel 充分發揮多處理器系統的優勢,明顯提升多數活頁簿的效能。The multithreaded calculation engine introduced in Excel 2007 enables Excel to make excellent use of multiprocessor systems, and you can expect significant performance gains with most workbooks.

對大多數的大型活頁簿而言,多處理器規模的計算效能增益與實體處理器的數量幾乎成正比。For most large workbooks, the calculation performance gains from multiple processors scale almost linearly with the number of physical processors. 然而,實體處理器的超執行緒產生的效能增益卻不大。However, hyper-threading of physical processors only produces a small performance gain.

如需詳細資訊,請參閱 Excel 效能:改良效能與限制For more information, see Excel performance: Performance and limit improvements.

RAMRAM

建立虛擬記憶體分頁檔的分頁過程很緩慢。Paging to a virtual-memory paging file is slow. 您必須擁有足夠的實體 RAM 供作業系統、Excel 及活頁簿使用。You must have enough physical RAM for the operating system, Excel, and your workbooks. 若計算期間您的硬碟活動量比平常大,而且您沒有執行會觸發磁碟活動的使用者自訂函數,代表您需要更多的 RAM。If you have more than occasional hard disk activity during calculation, and you are not running user-defined functions that trigger disk activity, you need more RAM.

如前所述,近期版本的 Excel 會有效運用大量的記憶體,32 位元版 Excel 2007 和 Excel 2010 可以處理佔用高達 2 GB 活頁簿記憶體的單一活頁簿或多個活頁簿。As mentioned, recent versions of Excel can make effective use of large amounts of memory, and the 32-bit version of Excel 2007 and Excel 2010 can handle a single workbook or a combination of workbooks using up to 2 GB of memory.

使用大量位址提示 (LAA,Large Address Aware) 的 32 位元版 Excel 2013 和 Excel 2016,視安裝的 Windows 版本而定,可以使用高達 3 GB 或 4 GB 的記憶體。The 32-bit versions of Excel 2013 and Excel 2016 that use the Large Address Aware (LAA) feature can use up to 3 or 4 GB of memory, depending on the version of Windows installed. Excel 的 64 位元版本可以處理更大的活頁簿。The 64-bit version of Excel can handle larger workbooks. 如需詳細資訊,請參閱 Excel 效能:改良效能與限制的<大型資料集、LAA 和 64 位元版 Excel>一節。For more information, see the "Large data sets, LAA, and 64-bit Excel" section in Excel performance: Performance and limit improvements.

有效運算的約略準則是,擁有足夠的 RAM 可以容納您必須在同時間開啟的最大活頁簿集合,再加上 1 至 2 GB 供 Excel 和作業系統使用,再加上額外的 RAM 供任何其他執行的應用程式使用。A rough guideline for efficient calculation is to have enough RAM to hold the largest set of workbooks that you need to have open at the same time, plus 1 to 2 GB for Excel and the operating system, plus additional RAM for any other running applications.

測量計算時間Measuring calculation time

若要更快速地計算活頁簿,您必須能夠精確地測量計算時間。To make workbooks calculate faster, you must be able to accurately measure calculation time. 您需要比 VBA 的 Time 函數更快速且更精確的計時器。You need a timer that is faster and more accurate than the VBA Time function. 下列程式碼範例中顯示的 MICROTIMER() 函數使用 Windows API 呼叫系統的高精度計時器。The MICROTIMER() function shown in the following code example uses Windows API calls to the system high-resolution timer. 其測量時間間隔可小至百萬分之一秒。It can measure time intervals down to small numbers of microseconds. 請注意,由於 Windows 是一個多工處理的作業系統,也因為第二次計算某個項目的速度會比第一次來得快,所以您測得的時間結果通常不一樣。Be aware that because Windows is a multitasking operating system, and because the second time that you calculate something, it may be faster than the first time, the times that you get usually do not repeat exactly. 若要測得最準的結果,最好是多測幾次,然後再取平均值。To achieve the best accuracy, measure time calculation tasks several times and average the results.

如需有關 Visual Basic Editor 如何大幅影響 VBA 使用者定義函數的效能,請參閱 Excel 效能:最佳化效能阻礙的秘訣的<較快的 VBA 使用者定義函數>一節。For more information about how the Visual Basic Editor can significantly affect VBA user-defined function performance, see the "Faster VBA user-defined functions" section in Excel performance: Tips for optimizing performance obstructions.

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

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

' Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency

' Get ticks.
    getTickCount cyTicks1                            

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

若要測量計算時間,您必須先呼叫適合的計算方法。To measure calculation time, you must call the appropriate calculation method. 下列副程式可為您測出一個範圍的計算時間、一個工作表或所有開啟中活頁簿的重新計算時間,或是所有開啟中活頁簿的完整計算時間。These subroutines give you calculation time for a range, recalculation time for a sheet or all open workbooks, or full calculation time for all open workbooks.

請將所有這些副程式和函數全部複製到標準 VBA 模組。Copy all these subroutines and functions into a standard VBA module. 若要開啟 VBA 編輯器,請按下 ALT+F11。To open the VBA editor, press Alt+F11. 在 [插入]**** 功能表,選取 [模組]****,然後將程式碼複製到模組。On the Insert menu, select Module, and then copy the code into the module.

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

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

' Initialize
    dTime = MicroTimer              

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

        ' Switch off iteration.

        If Application.Iteration <> False Then
            Application.Iteration = False
        End if
        
        ' Max is used range.

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

        ' Include array cells outside selection.

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

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

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

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

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

Finish:

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

若要在 Excel 中執行副程式,按下 ALT+F8。To run the subroutines in Excel, press Alt+F8. 選取您想要的副程式,然後按一下 [執行]****。Select the subroutine you want, and then click Run.

圖 5:Excel 的 [巨集] 視窗中顯示計算計時器Figure 5. The Excel Macro window showing the calculation timers

Excel 的巨集視窗

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

大多數計算緩慢的活頁簿,只有幾個問題區域或障礙就耗費了大半的計算時間。Most slow-calculating workbooks have only a few problem areas or obstructions that consume most of the calculation time. 如果您還不知道障礙在哪裡,請參考本節中說明所列循序向下鑽研的做法,找出障礙所在。If you do not already know where they are, use the drill-down approach outlined in this section to find them. 如果您已知問題所在,您必須測量每個障礙所耗費的計算時間,因此您可以調整工作的優先順序,消除障礙。If you do know where they are, you must measure the calculation time that is used by each obstruction so that you can prioritize your work to remove them.

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

此循序向下鑽研做法是先測量活頁簿的計算時間,再測量每個工作表的計算時間,再測量慢速工作表上的公式區塊。The drill-down approach starts by timing the calculation of the workbook, the calculation of each worksheet, and the blocks of formulas on slow-calculating sheets. 請依序完成每個步驟,並記錄下計算時間。Do each step in order and note the calculation times.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

消耗計算時間的不是公式的數量或活頁簿的大小,It is not the number of formulas or the size of a workbook that consumes the calculation time. 而是儲存格參照和計算作業的數量,以及使用的函數效率。It is the number of cell references and calculation operations, and the efficiency of the functions being used.

因為大部分的活頁簿是由混合絕對參照和相對參照的複製公式建構而成,這些活頁簿通常包含有大量的公式,這些公式又包含有重複或相同的計算和參照。Because most worksheets are constructed by copying formulas that contain a mixture of absolute and relative references, they usually contain a large number of formulas that contain repeated or duplicated calculations and references.

請避免使用複雜的巨型公式和陣列公式。Avoid complex mega-formulas and array formulas. 一般來說,較佳的作法是使用較多的欄與列以及較少的複雜計算。In general, it is better to have more rows and columns and fewer complex calculations. 因為如此可以讓 Excel 的智慧重算與多執行緒計算更良好地發揮最佳化計算的能力。This gives both the smart recalculation and the multithreaded calculation in Excel a better opportunity to optimize the calculations. 這樣也比較容易理解及偵錯。It is also easier to understand and debug. 下列有一些規則可協助您加速活頁簿計算。The following are a few rules to help you speed up workbook calculations.

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

找出相同、重複和不必要的計算,然後估算 Excel 大約需要多少儲存格參照和計算,才能計算出此障礙的結果。Look for duplicated, repeated, and unnecessary calculations, and figure out approximately how many cell references and calculations are required for Excel to calculate the result for this obstruction. 再思考如何使用更少的參照和計算就能得出相同的結果。Think how you might obtain the same result with fewer references and calculations.

這通常需要下列一或多個步驟:Usually this involves one or more of the following steps:

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

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

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

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

當您找到一個和函數或陣列公式相關的障礙時,請想想看有沒有更有效率的方式可以達成相同的結果。When you find an obstruction that involves a function or array formulas, determine whether there is a more efficient way to achieve the same result. 例如:For example:

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

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

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

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

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

愈能充分發揮 Excel 的智慧重算和多執行緒計算,每次 Excel 重新計算的處理量就愈少,因此建議您:The better use you make of smart recalculation and multithreaded calculation in Excel, the less processing has to be done every time that Excel recalculates, so:

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

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

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

  • 避免使用單一執行緒函數:Avoid single-threaded functions:

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

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

某些變更的結果可能會讓您大吃一驚,算出的結果可能不如預期,或是計算速度比原先預期的更慢。Some of the changes that you make might surprise you, either by not giving the answer that you thought they would, or by calculating more slowly than you expected. 因此,您必須計時並測試每項變更,方法如下:Therefore, you should time and test each change, as follows:

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

  2. 進行變更。Make the change.

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

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

規則範例Rule examples

下列各節提供如何使用規則加快計算的範例。The following sections provide examples of how to use the rules to speed up calculation.

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

舉例來說,您想計算某一欄的日期區間加總,而這一欄含有 2,000 筆數字。For example, you need to calculate the period-to-date sums of a column that contains 2,000 numbers. 假設欄 A 含有數字,而欄 B 和欄 C 應含有日期區間加總的結果。Assume that column A contains the numbers, and that column B and column C should contain the period-to-date totals.

您可以使用有效率的函數 SUM 撰寫公式。You could write the formula using SUM, which is an efficient function.

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

圖 6:日期區間 SUM 公式範例Figure 6. Example of period-to-date SUM formulas

日期區間 SUM 公式範例

將公式向下複製到 B2000。Copy the formula down to B2000.

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

如果您撰寫如下所示的公式,您可以消除這個相同的部分。You can eliminate this duplication if you write the formulas as follows.

  C1=A1
  C2=C1+A1

將這個公式向下複製到 C2000。Copy this formula down to C2000.

現在,一共要相加多少儲存格參照?Now how many cell references are added up in total? 除了第一個公式,每個公式使用兩個儲存格參照。Each formula, except the first formula, uses two cell references. 因此,總共是 1999*2+1=3999。Therefore, the total is 1999*2+1=3999. 這減少了 500 倍的儲存格參照。This is a factor of 500 fewer cell references.

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

錯誤處理Error handling

如果您有一個計算密集的公式,您希望當公式發生錯誤時,結果顯示為零 (完全相符的查閱經常發生這種情形),這樣的公式有許多撰寫方式。If you have a calculation-intensive formula where you want the result to be shown as zero if there is an error (this frequently occurs with exact match lookups), you can write this in several ways.

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

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

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

    A1=time expensive formula

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

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

    B1=IFERROR(time expensive formula,0)


動態計算唯一值Dynamic count unique

圖 7:計算唯一值的資料清單範例Figure 7. Example list of data for count unique

計算唯一資料範例

如果您的清單在欄 A 中共有 11,000 列資料,而且這些資料經常變動,而您需要一個公式可以動態計算清單中具唯一性的項目總數,且不計空白,以下是幾種可能的做法。If you have a list of 11,000 rows of data in column A, which frequently changes, and you need a formula that dynamically calculates the number of unique items in the list, ignoring blanks, following are several possible solutions.

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

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

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

    Public Function COUNTU(theRange As Range) As Variant
        Dim colUniques As New Collection
        Dim vArr As Variant
        Dim vCell As Variant
        Dim vLcell As Variant
        Dim oRng As Range
    
        Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
        vArr = oRng
        On Error Resume Next
        For Each vCell In vArr
        If vCell <> vLcell Then
            If Len(CStr(vCell)) > 0 Then
                 colUniques.Add vCell, CStr(vCell)
            End If
        End If
        vLcell = vCell
        Next vCell
    
        COUNTU = colUniques.Count
    End Function
    
  • 新增一欄公式Adding a column of formulas. 看看先前的資料範例,您會發現資料已排序完成 (Excel 耗費 0.5 秒排序 11,000 列)。If you look at the previous sample of the data, you can see that it is sorted (Excel takes 0.5 seconds to sort the 11,000 rows). 您可以利用這點,新增一欄可檢查本列資料是否和前一列相同的公式。You can exploit this by adding a column of formulas that checks if the data in this row is the same as the data in the previous row. 如果不同,公式傳回 1。If it is different, the formula returns 1. 否則,傳回 0。Otherwise, it returns 0.

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

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

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

      =SUM(B2:B11000)
    

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

總結Conclusion

您可利用 Excel 有效率地管理大上許多的工作表,相較於早期版本,在計算速度方面有大幅的效能提升。Excel enables you to effectively manage much larger worksheets, and it provides significant improvements in calculation speed compared with early versions. 當我們建立大型工作表時,很容易不小心設計成計算緩慢的結構。When you create large worksheets, it is easy to build them in a way that causes them to calculate slowly. 計算緩慢的工作表會提高出錯的風險,因為使用者難以在計算期間保持專注。Slow-calculating worksheets increase errors because users find it difficult to maintain concentration while calculation is occurring.

藉由使用一系列簡單明瞭的技巧,便可以讓原本計算緩慢的工作表加快 10 到 100 倍。By using a straightforward set of techniques, you can speed up most slow-calculating worksheets by a factor of 10 or 100. 下次您在設計和建立工作表時,不妨運用這些技巧,有效提升計算速度。You can also apply these techniques as you design and create worksheets to ensure that they calculate quickly.

另請參閱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.