實用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列的資料區域。

② 設定圖表資料

在定義了動態區域後，接著就可以建立圖表啦。將圖表的資料系列和水平座標軸標籤指定為所需的動態區域，即可實現動態圖表。

選擇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教學

以上是實用Excel技巧分享：帶你製作一張高大上的動態圖表的詳細內容。更多資訊請關注PHP中文網其他相關文章！