Excel 中 SUMPRODUCT 函数的使用技巧

原文标题:《学会这个函数,一个顶四个!》

Excel 里难度最大的,应该就是函数公式了。

举个例子:

上面这个公式,SUM 函数很简单吧,相信大家都知道是求和。

结果口算都能算出来,是 10。

这个函数又是什么意思呢?

部分同学这会儿就答不上来了。

其实非常简单,SUM 是相加,PRODUCT 是相乘,

所以计算结果就是把所有的数字相乘:24。

你肯定会表示不服:我就是不知道而已,知道的话肯定立马就答出来了。

没关系,再增加一点难度:

现在你已经学会了 SUMPRODUCT 函数,那把这两个函数结合到一起,计算结果应该是什么?

答:结果是 10。

为什么呢?

这节课,我们就一起来学一学 SUMPRODUCT 这个简单又复杂的函数!

基本用法

SUMPRODUCT 的作用,就是把两列数据相乘,之后再相加。

比如上面的计算结果,就是两列每个单元格相乘,然后相加,结果是 40。

所以这个效果通常会用来计算两列需要相乘的相加的场景,

比如说销量和产品单价:

计算总的销售额,一个 SUMPRODUCT 公式搞定:

=SUMPRODUCT(E2:E10,F2:F10)

有高手领路,是不是超级简单!

SUMPRODUCT 的参数如下:

- 参数 1,要相乘的第 1 列数据;

- 参数 2,要相乘的第 2 列数据;

- 参数 3,依次类推,最多可以选择 255 列数据。

福利小技巧

再送两个福利小技巧:

参数相乘效果一样

下面两个公式的结果是一样的,本质上都是两列数据相乘

当出现错误值的时候,继续在「研发部」里看看能不能找到 ——

=SUMPRODUCT(E2:E10,F2:F10)=SUMPRODUCT(E2:E10*F2:F10)

原理和下面 SUM 函数内相加是一样的。

=SUM(1,2,3)=SUM(1+2+3)

单列数据即相加

如果只有 1 列参数,那么效果和用 SUM 函数是一样的。

所以,第 1 个案例为什么是 10,你应该明白了吧,因为只有一列数据。

乘以数字

再来一个案例。

现在老板觉得产品单价太低了,每个产品单价都要增加 5 块钱,那么总销售额应该怎么算?

不用每个单价都加上 5,直接一个 SUMPRODUCT 公式搞定。

=SUMPRODUCT(E2:E10,F2:F10+5)

看见了吗?很简单,直接在求和的列当中加上 5 就可以了。

这个计算过程,实际上是对这个区域每个单元格逐一加了 5:

所以,加一个 5,和加一列 5 本质是一样的。

涨 5 块觉得还是不够高,直接价格翻倍乘以 2,应该怎么算?

公式是这样的:

=SUMPRODUCT(E2:E10,F2:F10*2)

他的计算原理和加号是一样的,如下,

条件计数

毕竟是 SUM 和 PRODUCT 两个函数的合体,SUMPRODUCT 用法还不止这些。

看下面这一个问题,想要统计每个部门的人数,公式是这样的:

=SUMPRODUCT((B2:B10=G2)*1)

思路解析:

B2:B10=G2,其实就是看看 B2、B3、……、B10 单元格,是不是和 G2,也就是「销售一部」长的一样。

如果一样,则为 true,也就是 1,那么得出的结果为 1;

如果不一样,则为 false,也就是 0,那么得出的结果为 0。

最后加起来就是符合条件的数量啦。

如果有多个条件,就再乘以这个条件判断的逻辑值。比如要判断每个部门女生数量:

公式如下:

=SUMPRODUCT((B2:B10=G2)*(D2:D10="女"))

再比如,要统计 2010 年以前入职的每个部门女生数量,再乘以一个条件值:

公式是这样的:

=SUMPRODUCT(B2:B10=G2)*D2:D10="女")*YEAR(E2:E102010)

用法你总结出来了吗?

一句话,有多少个条件就乘以多少次:

条件求和

就这样而已吗?SUMPRODUCT 肯定不服。

现在我们要统计每个部门的销售量,

对应的 SUMPRODUCT 公式是这个样子:

=SUMPRODUCT($E$2:$E$10*$F$2:$F$10*($B$2:$B$10=H2))

计算过程是这样的:

如果有多个条件,比如计算每个部门「产品 C」的销售额:

公式如下:

=SUMPRODUCT(($E$2:$E$10)*($F$2:$F$10)*($B$2:$B$10=H2)*(D2:D10="产品 C"))

和前面的计数原理一样,把数据列和条件列乘起来就可以了。

写在最后

到这里你可能会有点疑惑:

这些计算,用 SUMIFS 和 COUNTIFS 都可以实现啊,为什么还要用 SUMPRODUCT 啊?

原因不是明摆着的吗?一个函数顶四个!

要知道,条件数量不一样,你可能要在 SUMIF、SUMIFS、COUNTIF 和 COUNTIFS 之间来回切换,

而 SUMPRODUCT 只用一个函数就可以了。

另外 SUMPRODUCT 用逻辑运算符直接比较判断,公式逻辑更加的清晰。

更重要的,它还可以计算更复杂的条件判断。

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:拉登 Dony,编辑:竺兰

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

文章价值:
人打分
有价值还可以无价值
置顶评论
    热门评论
      文章发布时间太久,仅显示热门评论
      全部评论
      一大波评论正在路上
        取消发送
        软媒旗下人气应用

        如点击保存海报无效,请长按图片进行保存分享