在表哥表姐的心中流传着Excel函数四大王者的传奇,它们是if、sumproduct、text、vlookup。Sumproduct函数应用十分广泛,功能很强大,既能求和也能计数。如果说求和函数只学一种的话,那么这个函数必须是Sumproduct。让我们一起走进sumproduct函数殿堂,领略sumproduct函数的独特魅力。
sumproduct函数功能是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。语法:SUMPRODUCT(array1,array2,array3, ...)Array1, array2, array3, ... 为 2 到 255 个数组,其相应元素需要进行相乘并求和。数组参数必须具有相同的维数,否则,函数 SUMPRODUCT 将返回错误值 #VALUE!。
特别提醒:Sumproduct函数的计算区域不采用一整列计算,一般是采用单元格区域,比如A1:A100,而不采用A:A。
函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。
SUMPRODUCT有两种最基本的用法
用法一:sumproduct(条件1*条件2*...,求和区域),该公式会自动对文本进行排除,只统计数值的求和。
用法二:sumproduct(条件1*条件2*...*求和区域),该公式不会对文本进行排除,一旦求和区域中有文本,就会出现错误值#VALUE。如果求和区域均为数值格式,那么两个用法得出的结果一致,推荐使用第一种用法。
上图为某公司保费表,请问曹操保费多少?
公式:H2=SUMPRODUCT((D2:D15=G2),E2:E15)
公式解读:D2:D15=G2来判断D列中姓名是曹操的区域,E2:E15是保费列,也就是求和列。特别注意D2:D15和E2:E15维数是一致的,都是从2开始到15结束,因此公式运行正确。如果维数不一致,该公式将发生错误。
sumproduct函数单条件查找语法:sumproduct(条件1,求和区域)
以前我们讲过sumif函数,本题如果用sumif来解决。公式如下:=SUMIF(D:D,G2,E:E)。既然sumif也能做到单条件求和,那么SUMPRODUCT函数的优越性在哪里呢?
上图为某公司保费表,请问鲁国曹操保费多少?
公式:I2=SUMPRODUCT((C2:C15=H2)*(D2:D15=G2),E2:E15)
思路:本题属于多条件求和,sumif是只能单条件求和,无法进行多条件求和。如果用sumproduct函数将轻而易举解决。
sumproduct函数多条件查找语法:sumproduct(条件1*条件2*...,求和区域)
上图为某公司保费表,请问曹操做了几件?
公式:H2=SUMPRODUCT((D2:D15=G2)*1)
思路:D列中名字出现几次,就说明该名字做了几件保险。因此我们只要统计曹操出现的次数即可。sumproduct函数是一个多面手,不但能求和,还能统计个数。
SUMPRODUCT函数单条件计数语法:SUMPRODUCT(条件*1)或者SUMPRODUCT(N(条件))
Excel中N函数是Excel中的信息函数,作用是将非数值形式的值转化为数字,日期转换成序列值,true转换为1,其他转化为0.
上图为某公司保费表,请问鲁国曹操件数是多少?
公式:=SUMPRODUCT((C2:C15=H2)*(D2:D15=G2))
思路:多条件计数是单条件的升级,只要SUMPRODUCT函数只要再加上一个条件,就能轻松解决多条件计数。
sumproduct函数多条件计数语法:SUMPRODUCT(条件1*条件2*...)
重要的事说三遍:函数 SUMPRODUCT 的数组参数必须具有相同的维数,(比如从2到n,那么所有条件都必须从2到n,否则将会出错。),否则将返回错误值 #VALUE!。
本教程源数据表格百度网址:http://pan.baidu.com/s/1hsOpMuw
sumproduct函数今日只是小试牛刀,还没发挥出其真正强大的威力。欲知sumproduct函数终极威力,且听下回分解。
联系客服