现在购买专栏,即可加入学习社群,四位导师进行辅导,还可领取100G职场进阶大礼包。


SUM函数可以说是Excel中最简单、也最常用的函数,没有之一,它的功能就是求和。任何人第一次接触函数,我相信都会用Excel进行求和,但是求和真的这么简单么?今天我来给大家讲解三个异形求和案例,高手都不一定知道。

1、合并单元格求和

案例描述:如图所示,我们要求不同人员的总成绩,在D列对不同人员进行了合并单元格(注也有未合并的单元格)。

我们知道,合并单元格之后,公式无法正常的向下复制填充,难道要一个一个去写公式进行求和么?当然不用,通过对公式的简单变形,即可完成合并单元格的批量求和。

Step1:选中D2:D11单元格区域(注意,选中之后不要再按鼠标);

Step2:在选中单元格的状态下,将鼠标定位于编辑栏,然后输入公式=SUM(C2:C$11)-SUM(D3:D$11),注意像图中所示使用相对引用也可以;

(这里的D3:D$11只能通过手动输入,无法在工作表中选取)

看到这里,你一定一脸懵:为什么要用两个SUM进行相减?为什么最后要按Ctrl Enter输入公式?为什么要减去SUM(D3:D$11),而不是SUM(D4:D$11)?

我来进行逐一解释:

①为什么最后要按Ctrl Enter输入公式?

合并单元格无法进行复制填充,只能通过Ctrl Enter批量输入,大家请记住这个组合键,用处非常大。

②为什么要减去SUM(D3:D$11),而不是SUM(D4:D$11)?

这里有一条重要的原理:

在一整列中,第一个单元格的值等于整列的值减去第一个值之外的所有值;

大家好好念三遍,体会一下这个原理,这个原理,不管是未合并的单元格,还是说合并的单元格都适用。

举例来说,如图下图所示,第一个数值5等于大括号①所框选的数值之和减去大括号②所框选的数值之和,向下移动以此类推。

excel竖列自动求和公式无法求和(Excel三个不规则求和公式)(1)

我们在求合并单元格之和时,用到的正是这个原理间接求出来合并单元格中的数值之和。也就是说在公式=SUM(C2:C$11)-SUM(D3:D$11)中,第一个SUM()求出来的是整列的数值之和,第二个SUM()求出来的是除了第一个单元格之外的所有单元格之和。

(所以第二个SUM所引用的单元格起始位置需要比第一个SUM的起始位置下移一位)

2、带单位求和

很多人都明白,在Excel中输入数据,不能将数值和文本写在同一个单元格中,否则会对数据分析带来很大麻烦。但在实际工作中,仍然会以一些小白将数字和文本输入在同一个单元格中,如下图所示,产品的金额和单位写在了同一个单元格中,如何求总和呢?

在B7单元格中输入公式:=SUMPRODUCT(1*SUBSTITUTE(A1:F6,"元",""))

这个公式的原理是将单元格中的“元”替换成空值之后,再对区域进行求和,两个难点函数SUMPRODUCT和SUBSTITUTE,关于这两个函数的详细用法,由于篇幅限制,这里不多展开,感兴趣的可以学习我的专栏。

excel竖列自动求和公式无法求和(Excel三个不规则求和公式)(2)

3、小计求和

这又是一个难者不会、会者不难的求和小技巧。如图所示,需要对多次考试的成绩进行小计求和,求和的位置是不规则的,无法通过复制填充批量输入公式。

Step1:定位空值

①按Ctrl G调出定位窗口,选择“定位条件”

excel竖列自动求和公式无法求和(Excel三个不规则求和公式)(3)

②在弹出的定位条件对话框中,选择“空值”,然后点击确定

excel竖列自动求和公式无法求和(Excel三个不规则求和公式)(4)

这样,光标就选中了数据区域中的所有空值。

Step2:按”Alt =“组合键进行自动求和

excel竖列自动求和公式无法求和(Excel三个不规则求和公式)(5)

能够掌握这三个求和公式,可以说,对Excel求和函数的理解将上一层境界。

excel竖列自动求和公式无法求和(Excel三个不规则求和公式)(6)

,