45 多张工作表自动合并

工作中遇到了两张或多张标题一样的工作表需要合并成一个工作表，如果只是简单的表1加表2的内容累加，就可以用直接法，复制粘贴就行了，但此时需要的就是动态的，表1和表2只要有内容增加，合并的表也自动累加，这样多表合并，多人的资料就可自动汇总；

这样的场景还是经常遇到的，如不同车间的报表，不同计划员的排程表，不同销售的销售订单等；合并起来的优势也是非常多，可以统一分析，汇总，特别是团队协作人员比较多的时候；

合并的方法最佳还是用VBA，考虑的VBA的学习难度，这里更改为函数解决，这样写好了就可以建模，后续只需要更新源表就可以自动汇总了；

01 注意事项：

多表的合并对需要合并的报表是需要一定的要求的，特别注意以下几点：

1. 标题一样：这是重点，如果标题不一样，合并的效果就会错位；

2. 行列锁定：当确定了标题后，不要轻易更改，任意一张表的数据都不要轻易更改位置，如确定需要更改位置，要保证所需的合并的所有报表都需要同步更改；

3. 超级表：如用超级表（Ctrl+T）的功能来实现的动态引用，则需要每个表都需要建立“超级表”，并且标题行不能用公式了；

4. 版本支持：这里用的是OFFICE 365, 如用其它版本的话，函数会比较长；

02 超级表的方法：

优点：转成表后，根据表的性质，可以实现动态更新；不需要额外判断每个表的更新内容；

缺点：每张表都需要转成超级表，而且标题行支持公式；

操作步骤：

第1步：需要合并的表选中内容按下Ctrl+T创建表确定；这里用表1和表2替代，当然有多张表，表3也是一样；创建后选择表数据出现表设计后，代表创建成功；

第2步：新建立一个合并表复制标题录入函数完成

G3=VSTACK(表1[工单],表2[工单])

H3=VSTACK(表1[数量],表2[数量]),这个公式可以用G3向右填充公式得到，多列数据也是一样的；

03 纯函数的方法：

优点：写一次函数，后续自动更新；

缺点：对函数需要一定的基础；

思路：每张表不确定录入数据的行数，用COUNTA统计非空单格的数量,用这个数量作为OFFSET的第四参数的行高，配合VSTACK合并，就可以实现动态引用了；

操作步骤：

第1步：新建一个合并表，在A1辅助单元格录入表1的统计行数的公式=COUNTA('表1'!A:A)-1，公式是统计表1A列的非空单元格数量，减去1代表，第一行为标题，无需统计；返回结果5，代表，表1有数据的范围为5行；

第2步：在合并表的B1录入公式=COUNTA('表1'!1:1)，统计表1的标题的数量，返回结果2，代表，标题只占用两列；根据上面的方法把表2的也一起统计出来；（结果是3，和2 ）

第4步：在合并表的辅助单元格录入公式：

=OFFSET('表1'!$A$1,1,,A1,B1)，把表1的数据引用过来；

=OFFSET('表2'!$A$1,1,,A2,B2)，把表2的数据引用过来；

这里注意表的切换，和OFFSET第1参数的锁定方式；

第5步：在合并表的单元格录入合并完成后的公式：

=VSTACK(OFFSET('表1'!$A$1,1,,COUNTA('表1'!A:A)-1,COUNTA('表1'!1:1)),OFFSET('表2'!$A$1,1,,COUNTA('表2'!A:A)-1,COUNTA('表2'!1:1)))，就完成了多表的动态合并

如果有表3、表4、可以重复上面步骤即可；

04 批量的方法：

上面的方法对于需要合并的表不多的情况下，还可以一个一个写OFFSET函数，如果需要合并的报表太多，几十个，就非常痛苦了，我们需要用批量的方法；

优点：适合超过多个工作表需要合并；

缺点：还需要再次中转一下；手动判断预计行数，再次筛选

思路：在VSTACK参数中用多表引用，再用FILTER对结果筛选不为0的；

第1步：在合并表提前复制好标题，录入批量引用的函数，对行数进行预估，范围可以选择大一点，这里选择10000行；注意多张表的行数不能超过最大行数，100万行左右，不过一般情况下也不会达到100万行，这里6张表，也就是10000*6，代表每张表的内容如果超过10000行外的数据就不会合并了；

=VSTACK('表1:表6'!A2:B10000)

第2步：在边上录入函数 =FILTER(A:B,A:A0)，就完成了多表动态合并，公式的意思是，筛选不等于0的结果；

我是古哥：

从事制造行业18年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！