怎么让一列数据保留格式（系统导出的数据不规范）

在日常工作中，有时会遇到系统导出来的多个字段数据，只在同一列的情况。比如下图所示：

示例中，左边数据每四行为一条完整的记录，比如 2-5 行，6-9 行……我们需要将它转成右边的格式，才方便进行常规的统计分析。

解决这种数据一列转多列的问题，最开始我使用的是 VBA，但学了 PQ 之后，点点鼠标就可以完成啦！

PQ 鼠标操作法

❶ 打开数据文件，添加数据到 Power Query 编辑器。

具体操作步骤：

① 将鼠标定位在数据区域的任意单元格，单击【数据】选项卡；

② 选择【自表格/区域】，Excel 会自动扩展选区；

③ 如果弹出的对话框，勾选「表包含标题」，单击【确定】即可。

PS：【自表格/区域】在不同版本中，可能被称为【从表格】。

❷ 通过观察可以看到，交易数据列名和内容通过冒号分隔开，因此先按分隔符拆分列。

❸ 接下来是添加「索引列」，并以「交易数据.1」为依据，对「交易数据.2」进行透视。

具体操作步骤：

① 单击「添加列」—「索引列」，选择「从 0 开始」；

② 选中「交易数据.1」，单击「转换」—「透视列」，值列选择「交易数据.2」，高级选项选择「不要聚合」。

透视以后的数据变化很大。

虽然结果和我们想要的格式更进一步了，但是每条记录数据都被行列交错分布开来了。

这时我们需要利用「向上填充」和「筛选」进一步清洗。

❹ 选中「产品」、「金额」、「销售人」三列，向上填充已有数据。

具体操作步骤：

① 单击【产品】列，向右拖动下方的滚动条到末尾；

② 按住 【Shift】 键盘，再次单击末尾列的标题，这样就选中了最后三列；

③ 单击【转换】选项卡—【填充】，从下拉选项中选择【向上】。

完成填充以后数据表如下：

已经非常接近干净的数据了。

❺ 最后，将「机构列」的空值（null）筛选掉，并且删除「索引列」就可以得到想要的数据表。

具体操作步骤：

① 单击「机构」列，点击该列右上角的【筛选】按钮；

② 从弹出的窗口中取消勾选「null」 ；

③ 单击「索引列」，单击鼠标右键，选择【删除】。

点击「关闭并上载」，将数据上载到 Excel 中就可以啦！

公式 Plus 法

像这种有规律地把表格中的多个单元格分成一组；每组在不同行中，并且在列的方向按次序保存的情况。

也可以使用拉登老师介绍过的 P_INDEX 函数解决。

❶ 先使用常规的分列功能，将交易数据按「中文的冒号」分隔。

❷ 使用 P_INDEX 函数，输入相应参数获取【机构】列数据。

❸ 需要将公式往右边填充，可以使用 Column 函数实现动态引用。

最终使用到的公式为：

=P_INDEX($B$2,$B$6,$B$10,1,100,ROW(A1),COLUMN(A1)

总结一下

现在，我们来将两种方法对比一下~

鼠标操作法学会菜单功能就行了。主要记住以下几个要点：

❶ 按分隔符分列；

❷ 添加索引列作为透视列；

❸ 向上填充，注意「机构」列不填充；

❹ 筛选去除空行（null）。

公式 Plus 法的几个要点：

❶ 需要先下载「公式 Plus」插件，本文文末留言获取；

❷ 按分隔符拆分列；

❸ 使用 P_INDEX 自定义函数；

❹ 配合使用 Row 及 Column 函数实现动态引用。

今日内容分享就到这里，我们下期再见~

,