SUBTOTAL

傳回以指定彙總函式得出的儲存格垂直範圍小計。

使用範本

SUBTOTAL(1,A2:A5,B2:B8)

語法

SUBTOTAL(function_code, range1, [range2, ...])

• 函式代碼 進行小計彙總運算時所用的函式。

  • 1 is AVERAGE

  • 2 is COUNT

  • 3 is COUNTA

  • 4 is MAX

  • 5 is MIN

  • 6 is PRODUCT

  • 7 is STDEV

  • 8 is STDEVP

  • 9 is SUM

  • 10 is VAR

  • 11 is VARP

  • Hidden values can be skipped for any of these codes by prepending 10 (to the single-digit codes) or 1 (to the 2-digit codes). e.g. 102 for COUNT while skipping hidden cells, and 110時間範圍VAR while doing so.

• 範圍_1 計算小計時納入的第一個範圍。

• 範圍_2 - Additional ranges over which to calculate subtotals.

附註

• Cells that are hidden due to autofilter criteria are never included 特別要注意SUBTOTAL, irrespective of the 函式代碼 used.

• Cells within any of the specified 範圍 arguments that contain SUBTOTAL calls are ignored to prevent double-counting.

• SUBTOTAL can be used to created dynamic dashboards by having the function code argument refer to another cell. When combined with list-based data validation, this cell can become a drop-down list that instantly updates the entire dashboard.

• SUBTOTAL can be used for quick analysis of different subsets of data by building a subtotal dashboard above a filtered region. 每個 time the filter criteria change, the dashboard will automatically update with new aggregations.

• Using SUBTOTAL helps prevent double-counting associated with 簡單SUM公式

另請參閱

VARP: 根據整個總體計算變異數。

VAR: 根據樣本計算變異數。

SUM: 傳回一連串數字和/或儲存格的總和。

STDEVP: 根據整個總體計算標準差。

STDEV: 根據樣本計算標準差。

PRODUCT: 傳回一連串數字相乘的結果。

MIN: 傳回數字資料集中的最小值。

MAX: 傳回數字資料集中的最大值。

COUNTA: 傳回資料集中值的個數。

COUNT:

傳回資料集中的數值個數。

AVERAGE: 傳回資料集的數字平均值 (忽略文字)。

範例