技巧區‎ > ‎中級程度‎ > ‎

兩層的下拉式選單 [2005年7月]

張貼者:2010年5月29日 上午6:56Carson Cheng   [ 已更新 2010年5月29日 上午6:58 ]
也許你知道利用「資料|驗證|清單」,可以在儲存格內做到下拉式選單的效果:


但是要是你希望用戶輸入兩個數據,這兩個數據都是在預設的選單裡,但第二個選單有很多個,選哪一個則由第一個數據來決定...... 舉個例子吧!你要用戶輸入一個電腦產品的種類及型號,型號由產品決定,資料庫如下:
電腦產品 主機型號 滑鼠型號  喇叭型號
主機   B0123  M234   S9234
滑鼠   B3434  M355   S8998
喇叭   B2343  M323   S0943
          M236   S0248
                 S9348

如果用戶在第一個下拉式選單選滑鼠,則第二個下拉式選單應給用戶從M234、M355、M323、M236裡選型號,如此類推。

你不知道Excel可以做到這樣的效果?想學嗎??耐心看下去吧!

這裡需要幾個技巧:
● 相對和絕對位址的分別
● 範圍名稱裡可以定義函數的特性(當然你要知道什麼叫範圍名稱)
● CHOOSE()和MATCH()函數

(我好像在介紹小菜的材料那樣。)

步驟如下:

  1. 首先要定義好四個範圍名稱。身為Excel高手的你,又怎可以一個一個那樣子去定義呢?所以我們要把資料表選起來,然後選「插入|名稱|建立」,只勾選「頂端列」,按「確定」。

  2. 你可以在左上方的範圍名稱框內找到四個新定義好的名稱:

  3. 現在可以定義第一層的選單:

    1. 選取需要第一層下拉式選單的儲存格(A2:A11)
    2. 選「資料|驗證|設定|(儲存格內允許)清單」,來源輸入「=電腦產品」,記得一定要在前面加上=號(公式的意思):


  4. 接下來我們要定義一個動態的範圍名稱,其範圍可根據公式所計算出來的值而改變。

    1. 首先選B2(這一步很重要,不然相對位址的設定會錯誤)
    2. 選「插入|名稱|定義」,「現有名稱」輸入「選項」,「參照到」輸入以下的公式:
        =CHOOSE(MATCH(Sheet1!A2,電腦產品,FALSE),主機型號,滑鼠型號,喇叭型號)
        要注意的是A2一定要為相對位址,不能是絕對位址$A$2。公式的意思是,按照左方儲存格的數值,查是「電腦產品」範圍中的第幾列,如果是1,則返回「主機型號」的範圍地址,如此類推。
    3. 按「確定」


  5. 現在要定義第二層下拉式選單了!

    1. 選取需要第二層下拉式選單的儲存格(B2:B11)
    2. 選「資料|驗證|設定|(儲存格內允許)清單」,來源輸入「=選項」
    3. 按「確定」


  6. 最後,當然是要測試囉!在第一層選「滑鼠」,在第二層順利看到滑鼠的型號:

  7. 大功告成!


注意:
● 在第1步定義範圍的方法很方便,但如果每欄的記錄筆數不一樣,選單的末端可能會有空列,而用戶是可以選擇那些空白的項目。
● 除了使用資料驗證,還可以用表單工具列裡的工具:
  
  詳細情況,可參考附檔。


ċ
2_16.zip
(5k)
Carson Cheng,
2010年5月29日 上午6:56
ċ
2_16_1.zip
(5k)
Carson Cheng,
2010年5月29日 上午6:58
Comments