我用 BYCOL 算每一欄的最大值，公式一按下去，儲存格卻顯示 #REF!。公式沒寫錯，是它想往右展開，路上被別的東西擋住了。
LAMBDA 家族的函式（MAP、REDUCE、BYROW、BYCOL、SCAN、MAKEARRAY）語法在 LAMBDA 與 LET 基礎 講過，實戰例子在 拿掉輔助欄。這篇專門收我覺得最容易讓人卡住的幾個地方。
後面大部分的例子都用這張週銷量表，放在 A1:E6：
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 品項 | 第1週 | 第2週 | 第3週 | 第4週 |
| 2 | 蘋果紅茶 | 37 | 52 | 41 | 66 |
| 3 | 烏龍奶茶 | 112 | 98 | 127 | 84 |
| 4 | 蘋果綠茶 | 8 | 15 | 22 | 11 |
| 5 | 青茶 | 64 | 71 | 58 | 93 |
| 6 | 桂花烏龍 | 21 | 19 | 34 | 28 |
BYCOL 的結果是橫著長的
重現：我想要每一週的最高銷量，放在 G2：
每一欄取最大值，LAMBDA 拿到的 wk 是那一整欄
=BYCOL(B2:E6, LAMBDA(wk, MAX(wk)))四欄各出一個值，所以結果是一列四格，G2 到 J2：
| G | H | I | J | |
|---|---|---|---|---|
| 2 | 112 | 98 | 127 | 93 |
跟 BYROW 對照：BYROW 一列出一個值，結果是直的；BYCOL 一欄出一個值，結果是橫的。
如果 I2 已經有東西，G2 會是 #REF!，游標移上去，提示是「系統未展開陣列結果，以免覆蓋 I2 中的資料。」。公式本身沒問題，清掉 I2，或把公式搬到右邊有空位的地方就好。
想要直的，外面包 TRANSPOSE：
把橫向的結果轉成直向，G2:G5 四格
=TRANSPOSE(BYCOL(B2:E6, LAMBDA(wk, MAX(wk))))結果是 G2:G5 的 112、98、127、93。TRANSPOSE 的細節看 TRANSPOSE、FLATTEN、ARRAY_CONSTRAIN。
SCAN 不含初始值，REDUCE 保留最後累計
重現：拿第 1 週的銷量 B2:B6，初始值 0，一路累加。SCAN 與 REDUCE 的公式只差函式名稱：
SCAN 把每一輪的累計都排出來
=SCAN(0, B2:B6, LAMBDA(acc, x, acc+x))REDUCE 只回傳最後一輪的累計
=REDUCE(0, B2:B6, LAMBDA(acc, x, acc+x))手算：37、37 + 112 = 149、149 + 8 = 157、157 + 64 = 221、221 + 21 = 242。SCAN 會得到 5 格 37、149、157、221、242，REDUCE 只得到 242。
兩件事容易搞混：
- 初始值 0 不會出現在
SCAN的結果裡，第一格是 37 不是 0。本例每筆輸出一個累計值，所以輸出和輸入同樣是 5 格。 - 想要一整串卻寫成
REDUCE，只會拿到一個數字，沒有錯誤，很容易以為公式壞了，其實是函式選錯。
MAKEARRAY 的 r 和 c 從 1 開始
MAKEARRAY 不吃現成的範圍，自己指定列數與欄數，LAMBDA 拿到列號與欄號。我拿它做一張複利表：起始 48000，三種年利率 0.02、0.035、0.05 放在 B1:D1，A2:A6 手動輸入 1 到 5 年。公式放在 B2：
用 LET 幫利率取名，MAKEARRAY 的 yr 是第幾年，k 是第幾種利率
=LET(
rates, B1:D1,
MAKEARRAY(5, 3, LAMBDA(yr, k, ROUND(48000*(1+INDEX(rates, 1, k))^yr, 0)))
)先手算一格：第 1 年 2% 是 48000 × 1.02 = 48960。結果 B2:D6：
| B | C | D | |
|---|---|---|---|
| 2 | 48960 | 49680 | 50400 |
| 3 | 49939 | 51419 | 52920 |
| 4 | 50938 | 53218 | 55566 |
| 5 | 51957 | 55081 | 58344 |
| 6 | 52996 | 57009 | 61262 |
列號、欄號都從 1 起算，沒有第 0 年那一列。想看起始值，次方要改成 yr-1，下面的練習就是這題。
陣列回傳：官方限制與實際行為
重現：A2:A4 是三杯飲料的標籤，每格用逗號分隔：
| A | |
|---|---|
| 1 | 標籤 |
| 2 | 冷,半糖 |
| 3 | 熱,無糖,加珍珠 |
| 4 | 冷,微糖 |
我想把所有標籤拆開成一欄，順手寫了 MAP：
MAP 的 LAMBDA 回傳 SPLIT 的結果，一格會變成好幾格
=MAP(A2:A4, LAMBDA(x, SPLIT(x, ",")))MAP、BYROW與 BYCOL官方說明仍要求每次回傳單一值，不支援巢狀陣列。撰寫可維護的公式時，應優先依循這個文件說明。
不過實際輸入會看到不同的結果：上述 MAP 會展開成三列三欄，缺少的欄位留空；BYROW(B2:E6,LAMBDA(r,r))、BYCOL(B2:E6,LAMBDA(c,c)) 也都回傳原本 5×4 的矩陣。這不是報錯，但和官方說明不一致，不要把它當成任意形狀的巢狀陣列都支援的保證。
如果目標是把標籤攤成一欄，應明確選擇合併與堆疊的寫法，不依賴這項和官方說明不同的展開行為。
我有兩種改法。第一種是先別拆，整串接起來再一次拆：
TEXTJOIN 先把三格接成一串，SPLIT 再一次拆，TRANSPOSE 轉成直的
=TRANSPOSE(SPLIT(TEXTJOIN(",", TRUE, A2:A4), ","))結果是七格：冷、半糖、熱、無糖、加珍珠、冷、微糖。
第二種是改用 REDUCE，把每一輪的結果用 VSTACK 疊在累計後面。初始值我放表頭「標籤」，順便當第一列：
REDUCE 的累計是一個陣列，每一輪用 VSTACK 往下疊
=REDUCE("標籤", A2:A4, LAMBDA(acc, x, VSTACK(acc, TRANSPOSE(SPLIT(x, ",")))))結果是八格，第一格「標籤」，後面依序是冷、半糖、熱、無糖、加珍珠、冷、微糖。REDUCE 的累計可以是陣列，由 VSTACK 明確堆疊。這題如果只是要拆，我個人會用第一種，短很多。
兩個範圍大小不一致
重現：算單週銷量乘上單價時，不小心把第二個範圍少選了一列，B2:B6 配 C2:C5：
MAP 的兩個範圍大小不同，一個 5 列一個 4 列
=MAP(B2:B6, C2:C5, LAMBDA(a, b, a*b))範圍大小不一致時，整個公式會得到 #VALUE!，例如 3 列配 2 列的 MAP({1;2;3},{4;5},LAMBDA(a,b,a*b)) 也是。它不是先算短範圍再補空白。範圍大小不符的錯誤不能從其他函式推定，例如 FILTER 可回 #N/A，仍要看實際提示。
修法很單純，把兩個範圍選成一樣大。資料還在增加的話，我會讓兩個範圍都從同一個 FILTER 出來，細節在 拿掉輔助欄 的最後一節。
MAKEARRAY 做隨機姓名矩陣時先定規則
比方用人員清單生成 8×6 的隨機姓名表。這和不重複分組不同：每格各抽一次，同一個人可以出現很多次。
做之前先排除清單中間的空白，確認空清單時要顯示什麼。RANDBETWEEN 會重新計算，不能把剛好看到的名單當成已固定的抽籤結果；輸出區也要先空出 8 列、6 欄。若要留存這次結果，另存原始名單與規則，再把結果貼成值。
例如把「小林」、空白、「小陳」、「小王」與其餘空白放在 P2:P8，公式放在 R2，預先清空 R2:W9：
先排除空白，再做有放回的 8×6 隨機抽樣；空名單顯示提示
=LET(
src, P2:P8,
IF(
COUNTIF(src, "<>")=0,
"沒有可抽樣名單",
LET(
names, FILTER(src, src<>""),
MAKEARRAY(
8, 6,
LAMBDA(rr, cc, INDEX(names, RANDBETWEEN(1, ROWS(names))))
)
)
)
)結果是 8×6、共 48 格，每格都來自有效名單，同一個人可能重複出現。全空白來源回傳「沒有可抽樣名單」；輸出區預放了文字的話，公式會回 #REF!，原文字保留，所以要先清空。每次抽出的具體排列不固定，驗收時看尺寸與成員是否正確，而不是比對某一張隨機結果。
遞迴：具名函式呼叫自己
直接寫在儲存格的 LAMBDA 沒有名字，叫不到自己。要遞迴，得先存成具名函式。位數加總的 DIGIT_SUM 已經在 具名函式的進階陷阱 示範過，這邊換一個題目：48000 的存款，每年複利，幾年後會到 100000？
具名函式 YEARS_TO 的引數是 amount、target、rate，公式定義是：
已經達標就回 0 年，否則過一年再算一次；利率 0 以下永遠到不了，直接回錯誤
=IF(
amount>=target,
0,
IF(rate<=0, NA(), 1+YEARS_TO(amount*(1+rate), target, rate))
)amount>=target：結束條件，已經達標，不用再等。rate<=0：另一個結束條件。利率是 0，金額永遠不增加，遞迴會一直呼叫自己；這邊直接回#N/A。1+YEARS_TO(...)：過了一年，用年後的金額再問一次。
呼叫 ，手算 48000 × 1.035 的 22 次方約是 102312，21 次方還不到 100000，結果是 22。利率換成 0.05 是 16，換成 0.02 是 38。
驗算不想存具名函式的話，可以像進階陷阱那篇一樣，把 LAMBDA 自己當引數傳進去：
驗算用：f 就是 LAMBDA 本身，f(f, ...) 是呼叫自己
=LAMBDA(
f, a, t, r,
f(f, a, t, r)
)(
LAMBDA(f, a, t, r, IF(a>=t, 0, IF(r<=0, NA(), 1+f(f, a*(1+r), t, r)))),
48000,
100000,
0.035
)這段得到的同樣是 22；利率換成 0.05 是 16，0.02 是 38，0 則是 #N/A。
這一題其實有現成的函式：NPER 算出需要的期數，往上進位就是答案。
用 NPER 算期數再無條件進位，不需要遞迴
=ROUNDUP(NPER(0.035, 0, -48000, 100000), 0)NPER 的結果是 21.3354 左右，進位後是 22，與前面的手算年數一致。所以我自己的順序是：先找內建函式，再試 MAP、REDUCE、SCAN，真的每一步都依賴自己的結果、又沒有現成函式時才寫遞迴。
忘了結束條件，Google 說明中心提到會出現「Calculation limit was reached while trying to compute this formula」之類的計算上限錯誤，解法是簡化公式。
快不快，這篇不下結論
常有人問 MAP 會不會比輔助欄慢，或是遞迴比 REDUCE 慢多少。我沒有量過，這篇不給結論，也不給「超過幾列就不要用」的門檻。
想知道自己的資料適不適合，最老實的做法：把同一份資料複製成兩個工作表，一個放輔助欄、一個放 LAMBDA 公式，各自改一個輸入值，看重新計算要等多久。資料量一樣才有比較的意義。
練習
找出錯在哪
我想看起始 48000 的複利表，第一列要顯示第 0 年（也就是還沒計息的 48000），A2:A6 的年數也改成 0 到 4。B1:D1 放三種利率，公式放在 B2：。結果第一列卻是 48960、49680、50400，錯在哪？要怎麼改？
遇到錯誤先看提示、引數數量與輸入大小，再核對回傳形狀。官方說明與實際行為不同的地方，不要依賴它。
我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章留言討論
共 0 則留言