2011年5月1日 星期日

Excel-依選單內容顯示圖表

在 Excel 的工作表中有一個資料表(如下圖上),希望利用下拉式選單來挑選姓名,並依不同人員顯示不同的統計圖表內容(如下圖下),並且將被選取人員的資料以較深的底色標示,該如何處理呢?

1. 選取儲存格B14,設定資料驗證為「資料驗證準則:清單,來源:=$A$2:$A$12」。

2. 選取儲存格A1:G2,製作一個直條圖的統計圖表。

3. 選取儲存格B2:G12,設定格式化規則為「=$A2=$B$14」,將底色改為較深的綠色和文字色彩為紅色。

如此,會依儲存格B14的姓名,將該姓名的資料改變底色和文字色彩。

4. 選取圖表中的數列來觀察:

圖表中的資料來源分為三個部分,分別是欄名、列名和資料。所以只要能隨著儲存格B14的姓名,對應到不同的A欄姓名和資料,即可改變圖表內容。

5. 建立 DATA1 和 DATA2 二個名稱

選取[資料/名稱管理員]按鈕,開啟[名稱管理員]對話框。

按一下[新增]按鈕,輸入名稱:DATA1,範圍:工作表1,參照到:

DATA1:=OFFSET($A$2,MATCH($B$14,$A$2:$A$12,0)-1,,,)

利用MATCH函數找出儲存格B14所指姓名在儲存格A2:A12中的第幾個,並利用OFFSET函數改變其位址。

DATA2:=OFFSET($B$2,MATCH($B$14,$A$2:$A$12,0)-1,,,6)

提醒:位址要以絶對位址表示。

原理同 DATA2 說明,並將欄數設為 6 欄。

6. 選取圖表中的數列,將其內容改為:=SERIES(工作表1!DATA1,工作表1!$B$1:$G$1,工作表1!DATA2,1)

如此即可依儲存格B14的內容,調整圖表顯示的內容。如此即可達到依下拉式清單內容,顯示不同圖表內容,是一個節空間,並且可以動態呈現的作法。

思考一下,如果改成要以不同項目(電話、電冰箱、電鍋、電視、電子琴、電磁爐)來顯示圖表,該如何處理呢?

2 則留言:

  1. 火星人大哥您好
    您寫的這個網誌實在是太棒了
    我從您的網誌裡學到好多

    不過有關本篇的第五點
    我不知道要怎麼做耶
    DATA1要做在哪裡呢??

    可以請您說明一下嗎?
    謝謝

    回覆刪除
  2. 已將說明加入文章中,請參閱!主要是:
    選取[資料/名稱管理員]按鈕,開啟[名稱管理員]對話框。
    按一下[新增]按鈕,即可輸入DATA1的內容。

    回覆刪除

好康東東