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

原创 kaixinit  2018-07-22 13:28  阅读 1,041 次

如今改用sumproduct函数,就简单多了。查过sumproduct函数的用法,其解释为“求二个或二个以上数组的乘积之和”,假设因此就片面地理解为这与多条件求和无关。那就错了。事实上呢。利用条件真假的值(“真”相应值为1,“假”相应值为0),sumproduct函数用起来要比sumif函数好用的多。

比如。“=SUMPRODUCT((A1:A100=“project师”)*1)”就统计了A列中含有“project师”的行数。须要注意的是,后面“*1”是不可缺少的。否则结果为零。以下举几个应用案例。

1、库存统计

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

(1)入库记录表A、B、C、D、E、F列

入库时间 单据号 零件号 货物数量 包装种类 质量状态
2013-06-04 A1307703 F114001721 72 料箱 合格
2013-06-04 A1307703 F114001722 36 料箱 合格
2013-06-04 A1307703 F114001723 72 料箱 合格
2013-06-04 A1307703 F114001724 48 料箱 合格
2013-06-04 A1307703 F114001725 48 料箱 合格
2013-06-04 A1307703 F114001726 120 料箱 合格
2013-06-04 A1307703 F114001727 48 料箱 合格
2013-06-04 A1307703 F114001728 24 料箱 合格
2013-06-04 A1307708 F114001729 144 料箱 合格
2013-06-04 A1307708 F114001730 48 料箱 合格
2013-06-04 A1307708 F114001731 48 料箱 合格
2013-06-04 A1307708 F114001732 48 料箱 合格
2013-06-04 A1307708 F114001733 72 料箱 合格
2013-06-04 A1307708 F114001734 72 料箱 合格
2013-06-04 A1307714 F114001728 144 料箱 合格
2013-06-04 A1307714 F114001729 144 料箱 合格
2013-06-04 A1307714 F114001730 48 料箱 合格
2013-06-04 A1307714 F114001731 48 料箱 合格
2013-06-04 A1307714 F114001732 48 料箱 合格
2013-06-04 A1307714 F114001733 144 料箱 合格
2013-06-04 A1307714 F114001734 144 料箱 合格
2013-06-04 A1307719 F114001735 72 料箱 合格
2013-06-04 A1307719 F114001736 36 料箱 合格
2013-06-04 A1307719 F114001737 144 料箱 合格

 

(2)出库记录表A、B、C、D、E、F列

出库时间 单据号 零件号 货物数量 包装种类 质量状态
2013-06-06 0000003 F114001721 576 料箱 合格
2013-06-06 0000005 F114001722 192 料箱 合格
2013-06-06 0000006 F114001723 240 料箱 合格
2013-06-06 0000007 F114001724 252 料箱 合格
2013-06-07 0000008 F114001725 288 料箱 合格
2013-06-07 0000008 F114001726 288 料箱 合格
2013-06-07 0000008 F114001727 144 料箱 合格
2013-06-07 0000009 F114001728 432 料箱 合格
2013-06-07 0000010 F114001729 216 料箱 合格
2013-06-07 0000010 F114001730 360 料箱 合格
2013-06-07 0000010 F114001731 144 料箱 合格
2013-06-07 0000011 F114001732 144 料箱 合格
2013-06-07 0000012 F114001733 72 料箱 合格
2013-06-07 0000013 F114001734 360 料箱 合格
2013-06-07 0000014 F114001728 120 料箱 合格
2013-06-07 0000016 F114001729 72 料箱 合格
2013-06-07 0000016 F114001730 118 料箱 合格
2013-06-07 0000016 F114001731 144 料箱 合格
2013-06-07 0000016 F114001732 144 料箱 合格
2013-06-08 0000018 F114001733 72 料箱 合格
2013-06-08 0000018 F114001734 72 料箱 合格
2013-06-08 0000019 F114001735 216 料箱 合格
2013-06-08 0000019 F114001736 216 料箱 合格
2013-06-08 0000020 F114001737 192 料箱 合格

 

(3)库存记录表A、B、C、D、E、F、G列

零件号 货物名称 包装种类 质量状态 合计入库 合计出库 库存数量
F114001721 YN3 HousingLH 料箱 合格 1116 1564 72
F114001722 YN3 HousingRH 料箱 合格 492 1163 36
F114001723 YN3 LensLH 料箱 合格 668 2295 72
F114001724 YN3 LensRH 料箱 合格 600 2183 48
F114001725 YP7 HousingLH 料箱 合格 312 1152 48
F114001726 YP7 HousingRH 料箱 合格 624 926 120
F114001727 YP7 LensLH 料箱 合格 1008 636 48
F114001728 YP7 LensRH 料箱 合格 600 2308 24
F114001729 T61 HousingLH 料箱 合格 648 1352 144
F114001730 T61 HousingRH 料箱 合格 264 1368 0
F114001731 T61 LensLH 料箱 合格 228 954 0
F114001732 T61 LensRH 料箱 合格 180 1480 0
F114001733 T63 HousingLH 料箱 合格 520 1048 0
F114001734 T63 HousingRH 料箱 合格 444 1138 0

 

(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文件里有三张表,一张质量记录汇总表,一张每日通报,一张累计通报,在加入每天质量记录后(点击button。VBA程序自己主动依据P1单元格中的日期从数据库中提取质量记录)。每日通报和累计通报用sumproduct函数自己主动反应当前质量情况。改动日期,每日通报和累计通报两张报表马上反应当日的质量数据。

(1)质量记录A、B、C、D、E、F列

投递日期 投递单位 时限情况
进口邮件数 未及时妥投邮件数 未妥投邮件数 及时妥投率
2014-9-1 合肥市 168 30 7 77.98%
2014-9-1 阜阳市 44 3 0 93.18%
2014-9-1 蚌埠市 40 1 0 97.50%
2014-9-1 芜湖市 103 7 0 93.20%
2014-9-1 安庆市 60 9 0 85.00%
2014-9-1 宿州市 45 4 0 91.11%
2014-9-1 滁州市 73 1 0 98.63%
2014-9-1 六安市 52 1 0 98.08%
2014-9-1 黄山市 17 3 0 82.35%
2014-9-1 淮北市 15 1 0 93.33%
2014-9-1 亳州市 34 2 1 91.18%
2014-9-1 淮南市 37 1 1 94.59%
2014-9-1 马鞍山 24 1 0 95.83%
2014-9-1 宣城市 29 3 0 89.66%
2014-9-1 铜陵市 23 4 0 82.61%
2014-9-1 池州市 21 3 0 85.71%
2014-9-2 合肥市 270 109 8 56.67%
2014-9-2 阜阳市 62 7 0 88.71%
2014-9-2 蚌埠市 51 1 0 98.04%
2014-9-2 芜湖市 107 10 0 90.65%
2014-9-2 安庆市 72 5 0 93.06%

 

(2)每日通报A、B、C、D、E、F列

投递单位 时限情况
进口邮件数 未及时妥投邮件数 未妥投邮件数 及时妥投率
合肥市 168 30 7 77.98%
阜阳市 44 3 0 93.18%
蚌埠市 40 1 0 97.50%
芜湖市 103 7 0 93.20%
安庆市 60 9 0 85.00%
宿州市 45 4 0 91.11%
滁州市 73 1 0 98.63%
六安市 52 1 0 98.08%
黄山市 17 3 0 82.35%
淮北市 15 1 0 93.33%
亳州市 34 2 1 91.18%
淮南市 37 1 1 94.59%
马鞍山 24 1 0 95.83%
宣城市 29 3 0 89.66%
铜陵市 23 4 0 82.61%
池州市 21 3 0 85.71%
累   计 785 74 9 89.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就能够了。
本文地址:http://www.kaixinit.com/info/office/682.html
版权声明:本文为原创文章,版权归 kaixinit 所有,欢迎分享本文,转载请保留出处!

发表评论


表情