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

2023 年 2 月 7 日 更新

Arrayformula 製作多層選單

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

歡迎來到《數位小幫手》網站，閱讀文章之前請先了解我們的免責聲明與使用條款及隱私權政策。

點擊追蹤數位小幫手社群平台：Threads、Instagram、Twitter…等等。

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

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

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

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



如何用 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 函數所展開的陣列。

第二層資料驗證


完成第二層下拉式選單

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

完成第二層下拉式選單


測試效果

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

測試效果

訂閱《數位小幫手電子報》或《Telegram》

注意事項

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

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

結語

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

延伸閱讀

References：Extendoffice、文件編輯器說明 

原網站｜數位小幫手 digitalyoming.com ( 未經許可，禁止轉載 ) 
原網址｜https://digitalyoming.com/how-to-create-a-dependent-drop-down-list-with-arrayformula-function-in-google-sheets/
返回頂端