多表汇总如何在增删表后自动更新?
作者:小窝来源:部落窝教育发布时间:2023-12-08 17:42:40点击:846
增删表格后跨多表汇总能自动更新吗?可以!不同的表格形式采用的方法不同。结构和排序相同的表,可以增加一个空表后再进行汇总;项目排序不同的表可以借用宏表函数自动获取工作表名称进行汇总。
有伙伴问:跨多表汇总在增删表后汇总结果能自动更新吗?
这个问题小窝以前没有遇到过,多方测试,找到以下方法。
分成两种情况介绍。
第一种:各表格属性和排序一致
对于这种表,直接输入=SUM('*'!B2)即可汇总。输入后公式自动变成=SUM(周1:周5!B2)。
“'*'!”表示当前工作簿中除当前工作表外的其他所有工作表。
这种结构的多表汇总,只要不是在当前最后一张工作表,如此处的“周5”后增加表,不管增删都可以自动随着工作表的增加跟新数据。如下。
利用这个特性,我们可以汇总前新建一个空的“终表”,然后再汇总。后续需要增删表的时候,都在“终表”前操作则可以保持自动更新。
第2种:表格结构相同但排序不一致
这类表汇总通常要采用SUMPRODUCT+SUMIF+INDIRECT进行嵌套。
=SUMPRODUCT(SUMIF(INDIRECT("周"&ROW($1:$5)&"!a:a"),A2,INDIRECT("周"&ROW($1:$5)&"!b:b")))
这种汇总,如果删除每个工作表,公式会出错;
如果新增工作表,公式也不会更新:
那怎么让汇总能跟随工作表的增删自动更新呢?
需要用一个宏表函数来帮我们自动获取各个工作表的名称。具体操作如下:
Step 01 首先让汇总表与各分表的位置错开。
Step 02 定义名称,用宏表函数get.workbook获取各工作表的名称。此处名称取名为“name”,公式=get.workbook(1)。
Step 03 修改汇总公式,将原来的“"周"&ROW($1:$5)”改成“name”,如下。
=SUMPRODUCT(SUMIF(INDIRECT(name&"!a:a"),C2,INDIRECT(name&"!b:b")))
现在不管是增加还是删除工作表,汇总结果自动更新。如下。
OK,关于多表汇总在增删表后的自动更新就介绍到这。
亲,别忘了点赞分享。
本文配套的练习课件请添加客服微信buluowojiaoyu索取。
做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
版权申明:
本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。