Excel AGGREGATE 函數教學
AGGREGATE 函數是一個強大的函數，用於執行多種數學和統計計算（如求和、平均值、最大值等），並且能夠忽略錯誤值、隱藏的行或包含小計的數據。
? 語法
AGGREGATE(function_num, options, array, [k])
- function_num（必填）：指定要執行的函數類型（例如求和、平均值等）。
- options（必填）：決定是否忽略錯誤值、隱藏的行等。
- array（必填）：要計算的範圍或數組。
- [k]（選填）：如果選擇的函數需要額外參數（如
LARGE、SMALL），這個參數就必須填寫。
? 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 | 母體變異數 |
| 14 | LARGE | 取前第 k 大的數值 |
| 15 | SMALL | 取前第 k 小的數值 |
| 16 | PERCENTILE.INC | 百分位數（包含 0-1） |
| 17 | QUARTILE.INC | 四分位數 |
? options（選項代碼表）
| 代碼 | 作用 |
|---|---|
| 0 | 忽略隱藏行、錯誤值、含小計的數據 |
| 1 | 忽略隱藏行 |
| 2 | 忽略錯誤值 |
| 3 | 忽略隱藏行和錯誤值 |
| 4 | 忽略小計 |
| 5 | 忽略隱藏行和小計 |
| 6 | 忽略錯誤值和小計 |
| 7 | 忽略隱藏行、錯誤值和小計 |
? 範例
1️⃣ 計算忽略錯誤值的總和
=AGGREGATE(9, 2, A1:A10)
說明：計算
A1:A10的總和 (9=SUM)，並忽略錯誤值 (2=忽略錯誤)。
2️⃣ 計算前三大數值的平均
=AGGREGATE(1, 3, A1:A10, 3)
說明：計算
A1:A10內前三大數值的平均 (1=AVERAGE)，忽略隱藏行與錯誤 (3=忽略錯誤和隱藏行)。
3️⃣ 取第 2 大的數字（忽略錯誤值）
=AGGREGATE(14, 6, A1:A10, 2)
說明：找出
A1:A10中第二大的數 (14=LARGE)，並忽略錯誤值和小計 (6=忽略錯誤與小計)。
4️⃣ 計算 A1:A10 的最大值，但忽略隱藏的行
=AGGREGATE(4, 1, A1:A10)
說明：找出
A1:A10中的最大值 (4=MAX)，但忽略被隱藏的行 (1=忽略隱藏行)。
? AGGREGATE VS SUBTOTAL
| 函數 | 功能 | 可忽略錯誤值 | 可計算數組 |
|---|---|---|---|
SUBTOTAL | 計算部分數據的統計結果 | ❌ | ❌ |
AGGREGATE | 功能更強，可忽略錯誤、小計等 | ✅ | ✅ |
? AGGREGATE 的應用場景
- 忽略錯誤值，例如當數據包含
#DIV/0!或#VALUE!時仍能計算。 - 處理隱藏行，適用於篩選後仍要統計可見數據的情況。
- 選取前 K 大或 K 小的值，適用於排名計算。
? 總結
✅ AGGREGATE 是 比 SUBTOTAL 更強大 的函數，適合用來計算帶有錯誤值、隱藏行或小計的數據。
✅ 支援 19 種函數（如 SUM、AVERAGE、MAX、LARGE 等），適用於數據分析與報表整理。
✅ 能夠 忽略錯誤、隱藏行、小計，提高計算的靈活性。
如果你需要在 Excel 中做數據分析，AGGREGATE 是必學函數之一！ ?