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

Last Updated on 2021 年 12 月 5 日 by 小幫手

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 函數了。


aifian APP推薦活動
[ 這 是 APP 推 廣 活 動 廣 告 ]


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

Step 1:驗證資料

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

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


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

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


Step 3:輸入Arrayformula函數

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


Step 4:輸入 IF 函數第一段

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

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

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

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


Step 5:輸入 IF 函數第二段

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

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

一樣還有條件不成立的情況,也是繼續填入下一個 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 的陣列也會跟著變動。


Step 8:第二層資料驗證

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

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


完成第二層下拉式選單

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


測試效果

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


aifian APP推薦活動
[ 這 是 APP 推 廣 活 動 廣 告 ]


注意事項

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

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


結語

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

Reference:Extendoffice文件編輯器說明


延伸閱讀


aifian APP推薦活動


最後
§ 很感謝你的閱讀 §
如果你喜歡這篇文章的話
歡迎使用下面分享按鈕分享


留言區