Excel AGGREGATE 函數用法：忽略隱藏列與錯誤值計算

AGGREGATE 函數整合了 19 種常見的統計計算，最大的特色是能夠在計算時自動忽略隱藏列、錯誤值以及巢狀的小計函數，非常適合用於含有篩選或除錯狀態的報表。

Excel AGGREGATE 函數語法

=AGGREGATE(函數代碼, 選項, 範圍, [k])

參數	必填	說明
函數代碼	是	1 到 19 的數字，代表要執行的計算類型（例如 1 是 AVERAGE，9 是 SUM，14 是 LARGE）。
選項	是	0 到 7 的數字，決定要忽略哪些資料（例如 3 為忽略隱藏列、錯誤值及巢狀小計，6 為僅忽略錯誤值）。
範圍	是	要進行計算的儲存格範圍或陣列。
k	選填	當函數代碼為 14 到 19 時（如 LARGE、SMALL），用來指定第幾個最大或最小值。

回傳：根據指定的函數代碼與選項，回傳計算後的單一數值。

適用版本：Excel 2010 以上

AGGREGATE 函數範例

範例 1：忽略錯誤值進行加總

計算業績時，資料範圍內包含除以零的錯誤值，需將其排除並加總剩餘數值。

業務	業績
王小明	5000
陳怡君	#DIV/0!
林家豪	8000

在 C2 輸入公式：

=AGGREGATE(9, 6, B2:B4)

結果：13000。函數代碼 9 代表加總，選項 6 代表忽略錯誤值。公式會自動略過 B3 的錯誤值，將 5000 與 8000 加總，得到 13000。

範例 2：忽略隱藏列與錯誤值計算平均

計算產品庫存平均數量，並排除錯誤值與被篩選隱藏的資料列。

產品	庫存
蘋果	120
香蕉	0
橘子	#N/A
芭樂	90

在 C2 輸入公式：

=AGGREGATE(1, 7, B2:B5)

結果：70。函數代碼 1 代表平均值，選項 7 代表忽略隱藏列與錯誤值。公式略過 B4 的錯誤值，將 120、0、90 三個數值平均，算出 70。

範例 3：找出排除錯誤值後的第二高業績

在含有錯誤值的業績清單中，找出數值第二高的業績。

部門	業績
北區	45000
中區	#VALUE!
南區	52000
東區	38000

在 C2 輸入公式：

=AGGREGATE(14, 6, B2:B5, 2)

結果：45000。函數代碼 14 代表 LARGE，選項 6 忽略錯誤值。參數 k 設為 2，代表在排除 B3 的錯誤後，找出剩餘數值中第二高的業績 45000。

使用 AGGREGATE 的技巧與注意事項

• AGGREGATE 函數的隱藏列忽略功能僅對垂直資料（直行）有效，無法忽略水平隱藏的欄位。
• 選項設定為 3 或 7 時，除了忽略隱藏列與錯誤值，還會自動忽略範圍內的 SUBTOTAL 或其他 AGGREGATE 函數，避免重複計算。
• 函數代碼 1 到 13 不需要 k 值參數，如果多輸入了 k 值會導致計算錯誤。

AGGREGATE 常見錯誤與解決方法

錯誤	原因	解決方法
#VALUE!	使用了函數代碼 14 到 19（如 LARGE、SMALL 等需要指定位置的計算），卻沒有輸入 k 值參數。	確認公式的最後一個參數是否有填入正確的數字 k，例如尋找第二大值需在結尾加上 2。
#NUM!	輸入了無效的函數代碼（不在 1-19 範圍內）或無效的選項代碼（不在 0-7 範圍內）。	檢查公式中的前兩個數字參數，確保它們符合 AGGREGATE 函數規定的代碼範圍。

Google 試算表可以用 AGGREGATE 嗎？

Google 試算表目前不支援 AGGREGATE 函數。若需忽略隱藏列可改用 SUBTOTAL，若需忽略錯誤值則需搭配 IFERROR 函數處理。

下載 AGGREGATE 範例檔

上面的範例都整理在這個檔案裡，每個範例一個工作表，黃色格子是公式，可以直接改資料看結果變化：AGGREGATE 範例.xlsx（範例公式都在試算表軟體實際計算過）。

常見問題

相關函數

• SUBTOTAL

這篇收錄在「Excel 函數字典」。