Excel给不规范的数据“排排坐”

技术宅

数据横着坐 分列来相助

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

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

数据竖着排 转换来变换

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

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

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

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

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

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

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

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

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