Excel图表学习：创建子弹图

excelperfect

从Excel的角度来看，下面是子弹图的一些高级方面：

• 它们是组合图表，使用条形图或柱形图以及散点图。
• 使用主坐标轴和次坐标轴，但在很大程度上忽略了次坐标轴。
• 多个条形或列“显示”必须始终为零的值。
• 使用带有特殊格式的误差线。

 

可以想到三种创建子弹图的方法。

• 使用列或条形“三明治”，这是本文介绍的方法。
• 使用没有“三明治”的柱形或条形。
• 使用XY图。

 

这里使用方法1，可能是最容易设置的方法：

 

准备图表数据

下图1显示了子弹图报告中的每个图所依赖的数据。

图1

 

为了尽可能轻松地创建你的第一个图表，将新工作表的名称更改为“GG”，然后设置数据区域如图所示。在创建图表后，可以根据需要重命名工作表或移动数据。

 

黄色阴影的单元格包含在单元格中输入的数据值，图1中显示了这些数字。

 

单元格B2包含“poor”区域的最大值，单元格B3包含满意或“OK”区域的最大值，单元格B4包含该期间的实际值，单元格B5包含目标值。

 

将列A中的文本指定为列中对应黄色单元格的名称。

 

以绿色阴影显示的单元格由图表的SERIES公式引用。下面是单元格中使用的公式：

B8：=Target

B9：2

 

单元格B8确定Target（目标）值，单元格B9将目标值的指示居中放置在三个图表的中心，这些图表组合起来创建该子弹图。

 

单元格B12和B14始终为零，单元格E13包含Actual（实际）值，显示为黑色条形。这些单元格中的公式是：

B12：0%

B13：=Actual

B14：=B12

 

单元格C12和C14包含最大不满意数量的值。如果实际值大于最大不满意数量，则单元格 C13包含零；如果实际值小于最大不满意数量，单元格C13将填充黑色列上方的空白。如果实际值小于零，则单元格C13包含与单元格C12相同的值。公式是：

C12:=MaxPoor

C13:=MAX(MaxPoor-MAX(0,Actual),0)

C14:=C12

 

如果OK数量大于不满意数量，则单元格D12和D14包含正差异值；否则，它们包含零。单元格D13包含填充暗条顶部和OK数量顶部之间的间隙所需的数量。公式是：

D12:=MAX(MaxOK-C12,0)

D13:=MAX(MIN(MaxOK-MaxPoor,MaxOK-Actual),0)

D14:=D12

 

创建柱形子弹图

1.首先创建一个标准堆积柱形图。为此，选择单元格区域B12:D14，在功能区“插入”选项卡中，选择“插入——柱形图——堆积柱形图”。

 

2.因为在这种情况下Excel应用了错误的行/列分配，所以选择功能区“图表工具”选项卡“设计——切换行/列”。

 

3.选择并删除图例，此时的图表如下图2所示。

图2

 

注意，这些图表标有1、2和3。在上图1所示工作表单元格B9中输入的值2指向中间的类别2。

 

4.选择其中一条网格线，按Ctrl+1启动其格式设置窗格。在线条颜色选项卡中，选择无线条。

 

5.单击任一柱形，按Ctrl+1启动“设置数据系列格式”窗格。在系列选项中，将分类间距设置为0。现在的图表应该看起来如下图3所示。

图3

 

注意蓝色条是如何夹在红色和灰色条之间的，这就是我将其称为“三明治”方法的原因。

 

6.选择绘图区。通常，最简单的方法是单击图表边缘以选择图表区域，然后按一次向上箭头，选择绘图区域。

 

7.按Ctrl+1启动“设置绘图区格式”窗格。在“填充”选项卡中，选择“纯色填充”，然后选择一种浅蓝色。在“边框”选项卡中，选择实线，然后为线条指定浅灰色。

 

8.选择其中一柱形并指定与上面显示的子弹图颜色相对应的颜色。完成每种颜色的分配后，图表应如下图4所示。

图4

 

9.因为我们不需要X轴标签，只需选择X 轴并按Delete。

 

10.要添加目标条形，首先选择图表的边缘，以便看不到系列公式。然后将下面的公式复制并粘贴到公式栏中：

=SERIES(,GG!$B$9,GG!$B$8,4)

 

如果你的工作表数据区域与上图1相符，并且如我所述将该工作表命名为GG，则你的图表应如下图5所示。

图5

 

11.该图表显示橙色柱形，因为它自动使用与其他类型相同的图表类型。因此，我们需要把它改成散点图。为此，选择橙色柱形，选择功能区“图表工具”选项卡“设计——更改图表类型”。在XY（散点）选项卡中，选择“带直线的散点图”，如下图6所示。

图6

 

12.将系列4移动到主坐标轴。首先确保选择了系列4，然后按 Ctrl+1 启动“设置数据系列格式”窗格。在“系列选项”中，选择系列绘制在主坐标轴。

 

13.现在显示默认误差线，首先确保选择了系列4，然后选择功能区“图表工具”选项卡“设计——添加图表元素——误差线——其他误差线选项”，此时的图表如下图7所示。

图7

 

这里，我们不需要垂直误差线，可以选择删除它。

 

14.选择水平误差线，按Ctrl+1启动“设置误差线格式”窗格，设置其末端样式为“无线端”，固定值为“0.6”。线条为“实线”，颜色为黑色，宽度3磅。此时的图表如下图8所示。

图8

 

15.将图表调整为想要的大小并进行你想要的一些其他格式，最后的图表效果如下图9所示。

图9

 

创建水平子弹图

与上文介绍的类似，只是使用堆积条形图。

 

与上文有所区别的是：

• 使用垂直误差线创建目标指示，而不是水平误差线。
• 系列4的公式是：=SERIES(,GG!$B$8,GG!$B$9,4)。

 

所得到的图表效果如下图10所示。

图10

 

注：本文学习整理自exceluser.com，供有兴趣的朋友参考。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。