2013年11月19日 星期二

[Excel] 圖片動態範圍選擇

1. 試著將圖表中資料的儲存格位址以動態方式呈現!藉由命名名稱來操作。如果你點選圖表中的曲線,可以看到曲線中的公式如下:
=SERIES(工作表1!$B$1,工作表1!$A$2:$A$20,工作表1!$B$2:$B$20,1)

由於其位址採用絶對位址表示,所以當在資料最後增加一筆資料時,並不在圖表的位址範圍內,所以圖表無法跟著變動。所以,採用以下的方式來改良:

新增一個名稱DATA1,定義為:
DATA1:=OFFSET(工作表1!$A$2,0,0,COUNTA(工作表1!$A:$A)-1,1)

此公式的作用是要將原本的絶對位址,改以OFFSET函數求得,而在OFFSET函數中以工作表1!$A$2為頭,而以COUNTA(工作表1!$A:$A)-1為尾,形成一個動態的位址範圍。

新增一個名稱DATA2,定義為:

DATA2:=OFFSET(工作表1!$B$2,0,0,COUNTA(工作表1!$B:$B)-1,1)

特別注意範圍要選取這個圖表所在的工作表名稱,例如:工作表1。
然後,點選圖表中的曲線,在資料編輯列,將原公式更改如下:
=SERIES(工作表1!$B$1,工作表1!DATA1,工作表1!DATA2,1)
即將原公式中的工作表1! $A$2:$A$20 改以DATA1取代,特別注意要寫成:工作表1!DATA1。
即將原公式中的 工作表1!$B$2:$B$20 改以DATA2取代,特別注意要寫成:工作表1!DATA1。

如此,只要在A欄和B欄中新增一筆資料時,圖表就會跟著改變。


說明:
OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。傳回的參照位址可以是單一個儲存格或一個儲存格範圍。可以指定要傳回來的列數和欄數。
語法:OFFSET(reference, rows, cols, [height], [width])

Reference:必要參數。用以計算位移的起始參照位址。

Rows:必要參數。左上角儲存格要往上或往下參照的列數。Rows 可以是正數 (表示在起始參照位址下方) 或負數 (表示在起始參照位址上方)。

Cols:必要參數。這是結果的左上角儲存格要往左或往右參照的欄數。Cols 可以是正數 (表示在起始參照位址右方) 或負數 (表示在起始參照位址左方)。

Cols 可以是正數 (表示在起始參照位址右方) 或負數 (表示在起始參照位址左方)。

Height:選用參數。這是所傳回參照位址的高度 (以列數為單位)。Height 必須是正數。


Width:選用參數。這是所傳回參照位址的寬度 (以欄數為單位)。Width 必須是正數。

Reference:  http://isvincent.blogspot.tw/2010/07/excel_4587.html

沒有留言:

張貼留言