用excel制作九宮格圖表
Step 01. 準(zhǔn)備數(shù)據(jù)
A.下圖是隨機(jī)模擬的一份銷(xiāo)售數(shù)據(jù)明細(xì),小伙伴們可以根據(jù)自己實(shí)際工作數(shù)據(jù)靈活調(diào)整。
B.九宮格圖表的制作需要依賴(lài)函數(shù)和輔助數(shù)據(jù)區(qū)域搭配完成,所以下圖的輔助數(shù)據(jù)區(qū)域就出場(chǎng)了:
單元格公式:
=MOD(COLUMN(A1)-1,5)*200
MOD函數(shù):MOD(被除數(shù),除數(shù)),用于返回兩數(shù)相除的余數(shù)。
COLUMN函數(shù):COLUMN(單元格或者單元格區(qū)域),用于返回列號(hào)。
如果小伙伴們對(duì)函數(shù)不理解,可以打開(kāi)Excel表格按F1查看一下幫助,這里我就不過(guò)多解釋了,如果有需要的話可以留言,后續(xù)可以出單獨(dú)的函數(shù)文章。
C.九宮格分層的輔助數(shù)據(jù)如下圖所示:
細(xì)心的小伙伴一眼望去,就應(yīng)該發(fā)現(xiàn)了上圖數(shù)據(jù)中存在#N/A錯(cuò)誤,第一反應(yīng)肯定以為是數(shù)據(jù)出問(wèn)題了。莫慌,數(shù)據(jù)沒(méi)問(wèn)題哈,此處的#N/A是故意用NA函數(shù)返回的,不是你想的#N/A錯(cuò)誤。
解釋一下上圖的數(shù)據(jù)組成:
① 分成3層3組是因?yàn)槲覀円龅氖蔷艑m格的結(jié)構(gòu)3*3=9,小伙伴在制作的時(shí)候可以靈活更改哦~
② 分隔線1、2、3用來(lái)區(qū)分層數(shù),即上圖4中N8單元格的數(shù)值是用公式:=IF($M8="",COLUMN(A:A)*1000,NA())生成的。
公式的意思是判斷M8單元格是否等于空,等于空就返回引用列號(hào)乘1000,否則返回#N/A。在圖表中,#N/A將不會(huì)被顯示。
③ 圖4中的公式如下:
Q9單元格公式:=1000*(COLUMN(A1)-1)+B3
T16單元格公式:=1000*(COLUMN(A1)-1)+E3
W23單元格公式:=1000*(COLUMN(A1)-1)+H3
原理同分層時(shí)乘1000一樣,既然每層按照1000間隔去區(qū)分,那么數(shù)據(jù)也需要在原有基礎(chǔ)上去增加。
啰啰嗦嗦的解釋了一大堆,估計(jì)小伙伴們已經(jīng)等不及了,下面我們開(kāi)始圖表的制作吧。
Step 02. 插入折線圖
選擇N8:Y29單元格區(qū)域,單擊【插入】選項(xiàng)卡,在插圖分組中選擇折線圖中【帶數(shù)據(jù)標(biāo)記的折線圖】,刪除圖例和網(wǎng)格線,如下圖所示。
Step 03. 添加趨勢(shì)線
鼠標(biāo)雙擊分隔線標(biāo)記點(diǎn),右鍵選擇【添加趨勢(shì)線】,在【油漆桶】圖標(biāo)下方,設(shè)置線條為實(shí)線,顏色為灰白色,寬度1磅,短劃線類(lèi)型選擇實(shí)線。分隔線1設(shè)置步驟如下圖所示:
同理,對(duì)圖表上的分隔線2和分隔線3進(jìn)行操作,步驟如下圖所示:
Step 04. 設(shè)置標(biāo)記選項(xiàng)
可能有的小伙伴手感不是很好,操作的時(shí)候選不好圖表中的標(biāo)記點(diǎn),貼心的花花已經(jīng)想到了,可以先用鼠標(biāo)選中圖表,單擊【格式】選項(xiàng)卡,在當(dāng)前所選內(nèi)容下拉列表里面選擇【系列1】,設(shè)置所選系列格式,在【油漆桶】圖標(biāo)下方,將標(biāo)記選項(xiàng)設(shè)置為【無(wú)】,【系列2】和【系列3】設(shè)置同理。操作步驟如下8所示:
Step 05. 添加誤差線
鼠標(biāo)選中圖表,單擊【格式】選項(xiàng)卡,在當(dāng)前所選內(nèi)容下拉列表里面選擇【系列3】,接著在【設(shè)計(jì)】選項(xiàng)卡里面添加誤差線,將誤差線格式設(shè)置為【負(fù)偏差】、【無(wú)線端】、【誤差百分比為100%】,操作步驟如下圖9所示:
Step 06. 設(shè)置橫坐標(biāo)格式
單擊鼠標(biāo)右鍵,選中圖表橫坐標(biāo)軸,設(shè)置其坐標(biāo)軸格式,坐標(biāo)軸位置設(shè)置為【在刻度線上】,在【油漆桶】圖標(biāo)下方,設(shè)置線條為實(shí)線,顏色為灰白色,寬度為1磅,短劃線類(lèi)型選擇實(shí)線。
Step 07. 添加坐標(biāo)軸標(biāo)簽
用鼠標(biāo)選中圖表后,右鍵點(diǎn)擊【選擇數(shù)據(jù)】,編輯(水平)分類(lèi)軸標(biāo)簽,將區(qū)域設(shè)置為M8:M29,操作如下圖所示:
Step 08. 添加輔助數(shù)據(jù)
鼠標(biāo)選中圖表后,右鍵點(diǎn)擊【選擇數(shù)據(jù)】,在圖例項(xiàng)(系列)中點(diǎn)擊【添加】,系列名稱(chēng)選擇L1單元格,系列值選擇M3:AB3單元格區(qū)域,操作步驟如下圖所示:
Step 09. 更改圖表類(lèi)型
鼠標(biāo)選中圖表,單擊【格式】選項(xiàng)卡,在當(dāng)前所選內(nèi)容下拉列表里面選擇系列“輔助數(shù)據(jù)”,然后在【設(shè)計(jì)】選項(xiàng)卡里面將其圖表類(lèi)型更改為簇狀條形圖,操作步驟如下圖所示。
Step 10. 設(shè)置次要坐標(biāo)軸
鼠標(biāo)選中圖表,點(diǎn)擊【設(shè)計(jì)】選項(xiàng)卡 è 添加圖表元素 è 坐標(biāo)軸 è 次要縱坐標(biāo)軸。接著用鼠標(biāo)選中條形圖,右鍵點(diǎn)擊【選擇數(shù)據(jù)】,編輯【輔助數(shù)據(jù)】的軸標(biāo)簽區(qū)域?yàn)镸2:AB2,完成操作后,圖表右邊的次坐標(biāo)軸會(huì)出現(xiàn)剛剛添加的數(shù)據(jù)標(biāo)簽,操作步驟如下圖所示。
Step 11. 設(shè)置條形圖格式
我猜已經(jīng)有小伙伴忍這個(gè)條形圖很久了,實(shí)在是太礙眼了,下面我們就開(kāi)始盤(pán)它?。?!
選中條形圖,單擊鼠標(biāo)右鍵,選擇【設(shè)置數(shù)據(jù)系列格式】,在【油漆桶】圖標(biāo)下方設(shè)置無(wú)填充,邊框設(shè)置為無(wú)線條,并在【系列選項(xiàng)】中,把間隙寬度調(diào)到最大。操作步驟如下圖所示。
Step 12. 設(shè)置縱坐標(biāo)軸格式
鼠標(biāo)選中左邊的縱坐標(biāo)軸,單擊鼠標(biāo)右鍵,選擇【設(shè)置坐標(biāo)軸格式】,將最小值設(shè)置為0,最大值為3000,單位最大設(shè)置為200,接著在【設(shè)計(jì)】選項(xiàng)卡里面隱藏坐標(biāo)軸標(biāo)簽(也可以直接刪除坐標(biāo)軸)。操作步驟如下圖所示。
Step 13. 調(diào)整坐標(biāo)軸標(biāo)簽位置
通過(guò)上述操作,九宮格圖表算是初見(jiàn)雛形,但是和文章開(kāi)頭的效果圖還是有些差別,我們還需要將坐標(biāo)軸標(biāo)簽從右邊調(diào)整到左邊。先選中右邊的坐標(biāo)軸,單擊鼠標(biāo)右鍵,選擇【設(shè)置坐標(biāo)軸格式】,在標(biāo)簽選項(xiàng)里面設(shè)置標(biāo)簽位置為【低】,這樣坐標(biāo)軸標(biāo)簽就從右邊調(diào)整到左邊了。最后將圖表上方不需要的坐標(biāo)軸標(biāo)簽刪除掉,操作步驟如下圖17所示。
Step 14. 圖表區(qū)域命名
圖表中每格區(qū)域歸屬的信息需要插入一個(gè)文本框進(jìn)行標(biāo)注。插入文本框,在選中文本框的狀態(tài)下,在編輯欄中輸入等號(hào),然后鼠標(biāo)選中需要關(guān)聯(lián)數(shù)據(jù)的單元格,按回車(chē)鍵確認(rèn)。以湖北地區(qū)為例,操作步驟如下圖所示。
Step 15. 設(shè)置平滑線
為了讓圖表看起來(lái)更加“圓潤(rùn)”,我們需要去除折線圖的“棱角”。選中折線圖,單擊鼠標(biāo)右鍵,選擇【設(shè)置數(shù)據(jù)系列格式】,在【油漆桶】圖標(biāo)的最下方勾選平滑線。操作步驟如下圖所示。
Step 16. 美化圖表
對(duì)于圖表標(biāo)題的設(shè)置,我們也可以參考前面的文本框關(guān)聯(lián)單元格數(shù)據(jù)的方法,這樣在我們后期改動(dòng)數(shù)據(jù)的時(shí)候,就可以實(shí)現(xiàn)圖表數(shù)據(jù)動(dòng)態(tài)更新了。剩下的就是給圖表設(shè)置一個(gè)你喜歡的配色,并將其設(shè)置為圓角,然后用文本框在圖表左下角添加數(shù)據(jù)來(lái)源和作者的信息,讓圖表呈現(xiàn)的更加專(zhuān)業(yè)。操作步驟如下圖所示。
繼續(xù)查找其他問(wèn)題的答案?
-
Word 2019如何轉(zhuǎn)換為PowerPoint?(視頻講解)
2021-01-258次播放
-
PowerPoint 2019如何為切換效果添加聲音?(視頻講解)
2021-01-232次播放
-
PowerPoint 2019如何設(shè)置切換效果的屬性?(視頻講解)
2021-01-232次播放
-
PowerPoint 2019如何為幻燈片添加切換效果?(視頻講解)
2021-01-230次播放
-
2021-01-199次播放
-
2021-01-193次播放
-
2021-01-192次播放
-
2021-01-191次播放
-
2021-01-1912次播放
-
2021-01-196次播放
點(diǎn)擊加載更多評(píng)論>>