当前位置:首页  电脑知识  软件

Excel彩色箭头做标识 巧用条形图对比

Excel彩色箭头做标识 巧用条形图对比

日期:2022-10-10 19:30:16来源:浏览:

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

2111A-TXDB-1

1. 构造基础条形图

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

2111A-TXDB-2

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

2111A-TXDB-3

2. 添加对比箭头

图表中表示数据差额的箭头是通过误差线实现的,误差线的构建需要用到散点图。首先,在E2单元格中输入公式“=IF(B2<C2,ABS(B2-C2),0)”,用于误差线数据;在F2单元格中输入公式“=ABS(C2-B2)”,用于误差线的标签;选中图表中代表辅助列的系列,切换到“图表工具→设计”选项卡,依次点击“添加图表元素→误差线→标准误差”,选中新添加的误差线,在右侧依次进入“设置误差线格式→误差线选项→水平误差线”,“方向”处选择“负偏差”,“末端样式”处选择“无线端”,“误差量”处选择“自定义”,点击“指定值”,在弹出的窗口中,“负错误值”处选择E2:E8数据区域;切换到“填充与线条”选项卡,“线条”处选择“实线”,颜色为绿色,宽度为3磅,“开始箭头类型”处选择“燕尾箭头”(图4)。

2111A-TXDB-4

接下来,利用散点图的误差线对箭头进行左右标记,而散点图的生成需要添加辅助数据。在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)。

2111A-TXDB-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)。

2111A-TXDB-6

3. 添加标签

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

 
相关推荐