多张工作表自动合并的方法(合并表格怎么合并)

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

工作中遇到了两张或多张标题一样的工作表需要合并成一个工作表,如果只是简单的表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的结果;

多张工作表自动合并的方法(合并表格怎么合并)

本站部分文章来自网络或用户投稿,如无特殊说明或标注,均为本站原创发布。涉及资源下载的,本站旨在共享仅供大家学习与参考,如您想商用请获取官网版权,如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。
科技百科

mac连接iphone热点为啥上不去网(为什么电脑连不上苹果手机热点)

2023-3-21 1:41:18

科技百科

W10进入安全模式的6种方法(win10开机如何进入安全模式)

2023-3-21 1:42:02

搜索