ARRAYFORMULA 能讓一般函數支援陣列運算,輸入一次公式即可自動展開整欄或整列的計算結果。適合用於需要大量向下填滿公式的情境,能有效避免漏改公式的錯誤。
Google 試算表 ARRAYFORMULA 函數語法
=ARRAYFORMULA(陣列公式)| 參數 | 必填 | 說明 |
|---|---|---|
| 陣列公式 | 是 | 要套用陣列運算的數學運算式或函數公式,範圍需使用陣列形式(例如 A2:A 而非 A2)。 |
回傳:回傳陣列公式計算後的多個結果,會自動填滿到相鄰的空白儲存格中。
ARRAYFORMULA 函數範例
範例 1:基本數學運算
計算多筆商品的總價
| 業務員 | 單價 | 數量 | 總價 |
|---|---|---|---|
| 王小明 | 150 | 10 | |
| 陳怡君 | 200 | 5 | |
| 林志明 | 120 | 20 |
在 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 函數
一次判定多位學生的成績是否及格
| 學生 | 國文 | 數學 | 狀態 |
|---|---|---|---|
| 王小明 | 85 | 90 | |
| 陳怡君 | 55 | 70 | |
| 林志明 | 40 | 50 |
在 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 試算表函數」。




