湖南龙网

  1. 首页 > 生活百科 > >

SUMPRODUCT函数详解

小知识生活百科

下面将介绍SUMPRODUCT函数的实际应用示例 。
示例1：统计内容为日期的单元格区域A42:A407中早于今天的数量，单元格区域J42:J407是一组变量数组值 。
解决方案：使用($A$42:$A$407<TODAY())处理日期测试 。在单元格区域中已经设置了变量数组值，需要联合使用MATCH和ISNUMBER函数 。
=SUMPRODUCT((ISNUMBER(MATCH(J42:J407,Fred,0)))*($A$42:$A$407<TODAY()))
示例2：统计某给定日期后的数量 。
解决方案：如果测试日期在某个单元格中，那么公式为：
=SUMPRODUCT((C5:C309>$A$1)*(H5:H309=”A”))
下面的公式展示了一项在所有版本Excel中，在SUMPRODUCT函数中测试日期的技术，在公式中嵌入日期字符串：
=SUMPRODUCT((C5:C309>(–(“2004/05/31”)))*(H5:H309=”A”))
示例3：在公式中不输入多个条件判断，而是将它们输入到单元格中，只是在公式中引用这些单元格，可以吗？
解决方案：这似乎是一个简单的问题，使用下面的公式：
=SUMPRODUCT((B5:B63=L1:N1)*(C5:C63))
然而，公式运行失败，因为在列中而不是在行中输入的是条件判断，因此需要使用TRANSPOSE函数来满足SUMPRODUCT函数的要求：
=SUMPRODUCT((B5:B63=TRANSPOSE(P46:P48))*(C5:C63))
由于使用了TRANSPOSE函数，所以必须作为数组公式输入上面的公式 。
示例4：初始公式是：
=SUMIF(J2:J196,J209,L2:L196)
但还需要汇总下列单元格区域：R2:R196，U2:U196，V2:V196，Z2:Z196
解决方案：通过为每个单独的测试区域使用单独的SUMPRODUCT函数，很容易解决 。但下面的方法别出心裁，在公式中使用“+”运算符：
=SUMPRODUCT(–(J2:J196=J209),L2:L196+R2:R196+U2:U196+V2:V196+Z2:Z196)
示例5：在单元格区域A1:A100中查找值为“good”的字符串的次数，但一些单元格包含引线或尾部空格，或者HTML非中断空格 。
解决方案：基本的字符串统计是非常简单的，在公式中包含TRIM函数处理引线和尾部空格 。但TRIM函数不能处理HTML非中断空格，这必须使用SUBSTITUTE函数从被测试的单元格区域中提取：
=SUMPRODUCT(–(TRIM(SUBSTITUTE(A1:A100,CHAR(160),””))=”good”))
示例6：统计单元格区域中唯一值的数量 。
解决方案：如果整个单元格区域是A1:A20，那么公式为：
=SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20))
然而，如果单元格区域中任一单元格为空，那么会返回#DIV/0 。公式应修改为：
=SUMPRODUCT((A1:A20<>””)/COUNTIF(A1:A20,A1:A20& “”))
最后，为了克服一个Bug，即COUNTIF/SUMIF的第1个参数的隐含交叉区域使用该参数的父工作表的已用区域，也会返回#DIV/0，可以使用下面的公式：
=SUMPRODUCT((A1:A20<>””)/(COUNTIF(A1:A20,A1:A20)+(A1:A20=””)))
示例7：统计两个条件分别满足两个不同单元格区域的实例 。例如，单元格区域A1:A10中的内容是国家，B1:B10中是洲，C1:C10中是代表是否属于G7国家的标志 。现在需要统计在欧洲或者是G7国的数量 。
解决方案：要统计在欧洲或者是G7国的国家的数量，可以使用：
=SUMPRODUCT((B1:B10=”Europe”)+(C1:C10=”Y”))
问题是上面的公式将双倍统计处于欧洲并且也是G7国的国家，可以使用下面的公式解决：
=SUMPRODUCT((B1:B10=”Europe”)+(C1:C10=”Y”))-SUMPRODUCT(–(B1:B10=”Europe”),–(C1:C10=”Y”))
使用SUMPRODUCT函数计算在欧洲且是G7国的国家数，然后减去重复统计的国家数 。
当然，还有另一种方法，即：
=SUMPRODUCT(–((B1:B10=”Europe”)+(C1:C10=”Y”)>0))
这里，允许公式作双倍的统计，但添加了进一步的测试来检查每一个结果是否大于0 。因此，在里层的公式返回数据集{0;2;1;1;0;0;0;0;0;0}，测试该数据集中大于0的元素，得到数组{0;1;1;1;0;0;0;0;0;0}，然后将数组求和得到正确的结果 。

推荐阅读


上一篇：什么情况下使用INDEX函数?

下一篇：IE浏览器开发人员工具怎么使用？