用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è)。操作步驟如下圖所示。