【Google Sheet】用ARRAYFORMULA()讓試算表自動化

---

Why ARRAYFORMULA?

有一次同事問我：「如果不想每次在試算表新增欄位，都要下拉之前的vlookup公式，有沒有方法能解決?」，在研究的過程中，讓我發現了好用的ARRAYFORMULA函數！

在正式介紹ARRAYFORMULA之前（這個開場白好像介紹朋友哈哈），想先跟大家分享它的便利之處：

1. 只需要一個儲存格，就能大量運算：如同上面所提，原本需要不停下拉寫好的公式。在套上ARRAYFORMULA之後，只要一個儲存格就可以搞定！
2. 自動更新資料：自動更新資料是我推薦此函數的主要原因。如果試算表的欄位有變動，ARRAYFORMULA函數也會隨著更新，不需要手動維護。

不過最主要還是能幫助我們提升工作上的效率，那麼廢話不多說，趕快跟著我們一起認識ARRAYFORMULA函數吧！

---

語法

首先，先來介紹ARRAYFORMULA的語法。

= ARRAYFORMULA(陣列公式)

什麼是陣列公式呢？這邊我們直接從ARRAYFORMULA()中，所放置的內容來說明，可以大致分為兩類：

• 儲存格範圍相同的運算式：像是B2:B10*C2:C10。如下圖：

• 放入試算表其他函數：像是IF(B2:B10*C2:C10>1000,1,0)。如下圖：

在ARRAYFORMULA函數中，你會發現放入的數字多半為一個範圍，比如B2:B, C2:C10等等。這主要是為了讓它能自動運算下面的欄位。

一旦你在D2欄寫入ARRAYFORMULA函數，接下來的D3到D10欄都不需要計算，公式會自動幫你跑好，是不是很神奇呢?

接下來，我們會進一步帶大家看看ARRAYFORMULA的基礎&進階應用。

在開始介紹前，各位小讀者也可以點開連結，使用檔案-> 建立副本的方式copy一份出來，跟著我們一起做練習喔！

---

基礎應用

以下面這張表為例，若要求出C欄的總和，通常會在儲存格中輸入A2*B2, A3*B3……..A10*B10來得出答案。

如果套入ARRAYFORMULA公式，只需要在C2欄位輸入：

=ARRAYFORMULA(A2:A10*B2:B10)

就可以完成囉！結果如下圖：

不過這邊要特別注意，若你輸入的儲存格範圍不同，像是=ARRAYFORMULA(A2:A9*B2:B10)就會發生#N/A的情況，所以使用時記得確認輸入的數字範圍喔！

如果你將原先的A2:A10*B2:B10，改成A:A*B:B。你會發現，後面欄位出現0，這樣當你在A&B欄的數量&價格新增數字時，C欄也會出現對應的總和，就不需要手動下拉了。

---

---

進階應用

前面提到ARRAYFORMULA可以搭配各種函數使用，接下來介紹，平時較常使用的試算表函數與ARRAYFORMULA搭配的使用場景。

搭配VLOOKUP函數

單條件回傳一欄

以下圖為例，若我們要計算各水果對應的售價，可以使用VLOOKUP函數

= VLOOKUP(E2,B:C,2,0)

若搭配ARRAYFORMULA使用，則為：

=ARRAYFORMULA(VLOOKUP(E2:E7,B:C,2,0))

單條件回傳多欄

現在若我們加一欄數量，同樣可以用上面的方式，分別在G2 & H2欄位使用ARRAYFORMULA與VLOOKUP搭配的函數。

不過，你也可以使用{}將要回傳的欄位放在一起即可，如下：

=ARRAYFORMULA(VLOOKUP(F2:F7,B:D,{2,3},0))

---

搭配IF函數

以下圖為例，若我們要將總和>1000的欄位打上1，其餘打上0，使用原先的IF函數可以這麼列：

= IF(A2*B2>1000,1,0)

然後，再到對應儲存格中一一填入公式。

不過搭配ARRAYFORMULA之後，只要在一個儲存格內列下：

=ARRAYFORMULA(IF(A2:A10*B2:B10>1000,1,0))

就可以得到相同的結果囉！

---

搭配SUMIF函數

同樣的邏輯也可以套用到SUMIF的搭配當中。對於SUMIF函數的應用大家可以參考這篇詳細介紹。

如果將SUMIF搭配ARRAYFORMULA使用，可以這麼列：

=ARRAYFORMULA(SUMIF(B:B,E2:E4,C:C))

**備註：如果要將SUMIFS搭配ARRAYFORMULA會無法使用喔

那麼，以上是本篇文章對於ARRAYFORMULA函數的介紹，如果對於文中任何地方有問題，都歡迎來信或是留言跟我們說，期待下次再見啦！