Excel彩色箭头做标识 巧用条形图对比 - 青浦海洋数码电脑城

[db:作者]

单位开业绩总结会时，需要用条形图表对近两年的业务进行对比并标出差额，上升用绿色箭头标记，下降用红色箭头标记（图1）。这样的图表如何制作呢？其实，只要把条形图、散点图利用好，这样的图表就比较容易制作出来。下面以在Excel 2019中操作为例。



1. 构造基础条形图
上述效果的图表需要用到条形图和散点图，而上述图表中每个项目所对应的条形图实际上是3个，其中一个的填充色为空，这就需要利用辅助列来对源数据进行改造。在D2单元格中输入公式“=MAX(B2:C2)”，向下填充到相应的位置（图2）。



然后选中A1:D8数据区域，切换到“插入”选项卡，插入二维条形图中的“簇状条形图”。这时，插入图表中的辅助列放在了每组的最上边。右击图表的纵坐标轴，选择“设置坐标轴格式”，在右侧的“坐标轴选项”处勾选“逆序类别”；再选中图表的某个系列，在“系列选项”中将“间隙宽度”设置为0%；右击图表中代表辅助列的条形图，选择“设置数据系列格式”，在“填充与线条”选项卡中，将填充设置为“无填充”、“无线条”。这样，基础条形图就建立好了（图3）。



2. 添加对比箭头
图表中表示数据差额的箭头是通过误差线实现的，误差线的构建需要用到散点图。首先，在E2单元格中输入公式“=IF(B2



接下来，利用散点图的误差线对箭头进行左右标记，而散点图的生成需要添加辅助数据。在H2单元格中输入公式“=MIN(B2:C2)”，并向下填充到所需要的行；在I2单元格中输入“17.6”，I3单元格中输入“14.6”，I4单元格中输入“11.6”，依此类推到相应的单元格，这两列数据用于定位箭头的左标记；在J2单元格中输入公式“=IF(C2>B2,0,ABS(C2-B2))”，并向下填充，这列数据用于生成误差线的箭头。这样，一个散点图的数据就构建完成了。选中H2:H8数据区域，复制，再选中图表，按下“Ctrl+V”粘贴，右击并在图表中选中新添加的系列，选择“更改系列图表类型”，将新添加图表的类型改为“带直线的散点图”；右击图表，选择“选择数据”，在弹出窗口的“图例项”处，选择新添加的系列（即“系列4”），点击“编辑”，在弹出的“编辑数据系列”窗口的“X轴系列值”处选择H2:H8数据区域，在“Y轴系列值”处选择I2:I8数据区域，适当调整Y坐标，使数据点与箭头对齐。选中折线图，切换到“图表工具→设计”选项卡，依次点击“添加图表元素→误差线→标准误差”，选中新添加的误差线垂直方向的误差线，在右侧“设置误差线格式”中，“方向”处选择“正负偏差”，“末端样式”处选择“无线端”，“误差量”处选择“固定值”，输入0.5；再选中水平方向上的误差线，“方向”处选择“正偏差”，“末端样式”处选择“无线端”，“误差量”处选择“自定义”，点击“指定值”，在弹出窗口的“正错误值”处选择J2:J8数据区域；切换到“填充与线条”选项卡，“线条”处选择“实线”，颜色为红色，宽度为3磅，“开始箭头类型”处选择“燕尾箭头”；适当调整Y坐标，使垂直误差线与箭头对齐（图5）。



箭头右侧的标记同样也用散点图的误差线来实现。同样，再构建一个散点图数据源。在M2单元格中输入公式“=MAX(B2:C2)”，并向下填充到所需要的行；在N2单元格中输入公式“=I2-0.5”，并向下填充到N8单元格；在O2单元格中输入公式“=IF(C2>B2,1,2)”，并向下填充到所需要的行。选中M2:M8数据区域，复制，再选中图表，按下“Ctrl+V”粘贴，右击并在图表中选中新添加的系列，选择“更改系列图表类型”，将新添加图表的类型改为“带直线的散点图”；右击图表，选择“选择数据”，在弹出窗口的“图例项”处，选择新添加的系列（即“系列5”），点击“编辑”，在弹出的“编辑数据系列”窗口中的“X轴系列值”处选择M2:M8数据区域，在“Y轴系列值”处选择N2:N8数据区域。选中这个散点图，切换到“图表工具→设计”选项卡，依次点击“添加图表元素→误差线→标准误差”，选中新添加的误差线垂直方向的误差线，在右侧“设置误差线格式”中的“方向”处选择“正偏差”，“末端样式”处选择“无线端”，“误差量”处选择“自定义”，正负错误值都选择O2:O8单元格区域；再选中水平方向上的误差线，将其删除。分别选中两个散点图，将线条设置为“无线条”。这样，带箭头的条形图雏形就完成了（图6）。



3. 添加标签
右击图表中的条形图，选择“添加数据标签”；再右击箭头右侧代表系列5的标记，选择“添加数据标签”，在右侧“设置数据标签格式”中，勾选“单元格中的值”，选择F2:F8数据区域，去掉“Y值”、“显示引导线”前面的勾选，适当手动调整一下标签的位置。最后，根据需要更改图表标题，对图表的样式进行美化就可以了。