Sumproduct函数的本职工作是返回两组数乘积的和,如: =SUMPRODUCT(B2:B7,C2:C7)等同于B和C列一一对应求积,然后再用SUM求和。 扩展用法 原本它只是一个很简单的一个分组乘积后再求和的函数,后被高手挖掘出了很多扩展用法。不但可以求和,还可以计数、查找、计算排名。这简直是抢Coutifs、Sumifs、Lookup等函数的饭碗啊。
1、含文本数字的求和【例】对B列的数字求和,其中含有文本型数字=SUMPRODUCT(B2:B10*1)注:*1可以把文本型数字转换成数值型数字 2、多条件求和【例】如下图所示,根据A11的产和b11的类别统计总的数量=SUMPRODUCT((A2:A7=A11)*(B2:B7=B11)*C2:C7 3、不重复的值计数【例】下图所示的客户消费明细表中,要求计算客户的总人数。=SUMPRODUCT((1/COUNTIF(B2:B10,B2:B10))) 4、按条件整行求和【例】根据A11中的姓名,计算其1~6月份合计。 =SUMPRODUCT((A2:A6=A11)*B2:G6) 5、隔列求和 如果没有标题,可以用Sumproduct函数公式:=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 6、多表求和【例】如下面动图所示,要求计算本年所有月份的各个部门的工资合计数。汇总表B2单元格公式: =SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$3)&'月!A:A'),A2,INDIRECT(ROW($1:$3)&'月!C:C'))) GIF 7、多条件查找如果要返回数字且无重复,也可以用Sumproudct函数。【例】根据E列姓名和月份,从左表中查找其数量=SUMPRODUCT((A2:A31=E2)*(B2:B31=F2)*C2:C31) 8、交叉查找 【例】根据姓名和月份,要求从下图上表中查找对应的销量C14公式=SUMPRODUCT((A2:A11=A14)*(B1:G1=B14)*B2:G11) 9、中国式排名 【例】如下图所示,在C列计算B列的销量的排名, =SUMPRODUCT(($E$3:$E$13>=E3)*(1/COUNTIF(E$3:E$13,E$3:E$13))) 由于时间关系,总结的也只是sumproudct函数用法的一部分,可见这个函数远不是我们平时想像的那么简单。 |
|