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 大,即最大值 |
|