怎麼用excel製作九宮格圖表

你知道excel可以製作九宮格圖表嗎？分本將為你一一介紹詳細步驟，相信你看完之後一定會學會，並能製作出一份超秀的九宮格圖表的，讓我們一起看下去吧！

Step 01.  準備資料

A.下圖是隨機模擬的一份銷售資料明細，小夥伴們可以根據自己實際工作資料靈活調整。

 

B.九宮格圖表的製作需要依賴函數和輔助資料區域搭配完成，所以下圖的輔助資料區域就出場了：

 

單元格公式：

=MOD(COLUMN(A1)-1,5)*200

MOD函數：MOD(被除數,除數)，用於返回兩數相除的餘數。

COLUMN函數：COLUMN(單元格或者單元格區域)，用於返回列號。

如果小夥伴們對函數不理解，可以開啟Excel表格按F1檢視一下幫助，這裡我就不過多解釋了，如果有需要的話可以留言，後續可以出單獨的函數文章。

C.九宮格分層的輔助資料如下圖所示：

 

細心的小夥伴一眼望去，就應該發現了上圖資料中存在#N/A錯誤，第一反應肯定以為是資料出問題了。莫慌，資料沒問題哈，此處的#N/A是故意用NA函數返回的，不是你想的#N/A錯誤。

解釋一下上圖的資料組成：

①  分成3層3組是因為我們要做的是九宮格的結構3*3=9，小夥伴在製作的時候可以靈活更改哦~

②  分隔線1、2、3用來區分層數，即上圖4中N8單元格的數值是用公式：=IF($M8="",COLUMN(A:A)*1000,NA())生成的。

公式的意思是判斷M8單元格是否等於空，等於空就返回參照列號乘1000，否則返回#N/A。在圖表中，#N/A將不會被顯示。

③  圖4中的公式如下：

Q9單元格公式：=1000*(COLUMN(A1)-1)+B3

T16單元格公式：=1000*(COLUMN(A1)-1)+E3

W23單元格公式：=1000*(COLUMN(A1)-1)+H3

原理同分層時乘1000一樣，既然每層按照1000間隔去區分，那麼資料也需要在原有基礎上去增加。

囉囉嗦嗦的解釋了一大堆，估計小夥伴們已經等不及了，下面我們開始圖表的製作吧。

Step 02.  插入折線圖

選擇N8:Y29單元格區域，單擊【插入】索引標籤，在插圖分組中選擇折線圖中【帶資料標記的折線圖】，刪除圖例和格線，如下圖所示。

 

Step 03.  新增趨勢線

滑鼠雙擊分隔線標記點，右鍵選擇【新增趨勢線】，在【油漆桶】圖示下方，設定線條為實線，顏色為灰白色，寬度1磅，短劃線型別選擇實線。分隔線1設定步驟如下圖所示：

 

同理，對圖表上的分隔線2和分隔線3進行操作，步驟如下圖所示：

 

Step 04.  設定標記選項

可能有的小夥伴手感不是很好，操作的時候選不好圖表中的標記點，貼心的花花已經想到了，可以先用滑鼠選中圖表，單擊【格式】索引標籤，在當前所選內容下拉選單裡面選擇【系列1】，設定所選系列格式，在【油漆桶】圖示下方，將標記選項設定為【無】，【系列2】和【系列3】設定同理。操作步驟如下8所示：

 

Step 05.  新增誤差線

滑鼠選中圖表，單擊【格式】索引標籤，在當前所選內容下拉選單裡面選擇【系列3】，接著在【設計】索引標籤裡面新增誤差線，將誤差線格式設定為【負偏差】、【無線端】、【誤差百分比為100%】，操作步驟如下圖9所示：

 

Step 06.  設定橫坐標格式

單擊滑鼠右鍵，選中圖表橫坐標軸，設定其坐標軸格式，坐標軸位置設定為【在刻度線上】，在【油漆桶】圖示下方，設定線條為實線，顏色為灰白色，寬度為1磅，短劃線型別選擇實線。

 

Step 07.  新增坐標軸標籤

用滑鼠選中圖表後，右鍵點選【選擇資料】，編輯（水平）分類軸標籤，將區域設定為M8:M29，操作如下圖所示：

 

Step 08.  新增輔助資料

滑鼠選中圖表後，右鍵點選【選擇資料】，在圖例項(系列)中點選【新增】，系列名稱選擇L1單元格，系列值選擇M3:AB3單元格區域，操作步驟如下圖所示：

 

Step 09.  更改圖表型別

滑鼠選中圖表，單擊【格式】索引標籤，在當前所選內容下拉選單裡面選擇系列“輔助資料”，然後在【設計】索引標籤裡面將其圖表型別更改為簇狀條形圖，操作步驟如下圖所示。

 

Step 10.  設定次要坐標軸

滑鼠選中圖表，點選【設計】索引標籤 è 新增圖表元素 è 坐標軸 è 次要縱坐標軸。接著用滑鼠選中條形圖，右鍵點選【選擇資料】，編輯【輔助資料】的軸標籤區域為M2:AB2，完成操作後，圖表右邊的次坐標軸會出現剛剛新增的資料標籤，操作步驟如下圖所示。

 

Step 11.  設定條形圖格式

我猜已經有小伙伴忍這個條形圖很久了，實在是太礙眼了，下面我們就開始盤它！！！

選中條形圖，單擊滑鼠右鍵，選擇【設定資料系列格式】，在【油漆桶】圖示下方設定無填充，邊框設定為無線條，並在【系列選項】中，把間隙寬度調到最大。操作步驟如下圖所示。

 

Step 12.  設定縱坐標軸格式

滑鼠選中左邊的縱坐標軸，單擊滑鼠右鍵，選擇【設定坐標軸格式】，將最小值設定為0，最大值為3000，單位最大設定為200，接著在【設計】索引標籤裡面隱藏坐標軸標籤（也可以直接刪除坐標軸）。操作步驟如下圖所示。

 

Step 13.  調整坐標軸標籤位置

通過上述操作，九宮格圖表算是初見雛形，但是和文章開頭的效果圖還是有些差別，我們還需要將坐標軸標籤從右邊調整到左邊。先選中右邊的坐標軸，單擊滑鼠右鍵，選擇【設定坐標軸格式】，在標籤選項裡面設定標籤位置為【低】，這樣坐標軸標籤就從右邊調整到左邊了。最後將圖表上方不需要的坐標軸標籤刪除掉，操作步驟如下圖17所示。

 

Step 14.  圖表區域命名

圖表中每格區域歸屬的資訊需要插入一個文字方塊進行標註。插入文字方塊，在選中文字方塊的狀態下，在編輯列中輸入等號，然後滑鼠選中需要關聯資料的單元格，按確認鍵確認。以湖北地區為例，操作步驟如下圖所示。

 

Step 15.  設定平滑線

為了讓圖表看起來更加“圓潤”，我們需要去除折線圖的“稜角”。選中折線圖，單擊滑鼠右鍵，選擇【設定資料系列格式】，在【油漆桶】圖示的最下方勾選平滑線。操作步驟如下圖所示。

 

Step 16.  美化圖表

對於圖表標題的設定，我們也可以參考前面的文字方塊關聯單元格資料的方法，這樣在我們後期改動資料的時候，就可以實現圖表資料動態更新了。剩下的就是給圖表設定一個你喜歡的配色，並將其設定為圓角，然後用文字方塊在圖表左下角新增資料來源和作者的資訊，讓圖表呈現的更加專業。操作步驟如下圖所示。