Excel:三步搞定二维表转一维表

注:本文操作工具为excel365

 

省略二维表与一维表的介绍,直接进入正题,如何将excel二维表转化为一维表?

 

原始二维表

 

 

 

在数据管理中,通常运用一维表进行处理分析(关于这一点可以简单了解数据库,运用数据库的数据存储方式进行数据管理),原始的中国式经典二维表需转化为一维表,下面通过3步将二维表转化为一维表,先看处理得到的一维表数据:

 

处理得到的一维表

 

 

 

 

思路

 

原始的二维表中有多个行标题/表头及列标题/表头,实际工作中可能存在更复杂的情况,考虑将所有的行标题合并,所有的列标题合并,变为简单的二维表,运用数据透视表逆流程,将处理过的二维表逆透视,变为只有3列(2列属性,1列值)的一维表,再对2列数据进行拆分列处理,变为一维表,再对数据进行简单处理,得到最终数据

 

 

 

工具

 

使用powerquery进行二维表到一维表的转化,详细步骤如下

 

STEP 01

点击数据>自表格/区域,加载数据到powerquery,取消勾选“表包含标题”,将数据加载到powerquery中

 

 

 

STEP 02

在powerquery操作界面,对列1、列2进行合并列操作,合并为1列,选中第1列及第2列(原行头),点击转换>合并列

 

注:此处需添加分隔符,以便后续进行拆分

 

 

 

STEP 03

合并列后,进行行列转置,点击转换>转置

 

 

 

注:转置后若存在列值为空,可点击转换>填充>向下进行向下填充,适用于列标题(头)为合并单元格的情况

 

 

 

STEP 04

转置后将第一行用作标题,点击转化>将第一行用作标题

 

 

 

 

STEP 05

进行第二次合并列,选中第1列及第2列(原列头),点击转化>合并列,数据变为简单的二维表

 

注:此处需添加分隔符,以便后续进行拆分

 

 

 

STEP 06

对处理后的二维表进行逆透视,选中第1列数据,点击转化>逆透视列>逆透视其他列,数据变为仅有3列的一维表

 

 

 

STEP 07

对于仅有3列的一维表,进行拆分列反向操作,将之前合并的值拆分掉

 

注:由于合并列后部门值重复,作为标题的“供应商-产品”中存在部分值后面有后缀(_1)的情况,可通过powerquery中的拆分列或excel中的查找替换进行处理

 

 

 

STEP 08

点击“关闭并上载”保存数据,可在excel中查看到处理得到的数据

 

 

 

STEP 09

最后一步,将数据的表头进行更改,稍作处理变为最终想要得到的数据

 

注:数据中存在在powerquery中加上了后缀(_1)的值,需要进行处理

 

 

以上,为二维表转一维表的简单操作,还有很多别的处理办法,欢迎大家分享。

首发于个人微信公众号【八九子】,有任何问题可随时联系我,邮箱 fanyu1601@163.com

 

posted @ 2019-06-20 14:15  我打江南走过  阅读(10980)  评论(0编辑  收藏  举报