Excel数据可视化商务图表-目标达成差额完成率柱形折线组合图年度汇报必备（建议收藏积灰备用）

本节动图中，鼠标左键为黄色，右键为红色；

这一节是猪爸爸学excel之Excel数据可视化商务图表-计目标达成差额完成率柱形折线组合图（建议收藏积灰备用）

年关将近，职场牛马最头痛地莫过于年度总结了，别头痛，良方来了。今天这个目标达成及差额完成率柱形折线组合图，相信必定能够让你的头痛缓解。图表包含了：

2个支柱1个要点：

2个支柱，分别是计划柱子，达成柱子，计划为虚线柱子，达成为带颜色柱子；两个柱子重叠在一块，计划在前，达成在后，这样就能够很明显的看出完成情况及差额。

计划与达成的柱子，还用虚线箭头，从计划指向达成，辅助加强阅读理解。

计划数据标签在柱子底部，达成标签在柱子中间， 同时还引进了散点图来辅助制作差额数据在柱子的侧边。各数据有序排列，清楚明了。

1个要点，则是指图表第二层的自动分组颜色圆形完成率，通过公式将完成率区分成低于100%和高于等于100%两组，数据变化，颜色自动变化，图表更智能。

这么简洁实用的图表，实属工作旅行，必备良品，建议收藏积灰，赶紧拿来制作年度数据，争取升职加薪的机会~~

1、制图第一步，数据先整理

还是老生常谈，好的图表，肯定是需要辅助数据的，所以我们需要数据如下：

D列差异值D2公式=C2-B2，下拉复制，自定义单元格格式：+#,##0;-#,##0

E列完成率E2公式=C2/B2，下拉复制

F列为补充标签X轴定位，如图所示，微调数值大小可以调整标签左移右移到合适位置

G列为补充标签定位Y轴，G2公式=MAX(B2:C2)-ABS(D2)/2

H列通过IF函数判断完成率小于100%的返回对应值，条件不成立则为na

I列通过if函数判断完成率大于等于100的返回对应值，条件不成立则为na

J列为次坐标轴辅助数据，J2公式=-MAX(E2:E7)*1.5，将次坐标轴的折线图“提高”，1.5系数有必要可以再放大

K列为主坐标轴辅助数据，K2公式=MAX(B2:C7)*2，将主坐标轴的“柱子”往下压，2系数有必要也可再放大。

2、插入柱形图并组合图表

我们选择A1:C13以及H1:I13数据，【插入】-【柱形图】。在图表任一区域，右键【更改系列图表类型】，将2组“完成率”改为【带数据标记的折线图】，勾选【次坐标轴】。图表的雏形就出现了，这时候会发现柱子与折线“挤”在一起了，这个是小问题，等会引进2个自动调节的系列即可。

3、引进自动调节主次坐标轴数据

在网上很多其他教程，都会和你说，你将主坐标轴的【边界】-【最大值】手动调大，这样子就可以将柱子“压缩”下去，如果有必要，就可以再将次坐标轴的【边界】-【最小值】手动调小，这样子，就可以将“折线”升上去。但是，每当数据变化，我们都要去手动设置，多麻烦啊。所以，我们需要引进这2个小数据。也就是前边提到的J和K这2个宝贝了。

选择J1J：K2数据，复制粘贴到图表上，右键【更改系列图表类型】，将“次坐标轴自动调整”系列设置为【折线图】，勾选【次坐标轴】；将“主坐标轴自动调整”系列设置为【折线图】，不要勾选，注意，是不要勾选【次坐标轴】，这样子，我们就一劳永逸了，再也不需要每次都麻烦的去调整坐标轴了。

4、修改柱子系列

在上一步，【图表标题】和【网格线】，这2个不必要的元素，我顺带着就给删除了哈。接下来，双击任意柱子，调出设置窗口，设置【系列选项】中的【系列重叠】为100%，【间隙宽度】为100%。你看，两个柱子重叠起来了。

再设置“计划”系列【填充与线条】为【无填充】、【边框】为【实线】，【颜色】设置为深色，【宽度】自己看着调整，【短划线类型】为【短划线】，这样子，“计划”系列就变成虚线柱子了。

而“达成”系列，【填充】选个合适的颜色，【边框】为【无】即可。

如果你跟着一步一步做了，你会发现一个问题？

达成比计划高的，计划的头顶没了？给挡住了？

小问题，立马解决。

5、解决柱子谁前谁后问题

右键【选择数据】，调出【选择数据源】窗口，将【图例项】中的“计划”下移到“达成”下方，搞定（敲黑板，注意了，这个就是不同系列，在图层中，谁压过谁，谁盖过谁就可以通过这里进行设置）。

6、设置2组折线系列添加数据标签

这个简单，2组折线均设置【线条】为【无线条】，【标记选项】为【内置】，选择圆形，大小根据图表实际调整，本例设为25；不同的仅仅是，低于100%的，将标记点的【颜色】设为红色，【边框】为无；而高于等于100%的，则选择一个好看的颜色即可。

在鼠标右键分别添加数据标签，并在设置窗口设置【标签位置】为【居中】，对了，红色圆点的，记得字体改为白色，这样子对比度高，数据才看得清楚。

7、添加补充标签散点图并添加数据标签

这个文字描述有点烦，在其他文章也经常提到，我就不写了，直接看动图哈。

第一张图是如何添加散点

第二张图是演示添加数据标签并指定数值及X轴的微调

‍

用散点图的好外，就是如果你觉得位置不合适，可以通过适当的调整F列X轴的数值系列，改变散点位置，从而灵活的调整数据标签的位置。

还有点迷糊的小伙伴，看这里：有详细的说明散点图的添加方式

8、为“计划”系列添加误差线制作虚线箭头

2组柱子的数据标签我顺带着添加了，“计划”系列的【标签位置】为【轴内侧】，“达成”系列的【标签位置】为【居中】。

接下来，选择“计划”系列柱子，在【图表元素】勾选【误差线】，选择误差线，设置【就误差线选项】为【正偏差】、【无线端】、【误差量】自定义，指定值为D2:D13；再设置误差线的【颜色】、【宽度】、【刮划线类型】为【方点】、【结尾箭头类型】选择合适的箭头，搞定。

9、美化

这个就是常规操作了，隐藏坐标轴，记得不是删除，删除不必要的图例；然后插入几个【文本框】制作标题、副标题，数据说明等。就不再赘述了。

大脑：恩，看懂了~

手：你说啥？鼠标要点哪里？要怎么点击？

光看不练，上阵头晕，大家多多练习下，才能真正掌握哦~~

记得将我【设为星标】，我们才能不失联哦~~

我的财宝吗？想要的话就给你吧，去找吧！我把世界的一切都放在那里~~

今天的分享就到这里，大家生活愉快~~

求关注

求转发