让同事直呼大神的高级图表,制作只需简单四步
文末有示例文件获取方法
按时间段分析业绩变化是我们经常会遇到的场景,很多时候,绝对数据和变化趋势需要分开进行处理,反复对照 , 看起来不直观,分析效率相对较低,有没有什么图表可以把业绩大小和变化趋势统一在一起呢?今天,技巧君就来介绍一种可以直观反映业绩变化趋势的图表 , 效果如下:
图1:带利润变化趋势的高级图表DEMO
制图思路分析
图表主体主要有三部分构成:柱形图、增长趋势和下滑趋势 , 这里的难点在于增长和下滑趋势的创建,整个图表的主要绘制过程有以下几步:- 构建源数据,数据需要包含3个关键信息:每个月的利润绝对值 , 用于柱形图的创建;每个月的环比变化情况,决定趋势线长度和方向;每条趋势线的起点坐标 , 决定每条趋势线的位置 。
- 创建图表:用源数据创建出基础的柱形图,用散点图标出每条趋势线的起点位置
- 构造数据变化趋势:用误差线绘制出变化趋势线,用数据标签标注出数据变化幅度
- 图表美化:删除图表多余元素,按需要设置合适的颜色和排版
源数据构造
源数据中要包含几个关键信息:- 最左边的两列,月份和利润一般是统计得到原始数据,用于基础的柱形图创建;
- 右边的X、Y轴坐标 , 是变化趋势的起点坐标;
- 中间的增减范围是趋势线的长度和标注用的增减幅度
图2:源数据示例
趋势线起点X轴坐标:在柱形图中 , 从左往右柱形图中心的坐标依次为0.5、1.5、2.5......依次类推,那么 , 两个柱形中间的坐标即为1、2、3......
图3:X轴坐标示意
趋势线起点Y轴坐标:我们希望,每条趋势线都是从上个月的利润值指向下个月的利润值,所以 , 每条趋势线的起点Y轴坐标都是上个月的利润数据,由于需要把上涨和下滑分离,所以原始数据需要做成两列:
图4:Y轴坐标示意
两列数据的公式分别为:
上涨:=IF(B3>B2,B2,NA())下滑:=IF(B3
图5:Y轴坐标构建
增减范围分别决定趋势线的长度和显示的增减幅度,对应的数据分别是环比变化值和环比变化率
=B3-B2=B3/B2-1
图6:增减范围变化和幅度
创建基础图表
选中月份、利润、Y轴坐标,创建一个柱形图
选中最左边两列 , 按住Ctrl,再选中Y轴坐标 , 然后插入一个柱形图
图7:创建柱形图
把Y轴坐标对应的两个系列调整为散点图,并设置数据系列:
把起点系列数据改为散点图:选中任意图表序列→鼠标右键→更改系列图表类型→把Y轴坐标的两个系列改为散点图设置数据系列数据源:选中任意图表序列→鼠标右键→选择数据→编辑利润序列,把数据调整为1-12月的数据;编辑增加序列,把X轴调整为设置好的X轴 , Y轴数据不变编辑减少序列,把X轴调整为设置好的X轴,Y轴数据不变
图8:设置系列数据
构造数据变化趋势
图表中的趋势线是利用误差线构造的,我们先来构造下滑的趋势线:
选中下滑系列→添加误差线,删除横线的误差线,保留纵向误差线选中纵向误差线→设置误差线格式:正负偏差、无线端、误差量选择增减幅度;切换到填充选项卡 , 设置末端箭头,宽度为1.75磅 , 颜色为绿色
图9:构建下滑的趋势线
用同样的方法,添加上涨的趋势线,这次把上涨的趋势设置为红色:
图10:设置上涨的趋势线
图表的最终美化
添加上涨和下滑的数据标签 , 设置标签内容为上涨和下滑的幅度
选中上涨幅度的散点图→添加数据标签选中数据标签→设置数据标签格式→单元格中的值选择为涨跌幅度,Y值和引导线不勾选
添加上涨和下滑的幅度
隐藏掉变化趋势线的起点
选中变化趋势线的起点→设置数据系列格式→设置为无标记
图12:隐藏数据趋势线散点图
最后做配色、排版的美化就好了,颜色看自己喜好配色即可,这是最终的效果:
图13:最终效果示例
示例文件获取方法
关注我之后 , 私信发送【柱形图0405】,即可获?。?
【让同事直呼大神的高级图表,制作只需简单四步】记得评论分享收藏哦!