動態資料設計：資料變多，公式不用跟著改

你有過這種經驗嗎？每個月底結帳，都要打開試算表，把公式裡的範圍從「第 2 列到第 100 列」手動改成「第 2 列到第 135 列」，只因為這個月多了 35 筆訂單。

這種每個月都要回頭維護的公式，叫做硬編碼（Hard-coding），意思是把「會變的東西」直接寫死在公式裡。忘了改的話，報表不會報錯，只會安靜地少算。

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

圖：同樣 135 列資料，左邊那條公式少算了 35 筆，而且沒有任何錯誤提示。

公式漏掉新資料，但不會報錯

用小一點的資料手算一次。8 筆訂單放在 A2:B9，A 欄是訂單編號，B 欄是金額：

128 + 87 + 243 + 61 + 176 + 94 + 312 + 47 = 1148。

總額用 =SUM(B2:B9) 算，得到 1148，沒問題。接著下一週多了 3 筆：O-109 是 205、O-110 是 38、O-111 是 119，接在第 10 到 12 列。

我在 D2 放 =SUM(B2:B9)，D3 放 =SUM(B2:B)，來看看新增 3 筆之前與之後：

正確答案是 1148 + 205 + 38 + 119 = 1510。寫死到 B9 的那條還是 1148，少了 362，而且畫面上一切正常。

寫死的不只是範圍

跟家人學煮飯時，長輩常說「加兩碗水」。換了一個超大的碗，或今天想煮 10 人份，這個指令就失效了；改成「水跟米的比例是 1:1」，碗多大都成立。

你為了 9 點的課設了每天 07:30 的鬧鐘，國定假日照樣被吵醒。如果鬧鐘能連動行事曆，變成「第一堂課前 90 分鐘」，就不會有這種事。

試算表裡寫死的東西通常有三種：

後面照這三種順序走一遍。

範圍：算到資料結束為止

範圍的動態化有三條路，語法這篇不重講，各自有專門的文章：

• 開放式範圍：只寫起點、不寫終點，像上面的 B2:B。寫法與搭配 ARRAYFORMULA 的用法在 ARRAYFORMULA 入門。
• 表格：把資料轉成表格，新增一列時公式引用的範圍自動延伸，見 Google 試算表的「表格」功能。
• FILTER：結果跟著資料與條件重算，不必手動框範圍，見 FILTER 入門。

我自己的選法是，資料有欄名、而且常常新增，就先轉成表格；只是在別處加一個總計，開放式範圍就夠了；要依條件挑出一部分資料時才用 FILTER。

開放式範圍有一個要注意的地方：資料欄下面不要放總計。=SUM(B2:B) 會把底下的總計格自己也算進去，Sheets 會回報循環依附性的錯誤。總計放在欄位上方、旁邊，或另一個工作表。

另外我會在旁邊放一格對帳用的 =COUNTA(A2:A)。新增 3 筆之後它應該是 11，跟你預期的訂單數對得上，就知道範圍沒漏。資料量很大時整欄範圍會不會變慢，可以參考 計算效能的基礎認知，這篇不討論。

數字：改一格，全部更新

經營一家飲料店，有 5 種飲料，慶祝周年慶全部打 85 折（8.5 折）。

沒有參數化的話，每一種飲料的公式都寫 *0.85。下次想改成 8 折，你得手動改 5 次，漏了一個，那杯的定價就錯了。

參數化是把 0.85 放在一個固定的儲存格，所有公式都去讀它：

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

圖：五條公式都只認得「折扣率」那一格，要改折扣只動那一格。

我把價目表放在 A1:C6，A 欄是品項、B 欄是原價，旁邊 E1 放「折扣率」、F1 放 0.85。C2 這樣寫，往下拖到 C6：

$ 的意思放在 參照的藝術，這邊只要知道 $F$1 往下拖不會變。來看看結果：

F1 從 0.85 改成 0.8，C 欄五個數字一起變。

參數集中到「設定」工作表

參數多了以後，我會開一個專門的「設定」工作表，把折扣率、稅率、報表月份這些會變的值集中在那裡。公式前面多一個工作表名稱：=ROUND(B2*設定!$B$1,0)。工作表名稱是中文時，輸入完編輯列會自動補上單引號，變成 =ROUND(B2*'設定'!$B$1,0)，兩種寫法算出來一樣。

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

圖：參數都在同一頁，接手的人打開「設定」就知道哪些值可以調。

報表月份也是同樣的做法。回到價目表旁邊，F2 放 2026/10/01，標題這樣寫：

得到「銷售月報 2026 年 10 月」。下個月只要把 F2 改成 2026/11/01，標題就跟著換。我這邊用固定日期，不用 TODAY()，因為月報要的是「這份報表算的是哪個月」，不是今天幾號。

參數的值如果有來歷（為什麼是 0.85、誰決定的），在那一格加備註，寫法見 讓別人看得懂的試算表。想把 設定!$B$1 換成「折扣率」這種名字，可以看 設計可維護的試算表 的命名範圍。

動態換來什麼，失去什麼

開放式範圍換來不會漏資料，代價是別人看公式看不出「算到哪一列」，而且下面不能再放東西。參數化換來一處修改，代價是公式要跳到另一頁才看得到數值，參數越多越難追。

不是每份表都值得這樣做。我通常這樣判斷：

• 這份表會用超過三個月、資料會持續增加、或要分享給別人，就做動態設計。
• 只是回覆老闆一分鐘後要的臨時數字、一次性的草稿分析，寫死也無妨。

如果不確定，問自己一個問題：「N 個月後，我會不會需要手動改這個公式？」答案是會，就動態化。

答案是 137.3（1510 除以 11 是 137.2727...）。