Excel函数sumproduct应用案例-多条件求和

作者：iamlaosong

越来越觉得sumproduct这个函数有用，过去用sum组函数，改起来复制起来都麻烦，sumif在条件多的时候也觉得不方便。现在改用sumproduct函数，就简单多了。查过sumproduct函数的使用方法，其解释为“求二个或二个以上数组的乘积之和”，如果因此就片面地理解为这与多条件求和无关，那就错了。其实呢，利用条件真假的值（“真”对应值为1，“假”对应值为0），sumproduct函数用起来要比sumif函数好用的多。例如，“=SUMPRODUCT(（A1:A100=“工程师”）*1)”就统计了A列中含有“工程师”的行数，需要注意的是，后面“*1”是必不可少的，否则结果为零。下面举几个应用案例。

1、库存统计

Excel文件中有三张表，一张入库记录，一张出库记录，一张库存记录，在添加出入库记录后，库存表用sumproduct函数自动反应当前库存。

（1）入库记录表A、B、C、D、E、F列

入库时间单据号零件号货物数量包装种类质量状态2013-06-04A1307703F11400172172料箱合格2013-06-04A1307703F11400172236料箱合格2013-06-04A1307703F11400172372料箱合格2013-06-04A1307703F11400172448料箱合格2013-06-04A1307703F11400172548料箱合格2013-06-04A1307703F114001726120料箱合格2013-06-04A1307703F11400172748料箱合格2013-06-04A1307703F11400172824料箱合格2013-06-04A1307708F114001729144料箱合格2013-06-04A1307708F11400173048料箱合格2013-06-04A1307708F11400173148料箱合格2013-06-04A1307708F11400173248料箱合格2013-06-04A1307708F11400173372料箱合格2013-06-04A1307708F11400173472料箱合格2013-06-04A1307714F114001728144料箱合格2013-06-04A1307714F114001729144料箱合格2013-06-04A1307714F11400173048料箱合格2013-06-04A1307714F11400173148料箱合格2013-06-04A1307714F11400173248料箱合格2013-06-04A1307714F114001733144料箱合格2013-06-04A1307714F114001734144料箱合格2013-06-04A1307719F11400173572料箱合格2013-06-04A1307719F11400173636料箱合格2013-06-04A1307719F114001737144料箱合格

（2）出库记录表A、B、C、D、E、F列

出库时间单据号零件号货物数量包装种类质量状态2013-06-060000003F114001721576料箱合格2013-06-060000005F114001722192料箱合格2013-06-060000006F114001723240料箱合格2013-06-060000007F114001724252料箱合格2013-06-070000008F114001725288料箱合格2013-06-070000008F114001726288料箱合格2013-06-070000008F114001727144料箱合格2013-06-070000009F114001728432料箱合格2013-06-070000010F114001729216料箱合格2013-06-070000010F114001730360料箱合格2013-06-070000010F114001731144料箱合格2013-06-070000011F114001732144料箱合格2013-06-070000012F11400173372料箱合格2013-06-070000013F114001734360料箱合格2013-06-070000014F114001728120料箱合格2013-06-070000016F11400172972料箱合格2013-06-070000016F114001730118料箱合格2013-06-070000016F114001731144料箱合格2013-06-070000016F114001732144料箱合格2013-06-080000018F11400173372料箱合格2013-06-080000018F11400173472料箱合格2013-06-080000019F114001735216料箱合格2013-06-080000019F114001736216料箱合格2013-06-080000020F114001737192料箱合格

（3）库存记录表A、B、C、D、E、F、G列

零件号货物名称包装种类质量状态合计入库合计出库库存数量F114001721YN3 HousingLH料箱合格1116156472F114001722YN3 HousingRH料箱合格492116336F114001723YN3 LensLH料箱合格668229572F114001724YN3 LensRH料箱合格600218348F114001725YP7 HousingLH料箱合格312115248F114001726YP7 HousingRH料箱合格624926120F114001727YP7 LensLH料箱合格100863648F114001728YP7 LensRH料箱合格600230824F114001729T61 HousingLH料箱合格6481352144F114001730T61 HousingRH料箱合格26413680F114001731T61 LensLH料箱合格2289540F114001732T61 LensRH料箱合格18014800F114001733T63 HousingLH料箱合格52010480F114001734T63 HousingRH料箱合格44411380

（4）库存计算公式

合计入库：=SUMPRODUCT((入库!$C$2:$C$65535=库存!$A2)*(入库!$E$2:$E$65535=库存!$C2)*(入库!$F$2:$F$65535=库存!$D2)*(入库!$D$2:$D$65535))

合计出库：=SUMPRODUCT((出库!$C$2:$C$65535=库存!$A2)*(出库!$E$2:$E$65535=库存!$C2)*(出库!$F$2:$F$65535=库存!$D2)*(出库!$D$2:$D$65535))

公式中每一组数字的条件判定，就会得出不同的“真”与“假”，数组的值变成了不同的“0”和“1”，各组的数字相乘，只有条件完全达到的行，才有可能得到数值，这些数值相加的结果就是我们要求的值。这就是SUMPRODUCT方式的多条件求各和。上述第一个公式可以描述为（第二个公式同理）：

=SUMPRODUCT((入库零件号区域=库存零件号)*(入库包装种类区域=库存包装种类)*(入库质量状态区域=库存质量状态)*(入库数量区域))

库存数量：=E2-F2

2、每日质量通报

Excel文件中有三张表，一张质量记录汇总表，一张每日通报，一张累计通报，在添加每天质量记录后（点击按钮，VBA程序自动根据P1单元格中的日期从数据库中提取质量记录），每日通报和累计通报用sumproduct函数自动反应当前质量情况。修改日期，每日通报和累计通报两张报表立即反应当日的质量数据。

（1）质量记录A、B、C、D、E、F列

投递日期投递单位时限情况进口邮件数未及时妥投邮件数未妥投邮件数及时妥投率 2014-9-1合肥市16830777.98%2014-9-1阜阳市443093.18%2014-9-1蚌埠市401097.50%2014-9-1芜湖市1037093.20%2014-9-1安庆市609085.00%2014-9-1宿州市454091.11%2014-9-1滁州市731098.63%2014-9-1六安市521098.08%2014-9-1黄山市173082.35%2014-9-1淮北市151093.33%2014-9-1亳州市342191.18%2014-9-1淮南市371194.59%2014-9-1马鞍山241095.83%2014-9-1宣城市293089.66%2014-9-1铜陵市234082.61%2014-9-1池州市213085.71%2014-9-2合肥市270109856.67%2014-9-2阜阳市627088.71%2014-9-2蚌埠市511098.04%2014-9-2芜湖市10710090.65%2014-9-2安庆市725093.06%

（2）每日通报A、B、C、D、E、F列

投递单位时限情况进口邮件数未及时妥投邮件数未妥投邮件数及时妥投率 合肥市16830777.98%阜阳市443093.18%蚌埠市401097.50%芜湖市1037093.20%安庆市609085.00%宿州市454091.11%滁州市731098.63%六安市521098.08%黄山市173082.35%淮北市151093.33%亳州市342191.18%淮南市371194.59%马鞍山241095.83%宣城市293089.66%铜陵市234082.61%池州市213085.71%累   计78574989.43%

计算公式（单元格P1存放通报日期）：

进口邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!C$4:C$500))

未及时妥投邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!D$4:D$500))

未妥投邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!E$4:E$500))

及时妥投率 ：=(B4-C4-D4)/B4

（3）累计通报

内容和每日通报一样只是数据为当月累计而已，所以各字段公式也差不多，只是多了个小于符号“<”，即：

进口邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500<=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!C$4:C$500))

未及时妥投邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500<=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!D$4:D$500))

未妥投邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500<=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!E$4:E$500))

及时妥投率 ：=(B4-C4-D4)/B4

公式的含义就不解释了，就是多条件求和，这里的条件可以是多种形式的，等于、大于、小于、不等于都行，只要记住真假的值：真=1，假=0就可以了。