院长大大丨图文
教程基于Excel 2016
你用Excel那么久,做了不少报表,应该做过条件求和吧?也经常使用条件统计?
那你一定很懂SUM、COUNT函数了,即使加上IF,加上IFS,都应该个个精通。
但你是否知道,有1个函数,可以代替上述的6个?对,就是SUMPRODUCT函数,你一定见过,但未必会用。
今天,院长带你一齐看看SUMPRODUCT函数应该怎么用,条件求和、计数、加权汇总,样样精通。
函数特点
SUMPRODUCT:返回相应的数组或区域乘积的和,SUM代表求和,PRODUCT代表乘积,先相乘,后求和。
语法:
SUMPRODUCT(array1,array2,array3, ...)
Array1,array2,array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。
一般用法:快速求和
计算销售总金额,通过每个销售单中的单价乘以数量得到金额,然后相加求和。
一般写法【=SUMPRODUCT(E2:E6,F2:F6)】,条件之间用逗号隔开。
常用写法【=SUMPRODUCT(E2:E6*F2:F6)】,条件之间用乘号隔开。
由于计算的元素都为数值,所以两种写法都可以计算出正确的结果。
数组参数必须具有相同的维数
举个栗子,计算键盘销售金额,单价选择【E2:E6】,数量选择【F2:F3】,两列维数不同,返回错误值。
正确写法:把单价列区域调整为【E2:E3】即可。
非数值型的数组元素作为0处理
其中,第三个销售单数量待定,使用【=SUMPRODUCT(E2:E6,F2:F6)】,会把文本当0处理,不影响其他元素的计算。
使用【=SUMPRODUCT(E2:E6*F2:F6)】,数组元素含有文本,相乘导致第三个值为#VALUE!,从而导致整个函数返回错误值!
强大用法
除了快速求和以外,SUMPRODUCT还有更强大的用法,它能计数、能求权、还能条件求和、隔列求和。
条件计数
统计1号店键盘销售单数,使用SUMPRODUCT函数,选择门店和产品名称两个条件,条件分别满足【1店】和【键盘】,公式【=SUMPRODUCT((B2:B16=B2)*(D2:D16=D2))】,计算出单数为2。
函数解释:
B2:B16=B2,计算区域中等于B2的值,形成1和0的数组,D2:D16=D2同理;
两个数组相乘,得到新的1和0数组,相加计算结果为2。
联动条件计数
统计产品成本大于销售单价的单数,需计算成本列大于单价列,公式【=SUMPRODUCT((E2:E16>F2:F16)*1)】,计算出单数为4。
加权汇总
根据评比项权重与所得分值,计算出总分,公式【=SUMPRODUCT($B$2:$E$2,B3:E3)】,其中,权重引用范围不变,使用绝对引用,向下复制公式,得到员工加权后的总分。
条件求和
计算4号店销售金额大于4000元的合计金额,满足条件【4号店】和【金额大于4000元】的销售单,计算金额合计,公式【=SUMPRODUCT((B2:B16=B3)*(G2:G16>4000)*G2:G16)】。
隔列求和
计算公司本年度各月借贷总额,需要分别计算三个部门的借方、贷方合计。公式【=SUMPRODUCT(($B$2:$G$2=$H$2)*(B3:G3))】,其中,借贷方引用范围不变,使用绝对引用,向下复制公式,得到每月借方合计金额。同理,可计算贷方合计金额。
SUMPRODUCT是个好东西,希望你也有一个!
教程源文件链接:http://pan.baidu.com/s/1qYg21n2
联系客服