在excel直方图中添加目标行

需要制作的图表要求如下：

每组是一年，如2007年、2006年、2005年等。这样可以对比往年的数据。

每组的两个数据中，一个是收入竖条，一个是综合比率条，两者互不相关(即一个的上升不会影响另一个)。

对于这些柱状条中的每一个，都需要一条目标指示线，该指示线是横跨柱状条的小水平线(或者如果没有达到目标，则是柱状条上方的小水平线)。这条线没有穿过整个图表，每个单独的条形图都有一条单独的水平线。

对于收入条，如果该条与目标直线相交，则该条为绿色；否则就是红色的。

对于比率/百分比条，如果该条穿过目标线，则该条为红色；否则是绿色的。

生成的图表的效果如下图1所示。

图1

制作上述图表的示例数据如下：

图2。

首先，我们需要在创建图表之前进一步调整数据。因为我们希望条形图自动改变颜色，所以我们需要使用堆叠直方图。因为我们想要聚合堆叠的列，所以我们需要交错数据并插入适当的空白行。数据需要一行列标题，第一个聚合左侧有一个空行为空，接下来的两行是第一个聚合的两列，接下来的两行是两个聚合之间的空白，相同的空行是两个聚合之间的空白，接下来的两行是最后一个聚合的两列，最后一个空行是最后一个聚合右侧的空白。调整后的数据如下：

图3。

a列和b列的某些单元格中的“a”是一个临时占位符，稍后将被隐藏。c列和d列中的实际数据不会被绘制，但将用于计算。f、g、I和j栏中的公式如下：

单元格F3:=if (C3=E3，C3，0)。

单元格G3:=if (c3e3，c3，0)。

单元格i4:=if (D4=H4，D4，0)。

单元格j4:=if (d4h4，d4，0)。

在图3所示的数据表中，颜色用于区分不同的数据。蓝色单元格是实际绘制图形中的Y值，绿色单元格用于图形中的系列名称，紫色单元格用于图形的类别轴标签。

让我们开始制作图表。

选择单元格区域A1:B11，同时按住Ctrl键选择单元格区域E1:J11。插入“堆叠柱形图”，如下图4所示。

图4

调整图表中的系列颜色：红色和绿色适用于收入系列和比率系列，目标值为蓝色。系列颜色调整完成后的图表如图5所示。

图5

数列作为条是没用的，所以我们把它转换成XY数列，把标记隐藏起来。首先，右键点击利润目标系列，选择“更改系列图表类型”，选择XY图表，删除副坐标轴，如图6所示。

图6

对比率目标系列执行相同的操作，结果如图7所示。

图7

选择“利润目标”系列，添加“误差线”，设置误差条格式：方向选择“正负偏差”，末端样式选择“无线端”，误差量固定值设置为“0.5”，线条颜色和蓝色选择“实线”，线宽设置为1.75。

对“比率目标”系列也这样做。

以上两个操作完成后的图表如下图8所示。

图8

最后，格式化图表：删除图例，删除数据表中的“a”使单元格为空。结果如图9所示。

图9