我手上這張訂單表，為了算「折扣後金額」，旁邊多出三個輔助欄：小計、折扣率、折扣後金額。每新增一筆訂單，這三格都得往下複製，哪天有人手滑蓋掉其中一格，總額就悄悄錯了。
這篇用 MAP、SCAN、BYROW 把輔助欄拿掉，一個公式放在表頭旁邊就好。LET、LAMBDA 的語法在 LAMBDA 與 LET 基礎 講過了，這邊直接動手改表。
原本的訂單表
訂單放在 A1:C9，8 筆：
| A | B | C | |
|---|---|---|---|
| 1 | 品項 | 單價 | 杯數 |
| 2 | 蘋果紅茶 | 45 | 38 |
| 3 | 烏龍奶茶 | 55 | 112 |
| 4 | 蘋果綠茶 | 45 | 8 |
| 5 | 青茶 | 35 | 64 |
| 6 | 桂花烏龍 | 50 | 21 |
| 7 | 烏龍奶茶 | 55 | 48 |
| 8 | 青茶 | 35 | 132 |
| 9 | 蘋果紅茶 | 45 | 88 |
折扣規則是：小計達 1500 打 9 折，達 3000 打 85 折，其餘原價，折扣後金額四捨五入到整數。
先看輔助欄版本。D 欄小計 ，E 欄折扣率 ，F 欄折扣後金額 ，三格各往下填 8 列。第一筆手算：45 × 38 = 1710，達 1500 打 9 折，1710 × 0.9 = 1539。
三個輔助欄的問題我很清楚：欄位多、每格都要維持同一條公式、新增資料要記得往下拉。
逐列算折扣後金額
MAP 一格一格把資料交給 LAMBDA，每次的結果排成一欄。我把輔助欄的三個步驟用 LET 串起來，放進 LAMBDA：
單價與杯數兩欄配對，LET 算小計與折扣率，回傳折扣後金額
=MAP(
B2:B9,
C2:C9,
LAMBDA(
price, qty,
LET(
sales, price*qty,
rate, IFS(sales>=3000, 0.85, sales>=1500, 0.9, TRUE, 1),
ROUND(sales*rate, 0)
)
)
)price, qty：依序拿到 B 欄、C 欄的每一格。sales：單價乘杯數，也就是原本的 D 欄小計。rate：依sales決定的折扣率，也就是原本的 E 欄。ROUND(sales*rate, 0)：原本的 F 欄，回傳這一列的結果。
只放在 E2 一格，結果會自己往下展開到 E9。這邊的 E、F 是結果表的欄名，第一欄是列號：
| E | |
|---|---|
| 2 | 1539 |
| 3 | 5236 |
| 4 | 360 |
| 5 | 2016 |
| 6 | 1050 |
| 7 | 2376 |
| 8 | 3927 |
| 9 | 3366 |
第 3 列 55 × 112 = 6160，打 85 折是 5236；第 4 列 360 沒到 1500，維持原價。
累計加總
接著我想要每一列旁邊再多一欄「累計營收」。以前會多一個輔助欄，=F2 往下變成 。用 SCAN 就是：
把 E2:E9 的折扣後金額一路累加，每一輪的結果都保留
=SCAN(0, E2:E9, LAMBDA(acc, x, acc+x))SCAN 跟 REDUCE 的差別，是它把每一輪累計的結果都排出來，不是只給最後一個。放在 F2，結果是：
| F | |
|---|---|
| 2 | 1539 |
| 3 | 6775 |
| 4 | 7135 |
| 5 | 9151 |
| 6 | 10201 |
| 7 | 12577 |
| 8 | 16504 |
| 9 | 19870 |
最後一格 19870 就是八筆的折扣後總額。如果只想要這個總額， 就夠了。
兩個公式可以併成一條，把 SCAN 的第二個參數直接換成前面的 MAP。我自己習慣拆成兩格，因為 E 欄的金額我之後還會拿來做別的用途。
每週銷量，每列的最大值
這是另一張表，同樣 5 個品項的四週銷量，放在 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 |
我想知道每個品項的單週最高銷量，以及發生在哪一週。以前我會在 F 欄寫 MAX(B2:E2)，G 欄再用 MATCH 找位置。BYROW 一列一列處理，LAMBDA 拿到的 r 是那一整列：
每一列取最大值
=BYROW(B2:E6, LAMBDA(r, MAX(r)))每一列最大值出現在第幾週，用表頭 B1:E1 回傳週名
=BYROW(B2:E6, LAMBDA(r, INDEX($B$1:$E$1, 1, MATCH(MAX(r), r, 0))))分別放在 G2 與 H2，結果：
| G | H | |
|---|---|---|
| 2 | 66 | 第4週 |
| 3 | 127 | 第3週 |
| 4 | 22 | 第3週 |
| 5 | 93 | 第4週 |
| 6 | 34 | 第3週 |
MATCH(MAX(r), r, 0) 是從 r 這一列裡找最大值的位置，INDEX 再拿這個位置去表頭取名字。如果同一列有兩週一樣高，會回第一個。
BYROW 逐列檢查成績：平均與最低分一起看
逐列分類也可以用 BYROW 做，這裡設計成一個界線清楚的練習：三科都已填數值，且平均至少 80、每科至少 60 才「通過」。缺考、空白或文字先顯示待補，不當成 0 分，也不讓 AVERAGE 略過空格後照樣通過。
另開空白表，A1:D5 放以下資料，丁的英文真的留空：
| 合成學生 | 國文 | 英文 | 數學 |
|---|---|---|---|
| 甲 | 80 | 90 | 70 |
| 乙 | 100 | 100 | 40 |
| 丙 | 0 | 0 | 0 |
| 丁 | 80 | （留空） | 90 |
F2 放：
先確認三科都是數值，再判斷平均至少80且各科至少60
=BYROW(
B2:D5,
LAMBDA(
scores,
IF(
COUNT(scores)<>COLUMNS(scores),
"待補成績",
IF(AND(AVERAGE(scores)>=80, MIN(scores)>=60), "通過", "未通過")
)
)
)四列結果依序是通過、未通過、未通過、待補成績；邊界上，80／80／80（平均剛好 80）與 100／80／60（最低剛好 60）也都是通過。乙平均雖達 80，但數學 40，仍不通過；丙的全 0 是有效數值，判為未通過，與缺考不同。這是本篇自訂規則，不是任何學校的成績制度，分數有效範圍也應另用資料驗證限制。
MAP 會員分級：門檻等號與到訪次數
雙條件分級也可以用 MAP 做。這裡明訂：消費至少 80000 且到訪至少 10 次為鑽石；其餘消費至少 50000 為金卡；其餘一般。缺少任一欄先待補，不悄悄算成一般會員。
同一張練習表在 H1:I6 放下表（最後一列兩格留空），K2 放公式：
| 消費 | 到訪次數 | 預期分級 |
|---|---|---|
| 80000 | 10 | 鑽石 |
| 80000 | 9 | 金卡 |
| 50000 | 10 | 金卡 |
| 49999 | 10 | 一般 |
| （留空） | （留空） | 待補資料 |
MAP 每次各拿一筆消費與次數，最嚴格的 AND 條件放最前面
=MAP(
H2:H6, I2:I6,
LAMBDA(
amount, visits,
IF(
OR(amount="", visits=""),
"待補資料",
IFS(
AND(amount>=80000, visits>=10), "鑽石",
amount>=50000, "金卡",
TRUE, "一般"
)
)
)
)上表五種分支，這組 MAP 都會得到預期的分級。80000 的等號屬於高門檻，但到訪只有 9 次仍不能拿鑽石。這個版本假設兩欄都是非負數值、到訪次數是整數；匯入的文字數字與負數應先驗證，不能只看顯示方式。把最高門檻放在前面，才不會讓 80000 提前被金卡分支攔住。
庫存補貨，每一輪都依賴上一輪
最後這題輔助欄寫起來最彆扭。回到一開始的訂單表，倉庫起始庫存 320 杯，每筆訂單出貨後，如果庫存低於 90 就補 250 杯。每一筆訂單的庫存，要看上一筆算完的結果。
用 SCAN 把訂單的杯數 C2:C9 一格一格交進去：
庫存從 320 開始，每筆減掉杯數，低於 90 就補 250
=SCAN(320, C2:C9, LAMBDA(stock, n, IF(stock-n<90, stock-n+250, stock-n)))手算前四筆：320 − 38 = 282，282 − 112 = 170，170 − 8 = 162，162 − 64 = 98，都沒低於 90。放在 G2，八筆的結果是：
| G | |
|---|---|
| 2 | 282 |
| 3 | 170 |
| 4 | 162 |
| 5 | 98 |
| 6 | 327 |
| 7 | 279 |
| 8 | 147 |
| 9 | 309 |
第 6 列 98 − 21 = 77，低於 90，補 250 變成 327。第 9 列 147 − 88 = 59，補完是 309。整段只補了兩次貨。
如果只想知道跑完八筆之後剩多少，把 SCAN 換成 REDUCE，其餘照抄：
同樣的規則，只回傳最後的庫存
=REDUCE(320, C2:C9, LAMBDA(stock, n, IF(stock-n<90, stock-n+250, stock-n)))結果是 309。這種「這一格要看上一格」的題目，以前的輔助欄只能一格一格往下接，SCAN 把它收進一條公式。
新增一筆訂單怎麼辦
公式範圍寫死成 B2:B9，新增第 10 筆訂單就不會被算進去。改成 B2:B 這種開放的範圍，MAP 會連空白列也跑一遍，空白的單價乘空白的杯數是 0，所以下面會多出一長串 0。我個人的作法是先用 FILTER 過濾掉空白列再交給 MAP：
只處理單價有填的列，空白列不進 MAP
=MAP(
FILTER(B2:B, B2:B<>""),
FILTER(C2:C, B2:B<>""),
LAMBDA(price, qty, price*qty)
)FILTER 的細節請看 FILTER 函式。
什麼時候還是留輔助欄
我不是每次都拿掉輔助欄。
- 同組的人要看中間值：小計、折扣率如果有人想逐欄對帳，輔助欄的中間結果擺在眼前比較好檢查。
LET裡的名稱看不到值。 - 公式要給別人維護：
MAP加LET加IFS一條寫下來很長，不熟LAMBDA的同事會不敢動。這時我會把折扣規則存成具名函式，做法在 用具名函式建團隊函式庫。 - 簡單的逐列運算：只是兩欄相乘，用 ARRAYFORMULA 比
MAP短。
挑一張你有三個以上輔助欄的表，試著用 MAP 配 LET 收成一條公式看看吧！
我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章LAMBDA 與 LET 基礎：自訂函式、MAP 與 REDUCE
用 LET 幫重複出現的算式取名字，用 LAMBDA 自己定義函式並存成具名函式，再搭配 MAP 一格一格套用、用 REDUCE 一路累計成一個值，附 BYROW、BYCOL、SCAN、MAKEARRAY 的最短介紹。
用具名函式建團隊函式庫：含稅價、會員等級、工作天數
把團隊常用的計算邏輯，像含稅價、會員等級、扣掉假日的工作天數，做成一組具名函式放在一份函式庫試算表，其他試算表用「匯入函式」取用。附命名慣例與維護做法。
表單回覆表自動填滿：編號、單價、金額、狀態一次搞定
用 Google 表單收訂單時，在回覆工作表旁邊用 ARRAYFORMULA 自動產生編號、查價目表帶出單價、算金額，並用巢狀 IF 標出大單與待確認的列，新回覆進來不用再拖公式。
留言討論
共 0 則留言