延續上篇文章:Excel-在統計圖中更改時間標籤的間距,參考下圖,如果想要將多個工作表中的資料共用一個圖表(折線圖),該如何處理?

在下圖中,原來的表1~表7是放在多個工作表中,現在把它集合在一起,想要透過儲存格I2中的下拉式選單來選取表的名稱,而折線圖自動會顯示該表的數值內容。

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


【操作與解析】

在儲存格I2中要使用下拉式選單,可以透過「資料驗證」,設定其準則:

儲存格內允許:清單

來源:表1,表2,表3,表4,表5,表6,表7

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

接著,你藉由表1來產生一個折線圖(相關步驟在此省略),然後點選折線圖,觀察資料編輯列中的公式。如下:

=SERIES(圖表!$B$15,圖表!$A$16:$A$27,圖表!$B$16:$B$27,1)

其中『圖表!$B$15』和『圖表!$B$16:$B$27』必須改為動態。

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

因此,要定義二個名稱:

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』

注意:公式中使用的位址要設定為絶對參照,領域要設為「活頁簿」。

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

接著,選取折線圖,將公式:

=SERIES(圖表!$B$15,圖表!$A$16:$A$27,圖表!$B$16:$B$27,1)

改為:

=SERIES(折線圖.xlsx!DATA1,圖表!$A$16:$A$27,折線圖.xlsx!DATA2,1)

如此,便能利用儲存格I5的下拉式選單,來動態顯示想要的資料表的折線圖了。

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

arrow
arrow
    全站熱搜

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