制作子弹图的最简单方法

子弹图-Bullet图表-是一种用于展示目标与实际,或者数据评级(优良中差等)的非常有用的图表。但遗憾的是,在Excel中并没有这种基础图表,当然我们可以用条形图等基础图形模拟,但是比较麻烦。今天我们介绍一种非常简单的制作子弹图的方法。

子弹图介绍

下图就是一个子弹图(Bullete图表)的实际例子:

子弹图可以让你很容易对目标和实际数量做出对比,并且能够对数据做定性分析(好/坏,或者优良中差等等)。

下面是子弹图各元素的图解:

下面来介绍制作方法。

制作方法

整个制作过程只要3步:

1. 准备图表工作表

这个制作方法只用单元格+条件格式来完成的。所以,我们准备一个工作表:

新建一个叫做“子弹图”的工作表,选定要用的区域:D4:W8,共20列。

我们这里选定了20列,就是要用这20列模拟从0-最大值之间的数字。比如,如果最大值是1000,那么每列代表50。如果你希望得到更好的精度,可以选择更多的列,比如100列。

调整这20列的列宽,推荐设定宽度为:3.14。

在D4:W4区域填写一个序列:1-20:

调整第7行的行高,用于模拟刻度线。用的是单元格边框。

在第8行对应刻度线的位置,合并相邻的两个单元格,输入坐标轴标签数字:

2. 准备数据

数据表如下图:

我们演示的是用的横表,实际中你也可以用竖表。

第一行是我们实际的收入数值。第二行是在图表上显示的数字。

先看中间的“优秀”数据,实际是1000,因为图表用20个单元格模拟,所以显示值最大是20。

其余的的(绿色加亮)的单元格是公式,例如,C4单元格的公式是:

=ROUND(C3/$E$3*$E$4,0)

实际上就是按照比例缩小。

3. 创建条件格式规则

接下来就可以创建条件格式规则了。

其中,针对单元格D4:W6创建了4个规则:

  • 差-如果满足这个差的条件,就填充深灰色,公式是:
    =D$3<=数据!$C$4

  • 一般-如果满足这个一般的条件,就填充浅一些灰色,公式是:
    =D$3<=数据!$D$4

  • 好-如果满足这个好的条件,就填充更浅一些的灰色,公式是:
    =D$3<=数据!$E$4

  • 如果单元格对应的值正好等于目标值,就显示右边框,公式是:
    =B$3=数据!$F$4

针对单元格D5:W5,创建了一个规则:

  • 如果单元格对应的值小于等于根据实际收入计算的显示值,就填充为黑色。公式是:
    =D$3<=数据!$G$4

子弹图已经创建完成了(记住隐藏第3行的序列数字)。

总结

由于创建子弹图时,我们调整了工作表的列宽行高,所以一般情况下,我们不直接使用这个子弹图。我们会用照相机,在真正需要子弹图的地方,显示的是照相机的“照片”(关于怎么用照相机,请参见本公众号其他数据可视化的文章)。

在用条件格式方法创建子弹图时,要注意条件格式的顺序:后面的条件格式的效果会覆盖前面的条件格式的效果。你可以随时在管理规则对话框中调整规则的顺序:

我们创建了横向的子弹图。实际上用同样方法,你也可以创建竖向的子弹图。

除了用本文介绍的条件格式外,还可以用条形图或柱形图来制作子弹图,具体方法我们会在以后的文章中介绍。

取得本文模板文件的方式:

  1. 关注本公众号

  2. 点击底部菜单“联系客服”,与客服取得联系,索取“子弹图”模板文件


(0)

相关推荐