办公小技巧:Excel给不规范的数据“排排坐”

CFan 电脑爱好者 2018-12-10 09:04应用 标签:排排 技巧 办公 数据

日常工作中在录入原始数据的时候,总是有很多新手员工不按规范录入数据,这样导致后续需要对数据进行归类整理、检索的时候就非常不便。比如人事经常将新分配员工都按每个部门排列在一起,这样在后续员工考勤、工资表制作时带来很多不便(图1)。下面就给大家介绍一种方法,可以快速将不规范的数据整理得井井有条。

201806bgfsj01

图1 不规范数据

数据横着坐,分列来相助

对于类似数据之间使用的是统一分割符号,如果只是将数据按照行的方式排列,那么直接利用Excel的分列功能进行整理即可。

选中B列需要分类的数据,点击“数据/分列”,选择“其他”分割符号,选择“顿号”(如果分列的数据包含多个分隔符如逗号,冒号,可以使用查找替换进行分隔符统一后再用分列),这样即可将每个名字分列在不同单元格里(图2)。

201806bgfsj02

图2 使用分列整理数据

数据竖着排,转换来变换

上述只是将数据横着排列,而很多操作需要将人名数据竖着排列,比如制作工资单时的排列,需要每个员工一行,按照各自部门进行纵向排列。如果需要将横排的数据变换为竖排,那么可以借助Excel 2016集成查询编辑器的“转换”工具实现。定位到A1,点击“数据→从表格”,在弹出的窗口选择需要排列数据的A1:B4区域数据(图3)。

201806bgfsj03

图3 选择数据

点击“确定”,接着在打开的查询编辑器窗口点击“转换“选项卡下,依次单击“拆分列→按分割符”,然后选择“分隔符/自定义”,输入“顿号”,使用顿号将数据分列,具体操作和上述分列操作类似(图4)。

小提示:

这里使用分列和在工作表中使用分列略有不同。如果需要分列的右侧列中还有其他的内容,这里操作会自动扩展插入新的列,右侧已有数据列自动后延。而在工作中使用分列,右侧有数据时则会被覆盖掉。因此在需要对数据进行分列操作,如果原始数据中有较多列的内容(如B列是需要分列内容,C列、D列仍然有其他数据),为了不误删原始数据,强烈建议使用查询编辑器的分列进行操作。

201806bgfsj04

图4 分列数据

完成数据的分列后,此时B列的数据会自动被分列。继续按住Ctrl键依次选中已经完成的分列数据,点击“转换→逆透视列→逆透视列”,这样被选择的列会被自动转化为行的显示(图5)。

201806bgfsj05

图5 逆透视列操作

完成逆透视列操作后,原来数据会新增一列“属性“,按提示选中增加的属性列将其删除,这样就完成行列的转换了(图6)。

201806bgfsj06

图6 完成行列的转换

最后点击“文件→关闭并上载”,这样转换后的数据会自动添加到一个新的工作表,所有人名按照列的方式进行排列,这样很方便对人员进行考勤、工资表的制作带来便利(图7)。

201806bgfsj07

图7 完成行列的转换

从上述操作可以看到,借助查询编辑器的“逆透视列”可以很方便进行行列的转换。借助同样的方法我们可以轻松实现数据的转换。比如很多公司的销售报表都是二维报表格式,但是使用数据透视表来分析销售数据的时候,我们却要求数据源为一维表格的形式(图8)。

201806bgfsj08

图8 二维转一维

对于类似二维表格转换为一维表格,同样可以使用“逆透视列”的方法。同上定位到A1,全选需要转换的数据选择点击“数据→从表格”,打开查询编辑器窗口。按住Ctrl键选择“销售区域”和“城市”列,点击“转换→逆透视列→逆透视其他列”,对销售数据列进行逆透视操作(图9)。

201806bgfsj09

图9 逆透视其他列

这样数据列的内容会自动由原来的行变为列,同时在A、B列会自动填充对应的销售区域和城市的数据内容(图10)。

201806bgfsj10

图10 逆透视其他列后的效果

其他操作同上,保存并上载后,我们就将原来二维表格转换为一维表格的内容了。这样在使用透视表分析数据的时候就非常方便了,比如现在需要比较华东、华北城市的三季度的销售数据对比,原来的二维表格因为每个季度数据是并列排序不好透视选择,但是转换为一维表格后进行简单筛选显示即可(图11)。

201806bgfsj11

图11 转换后的透视图