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},然后将数组求和得到正确的结果。

示例8统计给定区域中多于一个值的实例。例如,统计单元格区域A1:A10中有多少“Ford”和“Chrysler”。

解决方案:可以使用下面的公式:

=SUMPRODUCT((A1:A10=”Ford”)+(A1:A10=”Chrysler”))

或者:

=SUMPRODUCT(–(A1:A10={“Ford”,”Chrysler”}))

示例9单元格区域A1:A10中是货物编码,B1:B10中是相应的存放位置,C1:C10是相应的货物数量,需要统计在特定的位置包含部分货物编码的项数。SUMIF函数可以使用通配符,但仅能进行一次测试,而SUMPRODUCT函数不直接支持通配符。

解决方案:可以使用FIND函数测试是否在货物编码里内嵌有部分编码,ISNUMBER函数用来测试是否找到了匹配的编码(避免出现#VALUE错误)

=SUMPRODUCT(–(ISNUMBER(FIND(“ATN”,A1:A10))),–(B1:B10=”Birmingham”),–(C1:C10))

FIND函数区分大小写,如果不需要区分大小写,那么使用SEARCH函数:

=SUMPRODUCT(–(ISNUMBER(SEARCH(“ATN”,A1:A10))),–(B1:B10=”Birmingham”),–(C1:C10))

示例10统计两个日期之间某天的数量,排除其中的任何假期。

解决方案:SUMPRODUCT函数可以用来计算两个日期之间某天的数量。例如,假设在单元格A1和A2中分别存放着这两个日期,下面的公式返回这两个日期之间星期三的数量。

=SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(A1& “:” & A2)))=4))

这里利用了Excel以从1900年1月1日起的顺序号存储日期的原理,在INDIRECT函数中使用两个日期来“直接”在行中装载所有的日期,使用WEEKDAY函数和ROW函数来测试以确定这些日期中哪些是指定的日期。

NETWORKDAYS函数提供了一种在统计中排除假期的便利方法。此外,能够添加一个排除假期的测试来达到目的。假设假期存放在名称为holidays的命名单元格中,可以使用:

=SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(A1& “:” &A2)))=4),–(COUNTIF(holidays,ROW(INDIRECT($A$1 & “:”& A2)))=0))

我们也可以模拟NETWORKDAYS函数来统计两个日期之间的天数,排除星期六、星期日和假期,公式如下:

=SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(A1& “:” & A2)))<>1),(–(WEEKDAY(ROW(INDIRECT(A1& “:” &A2)))<>7)),–(COUNTIF(holidays,ROW(INDIRECT($A$1 & “:” & A2)))=0))

看起来似乎没有必要,因为我们可以更容易地使用NETWORKDAYS函数,但相比NETWORKDAYS函数来说,这里提供了一点小小的优势,不用管日期顺序。通过改变星期值,可以使用排除任何的1、2、3或其它天数,不只是星期六和星期天。

注:Excel 2010中已经引入了NETWORKDAYS.INTL函数解决了部分NETWORKDAYS函数的局限。

示例11仅对匹配某条件的可见单元格求和。例如,对单元格区域B1:B100中含有值“North”的相对应的A1:A100中的值求和,由于在数据中应用了筛选,一些行不可见。

解决方案:公式的第一部分是在单元格区域B1:B100中直接测试是否含有值“North”并求单元格区域A1:A100中的相应的值的和:

–($B$1:$B$100=“North”),$A$1:$A$100

可见单元格的统计比较复杂,使用SUBTOTAL函数以及联合使用ROW、INDEX和OFFSET函数:

–(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1:$A$100)-ROW(INDEX($A$1:$A$100,1,1))=1)

最终的公式如下:

=SUMPRODUCT(–(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1:$A$100)-ROW(INDEX($A$1:$A$100,1,1))=1), –($B$1:$B$100=“North”),$A$1:$A$100)

赞(3)
office办公分享 » SUMPRODUCT函数详解

本文链接:SUMPRODUCT函数详解https://www.officeapi.cn/98876.html

OFFICE,天天学一点,进步多一点

联系我们