[Excel]仿制一张带百分比差值的柱状图

无意间看到一副带有百分比差值的柱状图,感觉设计比较巧妙,本期我们就来仿制一下此图:

数据源及相关公式

考虑到这是一个复合的图表,因此需要创建多个辅助数列来构建整个图表。

快速解释一下每一辅助数列的公式:

  • 年份标签:

    =A5&REPT(” “,$C$3)

考虑到每个年份中都包含有一个被隐藏的柱,为了使得年份标签尽可能位于可视柱的下方。因此使用REPT函数在A列年份之后添加足够的空格,使得年份向左移动。

  • 隐藏柱

    =C6

每个隐藏柱的数值对应下一行总量的数值。这用于制作柱状图中隐藏柱,也是线的底或者顶。

  • 偏差

    =C6-C5

当年和明年间的偏差。

  • 正偏差

    =IF(E5>=0,-E5,””)

如果偏差为正,则返回偏差的负数。

  • 负偏差

    =IF(E5<0,E5,””)

如果偏差为负,则返回偏差。

  • 偏差%

    =IFERROR((C6-C5)/C5,0)

用以制作隐藏柱的误差线标签

创建柱状图

选中B4:D11的数据,插入一个柱状图。

添加误差线

选中隐藏柱,在添加图表元素中选择-> 误差线 -> 更多选项

并对误差线格式,按以上设置。要注意的是,在自定义误差量时:

  • 正错误值为F5:F11
  • 负错误值为G5:G11

经由此设置,你的图现在变成了这样。

格式调整

  • 删除不必要的图例、标题
  • 设置y坐标数字格式为0,k

  • 设置数据系列的系列重叠间隙宽度0
  • 将隐藏柱的填充设置为 无填充

经由这些设置,你的图表现在是这样。

添加误差线标签

  • 选中隐藏柱后,选择标签

  • 按照以上设置标签,其中标签的值选择单元格中的值,并定义为H5:H11。而正误差的标签位置为数据标签外,负误差的标签位置为数据标签内

这就是最终的成品图啦。

当然,火箭君相信大家的想象力肯定不局限于此,比如这样的设计,你知道该怎么设置吗?