高效分析数据从设计数据源开始

引言

工欲善其事，必先利其器。在对数据进行分析的过程中，各种各样格式的数据源往往会对数据分析工作造成极大的困扰，后续数据分析工具的使用也会变得异常的复杂，因此，如何得到一个便于分析、处理的数据源，对于提高数据分析的效率，具有十分重要的意义，在数据收集之前，如果能够提前按照需要，设计好数据源进行采集，也能够对后续的数据清洗和整理带来极大的便利。

好的数据源长什么样

这是某医院科室考核的成绩（虚构），从表中我们可以看出各个科室各项指标的得分情况，但是，利用这个表格，我们想要进一步分析科室之间成绩差异，指标之间成绩差异就变得比较困难，分析受到了较大的制约，因此，从数据源的角度而言，这个表不是一个好的数据源。

然而如果我们拿到的数据源是下面这个样子，猛一看上去，这个数据还不如刚才的表格，从中几乎无法得到任何有效的信息，但是，这种形式的数据，几乎可以被所以统计分析软件直接使用，借助 Excel 的数据透视表功能，可以从各个角度对数据进行分析，所以，从数据源角度来说，这才是一个好的数据源。

一维数据和二维数据

细心的读者可能发现了，两个表格的主要差异，就在于需要分析的数据的排列方式，在第一个数据源中，数据是二维的，相当于已经把数据平铺在表格上，再想对数据进行变形、分析就会比较麻烦，而第二个数据源中，数据是一维的，我们可以随意对一维的数据进行展开、塑造，得到我们需要的分析表。
因此，在数据收集的过程中，要尽量保持数据的原貌，使数据以一维的方式存储，这样可以对以后的分析带来极大的便利。但是，如果拿到的已经是二维数据该怎么办呢？接下来来介绍一下通过“逆透视”来把二维数据还原为一维数据的几种方法。

Excel 逆透视的三种方法

借助数据透视表功能

1. 在 Excel 表格中依次按 Alt d p 键，进入下图窗口（数据透视表和数据透视图向导）；
   

2. 选择“多重合并计算取数据区域”并点击“下一步”按钮；
   

3. 选择“创建单页字段”，并“下一步”；
   

4. 在“选定区域”中选择需要进行逆透视的数据区域，并添加。需要注意的一点是，通过这种方法进行逆透视，只能包含一个标题行和一个标题列，如果有多个标题行（列），需要保留不重复的一行（列）作为逆透视的标题列，在逆透视结束后，再通过其他函数进行查找匹配。执行“完成”

5. 在上一步之后，我们将得到一个数据透视表，双击透视表右下角的行列总计值，就可以得到逆透视的数据，再借助 VLOOKUP 和 HLOOKUP 函数，补全数据源的必要信息。

借助查询功能（仅 Excel 2016 版本）

1. 在“数据”选项卡下，选择“从表格”新建查询；
   

2. 在创建表的窗口中，选择数据来源；
   

3. 选择需要进行逆透视的列，按住 Ctrl 选取多个，右击选择内容，选择“逆透视列”选项，就可以在查询中得到逆透视后的数据，点击左上角的“关闭并上载按钮，即可将逆透视后的数据传回到 Excel 当中。需要说明的是，这种方法能够支持多个标题列，但是不能包含多个标题行，因此也需要对标题行进行提前处理。
   

借助 VBA

利用 VBA 对数据进行逆透视是最灵活的方式，可以根据数据源的差异而调整程序，这里提供一种解决方案供有能力的用户进行尝试。

Option Explicit
Sub 逆透视()
Dim i, j As Integer
' i, j 为值矩阵
Dim line As Long
' line 为逆透视之后的行
Application.ScreenUpdating = False
' 关闭屏幕更新，提高运行效率
line = 1
For i = 2 To 20
' 按行循环
    For j = 2 To 20
    ' 按列循环
        If Sheets(1).Cells(i, j) <> "" Then
        ' 逆透视忽略空值单元格
            Sheets(2).Cells(line, 1) = Sheets(1).Cells(i, 1)
            ' 读取列标题
            Sheets(2).Cells(line, 2) = Sheets(1).Cells(1, j)
            ' 读取行标题
            Sheets(2).Cells(line, 3) = Sheets(1).Cells(i, j)
            ' 读取值
            line = line + 1
        End If
    Next
Next
Application.ScreenUpdating = True
' 一定记住要打开屏幕更新，否则结果无法显示
End Sub

案例

点击案例测试文中内容