6.2 数据透视图表：制作项目奖金情况动态看板

6.2

数据透视图表：制作项目奖金情况动态看板

6.2.1案例情况介绍

在某互联网公司中有多名开发人员，他们会针对产品出现的各类不同问题进行开发并完成修复。当BUG修复后，会根据BUG类型的不同对相关人员发放奖金，项目奖金金额=BUG类型的单价×核定的标准工时。在过去的一年中，这样的BUG修复记录多达几千行，在年终时对于每个人的项目奖金情况要进行可视化的呈现：每位员工在各类型BUG修复中的完成情况和个人在团队中的占比情况（图6-1中显示了部分数据情况）。

图6-1

看到下面这一张效果图（见图6-2），有没有感觉跟日常做的报表不一样？此报表的底色没有用Excel默认的白色背景，而是改成了深蓝色，这让整个看板充满了设计感与科技感。是的，这就是颜色的魅力！如果你的BI看板是放置在电子屏、会议室的大屏幕上进行展示的，就可以选择类似于这种“深色底纹、浅色字体”的配色方案。当然这个配色方案可以根据公司的Logo颜色或自己的喜好进行调整，但在配色方案的选择上，还是建议大家按照第1章的内容多参考一些成功的设计方案为宜。

图6-2

首先，我们来分析一下这张图表的整体结构，包括选项控件组合框条形图、圆环图、表格、小火箭图形等元素，并且通过单击控件组合框可以查看到不同人员的图表数据，实现动态图表的效果。

如何实现这种动态联动更新的效果呢？思路决定出路，每一个精美的图表背后都有3个最基础的数据来支撑：数据源、参数、报表。数据源是汇总业务流水的台账，方便我们日后查看业务数据的增减变化；参数是一些业务之间共性存在的基本规则，比如A、B、C、D、E、F各类不同BUG问题的开发单价等；报表则是用于呈现数据统计结果的各类表格、图表、动态图表等，可让我们的数据进行有效的可视化呈现。

针对Excel台账的数据源，我们还需要计算出具体的项目奖金情况，这需要用到与函数相关的内容。接下来我们就一同开始制作本节的案例吧。

6.2.2整理数据源

①将数据源设置为超级表（见图6-3）后，核定各记录的奖金金额。

②计算核定奖金（单价）。在G列中输入核定奖金（单价），其中G2单元格的公式=IF（（@是否完工）=＂完成＂，VLOOKUP（（@BUG类型），$K$2：$L$7，2，0），0）（见图6-4）。

图6-3

图6-4

③计算奖金。在H列中的数据为奖金（即最终发放给个人的项目奖金），其中H2单元格的公式=IF（（@是否完工）=＂完成＂，VLOOKUP（（@BUG类型），$K$2：$L$7，2，0）*（@核定工时），0）（见图6-5）。

图6-5

说明：在使用超级表编写的函数公式中，公式中的引用参数不直接显示单元格的地址，而是显示为@字段名。超级表公式的编写具有以下3个特点：

·当输入一个公式时，下面的单元格会自动填充公式，不需要再去手动填充公式。

·当需要增加多行新数据时，格式也能保持与以前的一样，并且有公式的列也会自动填充，直接呈现计算结果。

·在新增内容后，已做好的透视表不用再去更改数据源区域，只要单击刷新就可以了。

6.2.3制作数据透视表

1.创建透视表

选中数据源中的任意一个有字单元格，单击【插入】选项卡中的【数据透视表】，在弹出的对话框中选中【新工作表】，单击【确定】按钮（见图6-6）。此时Excel会以选中的单元格为起点，向四周扩散选中到一个连续的表格区域，即以前面新建的超级表作为数据源区域，并将选择的超级表表名（即表1）显示在数据来源窗口中。

2.设置透视表的布局

在数据透视表里将【BUG类型】放到【列】区域中，将【开发人员】放到【行】区域中，将【核定工时】放到【值】中。设置完成后，也便快速地统计出了各个开发人员已完工的项目奖金统计表（见图6-7）。

图6-6

图6-7

6.2.4制作图表看板

1.制作背景板

新建一张工作表，选中行列标签交叉位置处的小三角，即快速选中整张表格。利用ColorPix工具取色，将表格底色【形状填充】颜色的参数值调整为RGB色值：5，12，54，即深蓝色（见图6-8）。

图6-8

2.制作绘图数据源

①在第1行和第1列内输入表头和姓名，并设置文字的字体、字号、颜色等，其中字体颜色为亮蓝色，利用ColorPix工具取色，即RGB色值：0，176，240（见图6-9）。

图6-9

将相应的数据从透视表中运用VLOOKUP函数匹配进去，在B2单元格内输入公式=VLOOKUP（$A2，＇2透视表＇！$A：$H，COLUMN（B1），0）。

说明：COLUMN是一个简单的辅助函数，即COLUMN（reference），其中reference为需要得到其列标的单元格或单元格区域。这里我们使用到的COLUMN（B1）是指引用的第2列，即计算的结果等于2来取代VLOOKUP函数的第3个参数匹配结果的位置。这样方便我们在向右拖曳快速复制公式时，VLOOKUP函数引用的列号能够随着列的变化而变化（见图6-10）。

图6-10

②将公式批量填充进其他单元格，选中要填充的B2：G10单元格区域，在B2编辑栏的公式处，按住Ctrl+回车（Enter）组合键，完成批量填充（见图6-11）。

图6-11

③一键快速计算合计金额的方法如下：选中A1：H11区域，按住Alt+=组合键，实现快速批量求和。此时，已经完成了最右侧奖金总额列的结果计算和最下面的合计行的计算（图6-12）。

④美化表格。选中数据源中的第1行，单击鼠标右键，选择【设置单元格式】，在打开的【设置单元格格式】对话框中选择【边框】，设置直线样式为虚线，【颜色】为灰色，【边框】位置为下边框。设置好第1行后，单击【格式刷】按钮，单击倒数第2行，使其应用相同的格式效果（见图6-13和图6-14）。

图6-12

图6-13

图6-14

3.制作微图表：迷你图与条件格式

①插入迷你图。选中B12单元格，在【插入】选项卡的【迷你图】功能组中单击【柱形】按钮（见图6-15）。

图6-15

②设置迷你图的范围。在弹出的【编辑迷你图】对话框中，【数据范围】选择的是B2：B10单元格区域，单击【确定】按钮。此时在B12单元格内已经生成了一个迷你柱形图，并且它是能够随着行列的大小变化而变化的（见图6-16）。

图6-16

③快速填充迷你图。生成第一个迷你柱形图后，选中B12单元格右下角的十字句柄+，向后拖动到H12单元格内，让每一列的数据都快速生成迷你柱形图（见图6-17）。

④美化迷你图。选中所有迷你柱形图所在的单元格区域，单击【设计】选项卡，可以通过单击迷你柱形图按钮右侧的小三角，快速调整迷你图的颜色，或者直接通过【样式】功能区调整迷你图的样式。通过单击【标记颜色】右侧的小三角，选择【高点】，将颜色设置为亮蓝色（见图6-18）。

图6-17

图6-18

⑤设置条件格式（另一种嵌入单元格内的微图表）。在【奖金总额】处设置数据条效果。选中H2：H10单元格区域，单击【开始】选项卡，之后单击【条件格式】→【数据条】→【浅蓝色数据条】。此时单元格中已经生成了类似于条形图效果般的数据条了。如果你手动更改数据源表中的内容就会发现，数据条的条件格式和迷你图都会随着单元格数值的变化而变化（见图6-19）。

⑥条件格式的美化。若在数据呈现时无须显示数据条上的数字，而只显示数据条的话，只需单击【开始】选项卡，之后单击【条件格式】→【管理规则】，找到对应的条件格式规则后，单击【编辑规则】，在弹出的【编辑格式规则】对话框中，勾选【仅显示数据条】复选框，单击【确定】按钮（见图6-20）。

图6-19

图6-20

4.制作控件并关联数据源

①插入【姓名】的组合框控件：单击【开发工具】选项卡，之后单击【插入】→【组合框（窗体控件）】，在表格的空白区域，拖曳绘制一个组合框控件（见图6-21）。

②绘制完成后，选中控件，单击鼠标右键，选择【设置控件格式】（见图6-22），在弹出的【设置对象格式】对话框中设置【数据源区域】，选择A2：A10单元格区域；设置【单元格链接】，选择J10单元格。设置完成后，单击【确定】按钮（见图6-23）。

图6-21

图6-22

图6-23

③将J10单元格的字体颜色设置为白色，方便我们后面进行关联计算。设置完毕后，我们单击组合框控件，选择不同的姓名，则会在J10单元格中显示该姓名处在A2：A10单元格区域中的第几位。比如选择“表姐”，则J10单元格显示2（见图6-24）。在J12单元格中输入公式=INDEX（A2：A10，J10），即在A2：A10的范围内返回J10单元格的值所在位数的姓名。比如J10单元格显示2，则INDEX计算的结果返回的是“表姐”（见图6-24）。

图6-24

④在K12单元格中输入公式=VLOOKUP（$J$12，$A$2：$H$10，COLUMN（B2），0），然后拖动至Q12单元格填充公式（见图6-25）。

图6-25

5.制作动态柱形图

①选中K12：Q12，单击【插入】选项卡，之后单击【二维柱形图】→【簇状柱形图】。此时Excel根据选定的数据源，自动插入一张默认的Excel柱形图（见图6-26）。

②选中图表区域中的垂直坐标轴，单击鼠标右键，选择【设置坐标轴格式】，在右侧的窗格中，将【坐标轴选项】的最大值调整为800。这样做是为了避免选择不同人员姓名时出现非统一标准的柱形图，对读图者造成视觉上的误解（见图6-27）。

③删除图表标题、网格线、垂直坐标轴，设置图表的颜色为无填充，字体为微软雅黑，字体颜色为白灰色（见图6-28）。

图6-26

图6-27

图6-28

④选中蓝色的柱形，在右侧【设置数据系列格式】窗格的【系列选项】中，将【分类间距】调小一些，比如调整为127%，使得柱形图变得宽一些（见图6-29）。

图6-29

⑤再设置柱形图的渐变填充效果：在右侧【设置数据系列格式】窗格的【填充与线条】选项卡中，将填充模式更改为渐变填充，线性方向设置为自上而下，颜色设置为亮蓝色到深蓝色。选中图表区域周围的边界点，将图表调整到合适的大小和位置区域，即完成柱形图的绘制（见图6-30）。

图6-30

说明：下面计算部门奖金总额（见图6-31）。

更改透视表求和的值为奖金后，我们发现柱形图的数据显示异常。这就是使用透视表来作为图表数据源的好处，当有任何数据变化时，所有的图表调整也随之更新（见图6-32和图6-33）。

图6-31

图6-32

此时我们只需重新设置柱形图坐标轴的范围区间，即在【设置坐标轴格式】窗格中将最大值调整为“100000”，则柱形图又呈现出之前设置好的样子了（见图6-33）。

图6-33

6.制作动态圆环图

①在J13单元格内输入文字“部门奖金总额”。在K13单元格内输入公式=SUM（表1（奖金）），就是对数据源表格的奖金列的所有数据进行汇总求和（见图6-34）。

图6-34

②在L13单元格内输入文字“个人比率”。在M13单元格内输入公式=Q12/K13，这是指每一位员工的奖金总额占部门奖金的比率（见图6-35）。

图6-35

③在N13单元格内输入公式=1-M13，构建出圆环图的数据源（见图6-36）。

图6-36

④选择M13：N13单元格区域，单击【插入】选项卡，之后在【图表】中单击【饼图】→【圆环图】（见图6-37）。

图6-37

⑤美化圆环图。删除图表标题、图例，选中整个图表，将【形状填充】设置为【无颜色】，【形状轮廓】边框设置为【无边框】。再选中绘图中的环形，将【形状轮廓】边框设置为【无边框】（效果参见图6-38）。

图6-38

⑥设置圆环图的配色方案。仅选中蓝色部分的圆环（即单击两次选中该部分），在【设置数据点格式】窗格中将颜色设置为渐变填充。然后，仅选中橙色部分的圆环，调整其填充颜色为色板中已有的亮蓝色并将透明度更改为84%。从而使图表呈现出一种有数据为亮蓝色、无数据为半透明的效果（见图6-39）。

图6-39

⑦将圆环图的幅宽设置得大一些。选中圆环图后，在【设置数据系列格式】窗格中将【圆环图内径大小】更改为62%。完成后，调整圆环图的大小和位置，使其放置在看板中合适的位置（见图6-40）。

图6-40

6.2.5完善看板

具体步骤如下。

①制作标题引用动态数据来源。

在O13单元格中输入公式=J12&＂的奖金比率：＂&ROUND（M13，2）*100&＂%＂。这个公式的作用是将下一步文本框中需要显示的内容先固化在一个单元格中，使其成为文本框显示的来源（见图6-41）。

图6-41

②绘制一个文本框，并在编辑栏里输入=$O$13。这时文本框里就出现了相应的数据，更改文本框的背景颜色为无颜色，边框颜色设置为无颜色，并修改其字体、字号及颜色（见图6-42）。

图6-42

将N13和O13中辅助数据的字体设置为与看板底色一样的深蓝色，即起到类似于隐藏的效果，使得整个看板的版面更加整洁（见图6-43）。

图6-43

③插入Logo。在本例中，以小火箭图片进行演示。从示例文件中选择小火箭的图片，也可以根据自己的需求放置其他图片，将其插入Excel中（见图6-44）。

图6-44

默认插入的图片会有白色的底色，这与本例中商务图表的整体风格相异。因此，我们要将图片中的图案抠出来。选中图片，单击【格式】→【删除背景】，此时图片会将需要删除的区域涂抹成“玫红”色块（见图6-45）。

图6-45

单击【标记要保留的区域】，将不删除的地方标记出来。设置完毕后，单击【保留更改】，即可通过Excel完成快速抠图（见图6-46和图6-47）。

图6-46

图6-47

调整图片大小后将其放置到合适的位置，即可完成本例所有图表看板的绘制工作了（见图6-48）。

小结：大家初见这张看板时，会感觉其中的元素很多。但只要做过一次，就会知道制作过程其实并没有大家想象中那么难。同理，本例中商务图表的制作思路，还可用在你工作中其他数据的有效呈现上。

图6-48