Excel 中有一個低調的函數很少人知道,但它確是個名副其實的全能函數王,這個函數就是 aggregate。
為什麼說它是全能函數王呢?因為這個函數幾乎集合了大部分函數的計算功能。
aggregate 強大到什麼程度,來看一下就知道了。
語法:
AGGREGATE 函數有兩種形式;
- 引用形式:AGGREGATE(function_num, options, ref1, [ref2], …)
- 數組形式:AGGREGATE(function_num, options, array, [k])
功能:
以下是 aggregate 函數的功能數字對應列表:
選項:
以下是它的選項數字列表:
案例 1:忽略錯誤值求和
其實 aggregate 的功能說明列表,已經對這個函數的用法闡述得比較清楚了,不過我們還是來看一個比較典型的用法案例。
對下表中的 C 列忽略錯誤值求和。
解決方案 1:
先看一下用普通的 sum 函數求和,因為錯誤值的存在,無法求和:
如果用 aggregate 函數,這就不是個問題:
1. 在 C16 單元格輸入以下公式:
=AGGREGATE(9,6,C2:C15)
公式釋義:
- 對照上面的列表說明不難看出,第一個參數 9 表示:求和
- 第二個參數 6 表示:忽略錯誤值
- 最後一個參數是數據區域
2. 這是求和結果:
案例 2:帶條件求最大/最小值
這個功能就比較厲害了,相當於取代了 maxifs/minifs 函數的數組效果。
還是以這個表為例,求一班的最高分。
解決方案 2:
1. 在 E2 單元格輸入以下公式即可:
=AGGREGATE(14,6,C2:C15/(A2:A15="一班"),1)
公式釋義:
- 14:返回第 k 個最大值
- 6:忽略錯誤值
- C2:C15:統計區域
- (A2:A15="一班"):
- 統計條件;
- 如果滿足條件返回 1,不滿足則返回 0;
- 作為分母被數據區域除,會得出一組數組:有值或出錯
- 錯誤值會被第二個參數忽略
- 1:第 1 大,即最大值
閱讀更多 Excel學習世界 的文章