Excel数据可视化商务图表-适用于计划实际差异值完成率或同比环比等多指标多层柱形图（建议收藏积灰备用）

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

这一节是猪爸爸学excel之Excel数据可视化商务图表-计划实际差异值完成率或同比环比等多指标多层柱形图。

最近分享的基本上都是柱形图与其他的图形相结合的比较商务风的图表，今天我们来一个多层柱形图，图表包含了：

第一层：最基本的每月计划值、实际值；

第二层：计划与实际的差异值，并且根据正负差异值，分成了2组自动变化颜色的柱子；

第三层：完成率（使用了大头针图），并且也以100%为分界点，区分有2组自动变化颜色的大头针；

当数据发生变化时，第二、三层对应图表颜色均会自动变化，相当实用。

图表特色：

第一、二层柱子用的数量级别是相当的，这样子更适应平时的阅读习惯；

第三层因为完成率为百分比，数值偏小，为了在图表能够正常显示，做了系数放大处理。

整个图表，主要是利用了散点图+误差线这个高阶图表经常用到的利器，制作出来。

第二、三层的坐标轴旁，还增加了名称指示（对应数据更改的话，名称指示也同样自动更改），让读者能够清楚地知道对应的是什么数据。

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

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

这次的辅助数据稍微有点多哈，没办法，谁让我们要求多呢。

差异水平轴线：数据为H1=(MAX(B2:C13)+MAX(ABS(D2:D13)))*1.5；这个是综合考虑BC列计划与实际的最大值以及D列差异值的最大绝对值，再乘以系数1.5得来，避免第二层太矮了而和第一层重叠。

完成率水平轴线：数据为H2=(H1+MAX(D2:D13))*1.5，这个是让第三层与第二层能够错开的距离。

两个单独散点定位值为0.5，这个能够让制作的第二三层坐标轴及名称标签在图表靠左的位置。

完成率放大系数：数据为H4 =MAX(ABS(D2:D13)),这个是因为完成率是百分比，数值小，不放大的话，在图表上无法显示出来。

接下来就是第二层的数据差异值，差异值为实际值减去计划值，我们想分成正负2组颜色，所以其实就是要将数据分成2组,

J1差异组(正）：J2公式=IF(D2>0,D2+$H$1,NA())，判断差异值大于0的，也就是超额完成了的为一组，公式复制下拉到J13；

K1差异组（负）：K2公式=IF(D2<=0,D2+$H$1,NA())，这组是差异值小于等于0的，也就是未完成，同样公式复制下拉到K13；

最后就是第三层的完成率相关数据。

完成率放大：M列数据，M2公式=E2*$H$4，公式下拉复制，这个是等会用于制作完成率大头针的长度需要的数据；

另外完成率（低于100%）：N2公式=IF(E2<=1,M2+$H$2,NA())；

完成率（超100%）：O2公式=IF(E2>1,M2+$H$2,NA())，这个与第二层的判断原理一样，就不再赘述。

2、插入柱形图

这个是最简单的一步，选择A1:C13区域，【插入】-【柱形图】，搞定。在设置窗口【系列选项】，设置【系列重叠】为0%，【间隙宽度】为50%。【水平网格线】删除掉。

3、添加第二层坐标轴散点

这一步也不难，在图表区域，右键【选择数据】，弹出窗口，点击【添加】，在【编辑数据系列】窗口，【系列名称】为G1，【系列值】不用修改，点击【确定】，返回上一个窗口，继续【确定】。操作没问题的话，你会见到【图例】多了个“系列3”。

在图表区域，右键【更改系列图表类型】，将“差异水平轴线”系列修改为【散点图】，【确定】。

再重新回到【选择数据】，选中“差异水平轴线”系列，点击【编辑】，指定【X轴系列值】为H3，也就是0.5数值，【Y轴系列值】为H1，这样就完成了第二层坐标轴的起点位置了，同时也是第二层的名称标签来处。

4、添加误差线及数据标签

选中该散点，在【图表元素】勾选【误差线】。在【设置窗口】找到该散点的Y误差线，删除不要。再找到该散点的X误差线，也就是“系列“差异水平轴线”X误差线”，设置【误差量】-【自定义】指定值，【正错误值】为12（本例为12个月，所以对应用12），【负错误值】为0，【确定】。再设置该误差线的颜色为浅灰色。

选中该散点，右键【添加数据标签】，设置【标签选项】为【单元格中的值】，指定显示D1单元格的值，调整标签到合适位置。至此，该散点完成使命，我们将其【标记点】设置为【无】隐藏起来。这里，我顺带将纵坐标轴的【标签】-【标签位置】也给设置为【无】了（录屏有30秒的限制，超了上传不了，所以这些比较简单的操作就不录进去了）。

5、添加“差异组正”散点

继续右键【选择数据】，在弹出的【选择数据源】窗口，我们继续【添加】，在新弹出的【编辑数据系列】窗口，设置【系列名称】为J1，【X轴系列值】为A2：A13，【Y轴系列值】为J2：J13,这样，“差异组正”的散点就添加进来了。

6、添加误差线制作“差异组正”柱子

选中该散点系列，同理在【图表元素】勾选【误差线】。这次我们只要Y误差线，不要X误差线，不要删除错了哈。再对该系列的Y误差线设置【误差线选项】-【负偏差】、【无线端】、【误差量】指定值为D2：D13区域，这样子，这么细的柱子？？没事的，我们在【填充与线条】将其【宽度】设置为20磅，修改个颜色，搞定。

7、添加数据标签及散点隐藏

同理，我们对这组散点添加数据标签，引用单元格中的值为D2：D13区域。最后，狡兔死，良狗烹，这组散点完成它的使命了，让它隐退吧，我们同样将其标记点设置为【无】。

另一组“差异组负”的操作，可以说是依样画葫芦就可以了，这里就不再赘述了。

8、添加第三层坐标轴散点+误差线+数据标签

这个和步骤3+4一样，只不过散点的Y值是H2，差别就只是这个而已。我简单录了个动图，不明白的可以参考看看。

添加数据标签

9、添加“完成率（低于100%）”系列散点及误差线

这个和之前分享过的一个大头针图一样操作，散点标记点就用圆点，散点添加误差线，只要Y误差线，不要X误差线，通过误差线与标记点，就组成了类似大头针一样的图形了。详细操作看动图。之前文章我放结尾那里。

那么“完成率（超100%）”其实也就是一样的了，只不过是引用的数据变成了O列而已。就不再展开了，不然文章就像裹脚布一样又臭又长了~~

对应制作视频版在此，动图可能不够详细，加上视频辅助更好理解：

大脑：恩，看懂了~

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

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

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

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

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

求关注

求转发