2011年3月15日 星期二

Excel-在圖表中動態顯示固定量內容

在 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)

image

(2) 定義名稱

先定義 data1 和 data2 二個名稱:

image

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)

image

大功告成,你可以使用微調按鈕來調整想要看的資料區間,或是直接在儲存格F1中輸入一個起始數值也可以。

1 則留言:

  1. 你好,
    請問有2003版的方法嗎?
    我找這個功能找很久了,
    謝謝.

    回覆刪除

好康東東