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

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

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

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

選擇最為普通的「群組直條圖」

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

設定「圖例項目(數列)」

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

設定完的群組直條圖

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

快捷選單上點擊「資料數列格式」

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

快捷選單叫出「座標軸格式」視窗

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

將背景顏色設定為「無填滿」

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

先將外框顏色設定為灰色

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

注意到「圖例項目」裡的數列是可以調整順序的

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

即可讓水銀溫度計看起來更為清楚

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

將期間從2018年變成是19年1月到19年3月

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