建立自動化工作流程：讓資料自己流動

每個月月初，我都要把上個月的銷售紀錄貼進報表、改日期範圍、再檢查哪一格忘了更新，半小時就這樣沒了。這篇要做的事很單純：新資料進來之後，清理、彙總、報表全部自己往下算，月初只需要改一個儲存格。

分層的道理在 三層架構 和 活動報名系統設計 講過了，這裡不重講，只談「資料怎麼自動流過去」。

← 左右滑動看完整的圖，點圖可放大

圖：資料只往右流，每一站只讀上一站，報表不會把東西寫回去。

四站，還是三層

上圖有四個方塊，但分層還是三層：原數據層、計算層、呈現層。我把計算層拆成「清理」和「彙總」兩站，因為資料從表單或別人手上進來，總是髒的。先洗乾淨再加總，數字不對的時候才知道是洗的那一站出錯，還是加的那一站。

規則只有一條：每一站只讀上一站，不往回寫。下面五張工作表的名字固定為「原始資料」「清理」「彙總」「設定」「報表」，全篇都用同一份資料：十二筆批發銷售紀錄，日期從 2026/5/6 到 2026/10/9。

表中的 · 代表一個空白。要注意的是，把表格用貼上的方式放進試算表時，Sheets 會自動把前後空白修掉，貼完就看不到 · 了。想重現這份髒資料，要在儲存格裡自己打空白，或是用公式產生，例如 =CHAR(32)&"A@Mail.com"&CHAR(32)、=" a@mail.com"。

清理層：整欄一次洗好

原始資料是人打的，客戶 Email 前後有空白、大小寫亂跳，日期是文字（靠左對齊的就是文字）。我在「清理」工作表的 A2 放第一個公式，把日期轉成真正的日期：

DATEVALUE 是「日期值」的意思，把文字日期轉成日期序號。算出來的是 46148 這種純數字，還不是看得出來的日期，所以要選取清理層的 A 欄，用「格式」>「數值」>「日期」設成日期格式。這一步不能省，後面彙總層的 QUERY 用 month() 取月份，這一欄如果還是沒設格式的純數字，會出現 #VALUE!。B2 洗 Email：

C2 把金額原樣帶下來，這樣下游只需要讀「清理」這一張：

這三個公式各放一格，之後原始資料不管新增幾列，清理層都會自己長出來。A1:C1 要先手打標題「日期」「客戶」「金額」，後面彙總的 QUERY 會把第一列當標題。

圖：右邊的日期欄已設成日期格式，靠右對齊，代表它是真的日期，後面的月份加總才算得對。（示意圖把清理結果放在同一張表的右側；照著做的話，清理結果在「清理」工作表的 A:C。）

洗過之後差在哪裡？我拿同一個客戶 a@mail.com 去加總，先看原始資料：

結果是 149500。再看清理層：

結果是 275600。同一個客戶，只因為前後的空白，原始資料少算了將近一半。

彙總層：按月加總

清理層乾淨了，彙總層用一條 QUERY 按月加總，放在「彙總」工作表的 A1：

month() 從 0 起算，所以加 1；再把年份乘 100，組成 YYYYMM 數字鍵，例如 2026 年 10 月是 202610。只按月份號分組會把不同年度的同月合併；這裡按年月分組並排序。跨年也不會混在一起：2026/12、2027/1、2027/12 分別是 202612、202701、202712。建圖時請將年月欄設為分類標籤，金額設為資料系列。

如果我在原始資料最後多貼一列「2026/10/10、b@mail.com、7300」，10 月會自己變成 162400，彙總層的公式一個字都不用改。

練習完請刪掉剛新增的那一列，還原前面的 12 筆基準資料，再繼續下方的圖表與設定頁。以下 10 月 155100、9 月 160900 都以還原後的資料為準。

在結果旁邊插入一張柱狀圖，月份的走勢一眼就看得出來：

圖：單年度走勢示意，X 軸是月份數字；本篇的資料表改用 YYYYMM 區分跨年同月，建立圖表時以年月欄作分類標籤。

設定頁：使用者只改這一頁

報表要看哪個月、要不要更新，我不想讓同事去改公式，所以放在「設定」工作表：B1 是核取方塊（「插入」>「核取方塊」），B2 是報表月份，填該月 1 號。

圖：整份試算表只有這兩格是讓人改的。

彙總層在 E1 讀這兩格，算出「報表月份的銷售額」：

結束條件用小於下個月 1 號，因此月底附帶時間的紀錄也會包含；小於等於月底日期則只到當天零點。報表月份填 2026/10/1，結果是 155100，改成 2026/9/1 就變成 160900。

B1 取消勾選的話，E1 會顯示「已暫停」。

報表月份也可以不用每月手動改。把 B2 換成這個公式：

假設今天是 2026/10/11，結果是 2026/9/1；跨年時，例如 2026/1/11，結果是 2025/12/1。它用到 TODAY()，所以隔月打開結果就變了，這正是我想要的，但想固定看某個月的話，就手動填日期。

另外在 E2 放一個「資料更新到哪天」，報表看起來才有交代：

結果是 2026/10/9。Sheets 沒有「這份資料最後同步時間」的公式，NOW() 只會顯示現在幾點，所以我用最新一筆資料的日期代替。

報表層只剩引用：「報表」工作表的 B1 放 =彙總!E1，B2 放 =彙總!E2，其他都是排版，公式裡沒有任何計算。

從表單到報表的自動鏈

把前面接起來，整條線長這樣：

← 左右滑動看完整的圖，點圖可放大

圖：人只負責最左邊那一步，後面每一站都是上游一變就跟著動。

表單的設定看 Google 表單與試算表整合。資料分散在好幾份試算表的話，匯入與合併的做法在 IMPORTRANGE 跨試算表同步，這篇不重複。

這個設計的代價也要講：每一站多一張工作表，資料在清理層重複存了一份，出問題要沿著鏈往上找幾站。換來的是月初不用再手動貼資料，我覺得值得。但資料只有幾十列、一個人用的話，直接在原始資料旁邊算就好，分站反而麻煩。

複製成下個月的範本

要給別的客戶或下個月用，我會從整份試算表開始：點「檔案」>「建立副本」，工作表之間的參照會一起帶過去。新檔裡只要改「設定」，並確認 IMPORTRANGE 重新按過「允許存取」。

只想帶走單張工作表的話，在工作表標籤上按右鍵，選「複製到」>「新的試算表」。

圖：單張工作表複製過去後，指向其他工作表的公式要自己檢查。

什麼時候不要這樣做

公式能做到的是「資料變了，下游跟著算」。如果你要的是每天早上八點自動寄信、定時把資料寫進別的檔案，或是每分鐘都要更新，那是 Apps Script 的工作，公式做不到。需要人判斷的事（退款核不核准）也別硬塞進公式。

月初改一格月份，剩下的交給公式。下一步可以試試把你手上最常重貼的那份報表，照這四站拆一次看看。