Excel利用數列重疊編製預算達成率圖表 - 會計

Excel利用數列重疊編製預算達成率圖表

網誌圖文版：

https://www.b88104069.com/archives/4378

公司通常制定有年度預算，不但作為下個年度業務營收的展望，也是相對應的資本支出、
人員配置、技術研發、部門建制、費用管控等公司運作的引領方案。而真正到了下個年度
，有預算，當然就有實際金額和預算金額之間的達成率及差異分析，如此也是公司內部管
理相當重要的一個工具。

當我們想將預算達成率的狀況以圖表呈現，很快會發現，Excel沒有特別訂制相對應的圖
表，必須花些心思，在此便介紹如何用最常見的群組直條圖，透過一些小技巧，使其變化
成適合用於表達預算達成率、類似水銀溫度計的Excel圖表。以下具體介紹。

一、首先，2018年的營收預算及實際金額，想編製圖表，呈現營收預算達成率的狀況。於
上方功能區的「插入」頁籤，「圖表」群組中將「直條圖」下拉，選擇最為普通的「群組
直條圖」，再於空白的「圖表3」滑鼠右鍵，點擊「選取資料」。

二、於「選取資料來源」視窗中，設定「圖例項目(數列)」及「水平(類別)座標軸標籤」
，如圖所示，先前已經有多篇文章示範Excel圖表如何選取資料，在此不再細述，這個算
是基本功，必須非常熟悉。

三、設定完的群組直條圖，本文即以此為基礎，進一步將其加工為預算達成率圖表。

四、於圖表上右鍵，快捷選單上點擊「資料數列格式」，在「數列選項」頁籤中，將「數
列重疊」設定為「100%」，此時可以看「預算」和「實際」兩個數列重疊起來，從上個步
驟圖表判斷，可知是紅色在前、藍色在後，因為如果藍色在前，紅色便會完全被覆蓋看不
到了。

五、在設定達成率圖表之前，先更改座標軸，將游標移到圖表左邊的座標軸區域，滑鼠右
鍵，快捷選單叫出「座標軸格式」視窗，於「座標軸選項」頁籤中，「最小值」設定為「
0」，「最大值」設定為「100」。

六、游標移到圖表藍色數列區域，滑鼠右鍵，將背景顏色設定為「無填滿」，如此即技巧
性地將預算數列隱藏。

七、同樣將游標移到圖表藍色數列區域，滑鼠右鍵，先將外框顏色設定為灰色，再將「寛
度」設定為「4.5點」，如此即具備像水銀溫度計般的雛形。

八、於「選取資料來源」視窗中，注意到「圖例項目」裡的數列是可以調整順序的，通常
不會用到，但在這裡數列有重疊的情況，剛好適用，例如將「實際」「往上移」，和上個
步驟兩相比對，可以看到實際數列在後面，預算數列在前面，預算數列的外框就會將實際
數列覆蓋了。

九、沿用前兩個步驟方法，先把紅色數列移到前面，將其外框設置粗白線，再將紅色數列
移到後面，即可讓水銀溫度計看起來更為清楚，因為操作流程先前步驟已有說明，在此不
再細述。

十、在已經設置好水銀溫度計圖表的基礎上，只要重新選取資料，類似樞紐分析表變更資
料來源，馬上即可更新Excel圖表，例如將期間從2018年變成是19年1月到19年3月。除了
資料更新，同時修改垂直座標軸刻度、設置座標軸標籤字型、刪除圖例項目，像這樣的圖
表修改調整並無固定，可依照個人偏好進行。

本書用到了設定座標軸、選取資料等Excel圖表的基本技巧，也用到了數列重疊、填滿顏
色、圖表外框等輔助技巧，在進階運用Excel圖表時，紮實的基本功和巧妙的輔助技兩者
並行且缺一不可。只有熟悉基本套路之後，才能進一步發揮輔助招式，而如果沒有輔助招
式只有基本套路，那麼沒辦法跳出Excel圖表的既定框架。本書前面章節多次介紹過圖表
基本工具，如同本節範例所示，接下來章節較為著重於各式各樣輔助技巧的介紹，期許
Excel圖表脫胎換骨、更為契合報告訴求。


延伸閱讀：

Excel如何利用輔助數列編製門市店總數趨勢圖

https://www.b88104069.com/archives/4355

Excel如何編製門市店三年趨勢的堆疊直條圖

https://www.b88104069.com/archives/4353

Excel如何編製門市店成長的圖片直條圖

https://www.b88104069.com/archives/4345

--


周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室： https://www.facebook.com/acctexcel


--