文末有示例文件獲取方法
按時間段分析業績變化是我們經常會遇到的場景,很多時候,絕對數據和變化趨勢需要分開進行處理,反覆對照,看起來不直觀,分析效率相對較低,有沒有什麼圖表可以把業績大小和變化趨勢統一在一起呢?今天,技巧君就來介紹一種可以直觀反映業績變化趨勢的圖表,效果如下:
製圖思路分析
圖表主體主要有三部分構成:柱形圖、增長趨勢和下滑趨勢,這裡的難點在於增長和下滑趨勢的創建,整個圖表的主要繪製過程有以下幾步:
- 構建源數據,數據需要包含3個關鍵信息:每個月的利潤絕對值,用於柱形圖的創建;每個月的環比變化情況,決定趨勢線長度和方向;每條趨勢線的起點座標,決定每條趨勢線的位置。
- 創建圖表:用源數據創建出基礎的柱形圖,用散點圖標出每條趨勢線的起點位置
- 構造數據變化趨勢:用誤差線繪製出變化趨勢線,用數據標籤標註出數據變化幅度
- 圖表美化:刪除圖表多餘元素,按需要設置合適的顏色和排版
源數據構造
源數據中要包含幾個關鍵信息:
- 最左邊的兩列,月份和利潤一般是統計得到原始數據,用於基礎的柱形圖創建;
- 右邊的X、Y軸座標,是變化趨勢的起點座標;
- 中間的增減範圍是趨勢線的長度和標註用的增減幅度
趨勢線起點X軸座標:在柱形圖中,從左往右柱形圖中心的座標依次為0.5、1.5、2.5......依次類推,那麼,兩個柱形中間的座標即為1、2、3......
趨勢線起點Y軸座標:我們希望,每條趨勢線都是從上個月的利潤值指向下個月的利潤值,所以,每條趨勢線的起點Y軸座標都是上個月的利潤數據,由於需要把上漲和下滑分離,所以原始數據需要做成兩列:
兩列數據的公式分別為:
<code>上漲:=IF(B3>B2,B2,NA())
下滑:=IF(B3/<code>
增減範圍分別決定趨勢線的長度和顯示的增減幅度,對應的數據分別是環比變化值和環比變化率
<code>=B3-B2
=B3/B2-1/<code>
創建基礎圖表
選中月份、利潤、Y軸座標,創建一個柱形圖
<code>選中最左邊兩列,按住Ctrl,再選中Y軸座標,然後插入一個柱形圖/<code>
把Y軸座標對應的兩個系列調整為散點圖,並設置數據系列:
<code>把起點系列數據改為散點圖:
選中任意圖表序列→鼠標右鍵→更改系列圖表類型→把Y軸座標的兩個系列改為散點圖
設置數據系列數據源:
選中任意圖表序列→鼠標右鍵→選擇數據→
編輯利潤序列,把數據調整為1-12月的數據;
編輯增加序列,把X軸調整為設置好的X軸,Y軸數據不變
編輯減少序列,把X軸調整為設置好的X軸,Y軸數據不變/<code>
構造數據變化趨勢
圖表中的趨勢線是利用誤差線構造的,我們先來構造下滑的趨勢線:
<code>選中下滑系列→添加誤差線,刪除橫線的誤差線,保留縱向誤差線
選中縱向誤差線→設置誤差線格式:
正負偏差、無線端、誤差量選擇增減幅度;
切換到填充選項卡,設置末端箭頭,寬度為1.75磅,顏色為綠色/<code>
用同樣的方法,添加上漲的趨勢線,這次把上漲的趨勢設置為紅色:
圖表的最終美化
添加上漲和下滑的數據標籤,設置標籤內容為上漲和下滑的幅度
<code>選中上漲幅度的散點圖→添加數據標籤
選中數據標籤→設置數據標籤格式→單元格中的值選擇為漲跌幅度,Y值和引導線不勾選/<code>
隱藏掉變化趨勢線的起點
<code>選中變化趨勢線的起點→設置數據系列格式→設置為無標記/<code>
最後做配色、排版的美化就好了,顏色看自己喜好配色即可,這是最終的效果:
示例文件獲取方法
關注我之後,私信發送【柱形圖0405】,即可獲取!
記得評論分享收藏哦!
閱讀更多 Excel精選技巧 的文章