SUMPRODUCT函数详解
发布时间:2022-09-21 22:28:22
下面将介绍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)
猜你喜欢
- 电喵直播快贝在哪里领取?电喵直播是一款非常不错的游戏直播软件,在电喵直播中观看直播是可以领取到一些快贝的,有些用户还不知道要在哪里领取,下面
- COLUMNS函数返回数组或引用的列数,ROWS函数返回引用或数组的行数。语法为:COLUMNS(array)ROWS(array)▲Arr
- 壁纸之类的问题早已引起了很多人的关注。好的壁纸能带给我们好的工作心情,下面为大家介绍下一些经典的壁纸设置技巧。喜欢的朋友可以收藏下,以备不时
- 相信很多朋友都已经用上Windows 7,在使用过程中是不是还有不太明白的地方呢?本站将采用寓教于乐的方式答疑解惑,通过精彩生动
- 爱剪辑是一款和pr、剪映等类似的非常实用的视频剪辑软件,同样可以对视频进行基础的后期处理。有时候我们拍摄的视频素材可能杂音有点大,会影响视频
- Win10开机如何跳过密码登录?有部分用户由于之前设置了开机密码,所以每次都需要在开机时输入密码登录,久而久之会觉得有点麻烦,那么我们要如何
- 很多时候,我们在长时间不使用电脑时,win10系统电脑就会自动待机,等待被唤醒。不过,最近有些win10用户反映自己的电脑在待机的时候会出现
- Excel中的单元格具体该如何设置只显示公式而不显示结果呢?具体的设置方法大家请跟着小编一起学习吧,欢迎大家来到学习excel函数设置只显示
- Excel电子表格控件与Idex()函数结合可以制作非常酷,非常炫的图表。那么excel怎么制作动态图表?下面小编就为大家详细介绍一下,一起
- 通过设置Word2007文档页码格式,用户可以选择多种类型的页码数字样式,从而使插入的页码更美观、更实用。并且页码格式还包括“起始页码”选项
- 在MAC上弹出光驱是一件很麻烦的事,如果经常使用光驱的朋友可以看看下面的方法,对光驱很依赖的用户们不妨一试经常在MAC上弹出光驱是一件很麻烦
- 在我们日常使用的win10系统计算机中,应该会有很多用户遇到过计算机启动快、变灰的情况。我们该怎么办?下面的小编将为您带来win10计算机快
- word文档如何去除页眉横线?页眉的横线是很多人心里的一个疙瘩,无论你怎么删,它还是在那里。今天给大家分享的是如何有效去除word文档的页眉
- excel是我们最常用的数据处理软件之一,几乎所有的电脑上面都会装有excel。在早期学习的时候我们都是通过手工计算,使用最小二乘法来计算x
- 不管是笔记本电脑还是台式电脑预装的都是Win8系统,如果再安装一个Win7系统就好了,可以一边使用win7也可以使用win8,下面将步骤与大
- win7以及win10都自带了桌面备忘录,也就是便签的功能,接下来我们一起看看电脑自带的桌面备忘录在哪吧!1、首先我们点击电脑桌面左下角的“
- 我们在word里进行文字输入时,总想把文字的字体设置成有个性的字体,其实在文档中的书法中就可以直接提取出来了。下面就跟小编一起看看吧。wor
- 很多人在使用U盘的时候,打开U盘的属性,却发现U盘的容量变成0MB了,而且U盘的格式也变成了RAM格式了,这是怎么回事呢?遇到这种问题要如何
- 我们在win7系统下运行软件的时候经常会遇到各种各样的错误提示,比如有win7 32位旗舰版系统用户反映说在运行软件的时候提示部件MSCOM
- emui11第二批更新名单有哪些?emui11系统出来就收到了很多用户的青睐,很多用户都想升级,但是不知道到自己的机型能不能升级,所以今天小