為什麼我包了 ARRAYFORMULA，結果還是只有最上面一格有值？多半是裡面有個函式把整欄收斂成一個數字，或是 VLOOKUP 漏包在外面。ARRAYFORMULA 的寫法、A2:A 和 IF(A2:A="", "", ...) 在 ARRAYFORMULA 入門 與 表單回覆表自動填滿 講過，這篇直接看出狀況的地方。
用 Excel 的人先知道一件事：本篇「SUM、MAX 包了 ARRAYFORMULA 還是只回一格」是 Google 試算表的情境；Excel 365／2021／2024 有動態陣列， 本來就會自動溢出，也沒有 ARRAYFORMULA 這個函式。BYROW、MAP、LAMBDA 在 Excel 365 與 Excel 2024 可用，語法相同；XLOOKUP 在 Excel 365／2021／2024 可用，Excel 2019 以前沒有。
後面都用這張訂單，放在 A1:D9，D 欄的單價先當成已經填好的數字（查價目表的那一節會改放在 I 欄）。每個範例的結果各放在 F 欄或文中指定的位置，照著試的時候，換下一個範例前先把上一個範例的結果清掉，免得互相擋住：
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 品項 | 上午杯數 | 下午杯數 | 單價 |
| 2 | 蘋果紅茶 | 12 | 8 | 45 |
| 3 | 青茶 | 7 | 14 | 35 |
| 4 | 烏龍奶茶 | 9 | 6 | 55 |
| 5 | 蘋果綠茶 | 5 | 10 | 40 |
| 6 | 青茶 | 11 | 3 | 35 |
| 7 | 蘋果紅茶 | 4 | 9 | 45 |
| 8 | 烏龍奶茶 | 8 | 2 | 55 |
| 9 | 冬瓜檸檬 | 6 | 1 | 50 |
SUM 和 SUMPRODUCT：整欄只回一個數字
入門篇講過 AND、OR、MAX 放進去不會逐列算，SUM 和 SUMPRODUCT 也是同一類。我想在 F 欄算每一列的總杯數，直覺這樣寫，在 F2 輸入：
F2 得到 115，F3 以下全是空的。SUM 把 B2:C9 整塊加成一個數字，62 加 53 就是 115。要逐列，直接用加號：
每一列的總杯數
=ARRAYFORMULA(B2:B9+C2:C9)| F | |
|---|---|
| 2 | 20 |
| 3 | 21 |
| 4 | 15 |
| 5 | 15 |
| 6 | 14 |
| 7 | 13 |
| 8 | 10 |
| 9 | 7 |
SUMPRODUCT 也一樣。我想算每一列的金額，卻寫成：
這條回傳 5035，是所有訂單的總營收，不是每列一個。SUMPRODUCT 本來就會吃整個範圍，用在算總營收一格就夠了，不必包 ARRAYFORMULA：
一樣是 5035。要每列的金額，把乘法寫出來：
每一列的金額
=ARRAYFORMULA((B2:B9+C2:C9)*D2:D9)| F | |
|---|---|
| 2 | 900 |
| 3 | 735 |
| 4 | 825 |
| 5 | 600 |
| 6 | 490 |
| 7 | 585 |
| 8 | 550 |
| 9 | 350 |
這八個數字加起來就是 5035。
每列要用函式時：BYROW、MAP 還是 FILTER
但有些事不能用加號乘號改寫。我想知道每一列上午和下午哪個比較多，手看是 12、14、9、10、11、9、8、6。MAX 放進 ARRAYFORMULA 會得到整塊的最大值 14，這時候我改用 BYROW，它會把範圍一列一列交給 LAMBDA：
每一列取上午、下午的較大值
=BYROW(B2:C9, LAMBDA(r, MAX(r)))結果就是 12、14、9、10、11、9、8、6。兩欄各取一個值比較的話，MAP 也行，兩個寫法答案一樣：
BYROW、MAP 的觀念在 LAMBDA 自訂函式 有講，這邊只談怎麼選。我個人的順序是這樣：
- 加減乘除、
IF、比較這類單格就能寫的，用ARRAYFORMULA，一眼看得懂。 - 每列要做「彙總」（
MAX、SUM、TEXTJOIN）的，用BYROW。 - 要「留下某些列」、不要每列補一格的，用
FILTER。
最後一種舉例：上午加下午至少 15 杯的訂單。結果放在 J2，會展開成 J2:L5，避開 G、H 欄的價目表：
總杯數大於等於 15 的列
=FILTER(A2:C9, B2:B9+C2:C9 >= 15)| J | K | L | |
|---|---|---|---|
| 2 | 蘋果紅茶 | 12 | 8 |
| 3 | 青茶 | 7 | 14 |
| 4 | 烏龍奶茶 | 9 | 6 |
| 5 | 蘋果綠茶 | 5 | 10 |
BYROW 配開放式範圍要小心。BYROW(B2:C, ...) 連下面的空白列都會處理，空白列的 MAX 會是 0，整欄後面會拖出一堆 0。我會先用 FILTER 把有資料的列挑出來再交給它：
這個資料上結果跟前面一樣，也是 12、14、9、10、11、9、8、6。FILTER 一列都沒找到會是 #N/A，所以全空的表會顯示錯誤，真的要擋再包 IFERROR。
混用 VLOOKUP
價目表放在 G1:H6，單價從這裡查：
| G | H | |
|---|---|---|
| 1 | 品項 | 單價 |
| 2 | 蘋果紅茶 | 45 |
| 3 | 青茶 | 35 |
| 4 | 烏龍奶茶 | 55 |
| 5 | 蘋果綠茶 | 40 |
| 6 | 冬瓜檸檬 | 50 |
在 I2 輸入 VLOOKUP，查詢值給整欄品項，忘了包 ARRAYFORMULA：
I2 只會得到 45，I3 以下是空的，它只查了第一個品項。外面包上 ARRAYFORMULA 才會逐列查：
每一列查價目表的單價
=ARRAYFORMULA(VLOOKUP(A2:A9, G2:H6, 2, 0))| I | |
|---|---|
| 2 | 45 |
| 3 | 35 |
| 4 | 55 |
| 5 | 40 |
| 6 | 35 |
| 7 | 45 |
| 8 | 55 |
| 9 | 50 |
另一個要提的是 VLOOKUP 第三個參數的 2 是寫死的欄序。價目表的 G、H 中間若被人插了一欄，2 就會指到新欄，不報錯但單價是錯的。我自己查價目表改用 XLOOKUP，查詢欄和結果欄各自指定，插欄不受影響：
結果同樣是 45、35、55、40、35、45、55、50。XLOOKUP 的寫法在 XLOOKUP 入門。兩個範圍高度要一致，XLOOKUP(A2, G2:G6, H2:H5) 這種差一列的寫法在 Google 試算表會是 #N/A。
資料下面還有空白列的時候，開放式的 A2:A 去查會在空白列得到 #N/A，外面包 IF(A2:A="", "", ...) 就好，寫法在實戰篇。
整欄範圍：算到哪一列
開放式範圍 B2:B 方便，代價是公式的範圍包含工作表下面所有的列。試算表效能基礎 提到，公式實際要算的列數取決於工作表目前用到的列數。這節不給速度的數字，只講三種寫法的差別。
- 開放式
B2:B：新增資料自動跟上，下面空白列也會被算，要靠IF(A2:A="", "", ...)擋掉。 - 固定上限
B2:B500：範圍小，但是超過第 500 列的資料不會被算，而且各個範圍的大小要一致。把其中一個寫成C2:C499並不會整個失敗：兩邊長度對不起來時，短的那邊在多出的那一列補#N/A，其他列照常相乘。錯誤只落在最後一列，反而更容易漏看。 - 用
INDEX動態決定尾端：B2:INDEX(B:B, COUNTA(A:A))。
第三種的例子，A 欄有標題加 8 筆，COUNTA(A:A) 是 9，範圍就是 B2:B9：
範圍尾端跟著 A 欄筆數走
=ARRAYFORMULA(B2:INDEX(B:B, COUNTA(A:A)) + C2:INDEX(C:C, COUNTA(A:A)))結果跟前面的 B2:B9+C2:C9 一樣，20、21、15、15、14、13、10、7。INDEX 不屬於官方列出的會定時重新計算的函式（官方列的是 NOW、TODAY、RAND、RANDBETWEEN 等）。條件是 A 欄中間不能有空格、下面不能放別的東西，不然 COUNTA 就數歪了。
我的習慣是資料量小就用開放式，加上 IF 擋空白；公式多了開始覺得慢，再改成有上限的範圍。
被擋住的 #REF!
入門篇提過結果的那一欄要留空。實際上最難找的是「看起來是空的」的格子。
假設 F5 裡面有一個以前留下來的 =""，公式的結果是空字串，看起來沒東西，但它不是真正的空白。這時在 F2 輸入：
F2 會顯示 #REF!，把滑鼠移上去，提示是「系統未展開陣列結果，以免覆蓋 F5 中的資料。」提示裡的儲存格就是擋路的那一格。把 F5 清掉，結果就整欄展開了。
排查時我看三件事：提示裡的儲存格、那格是不是有舊公式（="" 這類）、有沒有同事在結果範圍裡手動打了數字。
練習
找出錯在哪
同事想在 F 欄算「上午、下午取較大值，乘以單價」，寫了：
每一列都是用 14 去乘單價。請說明原因並改正，答案要是每列各自取較大值。
遇到只出現一格的時候，先找整欄被收斂的函式，再檢查有沒有漏包的 ARRAYFORMULA。我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章留言討論
共 0 則留言