新知一下
海量新知
6 0 8 2 1 8 8

在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

Excel情报局 | Excel办公技能分享达人 2021/11/30 08:57

前言|职场实例

今天接受了一个小伙伴的咨询:

对方想要将一个Excel二维格式的表格转换成为一维格式的表格。

二维表转一维表的作用:

转换成为一维表格式后,我们就能高效直接的对数据源进行各种数据统计与数据透视汇总了。

如下方:

①左图是一个二维格式的表格:横向的部门和姓名与纵向的月份的交叉位置代表对应的销售量数据。

②右图是一个一维格式的表格:横向的每一行代表一个部门、姓名、月份及对应的销售量数据。

我们想要快速的将左表转换为右表的格式:

新知达人, 在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

错误方法|惯性思维

一般我们遇到这样的问题,首先想到的是:直接在数据源上插入数据透视表进行格式转换不就可以了。

但是通过下面的几个动图操作演示:

我们对数据源插入数据透视表后,将字段拖动至各个区域间排放后,发现都不能正确的得到结果。

错误格式①

新知达人, 在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

错误格式②

新知达人, 在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

错误格式③

新知达人, 在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

正确操作|Power Query

说实话,像这种案例,一定要交给Power Query来处理。大家不要一听到Power Query就望而却步,其实利用Power Query的基础功能来处理这个问题,真是太简单了,几秒钟就能解决问题。Power Query编辑器已经在Office Excel2016版本以及更高版本中作为内置的基础功能了,拿来就可以用。

首先我们鼠标点击数据源的任意一个单元格后,在【数据】选项卡下点击【从表格】按钮,在弹出的【创建表】的界面,默认勾选“表包含标题”,点击【确定】后,将数据加载至Power Query编辑器中。

新知达人, 在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

点击选中“部门”列,在【转换】选项卡下,点击【向下填充】功能,将“部门”列的空白单元格数据填充完整。

新知达人, 在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

然后Ctrl键同时选中“部门”和“姓名”列,在【转换】选项卡下,点击【逆透视其他列】命令按钮。即可快速的将二维表格式的数据转换为一维表格式的数据。

新知达人, 在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

更改一下列标题,双击列标题进入可编辑状态,依次将后两列的列标题命名为“月份”和“销售量”即可。

新知达人, 在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

最后将Power Query编辑器中整理好的数据加载放到Excel表格中即可。

点击【主页】-【关闭并上载至】命令,在弹出的【加载到】界面,选择加载到【现有工作表】,选择一个放置表格的起始位置,最后点击【加载】按钮,即可加载完成。

新知达人, 在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

最后转换好的数据,效果图如下图所示:

新知达人, 在职场中学会Excel数据清洗,解决那些汇总数据的格式障碍!

阅读完文章之后,希望小伙伴们在文章底部帮助小编 [点赞]+ 点亮 [在看]; 如果觉得内容有价值欢迎 [ 分享] 给你的同事朋友 ;小编也和大家一样,正在不断的学习与积累中,坚持持续分享的道路上很艰辛,需要有你们的鼓励与支持! 别逃避问题,否则它只会越积越多;每天进步一点点,你就胜过了大多数人。

更多“Excel”相关内容

更多“Excel”相关内容

新知精选

更多新知精选