免費軟體與線上工具1,417影音、圖片與 PDF9153C 開箱評測895生活、旅遊與遊戲798iPhone 與 Mac549LINE 與社群533Office 教學485Google 服務469Windows 與電腦460Android 手機402網路新知374AI 工具133
Google 服務

Google 試算表 ARRAYFORMULA 函數用法:一次計算整欄陣列資料

ARRAYFORMULA 能讓一般函數支援陣列運算,輸入一次公式即可自動展開整欄或整列的計算結果。適合用於需要大量向下填滿公式的情境,能有效避免漏改公式的錯誤。

Google 試算表 ARRAYFORMULA 函數語法

=ARRAYFORMULA(陣列公式)
參數必填說明
陣列公式是要套用陣列運算的數學運算式或函數公式,範圍需使用陣列形式(例如 A2:A 而非 A2)。

回傳:回傳陣列公式計算後的多個結果,會自動填滿到相鄰的空白儲存格中。

ARRAYFORMULA 函數範例

範例 1:基本數學運算

計算多筆商品的總價

業務員單價數量總價
王小明15010
陳怡君2005
林志明12020

在 D2 輸入公式:

=ARRAYFORMULA(B2:B4*C2:C4)

結果:1500。公式會自動將 B2 乘 C2、B3 乘 C3、B4 乘 C4。此處顯示 D2 的結果 1500,並會自動向下溢出填滿 D3 的 1000 與 D4 的 2400。

範例 2:字串結合

批次產生員工的完整 Email 帳號

員工編號姓名完整帳號
A001王小明
A002陳怡君
A003林志明

在 C2 輸入公式:

=ARRAYFORMULA(A2:A4 & "@company.com")

結果:A001@company.com。利用 & 符號與字串結合,ARRAYFORMULA 會將 A2 到 A4 的內容分別加上信箱網域。C2 顯示第一筆結果,並自動向下展開至 C4。

範例 3:搭配 IF 函數

一次判定多位學生的成績是否及格

學生國文數學狀態
王小明8590
陳怡君5570
林志明4050

在 D2 輸入公式:

=ARRAYFORMULA(IF(B2:B4>=60, "及格", "不及格"))

結果:及格。讓 IF 函數支援陣列判斷,一次檢查 B2 到 B4 的分數是否大於等於 60。D2 顯示及格,並自動向下溢出判定結果,省去手動下拉公式的麻煩。

使用 ARRAYFORMULA 的技巧與注意事項

  • 在編輯公式時,按下快捷鍵 Ctrl + Shift + Enter(Mac 為 Cmd + Shift + Enter),系統會自動在公式外包上 ARRAYFORMULA()。
  • 建議將範圍寫成開放式(如 A2:A),這樣未來新增資料時,公式會自動計算新列,不需重新修改範圍。
  • 若使用開放式範圍,建議搭配 IF 函數過濾空白儲存格(例如 IF(A2:A="",, 運算式)),以免產生過多無效的計算結果。

ARRAYFORMULA 常見錯誤與解決方法

錯誤原因解決方法
#REF!展開結果的範圍內有其他資料阻擋,導致公式無法向下或向右溢出。清除阻擋範圍內的資料,讓儲存格保持空白,公式就會自動展開。
範圍大小不一致導致計算錯誤陣列公式中使用的多個範圍列數或欄數不同(例如 A2:A10 與 B2:B5 相加)。確保公式內所有參照的陣列範圍大小完全一致。
只回傳單一數值搭配了 SUM、JOIN 等本身就會將陣列聚合為單一值的函數,無法透過 ARRAYFORMULA 展開。改用其他支援陣列的運算方式(例如將 SUM 改為加號),或搭配 BYROW 等 LAMBDA 函數處理。

Excel 有 ARRAYFORMULA 嗎?

Excel 中沒有同名函數。在較新版的 Excel(Microsoft 365)內建動態陣列功能,直接輸入陣列公式按 Enter 即可自動溢出展開;舊版則需按 Ctrl+Shift+Enter。

常見問題

為什麼套用 ARRAYFORMULA 後,SUM 函數還是只算出一個總和?

因為 SUM 函數的特性是將所有數值加總為單一結果。若要對每一列的資料分別加總,不能直接用 SUM,需改用加號(A2:A+B2:B)或使用 BYROW 函數搭配 LAMBDA 處理。

公式出現 #REF! 錯誤,提示「陣列結果未展開,因為會覆寫資料」,該如何處理?

這是因為 ARRAYFORMULA 準備填入計算結果的下方或右方儲存格中,已經有輸入其他文字或空白字元。只要將阻擋公式展開的儲存格內容清除,結果就會正常顯示。

可以把 ARRAYFORMULA 放在表頭(第一列)嗎?

可以。通常會搭配陣列常數與 IF 函數,例如 ={"總價"; ARRAYFORMULA(IF(A2:A="",, B2:B*C2:C))},這樣即使排序資料,公式也不會跑掉,非常適合用在共用表單。

相關函數

這篇收錄在「Google 試算表函數」。