更新於 2024/04/06發佈於 2022/07/17閱讀時間約 7 分鐘

ARRAYFORMULA 函式大解析（二）：應用篇

希望上次的 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，我們下個教學見！

分享至

成為作者繼續創作的動力吧！

討論區

追蹤

ARRAYFORMULA 函式大解析（二）：應用篇

結合欄位資料

用 IF 二元分類

用 VLOOKUP 做資料分類跟查表

喜特先生官方沙龍的其他內容

你可能也想看

發表回應

ARRAYFORMULA 函式大解析（二）：應用篇

結合欄位資料

用 IF 二元分類

用 VLOOKUP 做資料分類跟查表

喜特先生官方沙龍 的其他內容

你可能也想看

發表回應

喜特先生官方沙龍的其他內容