别再迷信VLOOKUP了！用这个技巧整理不规范数据源，领导站起来鼓掌！

    ★

    编按

    ★

    Hello各位小伙伴们~说到数据的行列转置，很多小伙伴可能会想到复制粘贴、Transpose函数或者VLOOKUP函数等方法。但是如果需要处理今天文中这样有点杂乱的数据，这些方法就有些吃力不讨好了。搞定繁杂的数据，当然还是Power Query（后文简称PQ）更加给力，今天我们一起来学习一下如何用PQ将奇数行和偶数行的数据分别放置到不同的列中。

    哈喽，大家好，欢迎来到部落窝教育！我是阿硕。最近，有小伙伴问了一个这样的问题：有一组数据，我想将这组数据，每隔一行转置到新的一列中，即将奇数行和偶数行分别放置在不同的两列中，有什么好的办法吗？

    我们先来看一下数据，如下图所示：

    A列是驾驶员的ID；

    B列是驾驶员的性别；

    C列是属性列，主要是用来标识D列中数据的类别，该列中的数据分别是收费站和时间（收费站和时间后面带有序号）；

    D列是数据列，该列中的数据是某ID通过的收费站的具体名称和该ID通过该收费站的具体时间。

    可以看到，在D列中，除第一行是数据的标识字段之外，其他的数据都是隔行交替排列的。收费站的名称都在偶数行，通过收费站的时间都在奇数行。这个小伙伴想得到的最终效果，就是将收费站和时间各自放在一列中，如下图所示。

    关注咱们公众号的小伙伴，对于这种将数据进行由一维转置成二维的问题，应该都已经比较熟悉了。用PQ中的透视列功能来做，是最方便、最快捷的。

    具体的操作方法，我们现在就安排上！

    【解决问题的思路】

    首先，要整理属性列的数据，得到透视数据的字段；

    然后，添加索引列作为辅助列，用于控制数据循环的次数；

    最后，进行透视列操作。

    具体操作步骤

    01

    整理属性列的数据，得到透视数据的字段

    首先，我们点击数据区域内的任意一个单元格，如D9，然后依次点击“数据”－“自表格/区域”，弹出“创建表”对话框，如下图所示。

    点击“确定”之后，就进入了PQ编辑器，也就是PQ的操作界面，如下图所示。

    单击选中属性列（请注意，此时该列变为浅绿色），然后依次点击“转换”－“拆分列”－“按照从非数字到数字的转换”，如下图所示。

    点击“按照从非数字到数字的转换”后，原来的属性列消失，在PQ中新增了两列，分别是属性.1和属性.2。

    其中，属性.1中的内容就变成了不带序号的“收费站”或“时间”，这一列就是我们想要的透视数据的列。而收费站或者时间的序号则被拆分到了属性.2这一列中，如下图所示。

    由于属性.2这一列对后续操作没有用处，所以我们单击鼠标右键，点击“删除”，将这一列删除即可。删除之后得到的结果如下图所示。

    02

    添加索引列作为辅助列，用于控制数据循环的次数

    依次点击“添加列”－“索引列”－“从0”，就可以添加一个索引列，如下图所示。

    得到的索引列，如下图所示。在这里，和大家解释一下，所谓“从0”开始的索引列，就是一列列名为“索引”，数据内容从0开始、以1为单位递增的整数序列。

    接下来，我们还需要对索引列进行一下加工。单击选中索引列，然后依次点击“转换”－“标准”－“用整数除”，如下图所示。

    点击“用整数除”后，弹出“用整数除”对话框。在“值”下方的输入框中，输入数字“2”，然后点击“确定”，如下图所示。

    点击“确定”后，请大家注意观察，索引列中的数据发生了变化，由原来的递增整数序列（0、1、2、3、……），变成了0、0、1、1、2、2、3、3……这样的重复数据，如下图所示。

    有的小伙伴们可能会问，刚才我们添加索引列，然后用整数除（即用2去除），如果不进行这个操作，可以吗？

    答案是不可以！这是必不可少的一步。在本例中，透视列的关键一步就在于这个索引列。

    这是因为，在我们的原始数据中，每两行对应的是同一个ID的通行记录，也就是说，某一个ID的通行信息保存在两行之中。因此，我们将递增的索引列除以2，从而得到0、0、1、1、2、2、3、3……这样的重复2次并且递增的数据（注：如果每一个ID对应三行的数据内容，则除以3，其余以此类推）。

    正是有了这样的索引列，在我们后续的透视列操作中，才保证每一个ID的每一次出行记录只对应两个信息，即一次出行对应一个收费站和一个时间。如果没有这样的一个索引列，那么在后续的透视列操作中，就会出现错误。

    03

    进行透视列操作

    单击选中属性.1这一列，然后依次点击“转换”－“透视列”，如下图所示。这里有一点一定要注意，因为我们是想要将收费站和时间这两个字段转置到两列中，所以在进行透视列操作之前，要先选中包含这两个字段的列，也就是属性.1列。

    点击“透视列”后，弹出“透视列”对话框，如下图所示。

    我们点击“值列”下方的下拉菜单，将字段由ID改为数据，然后点击展开“高级选项”，在“聚合函数值”下方的下拉菜单中，选择“不要聚合”，如下图所示。

    点击“确定”之后，就是见证奇迹的时刻了！此时，我们得到的数据如下图所示。

    可以看到，收费站和时间这两个字段，已经放在了新的两列中，两列中对应的内容也是收费站名称或者通行时间。

    另外，我们可以观察一下数据的行数，除表头字段之外，现在一共是10行数据（原始数据为20行），并且，每个ID的ID号、性别、收费站、时间等通行记录是放置在一行中的。

    此时，索引列对于后续的操作没有用处，我们可以点击右键，选择“删除”，将它删除，删除之后的数据如下图所示。

    好了，到现在为止，数据已经符合我们的要求了。接下来我们要做的，就是将数据上载回Excel中了。具体的操作步骤为：依次点击“主页”－“关闭并上载”－“关闭并上载”，即可，如下图所示。

    上载完成之后，在Excel中得到的数据就是我们在本文开头所展示的那个效果，如下图所示。

    小伙伴们，你们学会了吗？

    今日互动话题

    在评论区留下你的足迹叭~

    数据行列转置你会哪种方法？

赞 (0)

相关推荐