首頁 > 專題 > excel > 主體

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

青灯夜游
發布: 2022-07-25 19:21:37
轉載
9070 人瀏覽過

在之前的文章《實用Excel技巧分享:這5個快捷鍵,你都會用了嗎?在》中,我們了解了5個明星快捷鍵。而今天我們分享一個Excel圖表教程,可以說是很神奇的製作方法了,你絕對想像不到!

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

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

一、學習目標

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

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

我們需要滿足以下要求:

1.每一年都需要包含利潤和成長率兩項數據,且其互不影響;

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

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

其原始資料如下:

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

#二、 原始做法

基本上最初想到的做法是選取A1:C4數據,插入簇狀長條圖,得到如下的一張圖,和目標的圖有一定的差距:

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

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

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

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

#三、解決方案

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

## (一)處理原始資料

為了達到目標的長條圖,需要對原始資料進行處理,加入一定的輔助資料。

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

F3:

=IF(C3>=E3,C3,0)

Excel圖表學習之建立有目標值的多系列多條件長條圖G3:

=IF(C3

I4:Excel圖表學習之建立有目標值的多系列多條件長條圖=IF(D4>=H4,D4,0)

J4:

=IF(D4

製圖步驟如下:選取A1:B11和E1:J11作為製圖資料。先選取A1:B11,windows系統按住Ctrl鍵再選取E1:J11,mac系統按住Command鍵再選取E1:J11。插入長條圖,選擇堆積長條圖;

Excel圖表學習之建立有目標值的多系列多條件長條圖選取圖表標題,按一下右鍵刪除標題;

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

##選取圖表下方的圖例項,點選右鍵選擇“設定圖例格式”,將圖例設定顯示在圖表的右側;Excel圖表學習之建立有目標值的多系列多條件長條圖

##(二)處理目標利潤和目標成長率資料1Excel圖表學習之建立有目標值的多系列多條件長條圖

###選取圖表右側的“計畫利潤」圖例,按一下右鍵並選擇“變更圖表類型”,變更圖表類型為XY(散佈圖);## #############再次選中“計劃利潤”圖例,單擊右鍵並選擇“設定資料系列格式”,在彈出的對話框中,選擇系列繪製在主座標軸,這樣散點圖就與長條圖共用同一個座標軸;#########################選取圖表,功能表列就會出現“圖表設計”的選項,點擊“新增圖表元素”,在下拉式選單中選擇“誤差線”,點擊“其他誤差線選項”,在彈窗中選擇“計劃利潤”;############

我們需要的是水平線,需要刪除垂直誤差線。選取垂直誤差線,點選右鍵選擇“設定誤差線格式”,點選,把垂直誤差線線條設定為無線條。

1Excel圖表學習之建立有目標值的多系列多條件長條圖12-Excel圖表學習之建立有目標值的多系列多條件長條圖

選取水平誤差線,設定水平誤差線方向為“兩者”,末端無線端,誤差量固定值0.5,顏色藍色,寬度1.5磅。

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

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

依照上面步驟,對計畫成長率資料進行相同的操作(改為散點圖、刪除垂直誤差線、改變水平誤差線格式),最終完成效果如下面的圖表。

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

(三) 美化圖表

#將得到的圖表進一步美化,主要包括:

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

選取右側圖例“計劃利潤”,按一下右鍵,選擇設定資料系列格式,選取Excel圖表學習之建立有目標值的多系列多條件長條圖,點選標記,選取“資料標記選項”,選擇“無”,同理操作“計劃成長率”;

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

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

勾選「成長率紅色」圖例,按一下右鍵,在彈跳視窗中選取「設定資料系列格式」。設定長條圖的資料系列格式中的間隙寬度為50%,這個會應用到圖表中所有的柱形條,可以使所有柱形條的寬度變寬;

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

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

點擊Excel圖表學習之建立有目標值的多系列多條件長條圖,點擊「填充」選項,將成長率(綠色)和利潤(綠色)填滿為綠色,成長率(紅色)和利潤(紅色)填充為紅色。這個需要對每個系列都設定一次;

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

選取圖例,按delete鍵刪除圖例;選取長條條,按一下右鍵,選擇新增資料標籤,為每個系列添加數據標籤;

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

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

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

點選縱軸,選取後,按「delete」刪除縱軸;

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

##選取橫軸,單擊右鍵,選取「設定座標軸格式」。點選

,在線條中,選擇無線條選項,這樣就能去掉橫軸年份之間的豎線了;Excel圖表學習之建立有目標值的多系列多條件長條圖

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

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

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

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

四、 要點回顧

一張合格的圖表能夠體現製作者的專業性,能讓閱讀者快速把握重點。這次

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

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

2.誤差線的製作方法。

五、 後續思考

雖然最終的長條圖還不錯,但是如果能把每年的利潤放在一起,每年的成長率放在一起,就更能展現隨時間的變化。請你思考下,如何處理原始數據,製作出下面的長條圖。

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

相關學習推薦:excel教學

以上是Excel圖表學習之建立有目標值的多系列多條件長條圖的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:itblw.com
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板
關於我們 免責聲明 Sitemap
PHP中文網:公益線上PHP培訓,幫助PHP學習者快速成長!