Dashboard真实案例-从一团乱麻的源数据到专业清晰的Dashboard
我一直希望拿一个真实的案例介绍正确的数据处理和分析的思路和方法，但是要么太复杂，要么太简单。最近，终于遇到了一个合适的案例。从这个案例我们可以看到你面对的源数据的现状，以及应该如何处理，才能使你看起来令人无比头痛的繁琐工作中解脱出来。
本文较长，但是很重要，建议耐心阅读😉
先看背景和原始数据
2022年冬奥会在北京举行。可口可乐公司作为赞助商，需要为冬奥会提供饮料。为了保证现场的饮料供应，可口可乐公司从去年就组建了团队，开始进行各种准备工作。回归线作为可口可乐公司多年的供应商，被邀请参与了这个准备过程，为该团队提供支持服务，主要工作是帮助整理各种数据，设计各种表格，出具各种报表，以及设计一套工具，尽可能的自动化工作，减轻团队的工作负担。
本案例是其中的一个小项目的一部分：物流数据的处理和测算。
大部分人不太了解的是，为了保障饮料的供应，需要很多设备，比如冰柜之类的，为了保证冰柜中随时都有足够的饮料，需要有设备从库房到冰柜运送。这些设备，包括这些设备的运送，是一个庞大的数量。到底需要多少，需要根据各场馆的测算，比如需要多少饮料，需要使用多少人等等。
我的同事为了测算这个物流数据，设计了这么一个表格：
相信很多人都会对这种表格很熟悉。很多客户在问我问题的时候都会给我展示这种表格😅。
听过我的课的同学都知道，我是很反对这种表格的。我在各个场合下都强调一定要有规范的源数据表。只有有了规范的源数据表，我们后续的分析工作才能够变得足够简单，才能实现自动化。而这个表可以说是从任何角度看都不符合源数据表的规范要求。
但是，我不得不承认，像上面这种表格是摆脱不了的。主要的原因就在于这个表格实在是太方便了：
内容经常变动
由于现在还属于前期准备，所以很多信息不完善，比如场馆信息。有些内容开始是根据经验猜测的，后续随着其他信息的逐步完善，需要逐步调整，比如冰柜等设备类型。计算逻辑逐步调整
做过类似工作的同学可能会了解，整个测算的计算逻辑也不是一开始就有一个清晰的定义。往往会随着信息的完善逐步调整。更甚至，往往调整完了，一看具体结果，又会有新的思路，于是计算逻辑又会有大的调整。
基于这两点，我实在是想不出有比这个表更加合适的方式了。随时可以进行设备和场馆的修改（行列增删），随时可以更改计算逻辑（修改公式）。可以说，没有比Excel更加适合这个工作的工具了。（不要提开发专用软件，且不说时间来不来得及，即使时间充分做出来了，这种需要随时修改的需求，这个软件也是满足不了的）。
既然这个表这么好，问题出在哪里呢？
问题在于这个表格非常难以进行分析。
比如，考虑要从这个表上得到一下信息：
不同赛区（北京/张家口）的设备清单和数量
某种设备类型的数量和费用
某个场馆每种设备的数量和费用
某几个场馆的某种设备的详细类型，数量和费用
......
这个清单还可以继续拉很长，甚至没有止境。
对上面的每一个需求，你都可以从上面的表中得到，有些很容易，有些稍微复杂，但是绝对没有很困难的。
但是，你想象一下实际的场景中，某一个团队成员在考虑这个设备物流问题时，突然想知道某一个信息，他就得停下来，在这个表上找到相应的数据，如果不行，还必须写公式计算想要的结果，
先不说要花多长时间，他的思路就打断了。这是很严重的，会导致工作效率非常低下，如果是多个人一起开会遇到这种情况，效率会更低。
其实，关于这个问题，我们在前面的一篇文章中说过，有兴趣的可以看一看：
这个问题的本质就是任何人都没有办法可以直观的看清这份数据。而这个问题的解决方案也简单，那就是为这份数据制作一个Dashboard
重新规范数据
为了基于这份数据制作自动化的Dashboard，我们需要重新设计一下这个表格。
首先，我将源数据表改造成了下面的样子：
看上去变化比较大，但是实际上变化不大，主要是样式的变化。除了样式外，这个表跟上一个表相比，有以下的变化：
没有了总计和单价
设备数据中只保留了设备数量的计算逻辑和结果，去掉了费用
下面是为什么这么设计的原因：
为什么没有总计
原表中之所以有总计，是为了方便进行数据查询的。这实际上犯了一个大忌：源数据和结果数据混在一起。在新的方案中，结果数据我准备用Dashboard展现，就没有必要在这个表上保留总计了。为什么没有单价
仔细想想，就会发现，费用其实也是结果数据。实际上只要根据数量*单价计算即可，因此没有必要保留费用，也就没有必要保留单价了。为什么没有设备费用部分
原因上面说了。
因为我在上面的表去掉了单价，所以我又添加了一个单价表：
因此，经过规范后，我们现在有了两张表：设备物流测算表和单价表。
设计考虑
首先是实现方法的选择。实际上这个工作时间特别紧（从产生这个问题到用Dashboard，只有半天的时间），写程序实现肯定来不及了。所以我就考虑用Power Query和Power Pivot来实现。
方法确定后，那就是考虑中间的步骤了。很简单，首先需要将源数据表拆分成几个独立的数据源：
1个场馆表：记录各个场馆的基本信息（编号，名称等）和场馆的基础数据部分
n个分类设备表：由于不同类别的设备有不同的逻辑，所以，我们最好每个类别的设备单独一张表，记录这些设备的数量，费用等信息
1个汇总表：n个 分类设备表实际上是不方便分析的，我们需要将它们合在一张表上（你现在会有点困惑，看到后面就明白了）。
创建中间表
这个过程其实很简单。下面我把关键的步骤给大家介绍一下
创建查询-场馆基础数据
选中源数据表数据区域任意单元格，将其转换为超级表（Table）。然后在“数据”选项卡下，点击“从表格”，
在Power Query编辑器中，选中所有的基础数据之后的列，点击鼠标右键：
点击“删除列”，将这些列都删掉。
将右上角名称修改为“基础数据”：
在“主页”选项卡下，点击“关闭并上载”下面的箭头，选择“关闭并上载至”：
在对话框中，选择“仅创建连接”：
点击加载，“基础数据”连接被创建
创建设备单价表连接
用几乎同样的方法，选择设备单价表，创建设备单价表连接（这次我们需要所有的列）
创建仓库工具设备表
选中源数据表任意单元格，创建查询，删掉除了场馆ID和仓库工具各列之外的其他列：
选中仓库工具各列（除了场馆代码外其他列），点击鼠标右键，点击“逆透视列”：
得到了如下的表格：
将属性列名修改为“设备类型”，值列名修改为 “数量”：
将该查询命名为“场馆-仓库工具”，点击“关闭并上载至”，选择“仅创建连接”，然后加载。
创建仓库工具费用表
在“数据”选项卡中，点击“合并查询”，点击“合并”：
在对话框中，将第一张表选择为上一步创建的“场馆-仓库工具”，第二张表选择“设备单价表”：
保持下方的联接种类不变，按住Ctrl键依次点击设备表单价表的“类别”，“名称”列，然后按住Ctrl键依次点击场馆-仓库工具表的“设备类型”，“设备”列：
点击确定，在Power Query编辑器中得到如下的表格：
注意最后一列就是连接的设备单价表，显示是个Table。
点击最后一列的标题行最右边的按钮，打开一个对话框，去掉其他选择，只勾选“单价”：
点击确定后，得到了单价列：
在“添加列”选项卡下，点击“自定义列”，在对话框中将列名命名为费用，公式为：
=[数量]*[设备单价表.单价]
点击确定后，结果如下：
将查询命名为“合并-仓库工具费用”，点击“关闭并上载至”，选择“仅创建连接”，点击加载。
创建其他设备类型费用表
参照3/4两步，为其他设备类型的表格创建连接。得到N个查询连接。
将各个费用表合并起来
在“数据”选项卡中点击“新建查询”，点击“合并查询”，点击“追加”：
在对话框中，选择“三个或更多表”，在左边的列表中，选择前面创建的所以合并费用的连接，并点击添加，添加到右边的列表中：
点击确定后，得到一个新的查询，将该查询命名为“全部设备费用”，点击“仅创建连接”并加载
创建Dashboard
这个Dashboard是用数据透视表完成的。
首先将基础数据连接和全部设备费用连接添加到数据模型
然后在关系模型中，创建一关系（通过场馆代码就可建立各表之间的关系：
然后超级透视表（Power Pivot）：
该透视表以基础数据表为源数据创建，用于显示各场馆基础信息和数量
在创建第二个透视表：
创建第二个透视表时要注意以下几个要点：
基于全部设备费用表创建
行字段为基础数据表中的场馆名称和运营团队名称
列字段为设备（名称）
值字段为费用和数量
该透视表要放在第一个透视表所在的工作表上，放在右侧，起始行与第一个透视表一致，隐藏第二个都是表的所有行标签字段
如图添加两个切片器并且格式化切片器。继续格式化透视表，得到如下的以透视表为基础的Dashboard：
总结
这个案例从我拿到原来的测算表开始到完成差不多用了2个小时，原来非常头痛的工作就解决了（而要通过VBA编程解决这个问题，保守估计大约需要2天时间，这也是原来负责这个项目的同事直接在测算表上展示结果数据的原因）。团队成员可以随时在测算表中修改项目和计算逻辑，这些结果可以实时在Dashboard中反应出来，而这个反馈又可以为团队提供思路，这样逐步迭代修改，在这个模块中的内容最终会得到一个合理的结果。在Dashboard中可以通过切片器的选择，随时查看各种组合的汇总结果。整个分析和调整的效率得到了极大的提高。
实际上，这是很多实际工作最应该采用的思路和方案。
本文对关键步骤的介绍非常详细，但是因为篇幅的原因，仍然有一些步骤比较省略。总体而言，因为创建中间表这个过程比较关键，所以介绍的比较详细。而对于最后创建Power Pivot比较简略。如果有同学希望了解详细内容，可以看下面这个专题中的文章：