Excel中特别有用的不常用函数之Sumproduct函数
今天介绍SUMPRODUCT函数
如果要在Excel的所有不常用函数中,评选一个最有用的函数,我觉得非SUMPRODUCT莫属。这个函数不是必须的,使用它的场景也可以使用其他函数来解决,不过SUMPRODUCT可以极大的简化公式。
很多人知道这个函数,但是并不一定会在工作中使用它们。因为并不知道哪些场景可以使用这个函数来简化问题的解决。
01
这个函数的作用非常简单:
这是语法
它可以有很多个参数,每个参数代表一个区域或者数组。它的目的就是计算这多个区域或数组的对应乘积的和。
比如:
这个公式还有另外一个写法,那就是将参数之间的逗号换成乘号:
=SUMPRODUCT(C3:C7*D3:D7)
结果是一样的。
02
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中的一个固定用法,一旦你遇到这样的场景,这就是一个标准的解决方案。写在这里太长了,我写在另外一篇文章中。大家可以点击下面的了解阅读学习: