每筆銷售金額都要加上同一年之前的金額，到了新年再從當年的第一筆開始。SCAN 會回傳每一步的累計值，很適合做年度進度表。
關鍵是以日期的年份決定是否重設。空白列只是版面留白，不能代表跨年；反過來說，資料沒有插入空白列，跨年仍然要重新計算。
先準備故意亂序的資料
在 A1:B7 輸入下表，A4:B4 整列留空。日期必須是試算表認得的真正日期，金額必須是數值；可用日期選擇器輸入，避免把日期當成文字。
| 列 | A：日期 | B：金額 |
|---|---|---|
| 1 | 日期 | 金額 |
| 2 | 2027/1/2 | 0 |
| 3 | 2026/12/30 | 10 |
| 4 | ||
| 5 | 2027/1/1 | 5 |
| 6 | 2026/12/31 | 20 |
| 7 | 2026/12/29 | 2.5 |
先保留 A8:B20 空白。這個案例同時有跨年、夾在兩個年度交界的空列、小數與零金額。
在 D1:G1 依序填「排序日期、排序金額、年度累計、達成率」，J1 填「年度目標」，J2 填數值 100。清空 D2:G20，供三組公式展開。
第一步：檢查成對資料，再按日期排序
在 D2 輸入：
空列略過，日期或金額缺一格就先停止
=IF(
COUNTA(A2:B20)=0,
"沒有資料",
IF(
OR(
SUMPRODUCT(N((A2:A20="")<>(B2:B20="")))>0,
COUNT(A2:A20)<>COUNTA(A2:A20),
COUNT(B2:B20)<>COUNTA(B2:B20)
),
"請檢查日期與金額",
SORT(FILTER(A2:B20, A2:A20<>""), 1, TRUE)
)
)公式會擋下日期與金額沒有成對、日期為文字、金額為文字的情況。整份輸入空白顯示「沒有資料」。檢查的是數值型別；它無法判斷誤填的數字是不是你真正要的日期，因此仍要人工核對年份與日期顯示。
把 D2:D20 設為日期格式。D2:E6 應依序是：
| 排序日期 | 排序金額 |
|---|---|
| 2026/12/29 | 2.5 |
| 2026/12/30 | 10 |
| 2026/12/31 | 20 |
| 2027/1/1 | 5 |
| 2027/1/2 | 0 |
原始空列已移除，但不會因此重設累計。若同一天有多筆資料，本例逐筆累計；需要固定同日的先後順序時，應再加交易時間或唯一序號排序。本篇資料每天只有一筆。
第二步：用 SCAN 比較前後年份
在 F2 輸入：
第一筆直接取金額，年份相同累加，年份改變重新開始
=IF(
COUNT(D2:D20)=0,
"",
LET(
d, FILTER(D2:D20, D2:D20<>""),
v, FILTER(E2:E20, D2:D20<>""),
SCAN(
0,
SEQUENCE(ROWS(d)),
LAMBDA(acc, i,
IF(
i=1,
INDEX(v, i),
IF(
YEAR(INDEX(d, i))=YEAR(INDEX(d, i-1)),
acc+INDEX(v, i),
INDEX(v, i)
)
)
)
)
)
)SCAN 這次掃描的是列序號，而不是直接掃金額。如此一來，i 可以同時取得這一列的日期與金額，再比較前一列的年份。acc 保存上一步的累計結果。第一筆沒有前一筆可比，直接從它的金額開始。
F2:F6 會是 2.5、12.5、32.5、5、5。2027 年第一筆回到 5，第二筆金額為 0，所以維持 5。若金額是退款負值，會從當年度累計扣除，不會因負值而重設。
Google 官方說明，SCAN 的 LAMBDA 接受兩個名稱參數，每一步需回傳單一值；本例每次回傳的就是該列累計金額。SCAN 官方說明
第三步：加入年度目標達成率
在 G2 輸入，再把 G2:G20 設成百分比：
本例每年共用同一個正數年度目標
=IF(
OR(NOT(ISNUMBER($J$2)), $J$2<=0),
"請填正數目標",
IF(
COUNT(F2:F20)=0,
"",
ARRAYFORMULA(IF(F2:F20="", "", F2:F20/$J$2))
)
)J2 是每年的共同目標 100，所以五筆達成率依序是 2.5%、12.5%、32.5%、5%、5%。達成率會跟著年度累計重設；不是把所有年份一起除以目標，也不是讓每筆金額各自除完就結束。
目標填 0、負數或文字時，顯示「請填正數目標」。試完請把 J2 改回 100。若每年的目標不同，需要另建年度目標對照表，本篇這個共同目標公式就不適用。
練習與使用限制
- 把
B3從 10 改成 -2，確認 2026 年的累計變成 2.5、0.5、20.5；2027 年仍是 5、5。完成後還原為 10。 - 清空
A3、保留B3，應顯示「請檢查日期與金額」；還原日期 2026/12/30。 - 把
J2改成 0，確認得到提示而不是除以零錯誤，再還原為 100。
輸入範圍只到第 20 列；增加資料時，要同步擴充原始範圍、排序結果及後續累計參照。此處使用西曆年度，不適用從七月開始的會計年度。範例也沒有自動排除重複訂單，重複輸入會重複累加。
若出現 #REF!，先清空結果範圍並取消合併儲存格。不要把手動備註插進 D2:G20；備註應放在原始資料表，並以穩定的交易識別碼對應。
本文改寫自方格子的 SCAN 教學，將「空白列分段」改成依真正年份分組。若還不熟悉累加器，可先看 LAMBDA 實戰；想將達成率畫成小圖，再看 SPARKLINE 迷你圖表。
接下來可以
相關文章
查看所有文章拿掉輔助欄：用 MAP、SCAN、BYROW 重寫一張訂單表
一張手搖飲訂單表原本有小計、折扣、折扣後金額三個輔助欄，改用 MAP 搭配 LET 一條公式算完，再用 SCAN 做累計、用 BYROW 找每列最大值、用 SCAN 與 REDUCE 模擬補貨庫存。附何時還是留輔助欄的取捨。
LAMBDA 家族踩坑：BYCOL 方向、SCAN 初始值與陣列回傳的限制
整理 BYCOL、SCAN、MAKEARRAY、MAP、BYROW、REDUCE 常見的意外：結果橫向溢出被擋成 #REF!、SCAN 與 REDUCE 回傳長度不同、MAKEARRAY 從 1 起算、LAMBDA 回傳陣列、兩個範圍大小不一致，以及具名函式遞迴要怎麼寫結束條件。
SPARKLINE 迷你圖：在一格裡畫折線、長條與勝敗圖
用茶飲每週銷量示範 SPARKLINE 的四種圖表類型（line、column、winloss、bar），整理 charttype 與常用選項、把選項放在儲存格的寫法，以及用 REPT 湊長條的做法，另附極值著色、狀態進度與最新兩期變色練習。
留言討論
共 0 則留言