Excel如何更改資料排列呈現銷售統計圖表

編製銷售匯總表,常常產品有兩個維度,一個是銷售期間,一個是產品分類,某一項產品有可能不會在每個期間都有銷售,於是在匯總表格中會有空格產生。如果是以報表呈現,由於報表著重於數字表達,所以如果某個地方有空白,大家容易理解為零,尚不致於有太大問題。如果以圖表呈現,因為圖表本來就是圖像化的表達會比較強。如果是以圖表呈現,圖像化表達本來就是希望有較為美觀視覺的效果,在圖表中間有個空缺,看起來就會比較突兀。這一節即以實際範例和各位分享如何解決圖表中空缺的情形。

一、某汽車經銷商一整個年度四個季度的銷售統計,依照汽車品牌分類,注意到不一定所有品牌在每個季度都有銷售。

二、先選擇表格範圍,於上方功能區的「插入」頁籤,「圖表」指令集中將「直條圖」下拉,建立「群組直條圖」,可以看到中間都有空缺,以保時捷為例,其第三季度沒有銷售,所以會很突兀地沒有Q3綠色柱子。

三、在圖表數列上滑鼠右鍵,點選「選取資料」,進入「選取資料來源」視窗,目前是以季度作為數列、品牌作為類別。游標移到上方「切換列/欄」,輔助浮窗顯示「切換全部值」,其作用有點類似選擇性貼上的轉置,把直的欄和橫的列倒轉過來。

四、轉換成以品牌作為數列、季度作為類別,這樣雖然較為合乎一般以四個季度作水平時間軸的呈現,但由於各個品牌不是每個季度都有銷售,中間仍然會有空缺。再以保時捷為例,其第三季度沒有銷售,所以圖表上Q3並沒有藍色柱子。

五、為了在根本上解決圖表空缺的問題,有必要改變來源資料。如圖所示,有別於一般報表編製方式,將四個季度錯開到不同行列,如此確保了每個季度各個品牌的銷售紀錄上下是連在一起的。這樣的資料格式也可以想成,Q1只有在最上面有數字,從第六行開始都是空白,在這個理解基礎上,會比較清楚接下來的圖表呈現。

六、選取調整後的資料表格範圍,插入群組直條圖,如圖所示,已經初步具備想要的效果,只要再調整一下圖表格式。首先,下方的品牌文字歪斜,將游標移到「水平(類別)軸」,於快捷選單中點擊「座標軸格式」。

七、於「座標軸格式」的「對齊」,將「文字方向」由「水平」下拉改為「垂直」,如此可以看到圖表的水平座標軸標籤較為直覺美觀。

八、於「資料數列格式」的「數列選項」頁籤,將「數列重疊」設定為「95%」、「類別間距」設定為「0%」,如此成功將各品牌銷量依照季度排列,並且中間沒有空缺。

九、像這樣的特殊設計圖表,也許一開始較難理解,可以用測試資料輔助,如圖所示紅色部份,在各空白區域隨機填入一些數字,藉由觀察圖表相對應的變化,應該能理解,這裡其實是A2到A19總共有18個水平類別標籤,每個類別各有Q1到Q4四個數列值,由於第五步驟已經將表格整理成每一行品牌只能出現在某個季度,當第八步驟將數列調整成95%重疊時,每個水平類別標準剛好就會被某品牌某季度值佔滿,以第二列的保時捷而言,其實是Q1到Q4重疊,而只有Q1為「13」,Q2到Q4為空白(零),重疊之後變成只顯示Q1的「13」,依此原理再往下推敲,配合這個步驟的輔助測試圖表,應該就能清楚第八步驟最終所呈現的圖表樣貌了。

先前有章節分享利用數列重疊和邊框設定,使得預算達成率圖表呈現水銀溫度計的特殊效果,當時範例沒有更改原始資料,純粹在現有圖表下功夫。這一節範例雖然也沒有改變數字金額,不過卻是直接改變了原始資料的排列方式,方便呈現特殊的圖表效果。仔細瞭解箇中原理的話,是為了解決表格空白,增加了更多的空白,這些空白在圖表中搭配數列重疊,產生虛擬占位的效果。這個在Excel圖表技巧中稱之為輔助數列。在繪製複雜一點的Excel圖表,往往都會用到像這樣輔助數列的效果,預計在接下來章節也會有輔助數列的圖表,讀者熟悉了其原理,當然也可以舉一反三,依照各自案例需求,創造出具有個人特色的Excel圖表。