2011年4月6日 星期三

Excel-利用Offset函數製作動態圖表

當你在 Excel 的工作表中輸入一個資料表(如下圖),利用這些資料要來建立一個統計圖,希望能按不同季別和不同人員來分表顯示統計圖,該如何以一個圖表來製作這個動態圖表呢?

由於圖表中的內容必須是動態的位址,所以做法上可以使用控制項表單捲軸來產生控制數字,再來控制Offset函數,使其產生不同的位址,並且將此位址以名稱來定義,然後將名稱代入圖表中使用。

(1) 顯示不同季別統計圖

先利用控制項表單捲軸來產生1~4的數字(也可以自行輸入),置於儲存格B8中。

接著設定兩個名稱 data1 和 data2:

data1:=OFFSET(工作表1!$B$1,,工作表1!$B$8-1,,)

儲存格B8=1,data1 = $B$1;儲存格B8=2,data1 = $C$1;…

data2:=OFFSET(工作表1!$B$2,,工作表1!$B$8-1,5,)

儲存格B8=1,data2 = $B$2:$B$6;儲存格B8=2,data2 = $C$2:$C$6;…

先以儲存格A1:B6,做出一個統計圖(例如:長條圖),再選取圖表中的數列,於公式中將 data1 和 data2 取代原來的內容,按下 Enter 鍵,即可完成。只要控制捲軸,即可依不同季別顯示內容。

(2) 顯示不同人員統計圖

先利用控制項表單捲軸來產生1~5的數字(也可以自行輸入),置於儲存格B8中。

接著設定兩個名稱 data3 和 data4:

data3:=OFFSET(工作表2!$A$2,工作表2!$B$8-1,,,)

儲存格B8=1,data3 = $A$2;儲存格B8=2,data3 = $A$3;…

data4:=OFFSET(工作表2!$B$2,工作表2!$B$8-1,,,4)

儲存格B8=1,data4 = $B$2:$E$2;儲存格B8=2,data4 = $B$3:$E$3;…

先以儲存格A1:E2,做出一個統計圖(例如:長條圖),再選取圖表中的數列,於公式中將 data3 和 data4 取代原來的內容,按下 Enter 鍵,即可完成。只要控制捲軸,即可依不同人員顯示內容。

特別提醒:
定義名稱中的位址務必使用絶對位址,例如:$B$1,並且要以完整名稱表示,例如:工作表2!$B$1,不然無法成功。

沒有留言:

張貼留言

好康東東