【原】excel圖表技巧：如何制作一張直觀互動(dòng)的動(dòng)態(tài)圖表

部落窩教育BLW 2019-12-09

展開(kāi)全文

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

＊＊＊＊＊＊＊＊＊

在企業(yè)生產(chǎn)過(guò)程中，經(jīng)常需要做產(chǎn)量分析，如果有一張圖表，它能根據(jù)分析者的選擇，而動(dòng)態(tài)顯示任意時(shí)間段的產(chǎn)量情況，并且還能通過(guò)標(biāo)題欄給出對(duì)應(yīng)時(shí)間段的平均和最低產(chǎn)量數(shù)據(jù)，那將使我們的效率大大提升。（效果如下圖所示）

首先，我們來(lái)分析需求：①圖表需要?jiǎng)討B(tài)顯示任意時(shí)間段的產(chǎn)量數(shù)據(jù)；②標(biāo)題欄需要?jiǎng)討B(tài)顯示對(duì)應(yīng)時(shí)間段的平均和最低產(chǎn)量數(shù)據(jù)。

說(shuō)到excel中的“動(dòng)態(tài)”二字，那就不得不提到OFFSET函數(shù)了。在制作動(dòng)態(tài)圖表時(shí)，首先需要用OFFSET函數(shù)定義動(dòng)態(tài)區(qū)域。

操作步驟：

① 定義兩個(gè)名稱(chēng)區(qū)域

本例需要?jiǎng)?chuàng)建兩個(gè)名稱(chēng)區(qū)域，作用是定義兩個(gè)單元格區(qū)域。它們選取的范圍是動(dòng)態(tài)的，能夠根據(jù)用戶的操作而自動(dòng)改變，這兩個(gè)動(dòng)態(tài)單元格區(qū)域中的數(shù)據(jù)，將作為我們制作圖表的數(shù)據(jù)源。

我們?cè)诠ぷ鞅?span style="margin-top: 0px;margin-bottom: 0px;padding: 0px;">E3單元格輸入任意起始日期，在F3單元格輸入任意結(jié)束日期，在C3單元格輸入公式“=F3-E3+1”，得到起始日期到結(jié)束日期的總天數(shù)。

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

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

OFFSET函數(shù)可以實(shí)現(xiàn)對(duì)單元格區(qū)域的動(dòng)態(tài)選擇，語(yǔ)法結(jié)構(gòu)為：OFFSET（reference，rows，cols，[height]，[width]）。其中，reference參數(shù)用來(lái)定義區(qū)域的起始位置，rows參數(shù)用來(lái)定義行偏移量，cols參數(shù)用于定義列偏移量，height參數(shù)用于定義引用的行數(shù)，width參數(shù)用于定義引用的列數(shù)。以第一個(gè)公式“=OFFSET(Sheet1!$B$3,Sheet1!$E$3-Sheet1!$A$3,0,Sheet1!$C$3,1)”為例，意思是以B3單元格為起始位置，偏移E3單元格（起始日期）與A3單元格（2019年1月1日）的差的行數(shù)，不偏移列，引用一個(gè)“C3”行（C3單元格中的數(shù)據(jù)指定引用的行數(shù)），1列的數(shù)據(jù)區(qū)域。

對(duì)OFFSET函數(shù)不太熟悉的小伙伴可以點(diǎn)擊《Excel進(jìn)階之路必學(xué)函數(shù)：動(dòng)態(tài)統(tǒng)計(jì)之王——OFFSET（上篇）》教程學(xué)習(xí)一下。

② 設(shè)置圖表數(shù)據(jù)

在定義了動(dòng)態(tài)區(qū)域后，接著就可以創(chuàng)建圖表啦。將圖表的數(shù)據(jù)系列和水平坐標(biāo)軸標(biāo)簽指定為需要的動(dòng)態(tài)區(qū)域，即可實(shí)現(xiàn)動(dòng)態(tài)圖表。

選擇A3:B8單元格區(qū)域，插入“簇狀柱形圖”。鼠標(biāo)右鍵單擊圖表，在彈出的關(guān)聯(lián)菜單中，點(diǎn)擊“選擇數(shù)據(jù)”，彈出 “選擇數(shù)據(jù)源”對(duì)話框。

在對(duì)話框的左側(cè)“圖例項(xiàng)（系列）”欄中單擊“編輯”按鈕，彈出“編輯數(shù)據(jù)系列”對(duì)話框。在對(duì)話框中的“系列名稱(chēng)”處輸入“=Sheet1!$B$2”（直接點(diǎn)擊表格中的B2單元格即可），在“系列值”處輸入公式“=Sheet1!Yaxis”，表示該數(shù)據(jù)系列指定為“Yaxis”名稱(chēng)區(qū)域中的數(shù)據(jù)，完成設(shè)置后單擊“確定”按鈕關(guān)閉對(duì)話框。

接著，在“選擇數(shù)據(jù)源”對(duì)話框的右側(cè)“水平（分類(lèi)）軸標(biāo)簽”欄中單擊“編輯”按鈕，彈出“軸標(biāo)簽”對(duì)話框。在對(duì)話框的“軸標(biāo)簽區(qū)域”處輸入公式“=Sheet1!Xaxis”，表示將軸標(biāo)簽設(shè)置為“Xaxis”名稱(chēng)區(qū)域中的數(shù)據(jù)，完成設(shè)置后單擊“確定”按鈕關(guān)閉對(duì)話框。

通過(guò)上述步驟的設(shè)置，只需要在E3單元格和F3單元格中分別輸入任意的起始日期和結(jié)束日期，就能動(dòng)態(tài)的在圖表中顯示對(duì)應(yīng)時(shí)間段的產(chǎn)量情況。

③ 設(shè)置動(dòng)態(tài)標(biāo)題

完成了第一個(gè)需求，接下來(lái)我們來(lái)完成第二個(gè)：使標(biāo)題欄動(dòng)態(tài)顯示對(duì)應(yīng)時(shí)間段的平均和最低產(chǎn)量數(shù)據(jù)。

怎樣讓圖表標(biāo)題動(dòng)態(tài)顯示分析數(shù)據(jù)？我們?cè)谝粋€(gè)單元格中，使用公式對(duì)數(shù)據(jù)進(jìn)行計(jì)算，在圖表中插入標(biāo)題，再讓標(biāo)題框引用該單元格數(shù)據(jù)就可以了。

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

使用AVERAGE函數(shù)和MIN函數(shù)分別求出指定時(shí)間段產(chǎn)量區(qū)域“Yaxis”中的平均值和最低產(chǎn)量數(shù)據(jù)。再使用ROUND函數(shù)，將得到的結(jié)果按指定位數(shù)進(jìn)行四舍五入。最后用連接符“&”，將各個(gè)字段進(jìn)行連接。

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