2015年10月28日 星期三

Excel-利用下拉式選單顯示多個統計圖(OFFSET,資料驗證)

延續上篇文章:Excel-在統計圖中更改時間標籤的間距,參考下圖,如果想要將多個工作表中的資料共用一個圖表(折線圖),該如何處理?
在下圖中,原來的表1~表7是放在多個工作表中,現在把它集合在一起,想要透過儲存格I2中的下拉式選單來選取表的名稱,而折線圖自動會顯示該表的數值內容。
image

【操作與解析】
在儲存格I2中要使用下拉式選單,可以透過「資料驗證」,設定其準則:
儲存格內允許:清單
來源:表1,表2,表3,表4,表5,表6,表7
image
接著,你藉由表1來產生一個折線圖(相關步驟在此省略),然後點選折線圖,觀察資料編輯列中的公式。如下:
=SERIES(圖表!$B$15,圖表!$A$16:$A$27,圖表!$B$16:$B$27,1)
其中『圖表!$B$15』和『圖表!$B$16:$B$27』必須改為動態。
image
因此,要定義二個名稱:
DATA1:=OFFSET(圖表!$H$2,0,MATCH(圖表!$D$17,圖表!$H$2:$N$2,0)-1,1,1)
用以取代:『圖表!$B$15』
DATA2:=OFFSET(圖表!$H$3,0,MATCH(圖表!$D$17,圖表!$H$2:$N$2,0)-1,12,1)
用以取代:『圖表!$B$16:$B$27』
注意:公式中使用的位址要設定為絶對參照,領域要設為「活頁簿」。
image
接著,選取折線圖,將公式:
=SERIES(圖表!$B$15,圖表!$A$16:$A$27,圖表!$B$16:$B$27,1)
改為:
=SERIES(折線圖.xlsx!DATA1,圖表!$A$16:$A$27,折線圖.xlsx!DATA2,1)
如此,便能利用儲存格I5的下拉式選單,來動態顯示想要的資料表的折線圖了。
image

沒有留言:

張貼留言

好康東東