【原】Excel多表汇总在增删表后结果能自动更新的方法

文 前 推 荐   

表格结构不同的跨多表汇总

表格构成相同的跨多表汇总

INDIRECT函数入门

XOR异或逻辑函数妙用

编按：

增删表格后跨多表汇总能自动更新吗？可以！不同的表格形式采用的方法不同。结构和排序相同的表，可以增加一个空表后再进行汇总；项目排序不同的表可以借用宏表函数自动获取工作表名称进行汇总。

有伙伴问：跨多表汇总在增删表后汇总结果能自动更新吗？

这个问题小窝以前没有遇到过，多方测试，找到以下方法。

分成两种情况介绍。

第一种：各表格属性和排序一致

对于这种表，直接输入=SUM('*'!B2)即可汇总。输入后公式自动变成=SUM(周1:周5!B2)。

“'*'!”表示当前工作簿中除当前工作表外的其他所有工作表。

这种结构的多表汇总，只要不是在当前最后一张工作表，如此处的“周5”后增加表，不管增删都可以自动随着工作表的增加跟新数据。如下。

利用这个特性，我们可以汇总前新建一个空的“终表”，然后再汇总。后续需要增删表的时候，都在“终表”前操作则可以保持自动更新。

第二种：表格结构相同但排序不一致

这类表汇总通常要采用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，关于多表汇总在增删表后的自动更新就介绍到这。