如果你是老師，要幫全班每個人加 5 分，你會一位一位去改成績，還是在黑板上寫「全班加 5 分」？
試算表兩種做法都可以，這篇先聊看公式的角度：從「一格算一個值」換成「一個公式算一整組值」。後面的 ARRAYFORMULA、FILTER、SORT 都是這個角度的延伸，也會用一個小例子看花括號如何拼接資料。
我用五位同學的成績當例子，A 欄是分數：58、73、91、66、84。
一格一格算
先照老派的方式來。手算很直接：58 + 5 = 63，73 + 5 = 78，91 + 5 = 96，66 + 5 = 71，84 + 5 = 89，一共算五次。
在試算表裡，我會在 B2 輸入 ，再往下拖到 B6：
← 左右滑動看完整的圖，點圖可放大
圖：左邊是一格一個公式，右邊是一個公式，B3 到 B6 只是它展開的結果。
左邊就是拖完的樣子：B2 是 ，B3 是 ，一路到 B6，五格裡有五個各自獨立的公式。要把「加 5 分」改成「加 8 分」，得改完 B2 再拖一次。這種「一個蘿蔔一個坑」的做法沒有錯，只是資料多的時候，公式副本也跟著變多。
一個公式算一整欄
換成陣列的角度，我在 B2 輸入：
一個公式，算出 A2 到 A6 每個分數加 5 分
=ARRAYFORMULA(A2:A6+5)A2:A6 是五個分數合在一起的一組值，加 5 就是「這一組裡的每一個都加 5」。來看看結果：
| A | B | |
|---|---|---|
| 1 | 分數 | 加 5 分 |
| 2 | 58 | 63 |
| 3 | 73 | 78 |
| 4 | 91 | 96 |
| 5 | 66 | 71 |
| 6 | 84 | 89 |
B3 到 B6 我什麼都沒打，它們是 B2 這個公式往下「展開」出來的結果。要改成加 8 分，只改 B2 一個地方就好。
ARRAYFORMULA 本身怎麼寫、A2:A 這種不寫終點的範圍怎麼讓新資料自動算進來，在 ARRAYFORMULA 入門 有完整講解，這邊不重複。
吃一組，吐一個還是一組
同樣是把整個範圍交給函式，結果的形狀可以不一樣：
- 吃進一組，吐出一個值：372。
- 吃進一組，吐出一組：91、84、73、66、58，從輸入公式的那格往下排開。
IF、ROUND這類本來一次只看一格的函式，在 Google 試算表要包上ARRAYFORMULA才會一次處理整欄。
這三種都是日後會一直遇到的樣子，看到一個新函式，先問自己「它吃進一組之後吐出什麼形狀」，比硬背語法有用。
用 Excel 的人注意：Excel 365／2021／2024 有動態陣列， 這種公式會自動溢出，不需要包任何函式，Excel 也沒有 ARRAYFORMULA 這個函式；Excel 2019 以前才要用 Ctrl+Shift+Enter 的舊式陣列公式。另外 SORT 降序在 Google 試算表寫 FALSE，Excel 要寫 -1。本系列後面的 TOROW、TOCOL、VSTACK、HSTACK、CHOOSECOLS 等函式，Excel 365 與 Excel 2024 有同名函式，Excel 2021 沒有。
陣列的形狀
陣列有方向。縱向的一欄、橫向的一列，還有欄列都有的二維範圍，是三種最常見的形狀。
我再加一個小題目：每位同學有三種補考加分，3、5、8，我想知道每個人套用每種加分的結果。把加分放在橫向的 C1:E1，在 C2 輸入：
縱向的分數加上橫向的加分，得到二維結果
=ARRAYFORMULA(A2:A6+C1:E1)
← 左右滑動看完整的圖，點圖可放大
圖：一欄加一列，每一格都是「該列的分數加上該欄的加分」。
來看看結果，C2:E6 的 15 格都是一個公式展開的：
| C | D | E | |
|---|---|---|---|
| 2 | 61 | 63 | 66 |
| 3 | 76 | 78 | 81 |
| 4 | 94 | 96 | 99 |
| 5 | 69 | 71 | 74 |
| 6 | 87 | 89 | 92 |
這個結果跟 參照的藝術 裡用混合參照拖出來的二維折扣表長得一樣：那邊靠 $ 決定每一格該讀哪一欄、哪一列，這邊靠陣列的形狀自己交叉出來。
要記得，兩邊的大小得對得上。一欄五格加一列三格可以交叉成 5 × 3，但一欄五格加另一欄四格，就沒有一一對應的格子可以配了，不要這樣寫。
看懂花括號：先拼形狀，再交給 QUERY
{ } 是陣列常值／組合陣列的寫法，可以把幾塊範圍拼成一個陣列；想複習可以看方格子的花括號舊文。
另開空白表，在 A2:B3 放 A／10、B／20，在 D2:E3 放 C／30、D／40：
| 區塊 | 第1列 | 第2列 |
|---|---|---|
A2:B3 | A、10 | B、20 |
D2:E3 | C、30 | D、40 |
分號向下疊，兩塊各兩欄，結果四列兩欄
={A2:B3;D2:E3}逗號向右接，兩塊各兩列，結果兩列四欄
={A2:B3,D2:E3}上下疊應是 A／10、B／20、C／30、D／40 四列；左右接則第一列 A／10／C／30、第二列 B／20／D／40。垂直拼接要欄數相同，水平拼接要列數相同；本例使用台灣地區的分隔符號，其他地區設定遇到剖析錯誤時，先核對其陣列分隔規則。
組好的陣列可以交給 QUERY，欄位用 Col1、Col2：
先疊兩塊，再取至少20杯，依杯數由大到小
=QUERY(
{A2:B3;D2:E3},
"select Col1,Col2 where Col2 >= 20 order by Col2 desc",
0
)垂直拼接得到四列兩欄、水平拼接得到兩列四欄，這個查詢的結果依序是 D／40、C／30、B／20。資料區沒有表頭，所以最後填 0；Col2 是組合陣列的第 2 欄，不是隨意套原工作表字母。
這套形狀規則也能讀懂舊的多段 IMPORTRANGE 拼接，但多拆幾次不等於比較快；每次外部讀取、欄位對齊、權限與維護成本仍要另外確認。單純堆疊也可看 VSTACK／HSTACK，不要只為了保留花括號而增加外部連線。
展開需要乾淨的空間
結果是一整組，它就需要一整塊空位。回到第一個例子，如果 B4 以前手動輸入過「舊資料」，B2 的公式就展不開：
← 左右滑動看完整的圖，點圖可放大
圖：同一個公式，下面有沒有東西擋著，結果就不一樣。
這時 B2 會顯示 #REF!，提示是「系統未展開陣列結果，以免覆蓋 B4 中的資料。」。把 B4 清掉，結果就會展開。看到這個錯誤先別慌，通常只是下面有東西擋著，Excel 365／2021／2024 動態陣列的 #SPILL!，在 Google 試算表裡就是這個 #REF!。
什麼時候用，什麼時候不用
我個人的習慣是：資料會一直新增的欄位，就用陣列公式；一次性的小表，五、六列而已，我還是直接拖，比較直覺。
如果輸出的列數會比輸入少，例如只想留下 80 分以上的人，那是 FILTER 的工作，它一樣吃進一組、吐出一組，只是吐出的比較短。想看陣列公式放進真實的訂單表長什麼樣，可以接著看 表單回覆表自動填滿。
下次看到一個公式，先想想它面對的是一格還是一整組，試著用用看吧！
我是喜特先生，Mr. Sheet，我們下個教學見！
這個步驟如果每週都要重做
- 現在
- 每次成績更新後，把整理好的結果手動寄給各班老師
- 自動化後
- 成績表一更新就自動算好結果，並寄給各班老師
接下來可以
這篇在技能樹的 Lv.0「核心思維」，模組「陣列思維」（第 1／1 篇）。讀完這篇，路線上的下一個模組是「隱私設計」。
回到技能樹相關文章
查看所有文章ARRAYFORMULA 入門：一個公式算完整欄，新增資料自動跟上
從公式拖拉到一半漏掉新資料的問題出發，說明 ARRAYFORMULA 的寫法、A2:A 這種開放式範圍、用 IF 擋掉空白列，以及 AND、OR 這類函式在裡面為什麼不會逐列計算。
表單回覆表自動填滿：編號、單價、金額、狀態一次搞定
用 Google 表單收訂單時，在回覆工作表旁邊用 ARRAYFORMULA 自動產生編號、查價目表帶出單價、算金額，並用巢狀 IF 標出大單與待確認的列，新回覆進來不用再拖公式。
FILTER 入門：用一個條件篩出你要的資料
FILTER 的語法、單一條件怎麼寫、兩個條件的 AND 與 OR，以及沒有符合資料時的 #N/A。原始資料不動，結果放在別處。
留言討論
共 0 則留言