aggregate
消除條件格式限制的函數
AGGREGATE 函數消除了條件格式的限制。如果區域中存在錯誤,則數據條、圖標集和色階將無法顯示條件格式。
EXCEL中AGGREGATE 函數返回列表或資料庫中的合計。
這是因為當計算區域中存在錯誤時,MIN、MAX 和 PERCENTILE 函數不進行計算。出於相同的原因,LARGE、SMALL 和 STDEVP 函數也會影響某些條件格式規則的相應功能。
通過使用 AGGREGATE 函數,這些錯誤將被忽略,因而可以實現這些函數。此外,AGGREGATE 函數可將不同的聚合函數應用於列表或資料庫,並提供忽略隱藏行和錯誤值的選項。
AGGREGATE(function_num, options, ref1, [ref2], …)
AGGREGATE(function_num,options,array,[k])AGGREGATE 函數語法具有以下參數:
function_num 必需。一個介於1到19之間的數字,指定要使用的函數。
function_num | 函數 |
1 | AVERAGE |
2 | COUNT |
3 | COUNTA |
4 | MAX |
5 | MIN |
6 | PRODUCT |
7 | STDEV.S |
8 | STDEV.P |
9 | SUM |
10 | VAR.S |
11 | VAR.P |
12 | MEDIAN |
13 | MODE.SNGL |
14 | LARGE |
15 | SMALL |
16 | PERCENTILE.INC |
17 | QUARTILE.INC |
18 | PERCENTILE.EXC |
19 | QUARTILE.EXC |
Options 必需。一個數值,決定在函數的計算區域內要忽略哪些值。
選項 | 行為 |
0 或省略 | 忽略嵌套 SUBTOTAL 和 AGGREGATE 函數 |
1 | 忽略隱藏行、嵌套 SUBTOTAL 和 AGGREGATE 函數 |
2 | 忽略錯誤值、嵌套 SUBTOTAL 和 AGGREGATE 函數 |
3 | 忽略隱藏行、錯誤值、嵌套 SUBTOTAL 和 AGGREGATE 函數 |
4 | 忽略空值 |
5 | 忽略隱藏行 |
6 | 忽略錯誤值 |
7 | 忽略隱藏行和錯誤值 |
Ref1 必需。函數的第一個數值參數,這些函數使用要為其計算聚合值的多個數值參數。 Ref2,... 可選。要為其計算聚合值的 2 至 253 個數值參數。對於使用數組的函數,ref1 可以是一個數組或數組公式,也可以是對要為其計算聚合值的單元格區域的引用。ref2 是某些函數必需的第二個參數。以下函數需要 ref2 參數:
函數 |
LARGE(array,k) |
SMALL(array,k) |
PERCENTILE.INC(array,k) |
QUARTILE.INC(array,quart) |
PERCENTILE.EXC(array,k) |
QUARTILE.EXC(array,quart) |
function_num
在將 AGGREGATE 函數輸入到工作表上的單元格中時,只要鍵入 function_num 參數,就會立即看到可以作為參數使用的所有函數的列表。
錯誤
如果第二個引用參數是必需的但未提供,AGGREGATE 將返回 #VALUE! 錯誤。如果有一個或多個引用是三維引用,AGGREGATE 將返回錯誤值 #VALUE!。
嵌套聚合
如果 ref1, ref2,… 中有其他 AGGREGATE (或嵌套 AGGREGATE),將忽略這些嵌套 AGGREGATE,避免重複計算。如果 AGGREGATE 函數的引用中包含 SUBTOTAL,將忽略這些 SUBTOTAL。如果 SUBTOTAL 函數中包含 AGGREGATE,將忽略這些 AGGREGATE。
區域類型
AGGREGATE 函數適用於數據列或垂直區域,不適用於數據行或水平區域。例如:當使用選項 1 對某個水平區域進行分類匯總時,例如 AGGREGATE(1, 1, ref1),則隱藏某一列並不會影響聚合總值。但是,隱藏垂直區域中的某一行就會對聚合總值產生影響。