什麼是陣列函數？
一般常見的函數，像是 if()、sumifs()，這些都不是陣列函數，每次輸入函數之後，運算出來的值都只會出現在單一儲存格。
陣列函數的神奇之處就是，我們回傳的是陣列，陣列是一串數值，所以會出現在多個儲存格裡，所以你可能會發現，寫完陣列公式之後，出現了一整個報表，而不是一般常見函數的單一儲存格
先定義一下行與列
- 行：往右的是行
- 列：往下的是列
| row (第一行) | ow (第一行) | ow (第一行) |
|---|---|---|
| row (第二行) | row (第二行) | row (第二行) |
| column (第一列) | column (第二列) |
|---|---|
| column (第一列) | column (第二列) |
| column (第一列) | column (第二列) |
如何在 Google Sheet 中寫出陣列？
- 剛剛有提到，陣列是一串數值，因此如果要呼叫陣列出來，要用 { } 大括號，而不是 ( ) 平常使用的小括號
- 範例一：寫出單行陣列，往右展開
={1, 2, 3, 4}
- 範例二：寫出 2×2 的表格，也就是兩行兩列：分號在陣列內是跳行的意思，因此會有兩行兩列
={1, 2; 3, 4}
- 範例三：陣列 + 引用多儲存格
- 如果引用的儲存格，中間用逗點隔開，就會跳下一列
- 如果引用的儲存格，中間用分號隔開，就會接在同一列下面
={G19:G21,H19:H21,I19:I21}
={G19:G21;H19:H21;I19:I21}
- 範例：陣列 + 單儲存格
- 以 sum() 為例，一個 sum() 會回傳一個加總後的值，因此兩個 sum() 會回傳兩個加總後的值，以此類推。如果用逗號隔開，就會在同一行，如果用分號隔開，就會在同一列
- 這裡用一個簡單的表格來完美呈現逗號跟分號的區別
={SUM(G32:G34),sum(H32:H34),sum(I32:I34)}
={sum(G32:I32);sum(G33:I33);sum(G34:I34)}
陣列的優缺點分析
優點
- 以數據分析師的角度，用陣列可以減少我們寫公式的次數，節省時間，例如：平常加總需要寫 100 次，現在用陣列之後，只要寫 1 次
- 減少寫公式的次數，降低報表的錯誤率：每一個手動就會增加報表的錯誤率，因此減少寫公式的次數，就是增加正確率，也讓維護報表的成本變低
- 如果有新的數據加入，陣列公式也可以自動跑出結果
- 如果想要對兩張 google sheet 或是不同工作表處理，可以用陣列及分號的形式：引用多工作表、多數據
缺點
- 比較難理解，需要花一點時間理解陣列的運作：上手之後，可以省下很多時間「逐一」檢查公式的錯誤
- 並不是所有熟悉的基本函數都可以用陣列：不過，幾乎都可以找到替代方法
Arrayformula 如何使用？搭配常用的函數一起使用！
- 如何讓非陣列公式發揮作用？Arrayformula 的公式除了可以直接打 Arrayformula() 呼叫外，也可以用 Ctrl+Shift+Enter 這三個鍵呼叫出來
- 只要呼叫出 Arrayformula，非陣列公式 (有些公式不支援) 就可以開始以陣列的形式處理數值
範例一：IF & ARRAYFORMULA
=ARRAYFORMULA(if('RAW Data'!E:E>18,1,0))
範例二：加減乘除 +-×÷ & ARRAYFORMULA
=ARRAYFORMULA('RAW Data'!M2:M*'RAW Data'!P2:P)
範例三：統計函數 SUM & ARRAYFORMULA
=ARRAYFORMULA(sum('RAW Data'!N2:N*'RAW Data'!P2:P))
範例四：條件函數 SUMIF & ARRAYFORMULA
- 單一條件
=ARRAYFORMULA(sumif('RAW Data'!F:F,A:A,'RAW Data'!N:N))
- 多條件
=ARRAYFORMULA(sumif('RAW Data'!F:F&'RAW Data'!G:G,A:A&B:B,'RAW Data'!N:N))
如何將多餘儲存格改為空白？
- 方法一：加一個 IF 條件，判斷有值才運算，否則回傳空白
=ARRAYFORMULA(if(len(A:A),sumif('RAW Data'!F:F&'RAW Data'!G:G,A:A&B:B,'RAW Data'!N:N),""))
- (推薦) 方法二：或是一開始就在 SUMIF 中加入 FILTER，只運算條件不等於空白的 (<>””)
- 如果你還不清楚 FILTER 公式是什麼？可以看一下這篇：Google Sheet FILTER 用法，FILTER 也是在製作報表的時候，很實用的公式之一
=ARRAYFORMULA(sumif('RAW Data'!F:F&'RAW Data'!G:G,filter(A:A&B:B,A:A<>""),'RAW Data'!N:N))
SUMIFS 不支援 ARRAYFORMULA，怎麼辦？
- 因為 SUMIFS 的公式邏輯是從一整個數列裡面，找到符合條件的選項之後加總，所以公式當初設計的邏輯，本來就只會回傳一個值為結果
- SUMIFS 不支援 ARRAYFORMULA，可以用 SUMIF 多條件寫法代替！
=ARRAYFORMULA(sumif('RAW Data'!F:F&'RAW Data'!G:G,A:A&B:B,'RAW Data'!N:N))
- SUMIFS 不支援 ARRAYFORMULA，也可以以 Query 中的 WHERE 多條件查詢取代，詳情可以看：Google Sheet Query Where 條件教學
範例五：查找函數 VLOOKUP & ARRAYFORMULA
- VLOOKUP 查找單行
=ARRAYFORMULA(vlookup($A2,'RAW Data'!$A:$D,2,0))
=ARRAYFORMULA(vlookup($A2,'RAW Data'!$A:$D,3,0))
=ARRAYFORMULA(vlookup($A2,'RAW Data'!$A:$D,4,0))
- VLOOKUP 查找多行
=ARRAYFORMULA(vlookup($A2,'RAW Data'!$A:$D,{2,3,4},0))
- VLOOKUP 多條件查找且多行返回
- Google Sheet Arrayformula 5 大超實用使用情境教學：這邊會講很多實際的使用情境，也會解釋如果要：VLOOKUP 多條件+多欄返回結果，公式要怎麼寫
總結
- Array (陣列) 在 Google Sheet 中非常實用，可以減少我們寫的公式數量
- Arrayformula 其實不難，只要將他加上我們已經熟用的基礎公式 (IF、SUMIF、SUM…) 後，就等於學會了陣列在 Excel 或是 Google Sheet 中的作用
如何練習 Google Sheet Arrayformula 函數？
- 複製一份今天的 Google Sheet 教學文件
- 在我整理好的幾個工作表中，練習這 5 個使用範例
更多學習資源推薦
- Google 官方教學：Getting Started with Google Sheets
- Lisa 的超白話實用教學：Lisa 的 Google Sheet 公式教學
- Google Data Analytics Professional Certificate：Google 認證的資料分析師證照課程有幫助嗎？ 誰最適合上？可以參考我的學習心得，證照班適合想要全方位學習數據分析工具跟技巧的人！
如何使用這個網站？我的數據分析文章導覽！裡面也會分享更多實用 Google Sheet 教學：本站數據分析文章導覽 及 更多 Google Sheet 實用文章教學
Pingback: [數據分析#0] 數據分析文章導覽 - Lisa Wu 財富自由之路
Pingback: [數據分析#6] Google Sheet Arrayformula 5大超實用使用情境教學 - Lisa Wu 財富自由之路