在 Excel 中,如果你有一個資料表(如下圖左),其中有日期和數量的資料(例如有100筆),如果你想要建立一個圖表,每次只顯示 8 筆,而且可以使用微調按鈕來顯示不同資料(如下圖左),該如何設計呢?
要處理這類的動態資料顯示,必須藉由 OFFSET 函數和定義名稱來處理,參考以下的步驟:
假設資料範圍為儲存格B2:C101,圖表要以長條圖每次只顯示 8 筆資料,並且利用微調按鈕來調整。
(1) 新增微調按鈕
首先,在[開發人員]功能表中新增一個微調按鈕:
接著,在微調按鈕上按一下右鍵,選取[控制項格式]選項。
然後,設定內容如下:
目前值:1,資料由第 1 筆開始。
最小值:1,資料為 1~100 中顯示的第 1 筆。(每次顯示 8 筆,顯示 1~8 筆)
最大值:92,資料為 1~100 的第 93 筆。(每次顯示 8 筆,顯示 93 ~ 100 筆)
儲存格連結:$F$1。(改變值要顯示在儲存格F1)
(2) 定義名稱
先定義 data1 和 data2 二個名稱:
data1:=OFFSET(工作表1!$B$2,工作表1!$F$1-1,,8,)
data2:=OFFSET(工作表1!$C$2,工作表1!$F$1-1,,8,)
要注意,其中的位址表示要以絶對位址表示:「工作表名稱!儲存格」。
如果儲存格F1為1,則:
OFFSET(工作表1!$B$2,工作表1!$F$1-1,,8,) = OFFSET($B$2,0,,8,) = 儲存格B2:B9
OFFSET(工作表1!$C$2,工作表1!$F$1-1,,8,) = OFFSET($C$2,0,,8,) = 儲存格C2:C9
如果儲存格F1為2,則:
OFFSET(工作表1!$B$2,工作表1!$F$1-1,,8,) = OFFSET($B$2,1,,8,) = 儲存格B3:B10
OFFSET(工作表1!$C$2,工作表1!$F$1-1,,8,) = OFFSET($C$2,1,,8,) = 儲存格C3:C10
依此類推 …
(3) 設定圖表
先選取圖表中的數列。
接著,將其公式改為:=SERIES(工作表1!$C$1,資料表1!data1,資料表1!data2,1)
大功告成,你可以使用微調按鈕來調整想要看的資料區間,或是直接在儲存格F1中輸入一個起始數值也可以。