Excel-設定圖表只顯示指定起迄月份的資料(動態圖表)

如果想要依指定起、訖的月份來顯示統計圖表,該如何處理?

在下圖中,指定起始月份和終止月份,圖表只會顯示該區間的統計資料。

Excel-設定圖表只顯示指定起迄月份的資料(動態圖表)

【設計與解析】

選取儲存格A3:A29,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

1. 依指定起迄月份找出資料的起始列和終止列

起始列:=MIN(IF(MONTH(日期)>=E3,ROW(日期),""))

終止列:=MAX(IF(MONTH(日期)<=E4,ROW(日期),""))

利用 MINMAX 函數找出日期的月份大於起始月份的最值和小於終止月份的最大值。

以起始月:4、終止月:8 為例,日期區間在第 10 列和第 20 列之間。

Excel-設定圖表只顯示指定起迄月份的資料(動態圖表)

觀察新增的圖表在公式列裡的內容,你可以發現其由日期和數值兩個欄位的內容組成。

既然要做成動態的圖表,就必須以公式來取代這兩個固定儲存格範圍。

Excel-設定圖表只顯示指定起迄月份的資料(動態圖表)

2. 以 OFFSET 函數定義儲存格範圍的名稱

OFFSET 函數來取代統計圖表要使用的資料區間,並且將其設定為「名稱」。

show1:=OFFSET(DATA!$A$3,DATA!$M$3-3,0,DATA!$M$4-DATA!$M$3+1,1)

用以取代原儲存格範圍A4:A29。

show2:=OFFSET(DATA!$B$3,DATA!$M$3-3,0,DATA!$M$4-DATA!$M$3+1,1)

用以取代原儲存格範圍B4:B29。

公式受到儲存格E5(起始列)和儲存格E6(終止列)的控制,所以可以動態顯示圖表。

因為要用在名稱的定義上,所以公式中都採用「絶對位址」。(其中「DATA」是這個工作表的名稱)

Excel-設定圖表只顯示指定起迄月份的資料(動態圖表)

3. 置換統計圖上的公式

原公式:

=SERIES(工作表2!$B$3,工作表2!$A$4:$A$29,工作表2!$B$4:$B$29,1)

置換為:

=SERIES(DATA!$B$3,圖表.xlsx!show1,圖表.xlsx!show2,1)

其中「圖表」是這個檔案的名稱,圖表.xlsx!show1表示「圖表」檔案中的show1儲存格範圍。

Excel-設定圖表只顯示指定起迄月份的資料(動態圖表)

現在,只要設定起始月份和終止月份,圖表即只會顯示該月份區間的統計資料。

Excel-設定圖表只顯示指定起迄月份的資料(動態圖表)

【參考資料】

image OFFSET 函數參考微軟提供的說明網頁:OFFSET 函數
 

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel OFFSET 動態圖表
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(0) 人氣()