Excel 中多工作表求和、表格自动更新案例分享 | 您所在的位置:网站首页 › excel超链接求和 › Excel 中多工作表求和、表格自动更新案例分享 |
原文标题:《多个工作表求和,你还在按 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 月只有南京和天津的数据。 此时我们可以用 Power Query 多表合并结合透视表的方法来实现。 也可以仅使用函数的方法来实现。 因为大部分小伙伴可能无法使用 Power Query 这个功能。所以我们这里就分享函数方法来解决此问题。 公式如下: =SUM(IFERROR(SUMIF(INDIRECT(ROW($1:$12)&"月!A:A"),A2,INDIRECT(ROW($1:$12)&"月!B:B")),0))公式解析: 此公式大体由三个部分组成: ① SUMIF (INDIRECT (ROW ($1:$12)&"月!A:A"),A2,INDIRECT (ROW ($1:$12)&"月!B:B")) ② IFERROR( ① , 0 ) ③ SUM ( ② ) 用这个函数的方法可以一步到位。 当然还是有一定难度。 PS:在低版中需要按三键【Ctrl+Shift+Enter】结束公式。 另外,还有一种利用辅助区域 + 函数的方法,也可以实现。这里也一并介绍给大家。如下图: 其中:蓝色区域部分手动输入,黄色区域我们用等于号(=)分别引用每张工作表从【A2】单元格开始的内容。 大体意思就是在汇总表中建立一个辅助区域,然后将各个工作表中的数据都引用过来。 最后使用 SUMIF 函数对这个辅助区域进行求和即可。 公式如下: =SUMIF(E:H,A2,F:I)在【E:H】列中查找【A2】单元格的内容,并对【F:I】列对应的行数据进行求和。 另外:如果后期有新增工作表的话,可以事先将 1-12 月的辅助区域和公式的范围都设置好。 可能有的小伙伴们有这样的疑问?我直接复制后面的月份工作表中的数据粘贴到这个辅助区域不行吗? 非常好的一个问题!但是如果月份数据后期有变更,你是不是还要再复制一次二次三次呢?这里使用等于号(=)链接,就不会这么麻烦啦! 3、写在最后今天我们分享了一个神奇的多表求和的方法。就是利用新建一个辅助表的方法来实现。 此种方法简单实用,但是仅限用于表结构完全相同的情况下。对于财务报表模版的汇总、税务报表模版汇总、人事、行政等标准模版的汇总大有用处! 另外,我们还扩展了对于表结构的行内容不完全相同的情况下,如何使用函数来解决。使用扩展知识中函数的方法即可以解决表结构完全相同,也可以解决表结构不同的情况。 小伙伴们在平时的工作中可以多学习,多尝试,多思考,总会有新的发现噢! 不过 Excel 中的小技巧,可远远不止我今天介绍的这些。 如果你 Get 了 Excel 思维,即便是只用一个快捷键,也能快速搞定大量数据👇 本文来自微信公众号:秋叶 Excel (ID:excel100),作者:明镜在心 广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。 |
CopyRight 2018-2019 实验室设备网 版权所有 |