原文标题:《多个工作表求和,你还在按Shift?用这个方法,能让表格自动更新!》
对于大部分公司来说,进行数据的汇总统计是日常工作中必不可少的一项。
其中,将多个表格数据进行汇总统计,是数据统计中最后一道步骤。
有很多小伙伴们在汇总时都是进行重复性的操作,工作效率低下。
这不,如下图,是一张各地区业绩汇总表。
点击加载图片
需要将每个月的业绩都相加,体现在汇总表中。
每个月的数据结构与汇总表的数据结构完全相同。1月和2月工作表的明细数据如下图:
点击加载图片
大部分小伙伴们在汇总时是不是很喜欢用下面这2种方式?
方法一:
先选中[汇总表]中的[B2]单元格,然后输入一个等号(=),之后点击1月工作表中的[B2]单元格,然后再输入一个加号(+),再点击2月工作表中的[B2]单元格,回车。如下图:
点击加载图片
最后,利用单元格右下角的填充柄向下拖动填充公式,即可得出下面的[B3:B5]的结果。
点击加载图片
点击加载图片
方法二:
先选中[汇总表]中的[B2]单元格,然后输入一个等号(=),之后输入SUM函数,点击[1月]工作表,并按住SHIFT键,再点击[2月]工作表中的[B2]单元格,最后回车即可。
点击加载图片
点击加载图片
以上两种方法的优点是:操作非常简单。
缺点是:当有新增月份时,还需要重复操作一遍,不能一劳永逸。
如何才能只设置一次公式,就能让表格自动更新呢?
来来来,咱们就一起看下如何实现这个神奇的功能吧!
1、神奇的辅助表
先选中[2月]工作表,再点击旁边的加号(+),新建一张空白的工作表。如下图:
点击加载图片
双击该工作表标签,将工作表重命名为:12月。
点击加载图片
之后按照上面的[方法二]用SUM函数进行求和。
先选中[汇总表]中的[B2]单元格,然后输入一个等号(=),之后输入SUM函数,点击[1月]工作表,并按住SHIFT键,再点击[12月]工作表中的[B2]单元格,最后回车即可。
点击加载图片
然后,将[12月]工作表隐藏起来。
以后如有新增的工作表,其内容将自动统计在内。
比如:我们新增一张工作表,试试看是否是真的变成自动统计了。
先选中[2月]工作表,然后再点击旁边的加号(+),
并将新工作表重命名为3月,并输入内容:
点击加载图片
再来看看汇总表中的数据:
点击加载图片
自动将新增的工作表数据统计在内了。
完美解决自动化问题。
另外,等到12月份的时候,再将原来隐藏的[12月]这张工作表取消隐藏,然后输入内容,汇总表中的公式也不需要进行任何修改,数据还是会自动更新的。
怎么样?
这个多表自动求和的方法是不是挺神奇的吧!
以下动图,供参考!
点击加载图片
2、知识扩展
上面多表求和的方法只能适用于每个月的表格结构位置完全一样。
如果每个月表格结构不完全一样的话,就不适用了。
比如下面这样,1月只有北京和上海,2月只有南京和天津的数据。
点击加载图片
此时我们可以用PowerQuery多表合并结合透视表的方法来实现。
也可以仅使用函数的方法来实现。
因为大部分小伙伴可能无法使用PowerQuery这个功能。所以我们这里就分享函数方法来解决此问题。
点击加载图片
公式如下:
=SUM(IFERROR(SUMIF(INDIRECT(ROW($1:$12)&'月!A:A'),A2,INDIRECT(ROW($1:$12)&'月!B:B')),0))
公式解析:
此公式大体由三个部分组成:
(1)SUMIF(INDIRECT(ROW($1:$12)&'月!A:A'),A2,INDIRECT(ROW($1:$12)&'月!B:B'))
点击加载图片
(2)IFERROR((1),0)
点击加载图片
(3)SUM((2))
点击加载图片
用这个函数的方法可以一步到位。
当然还是有一定难度。
PS:在低版中需要按三键[Ctrl+Shift+Enter]结束公式。
另外,还有一种利用辅助区域+函数的方法,也可以实现。这里也一并介绍给大家。如下图:
点击加载图片
其中:蓝色区域部分手动输入,黄色区域我们用等于号(=)分别引用每张工作表从[A2]单元格开始的内容。
大体意思就是在汇总表中建立一个辅助区域,然后将各个工作表中的数据都引用过来。
最后使用SUMIF函数对这个辅助区域进行求和即可。
点击加载图片
公式如下:
=SUMIF(E:H,A2,F:I)
在[E:H]列中查找[A2]单元格的内容,并对[F:I]列对应的行数据进行求和。
另外:如果后期有新增工作表的话,可以事先将1-12月的辅助区域和公式的范围都设置好。
点击加载图片
可能有的小伙伴们有这样的疑问?我直接复制后面的月份工作表中的数据粘贴到这个辅助区域不行吗?
非常好的一个问题!但是如果月份数据后期有变更,你是不是还要再复制一次二次三次呢?这里使用等于号(=)链接,就不会这么麻烦啦!
3、写在最后
今天我们分享了一个神奇的多表求和的方法。就是利用新建一个辅助表的方法来实现。
此种方法简单实用,但是仅限用于表结构完全相同的情况下。对于财务报表模版的汇总、税务报表模版汇总、人事、行政等标准模版的汇总大有用处!
另外,我们还扩展了对于表结构的行内容不完全相同的情况下,如何使用函数来解决。使用扩展知识中函数的方法即可以解决表结构完全相同,也可以解决表结构不同的情况。
小伙伴们在平时的工作中可以多学习,多尝试,多思考,总会有新的发现噢!
不过Excel中的小技巧,可远远不止我今天介绍的这些。
如果你Get了Excel思维,即便是只用一个快捷键,也能快速搞定大量数据👇
点击加载图片
联系客服