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

引言

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

好的数据源长什么样

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


坏数据源

然而如果我们拿到的数据源是下面这个样子,猛一看上去,这个数据还不如刚才的表格,从中几乎无法得到任何有效的信息,但是,这种形式的数据,几乎可以被所以统计分析软件直接使用,借助 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

案例

点击案例测试文中内容

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,511评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,495评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,595评论 0 225
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,558评论 0 190
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,715评论 3 270
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,672评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,112评论 2 291
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,837评论 0 181
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,417评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,928评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,316评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,773评论 2 234
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,253评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,827评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,440评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,523评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,583评论 2 249

推荐阅读更多精彩内容