excel分表数据汇总统计

2020年8月14日 分类:Excel 作者:企业网盘

很多电商、工资等数据统计的时候都会遇到这样的问题:数据以不同的部门,或者不同的月份,也或不同的年份等情况分布在不同的工作表里,经常要求对分表根据相同数据项对数据进行汇总统计。

示例数据如下图:

共12月的数据,无需合并,直接汇总到“汇总”工作表“年销售业绩”列中。

注意:各分表的结构一致,姓名都在B列,求和的数据都在C列。

第一种情况:所有表“姓名”排序一致

这种求和方法很简单,只要在“汇总”表C2单元格输入公式“=SUM(‘*’!C2)”就可以了,回车以后,公式自动变成:=SUM(‘1月:12月’!C2)。

第二种情况:所有表“姓名”排序不一致

这种情况下求和稍有麻烦,我们一步一步分析:

第一步:

如果是仅对一个表,只需要Sumif函数直接求和即可,比如:

=SUMIF(‘1月’!$B$2:$B$37,汇总!B2,’1月’!$C$2:$C$37);

第二步:

现在是对12个表汇总,需借助INDIRECT函数生成对多个表区域的引用,即是:

INDIRECT(ROW($1:$12)&”月!$B$2:$B$37″)  –引用每个表的B列姓名数据区域

以及

INDIRECT(ROW($1:$12)&”月!$C$2:$C$37″)– 引用每个表的C列销量数据区域。

第三步:

写入Sumif函数,即是:

SUMIF(INDIRECT(ROW($1:$12)&”月!$B$2:$B$37″),汇总!B2,INDIRECT(ROW($1:$12)&”月!$c$2:$c$37″))

第四步:

INDIRECT函数引用的是一组数,数组求和,最后最外层加Sumproduct函数,即是:

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$12)&”月!$B$2:$B$37″),汇总!B2,INDIRECT(ROW($1:$12)&”月!$c$2:$c$37″)))。

结果如下:

另:

本例使用INDIRECT函数产生多维引用,其中:

使用ROW($1:$12)产生月份序号数组:

{1;2;3;4;5;6;7;8;9;10;11;12}

公式中的INDIRECT(ROW($1:$12)&”月!$B$2:$B$37″)作为SUMIF函数的range参数,表示每个月销售记录表的$B$2:$B$37区域,共12个区域:

{“1月!$B$2:$B$37″;”2月!$B$2:$B$37″;”3月!$B$2:$B$37″;”4月!$B$2:$B$37″;”5月!$B$2:$B$37″;”6月!$B$2:$B$37″;”7月!$B$2:$B$37″;”8月!$B$2:$B$37″;”9月!$B$2:$B$37″;”10月!$B$2:$B$37″;”11月!$B$2:$B$37″;”12月!$B$2:$B$37″}

同理,公式中的INDIRECT(ROW($1:$12)&”月!$c$2:$c$37”)表示每个月销售记录表的$c$2:$c$37区域,作为SUMIF函数的sum_range参数。

阅读已结束,喜欢的话就点个赞吧
注册坚果云网盘
还有其他问题,可以咨询小坚果咨询小坚果
继续阅读