湖南龙网

  1. 首页 > 生活百科 > >

4 SUMPRODUCT函数详解( 二 )

小知识生活百科


=SUMPRODUCT((A1:A10=”Ford”)*(B1:B10=”June”)*(C1:C10))
我们统计单元格区域A1:A10中的内容为“Ford”并且单元格区域B1:B10中的内容为June的项目数，以及统计单元格区域A1:A10中的内容为“Ford”并且单元格区域B1:B10中的内容为June并将单元格区域C1:C10相应单元格求和 。在Excel 2007及以上版本中，可以使用COUNTIFS函数和SUMIFS函数代替SUMPRODUCT函数，相应的公式为：
=COUNTIFS(A1:A10,”Ford”,B1:B10,”June”)
=SUMIFS(C1:C10,A1:A10,”Ford”,B1:B10,”June”)
在Excel 2007中，SUMPRODUCT函数的改进是可以取整列的地址 。在Excel开发者工具库中，SUMPRODUCT函数仍然保留着其独特的位置，因为COUNTIFS函数和SUMIFS函数仍然不能够计算已关闭工作簿中的值 。
性能分析
双目运算符（–）与*运算符
在大多数情形下，可以使用SUMPRODUCT函数的“*”或”–“版本，并且都能得到正确的功能 。然而，也有一些例外 。考虑在单元格区域A1:B10中是一个包含姓名和数量的表，其中第一行是文本标题“Name”和“Amount” 。公式：
=SUMPRODUCT(–(A1:A10=”Bob”),–(B1:B10)>0),B1:B10)
将正确地计算列A中姓名是“Bob”且列B中为正值的和 。然而，公式：
=SUMPRODUCT((A1:A10=”Bob”)*(B1:B10>0)*(B1:B10))
将返回#VALUE!错误 。错误的原因是由于B1中是文本，乘以文本值导致错误 。为了解决错误，单元格区域不能含标题单元格，应以单元格A2和B2开始 。
类似地，如果公式中的一个或多个单元格区域包含多列，则必须使用“*”运算符，而下面的公式将不能运行：
=SUMPRODUCT(–(A1:A10=”Bob”),–(B1:C10>0),–(B1:C10))
下面的公式工作得很好：
=SUMPRODUCT((A1:A10=”Bob”)*(B1:C10>0)*(B1:C10))
事实上下面的公式也可以：
=SUMPRODUCT((A1:A10=”Bob”)*(B1:C10>0),B1:C10)
使用转置
如果在SUMPRODUCT函数中使用TRANSPOSE函数，那么必须使用“*”运算符 。
公式效率
很多人都知道使用数组公式要付出高的代价，如果过多地使用将明显减弱工作表/工作簿的重新计算速度 。
虽然SUMPRODUCT函数不是数组公式，然而它也面临同样的问题 。虽然SUMPRODUCT函数通常比等价的数组公式更快，但与数组公式一样，SUMPRODUCT函数比COUNTIF函数和SUMIF函数更慢，因此如果合适的话使用这些函数会更好 。
因此，在下面的情形下，不要使用SUMPRODUCT函数：
=SUMPRODUCT((A1:A10=”Ford”)*(C1:C10))
而是使用等价的SUMIF函数：
=SUMIF(A1:A10,”Ford”,C1:C10)
甚至两个COUNTIF函数或SUMIF函数都比一个SUMPRODUCT函数更快，因此下面的公式：
=COUNTIF(A1:A10,>=10)-COUNTIF(A1:A10,>20)
比下面的公式更有效率：
=SUMPRODUCT((A1:A10>=10)*(A1:A10<=20))
【4 SUMPRODUCT函数详解】大致提高20% 。

推荐阅读


上一篇：IE浏览器怎么在桌面隐藏图标？

下一篇：3 SUMPRODUCT函数详解