玩轉(zhuǎn)Excel圖表系列
——如何創(chuàng)建有目標(biāo)值的多系列多條件柱狀圖
?
作者:小飛來源:部落窩教育發(fā)布時間:2018-07-03 14:07:54點擊:6329
小編有話說:有小伙伴給小編留言說讓多出一點圖表教程,看來大家對圖表還不是很熟悉,然而卻是我們工作中用的最多的,今天小編再給大家推薦一個圖表教程,可以說是很神奇的制作方法了,你絕對想象不到!
表格幾乎是每個職場人必備的工具,我們制作好的表格,會發(fā)給領(lǐng)導(dǎo)看、發(fā)給員工看或者發(fā)給客戶看,這時做出美觀易懂的表格尤其重要,今天教大家一個帶有目標(biāo)橫線且可自動變色的堆積柱形圖。
一、 學(xué)習(xí)目標(biāo)
現(xiàn)有一款產(chǎn)品從2015年到2017年三年的利潤和增長率,需要制作如下的柱狀圖。用橫線表示制定的利潤和增長率目標(biāo),綠色的柱形條表示完成目標(biāo),紅色的柱形條表示未完成。本柱形圖主要的目的是為了重點突出2015年到2017年的利潤和增長率哪些是達(dá)到目標(biāo),哪些是沒有達(dá)到的。
我們需要滿足以下要求:
1.每一年都需要包括利潤和增長率兩項數(shù)據(jù),并且其互不影響;
2.對于每個柱形條都有一個目標(biāo)橫線;
3.對于利潤和增長率柱形條,如果超過或者剛好達(dá)到目標(biāo)橫線,就呈現(xiàn)綠色,否則為紅色;
其原始數(shù)據(jù)如下:
二、 最初做法
基本最初想到的做法是選中A1:C4數(shù)據(jù),插入簇狀柱形圖,得到如下的一張圖,和目標(biāo)的圖有一定的差距:
1.缺少了目標(biāo)值,無法從圖中得知是否實現(xiàn)了計劃;
2.增加了圖例,使得整個構(gòu)圖不夠緊湊,閱讀者需要左右掃視,分散注意力;
3.利潤和增長率沒有根據(jù)是否完成計劃而改變顏色;
三、 解決方案
(一)處理原始數(shù)據(jù)
為了達(dá)到目標(biāo)的柱狀圖,需要對原始數(shù)據(jù)進(jìn)行處理,添加一定的輔助數(shù)據(jù)。
行2、5、8、11是為了讓控制每個年份的間隔,是占位數(shù)據(jù)。表格中有些數(shù)據(jù)是根據(jù)其他的數(shù)據(jù)推算出的。
F3:=IF(C3>=E3,C3,0)
G3:=IF(C3
I4:=IF(D4>=H4,D4,0)
J4:=IF(D4
制圖步驟如下:
選中A1:B11和E1:J11作為制圖數(shù)據(jù)。先選中A1:B11,windows系統(tǒng)按住Ctrl鍵再選中E1:J11,mac系統(tǒng)按住Command鍵再選中E1:J11。插入柱狀圖,選擇堆積柱狀圖;
選中圖表標(biāo)題,單擊右鍵刪掉標(biāo)題;
選中圖表下方的圖例項,單擊右鍵選擇“設(shè)置圖例格式”,將圖例設(shè)置顯示在圖表的右側(cè);
(二)處理目標(biāo)利潤和目標(biāo)增長率數(shù)據(jù)
選中圖表右側(cè)的“計劃利潤”圖例,單擊右鍵并選擇“更改圖表類型”,更改圖表類型為XY(散點圖);
再次選中“計劃利潤”圖例,單擊右鍵并選擇“設(shè)置數(shù)據(jù)系列格式”,在彈出的對話框中,選擇系列繪制在主坐標(biāo)軸,這樣散點圖就與柱形圖共用同一個坐標(biāo)軸;
選中圖表,菜單欄中就會出現(xiàn)“圖表設(shè)計”的選項,點擊“添加圖表元素”,在下拉菜單中選擇“誤差線”,點擊“其他誤差線選項”,在彈窗中選擇“計劃利潤”;
我們需要的是水平線,需要刪除垂直誤差線。選中垂直誤差線,單擊右鍵選擇“設(shè)置誤差線格式”,點擊,把垂直誤差線線條設(shè)置為無線條。
選中水平誤差線,設(shè)置水平誤差線方向為“兩者”,末端無線端,誤差量固定值0.5,顏色藍(lán)色,寬度1.5磅。
按照上面步驟,對計劃增長率數(shù)據(jù)進(jìn)行同樣的操作(更改為散點圖、刪除垂直誤差線、改變水平誤差線格式),最終完成效果如下面的圖表。
(三) 美化圖表
將得到的圖表進(jìn)一步地美化,主要包括:
將占位行2、5、8、11中的數(shù)據(jù)a改為空格(刪除a然后輸入一個空格),注意不要將數(shù)據(jù)清空,不然橫坐標(biāo)會出現(xiàn)不居中的錯誤;
選中右側(cè)圖例“計劃利潤”,單擊右鍵,選擇設(shè)置數(shù)據(jù)系列格式,選中,點擊標(biāo)記,選中“數(shù)據(jù)標(biāo)記選項”,選擇“無”,同理操作“計劃增長率”;
選中“增長率紅色”圖例,單擊右鍵,在彈窗中選中“設(shè)置數(shù)據(jù)系列格式”。設(shè)置柱形圖的數(shù)據(jù)系列格式中的間隙寬度為50%,這個會應(yīng)用到圖表中所有的柱形條,可以使所有柱形條的寬度變寬;
點擊,點擊“填充”選項,將增長率(綠色)和利潤(綠色)填充為綠色,增長率(紅色)和利潤(紅色)填充為紅色。這個需要對每個系列都設(shè)置一次;
選中圖例,按delete鍵刪除圖例;選中柱形條,單擊右鍵,選擇添加數(shù)據(jù)標(biāo)簽,為每個系列添加數(shù)據(jù)標(biāo)簽;
因為我們的輔助數(shù)據(jù)中,有幾個無用的“0”也被當(dāng)成了制圖數(shù)據(jù),在添加數(shù)據(jù)標(biāo)簽之后,顯示出來了。所以我們可以手動的將它們刪掉。點擊兩下需要刪除的“0”標(biāo)簽,當(dāng)出現(xiàn)下圖的選中狀態(tài),點擊“delete”,就可以刪掉。
點擊縱軸,選中之后,按“delete”刪除縱軸;
選中橫軸,單擊右鍵,選中“設(shè)置坐標(biāo)軸格式”。點擊,在線條中,選擇無線條選項,這樣就能去掉橫軸年份之間的豎線了;
為了更加清楚的說明橫線的作用,可以在圖表上插入文本框,添加目標(biāo)值橫線的圖例。
四、 要點回顧
一張合格的圖表能夠體現(xiàn)制作者的專業(yè)性,能讓閱讀者快速抓住重點。本次
制作的柱形圖中增加了目標(biāo)利潤和目標(biāo)增長率橫線,一眼就能看得出是否完成了計劃。本文也有一些難點需要大家好好思考后才能舉一反三,運用到自己的日常工作中。
1.原始數(shù)據(jù)不等于制圖數(shù)據(jù)。在絕大部分情況下,需要對原始數(shù)據(jù)進(jìn)行處理之后,有時是分拆數(shù)據(jù),有時是添加輔助數(shù)據(jù),才能得到制圖數(shù)據(jù)。因為這里利潤和增長率的顏色表現(xiàn)是不一樣的,而目標(biāo)數(shù)據(jù)表現(xiàn)的是橫線,所以都要分拆出來;
2.誤差線的制作方法。
五、 后續(xù)思考
雖然最終的柱形圖還不錯,但是如果能把每年的利潤放在一起,每年的增長率放在一起,就更能展現(xiàn)隨時間的變化情況。請你思考下,如何處理原始數(shù)據(jù),制作出下面的柱形圖。
本文配套的練習(xí)課件請加入QQ群:316492581下載。
如果您因工作所需使用到Excel,不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。
相關(guān)推薦:
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!