顯示滾動數據的動態圖表

  顯示滾動數據的意思是指,隨著日期的變化,永遠滾動地顯示最近幾天的數據。

下圖中顯示了每天各種產品的銷售數量統計,我們需要做一個圖表,讓它總是顯示最近幾天的銷售數據,並且可以設定需要顯示數據的天數。

顯示滾動數據的動態圖表

效果如下。在C2單元格選擇一個數值,顯示相應天數的數據。當數據增多時,總是顯示最近的數據。

顯示滾動數據的動態圖表

操作步驟:

Step 1、定義名稱,設定各個數據系列及坐標軸。

定義名稱的應用範圍很廣,可以說是必備知識。如果你對定義名稱還不是很了解的話,請點擊下面的鏈接學習。

高手必備技能之自定義名稱

在這裡我們定義下面4個名稱,使用了Offset函數定義了動態的區域。如果對Offset函數的用法還不是很熟悉,請點擊下面的鏈接學習。

Offset函數,很好很強大!

定義名稱方法1

定義名稱:

掛式空調=OFFSET(數據!$A$2,,COUNTA(數據!$1:$1)-1,,-圖表!$C$2)

立式空調=OFFSET(數據!$A$3,,COUNTA(數據!$1:$1)-1,,-圖表!$C$2)

洗衣機=OFFSET(數據!$A$4,,COUNTA(數據!$1:$1)-1,,-圖表!$C$2)

坐標軸=OFFSET(數據!$A$1,,COUNTA(數據!$1:$1)-1,,-圖表!$C$2)

解釋:

從A列的一個單元格開始,

偏移0行,

總共有幾天的數據就偏移幾列,這時到達最後一天所在的單元格,

行高不變,還是1,

列寬是負的「圖表」工作表中C2單元格的值,表示從最後一天往前擴展數據區域。

這樣就得到了最近幾天的數據。

通過下圖來看,是不是能好理解點?例如,從A4單元格開始向右偏移9列,到達最後一天,然後再往回找7天,形成了最近7天的數據。

顯示滾動數據的動態圖表

定義名稱方法二

如果上面這個負值作為列寬不太好理解的話,我們還可以用下面的定義名稱。

定義名稱:

掛式空調=OFFSET(數據!$A$2,,COUNTA(數據!$1:$1)-圖表!$C$2,,圖表!$C$2)

立式空調=OFFSET(數據!$A$3,,COUNTA(數據!$1:$1)-圖表!$C$2,,圖表!$C$2)

洗衣機=OFFSET(數據!$A$4,,COUNTA(數據!$1:$1)-圖表!$C$2,,圖表!$C$2)

坐標軸=OFFSET(數據!$A$1,,COUNTA(數據!$1:$1)-圖表!$C$2,,圖表!$C$2)

解釋:

這個是常見的Offset用法,指明偏移幾列,指明偏移後數據區域的寬度。

定義名稱方法三

前幾天我們學習了Index函數的用法(點擊鏈接直達:Index和Match是一對好兄弟),我們還可以這樣來定義名稱。

定義名稱:

數據=OFFSET(數據!$A$1:$A$4,,COUNTA(數據!$1:$1)-1,,-圖表!$B$2)

坐標軸=INDEX(數據,1,)

掛式空調=INDEX(數據,2,)

立式空調=INDEX(數據,3,)

洗衣機=INDEX(數據,4,)

解釋:

「數據」這個定義名稱返回的是包含標題的整個數據區域,用Index取每行的數據,就得到了坐標軸和每個系列的區域,注意Index最後一個參數是空或者0,表示取整行的數據。這種方法看起來也比較簡潔。

Step 2、插入圖表,用定義名稱作為數據區域

選擇任意一個空白單元格,點擊【插入】選項卡下面的「圖表」中的柱形圖。

這時圖表上沒有任何內容,在空白柱形圖上點擊右鍵菜單中的「選擇數據」。

a. 先點擊下圖中左邊的「添加」按鈕來添加數據系列。

顯示滾動數據的動態圖表

依次添加三個數據系列,系列值就直接設為定義好的名稱,如下。

=’20170524 顯示滾動數據的動態圖表 – 2.xlsx’!掛式空調

=’20170524 顯示滾動數據的動態圖表 – 2.xlsx’!立式空調

=’20170524 顯示滾動數據的動態圖表 – 2.xlsx’!洗衣機

大家注意,圖表中使用定義名稱時,需要使用下面這樣的結構:

工作簿名稱!定義的名稱

中間是英文狀態下的嘆號,如果工作簿名稱中有空格等則工作簿名稱前後需要加單引號。

b.再點擊右邊的「編輯」按鈕,設置坐標軸的值。

=’20170524 顯示滾動數據的動態圖表 – 2.xlsx’!坐標軸

最後再做一些美化工作就完成了動態圖表的設置。