1分钟搞定数据汇总！这3个Excel求和方法不学错亿（建议收藏）

公众号新闻

2023-05-25 00:05

关注【秋叶 Excel】👆发送【插件】

免费领 Excel 必备 4 大插件

作者：明镜在心

编辑：竺兰、萌萌

Excel 中最常用的汇总统计方式之一，就是求和。

每个公司对于求和的要求也不一样。有的是对数据区域中的行进行求和，有的对列数据进行求和。还有的是对于行列交叉位置的数据进行求和的，还有的是错位求和。

数据结构也是千差万别。

这不，有位仁兄发来问题求助，如下图：

要求是：将其中的手续费按照项目类别分别统计求和。如下图：

比如，常规培训手续费，需要将【B】列常规培训单元格下面的手续费都统计在内。即将【D3】和【D5】的单元格求和。

场地租赁手续费同理。

问题分析

乍看到这个问题时，我有点懵！

一般情况下我们可能会像下面这样记录数据。如下图：

在手续费之前加上归属的项目名称。

如果这样记录数据的话，就跟我们平时用求和函数的套路完全一样了。

用到的公式如下：

=SUMIF(B:B,F2,D:D)

公式解析：

在【B】列中，查找【F2】单元格的内容，对符合条件的，在【D】列对应单元格中的金额求和。

但是，现在的问题是他们并不是像上面图中那样记录数据。

如果直接用 SUMIFS 双条件求和，结果会是下面这样。

结果是 0！

公式如下：

=SUMIFS(D:D,B:B,LEFT(F2,4),B:B,"手续费")

问题出在哪里呢？又到底该如何才能求和呢？

解决问题

▋方法一：SUMIFS 多条件求和

根据上面的问题分析来看，原始表格记录成这样，的确是一个双条件求和的问题。

就是这里的双条件是一个错行求和。

我们将上面的公式改成如下这样：

公式如下：

=SUMIFS($D$3:$D$8,$B$2:$B$7,LEFT(F2,4),$B$3:$B$8,"手续费")

公式解析：

SUMIFS 多条件求和这里有两个条件，

第一个条件对是：「 $B$2:$B$7,LEFT(F2,4) 」。

在单元格【$B$2:$B$7】中，查找【F2】单元格中的前四个字符的内容。

即：常规培训。（这里用 LEFT 函数截取【F2】单元格左边四个字符）

第二个条件对是：「 $B$3:$B$8,"手续费" 」。

在单元格【$B$3:$B$8】中，查找手续费的内容。

如果同时符合条件的话，在【$D$3:$D$8】单元格区域中对应的行金额进行求和。

这里的第二个条件对和【$D$3:$D$8】这个求和区域正好与第一个条件对错了一行。

▋方法二：SUM 数组公式

这里我们可以将两个条件利用文本连接符错位连接的方法变成一个条件，然后再与求和区域相乘的方法来解决。

公式如下：

=SUM(($B$2:$B$7&$B$3:$B$8=F2)*$D$3:$D$8)

公式解析：

这里的思路跟上面的 SUMIFS 多条件求和的思路是一样的。

为了能让大家看明白，我们这里加两个辅助列演示下，如下图：

图中我们将【B3：B7】单元格复制到【C2：C6】，

将【E3：E7】单元格复制到【F2：F6】。

这样排列的话，跟常规的求和套路完全一样了。

$B$2:$B$7&$B$3:$B$8

结果是：

{"常规培训手续费";"手续费常规培训";"常规培训手续费";"手续费场地租赁";"场地租赁手续费";"手续费"}

与辅助列图中的【B2：B7】与【C2：C7】连接的结果完全一样。

再与【F2】单元格中的内容（常规培训手续费）进行比较，如果相等就返回 TRUE，否则返回 FALSE。

{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}

最后与$D$3:$D$8 单元格中的金额相乘。

TRUE 相当于 1，FALSE 相当于 0。

结果如下：

{-10;0;-10;0;0;0}

最后用 SUM 求出结果。即：-20。

知识扩展

这里我们再分享一种解题思路，就是透视表法。

从上面的原始记录来看，是属于一维数据表格，只要稍加变化就可以使用强大的透视表来处理了。

如下图：

在【E】列增加一个辅助列，并在【E2】单元格输入如下公式：

=IF(B2="手续费",E1,B2)

公式解析：

如果【B2】单元格等于手续费，就返回【E1】单元格的内容，否则返回【B2】单元格本身。

公式的意思很简单。主要目的就是让手续费归属于它上面一个单元格的项目内容。

然后，我们选中这个区域作为数据源，然后依次点击【插入】-【数据透视表】-调出透视表对话框，直接点【确定】。

在新生成的工作表中，将【辅助列】和【项目】拖到行区域，将【金额】拖到值区域。如下图：

这样大体上的统计就完成了。

进行如下设置，点击【设计】-【报表布局】-【以表格形式显示】。

最后筛选项目中的手续费即可。

今天，我们分享了一个很特别的求和方法：错行求和。

❶ 利用 SUMIFS 多条件错行求和。

❷ 利用 SUM 数组公式错行求和。

❸ 利用辅助列+透视表的方法进行错行求和。

当然在实际工作中，还会遇到错列求和、隔行求和、隔列求和等等。

大家可以参考今天的文章思路来解决。

好了，如果大家还有哪些搞不定的 Excel 问题，可以报名参加我们秋叶《3 天 Excel 集训营》。

这里不仅有老师、助教为你1V1 群内答疑解惑！还能学习更多有用的 Excel 小技巧！

仅需 1 元！现在就扫码购课加班班微信，助你成为效率达人！👇👇👇

*广告

也可以给我们留言噢！

如果喜欢我的文章，欢迎点赞&转发！

❤

关注公众号【秋叶 Excel】

回复关键词【礼包】

还能免费领办公神器

Office 模板和免商字体！

↓↓↓

微信扫码关注该文公众号作者

戳这里提交新闻线索和高质量文章给我们。

来源: qq

点击查看作者最近其他文章