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個月，第五位員工的整體銷售業績加起來過低，價值少，可以考慮轉崗優化
  另一方面可以對比各個顏色條的長度與對比，如最后哦一個員工每個月的銷售業績都比其他員工要長，第三位員工黃色也就是第四個閱讀的銷售業績明顯比其他員工長，可以通過對比進一步分析每位員工每個月業績優秀或一般的原因
  
  數據匯總表
  匯總了所有的銷售數據，期中將銷售業績、同比環比及累計銷售額同比進行了可視化處理，可以更直觀地看到數據的變化情況
  

總結

以上是生活随笔為你收集整理的Excel数据分析案例一——业绩达成分析的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。