这里有最实用的Excel使用技巧，通过提高Excel技能，可以让你轻松应对工作中的表格处理，提高你的工作效率！

上期讲了 SUBTOTAL，能自动忽略隐藏行，财务对账、库存盘点省了不少时间。

但有个问题——SUBTOTAL 遇到错误值就罢工。比如某列数据中某个单元格是#DIV/0! 或者#N/A，SUBTOTAL 直接返回错误，拿它一点办法都没有。

还有更头疼的场景：你要对筛选后的数据取第二大值、算中位数、或者做乘积求和，SUBTOTAL 根本做不到。

今天的主角 AGGREGATE 函数，Excel 2010 及以上版本内置，可以把它理解成 SUBTOTAL 的完全进化体——能干 SUBTOTAL 能干的所有事（忽略隐藏行 + 求和/计数/平均），还能干它干不了的事（忽略错误值 + 19种函数 + 数组运算）。

一、AGGREGATE 是什么？

一句话说清楚：

AGGREGATE = SUBTOTAL 的完全进化版，多了「忽略错误值」和「19种函数」两项超能力

基础语法：

=AGGREGATE(功能码, 忽略选项, 数据区域)

功能码表（共19种）：

忽略选项表（共8种）：

💡建议直接用选项码 3 或 7——忽略隐藏行+忽略错误值，一招通吃所有情况。

二、实战场景

场景1：销售报表——数据有错误值也能求和

痛点

你有一张销售日报表，有的地区数据还没到齐，填了#N/A。有的销售员录了个#DIV/0!的公式忘改了。你想求和，但SUM遇到错误值直接返回#N/A，整个汇总行都是红的。换成SUBTOTAL？它也一样。难道要手动一个个去删？

示例数据

公式：=AGGREGATE(9, 6, D2:D9)

结果：56,800（只加有效数字，跳过了 #N/A 和 #DIV/0!）

如果用 SUM：=SUM(D2:D9) → #N/A（一个错误值就全部完蛋）

分析：选项码=6（或选项码=2），遇到 #N/A、#DIV/0!、#VALUE! 等错误值统统跳过。普通函数和SUBTOTAL遇到错误值会直接传染，整个结果都变错误。AGGREGATE是唯一内置「遇错跳过」功能的统计函数。这在财务报表核对场景下尤其好用——当上游数据还有空位时，你照样可以出一个「已有数据的汇总」。

┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄

场景2：竞赛评分——忽略隐藏行算平均分

痛点

公司搞技能大赛，专家评委打分。有的评委特别仁慈（打分偏高），有的特别严格（打分偏低）。更麻烦的是，有的评委评分列被隐藏了，但AVERAGE不会自动忽略隐藏列。

示例数据

公式：=AGGREGATE(1, 3, B4:H4)

结果：忽略被隐藏的评委评分列后，对可见列求平均分。

分析：选项码=3（忽略隐藏行+忽略错误值），评委评分数据再乱也不怕。注意功能码 1 对应 AVERAGE，加上选项码3 之后，隐藏的列不会进计算。

┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄

场景3：销售排名——筛选后取第2名，错误值不影响

痛点

销售排行榜上，有的销售员数据异常（填了#N/A表示未出单），你想在筛选「只看华东区」后，找出销售额第二高的销售员。以前你得一步步清除错误值→筛选→LARGE——三步走，现在AGGREGATE一步到位。

示例数据

公式：=AGGREGATE(14, 3, C2:C8, 2)

结果：筛选「华东」后 → 9,800（第2高；最高是15,000）

分析：功能码14对应LARGE，取第k大值。选项码=3同时忽略隐藏行和错误值。换成SUBTOTAL：可以忽略隐藏行，但遇到#N/A直接罢工。换成LARGE：遇到#N/A罢工，且不会因为筛选自动调整范围。AGGREGATE是唯一一个同时搞定「数组运算 + 忽略隐藏 + 跳过错误」的通用函数。

┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄

场景4：月度费用统计——中位数比平均值更真实

痛点

老板问：「上个月部门报销费用的中位数是多少？」平均值容易被极端值拉偏（比如有人报了10万的大单），但中位数能真实反映「普通员工的报销水平」。SUBTOTAL没有MEDIAN功能码，但AGGREGATE有（功能码12）。

示例数据

公式：=AGGREGATE(12, 3, C2:C8)

结果：1,800（中位数——钱七的10万大单被自动自然排除）

如果用AVERAGE算平均：102,000+...+230 = 16,027（被钱七一个人拉高了近9倍）

分析：AGGREGATE的功能码12-19（中位数、众数、百分位数、四分位数）是SUBTOTAL根本做不到的。对于财务分析来说，中位数经常比平均值更有参考价值——尤其是在报销、薪酬、采购单价等极端值较多的数据中。

三、Python 版：大数据量的 AGGREGATE 效果

现实中的财务数据，往往不止十几行，而是几万行、几十万行。Excel的AGGREGATE函数用在大数据量上，卡顿是迟早的事。这时候上Python：

import pandas as pdimport numpy as np# 读取数据df = pd.read_excel('销售数据.xlsx')# 模拟 AGGREGATE(9, 6) 的效果：求和，跳过错误值total = df['销售额'].sum()# 自动忽略NaN# 模拟 AGGREGATE(14, 3, ..., 2) 取第2大df_east = df[df['区域'] == '华东']second_highest = df_east['销售额'].nlargest(2).iloc[-1]# 模拟 AGGREGATE(12, 3) 中位数median_value = df_east['销售额'].median()

一行搞定AGGREGATE的效果：.sum() / .median() / .nlargest() 在pandas中默认忽略NaN，而且Python不卡，50万行数据照跑不误。

四、AGGREGATE vs SUBTOTAL 对比总结

一句话选哪个：只要忽略隐藏行就够了 → SUBTOTAL（更简单）；需要忽略错误值或需要中位数/LARGE/SMALL → AGGREGATE

下期预告：你知道Excel里还有一个叫SUBTOTAL和AGGREGATE的「近亲」吗？它叫SUMPRODUCT——一个函数顶十个，条件求和、加权平均、多条件计数一把抓。敬请期待。

附：长期坚持原创不易，如文章能够为大家带来少少帮助的，请大家点赞并转发，以支持我继续分享创作，你的支持将是我的不竭动力！谢谢！

（本文为本公众号原创，未经允许和授权，严禁转载，违者必究）