在之前的文章《實用excel技巧分享:這5個快捷鍵,你都會用了嗎?》中,我們了解了5個明星快捷鍵。而今天我們分享一個excel圖表教程,可以說是很神奇的制作方法了,你絕對想象不到!
表格幾乎是每個職場人必備的工具,我們制作好的表格,會發給領導看、發給員工看或者發給客戶看,這時做出美觀易懂的表格尤其重要,今天教大家一個帶有目標橫線且可自動變色的堆積柱形圖。
一、 學習目標
現有一款產品從2015年到2017年三年的利潤和增長率,需要制作如下的柱狀圖。用橫線表示制定的利潤和增長率目標,綠色的柱形條表示完成目標,紅色的柱形條表示未完成。本柱形圖主要的目的是為了重點突出2015年到2017年的利潤和增長率哪些是達到目標,哪些是沒有達到的。
我們需要滿足以下要求:
1.每一年都需要包括利潤和增長率兩項數據,并且其互不影響;
2.對于每個柱形條都有一個目標橫線;
3.對于利潤和增長率柱形條,如果超過或者剛好達到目標橫線,就呈現綠色,否則為紅色;
其原始數據如下:
二、 最初做法
基本最初想到的做法是選中A1:C4數據,插入簇狀柱形圖,得到如下的一張圖,和目標的圖有一定的差距:
1.缺少了目標值,無法從圖中得知是否實現了計劃;
2.增加了圖例,使得整個構圖不夠緊湊,閱讀者需要左右掃視,分散注意力;
3.利潤和增長率沒有根據是否完成計劃而改變顏色;
三、 解決方案
(一)處理原始數據
為了達到目標的柱狀圖,需要對原始數據進行處理,添加一定的輔助數據。
行2、5、8、11是為了讓控制每個年份的間隔,是占位數據。表格中有些數據是根據其他的數據推算出的。
F3:=IF(C3>=E3,C3,0)
G3:=IF(C3
I4:=IF(D4>=H4,D4,0)
J4:=IF(D4
制圖步驟如下:
選中A1:B11和E1:J11作為制圖數據。先選中A1:B11,windows系統按住Ctrl鍵再選中E1:J11,mac系統按住Command鍵再選中E1:J11。插入柱狀圖,選擇堆積柱狀圖;
選中圖表標題,單擊右鍵刪掉標題;
選中圖表下方的圖例項,單擊右鍵選擇“設置圖例格式”,將圖例設置顯示在圖表的右側;
(二)處理目標利潤和目標增長率數據
選中圖表右側的“計劃利潤”圖例,單擊右鍵并選擇“更改圖表類型”,更改圖表類型為XY(散點圖);
再次選中“計劃利潤”圖例,單擊右鍵并選擇“設置數據系列格式”,在彈出的對話框中,選擇系列繪制在主坐標軸,這樣散點圖就與柱形圖共用同一個坐標軸;
選中圖表,菜單欄中就會出現“圖表設計”的選項,點擊“添加圖表元素”,在下拉菜單中選擇“誤差線”,點擊“其他誤差線選項”,在彈窗中選擇“計劃利潤”;
我們需要的是水平線,需要刪除垂直誤差線。選中垂直誤差線,單擊右鍵選擇“設置誤差線格式”,點擊,把垂直誤差線線條設置為無線條。
選中水平誤差線,設置水平誤差線方向為“兩者”,末端無線端,誤差量固定值0.5,顏色藍色,寬度1.5磅。
按照上面步驟,對計劃增長率數據進行同樣的操作(更改為散點圖、刪除垂直誤差線、改變水平誤差線格式),最終完成效果如下面的圖表。
(三) 美化圖表
將得到的圖表進一步地美化,主要包括:
將占位行2、5、8、11中的數據a改為空格(刪除a然后輸入一個空格),注意不要將數據清空,不然橫坐標會出現不居中的錯誤;
選中右側圖例“計劃利潤”,單擊右鍵,選擇設置數據系列格式,選中,點擊標記,選中“數據標記選項”,選擇“無”,同理操作“計劃增長率”;
選中“增長率紅色”圖例,單擊右鍵,在彈窗中選中“設置數據系列格式”。設置柱形圖的數據系列格式中的間隙寬度為50%,這個會應用到圖表中所有的柱形條,可以使所有柱形條的寬度變寬;
點擊,點擊“填充”選項,將增長率(綠色)和利潤(綠色)填充為綠色,增長率(紅色)和利潤(紅色)填充為紅色。這個需要對每個系列都設置一次;
選中圖例,按delete鍵刪除圖例;選中柱形條,單擊右鍵,選擇添加數據標簽,為每個系列添加數據標簽;
因為我們的輔助數據中,有幾個無用的“0”也被當成了制圖數據,在添加數據標簽之后,顯示出來了。所以我們可以手動的將它們刪掉。點擊兩下需要刪除的“0”標簽,當出現下圖的選中狀態,點擊“delete”,就可以刪掉。
點擊縱軸,選中之后,按“delete”刪除縱軸;
選中橫軸,單擊右鍵,選中“設置坐標軸格式”。點擊,在線條中,選擇無線條選項,這樣就能去掉橫軸年份之間的豎線了;
為了更加清楚的說明橫線的作用,可以在圖表上插入文本框,添加目標值橫線的圖例。
四、 要點回顧
一張合格的圖表能夠體現制作者的專業性,能讓閱讀者快速抓住重點。本次
制作的柱形圖中增加了目標利潤和目標增長率橫線,一眼就能看得出是否完成了計劃。本文也有一些難點需要大家好好思考后才能舉一反三,運用到自己的日常工作中。
1.原始數據不等于制圖數據。在絕大部分情況下,需要對原始數據進行處理之后,有時是分拆數據,有時是添加輔助數據,才能得到制圖數據。因為這里利潤和增長率的顏色表現是不一樣的,而目標數據表現的是橫線,所以都要分拆出來;
2.誤差線的制作方法。
五、 后續思考
雖然最終的柱形圖還不錯,但是如果能把每年的利潤放在一起,每年的增長率放在一起,就更能展現隨時間的變化情況。請你思考下,如何處理原始數據,制作出下面的柱形圖。
相關學習推薦:實用excel技巧分享:這5個快捷鍵,你都會用了嗎?