一题多解之两表条件汇总：MMULT与SUMIFS+COUNTIFS的对抗

dengjq2000

经常碰到两个表格进行条件汇总或计算，而且通常一个表是另一个表条件，需通过一个表为条件去查找另一个表数据，然后再进行汇总。
表一（车辆出勤记录）：

日期	车牌号码	车型类别	实际载重（吨）	行驶公里数（公里）
2012-2-1	粤 12XXX	B1类	5	500
2012-10-1	粤 13XXX	B1类	6	500
2012-10-11	粤 16XXX	A1类	20	200
2013-6-12	粤 22XXX	C2类	4	400
2013-9-1	粤 23XXX	B2类	5	500
2013-9-13	粤 33XXX	A2类	22	300
2013-10-16	粤 15XXX	A6类	28	400
2014-1-1	粤 18XXX	B1类	3	300
2014-2-4	粤 17XXX	C1类	7	700
2014-3-1	粤 22XXX	C2类	5	500
2014-4-2	粤 23XXX	B2类	2	200
2014-5-6	粤 17XXX	C1类	5	500
2014-6-6	粤 16XXX	A1类	22	300
2014-7-7	粤 33XXX	A2类	22	500
2014-8-12	粤 28XXX	A3类	21	400
2014-9-12	粤 27XXX	A4类	25	300
2012-10-15	粤 36XXX	A6类	26	600
2014-11-15	粤 26XXX	A6类	25	500
2014-12-25	粤 15XXX	A6类	23	700

表二（车辆参数表）：

车型类别	额定载重（吨）	是否挂车
B1类	8	否
C1类	6	否
C2类	7	否
B2类	9	否
A1类	25	是
A2类	26	是
A3类	27	是
A4类	28	是
A5类	29	是
A6类	30	是

需要求助是：

年度	额定载重（吨）	挂车行驶公里数（公里）
2012
2013
2014

注：该数据是根据：http://club.excelhome.net/thread-1166007-1-1.html  修改转变而来。

dengjq2000

方法一：辅助列

碰到这样的问题，如果通过辅助列，解决起来也相对比较方便。
如附件的工作表：“方法一辅助列”通过辅助列O列公式：

1. =VLOOKUP($E5,$I:$K,COLUMN(B$1),)

复制代码

往下拉，进行对“额定载重（吨）”的查找；通过辅助列P列公式：

1. =VLOOKUP($E5,$I:$K,COLUMN(C$1),)

复制代码

往下拉，来进行对“是否挂车”的判断。
然后通过公式，单元格T2公式：

1. =SUMPRODUCT((YEAR(C$5:C$23)=S4)*O$5:O$23)

复制代码

往下拉

以及单元格U2公式：

1. =SUMPRODUCT((YEAR(C$5:C$23)=S4)*(P$5:Q$23="是")*G$5:G$23)

复制代码

往下拉，便可很轻松完成。

dengjq2000

方法二：MMULT函数
通常碰到这样的问题，如果不借用辅助列来实现。首先想到就应该是：MMULT函数了。
单元格T2数组公式（数组公式确认需同时按：CTRL + SHIFT + ENTER 三键确认才有效）：

1. =SUMPRODUCT((YEAR(C$5:C$23)=S4)*(MMULT(--(E$5:E$23=TRANSPOSE(I$5:I$14)),J$5:J$14)))

复制代码

单元格U2数组公式：

1. =SUMPRODUCT((YEAR(C$5:C$23)=S4)*(MMULT(--(E$5:E$23=TRANSPOSE(I$5:I$14)),N(K$5:K$14="是")))*G$5:G$23)

复制代码

往下拉，便可实现。

现在主要来分析一下上面数组公式运算机理。
一、数组公式：=SUMPRODUCT((YEAR(C$5:C$23)=S4)*(MMULT(--(E$5:E$23=TRANSPOSE(I$5:I$14)),J$5:J$14))) 运算机理分析
1、TRANSPOSE(I$5:I$14)   目的就是为了转置。行排放变成列排放
选择一个一行十列单元格区域，然后把公式：TRANSPOSE(I$5:I$14) 进行CTRL + SHIFT + ENTER 三键确认，就会列出：

B1类

C1类

C2类

B2类

A1类

A2类

A3类

A4类

A5类

A6类

2、MMULT(--(E$5:E$23=TRANSPOSE(I$5:I$14)),J$5:J$14) ，实现了方法一辅助列当中O列的目的

3、=SUMPRODUCT((YEAR(C$5:C$23)=S4)*(MMULT(--(E$5:E$23=TRANSPOSE(I$5:I$14)),J$5:J$14)))
上述第2点理解好了，接下来就是挺简单的单元格求和罢了。

二、数组公式：=SUMPRODUCT((YEAR(C$5:C$23)=S4)*(MMULT(--(E$5:E$23=TRANSPOSE(I$5:I$14)),N(K$5:K$14="是")))*G$5:G$23)  运算机理分析
这数组公式与上面数组（即T列）运算机理一样，只是注意一下，对于条件判断需通过进行1与0转换

dengjq2000

方法三：SUMIFS与COUNTIFS

这方法三：SUMIFS与COUNTIFS才是重头戏。在EXCEL 2007版本增加了新函数当中就是有SUMIFS与COUNTIFS这两个函数。他们在解决多工作表多条件汇总与计数上实现飞跃。可以查阅主题：多工作表两（多）条件汇总  与 多工作表两（多）条件计数-COUNTIFS函数 解决了在EXCEL 2003实现的困惑。
在这里又要让SUMIFS与COUNTIFS这两个函数体现其独特的一面（详见可见主楼附件的工作表：方法三SUMIFS COUNTIFS）。
单元格T2公式：

1. =SUMPRODUCT(COUNTIFS(C$5:C$23,">="&DATE(S4,1,1),C$5:C$23,"<"&DATE(S4+1,1,1),E$5:E$23,$I$5:$I$14)*J$5:J$14)

复制代码

单元格U2数组公式：

1. =SUMPRODUCT(SUMIFS(G$5:G$23,C$5:C$23,">="&DATE(S4,1,1),C$5:C$23,"<"&DATE(S4+1,1,1),E$5:E$23,$I$5:$I$14)*IF(K$5:K$14="是",1,))

复制代码

或：

1. =SUMPRODUCT(SUMIFS(G$5:G$23,C$5:C$23,">="&DATE(S4,1,1),C$5:C$23,"<"&DATE(S4+1,1,1),E$5:E$23,$I$5:$I$14)*N(K$5:K$14="是"))

复制代码

让问题实现如果方便，也让MMULT函数怀恨在心了。

现在分析这两个公式（数组公式）运算机理。
一、公式：=SUMPRODUCT(COUNTIFS(C$5:C$23,">="&DATE(S4,1,1),C$5:C$23,"<"&DATE(S4+1,1,1),E$5:E$23,$I$5:$I$14)*J$5:J$14) 运算逻辑分析
1、COUNTIFS(C$5:C$23,">="&DATE(S4,1,1),C$5:C$23,"<"&DATE(S4+1,1,1),E$5:E$23,$I$5:$I$14)   目的就是分别以：
“车辆参数表”的第一列“I列车型类别”为条件对“原表”的第三列（E列车型类别），以及“原表”第一列（C列日期）分年度段为条件进行计数
2、=SUMPRODUCT(COUNTIFS(C$5:C$23,">="&DATE(S4,1,1),C$5:C$23,"<"&DATE(S4+1,1,1),E$5:E$23,$I$5:$I$14)*J$5:J$14) 理解第1点，接下来就单条求和了

dengjq2000

二、公式：=SUMPRODUCT(SUMIFS(G$5:G$23,C$5:C$23,">="&DATE(S4,1,1),C$5:C$23,"<"&DATE(S4+1,1,1),E$5:E$23,$I$5:$I$14)*IF(K$5:K$14="是",1,)) 运算机理分析
1、SUMIFS(G$5:G$23,C$5:C$23,">="&DATE(S4,1,1),C$5:C$23,"<"&DATE(S4+1,1,1),E$5:E$23,$I$5:$I$14)   目的就是分别以：
“车辆参数表”的第一列“I列车型类别”为条件对“原表”的第三列（E列车型类别），以及“原表”第一列（C列日期）分年度段为条件对“原表”的第五列[G列行驶公里数（公里）]进行汇总

2、=SUMPRODUCT(SUMIFS(G$5:G$23,C$5:C$23,">="&DATE(S4,1,1),C$5:C$23,"<"&DATE(S4+1,1,1),E$5:E$23,$I$5:$I$14)*IF(K$5:K$14="是",1,)) 进行条件判断，当是否挂车为“是”则为 1 提出数值汇总，否则为 0 不提出该数值

dengjq2000

方法四：SQL语句+数据透视表

SQL语句为：

1. select 日期,IIF(是否挂车="否",0,`行驶公里数（公里）`),`额定载重（吨）` from
   
2. (select A.日期,A.车型类别,A.`行驶公里数（公里）`,B.`额定载重（吨）`,B.是否挂车 from
   
3. (select YEAR(日期) as 日期,车型类别,`行驶公里数（公里）` from [数据源$C4:G] where not isnull(车型类别))A
   
4. left join
   
5. (select * from [数据源$I4:K])B
   
6. on A.车型类别=B.车型类别)
   

复制代码

分析该SQL语句：

select 日期,IIF(是否挂车="否",0,`行驶公里数（公里）`),`额定载重（吨）` from

(select A.日期,A.车型类别,A.`行驶公里数（公里）`,B.`额定载重（吨）`,B.是否挂车 from

(select YEAR(日期) as 日期,车型类别,`行驶公里数（公里）` from [数据源$C4:G] where not isnull(车型类别))A

left join

(select * from [数据源$I4:K])B

on A.车型类别=B.车型类别)

第一层：

select A.日期,A.车型类别,A.`行驶公里数（公里）`,B.`额定载重（吨）`,B.是否挂车 from

(select YEAR(日期) as 日期,车型类别,`行驶公里数（公里）` from [数据源$C4:G] where not isnull(车型类别))A

left join

(select * from [数据源$I4:K])B

on A.车型类别=B.车型类别目的就是实现以“车型类别”桥梁对表一进行添加列。

执行SQL语句后，运行的结果是：

日期	车型类别	行驶公里数（公里）	额定载重（吨）	是否挂车
2012	B1类	500	8	否
2012	B1类	500	8	否
2012	A1类	200	25	是
2013	C2类	400	7	否
2013	B2类	500	9	否
2013	A2类	300	26	是
2013	A6类	400	30	是
2014	B1类	300	8	否
2014	C1类	700	6	否
2014	C2类	500	7	否
2014	B2类	200	9	否
2014	C1类	500	6	否
2014	A1类	300	25	是
2014	A2类	500	26	是
2014	A3类	400	27	是
2014	A4类	300	28	是
2012	A6类	600	30	是
2014	A6类	500	30	是
2014	A6类	700	30	是

第二层：

select 日期,IIF(是否挂车="否",0,`行驶公里数（公里）`),`额定载重（吨）` from

(select A.日期,A.车型类别,A.`行驶公里数（公里）`,B.`额定载重（吨）`,B.是否挂车 from

(select YEAR(日期) as 日期,车型类别,`行驶公里数（公里）` from [数据源$C4:G] where not isnull(车型类别))A

left join

(select * from [数据源$I4:K])B

on A.车型类别=B.车型类别) 目的就是对是否挂车为“否”进行赋值为 0

执行SQL语句后，运行的结果是：

日期	Expr1001	额定载重（吨）
2012	0	8
2012	0	8
2012	200	25
2013	0	7
2013	0	9
2013	300	26
2013	400	30
2014	0	8
2014	0	6
2014	0	7
2014	0	9
2014	0	6
2014	300	25
2014	500	26
2014	400	27
2014	300	28
2012	600	30
2014	500	30
2014	700	30

dengjq2000

方法五：直接用SQL语句一气呵成
SQL语句：

1. select 日期,sum(`额定载重（吨）` ) as `额定载重（吨）`,sum(行驶公里数) as `行驶公里数（公里）` from (select 日期,IIF(是否挂车="否",0,`行驶公里数（公里）`) as 行驶公里数,`额定载重（吨）` from
   
2. (select A.日期,A.车型类别,A.`行驶公里数（公里）`,B.`额定载重（吨）`,B.是否挂车 from
   
3. (select YEAR(日期) as 日期,车型类别,`行驶公里数（公里）` from [数据源$C4:G] where not isnull(车型类别))A
   
4. left join
   
5. (select * from [数据源$I4:K])B
   
6. on A.车型类别=B.车型类别)) group by 日期
   

复制代码

中间两层已经方法四：SQL语句+数据透视表 进行了分析，不在分析。
对最外层进行分析：

select 日期,sum(`额定载重（吨）` ) as `额定载重（吨）`,sum(行驶公里数) as `行驶公里数（公里）` from (select 日期,IIF(是否挂车="否",0,`行驶公里数（公里）`) as 行驶公里数,`额定载重（吨）` from
(select A.日期,A.车型类别,A.`行驶公里数（公里）`,B.`额定载重（吨）`,B.是否挂车 from
(select YEAR(日期) as 日期,车型类别,`行驶公里数（公里）` from [数据源$C4:G] where not isnull(车型类别))A
left join
(select * from [数据源$I4:K])B
on A.车型类别=B.车型类别)) group by 日期

目的就是以日期分类进行对：额定载重（吨）与 行驶公里数 进行汇总。

dengjq2000

邀请：
希望这主题跟一部分人有所帮助，同时希望有其他方法解决的朋友，请亮出您的公式。好的跟帖子将给予一定财富或技术分的奖励（暂不对VBA跟帖进行评价）。谢谢！

dengjq2000

该主题对论坛求助主题的帮助或借鉴的有：
注意：
主楼的附件当中的工作表：“方法一辅助列”的单元格U4公式应该为：

=SUMPRODUCT((YEAR(C$5:C$23)=S4)*(P$5:Q$23="是")*G$5:G$23)

1、不增加行或列的求和公式，求指导 ；

2、多单元格混合运算         4楼

3、检索所需数据并提取

4、销量的各种情况，求各店最多

5、运用公式根据生产计划和BOM表计算物料的当天、班需求

6、不借助辅助列多条件求和。被求和对象有一定条件限制

7、考勤统计，多种考勤符号

8、用什么函数统计

dengjq2000

同时把SUMPRODUCT函数关于 * 与 , 一点区别一起共享大家