動態圖表是我們平時工作中,十分常用的一類圖表。與靜態圖表不同,它能更加直觀、靈活的展示數據,可以讓用戶進行交互式的比較分析,是圖表分析的較高級形式。那么今天就和大家說說如何用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教程