實用Excel技巧分享：一張圖表搞定數據比較、走勢與比例貢獻

在日常工作中，資料對比類別圖表我們常常會遇到，資料走勢類別圖表也屬於我們必會的圖表技巧，相信很多同學都能完成。但最近有位同學的奇葩領導提出：「既要有對比效果、又要有走勢效果、還得是多系列全年按月份來展示圖表，並且這些都要做一個圖裡！」

所以說，我們工作中，「難」不在沒技術，而是腦洞大開的上司。那麼今天就給大家一個製作「多系列對比/走勢圖」的想法。

【資料來源】

#在我們看下面的文章之前，同學可以自己考慮如果這樣的資料讓你來設計，你會做成什麼樣子呢？

【正文】

●如果只有1組資料

#沒啥說的，長條圖、長條圖、環形圖，隨你用就好了。

即使是需要看到百分比和走勢線的話，我們在資料中的C列插入一列百分比的輔助列即可，如下：

STEP1：選擇儲存格A1:B4區域，工具列插入－圖表－叢集長條圖；

那麼就得到了一個長條圖的初稿。

STEP2：滑鼠右鍵點選圖表區域任意位置，在彈出的選單中，左鍵點選「選擇資料」。

在彈出的設定框中，點選左側的「新增」系列，如下錄入資料或選擇工作表區域，以輸入內容；

#STEP3：再在圖表區域任意位置，點選滑鼠右鍵，選擇「更改圖表類型」；

##將佔比系列的圖表類型變更為「具有平滑線和資料標記的散佈圖」。

藉此得到了第二步驟的圖表雛形。

STEP4：選取圖中佔比系列的圖形，點選滑鼠右鍵，在選單中選擇「設定資料系列格式」選項；

在系列選項中，點選“次座標軸”，

得到圖表，如下：

再美化一下圖表介面，加入資料標籤，我們就不細說了，給個範例圖。

●再來看今天的主要數據

#不得不說，在實際工作中，圖表的製作還是應該有重點、有突出的去表現某個數據，如果很多資訊擠在一張圖中，會顯得混亂。

長條圖和折線圖（作者在這裡用的是散點圖）的組合是很常用的，而且這樣的組合，既有對比又有走勢，很方便觀看。那麼我們今天的主要數據是不是也可以做成這樣的效果呢？如下圖範例：

【初期製圖規劃】

如果我們決定使用長條圖折線圖的方式來表現這個表的話，那麼我們需要在紙上，或者腦中有一個大致的草圖：每一組圖形對應一個項目組12個月的數據。困難就在於，每一組圖形的底端要對齊；折線要出現在本項目圖形組中。

【圖表類型選擇】

很明顯，如果多個長條圖疊加起來的話，我們應該選擇“堆積長條圖”，可是如果我們使用原始資料操作的話，就會是下面的圖表。

各項目組的底端沒有對齊，這不是我們需要的，但如果我們在每個系列中間插入一個“佔位圖形”，是不是就可以把各個系列圖形「抬高」呢？再把佔位圖形的顏色和邊框設定為“無顏色”，就可以模擬出每行都是長條圖，並且底端對齊的效果了，如下：

同學們可以看一下，這裡我們已經把A、B點佔位圖形的顏色都去掉了，C點綠色的佔位圖形還保留著，是不是可以看出來，底端已經對齊了。

而有了佔位的空位之後，我們就可以在這些空位上加上散點，用來標記佔比，最後再連接這些散點，模擬折線圖的效果。所以我們對於走勢線，採用「有平滑線和資料標記的散佈圖」。

【整理資料】

我們需要在兩個系列圖形中間，插入一個佔位圖形，如果在資料表中考慮的話，就是加入一個輔助數據。下面我們來看看資料的整理階段。

“好圖表往往都不是源數據做出來的”，善於使用輔助數據，是做圖表進階的一個重要標誌，所以也有說法“圖表的使用，最後都歸結於函數的使用」。

上圖中「紅色」字，是系列的資料標籤顯示值，而「藍色」字，就是圖表的佔位圖形和散佈的標記點，逐一來看下每個數據的函數設計想法：

● A：專案A的資料標籤值

• B8​​單元格函數：=B2

●A點佔位：項目A的佔位圖形

• B9單元格函數：=MAX($B$2:$M$4)*2-B8

說明：B2:M4是來源資料區域，我們以資料中最大值的兩倍作為圖形中最高的比例參考，那麼各個項目做出來的圖形就會比例均勻，其原理：資料點數值對應佔位點數值=資料來源中最大值*2，所以對應佔位點數值=資料來源中最大值*2-資料點數值

● B：專案B的資料標籤值

• B10單元格函數：=B3

● B點佔位：專案B的佔位圖

• B11單元格函數：=MAX($B$2:$M$4)*2-B10

● C點佔位：項目C的佔位圖

• B12單元格函數：=B4

#● C點佔位：項目C的佔位圖

• B13單元格函數：=MAX($B$2:$M$4)*2-B12

#● A、B、C佔比：項目A、B、C的資料點，分別在本月中佔所有項目資料總和的比例

• B14單元格函數：=B2/SUM(B$2:B$4)

• #B15單元格函數：=B3/SUM(B$2:B$4)

• B16單元格函數：=B4/SUM(B$2:B$4)

● A佔比點：A項目的走勢圖，對應散點的座標位置。

• B17單元格函數：=B8 B9*1/3

#說明：散佈圖，本身是需要橫縱兩個座標點的，如果我們不設定X軸，那麼預設的就是序號，如下：

為了更貼合長條圖，我們的Y軸就設定成「對應項目佔位點的三分之一處」。

● B、C佔比點：B、C項目的走勢圖，對應散點的座標位置。

• B18單元格函數：=SUM(B8:B10) B11*1/3

• B19單元格函數：=SUM(B8 :B12) B13*1/3

說明：同學們要注意，因為各個項目的散點Y軸座標是有「高度」要求的，所以它們的資料來源，是累加其他項目數據，再加上對應佔比點的三分之一位置，這樣比較貼近對應的長條圖。

【圖表製作】

最困難的資料整理部分已經做完了，還記得我們做單組資料時是如何插入圖表的嗎？趕快動手試試吧！

藍色區域：長條圖

#勾選A7:M13區域，插入“堆積長條圖”，將A 、B、C系列新增「資料標籤」

黃色區域：散佈圖

依照文章開頭中對單組資料設計圖表的操作步驟，重複其中的步驟2、3。

• 選取B17:M17區域，插入「具有平滑線和資料標記的散佈圖」

• 選取B18:M18區域，插入「具有平滑線和資料標記的散佈圖」

• #選取B19:M19區域，插入「具有平滑線和資料標記的散佈圖」

新增“資料標籤”

新增散佈圖的“資料標籤”，並勾選“資料標籤”，在“設定資料標籤格式”中，設定為按下對應「單元格中的值」顯示內容：

如此這般，同學就會得到如下的圖表「毛坯」。

【後期整理】

後整理工作可以參考作者E圖表述的「一刪、二調、三排」的做法來處理。

1、刪除圖表中的“縱座標軸”和“刻度線”，以及不需要顯示的圖例；

##2、調整柱形圖的顏色和平滑線的顏色，將不需顯示的佔位柱形以及平滑線上的標記點設為無線條、無填滿。

3、透過上述操作，圖表已經基本成型了，選取長條圖的某一個系列，調整間隙寬度為5%，

#將長條圖上的資料標籤位置調整為軸內側，平滑線上的資料標籤位置調整為居中；

再調整下圖表中各個元素的位置，並更改字體、字體顏色等，就完成了我們上圖的效果。

【編後文】

對於圖表的製作建議大家：所有Excel的預設效果都不要使用。圖表更像平面設計，每個人的美感不同，出來的效果也不同，但是「視覺化」既然是要給人看的，就要講究美觀。最後分享今天圖文教學中，所使用的這一組配色方案，在「其他顏色－自訂」中輸入RGB值即可。

相關學習推薦：

excel教學

以上是實用Excel技巧分享：一張圖表搞定數據比較、走勢與比例貢獻的詳細內容。更多資訊請關注PHP中文網其他相關文章！