二维码 购物车
部落窝在线教育欢迎您!

杂乱无章的多列数据转成一列,看Excel老手是如何操作的?

 

作者:阿硕来源:部落窝教育发布时间:2021-10-25 09:33:40点击:2693

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

大家好,这里是部落窝教育。大家在日常工作中,会不会因为复杂的数据排成一列而头疼呢?在网络上搜索出来的课程大多是运用的循环引用,但是这个方法只适用于排列整齐的数据,如果是复杂的、排列不整齐的数据,又该如何操作呢?今天就让阿硕老师来教一下大家,如何用PQ来解决这个问题。


哈喽,大家好,我是阿硕。最近,有小伙伴咨询了这样一个问题:有一组数据,这些数据放置在多列之中,我想把这些数据汇总到一列,该如何做?

 

今天阿硕给大家分享一个在PQ中解决这个问题的方法。我们分两种情况来讲解,一种是数据不包含标题的情况,另一种是数据包含标题的情况。

 

一、数据不包含标题的情况

案例数据如下图所示。共有四列数据,各列中都是员工的姓名,想把它们汇总显示到同一列中。同时,在数据的排列顺序上,要先放置A列的数据,然后依次放置B列、C列、D列的数据。


 

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


 

我们取消勾选“表包含标题”(如已取消勾选则保持不变即可),然后点击“确定”,就可以进入PQ的操作界面,如下图所示。这时请大家稍微注意一下,PQ自动为每一列添加了列名,分别是“列1”、“列2”、“列3”、“列4”。

 

 

然后,我们依次点击“转换”-“转置”,可得到如下图所示的转置结果。由下图可以看到,原来在同一列中的数据,现在被转置到同一行里面了,也就是说,原本纵向放置的员工姓名,现在变成了横向放置。

 

 

依次点击“添加列”-“索引列”-“从0”,得到的结果如下图所示。可以看到,数据中增加了一列名叫“索引”的列,该列中的数据分别是0123

 

图形用户界面, 文本, 应用程序

描述已自动生成

 

单击选中“索引”这一列,此时该列变成浅绿色,然后依次点击“转换”-“逆透视列”-“逆透视其他列”,如下图所示。

 

图形用户界面, 应用程序

描述已自动生成

 

点击“逆透视其他列”之后,得到的结果如下图所示。可以看到,此时数据表中的数据变成了三列:第一列是“索引”列,其中的数据就是索引的值;第二列是“属性”列,其中的数据就是我们进入PQ时自动生成的数据标题;第三列是“值”列,其中的数据就是我们想要得到的将四列数据汇总成一列的结果。

 

 

由于“索引”列和“属性”列对于我们接下来的操作没有用处了,所以我们只需通过右键单击这两列的标题字段名称,然后在弹出的菜单中选择“删除”,进而将这两列删除即可。同时,我们对“值”列进行一下重命名——这个操作也很简单,只需双击“值”列的标题字段,然后将“值”改为“数据汇总”即可。经过这些处理,我们最终得到的数据如下图所示。

 

2

 

接下来,我们依次点击“主页”-“关闭并上载”-“关闭并上载”,就可以将数据上载至Excel中啦!具体的操作步骤,如下图所示。

 

手机屏幕的截图

描述已自动生成

 

最终,我们在Excel中得到的数据结果,如下图所示。


 

思路总结:数据转置-添加索引列-逆透视除索引列之外的其他列。

 

一、          数据包含标题的情况

本例中,我们还是沿用上一案例的基础数据,只不过为每一列的数据都加上了标题。如下图所示,A1B1C1D1为员工所在的部门,亦即为数据的标题。

 

 

在这里要和小伙伴们强调一下,在数据不包含标题的情况下,数据最终会被汇总成一列。但是如果数据包含标题的话,那么最终汇总出来的数据会有两列,其中一列用于标识组别(在本例中即为部门),另外一列则是对各列数据(除标题字段外)的汇总。最终的汇总出来的数据表,就是我们经常说的一个标准的一维表,如下图所示。


 

下面就让我们继续学习吧!

 

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


 

我们勾选“表包含标题”(如已取勾选则保持不变即可),然后点击“确定”,即可进入PQ的操作界面,如下图所示。

 

 

在上一案例中,由于数据不包含标题,所以我们可以直接进行转置操作。而在本例中,由于数据包含标题,所以我们在转置之前,要多进行一步操作,然后再对数据进行转置。这个操作就是将数据的标题降级为普通的数据。具体操作为:依次点击“主页”-“将第一行用作标题”-“将标题作为第一行”,如下图所示。


 

点击“将标题作为第一行”之后,得到的数据如下图所示。可以看到,列名自动变成了“Column1”、“Column2”、“Column3”、“Column4”,原有的标题字段(“市场部”、“营销部”、“财务部”、“人事部”)全部变成了普通数据。

 

表格

描述已自动生成

 

依次点击“转换”-“转置”,得到的结果如下图所示。可以看到,原来在同一列中的数据,现在被转置到同一行里面了。在这里,我们要特别注意一下,“市场部”、“营销部”、“财务部”、“人事部”这些标题字段,全部位于数据的第一列(即Column1)中。



 

在上一案例中,我们在做逆透视列的时候,是通过添加的索引列来做的。在本例中,有所区别:我们使用Column1(即部门)来做逆透视列操作,所以不用再额外添加索引列了。逆透视列的具体操作为:单击选中Column1列,此时该列变成浅绿色,然后依次点击“转换”-“逆透视列”-“逆透视其他列”,如下图所示。

 

 

点击“逆透视其他列”之后,得到的结果如下图所示。

 

 

接下来,我们再对数据稍做整理。我们右键单击“属性”字段,在弹出的菜单中选择“删除”,将“属性”列删除;分别双击数据标题“Column1”和“值”,对它们进行重命名,将二者分别命名为“部门”和“姓名”。进行这些操作后,得到的结果如下图所示。

 

 

可以看到,此时数据已经变成了两列,第一列是部门,第二列是姓名,这样的数据刚好是一个标准的一维表,这就比较符合我们使用Excel存储或处理数据的规范啦,有利于后续的使用。

 

好了,接下来,我们只需将数据上载至Excel中就OK了。我们依次点击“主页”-“关闭并上载”-“关闭并上载”完成上载,结果如下图所示。

 

 

思路总结:标题降级-数据转置-逆透视除标题列之外的其他列

 

好了,小伙伴们,今天的学习内容就是这些,你学会了吗?

 

编后语:

千里之行始于足下,学习需要积累,需要从此刻做起。数据的整理不是一时之功,要想熟练操作,需要一刻不停地保持一颗积极向上的心。今天的文章是将多项数据并做一列进行了归纳与总结,希望对您有所帮助。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

将excel中的数据按照指定数量进行重复,非得用VBA才能实现吗?

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招,速收藏

Power Query的数据替换技巧比Excel函数更万能!

版权申明:

本文作者阿硕;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。