湖南龙网

  1. 首页 > 生活百科 > >

SUMPRODUCT函数详解( 二 )

小知识生活百科


示例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函数详解】=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)

推荐阅读


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

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