技術文章 » Excel圖表學習之建立有目標值的多系列多條件柱狀圖

Excel圖表學習之建立有目標值的多系列多條件柱狀圖

2022-07-25 22:01:03
在之前的文章《》中，我們瞭解了5個明星快捷鍵。而今天我們分享一個Excel圖表教學，可以說是很神奇的製作方法了，你絕對想象不到！

手機如何做表格：點選檢視

表格幾乎是每個職場人必備的工具，我們製作好的表格，會發給領導看、發給員工看或者發給客戶看，這時做出美觀易懂的表格尤其重要，今天教大家一個帶有目標橫線且可自動變色的堆積柱形圖。

一、 學習目標

現有一款產品從2015年到2017年三年的利潤和增長率，需要製作如下的柱狀圖。用橫線表示制定的利潤和增長率目標，綠色的柱形條表示完成目標，紅色的柱形條表示未完成。本柱形圖主要的目的是為了重點突出2015年到2017年的利潤和增長率哪些是達到目標，哪些是沒有達到的。

1.png

我們需要滿足以下要求：

1.每一年都需要包括利潤和增長率兩項資料，並且其互不影響；

2.對於每個柱形條都有一個目標橫線；

3.對於利潤和增長率柱形條，如果超過或者剛好達到目標橫線，就呈現綠色，否則為紅色；

其原始資料如下：

2.png

二、 最初做法

基本最初想到的做法是選中A1:C4資料，插入簇狀柱形圖，得到如下的一張圖，和目標的圖有一定的差距：

1.缺少了目標值，無法從圖中得知是否實現了計劃；

2.增加了圖例，使得整個構圖不夠緊湊，閱讀者需要左右掃視，分散注意力；

3.利潤和增長率沒有根據是否完成計劃而改變顏色；

3.png

三、 解決方案

(一)處理原始資料

為了達到目標的柱狀圖，需要對原始資料進行處理，新增一定的輔助資料。

4.png

行2、5、8、11是為了讓控制每個年份的間隔，是佔位資料。表格中有些資料是根據其他的資料推算出的。

F3：＝IF(C3>=E3,C3,0)

G3：=IF(C3<e3,c3,0)

I4：=IF(D4>=H4,D4,0)

J4：=IF(D4<h4,d4,0)

製圖步驟如下：

選中A1:B11和E1:J11作為製圖資料。先選中A1:B11，windows系統按住Ctrl鍵再選中E1:J11,mac系統按住Command鍵再選中E1:J11。插入柱狀圖，選擇堆積柱狀圖；

5.png

選中圖表標題，單擊右鍵刪掉標題；

6.png

選中圖表下方的圖例項，單擊右鍵選擇「設定圖例格式」，將圖例設定顯示在圖表的右側；

7.png

(二)處理目標利潤和目標增長率資料

選中圖表右側的「計劃利潤」圖例，單擊右鍵並選擇「更改圖表型別」，更改圖表型別為XY(散點圖)；

8.png

再次選中「計劃利潤」圖例，單擊右鍵並選擇「設定資料系列格式」，在彈出的對話方塊中，選擇系列繪製在主座標軸，這樣散點圖就與柱形圖共用同一個座標軸；

9.png

10.png

選中圖表，選單欄中就會出現「圖表設計」的選項，點選「新增圖表元素」，在下拉式選單中選擇「誤差線」，點選「其他誤差線選項」，在彈窗中選擇「計劃利潤」；

11.png

我們需要的是水平線，需要刪除垂直誤差線。選中垂直誤差線，單擊右鍵選擇「設定誤差線格式」，點選，把垂直誤差線線條設定為無線條。

12.png12-2.png

選中水平誤差線，設定水平誤差線方向為「兩者」，末端無線端，誤差量固定值0.5，顏色藍色，寬度1.5磅。

13.png

14.png

按照上面步驟，對計劃增長率資料進行同樣的操作（更改為散點圖、刪除垂直誤差線、改變水平誤差線格式），最終完成效果如下面的圖表。

15.png

(三) 美化圖表

將得到的圖表進一步地美化，主要包括：

將佔位行2、5、8、11中的資料a改為空格（刪除a然後輸入一個空格），注意不要將資料清空，不然橫座標會出現不居中的錯誤；

選中右側圖例「計劃利潤」，單擊右鍵，選擇設定資料系列格式，選中，點選標記，選中「資料標記選項」，選擇「無」，同理操作「計劃增長率」；

16.png

17.png

選中「增長率紅色」圖例，單擊右鍵，在彈窗中選中「設定資料系列格式」。設定柱形圖的資料系列格式中的間隙寬度為50%，這個會應用到圖表中所有的柱形條，可以使所有柱形條的寬度變寬；

18.png

19.png

點選，點選「填充」選項，將增長率（綠色）和利潤（綠色）填充為綠色，增長率（紅色）和利潤（紅色）填充為紅色。這個需要對每個系列都設定一次；

20.png

選中圖例，按delete鍵刪除圖例；選中柱形條，單擊右鍵，選擇新增資料標籤，為每個系列新增資料標籤；

21.png

因為我們的輔助資料中，有幾個無用的「0」也被當成了製圖資料，在新增資料標籤之後，顯示出來了。所以我們可以手動的將它們刪掉。點選兩下需要刪除的「0」標籤，當出現下圖的選中狀態，點選「delete」，就可以刪掉。

22.png

點選縱軸，選中之後，按「delete」刪除縱軸；

23.png

選中橫軸，單擊右鍵，選中「設定座標軸格式」。點選，線上條中，選擇無線條選項，這樣就能去掉橫軸年份之間的豎線了；

24.png

25.png

為了更加清楚的說明橫線的作用，可以在圖表上插入文字方塊，新增目標值橫線的圖例。

26.png

四、 要點回顧

一張合格的圖表能夠體現製作者的專業性，能讓閱讀者快速抓住重點。本次

製作的柱形圖中增加了目標利潤和目標增長率橫線，一眼就能看得出是否完成了計劃。本文也有一些難點需要大家好好思考後才能舉一反三，運用到自己的日常工作中。

1.原始資料不等於製圖資料。在絕大部分情況下，需要對原始資料進行處理之後，有時是分拆資料，有時是新增輔助資料，才能得到製圖資料。因為這裡利潤和增長率的顏色表現是不一樣的，而目標資料表現的是橫線，所以都要分拆出來；

2.誤差線的製作方法。

五、 後續思考

雖然最終的柱形圖還不錯，但是如果能把每年的利潤放在一起，每年的增長率放在一起，就更能展現隨時間的變化情況。請你思考下，如何處理原始資料，製作出下面的柱形圖。

27.png

相關學習推薦：

以上就是Excel圖表學習之建立有目標值的多系列多條件柱狀圖的詳細內容，更多請關注TW511.COM其它相關文章！