我一直到開始著手到很大量的資料後，才開始接觸到 arrayformula，他在 excel 裡面對應到的就是陣列公式，arrayformula 可以把一整行需要展開的公式簡化成一個儲存格，好處就是省事又速度快

1. 如果有新的資料加入，不用將公式向下展開
2. 原先需要用到幾萬個儲存格的計算空間，現在只需要一格

而 arrayformula 通常會包在計算公式的最外面，計算公式包含陣列或數學運算式 (通常包含多個儲存格範圍)。數學計算函數「往下帶入、向下拉儲存格」的這個動作可以完全以arrayformula 取代。

Arrayformula 使用情境

看得出來，只有紅色的地方有寫公式嗎！順帶一提，所有的公式都會在 Google Sheet 內，這裡會幫你自動建立副本到你的 Google Drive 內，可以馬上邊看邊練習喔～

使用情境 1-想複製並且連動別張工作表

# 連動一欄或是多欄

=ARRAYFORMULA('RAW Data'!A:C) 

使用 arrayformula 的好處是 自動化，不需要再用複製貼上，也省得如果想要連動的資料改變了，還要再去手動複製貼上一次的麻煩。

# 連動一欄或是多欄時篩選某些條件，例如只要男性的資料

=ARRAYFORMULA('RAW Data'!A1:C1) 

=filter('RAW Data'!A:C,'RAW Data'!D:D="M") 

1. 先使用 arrayformula 貼上表頭
2. filter() 也是陣列公式的一種型態，他可以透過一格公式回傳一整個數列，第一個參數放你要回傳的欄位 A:C，第二個參數放你要篩選的條件，例如：你想要只回傳男性 “M” 的資料
   • 延伸閱讀：Google Sheet Filter() 使用方法

Q: 如果我是一個賣場管理員、電商老闆，我的報表裡面可能會有很多個子賣場、或是不同種類的商品，那如果我想要在一個 Google Sheet 裡面管理我的報表，我該怎麼做？

1. 第一步：那你可以將整個賣場的資料匯到一個工作表之後
2. 第二步：建立一個報表的範本
3. 第三步：使用連動資料的函數+範本，快速建立每個子賣場的獨立工作表

使用情境 2-相乘後相加

# arrayformula

=ARRAYFORMULA(sum(B11:B*C11:C)) 

如果你想要快速算出，每個品項的單價 * 銷售量，得到當天的營業額，那可以將兩個數列分別放在 sum 裡面，最後再用 arrayformula 包住。

# sumproduct

=SUMPRODUCT(G11:G,H11:H) 

sumproduct 雖然比較直覺，但是相似的功能都能用 arrayformula + 變化合成，例如說你想要：相除再相加。所以還是很推薦大家用 arrayformula 來養成習慣。下面舉例相除再相加：

=ARRAYFORMULA(sum(J10:J14/K10:K14)) 

使用情境 3-if

# arrayformula+if

=ARRAYFORMULA(if(A9:A>0,1,0)) 

在 if 裡面我們都是用 =if(A9>0,1,0) 這樣的公式來判斷，當我們要用 arrayformula+if 組合時，只要把 A9 單一儲存格，改成數列 A9:A 就可以了。

=ARRAYFORMULA(if(A9:A="","",if(A9:A>0,1,0))) 

為了想讓結果呈現好看一點，我們不想讓沒有數字的也被 if 判斷，那我們可以在 if 前面先判斷：如果數列為空白，那就直接顯示空白。

使用情境 4-sumif/sumifs

# arrayformula+sumif 單一條件

=ARRAYFORMULA(sumif('RAW Data'!C:C,A9:A12,'RAW Data'!M:M)) 

平常我們使用 sumif 時，=sumif(C:C,A1,M:M)，中間那一格只會放一個儲存格，在 arrayformula 中，只要把 A1 替換成數列 A9:A12 就可以了。

# arrayformula+sumif 多條件

=ARRAYFORMULA(sumif('RAW Data'!C:C&'RAW Data'!K:K,D9:D12&E9:E12,'RAW Data'!M:M)) 

不知道為什麼多條件沒有辦法用 sumifs+arrayformula 來實現，我查了一些資料後發現，可以用 sumif 來組成多條件，只要把條件列用 & 連在一起，像是範例中的 C:C&K:K，查詢值也用 & 相連，D9:D12 & E9:E12，結果出來就是正確的。

# 也可以使用 query

=QUERY('RAW Data'!A:P,"select C,K,sum(M) where K=date'2020-01-01' group by C,K") 

query 也是進階函數裡面我覺得超好用的，他跟 SQL 語法處理很類似，如果學好 query 那同時你一定會有 SQL 基礎，可以 延伸閱讀：資料分析 101 – Google Sheet Query 教學，來學更多跟 query 有關的用法！

使用情境 5-vlookup

# vlookup 單一條件查找，回傳一欄或是多欄

=ARRAYFORMULA(VLOOKUP(A9:A12,'RAW Data'!A:E,5,0)) 

平時的 vlookup：=vlookup(A1,A:E,5,0)，第一個查找值只會放一個，arrayformula 中可以放一整欄要查找的數列 A9:A12。

=ARRAYFORMULA(VLOOKUP(A9:A12,'RAW Data'!A:E,{5,4},0)) 

如果想回傳多個結果，可以用 { } 把要回傳的欄樹組在一起就可以了。

# vlookup 多條件+多欄返回結果

=ARRAYFORMULA(VLOOKUP(H9:H11&I9:I11,{'RAW Data'!A:A&'RAW Data'!K:K,'RAW Data'!M:M,'RAW Data'!I:I},{2,3},0)) 

剛剛把 vlookup 單一條件+多欄回傳的結果整理完之後，大家應該會對多條件+多欄返回結果很有興趣，因為比較複雜，所有我把它拆解一下。假設我想知道某個外送員在不同月份的訂單、同時又想回傳他的 email，我該如何做？我們來一步一步拆解，裡面的 vlookup 怎麽寫？

1. 第一步，先把兩個條件組起來，外送員姓名 & 月份：H9:H11&I9:I11
2. 第二步，去 raw data 裡面把查找欄位用 { } 組起來：{‘RAW Data’!A:A&’RAW Data’!K:K,’RAW Data’!M:M,’RAW Data’!I:I}
   • 第一欄我要姓名 & 月份，這樣我才可以跟第一步裡面的姓名 & 月份拼起來查找，就是 A&K
   • 第二欄我要訂單，M
   • 第三欄我要 email，I
3. 第三步，我要查找訂單跟 email，因此呈第二步，我要的應該是 2,3 欄，因此第三個參數：{2,3}

使用情境 6-簡單加減乘除，不用再下拉公式

# A欄+B欄

=ARRAYFORMULA(A8:A17+B8:B17) 

使用情境：跟別人共用表單，如果 A, B 欄增加數字，我們 C 欄的公式又要下拉，真麻煩，用 Arrayformula 就不用下拉，一鍵搞定！

# A欄*B欄

=ARRAYFORMULA(I8:I17*J8:J17) 

=ARRAYFORMULA(100*M8:N10) 

Arrayformula 使用後總結

在看 ARRAYFORMULA 的使用情境之前，如果你還是不知道陣列是什麼，可以先看一下 Google Sheet Arrayformula 怎麼用？5 大常用函數解析，我在這裡解釋陣列的概念是什麼、與 ARRAYFORMULA 最常搭配的常用函數

如何練習 Google Sheet Arrayformula 函數？

1. 複製一份我的 Google Sheet 教學文件
2. 在我整理好的幾個工作表中，練習這 5 個使用範例

更多學習資源推薦

• Google 官方教學：Getting Started with Google Sheets
• Lisa 的超白話實用教學：Lisa 的 Google Sheet 公式教學
• Google Data Analytics Professional Certificate：Google 認證的資料分析師證照課程有幫助嗎？ 誰最適合上？可以參考我的學習心得，證照班適合想要全方位學習數據分析工具跟技巧的人！

如何使用這個網站？我的數據分析文章導覽！裡面也會分享更多實用 Google Sheet 教學：本站數據分析文章導覽 及 更多 Google Sheet 實用文章教學