Excel最全子弹图讲解

浏览: 9480

前言

这是2017年EasyCharts团队第一篇文章。我们是一支热爱数据分析与可视化的青年团队,更多精彩内容敬请关注我们的微信公众号!


Clipboard Image.png

也祝大家新的一年里学习进步,工作顺利,加薪不断,能在公司里金鸡独立、一枝独秀!



今天跟大家分享的图表是——子弹图(bullet chart)

▽▼▽

名字听起来是不是很高大上呀,这个图表是用于日常绩效评估的,可以从图表中了解到各个项目的目标及实际进程等各项指标!在项目管理中经常会用到!

●●●●●

至于方法嘛,坦白的说,一点儿也不难,都是些之前推送过的文章里常用的技巧方法(次坐标轴、复合图表类型)。

先看下作图数据:

其中后三列数据只作为图表中绩效评估的衡量指标,前两列数据作为图表中的核心指标。

Clipboard Image.png

选中所有数据区域,插入——图表——堆积柱形图。

Clipboard Image.png

Clipboard Image.png

默认输出的图表行列并非我们所要的效果,需要调出选择数据菜单,互换行列位置。

Clipboard Image.png

Clipboard Image.png

我们需要对输出的默认堆积柱形图进行进一步的修改。首先需要将目标值系列的图表类型更改为带散点的折线图,具体方法如下。

Clipboard Image.png

Clipboard Image.png

并对更改成带数据点的折线图的目标值数据系列,进行更多的格式设置,隐藏其折线,将数据点更改为小横线。

Clipboard Image.png

Clipboard Image.png

Clipboard Image.png

然后开启实际值数据序列的次坐标轴,并调整该系列数据系列间距(320%)。

Clipboard Image.png

现在图表已经大体完成,剩余的任务是美化和修改各元素格式(字体、配色、排版)。

Clipboard Image.png

最后大功告成啦~


今天要跟大家分享的技巧是子弹图(Bullet Chart)在条形图中的实现!

▽▼▽

前一节分享了子弹图(柱形形式)的 制作技巧,这一片接着讲解子弹图在条形图中的实现方式!

●●●●●

原数据是一致的,只是想要做成条形图,需要额外增加一列辅助数据(G列)。

Clipboard Image.png

然后选择A~F列数据区域,插入——图表——堆积条形图。

Clipboard Image.png


很明显软件默认输出的并非我们想要的条形图,行列位置颠倒了。

这时候需要调出选择数据菜单,互联行列位置。

Clipboard Image.png

Clipboard Image.png


接下来的一步是,反转条形图的数据序列顺序(默认的与原数据区域是相反的)。

Clipboard Image.png

Clipboard Image.png

这时候关键的步骤来了,选中实际数据序列,更改图表类型为散点图,然后从新指定他的横轴(B列)、纵轴(G列)。

Clipboard Image.png

Clipboard Image.png


同样的步骤,选择目标数据序列(Object所在的C列),更改为散点图,然后从新指定它的横轴(C列),纵轴(G列)。

Clipboard Image.png

Clipboard Image.png

此时图表会变成如下所示的样子。

Clipboard Image.png

此时选择实际(Actual)数据序列,在图表设置菜单中,调用误差线设置菜单,设置其横轴负误差线,自定义,选择误差线范围为其自身的值(A列值)。

Clipboard Image.png

Clipboard Image.png

删除垂直误差线,并调整水平误差线的格式、线条粗细、颜色等。

Clipboard Image.png

插入一个垂直短线段(线条填充红色),并复制,贴入(直接选中Object序列)即可!

Clipboard Image.png

最后修改背景中的三个序列填充颜色(general、good、excellent)(注意颜色填充为同色系不同色调的)。

Clipboard Image.png


删除掉图表中不必要的冗余元素,修改字体、配色!

Clipboard Image.png


还要跟大家分享了关于sparklines迷你图插件的的第一类图表类型,尺度(Scales)图表类型,今天要分享的是第二个图表类型——在项目标管理中使用频率非常高的子弹图(Bullet)。

当然这些图表在之前的excel图表教程中都已经分享过具体做法及数据结构,有兴趣可以自己参阅往期教程。

下面是打开sparklines插件的bullet图表菜单:


以下是官方指导文档中的关于子弹图的参考示例:


函数内前三个标红的参数是该函数的必备参数(不能省略:想想也是,想要做这种绩效评价指标,你得告诉图表你的现状(实际值)、目标(目标值)以及值范围才能建立起完整的绩效评价体系)。

后面的黑色参数应该可以省略(可以省略并不代表在写函数的时候可以不用去管,只是可以不输入参数,但是仍然需要你给对应参数位置留空(用逗号隔开)。)

下面是每一个参数的具体含义及控制的图表元素:

Measure:程度,测量,就是你的指标变量,控制子弹图中的那条颜色最深最细的长条。

Target:目标,控制图表中红色竖线。

Maxi:最大值范围,控制整个图表的数据显示范围。

Good、Bad:优、劣;控制图表中位于图表底部的指标优劣范围,一般用很浅中性色系填充。

Forecast:预测;控制图表中位于中心位置最细的小条,用于与指标变量和目标值进行对比。

TickUnit:刻度线,显示在图表底部短线。

ColorScheme:颜色,控制图表底层good、bad以及最大值前的三段矩形区域颜色。

Vertical:垂直轴,该参数可以控制图表显示方向,默认为False,也就是水平。(一般常用水平,看着比较舒服)。

以下是完整的子弹图函数语法公式:(不熟练的情况还是需要打开函数输入框,如果很熟练的话,你可以直接在单元格中输入整条语法)

=bulletchart(D3,85,100,80,60,83,10,9868950)

Clipboard Image.png

Clipboard Image.png

下面可以看下将Vertical参数修改成True会变成什么样子?

=bulletchart(D3,85,100,80,60,83,10,9868950,TRUE)

Clipboard Image.png

果然效果也是很不错的,只是修改一个参数而已,这款神器果然了得,如果是在excel中使用内置图表来做,估计你得忙大半天。

第一个图表完成之后,就像在excel中填充函数公式一样,批量向下填充第一个图表单元格中的公式。

一排整齐划一的迷你字段图就出炉了。

Clipboard Image.png

至于倒子弹图(Reverse Bullet),打开看了一下,语法与刚才做的普通子弹图语法参数均一样,可是输完参数跑的时候VBA报错,不知何故。也有可能是输入参数非法(没有仔细研读变量类型限制信息),如果哪位感兴趣的小伙伴儿知道怎回事也希望可以分享给我,不胜感激!


所幸我们经常用到该图表普通的(方向朝右)形式的场合比较多一点儿,以上讲解已经能够满足日常使用需要。

Clipboard Image.png

推荐 2
本文由 EasyCharts 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录注册