編輯嚴選ARRAYFORMULA 函式大解析（二）：應用篇
編輯嚴選
2022/07/17閱讀時間約 7 分鐘
希望上次的 ARRAYFORMULA 函式大解析（一）：基本原理有幫到大家一點忙！這次會示範三個 ARRAYFORMULA 常見的應用方法，你也可以摸索看看其他的應用方式，找到更多 ARRAYFORMULA 的可能性。如果你還不知道什麼是 ARRAYFORMULA，歡迎先到上一篇了解一下，再看這篇會更有概念唷！
那二話不說，快到這個試算表、複製一份到你的雲端硬碟，我們一起來練習吧。
結合欄位資料
先來看看第一份工作表（練習一：結合欄位）。
我們來試著將第一個欄位（紅豆、綠豆、草莓 ...）和第二個欄位（冰棒、冰淇淋、果汁 ...）結合，希望可以在 C 欄得到「紅豆冰棒」、「綠豆冰棒」、「草莓冰棒」的效果。
那麼，想讓文字跟文字串連，我們可以用 & 這個符號來達成。像是：
="A" & "B"--> AB="甲" & "乙"--> 甲乙="紅豆" & "冰棒"--> 紅豆冰棒
如果我們想要用 ARRAYFORMULA 來一次把這些串連做完的話的話，只要用 & 結合兩個相同大小的範圍、再用 ARRAYFORMULA 包起來就可以了！我們可以在 C3 寫上：
=ARRAYFORMULA(A3:A11 & B3:B11)
來看看效果：
如果你想要在欄位和欄位的中間，加個文字或空白串連（像是「2022/07/01～2022/08/31」、「雞腿＋叉燒」、「吉岡 里帆」等等），只要在 & 之後放上文字、再用第二個 & 結合就可以了，舉例來說：
=ARRAYFORMULA(第一個欄位範圍 & "～" & 第二個欄位範圍)=ARRAYFORMULA(第一個欄位範圍 & "＋" & 第二個欄位範圍)=ARRAYFORMULA(第一個欄位範圍 & " " & 第二個欄位範圍)
那我們往下滑，看到這個例子：
這次來試試看把第一欄跟第二欄用空白的「 」串在一起，一樣希望在 C 欄可以得到「吉岡 里帆」、「有村 架純」、「綾瀨 遙」等等的結果。
我們在 C16 寫個：
=ArrayFormula((A16:A23 & " " & B16:B23))
結果就會像這樣：
結合完成！就這麼簡單 ∠( ᐛ 」∠)_
你也可以透過 ARRAYFORMULA，調換欄位順序再結合、或是增加更多字串連結，也是很方便的。
用 IF 二元分類
另外一個常見的 ARRAYFORMULA 應用，就是和 IF 結合，讓 ARRAYFORMULA 一次幫你的資料做好分類（如果忘了 IF 怎麼用的話，快回到這邊複習一下）。
我們換到下一張工作表（練習二：IF 分類）：
這邊有學生名字、小考成績，還有一個空白的欄，判斷學生是不是需要重考。我們這邊假設低於 60 分就要重考了。如果是普通的 IF，我們會在 C2 寫：
=IF(B2 < 60, "是", "否")
那麼，既然要用 ARRAYFORMULA 了，就要給個範圍讓它判斷。我們要判斷的分數在 B2 到 B 欄的最後一行，也就是 B2:B。範圍定義好了，那我們就可以在 C2 寫：
=ARRAYFORMULA(IF(B2:B < 60, "是", "否"))
來看看結果：
也是一次就判斷好了，成功 (๑•̀ω•́)ノ
用 VLOOKUP 做資料分類跟查表
我們再到最後一個工作表看看（練習三：VLOOKUP）。
先看右手邊，有人名、他們居住的城市、年齡跟電話的一張表。我們的目標就是利用左手邊的人名， VLOOKUP 查詢取得右邊相對應的資料，並且用一條 ARRAYFORMULA 一次完成（如果不小心忘了 VLOOKUP 是什麼，歡迎來複習）。聽起來很難，其實沒事的！
我們先來看看普通的 VLOOKUP 是怎麼運作的：
=VLOOKUP(關鍵字, 範圍, 索引, [已排序])
舉例來說，先來查詢第一位在 A3「蔡振萱」的居住城市。我們在 B3 下：
=VLOOKUP(A3, F3:I, 2, FALSE)
就會得到：
嗯，沒什麼問題！但如果要用 ARRAYFORMULA 一次查一個範圍的關鍵字（也就是這個例子的整個 A 欄）、還有多種索引（也就是 B、C、D 欄），我們要稍稍改造一下原本的 VLOOKUP 函式：
=ARRAYFORMULA(VLOOKUP(關鍵字範圍,查表範圍,{索引, 索引, 索引...},排序))
看起來好像有點複雜，放心！來看個圖解：
- 關鍵字範圍（左邊的藍綠色框框）：ARRAYFORMULA + VLOOKUP 會用這個範圍，查詢右手邊紅色框框的關鍵字在哪。以這個例子來說，我們的關鍵字範圍就是 A3:A20。
- 查表範圍：跟普通的 VLOOKUP 一樣，定義好要查詢的範圍是哪裡即可。我們這邊的例子是 F3:I，F3 到 I 欄的最後一行資料。
- 索引（兩側的綠方塊 #2 / #3 / #4）：當 ARRAYFORMULA + VLOOKUP 知道關鍵字在哪了，我們就可以用索引號碼把資料查出來。這次要拿三欄的資料，也就是索引 2、3、4 的地方。這時我們要用 { } 這組大括號，把 2、3、4 這三個索引數字告訴 ARRAYFORMULA + VLOOKUP，要把這三欄資料帶過來。以這個例子來說，我們的索引就是 {2, 3, 4}。
- 排序：也跟普通的 VLOOKUP 一樣，通常設定 FALSE（非排序）。
那我們把剛剛的思路統整一下，就會得到：
=ARRAYFORMULA(VLOOKUP(A3:A20,F3:I,{2, 3, 4},FALSE))
我們把這個辛辛苦苦做好的函式放在 B3，就會得到：
嘿嘿嘿，做！好！囉 (っ´ω`c)
謝謝你，ARRAYFORMULA 超人！🦸♂️
如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍，即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作！
想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！
留言0
查看全部
