謝謝讀者提供題材!
→ 如果你有試算表的問題想問問,可以到這邊的
發問表單→ 如果你也希望我分享一些東西,可以到這邊的
徵文表單
我想做的兩層下拉式選單,是先點選第一層下拉式選單後,會出現第二層下拉式選單,並且會依照第一層的選項、第二層會出現相對應的項目。
你可以看看這邊的 GIF,觀察一下:
我在 A2 選了台北市,後面的 B2 就出現了台北市的行政區;選了台中市,也會出現台中市的;選了高雄市的,就出現高雄的。
我的解法是:用已命名範圍跟 INDIRECT 函式做,就可以了!
不過,要注意,如果你在工作表裡面有多個兩層下拉式選單,就不建議這樣做,可能要用別的方法比較好。
OK,我們開始囉!
用已命名範圍+INDIRECT 函式製作兩層下拉式選單
裡面會有兩個工作表,第一個是要製作兩層選單的地方,第二個是幫你準備好的台灣各行政區的資料:
我們等等會先從第二個工作表「資料驗證清單」開始第一步。
在這個工作表裡,第一列是你的第一層選單要呈現的項目,然後各欄則是第二層的資料。如果你想要製作自己的兩層下拉式選單,建議也可以做這樣的配置唷!接下來一步步帶你嘗試嘗試。
設定「已命名範圍」
我們接下來要幫每個縣市製作「已命名範圍」,準備給後續的 INDIRECT 函式使用。
先到目錄的「資料」>「已命名範圍」,點下去之後你會發現右邊會有個「已命名範圍」的窗格跳出來:
點選「+新增範圍」:
你應該會看到有兩個欄位,一個是預設名稱的「NamedRage1」、另一個是範圍的位址:
我們先從最左邊的「臺北市」開始做。
我們先把第一個欄位「NamedRage1」改成「臺北市」:
接下來指定「臺北市」的範圍。點第二個欄位的「田」後,選取臺北市下面的資料,從第二列選到資料的最後一列(A2:A13),按「確定」、再按「完成」。可以參考這邊的 GIF:
這樣就完成「臺北市」的已命名範圍囉!
這個步驟要不斷重複,直到把所有的欄位(新北市、桃園市、臺中市等)都設定完畢才行喔!全部都設定好的話,應該會像這樣,把所有欄位的已命名範圍都做好的狀況:
設定第一層下拉式選單(資料驗證)
我們切到第一個工作表「兩層選單」:
先來製作第一層下拉式選單吧!我們要在這邊放入各縣市的清單(臺北市、新北市、桃園市、臺中市等)。
選取 A2,也就是我們要做第一層下拉式選單的地方後,到目錄的「插入」>「下拉式選單」:
右邊應該又會彈出來一個窗格,這邊在「條件」選「下拉式選單(來自某範圍)」:
點選完後會像這樣:
範圍還是空的,我們要來指定一下。
點選「條件」下面欄位的「田」字圖示,然後切到「資料驗證清單」工作表,選取第一列有縣市名稱的範圍(A1:V1),但記得別選到最後一欄(W1)喔!一樣用 GIF 示範:
這樣第一層下拉式選單就完成了。
設定 INDIRECT
再來要利用 INDIRECT 可以找到參照已命名範圍資料的特性,生成第二層下拉式選單的資料來源。
我們先在第一層下拉式選單裡面選「臺北市」(或任何一項資料都可以):
再來,切換到「資料驗證清單」工作表,到最後一欄「INDIRECT」那邊(W2)。
這邊輸入 INDIRECT 函式,連接「兩層選單」工作表的第一層下拉式選單所在的儲存格('兩層選單'!A2):
=INDIRECT('兩層選單'!A2)
附上 GIF:
痛快按下 Enter 後,應該就會看到「臺北市」(或是你指定的縣市)下面的行政區了!你可以回到「兩層選單」工作表的第一層下拉式選單,點選不同的縣市,看看 INDIRECT 有沒有正確顯示出相對應的行政區。
到這邊只剩最後一步,我們再做第二個下拉式選單可以囉!
設定第二層下拉式選單(資料驗證)
回到「兩層選單」工作表,我們要在 B2 做第二層的下拉式選單。設定的方法和剛剛類似,只是要把範圍改成剛剛 INDIRECT 那欄。
選取 B2,到目錄的「插入」>「下拉式選單」:
條件的下拉式選單一樣改成「下拉式選單(來自某範圍)」:
再來指定資料範圍,一樣點取「田」字圖示,選取資料範圍出現後,選取「資料驗證清單」工作表 INDIRECT 所在的欄位,指定 W2:W:
'資料驗證清單'!W2:W
附上 GIF 給你參考:
為什麼這邊不限制列數,是因為不確定第一層指定縣市後,這邊 INDIRECT 的動態資料到底會多長,所以我就直接給 W2:W 了。不過如果你想省一點點效率,以這次的例子來說,你也可以指定 W2 到 W39(也就是 W2:W39),因為「高雄市」那欄資料的最尾端到第 39 列、是整組資料裡面最長的地方。
大功告成囉!回到「兩層選單」工作表,選第二層下拉式選單,應該就會出現相對應的行政區了:
你可以換成不同縣市(第一層下拉式選單),看看行政區有沒有跟著更動。如果有的話,就恭喜你做出兩層下拉式選單囉~
要注意的事情
這次是以台灣的行政區域做示範,你也當然可以依照你的需求,更改成不同的資料,但要注意幾件事:
- 「已命名範圍」的名稱要跟第一層下拉式選單的項目相符。
- 設定第二層下拉式選單的時候,取 INDIRECT 的範圍時別小氣,直接給到最後一列沒關係(除非你有把握資料的最大列數、或是你發現試算表效率變差的時候可以再把範圍改小)。你也可以把空白列刪掉,改善試算表讀取的速度一點點。
- 建議把兩層下拉式選單所在的工作表、和下拉式選單資料所在的工作表分開,方便管理。這是考慮到如果你的試算表是分享給其他人的時候,你可能也不希望他們動到你的資料,這時候如果工作表是分開的,你就可以在工作表上設定個別的權限,避免不必要的錯誤。
如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!
我是喜特先生,Mr. Sheet,我們下個教學見!