第28关 综合实战应用3
你好，又见面了，今天是Excel实战营最后一关。坚持学习到现在的你，曾经被乱糟糟的表格困扰，不太懂函数怎么写，插入的图表也只是默认的，但是通过这个一个月的学习和解惑，你一定收获良多。
接下来，我们即将进入最后一个实战训练——综合实战应用3。
今天，我们要帮一家制造公司的财务梦姐做一份公司年度费用分析看板，然后给老板汇报。
与上两关公司年度人员数据分析不同的是，这个看板的制作还用到了动态图表制作功能，等你做出这个看板，你就能真正掌握做一个动态看板汇报的全部流程和操作。具体涵盖以下知识点：
为了让你能主动地探索学习，我们在这一关中，特意省去了部分具体操作步骤，希望你能依靠自己前27节所学，亲手完成这个综合应用案例。
好了，请先下载今天的练习表格：
1.案例说明
我们先分析下案例：
梦姐从事财务工作以来，每年都会遇到被老板要求提供费用分析报告的情况。
因为对于企业而言，开源节流是关乎存活的大事，很多企业很重视销售数据的分析，但往往对费用分析不够重视。做好分析等于给企业做了一个诊断，往往通过费用分析可发现一些问题，从而未雨绸缪。
通过前面27关的学习，相信你一定知道了：汇报呈现最好的方式就是图表。
下面先看看阿狸老师把公司年度费用分析看板做成什么样了：
拿到这样一张图，你知道是怎么做出来的吗？
我们一起回顾一下看板制作的流程：
第一阶段，要根据看板，拆解出案例需要哪些数据。
第二阶段，根据所需要的数据，进行数据汇总。
第三阶段，制作对应图表，拼出看板。
2.案例拆解
我们仔细观察看板，可以发现需要以下6个维度的数据：
1、2019年费用总额
2、2019年费用增长率
3、2019年各类费用计划总额和实际总额
4、2018年各类费用总额
5、各类费用下二级费用总额
6、1-12月各类费用总额
拆解完案例，明确了制作思路，下面我们开始实操一步步做出看板。
3.数据汇总
打开刚才下载的练习文件，你是否看到了原始数据表呢？
1、2019年费用预算表
2、2019年费用核算表
3、2018年费用核算表
观察发现原始数据中有合并单元格，还包含各个分类的汇总数据，不方便我们对数据汇总。
所以我们需要对表格进行简单处理：
第一步，取消A列合并单元格，填充空白单元格为上一个单元格。（提示：定位快捷键：Ctrl+G，批量填充快捷键：Ctrl+Enter）
你做出来了吗？
第二步，删除汇总行，采用分类汇总功能进行汇总，做出如下效果：
（提示：分类汇总在【数据】选项卡下）
重复以上的操作，把【2019年费用预算表】和【2018年费用核算表】的表格进行相同的操作。这样我们就已经把原始数据整理好了。
补充完数据字段后，我们就对看板拆解出的7组数据依次进行整理：
我们新建一个工作表【作图数据】，将需要统计的数据框架先列在表格中：
1、2019年费用总额
2、2019年费用增长率
3、2019年各类费用计划总额和实际总额
4、2018年各类费用总额
5、各类费用下二级费用总额
6、1-12月各类费用总额
红色背景色的空白单元格是需要填充的作图数据，我们从三个原始数据表中提取。接下来，一个一个看如何取数。
1、2019年费用总额→【2019年费用核算表】总计
2、2019年费用增长率→(2019年费用总额-18年费用总额) / 18年费用总额
做出效果如下：
3、2019年各类费用计划总额和实际总额→【2019年费用核算（预算）表】费用分类汇总数据
4、2018年各类费用总额→【2018年费用核算表】费用分类汇总数据
完成效果如下：
5、各类费用下二级费用总额→【2019年费用核算表】二级费用分类汇总数据
6、1-12月各类费用总额→【2019年费用核算表】费用分类总计数据
做出如下效果：
到这里，已经完成了初步的数据汇总。我们再来看看最终的看板，还差哪些数据？
看板分为两个部分：
第一部分【总体情况】，可以直接引用数据或者插入图表。
第二部分【费用细分情况】，则是动态图表。
第一部分的数据已经有了，第二部分两个动态图表的作图数据还需要梳理：
我们先看各类费用趋势动态图，根据选择的费用类别，显示该费用全年的数据。
一起来回顾制作动态图表的步骤：
①插入组合框
②引用数据
③插入图表
对比一下，你是否做出如下效果？
类似地，各类费用占比动态图也按照以上步骤操作。这里阿狸需要提醒一下，在引用数据之前，需要先把各类费用对应的二级费用列表先列出来：
然后进行作图数据的引用：
① 添加辅助列数据：1-7，对应各费用在列表中的相对位置（第几行）；
② 用index函数引用对应的费用名称；
③ 用vlookup函数查找对应的费用总额。
再用vlookup函数进行查找对应费用总额时需要注意的是，销售费用和管理费用下的二级费用里都有人力成本，所以此处查找时我们可以借助“&”连接O列和P列新增辅助列以便用vlookup时进行精确查找。
最后，制作出如下效果：
完成了作图数据的汇总。接下来，进行看板制作：
4.看板制作
首先，根据作图数据插入对应类型的图表，并进行美化。
这里阿狸老师用的是以下配色方案，供你参考：
阿狸老师对4张图表选择如下，做出了如下效果，你是怎么选的呢？
图1：2019年计划&实际费用对比→温度图
图2：2019年费用同比增长分析→柱形图-折线图
图3：各类费用占比→饼图
图4：各类费用全年趋势→柱形图
看板组合第二步，新建工作表命名为【费用分析看板】，将图表和数据组合在一起：
这里有两点需要注意：
第一，费用总额3064.217转换成3,064万需要用到text函数；（提示：=TEXT(作图数据!A2,"000,0")&"万"）
第二，组合框控件剪切到新的工作表后，需要重新选择一下数据源区域和结果区域，如下图：
调整后，就做出了最终费用分析看板，你做出来了么？
恭喜你完成了公司年度费用分析看板的制作！今天，你把数据汇总、看板的知识点又综合应用了一遍：
最后，让我们一起思考，这份费用分析看板能得出什么结论，可以更好的帮助公司领导作出更有效的决策？阿狸老师提供两个方向供大家参考：
1、 生产费用和管理费用超出了预定的金额，明年是否需要增加预算？
2、 全年每月预算是否可以根据实际情况进行动态调整？
经过三天的综合实战应用，相信你不仅只是巩固了之前所学习的知识，还对Excel在实际工作中的应用有了更深刻的理解。
Excel实战营的学习到这里就结束了，但是学习这件事远不会结束。
阿狸老师认为，更好的学习是学以致用。
在实际工作中，你会遇到各种各样的应用场景，有些问题可以直接套用学过的知识解决，但更多的问题可能需要你先去思考分析，然后再用Excel对应的功能去解决。
学习就像是升级打怪一样，路上会遇到越来越难打的怪兽，但是每当你打败一个怪兽，你的自身能力也会变得越来越强！
阿狸老师也相信你的Excel应用之路，一定会比现在更精彩！扫描下方二维码，就可以获得价值99元的通关礼包，这也是一个完整通关的礼包！
最后，阿狸老师衷心祝福你，愿你能不断精进Excel技能，学以致用，让它成为你职场效率和能力的加速器！
