【Excel教學】製作兩層以上連動的下拉式選單

文、意如老師

 

 

填寫表單時，總是有一些固定欄位需要填寫，例如地址中“縣市“,”鄉鎮“，讓使用者輸入實在是太擾民了，也容易誤植一些文字，所以我們今天要來實作下拉選單。

已縣市為例：選擇第一層後（縣市）再選第二層（鄉鎮）

而第二層選單會為第一層選完後的結果自動更新下拉選單。

 

 

 

例：選完第一層下拉（花蓮縣）

 

 

 

 

鄉鎮的下拉會自動更新只有花蓮縣的縣市讓使用者選擇

 

 

 

 

 

 

 

任務一：建立資料

 

先準備第一層與第二層的清單資料，如下表

宜蘭縣	花蓮縣	金門縣	南投縣
三星鄉	玉里鎮	金沙鎮	中寮鄉
大同鄉	光復鄉	金城鎮	仁愛鄉
五結鄉	吉安鄉	金湖鎮	水里鄉
冬山鄉	秀林鄉	金寧鄉	名間鄉
壯圍鄉	卓溪鄉	烈嶼鄉	竹山鎮
宜蘭市	花蓮市	烏坵鄉	信義鄉
南澳鄉	富里鄉		南投市
員山鄉	新城鄉		埔里鎮
釣魚臺	瑞穗鄉		草屯鎮
頭城鎮	萬榮鄉		國姓鄉
礁溪鄉	壽豐鄉		魚池鄉
羅東鎮	鳳林鎮		鹿谷鄉
蘇澳鎮	豐濱鄉		集集鎮

 

 

 

縣市是第一層下拉清單(A1:D1)，鄉鎮為第二層(A2:D14)

 

 

 

 

實作：解題請參考（任務三，任務五）

1. G2儲存格，新增縣市的下拉選單（為第一層）

2. H2儲存格，待第一層選單被選擇後，動態更新第二層下拉選單

 

 

 

任務二：幫儲存格取名字

 

1. 選取A1-D14儲存格

2. 工具列 ▶ 公式

3. 從選取範圍建立

4. 於以下位置建立名稱 ▶ 頂端列

5. 確定

 

 

 

 

點選名稱管理員，查看是否建立成功，確認完後按下關閉即可。

 

 

 

 

任務三：建立第一層選單

 

G2儲存格，新增縣市的下拉選單（為第一層）

1. 點選G2儲存格

2. 工具列 ▶ 資料

3. 資料驗證

 

 

 

 

1. 資料驗證準則 ▶ 選擇清單

2. 來源

3. 選擇A1:D1

4. 確定

 

 

 

 

已成功建立一層選單，如下圖：

 

 

 

 

 任務四：認識INDIRECT( )函數 

 

INDIRECT( )可傳回所指定的參照，而該參照會立刻進行對照並顯示其內容。

簡單來說就是讓 INDIRECT 從其他的地方取得文字的儲存格位置，然後再去把對應位置的資料取出來

先準備資料如下：

1. 在A1: B3 儲存格輸入資料

2. 在D2輸入要查詢的儲存格位置

3. 在F2輸入公式 =INDIRECT(D2)。D2為可輸入要查詢的儲存格位置

 

 

 

檢驗效果：

1. F2儲存格公式為：=INDIRECT(D2)，要找尋的是D2儲存格目前值為A2，因此會抓出user-A2的值。

2. F3儲存格公式為：=INDIRECT(D3)，要找尋的是D3儲存格目前值為A3，因此會抓出user-A3的值。

 

 

 

 

任務五：建立第二層連動選單

 

1. 點選h2儲存格

2. 工具列 ▶ 資料

3. 資料驗證

 

 

 

 

資料驗證準則 ▶ 允許 ▶ 選擇“清單”

來源：

輸入公式 ▶ =INDIRECT($G$2) ▶ 確定

 

 

 

 

驗證結果：

先選擇第一層縣市G2 下拉：例如值為“宜蘭縣“，再看看第二層選單H2鄉鎮的下拉選單是否正常更新為宜蘭縣的鄉鎮。

完成檔如下：

 

 

 

 

 

 

 

FB粉絲團：https://www.facebook.com/lccnetzone
YouTube頻道：https://www.youtube.com/user/LccnetTaiwan
痞客邦Blog：http://lccnetvip.pixnet.net/blog