艾倫目前已經寫過3篇有關 Excel下拉式選單的文章,這篇要繼續分享「Excel下拉式兩層、三層選單連動」教學,之前分享的下拉式選單教學都比較基礎,這篇直接來到進階教學,當我們選擇台北時,第二層下拉式選單就會自動出現台北全部的行政區,假設選擇好台北市、大同區,第三層選單就會直接出現郵遞區號 103,一起來看看這連動的下拉式選單要如何設定?
更多 Excel 熱門文章:
● 2步驟建立 Excel下拉式選單內容,Excel新手必學小技巧!
● 3步驟新增可自動更新的動態化 Excel 下拉式選單
● [教學] Excel 如何建立打勾、打叉下拉式選單?
● 3招 Excel 快速調整「表格順序」和「選取表格」,用「Shift + 滑鼠」就可
● Execl 善用「複製格式」,每一份表格內容都超整齊!
什麼是 Excel下拉式連動選單?
下拉式連動選單就像圖中,艾倫縣市選擇「台北」後,鄉鎮市區的行政區就會顯示台北相關的行政區,艾倫選擇「大同區」後,最後的郵遞區號就會顯示大同區的郵遞區號,這就是所謂的下拉式連動選單,一層一層都是相關的,後面會一步一步跟大家說明如何設定下拉式連動選單。
首先,輸入所有選單內容
這是艾倫這篇範例的下拉式選單內容,縣市、行政區、郵遞區號都一一先輸入完成。
三層連動式 Excel下拉選單教學:
第一層 Excel下拉式選單設定:
第一層下拉式選單是最容易的,這第一層的教學之前也有分享過(延伸閱讀:2步驟建立 Excel下拉式選單內容,Excel新手必學小技巧!),這邊就簡單跟大家說,選擇 O3 表格位置,接著選擇「資料 -> 資料工具 -> 資料驗證」。
開啟資料驗證視窗後,「儲存格內允許」改選「清單」,「來源」選擇縣市名稱 A2 – A5。
這樣「第一層下拉式選單」就設定完成。
第二層 Excel下拉式選單設定:
第二層下拉式選單我們要來設定行政區的部份,將「台北市C1 – 文山區C13」表格圈選起來,接著選擇「公式 -> 已定義之名稱 -> 從選取範圍建立」,這步驟是要建立名單管理員,以台北市為名稱,將所有行政區建立在台北市底下。
因為我們要以最頂端的「台北市」為建立名稱,所以這邊要選擇「頂端列」,新北市、桃園市、新竹縣都要分別做一樣的動作。
分別設定完成後,「名稱管理員」中就可以看到台北市、桃園市、新北市、新竹縣的清單。
接著在鄉鎮市區 P3 表格中選擇「資料 -> 資料工具 -> 資料驗證」,「儲存格內允許」改選「清單」,「來源」輸入「=INDIRECT($O3)」,第二層要跟第一層下拉式選單連動時,需要使用 INDIRECT 函數來指定。(小提醒:選擇表格時,會自動跳出 $O$3 這樣會直接鎖定 O3 這一格,要改成 $O3,這樣我們複製表格設定到 O4 時,會鎖定 O這欄,但數字欄位部份會更著變動。)
這樣「第二層下拉式選單」就設定完成,當我們選擇台北市,行政區下拉式選單就會出現台北市相關的行政區,選擇新北市就會出現新北市相關的行政區。
第三層 Excel下拉式選單設定:
最後我們開始來設定第三層郵遞區號選單的部份,郵遞區號是一區對一個,像中正區是100、大同區是103,所以我們先圈選 C2 – D13,接著選擇「公式 -> 已定義之名稱 -> 從選取範圍建立」。
郵遞區號是一區對一個,所以這邊名稱要選擇「最左欄」。
名稱管理員裡面就會看到行政區名稱對應著郵遞區號。
上面設定完成後,在郵遞區號 Q4 表格中選擇「資料 -> 資料工具 -> 資料驗證」,「儲存格內允許」改選「清單」,「來源」輸入「=INDIRECT($P3)」,第三層要跟第二層下拉式選單連動時,需要使用 INDIRECT 函數來指定。(小提醒:選擇表格時,會自動跳出 $P$3 這樣會直接鎖定 P3 這一格,要改成 $P3,這樣我們複製表格設定到 P4 時,會鎖定 P這欄,但數字欄位部份會更著變動。)
最後「第三層下拉式選單」就設定完成,當我們第一層選擇 台北市,第二層選擇 大同區,第三層下拉式就會出現大同區的郵遞區號103,再驗證一次,當我們第一層選擇 新北市,第二層選擇 板橋區,第三層下拉式就會出現板橋區的郵遞區號220。