2015年6月14日 星期日

Excel-選取年月顯示不同圖表(動態圖表,INDIRECT,OFFSET)

如果在 Excel 中的多個資料表具有相同的外型,即表格一樣資料不一樣,想要製做成動態圖表,那就容易多了。因為網友常會問到用一個圖表就要顯示多個資料表的內容,希望使用表單工具(例如:微調按鈕、下拉式清單等),即可呈現不同的資料表的統計圖表。該如何製作這樣的圖表呢?
本例以 8 個工作表分別是2015年1,2,3,4月和2014年1,2,3,4月的工作表(如下圖),其資料表名稱的格式一致(皆為2015-1,表示2015年1月)。
藉由調整按鈕和下拉式清單來改變圖表的資料來源:
首先,你必須使用[開發人員]功能表來新增控制年和月的微調按鈕控制項表單:
以控制「年」為例,設定最小值為 2014、最大值為 2015,儲存格連結至:B1。
以控制「月」為例,設定最小值為 1、最大值為 4,儲存格連結至:D1。
接著,要來設計一個公式可以讀取年和月的參數,並且設計自動產生該年該月的資料表(如下圖)。
儲存格I2:=INDIRECT("'" & $B$1&"-"&$D$1&"'!"&ADDRESS(ROW(3:3),
COLUMN(B:B)))
透過 INIDIRECT 函數將字串轉換成實際的儲存格位址。(工作表名稱範例:2015-1)
複製儲存格I2,貼至儲存格AM11。
關於選取店名部分,可以藉由儲存格H2:H11的店名來建立下拉式清單。
對儲存格F1設定「資料驗證」,將驗證準則設定為允許:清單,來源:=$H$2:$H$11。
再來的步驟很重要,是建立動態圖表的關鍵因素。
要建立二個名稱,其中一個要指到某一個店名,另一個要指到該店名所對應的數值資料。
name:=OFFSET(DATA!$I$1,MATCH(DATA!$F$1,DATA!$H$2:$H$11,0),0,1,1)
range:=OFFSET(DATA!$I$1,MATCH(DATA!$F$1,DATA!$H$2:$H$11,0),0,1,31)
請注意:
(1)名稱的範圍請指定為目前的工作表(本例為:DATA)
(2)名稱中的參照請使用絶對位址,例如:DATA!$F$1。
再來,要如何在圖表中將兩個名稱予以套用呢?
只要選取圖表中的數列,按一下右鍵,選取「選取資料」。
在圖例項目中按一下:編輯。
編輯數列,修改如下:數列名稱:DATA!name,數列值:DATA!range。
其中 DATA 為本例的工作表名稱。
修改完後,觀察公式列上的公式已套用定義好的name和range名稱。
來到這裡,就算是大功告成。可以使用微調按鈕和下拉式清單來動態呈現圖表了,最後要提醒,資料放在各個工作表中,工作表名稱必須要有規則,才能在公式中取用。

沒有留言:

張貼留言

好康東東