你手上大概也有過這種資料夾：每辦一場活動就開一個新檔，「2024春_報名.xlsx」「報名_改_最終2.xlsx」「付款追蹤_舊.xlsx」，活動結束後想查去年誰來過，翻了十分鐘還是找不到。報名人數靠眼睛數，付款狀態記在另一個檔，誰是候補只有負責人腦袋裡知道。
這篇用「活動報名」當例子，把這團東西整成一份試算表。
← 左右滑動看完整的圖，點圖可放大
圖：目標是左邊三個檔變成右邊一份，而且每個人只需要打開同一個地方。
六張工作表，一條資料流
整份試算表我拆成六張工作表，下圖是資料怎麼流動的：
← 左右滑動看完整的圖，點圖可放大
圖：活動總覽與付款追蹤要手填；報名確認也要人工填取消標記、延伸公式。這三張都有日常維護工作。
我先講原則，後面每一步都照這張圖走：
- 原始資料自己一張，不在上面改任何東西，源頭才不會被不小心動到。
- 「活動總覽」要填活動資料，「付款追蹤」要更新收款，「報名確認」E 欄要維護取消狀態；新增報名時還要把 A 到 D 欄公式往下延伸，並核對新列已進入統計。
- 統計儀表板只負責顯示，不存任何原始資料：統計公式算在「活動總覽」，儀表板只引用結果。
這個切法跟 三層架構 是同一個想法：資料、計算、輸出分開放。這裡不重講，直接動手。
先把表單回應拉進來
報名一律走 Google 表單，回應會寫進表單自己的「回應試算表」。我用 IMPORTRANGE 把那份資料拉進主系統的「報名原始資料」，貼在 A1：
把 FORM_SHEET_ID 換成回應試算表網址中的 ID，工作表名稱依你的回應分頁而定
=IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/FORM_SHEET_ID",
"表單回應 1!A:H"
)我的表單前三欄是時間戳記、活動、報名者，後面五欄是其他問題，所以這份資料下面都用 A 到 C 欄。
這組來源檔與目標檔若尚未連結，輸入後通常會先顯示 #REF!。依官方流程：
- 由對來源有存取權、且能編輯目標檔的人操作，先確認來源網址與工作表名稱。
- 在目標檔輸入上述公式，稍候後選取錯誤格。
- 若訊息要求連結這兩份試算表，按「允許存取」；若訊息說沒有來源權限，先取得來源存取權，不能只重按按鈕。
- 確認標題與資料已展開，再檢查後續公式。同一來源與目標已連結時，不必為每條公式重按，也不必所有管理員各授權一次。
授權前先確認目標檔的編輯者都適合接觸來源資料：連結完成後，他們可以匯入來源的任何範圍，不只當前公式的欄位。發布報名系統前先完成連結與結果核對，避免下游讀到尚未授權的錯誤。
另一條路是直接在表單的「回應」分頁把回應連結到這份主試算表，這樣就不需要 IMPORTRANGE。我仍然選用 IMPORTRANGE，因為回應試算表單獨放著，表單改版或換人負責時，不會動到主系統。你只有一份表單的話，直接連結比較省事。
報名確認：用累計筆數判斷已確認或候補
「報名確認」是整個系統的主表。A 到 C 欄我用 這種直接引用，往下拖到資料最後一列。D 欄是「狀態」，E 欄是「管理員標記」，只有 E 欄是手動填的。
名額放在「活動總覽」，A 欄活動、B 欄活動日、C 欄名額：
| 活動 | 活動日 | 名額 |
|---|---|---|
| 讀書會 A | 2026/11/14 | 3 |
| 攝影入門 | 2026/11/21 | 12 |
名額 3 是為了讓候補早點出現，實際活動當然不會這麼小。
「付款追蹤」是另一張手動表，一筆報名一列，收到錢時才更新。我只放四欄：A 欄活動、B 欄報名者、C 欄金額、D 欄付款狀態（「已付款」或「未付款」）。例如：
| 活動 | 報名者 | 金額 | 付款狀態 |
|---|---|---|---|
| 讀書會 A | 報名者 01 | 300 | 已付款 |
| 讀書會 A | 報名者 02 | 300 | 未付款 |
| 攝影入門 | 報名者 06 | 800 | 已付款 |
活動與報名者的寫法要和「報名確認」一致。本例的「報名者 01」等文字視為不重複的示意報名識別碼；實務應另存穩定的報名 ID 與姓名，不能只靠姓名辨認同一筆報名。同一 ID 有重複回應或互相矛盾的狀態時，先查明哪筆有效，未釐清前不要拿儀表板比例做決策。
D2 這樣寫，往下拖：
E 欄標了「取消」的列直接顯示取消；其他列計算「從第 2 列到這一列、同一活動、沒被取消」的累計筆數，不超過名額就是已確認，超過就是候補
=IF(
E2="取消",
"取消",
IF(
COUNTIFS($B$2:B2, B2, $E$2:E2, "<>取消")<=VLOOKUP(
B2,
活動總覽!$A$2:$C$30,
3,
FALSE
),
"已確認",
"候補"
)
)重點是 $B$2:B2：起點鎖住、終點不鎖，往下拖時範圍會一格一格變長，所以每一列算的都是「到我這一列為止」的人數，也就是報名先後順序。
假設「讀書會 A」有 5 筆報名，「攝影入門」有 1 筆，D 欄應該得到：
| 報名時間 | 活動 | 報名者 | 狀態 |
|---|---|---|---|
| 2026/10/02 | 讀書會 A | 報名者 01 | 已確認 |
| 2026/10/02 | 讀書會 A | 報名者 02 | 已確認 |
| 2026/10/03 | 讀書會 A | 報名者 03 | 已確認 |
| 2026/10/04 | 讀書會 A | 報名者 04 | 候補 |
| 2026/10/05 | 讀書會 A | 報名者 05 | 候補 |
| 2026/10/05 | 攝影入門 | 報名者 06 | 已確認 |
我另外加了條件式格式，已確認淡綠、候補淡灰，掃一眼就知道誰在候補：
← 左右滑動看完整的圖，點圖可放大
圖：狀態欄靠公式跑，只有 E 欄的「取消」是人填的。
D 欄是公式算出來的，資料驗證只能管「手動輸入」的內容，對公式結果沒作用。上面這條公式會把超過名額的報名標成「候補」，不會阻止表單繼續收件，也不會替你發送確認或候補通知。資料驗證留給 E 欄：選取 E2:E500，「資料」>「資料驗證」設成下拉選單，只能選「取消」或留空，避免有人打成「已取消」，公式就認不得了。
有人取消，候補自動遞補
遞補不用任何額外動作。管理員把報名者 02 在 E 欄選成「取消」之後，D 欄整排重算：
← 左右滑動看完整的圖，點圖可放大
圖：取消的那一列退出累計，後面的列往前補一位，最早的候補自動變成已確認。
手算一次：取消前報名者 04 是第 4 位，4 大於名額 3 所以候補；報名者 02 取消後，沒被取消的累計筆數變成 01、03、04 共 3 筆，3 沒有超過 3，報名者 04 就變成已確認。報名者 05 累計 4 筆，仍是候補。
E 欄的「取消」跟著列位置走。不要對「報名原始資料」或「報名確認」做排序，資料一換位置，取消標記就會對到別人頭上。要看別種排列，用 SORT 另外生一份。
候補名單與統計儀表板
候補名單不用手抄，直接用 FILTER 從「報名確認」撈出狀態是「候補」的列，放在「候補名單」的 A2：
撈出候補者的活動與報名者，順序就是遞補順序；沒有候補時顯示文字，不顯示 #N/A
=IFERROR(FILTER(報名確認!B2:C500, 報名確認!D2:D500="候補"), "目前沒有候補")照上面的資料，會看到「讀書會 A」與「報名者 04」「報名者 05」兩列；報名者 02 取消後，只剩報名者 05 一列。
「活動總覽」A 到 C 欄是手填的活動設定，D 欄放「已報名」，E 欄放「剩餘名額」，這兩欄是公式，D2 與 E2 是：
本場活動狀態為已確認的筆數，範圍寫到 500 列，比整欄好控制
=COUNTIFS(報名確認!$B$2:$B$500, A2, 報名確認!$D$2:$D$500, "已確認")名額減已報名，就是剩餘名額
=C2-D2「讀書會 A」會是 3 與 0，「攝影入門」是 1 與 11。
第六張「統計儀表板」不放任何公式計算，只用 這樣的直接引用，把活動、名額、已報名、剩餘名額與付款完成率（「活動總覽」的 A、C、D、E、F 欄）往右往下拖出來。活動多起來之後，再在這張表放一張各活動報名狀況的堆疊長條圖，已確認與候補一起看，一眼就知道哪場滿了、哪場候補排得長：
圖：攝影入門的候補人數比其他場都多，下次可以考慮加開一場。
圖中五場活動的人數是另外填的示範數字，不是上面那 6 筆。付款完成率則必須先限定同一個母體：
- 分母：本活動目前有效、狀態為「已確認」的不重複報名 ID 數。
- 分子：上述 ID 當中，付款狀態已核對為「已付款」的 ID 數；同一 ID 有多筆付款紀錄也只算一次。
- 已取消與仍在候補的報名，不論是否先付款，都不列入這個確認名單付款率；退款與候補款另行追蹤，不能直接當成有效已確認款。
- 沒有已確認報名時顯示「—／尚無已確認報名」，不除以零，也不解讀成 0% 已付款。
直接數「付款追蹤」全部已付款列，可能把取消、候補或重複紀錄算進分子。請先逐 ID 與目前確認名單核對，不能只因比率看起來正常就當成帳已對好。
在「活動總覽」的 F1 寫「付款完成率」，F2 輸入下式，往下填滿各活動，再將 F 欄設為百分比：
先取本活動非空、已確認的不重複報名ID，再計這些ID當中已付款的比例；沒有已確認者顯示—
=IF(
COUNTIFS(
報名確認!$B$2:$B$500, A2,
報名確認!$D$2:$D$500, "已確認",
報名確認!$C$2:$C$500, "<>"
)=0,
"—",
LET(
ids, UNIQUE(FILTER(
報名確認!$C$2:$C$500,
報名確認!$B$2:$B$500=A2,
報名確認!$D$2:$D$500="已確認",
報名確認!$C$2:$C$500<>""
)),
SUM(MAP(ids, LAMBDA(regId,
IF(COUNTIFS(
付款追蹤!$A$2:$A$500, A2,
付款追蹤!$B$2:$B$500, regId,
付款追蹤!$D$2:$D$500, "已付款"
)>0, 1, 0)
)))/ROWS(ids)
)
)本例沿用「報名確認」C 欄與「付款追蹤」B 欄作為同一個示意 ID；實務若將 ID 獨立在別欄，兩處都要改成同一 ID 欄。ids 不含空白、取消或候補，付款紀錄重複也只回 1；範圍只預留到第 500 列，超過後必須同步延伸相關公式。
這個公式只認目前有效的「已付款」標記，不能代替核對金額或退款。舊付款列若已退款，要先更新有效付款狀態；同一 ID 狀態衝突要人工釐清。前面 D2 的人數公式數的是列，仍要求名單一個 ID 只有一筆有效報名，不能因付款率有去重就忽略重複名單。
這條付款完成率公式用範例資料跑過：取消已付款與候補先付款的情況各為 50%、重複付款紀錄為 2/3、零確認顯示「—」。
檔案太大或要分權限時再拆
上面這套全部放在一份試算表。我不會一開始就拆成多個檔，因為拆開之後每組來源與目標檔的首次連結都要授權，出錯時也得開好幾個檔才找得到原因。
試算表的儲存格上限是 2,000 萬格（2026 年 9 月從 1,000 萬加倍）。以每場 100 人、每人 8 欄、一年 20 場估算，也才 16,000 格，離上限很遠。真正會逼你拆的通常是這兩件事：
- 不同部門要看不同的資料，而同一份試算表很難把權限切得這麼細。
- 開檔或重算已經慢到影響工作。這時先看 試算表為什麼越來越慢 和 計算設定導覽，很多時候改一下範圍寫法就好，不一定要拆。
真的要拆，我用 Hub-and-Spoke（輪轂與輻條）：
← 左右滑動看完整的圖，點圖可放大
圖：各部門只改自己的檔，中央彙總只有管理員能動，報表檔開放給大家看。
這個做法換來權限乾淨，部門改自己檔案的格式也不會影響別人。代價是每組「來源檔 → 目標檔」首次連結要授權，授權的人得有來源檔的存取權與目標檔編輯權；同組檔案不是逐公式授權，而且跨檔出錯時，你得一個檔一個檔往上追。
新增活動與交接
之後日常只動三個地方：開新活動時在「活動總覽」加一列；收到款項時在「付款追蹤」更新狀態；有人取消時在「報名確認」的 E 欄選「取消」。新的報名進來後，核對新增筆數，把「報名確認」A 到 D 欄最後一列往下延伸同樣筆數；E 欄保持對應報名者的人工標記，確認新列已有狀態再查看統計。一次進來多筆，不是只拖一列。
上線前我會先檢查四件事：
- 逐組核對來源與目標檔連結，由合適的授權者完成首次「允許存取」，確認其他目標檔編輯者的存取範圍；不要求所有管理員各按一次。授權者離開來源檔後，連結可能失效，交接時要重新確認。
- 測試用的假資料清掉。
- 「報名原始資料」和「活動總覽」設成保護範圍，只有管理員能改。活動總覽的 D 到 F 欄是公式，會一起被鎖，所以新增活動要請管理員處理。
- 新增一張 README 工作表，寫每張工作表的用途與上面這幾個日常操作，換人接手就看這一頁。
試著先用 6 筆假資料把候補跑一遍吧，跑通了再把真的報名開放出去。
接下來可以
這篇在技能樹的 Lv.5「系統設計與最佳實踐」，模組「完整系統設計案例」（第 1／1 篇）。讀完這篇，路線上的下一個模組是「建立自動化工作流程」。
回到技能樹相關文章
查看所有文章設計可維護的試算表：讓三個月後的自己也看得懂
用一份批發折扣表示範怎麼改造：資料、計算、報表分成三類分頁並加前綴，折扣率搬到設定頁再取名字，巢狀 IF 拆成三欄或改用 LET，最後用統一的底色標出哪格能動。
三層架構入門：把原數據、計算、呈現分到三個分頁
用一份茶飲店月報當例子，示範怎麼把試算表拆成原數據層、計算層、呈現層三個分頁，改一格月份整份報表跟著換，數字不對時也知道從哪一層開始查。
IMPORTRANGE 跨試算表同步：授權、匯入與合併
用 IMPORTRANGE 把另一份試算表的資料匯進來：從網址取得 ID、第一次按「允許存取」、範圍的寫法、把好幾份檔案疊成一張，以及授權跟權限的關係。
留言討論
共 0 則留言