前段时间收到的问题当中,大家问的最多的就是文本的拆分、提取、合并之类的问题。
另外就是,工作表合并、工作簿合并问题。
今天要给大家讲的,就是这样一个「工作表合并」的问题。
01
问题描述
这个同学是这样问的:
打开表格之后是这样的:
Q:怎么把这些数据,快速合并起来变成下面的样子呢?
非常简单嘛!
01
常规做法
来跟着我一起做:
▋ 复制数据
【Ctrl+C】,复制第 1 个表格的数据。
▋ 选择性粘贴
注意了,关键的地方就在粘贴这一步!
❶ 在目标单元格位置,右键,选择【选择性粘贴】。
❷ 勾选「跳过空单元」。
粘贴后效果如下:
是不是你想要的结果?
「跳过空单元」香不香?
▋ 反复操作
重复前面的操作,再把其他工作表的数据粘贴过去,就行了。
就这么简单的问题,也来问我?
有没有跟着我一起膨胀起来?
错,完全错误。
用选择性粘贴的方法解决这个问题,会有下面几个缺点:
❶ 效率非常低。
一个工作表,一个工作表的复制粘贴,如果是二三十个表,还是没有提高工作效率。
❷ 数据被覆盖风险。
如果目标位置已经有了数据,粘贴的时候会把已有的数据覆盖掉。
(黄色的 F,把部分绿色 T 覆盖掉了。)
接下来,我们来看看,10 年 Excel 老司机的正确姿势。
03
高级做法
正确的姿势,是使用 Power Query,自动合并工作表,而且可以保持表格一键更新。
很多人 400 多块的 Excel 正版都买了,却只有 3 毛钱的功能;
Power Query 就在你的 Excel 里,搞不明白你为什么不用。
跟着我一起操作:
▋ 加载工作簿文件
首先,把当前的表格,加载到 Power query 中,等待合并工作表。
▋ 工作表合并
❶ 保留 Data 列。
首先你看到的是工作表的清单,所有的数据都保存在「Data」这一列,所以先删除其他列。
❷ 展开 Data 列。
然后点击右上角,展开「Data」的内容,数据就合并好了。
❸ 筛选标题。
每个表里都有一行标题,所以通过筛选的方式,把其他标题都筛选掉。
这样,多个工作表的数据,就合并完成了。
▋ 数据逆透视
现在的表格,是一个二维表,不方便处理,需要使用逆透视功能,转成一维表。
先别管什么是二维表,先跟着做,做完就明白了。
在「转换」选项卡里,点击「逆透视」转变表格,再修改一下标题的名称。
到这里,基础的数据就处理好了,然后我们再从这里出发,输出需要的格式。
▋ 透视列
需求的结果,本质上,就是对数据进行透视表操作。
不过统计的不是个数,而是把所有的文本都合并起来,放在单元格里。
这个操作在 Power Query 里非常简单。
在「转换」选项卡里,点击「透视列」,聚合方式选择「不聚合」即可。
不过这个时候,统计出来的会有 ERROR,因为个别单元格里会有多个符合条件的值。
对 PQ 的公式稍微修改一下就可以了,添加聚合的函数 Text.Combine,如下:
公式如下:
= Table.Pivot(重命名的列 1, List.Distinct(重命名的列 1[日期]), "日期", "计划", each Text.Combine(_,","))
然后就得到了需要的结果。
▋ 关闭并上载
数据处理好之后,还是要返回到 Excel 里,再交给领导。
点击左上角的「关闭并上载」,把数据加载到工作表里,就可以了。
最后结果如下:
快,快,快!
快夸我,我要膨胀了!
04
总结一下
怎么样,这么操作秀不秀?
想用好 Power Query,首先要知道它能为我们做什么,总结一下今天的操作。
使用 Power Query 可以做这些事情:
❶ 多个工作表合并;
❷ 二维表转一维表;
❸ 透视列,同时保留文本内容。
下次遇到类似的需求,记得用 Power Query,别再闷头想公式啦~
除了 Power Query,还有哪些插件可以实现多表合并?(多选)
- 方方格子
- 易用宝
- 慧办公
- 其他,留言区见
私信回复关键词【2020】,获取100+套高颜值图表模板!