在Excel中用PowerQuery拆分单元格数据的两种方法比较

这个例子来自知乎。在解释PowerQuery的强大功能时，作者使用了一个反向透视处理的示例，其中数据都在同一个单元格中，如下所示：

必须提取颜色，以便可以将数据格式化为标准化数据表格式。

但我觉得作者的方法（@Excel三十六计）不够简洁，或者用常见知乎的话来说，不够优雅，所以我想到了这篇文章。

01知乎作者的解决方案：通过符号反向透视

知乎作者采用的方法是将数据源引入PowerQuery，使用符号对列进行排序，然后使用反向主元法对数据进行归一化。

为了省去大家的麻烦，我又重复了一遍处理步骤，做了一个小小的优化。

1介绍数据来源。

在本例中，我们直接将当前工作表中的数据源引入到当前Excel文件中。

打开原始数据，确保鼠标位于数据区域，单击Excel2016及更高版本菜单栏中的【数据】选项卡（其他较低版本需要单独下载安装，并启用PowerQuery插件），选择【来自表格/区域】，在弹出窗口中选择【表格包含标题】，然后确认：

知乎的帖子里多了一个CTRLT将数据区转换为Excel表格格式，其实可以省略。

这将带您进入PowerQuery查询编辑页面。

2按符号排序。

选择[颜色]列，然后单击PowerQuery查询编辑器窗口[主页]选项卡上的[拆分列]命令，并选择[按分隔符]：

此时，您将进入列分隔设置页面。我们可以直接点击【确定】，因为PowerQuery会自动识别用于列分隔的逗号，也知道要拆分多少列。

当数据使用多个分隔符时，它会变得更加复杂，这将在将来的另一篇文章中讨论。

3取消透视其他列

由于我们不确定上一步中使用分隔符拆分了多少列，因此我们选择[反向透视其他列]。

选择【产品名称】、【服装】、【数量】三列，然后单击菜单栏中的【转换】选项卡，在【透视列】中选择【反向透视其他列】。

完成后，它应该看起来像这样：

4删除不需要的列并重命名[Value]列。

我们发现在上一步中取消透视其他列后，不再需要[Attribute]列，因此将其删除。然后重命名[值]列。

这样整个操作就完成了，剩下的只是数据加载，这里略过。

可以看到整个操作需要4步。看起来很多，但如果你熟练的话，每分钟几十秒就足够了。

但是，我觉得这不是处理这个问题的最优雅的方式。

02优雅的方式：通过分隔符划分行

处理这种同一单元格中的多个值被分隔符分隔的情况最优雅的方法是直接根据分隔符将它们拆分成行。

下面简要描述这些步骤，相同的步骤将被跳过。

1数据源介绍与之前相同，略过。

2根据分隔符划分行。这是一个根本性的步骤。类似的部分将被跳过，仅提及不同的部分。

在“按分隔符分列”设置窗口中，单击[高级选项]，选择[行]，然后单击[确定]：

这一切都完成了，不需要取消透视，不需要删除不必要的行，不需要重命名，只需加载。

03总结

那么最后这个[更优雅]的方法是最好的选择吗？不必要。

对于我们每个人来说，解决同一问题往往有多种方法。最快的方法不是最优雅、最简单的方法，而是我们最熟悉的方法。

因此，如果您不知道如何根据分隔符将数据拆分为行，那么拆分为列并使用unpivot是您最快、最有效的方法。

不过，读完本文后，您就会知道第二种方法，它更快、更简洁。从现在开始，最快的方法将不再是拆分列然后取消透视。

这就是为什么我们需要学习新知识，掌握新的解决问题的方法。

只要你保持开放的心态，将来你一定会遇到其他更快、更简单的方法来解决此类问题。