討論區
喜特先生 Mr. Sheet
更新於 2024/04/06閱讀時間約 6 分鐘

ARRAYFORMULA 函式大解析（一）：基本原理

想像一下，如果你有這樣的資料，要在 C 欄計算各行的小計（單價乘數量）：
你或許會在 C2 的儲存格寫： 
=A2 * B2
然後就把這行算式往下填滿、複製貼上，OK！算完了。這樣方法的確可行，可是如果資料有幾千、幾萬筆，每次都要這樣向下填滿，不但很費時，也會有運算效率不良的問題（可以參照五個「太多」，讓試算表變龜速的原因）。
那怎麼辦？我們今天要介紹的 ARRAYFORMULA 就可以解決這個問題！
學會 ARRAYFORMULA 可以讓你輕鬆處理大量資料，可以簡化算式或函式套用在範圍的步驟，讓試算表更穩定、運作更順暢，也可以減少錯誤的發生。這是我繼 QUERY 後第二個大推的函式，讓你從 Google 試算表的新手搖身一變，成更厲害的專家！

ARRAYFORMULA 是什麼

簡單來說，ARRAYFORMULA 是個「可以讓你只用一個算式，算出整欄或整列的值」的函式。回到上面的例子：
我們想取得小計，要在 C2 上面寫： 
=A2 * B2
這樣會拿到 500 乘 6 的結果。但如果我們想要連其他行的小計也一次算好，就可以用 ARRAYFORMULA： 
=ARRAYFORMULA(A2:A * B2:B)
看看結果：
欸嘿，直接算完啦！(ﾉω)ﾉ 你會看到 A2 以下的計算就自動算好了，沒問題！

為什麼推薦用 ARRAYFORMULA 呢？
  1. 可以一次大量運算：只要把想算的式子定義好，放到一個儲存格裡、用 ARRAYFORMULA 包起來，它就會自動套用到整個欄或整個列。
  2. 隨著資料更新自動計算：如果你的儲存格有任何數字變動，或是欄、行有增加減少，ARRAYFORMULA 也會跟著更新。
  3. 讓試算表負擔變輕：用了 ARRAYFORMULA 後，因為算式減少，整個試算表都變輕盈了。
  4. 讓你的工作更輕鬆：有了 ARRAYFORMULA，我們就不需要手動輸入、向下填滿算式了，這樣也可以減少人為錯誤的發生。
  5. 跟不同函式結合的可能性：ARRAYFORMULA 也可以跟特定函式結合，常見的組合技有 IF、SUMIF、COUNTIF、VLOOKUP 等等都可以！日後會再詳細介紹。

語法 

=ARRAYFORMULA(陣列算式)
嗯，陣列算式是什麼？這邊你可以寫：
  • 範圍，像是 A1:B10、C:C20、5:5
  • 儲存格範圍大小相同的算式，像是 A2:A10 + B2:B10、C2:C * D2:D
  • 傳回多個儲存格結果的函式，像是 QUERY、FILTER、UNIQUE、SORT
那第二點「儲存格範圍大小相同」的算式又是什麼？拿剛剛的表格看一下：
要讓 ARRAYFORMULA 運作的話，必須要給它一樣的儲存格範圍。拿剛剛表格裡的寫法： 
=ARRAYFORMULA(A2:A * B2:B)
這邊的範圍就是整個 A2 到所有 A2 以下的範圍（A2:A）、乘上整個 B2 到所有 B2 以下的範圍（B2:B），儲存格範圍的大小都一樣。
換個角度看，A2:A10 跟 B2:B10 的儲存格範圍的大小也相同，都是 9 格。如果範圍大小不一致，就會有機會遇到各種錯誤：
我在 C2 寫了 "=ARRAYFORMULA(A2:A * B2:B3)"，而 C4 以後的小計沒辦法被算出來。
另外，如果你覺得 ARRAYFORMULA 這整串很難打，也可以在算式準備好後，按快捷鍵 Ctrl/Command + Shift + Enter，Google 試算表也會自動幫你把剛剛的算式包起來。

範例一

歡迎到這個試算表，複製一份到你的雲端硬碟，一起練習吧！打開來後，應該會看起來像這樣：
（ARRAYFORMULA 練習一：相乘）
我們一樣來試試看算出各個品項的小計吧！
先來看看範圍怎麼定義。這邊第 2 行到第 11 行有不同的飲料品項，要乘的範圍有 B 欄跟 Ｃ欄，兩邊都是 10 格，儲存格範圍大小一致，沒問題。算式的話就是 B 欄乘上 C 欄，也就是： 
=B2:B * C2:C
可是如果在這時候按下 Enter，只會得到第一行的相乘結果，但 D3 後的儲存格卻得不出結果，因為 Google 試算表不知道你要一次計算兩欄，這時候就要用 ARRAYFORMULA 來處理，包住這串算式。我們在 D2 寫下： 
=ARRAYFORMULA(B2:B * C2:C)
結果如下：
你會發現連 D2 下面的其他儲存格也都自動完成了！之後就算要修改函式，只要改 D2 就可以了，讓維護變得很簡單。

範例二

我們切換到另一個練習用的工作表「ARRAYFORMULA 練習二：相加」。
這邊看得到 A 欄有不同店家，B 欄到 E 欄則是春、夏、秋、冬的營業額。我們這次來試試看在 B 欄到 E 欄相加吧！ 
=B2:B + C2:C + D2:D + E2:E
一樣，在 F2 用 ARRAYFORMULA 把它包起來。 
=ARRAYFORMULA(B2:B + C2:C + D2:D + E2:E)
搞定！很簡單吧 (*´∀｀*)人(*´∀｀*)
之後我會示範 ARRAYFORMULA 跟不同算式、函式的搭配，介紹給大家更多 ARRAYFORMULA 的不同可能性。就跟 QUERY 一樣， ARRAYFORMULA 是個很值得一學的函式，可以讓你面對大型資料更輕鬆，讓你更快把任務搞定！ 
如果你喜歡這次的文章，歡迎你透過這些方法支持我：

・按下愛心、按下儲存

・留言告訴我你的想法

・加入喜特先生的官方沙龍，即時看到我發布的教學

・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案

・追蹤喜特先生的 Facebook

・按這邊小額贊助我的創作！
想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！
分享至
成為作者繼續創作的動力吧！
討論區
追蹤
討論區
追蹤
從 Google News 追蹤更多 vocus 的最新精選內容從 Google News 追蹤更多 vocus 的最新精選內容

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

你可能也想看

發表回應

成為會員 後即可發表留言
© 2024 vocus All rights reserved.