夜雨聆风学习资料网

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属于统计辅助求和,不要拿来做业绩汇总,大材小用。

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料