【原】excel圖表技巧：如何制作一張直觀互動的動態圖表

編按：哈嘍，大家好！動態圖表是我們平時工作中，十分常用的一類圖表。與靜態圖表不同，它能更加直觀、靈活的展示數據，可以讓用戶進行交互式的比較分析，是圖表分析的較高級形式。那么今天就和大家說說如何用excel制作一張根據時間段而自動變化的動態圖表。趕緊來看看看吧！

＊＊＊＊＊＊＊＊＊

在企業生產過程中，經常需要做產量分析，如果有一張圖表，它能根據分析者的選擇，而動態顯示任意時間段的產量情況，并且還能通過標題欄給出對應時間段的平均和最低產量數據，那將使我們的效率大大提升。（效果如下圖所示）

首先，我們來分析需求：①圖表需要動態顯示任意時間段的產量數據；②標題欄需要動態顯示對應時間段的平均和最低產量數據。

說到excel中的“動態”二字，那就不得不提到OFFSET函數了。在制作動態圖表時，首先需要用OFFSET函數定義動態區域。

操作步驟：

①  定義兩個名稱區域 

本例需要創建兩個名稱區域，作用是定義兩個單元格區域。它們選取的范圍是動態的，能夠根據用戶的操作而自動改變，這兩個動態單元格區域中的數據，將作為我們制作圖表的數據源。 

我們在工作表E3單元格輸入任意起始日期，在F3單元格輸入任意結束日期，在C3單元格輸入公式“=F3-E3+1”，得到起始日期到結束日期的總天數。

接著在“公式”選項卡的“定義的名稱”組中單擊“定義名稱”，彈出“新建名稱”對話框。在對話框的“名稱”處輸入“Yaxis”，在“引用位置”處輸入公式“=OFFSET(Sheet1!$B$3,Sheet1!$E$3-Sheet1!$A$3,0,Sheet1!$C$3,1)”，該名稱用于獲取指定時間段對應的產量區域。最后單擊“確定”。

按照上述步驟，打開“新建名稱”對話框，在對話框的“名稱”處輸入“Xaxis”，在“引用位置”處輸入公式“=OFFSET(Sheet1!$A$3,Sheet1!$E$3-Sheet1!$A$3,0,Sheet1!$C$3,1)”，該名稱用于獲取指定時間段對應的日期區域。

OFFSET函數可以實現對單元格區域的動態選擇，語法結構為：OFFSET（reference，rows，cols，[height]，[width]）。其中，reference參數用來定義區域的起始位置，rows參數用來定義行偏移量，cols參數用于定義列偏移量，height參數用于定義引用的行數，width參數用于定義引用的列數。以第一個公式“=OFFSET(Sheet1!$B$3,Sheet1!$E$3-Sheet1!$A$3,0,Sheet1!$C$3,1)”為例，意思是以B3單元格為起始位置，偏移E3單元格（起始日期）與A3單元格（2019年1月1日）的差的行數，不偏移列，引用一個“C3”行（C3單元格中的數據指定引用的行數），1列的數據區域。

對OFFSET函數不太熟悉的小伙伴可以點擊《Excel進階之路必學函數：動態統計之王——OFFSET（上篇）》教程學習一下。

②  設置圖表數據

在定義了動態區域后，接著就可以創建圖表啦。將圖表的數據系列和水平坐標軸標簽指定為需要的動態區域，即可實現動態圖表。

選擇A3:B8單元格區域，插入“簇狀柱形圖”。鼠標右鍵單擊圖表，在彈出的關聯菜單中，點擊“選擇數據”，彈出 “選擇數據源”對話框。

在對話框的左側“圖例項（系列）”欄中單擊“編輯”按鈕，彈出“編輯數據系列”對話框。在對話框中的“系列名稱”處輸入“=Sheet1!$B$2”（直接點擊表格中的B2單元格即可），在“系列值”處輸入公式“=Sheet1!Yaxis”，表示該數據系列指定為“Yaxis”名稱區域中的數據，完成設置后單擊“確定”按鈕關閉對話框。

接著，在“選擇數據源”對話框的右側“水平（分類）軸標簽”欄中單擊“編輯”按鈕，彈出“軸標簽”對話框。在對話框的“軸標簽區域”處輸入公式“=Sheet1!Xaxis”，表示將軸標簽設置為“Xaxis”名稱區域中的數據，完成設置后單擊“確定”按鈕關閉對話框。

通過上述步驟的設置，只需要在E3單元格和F3單元格中分別輸入任意的起始日期和結束日期，就能動態的在圖表中顯示對應時間段的產量情況。

③  設置動態標題

完成了第一個需求，接下來我們來完成第二個：使標題欄動態顯示對應時間段的平均和最低產量數據。

怎樣讓圖表標題動態顯示分析數據？我們在一個單元格中，使用公式對數據進行計算，在圖表中插入標題，再讓標題框引用該單元格數據就可以了。

在D3單元格中輸入公式“="平均產量為"&ROUND(AVERAGE(Yaxis),1)&"方"&",日產量高于"&ROUND(MIN(Yaxis),1)&"方"”。

使用AVERAGE函數和MIN函數分別求出指定時間段產量區域“Yaxis”中的平均值和最低產量數據。再使用ROUND函數，將得到的結果按指定位數進行四舍五入。最后用連接符“&”，將各個字段進行連接。

然后在圖表中添加圖表標題，雙擊標題，在編輯欄中輸入公式“=Sheet1!$D$3”。引用D3單元格的數據作為圖表標題。

這樣一來，標題欄就能動態顯示數據分析結果，查詢數據是不是變得既方便又直觀？

④  美化圖表

雙擊圖表中數據系列彈出“設置數據系列格式”對話框，在“系列選項”設置欄中將“分類間距”設置為100%。

 在“圖表工具”欄中點擊“設計”，選擇自己喜歡的圖表樣式，并適當調小標題字體，刪除網格線和縱坐標軸，添加“數據標簽”，圖表制作完成的效果如下。

學會了么？照步驟練習一下吧！

＊＊＊＊部落窩教育－excel動態圖表制作步驟＊＊＊＊

原創：賦春風/部落窩教育（未經同意，請勿轉載）