excel图表如何创建动态的目标线

一个在柱状图中创建动态目标线的技巧，如下图1所示，调节图表右侧的滚动条，可以看到左侧图表中用作目标线的红色直线作相应的调整。

图1

示例数据如下图2所示。

图2

其中，单元格C2中的公式为：

=IF(B2>$F$2,B2,NA())

下拉至单元格C13。

步骤1：绘制图表

1.选择单元格区域A1:C13。

2.单击功能区“插入”选项卡“图表”组中的“柱形图—簇状柱形图”，如图3所示。

图3

插入一个二维簇状柱形图，如下图4所示。

图4

3.选取图表中的“高于目标值”系列，单击右键，选取“设置数据系列格式”命令，如图5所示。

图5

在设置数据点格式中，将“系列重叠”设置为100％，如下图6所示。

图6

删除图表标题、图例，并调整坐标轴刻度值后，图表如下图7所示。

图7

步骤2：添加目标线

1.选取图表。

2.单击功能区“图表设计”选项卡“数据”组中的“选择数据”命令。在出现的“选择数据源”对话框中单击“添加”按钮，在出现的“编辑数据系列”对话框中，系列名称输入“目标线”，系列值选择单元格F2，如下图8所示。

图8

单击两次“确定”按钮后的图表如下图9所示，可以看出最左侧有一个不同颜色的柱状条即为刚添加的数据系列。

图9

3.选取刚添加的数据系列，单击右健，在快捷菜单中选取“更改系列图表类型”，修改为散点图，如下图10所示。

图10

此时的图表如下图11所示，可以看到图表左侧出现了一个数据点。

图11

4.选取该数据点，单击功能区“图表设计”选项卡最左侧“添加图表元素—误差线—其他误差线选项”，如下图12所示。

图12

添加误差线后，选取水平误差线。设置误差线格式如下图13所示。其末端样式为“无线端”，误差量为“自定义”，指定值为正错误值11、负错误值0。

图13

5.继续设置水平误差线线条格式，包括颜色、线型等，如下图14所示。

图14

6.设置数据点的格式为无填充、无线条，将数据点隐藏，结果如下图15所示。

图15

步骤3：添加滚动条

1.单击功能区“开发工具”选项卡“控件”组中的“插入—表单控件—滚动条”，如下图16所示。

图16

将滚动条绘制在图表右侧，如下图17所示。

图17

2.设置滚动条的最大值为柱状图数据的最大值，本例中为153；设置单元格链接为任意单元格，本例中为单元格G2，如下图18所示。

图18

注意，单元格F2中的公式为：

=153-G2

至此，图表绘制完成。现在，你可以拖动改变滚动条，来观察图表中目标线的变化。