如何将Excel中的二维数据转化成一维数据？

日常的工作中，未经处理数据最好的状态就是一维数据状态，在这个状态下，就可以对数据进行多层次多方面的分析运算，减少一些二维数据带来的不必要的麻烦。

 

案例：如何将下面的左边的二维数据转化成一维数据呢？

对于这个问题，今天给大家提供三种通用的方法：

• 常规的数据透视表的方法；

• Power Query的逆透视的方法；

• SQL方法

下面就这三种方法一一做详细的说明。

1

数据透视表

Step-1:首先选中数据区域，然后单击【插入】选项卡-【数据透视表】-【现有工作表】（指定存放位置）-【确定】，如下图所示：

Step-2：在弹出的对话框中，依次将各个字段拖入到相应的位置，将“区域”与“分公司”拖入至【行】字段，将“工资”，“”奖金，“社保”，“年绩效奖”分别拖入到【值】字段，显示方式为“求和”，如下图所示：

Step-3：将【列】字段中的“Σ数值”字段拖入至【行】字段，如下图所示：

Step-4：选中透视表任意位置，然后单击【数据透视表工具】下的【设计】选项，在弹出的选项组中依次进行操作：首先单击【分类汇总】-【不显示分类汇总】；其次单击【总计】-【对列和列禁用】；最后单击【报表布局】-【以表格形式显示显示】-【重要所有标签项目】，如下图所示:

效果如图所示：

Step-5：选中整个透视表区域，然后复制，再单击【开始】选项卡-【粘贴】-【值和数字格式】，如下图所示：

Step-6：选中C列，按组合键,在弹出的对话框中输入查找“求和项：”，转换到替换为空白，选择【替换全部】如下图所示：

Step-7：将第3列与第4列的名称分别修改为“类型”与“预算金额”，调整格式，最后结果结果（部分截图）如下图所示：

注：该方法适用于所有具有数据透视表的Excel版本，但在2007及以下版本中没有【重复所有项目标签】的功能，Excel2010及以上版本中有此功能。但是并不影响使用，大家可以使用定位功能定位到空格，然后输入公式，按的批量填充的方法进行实现重复所有项目标签的功能（具体的操作方法见后面的视频）。

2

Power Query

Power Query在Excel2016的版本中是内置的功能，在【数据】选项卡下面，如果处理的数据是本工作簿的数据，那么直接选择【从表格】即可进入到Power Query编辑器；Excel2010与Excel2013版本可以到微软官方上下载相应的插件，地址为：https://www.microsoft.com/zh-CN/download/details.aspx?id=39379

 

Step-1:首先任意数据区域的任意位置，然后单击【数据】选项卡-【从表格】-勾选【表包含行标题】-【确定】，如下图所示：

Step-2:进入到Power Query编辑器界面后，选中“区域”与“分公司”两列，单击【转换】选项卡-【逆透视其他列】，如下图所示：

Step-3:然后分别修改第3列与第4列的名称为“类型”与“预算金额”，如下图所示：

Step-4:单击【开始】-【关闭并上载至】-【现有工作表】（选择存放区域）-【加载】，如下图所示：

最终的结果如下图所示：

注：该方法只能在Excel2016版本及具有安装PowerQuery for Excel官方插件的Excel2010及Excel2013版本中使用。其结果当源数据发生了更改或者删减的时候可以刷新。

3

SQL法  

SQL方法对于许多的没有SQL的基础的朋友们来说可能的点难以理解，事实上在Excel中是支持SQL的。SQL是一种结构化的查询语言，不但能进行数据查找还能进行数据重组。

 

Step-1:在新的工作表中单击【数据】选项卡-【现有连接】，找到数据源表，选中后执行【打开】，如下图所示：

Step-2:在弹出的界面中选择“原始数据”，然后单击【确定】，如下图所示：

Step-3:在弹出的界面中选择显示方式为【表】，存放的位置为现有工作表的A1单元格，后后单击【属性】，如下图所示：

Step-4:在弹出的对话框中选择【定义】-【命令文本】（输入SQL代码）-【确定】，然后在弹出和对话框中单击【确定】，如下图所示：

SQL代码如下：

select 区域,分公司,'工资' as 类型,工资 as 金额
from [原始数据$]
union all
select 区域,分公司,'奖金' as 类型,奖金 as 金额
from [原始数据$]
union all
select 区域,分公司,'社保' as 类型,社保 as 金额
from [原始数据$]
union all
select 区域,分公司,'年绩效奖' as 类型,年绩效奖 as 金额
from [原始数据$]
order by 区域,分公司,类型

最后结果结果如下：

SQL语句其实上不难，但是在数据处理与查询中有着重要的作用。那么上面的这个例子其实很简单，总结起来就两点，一个是对字段进行别名的命名与新建一个字段，并且指定其具体的内容；二是将查找到的数据使用union all连接起来即可。具体的说明如下：

一是查找语句，以第一句查找工资为例给大家进行说明：

select 区域,分公司,'工资' as 类型,工资 as 金额
from [原始数据$]

意思就是说从（from）工作表或者数据区域（[原始数据$]）里查找（select）区域字段，分公司字段；'工资' as 类型，意思是新建立一个字段为类型，这个字段的每一个记录为一个常量“工资“,然后再查找工资这个字段，将其字段名修改为工资。这里要大家注意的是：工资与“工资”的区别，不带双引号的工资指的是数据源里的字段名称，带引号的”工资“指的是赋予类型这个字段的一个常量值；

二是连接语句：union all指的是将查找到工资，奖金，社保与半年绩效奖这些数据连接起来。最后的orger by语句是一个排序语句，具有升序与降序两种排序方式。

注：该功能在Excel的常用的版本中都可以使用。其结果当源数据发生了更改或者删减的时候可以刷新。

---

来源：24财务excel