首页 > 代码库 > Excel函数sumproduct应用案例

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料箱合格1116156472
F114001722YN3 HousingRH料箱合格492116336
F114001723YN3 LensLH料箱合格668229572
F114001724YN3 LensRH料箱合格600218348
F114001725YP7 HousingLH料箱合格312115248
F114001726YP7 HousingRH料箱合格624926120
F114001727YP7 LensLH料箱合格100863648
F114001728YP7 LensRH料箱合格600230824
F114001729T61 HousingLH料箱合格6481352144
F114001730T61 HousingRH料箱合格26413680
F114001731T61 LensLH料箱合格2289540
F114001732T61 LensRH料箱合格18014800
F114001733T63 HousingLH料箱合格52010480
F114001734T63 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就可以了。






Excel函数sumproduct应用案例