設計可維護的試算表：讓三個月後的自己也看得懂

三個月前我做了一份批發訂單的折扣表，今天老闆說會員折扣要從 10% 改成 20%。我打開檔案：分頁叫「工作表1」，折扣率寫在公式裡，還有一整欄的黃色底色，沒有人記得黃色代表什麼。

這篇拿同一份折扣表，一步一步改成三個月後還改得動的樣子。

圖：改造前。折扣率和門檻寫死在每一條公式裡，黃色、橘色、粉紅色各代表什麼，只有當時的我知道。

改造後的結構是下面這張，資料只往右流，不回頭改上游：

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

圖：全站通用的三層圖。這篇的折扣表會一格一格對應到這三層。

三層各管什麼，三層架構入門 已經講過，這邊直接動手。

改造前：折扣寫在每一條公式裡

先看資料。6 筆訂單，A 到 D 欄是手打的：

規則是金額滿 1200 才打折，會員 10% 折，一般 5% 折。E 欄原本這樣寫：

這條公式算得沒錯，6 列的結果就是上表最右欄。麻煩在老闆要改折扣的時候：0.1 寫在每一列的公式裡，真實的訂單檔有幾百列，你得全部找出來改。用「尋找並取代」把 0.1 換成 0.2，剛好也是 0.1 的別的數字會一起被換掉，而且不會有任何提示。

來看怎麼改。

先把資料、計算、報表分開

我把這份檔案拆成四張工作表，用下圖的方向連起來：

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

圖：藍灰色是人會去填的，淡綠色是公式算出來的。想改折扣率，只動「設定」那一頁。

命名我用「層級前綴 + 底線 + 內容」：原數據_、計算_、報表_。打開檔案看分頁列，不用點進去就知道這張是原始資料還是公式。設定 放的是人會去調的值，跟原數據一樣是手打的，所以我沒有幫它加前綴。

讓別人看得懂的試算表 的例子在前綴前面多加了 01_ 這種數字，用哪一種都可以，重點是同一個檔案只用一種。Google 試算表不會依名稱自動排序分頁，順序要自己拖曳。

原數據層的原則跟三層架構那篇一樣：筆誤直接在原數據改，因為那是在修正事實。但「O-0414 退貨，不算」「這筆算到下個月」這類算法上的決定，不要動原數據，放到計算層去處理。原數據留著整筆訂單，以後有人問「那天到底收了幾筆」才答得出來。

把折扣率搬到「設定」並取名字

數字為什麼要從公式搬到一格，動態資料設計 講過了。我直接照做，在 設定 工作表放三個參數：

公式如果寫 設定!$B$3，三個月後的你還是得跳去那一頁，才知道 B3 是什麼。取個名字就不用跳了：

1. 選取 設定 的 B2（0.05 那格）。
2. 點「資料」>「已命名範圍」，畫面右邊會跳出面板，按「+ 新增範圍」。
3. 名稱填 折扣_一般，按「完成」。
4. B3 取名 折扣_會員，B4 取名 門檻金額。

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

圖：取好的名字會列在右側面板，點一下就知道它指向哪一格。

之後公式裡直接寫名字。折扣的算法會在下一節放進 計算_折扣，這邊先看寫法的差別：設定!$B$3 變成 折扣_會員，一眼就知道這是會員折扣。

這個做法也有代價：名字指向哪一格，要打開右側面板才查得到；把命名範圍刪掉的話，用到它的公式會顯示 #NAME?。所以我只替會被很多公式用到的參數取名字，一兩個地方用到的就直接指向儲存格。

把巢狀 IF 拆成三欄

改造前的 IF 套了兩層。我拆成三個欄位，每欄只做一件事。計算_折扣 的 A 到 D 欄：

來看看結果：

數字跟改造前的 E 欄一模一樣，差別是哪一筆沒打折、為什麼，現在看欄位就知道。O-0413 金額 460 沒到 1200，B 欄就寫「否」。

最後一步是合計。我在 計算_折扣 的 F1 寫「折後合計」，F2 這樣寫：

結果是 9060。原數據的金額加起來是 9700，所以這個月折掉 640。報表_月報 的 B1 只做參照：

現在回答老闆的問題：把 設定 的 B3 從 0.1 改成 0.2，計算_折扣 的 O-0412 變成 1024，O-0414 變成 2520，報表的合計從 9060 變成 8617。公式一個字都沒動。

看完這個結果，請把 設定!B3（折扣_會員）從 0.2 改回 0.1，O-0412 會回到 1152、合計回到 9060。後面的 LET 範例與練習都以 0.1 為準。

想一格寫完，用 LET

三個欄位的缺點是欄位多。如果你覺得中間值不需要留下來，LET 可以在一條公式裡幫中間結果取名字：

LET 的語法是「名字, 值, 名字, 值, ⋯⋯, 最後的算式」，前面每一組都是在取名字，最後一個才是結果。這條對 O-0412 會得到 1152，跟上面三欄的結果一樣。

我個人的取捨是：自己用的表、公式不長，我用 LET；以後要給別人接手、或出了錯要一欄一欄對的表，我留三個欄位，因為中間值看得到，除錯快很多。LET 把中間值藏在公式裡，想看某一步算出什麼，得自己拆開。

LET 的效能問題可以看 計算效能的基礎認知，這篇只談好不好讀。

底色讓人知道哪格能動

四種底色，全站用同一套。這份折扣表套下去是這樣：

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

圖：圖例本身放在 README 或第一頁，旁邊貼一小段套用後的樣子。

• 輸入（藍灰底）：原數據_訂單 的 A 到 D 欄，以及 設定 的三個數值。這些是人填的。
• 公式（淡綠底）：計算_折扣 全部，還有 報表_月報 的 B1。看到淡綠色就不要手改。
• 待確認（米黃底）：還有疑問的資料。比方說某筆訂單的客戶等級還沒問清楚，先塗米黃色。
• 暫不用（灰底）：保留著但現在不參與計算的格子。

參數的值也是人填的，所以用輸入色，不另外替它找第五種顏色。哪格是參數，靠 設定 這個分頁名稱和命名範圍分辨。

顏色只是輔助。「達門檻」我用「是／否」文字寫出來，因為顏色不能被公式讀取，色弱的同事也可能分不出來，文字才是真正的資訊。標題列凍結、分頁標籤顏色、備註和 README 怎麼做，在 讓別人看得懂的試算表 一次講完，這邊不重複。

新增資料，還有換人接手

新增一筆訂單，只做兩件事：把新的一列貼到 原數據_訂單 最下面，再把 計算_折扣 的 A 到 D 欄往下拖一列。想讓公式自己長出來的話，看 ARRAYFORMULA 入門。F2 的 SUM(D2:D) 用的是開放式範圍，新增列不用改，這一點在 動態資料設計 有說明。

換人接手，我希望對方只看兩個地方：第一頁的圖例，和 設定。要調折扣去 設定，要查數字怎麼來的，沿著 報表_月報 找 計算_折扣，再找到 原數據_訂單。排查的順序跟三層架構那篇一樣，由報表往上游一層一層查。

什麼時候不用拆這麼細

這個設計換來改折扣只動一格、查錯有固定路線，代價是分頁變多，公式要跳著追，命名範圍也多一份要維護的清單。

如果表只有你自己用、資料幾十筆、算完就丟，一個分頁加一格折扣率就夠了。等到第二個人要改資料、這份表會用超過一個月，再拆。多人一起改的時候，誰能動哪一頁，接著看 協作權限管理。

找一份你三個月前做的表，先把藏在公式裡的數字搬進設定頁吧！