excel 小技巧办公，Excel给不规范的数据

excel 小技巧办公，Excel给不规范的数据图3 选择数据上述只是将数据横着排列，而很多操作需要将人名数据竖着排列，比如制作工资单时的排列，需要每个员工一行，按照各自部门进行纵向排列。如果需要将横排的数据变换为竖排，那么可以借助Excel 2016集成查询编辑器的“转换”工具实现。定位到A1，点击“数据→从表格”，在弹出的窗口选择需要排列数据的A1:B4区域数据（图3）。选中B列需要分类的数据，点击“数据/分列”，选择“其他”分割符号，选择“顿号”（如果分列的数据包含多个分隔符如逗号，冒号，可以使用查找替换进行分隔符统一后再用分列），这样即可将每个名字分列在不同单元格里（图2）。图2 使用分列整理数据数据竖着排，转换来变换

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

图1 不规范数据

数据横着坐，分列来相助

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

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

图2 使用分列整理数据

数据竖着排，转换来变换

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

图3 选择数据

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

小提示：

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

图4 分列数据

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

图5 逆透视列操作

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

图6 完成行列的转换

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

图7 完成行列的转换

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

图8 二维转一维

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

图9 逆透视其他列

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

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

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