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

Last Updated on 2021 年 9 月 11 日 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製作多層下拉選單?

步驟一:資料驗證

首先要製作第一層的下拉選單,打開「資料」>「資料驗證」的介面。

將「第一層下拉選單」的儲存格填入第一欄,然後條件用「範圍內的清單」,範圍則套用第一層選項的儲存格範圍。以下面這張圖來說,就是框選「北部、中部、南部」的儲存格。


步驟二:完成第一層下拉選單

按「儲存」後,就完成了第一層的下拉式選單。


步驟三:輸入Arrayformula函數

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


步驟四:輸入IF函數第一段

Arrayformula括號後面,緊接著填入「IF 函數公式」,這時候就要開始列出IF的條件了。

首先,輸入第一個條件成立的部分,要以「第一層下拉式選單」的儲存格當作公式起點。

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

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

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


步驟五:輸入IF函數第二段

當第一個選項不成立時,接著填入第二個IF公式,邏輯跟上一步差不多。

只是這次成立的條件,是當第一層下拉選單的選項,剛好等於表格第二列的「中部」時,就顯示第二層「中部的儲存格範圍」。公式這時候就是:

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

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


步驟六:直到完成整個公式

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

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

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

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

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


步驟七:Arrayformula陣列展開

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


步驟八:第二層資料驗證

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

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


步驟九:完成第二層下拉式選單

最後就可以完成「第二層下拉式選單了」!


測試效果

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


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


注意

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

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


結語

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

Reference:Extendoffice文件編輯器說明


延伸閱讀

【Google試算表】全部文章總整理

Google試算表【下拉式選單】怎麼做?資料驗證2招用起來!

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

【三層下拉式選單】詳細示範文,什麼時候會用到第3層呢?

Google試算表【下拉式選單】怎麼根據選項換不同顏色?

Google試算表【條件式格式設定】輕鬆凸顯表格重點!


aifian APP推薦活動


最後
§ 很感謝你的閱讀 §
如果你喜歡這篇文章的話

→ 歡迎 留言分享 喔!

→ 歡迎點下面這張圖來【贊助】支持我!讓我繼續為你創作更多內容!

贊助我一杯咖啡


發表迴響