62

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

621案例情况介绍

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

图6-1

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

图6-2

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

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

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

622整理数据源

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

2计算核定奖金（单价）。在g列中输入核定奖金（单价），其中g2单元格的公式=if（（@是否完工）=＂完成＂，vlookup（（@bug类型），$k$2：$l$7，2，0），0）（见图6-4）。

图6-3

图6-4

3计算奖金。在h列中的数据为奖金（即最终发放给个人的项目奖金），其中h2单元格的公式=if（（@是否完工）=＂完成＂，vlookup（（@bug类型），$k$2：$l$7，2，0）（@核定工时），0）（见图6-5）。

图6-5

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

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

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

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

623制作数据透视表

1创建透视表

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

2设置透视表的布局

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

图6-6

图6-7

624制作图表看板

1制作背景板

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

图6-8

2制作绘图数据源

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

图6-9

将相应的数据从透视表中运用vlookup函数匹配进去，在b2单元格内输入公式=vlookup（$a2，＇2透视表＇！$a：$h，lu（b1），0）。

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

图6-10

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

图6-11

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

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

图6-12

图6-13

图6-14

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

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

图6-15

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

图6-16

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

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

图6-17

图6-18

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

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

图6-19

图6-20

4制作控件并关联数据源

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