首页
学习
活动
专区
工具
TVP
发布
精选内容/技术社群/优惠产品,尽在小程序
立即前往

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生产计划,关注古哥计划!

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20221222A00QKN00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码

添加站长 进交流群

领取专属 10元无门槛券

私享最新 技术干货

扫码加入开发者社群
领券