原来Excel中的SUMPRODUCT函数如此神通广大

问题:一个水果商的进货表如下所示,求该进货商此次进货的费用?

解析:在D2单元格中输入的公式可做如下拆分:

=SUMPRODUCT(B2:B4,C2:C4)=B2*C2+B3*C3+B4*B4=8*22+6*12+12*11=380

问题:求下表中销售部门男性的业绩。

解析:在F2单元格中输入的内容如下所示:

=SUMPRODUCT((A2:A7="销售部")*(C2:C7="男"),D2:D7)

其中A2:A7="销售部"会返回一个数组{1,0,0,1,0,1},即A2:A7这六个单元格中的内容等于"销售部"的会返回1,否则返回0。C2:C7="男"会返回一个数组{1,0,0,1,1,0},即C2:C7这六个单元格中的内容等于"男"的会返回1,否则返回0。则F2单元格中输入的内容可以变形为:

=SUMPRODUCT({1,0,0,1,0,1},{1,0,0,1,1,0},D2:D7)=1*1*D2+0*0*D3+0*0*D4+1*1*D5+0*1*D6+1*0*D7=D2+D5=65+21=86

问题:求下表中销售部广告部女性的业绩。

解析:在F2单元格中输入的内容如下所示:

=SUMPRODUCT(SUMIFS(D2:D7,A2:A7,{"广告部","销售部"},C2:C7,"女"))

其中D2:D7表示求和区域,A2:A7和{"广告部","销售部"}为一组,表示在A2:A7单元格区域内,找出广告部和销售部,C2:C7和"女"为一组,表示在A2:A7内找出女,则SUMIFS这个函数的意思便是对同时满足上述的两组条件的D2:D7单元格区域内的数据进行相加,即将广告部的李四的业绩34与销售部宋虎的业绩59相加,得到93,但实际得到的结果却是只有广告部李四的业绩34,因此得到一个结论便是类似于{"广告部","销售部"}这样的条件,SUMIFS只能够计算里面的第一个条件,如果想要计算所有的条件,需要借助SUMPRODUCT函数,这样的话,SUMPRODUCT函数括号里面的SUMIFS函数就会变成

=SUMPRODUCT(D2:D7,{1,1,0,1,0,1},{0,1,1,0,0,1})=D2*1*0+D3*1*1+D4*0*1+D5*1*0+D6*0*0+D7*1*1=D3+D7=34+59=93

其中第一个括号里面的1表示在A2:A7单元格中满足"销售部"或"广告部",否则为0,第二个括号里面的1表示在C2:C7单元格中满足"女",否则为0.