【Google 試算表】如何建立多層下拉式選單?先從第 2 層學起來!

2022 年 11 月 1 日 更新

How to Create a Dependent Drop Down List in Google Sheets 封面圖片

Google 試算表使用下拉選單來輸入資料,是很常見的技巧之一,但是如果想要建立第二個下拉選單,而且選單的內容要根據第一層的選項來變動,設定上就會複雜一點,不過完成這種關聯性的選單之後,也能讓表格功能更加多元!

製作 兩層下拉式選單

如果會在Google試算表裡建立單一個下拉選單的話,那通常第二層應該就不會太難了,大致上會需要用到下列這三種工具:

  • 儲存格範圍命名
  • INDIRECT 函數
  • 資料驗證

接下來開始講解怎麼運用這三種工具製作兩層式的下拉式選單:

Step 1:開啟已命名範圍

首先,針對要製作下拉選單所參考的資料範圍進行「儲存格命名」(後面使用的 INDIRECT 函數會用到),可以先點擊上面功能選單的 資料已命名範圍,它可以打開工作表右側已命名範圍的操作介面。

打開功能選單的資料→已命名範圍


Step 2:幫儲存格命名

將六年級所要顯示的清單範圍框選起來,並且在公式列最左邊的 名稱欄 中替這個範圍命名為「六年級」。

接下來也可以同時替「五年級」跟「四年級」的儲存格範圍命名。

幫儲存格命名

命名好的範圍,都會顯示在 已命名範圍 的介面當中。想管理、修改或刪除名稱的話,都可以從這邊操作。

管理「已命名範圍」


Step 3:資料驗證

接下來要先製作「第一層」的下拉選單,所以點擊功能選單的 資料資料驗證,打開驗證資料的介面。

打開功能選單的資料→資料驗證


Step 4:使用範圍內的清單

進入「驗證資料」的介面之後,條件的設定保留預設的 範圍內的清單 就好,然後把「第一層」要顯示的儲存格資料範圍填寫進去,也就是 六年級五年級四年級 這三個儲存格範圍,設定好之後就可以按 儲存

範圍內的清單


Step 5:完成第一層下拉式選單

這樣「第一層」的下拉選單就可以使用了!

完成第一層下拉式選單


Step 6:設定 INDIRECT 函數

接著要繼續設定第二層的下拉選單,先在儲存格裡面輸入 INDIRECT函數,函數裡面的參照就可以指向「第一層下拉選單」的儲存格。

輸入INDIRECT函數

完成函數設定之後,可以試試更換「第一層下拉選單」的選項,INDIRECT 函數所回傳的資料也會跟著變動。

INDIRECT函數回傳的資料


Step 7:資料驗證

再次打開「驗證資料」的介面,條件一樣使用 範圍內的清單,然後點擊旁邊「框選範圍」的圖示。

資料驗證


Step 8:使用 INDIRECT 函數所回傳的清單

這次要框選的範圍,就是 INDIRECT 函數所回傳的資料範圍,如果回傳的資料有三格,那就是要把回傳的三個資料都框選起來。框選完範圍之後,按 確定 完成資料驗證。

這裡需要注意框選的範圍,回傳的資料都要框選到,不能只框選一格,否則第二層下拉選單也只會有一個選項。

框選INDIRECT函數所回傳的資料範圍


完成多層下拉式選單

最後就可以看到剛製作好的「第二層下拉式選單」了!而第二層打開的選單內容會跟第一層的選項一起連動。

如果第一層選項為「六年級」,那第二層的選單內容就會出現:六年 A 班、六年 B 班及六年 C 班,也就是儲存格所命名的範圍。

完成多層下拉式選單

完成多層下拉式選單


Binance 幣安推薦碼 VU3NZ3AJ
[ 這 是 加 密 貨 幣 交 易 所 推 廣 活 動 廣 告 ]


搭配技巧

技巧一:為命名的資料標示層數

如果要製作的資料範圍有點多的話,記得要幫每個命名的資料範圍標示層數,並且做一下分組、分類,這樣之後想要修改的話,才不會打亂資料

為命名的資料標示層數 方便分類管理


技巧二:可以從別張工作表中提取資料

資料雖然可以用隱藏或分組的功能來收納起來,但是也可以放在其它工作表當中。

因為使用 資料驗證INDIRECT函數 時,是可以跨工作表的,雖然製作下拉選單的時候要一直切換工作表,但是完成之後的工作表就會簡潔許多。

可以從別張工作表提取資料

將資料來源跟下拉選單分開



切換選單內容時,會出現警示符號

雖然使用這個方式可以製作出「第二層下拉式選單」,不過在切換第一層的選項時,第二層的儲存格就會出現警示的符號。

切換第一層的選項時,第二層的儲存格會出現警示的符號

結語

在使用 Google 試算表或是 Excel 的時候,可能偶爾會有這類有關聯性的多層下拉選單的製作需求,流程上可能會有點差異,但概念上是差不多的,同樣都會額外設定比較多的東西,這時如果資料沒有分類清楚,就很容易弄亂。

多多善用以上提供的小技巧,或是自己管理表格資料常用的習慣,後續要修改資料時,就不會花太多時間囉!

如果還需要建立第三層的下拉選單,原理就跟兩層的下拉選單差不多,只是要額外設定新的範圍命名新的 INDIRECT 函數,然後再做好分類管理就好。

延伸閱讀

References:Productivityspot文件編輯器說明

利益揭露:網站內部分可見的連結/代碼有參與聯盟計劃,如果您透過這些連結/代碼購買商品或註冊會員的話,並不會有任何損失或額外費用的產生,還可能獲得些許優惠,而我僅會獲得一小部分的佣金,能幫助我持續營運這個網站,並且更有動力進行創作。

喜歡這篇文章嗎?分享給你的朋友吧