ARTICLE · 1379683
Excel函数江湖·求和篇【续篇】
Excel函数江湖·求和篇【续篇】在前些天,咱们把一众求和心法尽数盘点,SUM、SUMIF、SUMIFS、SUMPRODUCT轮番登场,不少同门反馈,数据杂乱、行列错位、有文本隔断的时候,普通求和招式容易走火,今天这一卷,就把冷门高阶求和秘传补齐,打通求和最后一关。
心法总纲:基础求和管整片,条件求和守规矩,下面这些,专门用来应对不规则数据、数组、筛选、可见单元格、交叉区域等疑难战局。
一、SUBTOTAL 【筛选求和专属心法】
江湖定位:只算可见单元格,筛选后自动忽略隐藏行,这是它独一份的本事,SUM做不到。
语法:
excel
=SUBTOTAL(9,求和区域)
招式注解:
第一参数 9 =求和; 109 =求和+手动隐藏行也不计入(9只忽略筛选隐藏,手动隐藏行依旧统计)。
⚠️ 禁忌:不支持多区域跨表复杂条件;嵌套SUBTOTAL会重复统计,极易算重。
实战场景:做明细表、台账,下拉筛选品类、月份,实时看汇总,报表同门最常用。
二、AGGREGATE 【SUBTOTAL的进化版神功】
江湖定位:SUBTOTAL升级版,容错更强,能跳过错误值,算是求和里的“抗毒心法”。
语法:
excel
=AGGREGATE(9,6,区域)
招式注解:
- 第1参数 9 代表求和
- 第2参数 6 = 忽略错误值+忽略隐藏行
对比SUBTOTAL:区域里面如果存在#N/A、#DIV/0!这类报错,SUBTOTAL直接崩盘报错,AGGREGATE可以直接跳过错误,照常算出总和。
短板:同样是区域运算,原生不支持多条件。
三、SUMX系列数组求和(SUMXMY2 / SUMX2MY2 / SUMX2PY2)
江湖定位:数组成对运算求和,统计、建模、方差计算才会用到,属于偏门绝学,日常报表极少出手。
1. SUMX2PY2:两组数组各自平方之后,相加,再全部求和(平方和之和)
=SUMX2PY2(数组1,数组2)
2. SUMX2MY2:两组数组平方相减之后求和
=SUMX2MY2(数组1,数组2)
3. SUMXMY2:两组数组对应值相减,差值平方后求和(残差平方和)
=SUMXMY2(数组1,数组2)
适用:做回归、偏差计算,普通业绩表基本不用。
四、BYROW + SUM / BYCOL + SUM 【365动态数组新派求和】
江湖定位:微软365专属,分行、分列批量求和,新一代内功,替代旧数组三键。
示例1:逐行求和,溢出返回每行合计
excel
=BYROW(A2:C10,LAMBDA(x,SUM(x)))
示例2:逐列求和
excel
=BYCOL(A2:C10,LAMBDA(x,SUM(x)))
优势:自动溢出、不用下拉填充,配合IF还能加条件,适合批量结构化报表。
五、TOCOL/TAKE + SUM 【不规则碎片求和】
江湖定位:365高阶连招,把零散、错位、夹杂空值文本的数据先整理成一列,再求和。
例:跳过空单元格,提取数字再汇总
excel
=SUM(TOCOL(A1:Z10,1))
TOCOL(区域,1) 代表剔除空白,只取出有效数字,适合数据排版很乱、单元格零散分布的场景。
六、DSUM 【数据库函数求和,老派单表条件求和】
江湖定位:数据库类型函数,自带条件区域,适合固定多条件看板,很多老手遗忘的古招。
语法:
excel
=DSUM(数据区域,要汇总的列标题,条件区域)
注意:条件区域必须带上表头,和原表标题一模一样。
优点:多条件写起来直观;缺点:条件区域要单独占一块单元格,不方便内嵌到一个单元格公式里,自动化联动弱,现在大多被SUMIFS替代。
七、SUMSQ 平方求和
江湖定位:单独把每个数字先平方,再相加。
excel
=SUMSQ(A2:A10)
单独求和用不到,搭配统计公式、标准差计算才会出场。
求和函数门派速选诀(总汇总,新旧招式合一)
- 普通整片数字求和:SUM
- 单条件求和:SUMIF
- 多条件求和:SUMIFS(日常主力)
- 筛选后只算可见行:SUBTOTAL(9/109)
- 区域有错误值,还要忽略隐藏行求和:AGGREGATE
- 多条件+数组灵活运算,兼容旧版:SUMPRODUCT
- 365动态数组,分行分列批量求和:BYROW/BYCOL+SUM
- 零散不规则数据提取数字求和:TOCOL+SUM
- 数据库式带表头条件:DSUM
- 平方求和、成对数组偏差求和:SUMSQ、SUMX系列
练功禁忌总提醒
1. SUM、SUMIFS默认自动忽略文本,但是遇到文本型数字会识别;
2. SUBTOTAL、AGGREGATE对【公式生成的隐藏】无效,只识别筛选/手动隐藏;
3. 365专属函数(BYROW、TOCOL)在2019及更低版本打开直接报错,发给同事前要留意版本;
4. SUMX、SUMSQ属于统计辅助求和,不要拿来做业绩汇总,大材小用。
心法总纲:基础求和管整片,条件求和守规矩,下面这些,专门用来应对不规则数据、数组、筛选、可见单元格、交叉区域等疑难战局。
一、SUBTOTAL 【筛选求和专属心法】
江湖定位:只算可见单元格,筛选后自动忽略隐藏行,这是它独一份的本事,SUM做不到。
语法:
excel
=SUBTOTAL(9,求和区域)
招式注解:
第一参数 9 =求和; 109 =求和+手动隐藏行也不计入(9只忽略筛选隐藏,手动隐藏行依旧统计)。
⚠️ 禁忌:不支持多区域跨表复杂条件;嵌套SUBTOTAL会重复统计,极易算重。
实战场景:做明细表、台账,下拉筛选品类、月份,实时看汇总,报表同门最常用。
二、AGGREGATE 【SUBTOTAL的进化版神功】
江湖定位:SUBTOTAL升级版,容错更强,能跳过错误值,算是求和里的“抗毒心法”。
语法:
excel
=AGGREGATE(9,6,区域)
招式注解:
- 第1参数 9 代表求和
- 第2参数 6 = 忽略错误值+忽略隐藏行
对比SUBTOTAL:区域里面如果存在#N/A、#DIV/0!这类报错,SUBTOTAL直接崩盘报错,AGGREGATE可以直接跳过错误,照常算出总和。
短板:同样是区域运算,原生不支持多条件。
三、SUMX系列数组求和(SUMXMY2 / SUMX2MY2 / SUMX2PY2)
江湖定位:数组成对运算求和,统计、建模、方差计算才会用到,属于偏门绝学,日常报表极少出手。
1. SUMX2PY2:两组数组各自平方之后,相加,再全部求和(平方和之和)
=SUMX2PY2(数组1,数组2)
2. SUMX2MY2:两组数组平方相减之后求和
=SUMX2MY2(数组1,数组2)
3. SUMXMY2:两组数组对应值相减,差值平方后求和(残差平方和)
=SUMXMY2(数组1,数组2)
适用:做回归、偏差计算,普通业绩表基本不用。
四、BYROW + SUM / BYCOL + SUM 【365动态数组新派求和】
江湖定位:微软365专属,分行、分列批量求和,新一代内功,替代旧数组三键。
示例1:逐行求和,溢出返回每行合计
excel
=BYROW(A2:C10,LAMBDA(x,SUM(x)))
示例2:逐列求和
excel
=BYCOL(A2:C10,LAMBDA(x,SUM(x)))
优势:自动溢出、不用下拉填充,配合IF还能加条件,适合批量结构化报表。
五、TOCOL/TAKE + SUM 【不规则碎片求和】
江湖定位:365高阶连招,把零散、错位、夹杂空值文本的数据先整理成一列,再求和。
例:跳过空单元格,提取数字再汇总
excel
=SUM(TOCOL(A1:Z10,1))
TOCOL(区域,1) 代表剔除空白,只取出有效数字,适合数据排版很乱、单元格零散分布的场景。
六、DSUM 【数据库函数求和,老派单表条件求和】
江湖定位:数据库类型函数,自带条件区域,适合固定多条件看板,很多老手遗忘的古招。
语法:
excel
=DSUM(数据区域,要汇总的列标题,条件区域)
注意:条件区域必须带上表头,和原表标题一模一样。
优点:多条件写起来直观;缺点:条件区域要单独占一块单元格,不方便内嵌到一个单元格公式里,自动化联动弱,现在大多被SUMIFS替代。
七、SUMSQ 平方求和
江湖定位:单独把每个数字先平方,再相加。
excel
=SUMSQ(A2:A10)
单独求和用不到,搭配统计公式、标准差计算才会出场。
求和函数门派速选诀(总汇总,新旧招式合一)
- 普通整片数字求和:SUM
- 单条件求和:SUMIF
- 多条件求和:SUMIFS(日常主力)
- 筛选后只算可见行:SUBTOTAL(9/109)
- 区域有错误值,还要忽略隐藏行求和:AGGREGATE
- 多条件+数组灵活运算,兼容旧版:SUMPRODUCT
- 365动态数组,分行分列批量求和:BYROW/BYCOL+SUM
- 零散不规则数据提取数字求和:TOCOL+SUM
- 数据库式带表头条件:DSUM
- 平方求和、成对数组偏差求和:SUMSQ、SUMX系列
练功禁忌总提醒
1. SUM、SUMIFS默认自动忽略文本,但是遇到文本型数字会识别;
2. SUBTOTAL、AGGREGATE对【公式生成的隐藏】无效,只识别筛选/手动隐藏;
3. 365专属函数(BYROW、TOCOL)在2019及更低版本打开直接报错,发给同事前要留意版本;
4. SUMX、SUMSQ属于统计辅助求和,不要拿来做业绩汇总,大材小用。















































































