Excel精英培训网

 找回密码
 注册

QQ登录

只需一步,快速开始

你正在寻找更好的Excel学习教程吗?Excel技巧80集+数据透视表+函数初中高全套+VBA80集,想学的这儿全都有
查看: 87|回复: 5

[求助] 请指点如何实现跨表多条件求和,谢谢!

[复制链接]
发表于 2017-8-13 11:01 | 显示全部楼层 |阅读模式
excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
请指点如何实现跨表格进行多条件求和,谢谢!
求和区域.png
数据区域.png

多条件数据计算.rar

8.89 KB, 下载次数: 5

发表于 2017-8-13 11:16 | 显示全部楼层
C4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)<YEAR(计算!$A4))*1)
D4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)<YEAR(计算!$A4))*(数据!$C$2:$C$26))
E4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)=YEAR(计算!$A4)*(MONTH(数据!$D$2:$D$26)<MONTH($A4))*1))
F4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)=YEAR(计算!$A4)*(MONTH(数据!$D$2:$D$26)<MONTH($A4))*(数据!$C$2:$C$26)))
G4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)=YEAR(计算!$A4)*(MONTH(数据!$D$2:$D$26)=MONTH($A4))*1))
H4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)=YEAR(计算!$A4)*(MONTH(数据!$D$2:$D$26)=MONTH($A4))*(数据!$C$2:$C$26)))
回复 支持 反对

使用道具 举报

 楼主| 发表于 2017-8-13 13:04 | 显示全部楼层
cabcyvr 发表于 2017-8-13 11:16
C4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)

谢谢您的解答!我按您的公式进行了测试,发现C、D、E、G列结果正确,但F、H列没有结果(请见附件),请问为何会出现这种情况?另外还想请教二个问题:一是可以将计算公式中所有涉及到“数据”表格中G、D、C列的区域由$2至$26行扩展到整个G、D、C列吗?二是除了使用SUMPRODUCT函数外,也可以使用SUMIFS或MMULT函数来实现上述多条件求和吗?

多条件数据计算2.rar

11.37 KB, 下载次数: 4

回复 支持 反对

使用道具 举报

发表于 2017-8-13 18:23 | 显示全部楼层
不要胡来 发表于 2017-8-13 13:04
谢谢您的解答!我按您的公式进行了测试,发现C、D、E、G列结果正确,但F、H列没有结果(请见附件),请问 ...

不好意思,EFGH4列公式都有错误,已经修改,之前只有CD列是对的。

C4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)<YEAR(计算!$A4))*1)
D4 =SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)<YEAR(计算!$A4))*(数据!$C$2:$C$26))

修改后的EFGH
E4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)=YEAR(计算!$A4))*(MONTH(数据!$D$2:$D$26)<MONTH($A4))*1)
F4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)=YEAR(计算!$A4))*(MONTH(数据!$D$2:$D$26)<MONTH($A4))*(数据!$C$2:$C$26))
G4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)=YEAR(计算!$A4))*(MONTH(数据!$D$2:$D$26)=MONTH($A4))*1)
H4=SUMPRODUCT((INT(数据!$G$2:$G$26)=INT(计算!$A4))*(YEAR(数据!$D$2:$D$26)=YEAR(计算!$A4))*(MONTH(数据!$D$2:$D$26)=MONTH($A4))*(数据!$C$2:$C$26))


回复 支持 反对

使用道具 举报

发表于 2017-8-13 20:08 | 显示全部楼层
C列=COUNTIFS(数据!G:G,计算!A3,数据!D:D,">=2017/01/01",数据!D:D,"<"&DATE(YEAR(计算!A3),MONTH(计算!A3),1))D列=SUMPRODUCT((数据!G$2:G$26=计算!A3)*(数据!D$2:D$26<DATE(2017,1,1))*数据!C$2:C$26)
E列=COUNTIFS(数据!G:G,计算!A3,数据!D:D,">=2017/01/01",数据!D:D,"<"&DATE(YEAR(计算!A3),MONTH(计算!A3),1))
F列=SUMPRODUCT((数据!G$2:G$26=计算!A3)*(数据!D$2:D$26>=DATE(2017,1,1))*(数据!D$2:D$26<=DATE(YEAR(计算!A3),MONTH(计算!A3),1))*(数据!C$2:C$26))
G列=COUNTIFS(数据!G:G,计算!A3,数据!D:D,">="&DATE(2017,MONTH(A3),1),数据!D:D,"<"&DATE(YEAR(计算!A3),MONTH(计算!A3)+1,1))
H列=SUMPRODUCT((数据!G$2:G$26=计算!A3)*(数据!D$2:D$26>=DATE(2017,MONTH(A3),1))*(数据!D$2:D$26<DATE(YEAR(计算!A3),MONTH(计算!A3)+1,1))*(数据!C$2:C$26))

回复 支持 反对

使用道具 举报

发表于 2017-8-14 05:10 | 显示全部楼层
在日期比较的公式中加INT的目的是为了防止两边日期数值输入时 不规范造成成实际数值差异而造成误判断。

比如一个是年月日,一个是年月日时分秒,但显示出来的只是年月日,不能保证是否是真正的年月日还是通过其他格式只显示了年月日。这样直接用等号判断就会因为小数点的原因判断为不等于。
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2017-8-23 08:47 , Processed in 0.093600 second(s), 21 queries , Gzip On, Memcache On.

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表