Excel - SUMPRODUCT函数应用
更新时间:2023-09-13 04:16:01 阅读量: 综合文库 文档下载
- excel推荐度:
- 相关推荐
Excel SUMPRODUCT函数应用
SUMPRODUCT是一个很特别的公式,表面的作用跟我们平常用的这函数的目的不大一样。特别是在2003版或以前的Excel中,还没有SUMIFS,COUNTIFS的功能(就是多条件的SUMIF和COUNTIF),就是在2007版以后,多了SUMIFS,COUNTIFS这两个函数,还是有学习这个函数的意义的。典型的脑子转个弯,就可以达到不同效果。
用途: 给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。比如说第一个数组是{1,2,3},第二个数组是{6,7,8},SUMPRODUCT会把两个数组相对应的值相乘,就是 1*6,2*7,3*8,然后把结果相加,就是 1*6 + 2*7 + 3*8。
语法: SUMPRODUCT(数组1,数组2,数组3,...)
数组1/数组2/数组3等 是需要进行相乘并求和的数组,一般是单元格范围的索引。
需要注意的是所有数组内的元素数目必须相同,如果是单元格范围,每个单元格范围的大小必须相同。
例子: 1 2 3 4 公式 A 1 2 3 4 B 5 6 7 8 结果 C 9 10 a b 解释 两个数组的所有元素对应相乘,然后把乘积相加,即 1*5 + 2*6 + 3*7 + 4*8。 非数值型会被视同为 0 处理,所以是 1*9 + 2*10 + 3*0 + 4*0。 三个数组的所有元素对应相乘,然后把乘积=SUMPRODUCT(A1:A4,B1:B4,C1:C4) 165 相加,即 1*5*9 + 2*6*10 + 3*7*0 + 4*8*0。(把非数值视同 0 ) D 2 4 5 7 =SUMPRODUCT(A1:A4,B1:B4) 70 =SUMPRODUCT(A1:A4,C1:C4) 29 多行/列数据也是可以用SUMPRODUCT的,等=SUMPRODUCT(A1:B4,B1:C4) 175 于1*5 + 2*6 + 3*7 + 4*8 + 5*9 + 6*10 + 7*0 + 8*0。 =SUMPRODUCT(A1:A4,B1:B3) #VALUE! 数组的个数不一样,返回错误值#VALUE!。 数组的个数一样,可是形状(行数和列数)不一样,同样返回错误值#VALUE!。 直接输入数组也可以使用这个函数。(不过一般这样用比较少) 这个就用的比较多了。如果一个数组是固定的值,可以考虑直接输在公式中。 把行变成列就不行了。这是因为形状不一样。在数组中,逗号是视同为新的一列,分号才是新的一行。 比如A1到C2单元格,=SUMPRODUCT({1,2,3,4},A1:A4) #VALUE! 如果用数组直接列出他们的值,就是{1,5,9;2,6,10}。一行一行的列出,每一行用分号区分,同一行的不同单元格就用逗号区分。 把上面公式中的逗号改成分号就得到想要的结果了。 如果想要把每个单元格乘上同一个常数,需=SUMPRODUCT(A1:A4,3) #VALUE! 要把常数先乘以数组,不然,由于常数不是数组,会返回错误#VALUE!。应该用 =SUMPRODUCT(A1:A4*3) =SUMPRODUCT(A1:A4,A1:D1) #VALUE! =SUMPRODUCT({1,2,3,4},{5,6,7,8}) 70 =SUMPRODUCT({1,2,3,4},A1:D1) 46 =SUMPRODUCT({1;2;3;4},A1:A4) 30
多条件加总和多条件计数
SUMPRODUCT是一个很特别的函数。它原本的用途是把两个或以上数组对应的数值相乘后把乘积相加。 可是更多时候,我们会把它用作多条件加总/多条件计数。就是在2007版中,Excel增加了SUMIFS(多条件加总)和 COUNTIFS(多条件计数)两个函数,用SUMPRODUCT 还是有SUMPRODUCT的优势。
首先,SUMPRODUCT在定义条件的时候,由于是直接用等式或其他比较公式作为条件, 比SUMIFS和COUNTIFS更灵活。
第二,学会用SUMPRODUCT去做多条件加总/多条件计数后, 会更容易理解SUMPRODUCT的其他用途,如\(条件排序), 或多条件VLOOKUP等。
另外,(也可能是习惯的问题)SUMPRODUCT直接用公式作为条件,看上去更直观。 我们先看看用SUMPRODUCT简单替代SUMIF/COUNTIF。
1 2 3 4 5 A a c A b a B 1 2 3 4 5 比如说,针对左边两列数值,我们需要加总所有第一列是 \或 \对应第二列的值(1+3+5)。 直接用SUMIF,公式就是: =SUMIF(A1:A5,\
A1:A5是条件范围,\是条件的要求数值,B1:B5是加总范围。 如果用SUMPRODUCT,公式就是:=SUMPRODUCT((A1:A5=\ 1 2 3 4 5 A a c A b a B 1 2 3 4 5 各元素公式 (A1=\(A2=\(A3=\(A4=\(A5=\结果 1 0 1 0 1 公式里的第一个数组(A1:A5=\反映了条件,我们把这数组中五个元素分别在左边表格的第三列中列出。
第一个元素是(A1=\, A1=\会返回 “TRUE” , SUMPRODUCT会把所有非数值的元素忽略,以我们要把 “TRUE” / “FALSE” 变成数值 1 或 0。
一般使用的方法是把条件乘以1,也可以在前面加两个负号。 比如(A1=\,(A2=\或
--(A1=\,--(A2=\
这样,第一个数组返回的,就是右边表格中第四列的值。
SUMPRODUCT((A1:A5=\实际上就是 “1*1 + 0*2 + 1*3 + 0*4 + 1*5” ,等于符合条件的的就加总,不符合条件的的就不加。
实际上SUMPRODUCT并不一定要求两个或以上的数组,一个数组也是可以的。所以有时候写公式的时候,会直接把要乘的都放第一个数组里: =SUMPRODUCT((A1:A5=\
我自己的习惯是把条件都放在第一个数组,把加总的放在第二个数组,方便自己纠错的时候分析。
要注意的是,条件前后必须用(),不然,由于乘号的优先顺序比较高, A1:A5=\就会被视同为A1:A5=(\,公式自然就报错了。
要做COUNTIF,跟上面SUMIF的方式很类似, 只需要把*B1:B5改成*1就可以了。如下:
=SUMPRODUCT((A1:A5=\
说SUMPRODUCT比SUMIFS和COUNTIFS更灵活, 是因为SUMPRODUCT的条件是用公式定义的。比如说在上面的例子中,如果只需要加小写的\,不加大写的\, 用SUMIF就只能加辅助列,计算那个单元格是小写的\;用SUMPRODUCT就可以一步到位了: =SUMPRODUCT(EXACT(A1:A5,\
理解了上面单个条件加总/计数,要设计多条件加总/多条件计数,实际上就是用: =SUMPRODUCT((条件一)*(条件二)*(条件三),加总范围)
1 2 3 4 5 A A B B C A B a c a b a C 1 2 3 4 5 比如要把A列=A,B列=a 对应的C列数字加总: =SUMPRODUCT((A1:A5=\
要计算A列=A,B列=a 的个数: =SUMPRODUCT((A1:A5=\
只要多于一个条件,就不用 *1 或者 -- 了。
条件排名和多条件排名
上周说过怎么用SUMPRODUCT做多条件加总和多条件计数,实际上SUMPRODUCT也能做条件排名/多条件排名。
我们先看看简单的排名(从大到小)。 1 2 3 4 5 A 4 2 4 1 5 B 2 4 2 5 1 B列公式 =RANK($A1,$A$1:$A$5) =RANK($A2,$A$1:$A$5) =RANK($A3,$A$1:$A$5) =RANK($A4,$A$1:$A$5) =RANK($A5,$A$1:$A$5) 左面的公式是直接用RANK函数得出的结果。 A列中,5是最大(第5行),所以排第1;1是最小(第4行),所以排最后(第5)。 RANK函数对相同数字会返回一样的排名,可是会影响后面的排名。 比如A列中有两个4(第1行和第3行),都排第2,可是由于有两个第2,就没有第3了。这跟我们一般理解的排名也是一样的。
用SUMPRODUCT做简单的排名,同样要考虑条件。从大到小排名,实际上就是算出有多少个在列表里面的数是大于自己的。如下面Excel表格里的公式: 1 2 3 4 5 A 4 2 4 1 5 B 2 4 2 5 1 B列公式 =SUMPRODUCT(($A$1:$A$5>$A1)*1)+1 =SUMPRODUCT(($A$1:$A$5>$A2)*1)+1 =SUMPRODUCT(($A$1:$A$5>$A3)*1)+1 =SUMPRODUCT(($A$1:$A$5>$A4)*1)+1 =SUMPRODUCT(($A$1:$A$5>$A5)*1)+1
- 冀教版版五年级科学下册复习资料
- 微生物学复习提纲
- 2013—2014学年小学第二学期教研组工作总结
- 国有土地转让委托服务合同协议范本模板
- 我的固废说明书
- 企业管理诊断报告格式
- 东鼎雅苑施工组织设计
- 谈谈如何做好基层党支部书记工作
- 浮梁县环保局市级文明单位创建工作汇报
- 管理学基础知识
- 大学物理实验报告23 - PN结温度传感器特性1
- 计算机网络实践
- 酒桌上这四种情况下要坐牢,千万别不当回事……
- 国家康居示范工程建设技术要点
- 中国贴布行业市场调查研究报告(目录) - 图文
- 新课标下如何在高中物理教学中培养学生的创新能力初探
- 营养师冬季养生食谱每日一练(7月4日)
- 关注江西2017年第3期药品质量公告
- 建设海绵城市专题习题汇总
- 10万吨年环保净水剂建设项目报告书(2).pdf - 图文
- SUMPRODUCT
- 函数
- 应用
- Excel
- 2018届译林版九年级上期末考试英语试卷含答案 - 图文
- 项目管理期末复习资料
- 人民警察使用警械和武器条例考试题库
- 中西方不同时期绘画差异比较
- 2015年医疗质量管理方案A
- 某装饰装修工程施工应急预案
- 最新年高考政治学业水平测试一轮复习 专题十六 认识社会与价值选择(含解析)(考试必备)
- 常州正衡中学历年小升初择校考真题题型精选
- 实验十 热工学实验
- 上机实验5:Word操作二(文档的排版技术)
- 大地税函(2010)10号文大连市地方税务局关于印发2010年度企业所得税汇算清缴若干规定的通知
- 同济大学启明星软件BSC - 4.0模块使用说明书
- 浙江省2012年信息技术高考试题完整版及标准答案 - 图文
- 五年级期中考试试卷下册
- 英语语言学常见名词解释
- 离线作业 - 人事选拔与测评作业集答案
- 直流电机的国家标准的说明
- 小学四年级民族团结教育教案主题 八节日万花筒
- 长沙理工大学交通土建毕业设计论文
- 战略合作协议