Excel数据分析案例一 即时黄金走势分析图表格

Excel数据分析案例一——业绩达成分析 业绩达成销售分析数据的加工处理①看板制作②业绩额完成率表格 KPI子弹图的绘制累计销售额同比、员工销售情况及单月业绩变化图的绘制累计销售额同比单月业绩变化员工销售情况 绘制数据汇总表项目总结

业绩达成销售分析

销售明细原始数据中有以下字段 有销售目标表如下

以上两个表格可以提取以下5个指标

业绩完成率 业绩完成率=(本月销售额-本月目标)/本月目标环比 (本月业绩-上月业绩)/上月业绩同比 (本期业绩-去年同期业绩)/去年同期业绩累计销售额 从当年一月一号至当前的销售额，简称YTD累计销售额同比 (今年累计销售额-去年累计销售额)/去年累计销售额 数据的加工处理 ①看板制作

选中要绘制的单元格区域 - 对区域进行颜色填充为黑色 - 插入各种不同的颜色模板 - 加入文本框，最终效果如下

②业绩额完成率表格 新建一个sheet，命名为【绘图数据】，包含以下字段： 年月：2018年1月~2018年7月的数据 目标业绩：通过原始【销售目标】表格制作数据透视表获得，全选数据插入数据透视表，行为【月份】，值为【销售目标】即可求出每月的销售目标总和，然后将前7个月的数据复制到绘图数据表格中 销售业绩：通过【销售明细原始数据】表格制作数据透视表获得，行设置为【年月】并删除季度，值设置为【销售额】，将2018年7个月的数据复制到绘图数据表格中，点击【数字】中的小数点更改按钮进行四舍五入，只保留整数部分 去年同期销售业绩：2017年1~7月的销售业绩，同样可以通过上面的数据透视表进行复制 上月业绩：每个月份对应减去一个月，所以从2017年12月开始复制 今年业绩YTD：=SUM($C$2:C2),固定C2单元格以后进行求和 去年业绩YTD：同理，用去年同期销售业绩进行sum求和=SUM( $D$2:D2) 经过以上步骤以后得到表格如下图 同比：用今年销售业绩减去去年同期销售业绩除以去年同期销售业绩 =(C2-D2)/D2 环比：当月销售业绩减去上月业绩除以上月业绩，=(C2-E2)/E2 同比YTD：今年业绩YTD减去年业绩YTD除去年业绩YTD，=(F2-G2)/G2 美化百分比小数点以后得到下图的结果 KPI子弹图的绘制

子弹图用作查看目标业绩、销售业绩以及销售业绩所处等级，所以需要新表如下 注意：后面三个字段是累加的关系 选择任意单元格点击插入堆积柱形图，将【目标业绩】和【销售业绩】设置为次坐标轴，并将销售业绩改为带数据的折线图 选择折线图设置数据系列格式，选择标记将标记选项设置为内置，类型改为横线，然后美化去掉网格线图例等 适当调小左边坐标轴的边界和单位，显示单位改为【千】 右边坐标轴同理 还要添加最终业绩和超出百分比的文本框，在【绘图数据】中进行计算并插入，最终效果如下

累计销售额同比、员工销售情况及单月业绩变化图的绘制 累计销售额同比

选择年月和同比两列插入带数据标记的折线图，美化后结果如下

单月业绩变化

选择销售业绩、同比环比三列来制作，同比和环比需要设置次坐标轴到同一个坐标轴中，整体美化后结果如下

员工销售情况

根据销售额明晰原始数据做出的数据透视表如下，筛选2018全年在职的员工 插入柱状堆积图，美化后如下图所示 最后的整体效果如下图

绘制数据汇总表

效果如下图，长方形都是在PPT中手动绘制的，先把表格的数字复制过去再进行手动绘制 在Excel中可以通过添加条件格式进行绘制，效果如下

项目总结

①首先对原始数据表格进行处理，分为以下五个维度

业绩完成率 - 可以看出员工是否完成本月目标 业绩完成率=(本月销售额-本月目标)/本月目标环比 - 与上月业绩进行性环比 (本月业绩-上月业绩)/上月业绩同比 - 与去年同期业绩比较 (本期业绩-去年同期业绩)/去年同期业绩累计销售额 - 累计到个个月的销售额 从当年一月一号至当前的销售额，简称YTD累计销售额同比 - 累计销售额与去年同期累计销售额的对比 (今年累计销售额-去年累计销售额)/去年累计销售额 得到表格如下，可以用条件格式来对数据情况进行可视化处理 ②从以下四个维度制作数据看板 业绩额完成率 主要用于员工KPI展示 业绩分为一般、良好、优秀三个等级，作为堆积柱状图的背景进行对比 较细的柱为实际完成的销售业绩 将目标业绩设置为带数据的折线图并将数据点设置为横线，可以直观看出实际完成销售业绩是否达标 加入本年度总业绩和与去年对比的增长百分比文本框 表格整体可以直观反映出各月业绩对比、每月是否完成业绩目标、每月的实际完成业绩处于哪个等级，及本年度销售总额+去年同期相比的增长率 累计销售额同比 主要用于整体销售情况把握，在时间不变的情况下累计销售额会有一定的浮动，可以进一步控制变量细化分析波动原因 采用带数据的折线图，可以直观看出累计销售额与去年同期相比是增加还是减少，以及具体减少/增加的百分比，可以看出1月的销售额与去年同期相比减少了29%，2月开始有个较大幅度的提升，说明2月的业绩补上了1月落下的业绩，在实际工作场景中可以分析2月业绩优秀的具体原因 单月业绩变化 柱状图可以直观看出每月销售业绩的变化趋势与数值对比情况 环比折线图反映了与上个月对比本月的销售额变化趋势 同比折线图反映了与去年同期对比本年度销售额是增加还是减少 可以以月为分析维度，同步分析业绩、同比与环比的关系及波动情况，若环比与去年持平则不需要考虑时间因素，若环比较去年有较大的波动则可以进一步分析增长或减少的原因 员工销售情况 也是用于对比员工的业绩情况，整体柱长代表该年度的累计销售情况，相同颜色的部分则代表相同月份的员工销售情况 首先看整体，排除第一位员工只在职了1个月，第五位员工的整体销售业绩加起来过低，价值少，可以考虑转岗优化 另一方面可以对比各个颜色条的长度与对比，如最后哦一个员工每个月的销售业绩都比其他员工要长，第三位员工黄色也就是第四个阅读的销售业绩明显比其他员工长，可以通过对比进一步分析每位员工每个月业绩优秀或一般的原因 数据汇总表 汇总了所有的销售数据，期中将销售业绩、同比环比及累计销售额同比进行了可视化处理，可以更直观地看到数据的变化情况