偏差图非常适用于同比分析的场景，它在一张图上既做数据对比，又同时显示差异额和差异率，不仅颜值高还特别实用，老板看了都夸你专业！下面分享详细制作方法，步骤有点多，建议收藏学习。
1.添加辅助列
假设A列为月份，B列和C列是2年的数据。从D列开始添加：
- D列（差异额）：=B2-C2
- E列（差异率）：=B2/C2-1，设置百分比格式
- F列（缩小一半）：=D2/2（原值柱子太高，缩小后好布局）
- G列（扩大比率）：=E2*350（比率太小显示不了，乘350后区间和D列基本一致）
另在J1和J2单元格填暂定高度（比如J1=100，J2=80），差异额线和差异率线的高度，后面可调。
散点坐标区域（差异额柱用）：
K列为Y值
- L列计算正柱位置：=IF(E2>0,J$1+F2,NA())
- M列计算负柱位置：=IF(E2<0,J$1+F2,NA())
差异率区域（O:Q列）同理：
- P列计算正点位置：=IF(G2>0,J$2,NA())+G2
- Q列计算负点位置：==IF(G2<0,J$2,NA())+G2
2.插入柱形图并添加两条参考线
- 选中A、B、C三列，插入簇状柱形图
- 右键“选择数据”添加差异额系列，系列值选J1
- 更改图表类型，将差异额设置为散点图
- 选中散点，更改数据，X值设为0.5
- 差异率同理，最后得到2个散点
- 分别选中这两个点，添加误差线，正偏差，固定值设为6。两条水平参考线就出现了。
3.添加差异额的散点（正柱和负柱）
右键“选择数据”，添加系列：
- 正柱系列：X值选K列，Y值选L列
- 负柱系列：X值选K列，Y值选M列
- 图表上会出现两组散点，分别给正柱散点和负柱散点添加误差线（Y误差线），误差量选自定义，正错误值为0，负错误值选F列数据。
- 负柱同理，最后线宽调至20磅。
- 然后添加数据标签，标签选单元格中的值（D列差异额），标签位置选靠上
- 同样给负柱的误差线也加数据标签，标签值还是选D列，位置选靠下
4.添加差异率的柱形
- 操作同上，给正点以及负点添加散点系列，X轴选择O列，Y值分别选择P和Q列数据
- 增加正点、负点的误差线，操作同上，正错误值为0，负错误值选G列数据
5.美化收尾
- 把两条参考线改成虚线或浅灰色。
- 调整柱子颜色（正柱用绿色，负柱用红色），删除多余网格线。
- 如果差异额线或差异率线位置不合适，直接改J1或J2的值，图会自动更新。
这个图表步骤确实多，但谁让它颜值高又实用。网上详细的教程还是比较少的，建议先收藏学习哦，以后做同比分析直接用这类偏差图，老板看了都得夸你专业。
评论 (0)