第24关 动态图表制作
上一节课我们学会了商务图表的美化，好的呈现为我们前面辛苦做出的成果增分，今天我们来进一步学习动态图表的制作，让汇报更上一层楼！
请下载今日实操练习资料【第24关-动态图表制作】：
这是某公司的年度销售数据，我们要根据已有的数据源，制作该公司2019年度业绩报表。最终效果如下：
1. 汇总数据
打开【年度销售数据】表格，我们来观察一下：
表格中的日期是根据每一天进行统计的，整个表格有近900行，这样我们很难进行数据观测和进一步处理。
我们先来通过透视表进行数据汇总，按月份进行业绩统计。回忆一下数据透视表的制作过程。
将销售地区拖到行字段，日期组合成月，拖到列字段，销售金额拖到值字段。效果如下，你做出来了吗？没有做出来的同学需要复习一下透视表部分的知识点了哦！
透视表创建好以后，将【目标销售数据】工作表中的表格复制【sheet1】:
接下来复制好以后，我们用GetPpivotDdata函数，将透视表中的数据引用到刚刚复制好的表格中。这里使用GetPivotData函数可以引用是因为该函数的引用类型是内容引用，即直接引用> 透视表中的数据。和地址引用（即单元格引用）的区别是在透视表值不改变的情况下，引用值不随透视表位置的改变而改变。
勾选好以后，我们就可以引用透视表中的数据，自动生成公式了。我们引用透视表中1月份安徽的销售数据到E19单元格中试一下：
E19单元格出现公式：=GETPIVOTDATA("销售金额",$A$3,"销售地区","安徽","月",1)。这里有6个参数，我们一一来看一下，每个参数的含义：
这里每个参数都是绝对的，我们想用单元格引用，适用于其他地区、其他月份数据引用。就需要调整一下函数公式。
【注意】
第4个参数，行字段对应的项目，引用A19单元格，注意需锁定列
第6个参数，列字段对应的项目，引用E18单元格，注意需锁定行
设置好公式后，向下向右填充，透视表内的信息就都规范引用到下表了。效果如下：
不过还有两列是空白的，我们先用函数引用总业绩， 各地区的总业绩其实就是总计列的数据。
引用时，前面4个参数仿照上面的公式书写即可。那这里列字段和对应项目写什么呢？其实不用写，只写行字段及其对应的参数，引用的数据就是总计的数据就可以了 。
=GETPIVOTDATA("销售金额",$A$3,"销售地区",$A19)
下拉，总业绩就都算出来了。紧接着我们计算完成率并调整成百分比格式（完成率=总业绩/目标），结果如下：
数据已经整理好了，接下来我们就可以开始制作动态图表了。
2. 插入组合框
之所以要做动态图表，就是因为我们想通过点击地区就能查看对应的图表。这就像切片器一样。Excel有一个控件也能实现相同的功能，就是组合框。根据下图指示我们就可以找到组合框：
如果你根据发现上方没有找到【开发工具】，就需要打开Excel选项设置一下，操作如下：
找到组合框以后，我们来设置组合框参数。
组合框有三个参数：
数据源区域：选择一个列表，用于选择。这里选择销售地区列表
单元格链接：点击空白处任一单元格即可。为了同一教学，大家选择A32单元格
下拉显示项数：可以随意设置。这里就用默认的8
点击【确定】后，控件就设置好了，我们可以尝试点击组合框右侧小三角，选择【广东】。A32单元格会返回3，也就是“广东”在销售地区列表中位置：
使用组合框已经实现了切片器点击切换的功能，那我们怎么把组合框和要展示的图表连接起来呢？
3. 作图数据
通过前面三关的学习，我们知道了数据源不等于作图数据。我们来看看，我们想做哪些图，来推出需要哪些数据。首先确定做什么图（温馨提示：下面的两张最终效果图只需要欣赏，先别> 忙着做噢，后续有详细的步骤指导 ）
① 各地区业绩完成情况（温度图-目标+实际完成率）。最终效果如下图：
做出这样的效果图需要三个数据：
各地区业绩完成率
完成率100%数据
当前地区业绩完成率
② 各地区全年业绩趋势。最终效果如下图：
以上效果需要三个数据：
当前地区1-12月份业绩总额
当前地区业绩最大值
当前地区业绩最小值
明确了所做图表也清楚了制作图表所需要的数据，接下来我们就用【index函数】计算出所需数值。
【注意】这里我们要利用index函数返回选项名称，而不是选项位置
紧接着我们根据作图需求，算出辅助数据根据刚才拆解所需的数据， 我们只需复制上面表格地区和业绩完成率数据，添加一列完成率对比数据100%。最后添加一列当前地区完成率。效果如下：
要判断当前地区的完成率，我们可以使用【if函数】。例如，当前地区是“广东”，在第4列里，只有D38单元格显示67%，其他单元格显示#NA。显示#NA，作图的时候，数据点不会显示。
这里用if函数判断，条件为：A36=A33。这里需注意A33单元格需要绝对引用。成立结果：B36，不成立结果：NA()。
根据上图提示书写好公式后，第1张图表作图数据就出来了。结果如下，你做对了吗？
我们再来根据刚才拆解所需的数据，确定第二张图的作图数据表格
当前地区1-12月份业绩总额
当前地区业绩最大值
当前地区业绩最小值
首先我们要清楚：A50单元格当前地区要引用的是A33单元格；1-12月销售业绩需要用index函数从上面的表格引用数据。
我们可以理解为：=index（从哪找，第几行）
公式设置好以后我们就可以开始计算【最大值】和【最小值】了：
【最大值】
条件：B50=最大值，最大值=max（数据区域）
成立结果：B50
不成立结果：NA()
类似地，我们也就可以自己求出最小值系列数据。第2张图表作图数据也就做好了，结果如下，对比一下看看自己算对了没有吧！
4、创建图表
数据都计算好了，我们就可以开始做图表了。首先我们来创建各地区业绩完成情况（各地区业绩完成情况=目标+实际完成率）的柱形图。效果如下：
然后参照我们已经学过的温度图制作步骤，先更改图表类型，后调整系列间距。操作如下：
紧接着我们就可以进行图表美化了，这里可以参考下图提供的配色方案进行颜色更改：
颜色设置好以后，再进行细节调整：添加数据标签，添加图表标题，删除多余信息。各地区业绩完成情况柱形图就做好了！效果如下：
接下来我们创建各地区全年业绩趋势的折线图。先创建基础表格，如下图：
然后设置三个系列数据的格式。最大值、最小值在图上表现为一个点，因此设置它的数据标记格式：最小值：绿色实心圆圈；最大值：橙色实心圆圈。再为系列”广东”设置线条颜色。具体操作如下：
为了查看方便，我们可以再给最大值和最小值添加上数据标签，如“最大值，92.6万”。操作很简单，只需要在标签选项中，勾选系列名称和值。
92.6万是怎么显示出来的呢？在数字选项中，设置自定义格式：#0!.0,”万”就可以了，具体操作如下：
同理设置最小值的数据标签即可。接下来我们添加图表标题为【2019年xx（地区）业绩趋势】，并链接到单元格。
需要注意：因为销售地区不固定，随组合框的选择改变而改变，所以第一步，我们先在F31单元格生成一个标题。用&连接文本和单元格；第二步，图表标题直接引用F31单元格。操作如下：
最后再美化一下图表。将作图数据格式也调整为以万为单元格显示，设置图表背景颜色如下：
5、设置条件格式
图表都做好了，我们只需要把数据和两张图以及控件组合在一起，表格套用一下表格样式，就完成动态图表看板的制作了！
我们先来添加表头：
然后结合控件突出显示当前地区数据。我们可以发现，点击不同的地区，两个图会随之改变。这里我们也可以在点击不同区域时，突出显示该地区所在行的数据。
首先选中整个表格，然后新建规则：当A59单元格等于当前的地区（A33单元格）时，填充为橙色。
注意：A59单元格需要锁定列，A33单元格需要绝对引用
点击确定后，我们的动态数据看板就制作完成了！效果如下，你做出来了吗？
6.课程总结
这一节课，我们主要学习了动态图表的制作，再这个过程中应用到了大量前面课程学习的知识。我们来回顾一下：
7.下关预告
恭喜你学完了图表部分的内容，轻轻扫描下方二维码，就可以获得价值99元的通关礼包！
下一关我们将一起学习Excel表格完成后的打印和文件保护相关的知识，敬请期待！
下载表格
第24关-函数礼包24 统计函数
