ARTICLE · 1109609
Excel数学和三角|AGGREGATE函数详解,忽略错误值、隐藏行的万能统计函数
Excel数学和三角|AGGREGATE函数详解,忽略错误值、隐藏行的万能统计函数大家平时做Excel统计的时候，是不是经常遇到这些麻烦： 表格里面有#N/A、#VALUE!这类错误值，SUM、AVERAGE一计算直接报错； 有些行手动隐藏了，用普通求和函数，隐藏行数据还会被算进去，不符合统计需求。 今天给大家介绍AGGREGATE函数，它是数学与三角函数里的“万能统计工具”，可以选择忽略隐藏行、忽略错误值，求和、计数、求平均、找最大最小值，一个函数全都搞定。 一、函数基础语法 excel AGGREGATE(功能编号,忽略选项,引用区域,[k值]) - 功能编号：选择你想要做什么运算（求和、平均值、最大值等） - 忽略选项：设置要不要忽略隐藏行、错误值 - 引用区域：要统计的数据范围 - [k值]：部分功能需要，比如第N大、第N小，可选参数 ✅ 【功能编号对照表】 1：平均值 2：计数（统计非空单元格数量） 3：统计非空文本+数字单元格数量（COUNTA效果） 4：最大值 5：最小值 6：乘积 7：样本标准差 8：总体标准差 9：求和（SUM） 10：样本方差 11：总体方差 12：中位数 13：众数 14：第k大值（LARGE） 15：第k小值（SMALL） 16：百分位数 17：四分位数 ✅ 【忽略选项（第二参数）】 0 或省略：忽略隐藏行，不忽略错误值 1：忽略隐藏行和错误值（最常用！） 2：忽略错误值，不忽略隐藏行 3：忽略隐藏行、错误值、子总计 重点：选项1是日常高频用法，同时跳过隐藏行+错误值 二、基础实操案例 案例1：带错误值区域，忽略错误直接求和 A2:A10单元格内有数字，其中部分单元格是#N/A错误，直接SUM会报错。 公式： excel =AGGREGATE(9,1,A2:A10) 解析： 9=求和；1=忽略隐藏行+错误值；A2:A10统计区域 👉 效果：自动跳过里面的错误单元格，只对有效数字求和。 案例2：求区域平均值，屏蔽错误值 excel =AGGREGATE(1,1,A2:A10) 1=求平均值，1=忽略隐藏行、错误值。哪怕区域有报错，依旧正常算出平均。 案例3：忽略隐藏行，求最大值 excel =AGGREGATE(4,1,A2:A10) 当手动隐藏几行数据，MAX函数依旧会统计隐藏内容；AGGREGATE可以不统计隐藏行。 案例4：提取区域第2大的值（k参数用上） excel =AGGREGATE(14,1,A2:A10,2) 14=第k大；1=忽略错误/隐藏行；A2:A10数据源；2代表取第二名的数值。 三、AGGREGATE 和 SUBTOTAL 的区别（重点） 很多人会把这两个函数弄混，这里一次性分清： 1. SUBTOTAL：可以忽略隐藏行，但是遇到错误值，直接计算报错 2. AGGREGATE：既能忽略隐藏行，还能跳过单元格错误值，这是它最大优势 简单一句话：数据里有错误值，优先用AGGREGATE。 四、常见报错&避坑指南 1. #VALUE!报错 - 原因：功能编号、忽略选项输入不是规定数字；用第k大/小时，k填了0或者大于数据总数。 ✅解决：核对参数，k必须是≥1的正整数。 2. 筛选后统计失效？ AGGREGATE本身支持筛选，第二参数选1，筛选隐藏的行会自动排除。 3. 不支持整列大范围引用（A:A） 尽量用实际数据区域A2:A10，整列引用会拖慢表格运算速度。 五、实用小总结 ✅ AGGREGATE核心亮点： ① 一个函数，搞定求和、平均、最值、排序取值等十几种统计； ② 支持同时忽略隐藏行+错误值，普通SUM/MAX/AVERAGE做不到； ③ 处理带报错数据的表格、筛选后统计，是首选函数。 ❌ 局限： 不支持跨多表三维引用，适合当前工作表内连续区域统计。 小练习：你可以在表格里输入一组带#N/A的数据，试试 =AGGREGATE(9,1,区域) 感受效果。 本专栏会持续更新Excel实操干货 建议大家收藏推文，闲暇照着实操练习。 关注本公众号，不错过后续连载内容。