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-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文件中有三张表，一张质量记录汇总表，一张每日通报，一张累计通报，在添加每天质量记录后（点击按钮，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就可以了。