ARRAYFORMULA 入門：一個公式算完整欄，新增資料自動跟上

今天要來聊聊 ARRAYFORMULA，它可以讓你只寫一個公式，就把整欄算完，之後新增的資料也會自動算進來。

起因很普通：公式拖拉到最後一列，過幾天又多了幾筆資料，我忘了再拖一次，那幾列的金額就空在那邊，月底對帳才發現。

拖拉公式會漏在哪裡

我手上有一張手搖飲的訂單，6 筆，想算每筆的金額（杯數乘以單價）：

傳統做法是在 D2 輸入 =B2*C2，再往下拖到 D7。如果隔天 A8 多了一筆「青茶 17 杯、單價 35」，D8 還是空的，要記得再拖一次才行。

資料只有 6 筆還好，每天都有資料進來的話，這個動作會一直重複。這時候就可以請 ARRAYFORMULA 出場。

ARRAYFORMULA

ARRAYFORMULA 可以讓原本只處理單一儲存格的公式，一次處理整個範圍，結果會從輸入公式的那格往下展開。

語法

=ARRAYFORMULA(陣列公式)

• 陣列公式：可以是一個範圍、一個用範圍做的運算式（範圍大小要一樣），也可以是會傳回多格結果的函式。

範例

我想把 D 欄的金額改成只用一個公式。把 B2*C2 裡的儲存格換成整欄的範圍，外面包上 ARRAYFORMULA，在 D2 輸入：

• B2:B：從 B2 一路到 B 欄最後一列，這叫做開放式範圍，後面會再講。
• C2:C：同上，單價。

按下 Enter，D2 以下會一次長出整欄的結果。來看看結果，這時候 A8 補上了「青茶 17 杯、單價 35」：

第 8 筆自己跟上了，不用拖。但是第 9 列沒有資料，卻出現一個 0，下一節就來處理它。

另外，Google 試算表的官方說明提到，編輯公式的時候按 Ctrl+Shift+Enter，可以自動幫公式包上 ARRAYFORMULA（官方只寫了這組按鍵，沒有另外列出 Mac 的按法）。我個人還是習慣自己打，但忘記包的時候這個快捷鍵很方便。

結果的那一欄要留空

ARRAYFORMULA 的結果會佔用 D2 以下的整欄，如果 D4 已經有東西（例如以前手動輸入的數字），D2 就會顯示 #REF!，訊息是「系統未展開陣列結果，以免覆蓋 D4 中的資料。」。把 D4 清掉，結果就會展開了。

開放式範圍

B2:B 這種只寫起點、不寫終點的範圍，叫做開放式範圍，會一路算到 B 欄最後一列。起點從 B2 開始，是為了跳過標題列。

如果你寫成固定範圍：

D2 到 D7 的結果會是對的，但是第 8 列之後不會算，新增的資料又漏掉了，跟手動拖拉沒兩樣。所以要自動延伸，範圍的終點就別寫。

用 Excel 的人：B2:B 這種開放式範圍是 Google 試算表的寫法。Excel 365／2021／2024 的動態陣列不需要 ARRAYFORMULA，=B2:B7*C2:C7 會直接溢出；要讓新資料自動算進來，Excel 得改用 B2:B1000 這類寫大一點的範圍、整欄 B:B，或把資料轉成表格再用結構化參照。

擋掉空白列

回到剛剛那個多出來的 0：開放式範圍會把下面所有空白列也算進去，空白乘空白得到 0，於是整欄後面都會是 0。

我通常的寫法是在最外層加一個 IF，A 欄是空白就顯示空白，不是才計算：

• A2:A=""：判斷品項欄是不是空白，對整欄一次判斷。
• ""：空白的列就顯示空白。
• B2:B*C2:C：有資料的列才算金額。

這時候 D2 到 D8 是 540、385、320、315、945、275、595，D9 以後都是空白。判斷用的欄位我習慣選「一定會有資料」的那一欄，例如品項或日期。

這個 IF(某欄="", "", 算式) 的樣子，之後你寫 ARRAYFORMULA 幾乎每次都會用到。

標題寫進公式裡

如果希望連標題列也由公式產生，可以用大括號把標題和結果疊起來，分號代表往下接。在 D1 輸入：

D1 會是「金額」，D2 以下接著是各筆金額。這樣整欄只有 D1 一格有公式，D2 以下要保持空白。

哪些函式不會逐列計算

下面的例子先把 A8 那筆青茶刪掉，回到原本的 6 筆資料。

ARRAYFORMULA 不是對所有函式都會一列一列算。AND、OR、MAX、SUM 這類會把一堆值收斂成一個值的函式，放進去還是只會回傳一個結果。

舉例來說，我想把「杯數大於等於 20，或單價大於等於 50」的列標成「主推」。直覺會這樣寫，在 E2 輸入：

OR 會看整欄，只要任何一列成立就回傳一個 TRUE，所以 E2 只會得到一個「主推」，其他列都是空的。

改成用加法代替 OR，乘法代替 AND：

比較式 B2:B>=20 對每一列會得到 TRUE 或 FALSE，而 TRUE 和 FALSE 可以拿來加減乘，所以「加起來大於 0」就是「至少一個成立」。來看看結果：

AND 同理，改成相乘：(B2:B>=10)*(C2:C>=45)，兩個都成立才是 1。照這個寫法，上面資料只有第 2 列和第 6 列會被標成「主推」。

SUM、MAX 這類聚集函式也一樣，想逐列比較兩欄的大小，我會直接用 IF(B2:B>C2:C, B2:B, C2:C)，不要用 MAX。

另一類是本來就吃範圍的函式。SUMIF 的條件如果給它一整欄清單，包上 ARRAYFORMULA 就會一個條件算出一個結果。我在 F2:F5 列出四種品項，在 G2 輸入：

G2 到 G5 會是 33、12、8、9，分別是蘋果紅茶、烏龍奶茶、蘋果綠茶、青茶的杯數總和。SUMIF 本身的用法可以參考 SUMIF 與 COUNTIF。

練習

單一的 ARRAYFORMULA 學會之後，下一步是把它放進真實的表單回覆表，搭配查價目表、自動編號，請看 ARRAYFORMULA 實戰：表單回覆與流水帳自動填滿。

如果包了 ARRAYFORMULA 還是只有一格有值，或是出現 #REF!，請看 ARRAYFORMULA 為什麼只算出一格？。