如果在 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名稱。

來到這裡,就算是大功告成。可以使用微調按鈕和下拉式清單來動態呈現圖表了,最後要提醒,資料放在各個工作表中,工作表名稱必須要有規則,才能在公式中取用。

【延伸閱讀】

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

arrow
arrow
    全站熱搜

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