【Google 試算表】怎麼用 Arrayformula 製作多層下拉選單?

2022 年 11 月 1 日 更新

How to Create a Dependent Drop-Down List with arrayformula function in Google Sheets 封面圖片

當我們在 Google 試算表製作「多層下拉選單」的時候,有沒有想過其他方式也能製作多層選單呢?其實除了 Indirect 函數之外,還可以搭配其他函數,雖然不一定比較有效率,但也可以算是製作多層選單的方法之一喔!

會使用到 Google 試算表的那些功能?

在這篇文章當中,將會用到以下三種 Google 試算表的主要功能:

  1. 資料驗證
  2. Arrayformula 函數
  3. If 函數

其中「資料驗證」是製作下拉選單的必備工具。而若要製作多層關聯性的下拉式選單,除了搭配 Indirect 函數的方法之外,就是這篇會使用到的 Arrayformula 跟 If 函數了。


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


如何用 Arrayformula 製作多層下拉選單?

Step 1:驗證資料

首先點擊功能選單的 資料資料驗證 來打開「驗證資料」的介面。

先設定好將下拉選單要顯示的儲存格範圍,然後「條件」使用預設的 範圍內的清單,右邊的資料範圍則套用「第一層」選項的儲存格範圍,以下圖來說,就是框選「北部、中部、南部」這三格的儲存格範圍,然後按儲存即可。

驗證資料


Step 2:完成第一層下拉選單

這樣就完成了第一層的下拉式選單。

完成第一層下拉選單


Step 3:輸入Arrayformula函數

接下來為了要製作第二層的選單,會開始使用 Arrayformula 函數,首先在其他的儲存格內填入 Arrayformula 的公式

輸入Arrayformula函數


Step 4:輸入 IF 函數第一段

Arrayformula 括號後面接著填入「IF 函數公式」,先用「第一層下拉式選單」的那個儲存格當作判斷式。

以下面這張圖來說,當第一層的儲存格顯示的選項,剛好等於上面表格第一排的「北部」時,IF 函式就會回傳右邊第二層的「北部地區的資料範圍」,也就是「B2:D2」。這時候的公式為:

=Arrayformula(IF(B8=A2,B2:D2,

但如果條件不成立時,則要繼續運算下一個 IF 公式。

輸入 IF 函數第一段


Step 5:輸入 IF 函數第二段

當第一個判斷式不成立時就繼續執行第二個 IF 的公式。邏輯跟上一步差不多,只是這次成立的條件是「當第一層下拉選單的選項剛好等於第一層的中部時,就顯示中部右邊第二層的儲存格範圍」。公式這時候就是:

=Arrayformula(IF(B8=A2,B2:D2,IF(B8=A3,B3:D3,

一樣還有條件不成立的情況,也是繼續填入下一個 IF 公式。

輸入 IF 函數第二段


Step 6:直到完成整個公式

依循上面兩個步驟的邏輯,直到將最後的 IF 函數的條件式完成,才能完成你的 Arrayformula 公式。

以下屠來說,因為第一層的下拉選單只有三個選項,所以這裡的 IF 層數也只有三層,如果第一層的選項很多的話,IF 函數的層數也會跟著變多。

最後的 IF 公式,記得要填入輸入條件不成立時的第三個引數,由於沒有要繼續輸入 IF 函數了,所以這裡直接用兩個雙引號就好。總之公式如下:

=Arrayformula(IF(B8=A2,B2:D2,IF(B8=A3,B3:D3,IF(B8=A4,B4:D4,""))))

通常這種比較長的公式,都會在公式列中用「換行」的方式來整理公式。

完成整個公式


Step 7:Arrayformula 陣列展開

完成 Arrayformula 公式之後,它就會展開陣列,這個時候你可以切換看看「第一層選單」的選項,切換的同時, Arrayformula 的陣列也會跟著變動。

Arrayformula 陣列展開


Step 8:第二層資料驗證

Arrayformula 欄位測試沒問題的話,就可以製作「第二層的下拉選單」了。

一樣打開 資料資料驗證,把第二層選單的儲存格填入第一欄,然後「範圍內的清單」的資料範圍就直接框選 Arrayformula 函數所展開的陣列。

第二層資料驗證


完成第二層下拉式選單

最後完成「第二層下拉式選單」!

完成第二層下拉式選單


測試效果

最後還是要測試一下這種搭配的效果,應該會是正常的。

測試效果

注意事項

由於 Arrayformula 會展開資料的陣列,如果展開的儲存格範圍內有其他資料存在的話,陣列就會展開失敗,接著出現錯誤的訊息。這個時候就沒辦法用資料驗證來框選範圍囉,要把障礙物或是 Arrayformula 函數移到其他地方。

移動 Arrayformula 的儲存格時,別忘了先按「F4」來鎖定公式所套用的參照範圍喔。

結語

整體來說,雖然搭配 Arrayformula 函數的這種方式,不一定會比使用 Indirect 函數來得快,但也算是建立「多層下拉選單」的方法之一了,就給大家當作參考吧!

延伸閱讀

References:Extendoffice文件編輯器說明

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

歡迎訂閱電子報

訂閱《數位小幫手電子報》以獲取新文章或優惠資訊!

    我們尊重您的隱私,並可以隨時取消訂閱。

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