方格精選

ARRAYFORMULA 函式大解析(一):基本原理

更新於 發佈於 閱讀時間約 6 分鐘

想像一下,如果你有這樣的資料,要在 C 欄計算各行的小計(單價乘數量):

raw-image

你或許會在 C2 的儲存格寫:

=A2 * B2

然後就把這行算式往下填滿、複製貼上,OK!算完了。這樣方法的確可行,可是如果資料有幾千、幾萬筆,每次都要這樣向下填滿,不但很費時,也會有運算效率不良的問題(可以參照五個「太多」,讓試算表變龜速的原因)。

那怎麼辦?我們今天要介紹的 ARRAYFORMULA 就可以解決這個問題!

學會 ARRAYFORMULA 可以讓你輕鬆處理大量資料,可以簡化算式或函式套用在範圍的步驟,讓試算表更穩定、運作更順暢,也可以減少錯誤的發生。這是我繼 QUERY 後第二個大推的函式,讓你從 Google 試算表的新手搖身一變,成更厲害的專家!


ARRAYFORMULA 是什麼

簡單來說,ARRAYFORMULA 是個「可以讓你只用一個算式,算出整欄或整列的值」的函式。回到上面的例子:

raw-image

我們想取得小計,要在 C2 上面寫:

=A2 * B2

這樣會拿到 500 乘 6 的結果。但如果我們想要連其他行的小計也一次算好,就可以用 ARRAYFORMULA:

=ARRAYFORMULA(A2:A * B2:B)

看看結果:

raw-image

欸嘿,直接算完啦!(ノ>ω<)ノ 你會看到 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

那第二點「儲存格範圍大小相同」的算式又是什麼?拿剛剛的表格看一下:

raw-image

要讓 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 以後的小計沒辦法被算出來。

我在 C2 寫了 "=ARRAYFORMULA(A2:A * B2:B3)",而 C4 以後的小計沒辦法被算出來。

另外,如果你覺得 ARRAYFORMULA 這整串很難打,也可以在算式準備好後,按快捷鍵 Ctrl/Command + Shift + Enter,Google 試算表也會自動幫你把剛剛的算式包起來。


範例一

歡迎到這個試算表,複製一份到你的雲端硬碟,一起練習吧!打開來後,應該會看起來像這樣:

(ARRAYFORMULA 練習一:相乘)

(ARRAYFORMULA 練習一:相乘)

我們一樣來試試看算出各個品項的小計吧!

先來看看範圍怎麼定義。這邊第 2 行到第 11 行有不同的飲料品項,要乘的範圍有 B 欄跟 C欄,兩邊都是 10 格,儲存格範圍大小一致,沒問題。算式的話就是 B 欄乘上 C 欄,也就是:

=B2:B * C2:C

可是如果在這時候按下 Enter,只會得到第一行的相乘結果,但 D3 後的儲存格卻得不出結果,因為 Google 試算表不知道你要一次計算兩欄,這時候就要用 ARRAYFORMULA 來處理,包住這串算式。我們在 D2 寫下:

=ARRAYFORMULA(B2:B * C2:C)

結果如下:

raw-image

你會發現連 D2 下面的其他儲存格也都自動完成了!之後就算要修改函式,只要改 D2 就可以了,讓維護變得很簡單。


範例二

我們切換到另一個練習用的工作表「ARRAYFORMULA 練習二:相加」。

raw-image

這邊看得到 A 欄有不同店家,B 欄到 E 欄則是春、夏、秋、冬的營業額。我們這次來試試看在 B 欄到 E 欄相加吧!

=B2:B + C2:C + D2:D + E2:E

一樣,在 F2 用 ARRAYFORMULA 把它包起來。

=ARRAYFORMULA(B2:B + C2:C + D2:D + E2:E)
raw-image

搞定!很簡單吧 (*´∀`*)人(*´∀`*)


之後我會示範 ARRAYFORMULA 跟不同算式、函式的搭配,介紹給大家更多 ARRAYFORMULA 的不同可能性。就跟 QUERY 一樣, ARRAYFORMULA 是個很值得一學的函式,可以讓你面對大型資料更輕鬆,讓你更快把任務搞定!

如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!

想要看更多文章,歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔!

我是喜特先生,Mr. Sheet,我們下個教學見!



留言
avatar-img
留言分享你的想法!
enga-avatar-img
2025/02/14
請問一下,怎麼在ARRAYFORMULA裡,使用COUNTIF
喜特先生 Mr. Sheet -avatar-img
發文者
2025/02/14
enga 哈囉,感謝你來看我的教學! 或許可以考慮用 MAP 函式搭配 LAMBDA 函式來做陣列的 COUNTIFS,應該就可以達到一次完成的效果。 可以參考:https://vocus.cc/article/661377f2fd897800010235ec 如果有任何問題,歡迎繼續反映!
avatar-img
喜特先生官方沙龍
18.1K會員
152內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
2025/04/20
請你試著在 Google 試算表的儲存格上打這個,按下 Enter: =WHATTHEFOXSAY() 會有神奇的事情發生喔 ✨
Thumbnail
2025/04/20
請你試著在 Google 試算表的儲存格上打這個,按下 Enter: =WHATTHEFOXSAY() 會有神奇的事情發生喔 ✨
Thumbnail
2024/06/02
上次介紹了 REPLACE 函式,可以用來取代儲存格內的特定文字。其實我們還有一個相似的函式叫 SUBSTITUTE,也有取代的功能,不過當然有一點不一樣的地方!今天會分享語法的範例,還有跟 REPLACE 的差異在哪。一起來看看!
Thumbnail
2024/06/02
上次介紹了 REPLACE 函式,可以用來取代儲存格內的特定文字。其實我們還有一個相似的函式叫 SUBSTITUTE,也有取代的功能,不過當然有一點不一樣的地方!今天會分享語法的範例,還有跟 REPLACE 的差異在哪。一起來看看!
Thumbnail
2024/05/25
這是文字處理基礎函式的第四篇文章,今天要來介紹 REPLACE 函式! REPLACE 可以取代掉儲存格內的文字,今天會分享一下它語法怎麼寫、也有兩個實際應用的案例。一起來看看! REPLACE 語法 REPLACE 的語法長了一點點,有四個參數要設定: =REPLACE(要取代
Thumbnail
2024/05/25
這是文字處理基礎函式的第四篇文章,今天要來介紹 REPLACE 函式! REPLACE 可以取代掉儲存格內的文字,今天會分享一下它語法怎麼寫、也有兩個實際應用的案例。一起來看看! REPLACE 語法 REPLACE 的語法長了一點點,有四個參數要設定: =REPLACE(要取代
Thumbnail
看更多
你可能也想看
Thumbnail
「欸!這是在哪裡買的?求連結 🥺」 誰叫你太有品味,一發就讓大家跟著剁手手? 讓你回購再回購的生活好物,是時候該介紹出場了吧! 「開箱你的美好生活」現正召喚各路好物的開箱使者 🤩
Thumbnail
「欸!這是在哪裡買的?求連結 🥺」 誰叫你太有品味,一發就讓大家跟著剁手手? 讓你回購再回購的生活好物,是時候該介紹出場了吧! 「開箱你的美好生活」現正召喚各路好物的開箱使者 🤩
Thumbnail
歡迎來到 AL 的 Googlesheet 學習筆記系列文章。在這個系列中,我們將一步步介紹各種函數,並將它們應用於日常生活中,加速工作、提高效率。 計算商品與單價、小計與總計是多數人日常辦公時常常面臨的課題,今天要介紹的函數 SUMPRODUCT
Thumbnail
歡迎來到 AL 的 Googlesheet 學習筆記系列文章。在這個系列中,我們將一步步介紹各種函數,並將它們應用於日常生活中,加速工作、提高效率。 計算商品與單價、小計與總計是多數人日常辦公時常常面臨的課題,今天要介紹的函數 SUMPRODUCT
Thumbnail
高效生活,幫助你找回更多自己的時間 哈囉,這裡是 AL 的 Googlesheet 學習筆記 本系列文章,會帶你認識各種函數,學習並應用於日常,加速生活與工作、提升效率 今天要介紹的函數是 ARRAYFORMULA
Thumbnail
高效生活,幫助你找回更多自己的時間 哈囉,這裡是 AL 的 Googlesheet 學習筆記 本系列文章,會帶你認識各種函數,學習並應用於日常,加速生活與工作、提升效率 今天要介紹的函數是 ARRAYFORMULA
Thumbnail
你是不是常常要面對堆積如山的excel表格?其實有一些簡單的訣竅,可以讓你大大減輕製作數十頁資料的痛苦......
Thumbnail
你是不是常常要面對堆積如山的excel表格?其實有一些簡單的訣竅,可以讓你大大減輕製作數十頁資料的痛苦......
Thumbnail
Google 試算表還有一個花括號 { } 的參照方法,可以參照範圍。學會的話,對處理大量資料有很多好處!我們來看看怎麼用 { } 來處理吃資料吧。
Thumbnail
Google 試算表還有一個花括號 { } 的參照方法,可以參照範圍。學會的話,對處理大量資料有很多好處!我們來看看怎麼用 { } 來處理吃資料吧。
Thumbnail
上次說完了 ARRAYFORMULA 的基本原理,這次來看看怎麼應用它們!一起來看看!
Thumbnail
上次說完了 ARRAYFORMULA 的基本原理,這次來看看怎麼應用它們!一起來看看!
Thumbnail
或許 ARRAYFORMULA 是個聞風喪膽的函式,但是你懂了它、它就會幫你!一起來看看 ARRAYFORMULA 是什麼吧!
Thumbnail
或許 ARRAYFORMULA 是個聞風喪膽的函式,但是你懂了它、它就會幫你!一起來看看 ARRAYFORMULA 是什麼吧!
Thumbnail
遇懶預覽專用第一段:"=subtotal(3,$B$2:B2)",如果還是不確定怎麼用的話請繼續往下看;本文適用於Apple Numbers、Google Spreadsheet以及Microsoft Office Excel,不負責任主觀判定實用度為87%,使用頻率也是87%,難易度則是8.7%。
Thumbnail
遇懶預覽專用第一段:"=subtotal(3,$B$2:B2)",如果還是不確定怎麼用的話請繼續往下看;本文適用於Apple Numbers、Google Spreadsheet以及Microsoft Office Excel,不負責任主觀判定實用度為87%,使用頻率也是87%,難易度則是8.7%。
追蹤感興趣的內容從 Google News 追蹤更多 vocus 的最新精選內容追蹤 Google News