第 2 章 破解难题:Tableau连接复杂Excel 数据

第 2 章 破解难题:Tableau连接复杂Excel数据

本章介绍如何在Tableau Desktop中处理常见的Excel数据问题,包括多文件数据合并、数据关联、行列转置和数据混合分析等。不需要使用Tableau连接到Excel数据做分析的读者,请跳过本章,对于平时大量使用Excel+Tableau做分析的读者,则强烈建议认真阅读本章,并跟着所有步骤实际操作一遍。

学习难度:初级

知识点:数据解释器,数据并集,数据别名,数据混合

2.1 陷入困难

上周大麦给大家讲解Tableau后,大家的热情很高,都尽量多使用Tableau做数据分析。不过由于大家日常工作繁杂,对Tableau还只是初步上手,有时候忙起来,就不自觉地又回到Excel+PPT的路子上去了。

这天早晨,大胡过来跟小白交代了一项新任务。

大胡 小白,我往你的邮箱发了一些市场调查的数据,你收一下,做个市场竞争的分析,明天开会的时候公司决策层要看。

小白 好的,老板。

大胡 哦,对了,你最好用Tableau软件来分析,那个软件分析功能强,展现效果好,这次开会顺便给公司的高层也推介一下。

小白 哦,好。

大胡走后,小白开始查收邮件,坐在座位上目不转睛地瞪着电脑干活儿。临近中午的时候,大明开完会回到办公室,看见小白聚精会神,两眼发红地看着电脑。

大明 小白,忙啥呢?怎么看上去很累的样子?

小白 哦,大胡早晨发给我一些市场调查数据,让我用Tableau分析一下,可是要先整理这些Excel的格式才行,这些文件的格式实在是太……唉,要是我用Excel和PPT做,没准已经做完一些了,现在只能先把所有文件整理完,大胡明天开会要用这些分析,估计今晚我得加班了,还不知道能不能做完。坐在这整整半天没动窝了,感觉咋这么费劲呢?

大明 啊?整理Excel格式?为什么啊?我看看都是啥格式。

小白 嗯,你来看看吧。上次大麦来的时候,咱们分析的数据格式很规整,工作表的第一行是列名,接下来是数据,很清楚。可是你看市场数据,所有文件都有一些标题,有的还不是一行;有的一个工作表里面有若干个小表格,看着乱糟糟的,也不知道怎么摘出来;有的是交叉表格式,我得转成简单列表格式;还有的每个月都是一个独立文件,我得先合并到一起;有一些代码的对照表在单独的Excel文件里,我得手工先把数据对照出来,弄了半天眼都花了!

大明 等等,慢慢来,一个一个看。这个是有标题的情况对吧?

有标题的Excel表格

小白 嗯,这是工作表里面有多个表格的情况。其中包含一个公司代码对照表和一个需要进行行列转置的利润表。

一个Excel工作表中存在多个表格的情况

   这几个是格式相同的Excel文件,每月会增加一个文件。

{%}

多个格式相同的Excel文件

大明 这样吧小白,我觉得这些Excel文件整理起来绝不是一天能做完的,就算你今天在这工作通宵,也不一定能搞定。既然这样,还不如先去吃午饭,没准儿吃饱饭一高兴,想个妙招儿三下两下都搞定了呢。

小白 哪有时间吃午饭?要不一会儿你去吃饭的时候帮我打包回来吧,这个任务的时间太紧了……

大明 嘿,我跟你说了嘛,吃饱了才有力气干活儿,才有脑子想办法!

小白 有啥方法呢?你要是有办法,我请你吃饭!

大明 哦?真的?那好吧,请饭就不用了,午饭后请我喝咖啡就行了。

小白 你真有办法?大明哥,你可别害我,这个真的明天要用!

大明 放心放心!不会害你的,走吧!

在餐厅吃午饭时,大明点了几个小菜慢悠悠地吃起来,而小白只买了一碗面却狼吞虎咽,一副心不在焉的样子。

大明 咳,你这萌妹子吃饭整得比爷们儿还豪放,我这压力很大啊~

小白 没有啦,你慢慢吃,我就是在想那些Excel文件的事情。

大明 哦,上礼拜不是学了Tableau吗?不用那么苦哈哈了吧?

小白 可是,用Tableau也得先整理数据啊!

大明 小白你知道吗?Tableau是让人们生活更美好,绝不是让人们工作更苦逼的。看你急的这样,不逗你了,我也快点吃完给你三两下搞定,下午分析完数据还能有大把时间去喝下午茶。

小白 真的?大明哥你可别骗我,搞定了我请你喝咖啡!

大明 好,就这么定,走。

2.2 Tableau轻松搞定

回到办公室后,大明让小白打开Tableau Desktop,然后打开“市场数据-201702.xlsx”文件。

大明 把“数据”工作表拖放到画布上,你会发现在数据预览区域未能正确识别数据区域。

{%}

未能识别Excel字段名称的数据连接

   我们先处理第一个问题,数据文件有标题的情况,Tableau可以自动跳过那些标题行,识别数据区域。其方法很简单,在“工作表”下方,有一个“使用数据解释器”复选框,选中它,Tableau就会自动解析数据中的“脏数据”,并且进行自动处理,比如空行压缩、自动跳过标题和表头、寻找数据区域等。选中它,进行数据预览。

{%}

启用数据解释器

小白 哇!我还一个文件一个文件地删这些标题和空行呢!太牛了!

大明 这就牛了?你也太小看Tableau了。顺便解决你的第二个问题,每月一个Excel文件,需要进行合并,这种合并在Tableau里叫作并集,在数据库里实际上就是Union操作,其特征是把结构相同的多份数据上下拼接起来形成一份数据表。

小白 啊?这个咋做?

大明 很简单,先把“数据”表从画布窗格中移走,然后按住左边的“新建并集”标签并将其拖放到画布区域,松开鼠标就会跳出一个并集设置对话框。这个对话框处理两种情况,现在看到的是第一种,也就是结构相同的数据在同一个Excel文件的不同Sheet中,你可以把表格中的多个Sheet直接拖放到并集窗口,自动完成合并。

{%}

新建并集

   不过显然这个场景与你现有的Excel文件不同,所以我们切换到另一种模式“通配符 (自动)”模式。在工作表中输入“数据”两个字,因为你的每个文件里面需要合并的工作表的名字叫作“数据”,如果你的工作表的名字是数据加一些变化的后缀,可以用“数据*”命名,其中星号代表通配符。工作簿名称中输入“市场数据*.xlsx”。下面是搜索范围, Tableau可以搜索当前文件夹以及它的子文件夹、父文件夹中的文件。你的文件都在一个文件夹里,所以这两个复选框就不用选了。现在可以点击“确定”,看看发生了什么。

{%}

使用通配符并集

小白 所有文件自动合并啦!太神奇了!

大明 先别一惊一乍的好不好?我们再看第三个问题。一个Sheet中有多个表格的问题,这里我们首先需要在Excel中为每个表格设定一个命名区域。

小白 命名区域?是个什么东西?

大明 Excel高手啊……竟然不知道命名区域?命名区域就是把工作表中的某一块单独起一个名字的意思,比如把“代码对照表”里的“公司代码对照表”和“利润”两个表格,分别起名为“对照表”和“利润表”,就像这样。

{%}

Excel中的命名区域

提示:选中其中一个表格,在左上角A、B上面的位置输入命名区域的名称。

   然后我们在Tableau中添加一个数据连接,点击当前连接名称上面的蓝色字“添加”,选择“代码对照表”并打开,在左边表格列表就出现命名区域的表格了。选择“对照表”,把它拖放到画布中,由于它与并集的字段名称没有相同的,所以Tableau没有自动识别关联关系,我们来手工指定一下,使并集中的“公司”等于“对照表”中的“公司代码”。

{%}

关联对照表

小白 哇塞!我原来只知道用VLOOKUP,就想着先合并完那几个文件,然后把这个对照表也粘贴到同一个Excel工作簿中,可是整个上午我的工作表还没合并完。

大明 刚才我们用的是跨库关联功能,也就是说关联的表格不在同一个数据文件中,甚至不在同一个数据库里面,我们可以同时连接,再进行跨文件、跨库关联。我们继续处理你的下一个问题,“利润表”的行列转置问题和进一步数据关联的问题。这次要使用“数据”菜单下面的“新建数据源”。

{%}

“新建数据源”选项

   选择“新建数据源”之后,画面就会完全新建一个数据源,这次我们仍然选择“代码对照表”这个Excel文件,这次把“利润表”拖放到画布上,就像这样。

{%}

连接利润表

   对于这个表,我们主要解决行列转置的问题,我们在键盘上按住Ctrl键,用鼠标选择“201701利润”“201702利润”“201703利润”“201704利润”这几列,然后在表头上单击鼠标右键,此时会出现快捷菜单。

{%}

使用“数据透视表”

   里面有一项“数据透视表”,就是做行列转置的,我们选择这个菜单项,行列转置完毕!

{%}

转置后的“利润表”

   如果你想恢复之前的格式,那么选中这两列,在快捷菜单里选择“移除数据透视表”一项即可。我们保留转置后的结果,但还要做进一步的处理。首先要把字段名称改为“月份”和“利润值”。然后对于“月份”列,我们还需要把它转换成日期格式,这需要几个步骤,第一步单击鼠标右键,调出月份字段的快捷菜单,选择“重命名”一项。

{%}

重命名字段

   在弹出的“编辑别名”菜单里面,为每个字段值取一个别名,很简单,就是去掉利润两个字而已。以后你熟悉了也可以用计算字段来做,现在就先使用这种傻瓜模式吧,就像这样。

编辑别名

   然后数据预览窗格里面的月份一列就变成了201701、201702、201703和201704,为了跟其他数据做关联分析,需要把它改为日期格式,Tableau智能化程度很高,直接把这一列的类型改成日期就可以了!

{%}

更改字段类型

小白 这也可以?原来是字符串,改别名还能自动变成日期?

大明 是的,变成日期是非常重要的一个步骤,一方面,只有变成日期,才能使用Tableau的智能日期处理,自动进行分层分析,包括年度、季度、月份等,还有连续日期格式和离散日期格式的不同用途。另一方面,只有这里变成日期,才能与另外那份销售额的数据进行关联,分析同一个公司同一个月份的销售额和利润。Tableau不但在使用“日期”维度进行分析时是智能的,在数据源中识别日期也是智能的。就像刚才这个例子,将“201701”这种格式指定为日期格式,Tableau就智能地将它转成日期格式。如果是“2017年3月20日”这种写法,也能自动转成标准日期格式。甚至在一列数据中,有的行是“201701”,有的行是“2017年3月20日”,当你统一指定这一列是日期格式时,Tableau都能自动将所有行数据转换成标准的日期格式。

小白 这就……所有问题都搞定啦?

大明 原理上是都搞定了,不过我还得告诉你具体怎么用,现在切换到工作表1(销售数据),数据窗格最上面有两个数据连接,一个是利润表(代码对照表),另一个是市场数据,我们可以在每个数据连接上单击鼠标右键,在弹出菜单中选择“重命名”,给它们重新起个名字。

{%}

重命名数据源

   不过这并不是关键,重要的是“市场数据”里面有销售额,而“利润表”里有利润数据,我们想要在分析过程中把两个数据源关联起来,这时需要使用“数据”菜单下面的“编辑关系”。

{%}

编辑数据源关系

   Tableau会自动识别两个数据源的关联条件,通常,日期以及日期的计算结果比如季度、周等都会自动作为关联字段,而且两个数据源中同名的字段会自动建立关联。一般情况下,直接使用自动生成的关联关系,但遇到两个数据源中字段名称不相同的情况,就需要手工指定关联条件。这里,我们就需要自定义一个关系,即公司与公司代码的关系。

编辑主数据源和辅助数据源之间的关系

小白 那个“主数据源”“辅助数据源”是什么意思?

大明 观察挺细,不错嘛!还记得大麦来的时候讲过的数据关联类型有内关联、左关联、右关联和完全外部关联等几个选项吗?

小白 记得。类似于集合运算。

大明 现在我们的这个功能叫作数据混合,与关联的原理比较接近。主数据源和辅助数据源的关联相当于左关联,主数据源在左边,关联后的数据集以主数据源为准。现在的两个表里面关联字段的取值范围都是一样的,所以谁做数据源都一样。但在使用数据混合的时候,这些概念要很清楚,否则当数据分析结果不对时,很难弄清楚问题出在哪里。

小白 嗯,那么这个数据混合该怎么用呢?

大明 用起来就简单啦。先从“利润表”里面拖放“月份”到“列”功能区,展开成“年”和“月”两个胶囊,把“公司代码”拖放到LOD区域,把“利润”拖放到“行”功能区,得到这样的结果。

{%}

主数据源查询数据

   我们知道,销售额数据是在另外一个数据源里面,没关系,可以用鼠标点击“市场数据”数据源,直接将“度量”窗格中的“销售额”拖放到“行”功能区,再把“公司名称”维度拖放到“标记”功能区的“标签”按钮上,然后把“公司名称”拖放到“标记”功能区的“颜色”按钮上。这样,销售额和利润就呈现在同一个图表中了。

{%}

数据混合分析

小白 搞定啦?还有吗?

大明 哎,该你一惊一乍的时候又平静了。搞定啦!

小白 没有啦,你这新花样太多了,我怕又被你笑。

大明 这次是真的搞定了,不过有一些细节你还得注意。在混合分析之后,我们会发现在左侧 “维度”窗格中,主数据源和辅助数据源赖以关联的字段右边会出现一个红色的曲别针,这表示当前数据视图中正在使用的关联条件。如果你用鼠标点击这个曲别针图标,Tableau会断开两个数据源的关联,断开关联之后数据呈现的结果可能就完全不一样了,所以除非你很清楚你在干什么,否则不要随便断开连接。

   还有最后一点,刚才两份数据中分别有销售额和利润,但这两份数据的颗粒度是不一样的,销售额细分到公司、月份和产品类别,而利润数据只到公司和月份,没有细分到产品类别。所以这就决定了做数据混合分析时,分析的颗粒度就只能到公司和月份的组合。如果在视图中出现了产品类别,混合后的销售额和利润就需要经验丰富的分析员才能正确解读了。

小白 啊哈,看来要学习的东西还有很多啊!没想到Tableau有这么多的功能用来处理Excel!

大明 其实对于文本文件来说,这些功能也都是可以用的,但如果是连接数据库,就不一定都能用了,或者操作的方法有差异。

小白 嗯。看样子我一会儿就能搞定数据了,再花一小时分析,直接用Tableau的故事代替PPT,今天不但不用加班,还能去喝杯下午茶!

大明 哈,我跟你说的嘛,Tableau让生活更美好。

小白 可是,我上午整整半天的工作,岂不是白费工夫了?

大明 你又没来问我,怪我咯?

小白 哪里哪里,我说话算数,请你喝咖啡吧!下楼下楼,你想喝啥?

大明 斯达巴克斯大杯拿铁!哈哈!

目录

  • 前言
  • 第 0 章 没有Tableau的日子
  • 第 1 章 分析师起步:Tableau的第一堂课
  • 第 2 章 破解难题:Tableau连接复杂Excel 数据
  • 第 3 章 通过数据洞察业务:Tableau计算基础
  • 第 4 章 初识表计算
  • 第 5 章 增收不增利,成长有隐忧:Tableau 计算进阶
  • 第 6 章 欢迎进入Tableau计算深水区:LOD 表达式概述
  • 第 7 章 老客户贡献分析:集的应用
  • 第 8 章 客户80/20定律:快速嵌套表计算
  • 第 9 章 关注重点产品:排序
  • 第 10 章 数据桶与指标分段:数据分组
  • 第 11 章 销售要重新划地盘儿啦:手工分组
  • 第 12 章 灵活的KPI分析:数据混合与嵌套表计算
  • 第 13 章 提升分析性能:数据提取
  • 第 14 章 把数据分析和网络百科相连:动态仪表板
  • 第 15 章 一切都可以图形化:自定义地图应用详解
  • 第 16 章 更多的灵活与互动性:参数概述
  • 第 17 章 分析常常就是筛选过程:筛选器概述
  • 第 18 章 让数据更生动:自定义形状
  • 第 19 章 流向分析:桑基十八式
  • 第 20 章 数据准备也能可视化:Tableau Prep
  • 第 21 章 职业困惑:数据分析师有没有前途