Excel 如何设计目标数据和实际数据的差异表?

图11.16中包含目标数据与实际的业务数据,现要求使用图表将它们表现出来,需要同时展现两者的差异。

图11.16 目标数据与实际数据

解题步骤

突显差异值的直接解决方法是使用误差线,具体操作步骤如下。

1.选择 A1 单元格,然后单击功能区的“插入”→“插入柱形图和条形图”→“簇状柱形图”,图表效果如图11.17所示。

图11.17 簇状柱形图的默认效果

2.选中图表的任意位置,然后打开功能区的“格式”选项卡,在选项卡左上角有一个名为“图表元素”的组合键,单击其倒三角按钮会弹出一个列表,从列表中选择“系列 "实际"”。然后单击下方的“设置所选内容格式”,从而在工作表右方产生“设置数据系列格式”任务窗格。

3.在任务窗格中将系列重叠值修改为50%,将分类间距修改为10%,设置界面如图11.18所示,而设置后的图表将显示为如图11.19所示的效果:

图11.18 修改“系列 "实际"”的重叠

图11.19 调整后的图表

百分比与间距百分比

4.选择图表标题,将原文本修改为“目标与实际比较图”,然后将图表下方的图例移到右上角,按住绘图区下方的控制点向下拉,从而将绘图区拉伸至填满下方的图表区。此时图表将显示为图11.20所示的效果。

图11.20 移动图例及拉伸绘图区

通常,没有特殊要求时,图11.20已经是一个完整的图表,它将目标数据和实际数据两个系列半重叠显示,目的是告知图表查看者它们是一组数据、存在某种关联,吸引查看者去关注两者的差异。尽管在本质上此图11.20和图11.17没有区别,但是调整两个系列的重叠百分比之后有助于强调两者的关系,同时方便比较。

本例还要求标示出差值,因此还需要继续以下步骤。

5.在D1单元格输入“差值”,在D2单元格输入公式“=B2-C2”,然后将公式向下填充到D13。

6.从“格式”选项卡中的“图表元素”列表中选择“系列"目标"”,然后依次单击功能区的“设计”→“添加图表元素”→“误差线”→“其他误差线”选项。

7.在右边的“设置误差线格式”窗格中将误差线的方向设置为“负偏差”,将末端样式设置为“线端”,将误差量设置为“自定义”,最后单击“指定值”,弹出“自定义错误栏”对话框。

8.在“负错误值”栏中输入“=Sheet1!D2:D13”,然后单击“确定”按钮返回工作表界面。图11.21是误差线选项设置界面,图11.22则用于指定负错误值的来源。

图11.21 设置误差线选项

图11.22 设置负错误值来源

9.从“格式”选项卡中的“图表元素”列表中选择“系列 "实际"”,依次单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签内”;接着从“格式”选项卡中的“图表元素”列表中选择“系列 "目标"”,并依次单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”,此时图表的最终效果如图11.23所示。

图11.23 图表的最终效果

知识扩展

1.误差值的原本作用并不是标示两个系列之间的差异,它属于统计学中的一个概念,用于标注当前系列的取值范围,允许上下浮动一定范围。由于Excel允许自定义误差值,因此它也可以用于连接两个系列或标注两个系列的数值差异。

2.当两个系列的大小相近时,应该将一个系列的标签放在靠上的位置,另一个系列的标签放在靠下的位置,从而避免标签重叠,影响美观,同时也可以提升查看图表的效率。

3.如果要求在图表中标注误差值,那么只能采用手工修改的方式来完成。例如,对系列“实际”添加数据标签,然后逐个选中数据标签,并修改为误差值,修改后的效果如图11.24所示。

图11.24 将系统“实际”的数据标签修改为误差值

图11.24中的标签207表明实际数据与目标数据还差207,-60则表示超过了目标值60个单元格。