您当前的位置:网站首页>小刀娱乐网>合并表格怎么合并(多张工作表自动合并的方法)

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

2023年03月21日 投稿作者:admin 围观人数:200
合并表格怎么合并(多张工作表自动合并的方法)

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

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

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

合并表格怎么合并(多张工作表自动合并的方法)  小刀娱乐网  第1张

01 注意事项:

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

1. 标题一样:这是重点,如果标题不一样,合并的效果就会错位;

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

3. 超级表:如用超级表(Ctrl+T)的功能来实现的动态引用,则需要每个表都需要建立“超级表”,并且标题行不能用公式了;

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

02 超级表的方法:

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

缺点:每张表都需要转成超级表,而且标题行支持公式;

操作步骤:

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

合并表格怎么合并(多张工作表自动合并的方法)  小刀娱乐网  第2张

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

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

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

合并表格怎么合并(多张工作表自动合并的方法)  小刀娱乐网  第3张

03 纯函数的方法:

优点:写一次函数,后续自动更新;

缺点:对函数需要一定的基础;

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

操作步骤:

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

合并表格怎么合并(多张工作表自动合并的方法)  小刀娱乐网  第4张

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

合并表格怎么合并(多张工作表自动合并的方法)  小刀娱乐网  第5张

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

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

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

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

合并表格怎么合并(多张工作表自动合并的方法)  小刀娱乐网  第6张

第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、可以重复上面步骤即可;

合并表格怎么合并(多张工作表自动合并的方法)  小刀娱乐网  第7张

04 批量的方法:

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

优点:适合超过多个工作表需要合并;

缺点:还需要再次中转一下;手动判断预计行数,再次筛选

思路:在VSTACK参数中用多表引用,再用FILTER对结果筛选不为0的;

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

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

合并表格怎么合并(多张工作表自动合并的方法)  小刀娱乐网  第8张

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

合并表格怎么合并(多张工作表自动合并的方法)  小刀娱乐网  第9张

标签

合并,表格,工作表,方法
版权说明
免责声明:本文文章内容由技术导航发布,但不代表本站的观点和立场,具体内容可自行甄别.