Excel中特别有用的不常用函数之Sumproduct函数

今天介绍SUMPRODUCT函数

如果要在Excel的所有不常用函数中,评选一个最有用的函数,我觉得非SUMPRODUCT莫属。这个函数不是必须的,使用它的场景也可以使用其他函数来解决,不过SUMPRODUCT可以极大的简化公式。

很多人知道这个函数,但是并不一定会在工作中使用它们。因为并不知道哪些场景可以使用这个函数来简化问题的解决。

01

SUMPRODUCT的语法

这个函数的作用非常简单:

这是说明

这是语法

它可以有很多个参数,每个参数代表一个区域或者数组。它的目的就是计算这多个区域或数组的对应乘积的和。

比如:

这个公式直接就可以根据数量列和单价列计算出对应的乘积的和,即合计销售额

这个公式还有另外一个写法,那就是将参数之间的逗号换成乘号:

=SUMPRODUCT(C3:C7*D3:D7)

结果是一样的。

02

SUMPRODUCT举例

SUMPRODUCT能够做的当然比上面讲的基本用法多得多。我们下面通过一些例子为大家进行讲解。

例1 代替数组公式

有时候,我们有些情况下需要通过数组公式来计算结果:

这里,我们希望计算所有那些数量超过5的产品的销售额的合计。使用SUM和IF结合的数组公式可以完成。但是使用数组公式有一个小问题,就是需要用CTRL+SHIFT+ENTER三键一起输入。如果忘了,或者修改公式的人不知道,就会得到错误的结果。

我们可以使用SUMPRODUCT来代替:

这个公式:=SUMPRODUCT(C3:C7,D3:D7,N(C3:C7>5))

使用了3个参数,前面两个跟一开始的那个例子一样,分别是C3:C7,D3:D7,代表了销量和单价。第三个参数:N(C3:C7>5)是一个函数,先看里面C3:C7>5,分别对每个数量与5进行比较,结果就是{FALSE, TRUE, TRUE,FALSE,TRUE},N是一个函数,用来将它的参数转换为数值,所有的TRUE转换为1,FALSE转换为0。于是,N(C3:C7>5)的结果就是{0,1,1,0,1},

整个公式就变成了:

=SUMPRORUCT(C3:C7,D3:D7,{0,1,1,0,1}

将这三个参数的每个元素对应相乘,然后求和。就得到了最终结果。

例2 多条件求和

在前面的例子中,现在我们要查找产品为A,数量为3的那条记录对应的单价是多少。

这就是两个条件的查找。如果用vlookup函数,就需要添加辅助列,比较啰嗦。使用SUMPRODUCT可以很好的解决这个问题:

需要提醒的是,如果把中间的乘号换成逗号,采用SUMPRODUCT的另外一个写法,就必须结合N函数来使用:

=SUMPRODUCT(N(B3:B7="A"),N(C3:C7=3),D3:D7)

如果不想返回单价,只想找到符合条件的行号,可以使用公式:

=SUMPRODUCT(N(B3:B7="A"),N(C3:C7=3),ROW(D3:D7)-2)

把最后一个用法推而广之,就是例3

例3 筛选满足多个条件的记录

这个例子是Excel中的一个固定用法,一旦你遇到这样的场景,这就是一个标准的解决方案。写在这里太长了,我写在另外一篇文章中。大家可以点击下面的了解阅读学习:

【Excel实用技巧】如何筛选满足多个条件的记录

END
(0)

相关推荐