為什麼我的 TAX_PRICE 明明沒有報錯，同事填進去的金額卻是 270？具名函式只會乖乖照定義算，傳進去的東西對不對它不管。
建立函式的步驟在 具名函式怎麼建立，團隊怎麼共用在 具名函式實戰，這篇專門講會踩到的地方。後面的例子我都用等價的 LAMBDA 直接呼叫來展示邏輯，不逐一示範面板操作，具名函式的欄位怎麼填就是把 LAMBDA 拆開，這個對應在入門篇講過。
沒報錯，但答案是錯的
先看沒有任何驗證的 TAX_PRICE，公式定義是 。稅率應該填 0.05，同事習慣填 5：
稅率誤填 5 而不是 0.05，沒有任何錯誤提示
=LAMBDA(price, rate, ROUND(price*(1+rate), 0))(45, 5)手算：45 × (1 + 5) = 270。公式沒有報錯，儲存格就乖乖顯示 270，整份報表的含稅價都變成六倍，而且沒人看得出來。
單價如果被填成文字，狀況不一樣：
單價傳入文字，乘法直接報錯
=LAMBDA(price, rate, ROUND(price*(1+rate), 0))("abc", 0.05)你應該會得到 #VALUE!。至少這個看得到，前一個才是真的麻煩。
引數驗證
在函式定義裡先檢查輸入，不對就回自己寫的訊息。先用 ISNUMBER 擋掉文字和空白：
單價不是數字就回提示，否則算含稅價，稅率固定 0.05
=LAMBDA(price, IF(ISNUMBER(price), price*1.05, "請輸入數字"))(45)單價 45 的結果是 47.25。換成文字或空白儲存格：
傳入文字，回傳自訂訊息
=LAMBDA(price, IF(ISNUMBER(price), price*1.05, "請輸入數字"))("abc")得到「請輸入數字」。空白儲存格也一樣：ISNUMBER 對空白回傳 FALSE，所以空白格會回「請輸入數字」，不會偷偷當成 0 去算。
有一個小地方要提：看起來是數字的文字，例如從別的系統貼過來、靠左對齊的「45」，ISNUMBER 也是 FALSE：
文字的 45 不是數字
=LAMBDA(x, ISNUMBER(x))("45")結果是 FALSE。這時候驗證訊息會擋下它，這是好事，不然你要一直追為什麼有的列算得出來、有的不行。
範圍也要檢查
前面稅率填 5 的問題，用範圍檢查擋：稅率只能在 0 到 1 之間。兩個引數都要驗，就巢狀兩層 IF：
先檢查單價，再檢查稅率，都過關才計算
=LAMBDA(
price, rate,
IF(
NOT(ISNUMBER(price)),
"單價請輸入數字",
IF(
OR(NOT(ISNUMBER(rate)), rate<0, rate>1),
"稅率請填 0 到 1 之間",
ROUND(price*(1+rate), 0)
)
)
)(
45,
5
)結果是「稅率請填 0 到 1 之間」，不再是靜悄悄的 270。把 5 換成 0.05，同一條公式的結果是 47。
巢狀 IF 超過兩層會開始難讀，那個時候我會改用 IFS，寫法可以參考 巢狀 IF 與 AND／OR 組合的陷阱。
回文字還是回錯誤
自訂訊息有個副作用：它是文字，不是錯誤。把一欄結果加總，文字會被 SUM 默默略過：
| E | |
|---|---|
| 2 | 47.25 |
| 3 | 請輸入數字 |
| 4 | 47.25 |
E2 是單價 45 的結果，E3 是文字「abc」被擋下的訊息。E4 放 ，結果是 47.25，少算的那一列沒有任何警告。
如果你希望壞資料讓整份報表跟著出錯，被看到，就回傳真正的錯誤，例如用 NA()：
驗證失敗時回傳 #N/A，而不是文字
=LAMBDA(price, IF(ISNUMBER(price), price*1.05, NA()))("abc")結果是 #N/A，這時 SUM 也會跟著變成 #N/A，錯誤不會被吞掉。我個人的取捨：給人看的明細欄用文字訊息，有人會加總的欄位回錯誤，需要的時候外面再用 IFERROR 接。
匯入後要重新匯入才會更新
在 函式庫實戰 提過，其他試算表用「匯入函式」取得函式。Google 說明中心沒有寫匯入後是複本還是連結，所以維護時請當作複本來處理：
- 函式庫裡改了公式定義，要當作已經匯入的試算表不會跟著變。
- 要更新，得在那份試算表再匯入一次。因為說明中心寫，同名函式匯入時會取代原本的，所以重新匯入就是更新。
- 反過來，在匯入後的試算表裡直接改函式，下次匯入同名函式時會被函式庫的版本取代。
我的做法：改函式只在函式庫改，再通知大家重新匯入。
名稱衝突
Google 說明中心整理的規則，撞名的情況不同，結果也不同：
- 跟內建函式同名，例如
SUM：不能建立。 - 跟已有的具名函式同名：用匯入的話，新的會取代舊的。所以兩份函式庫有同名函式時，後匯入的贏。
- 跟具名範圍同名：具名範圍優先。你寫
TAX_PRICE，如果有人先把某個範圍取名叫TAX_PRICE，那個範圍會蓋過函式。 - 跟 Apps Script 自訂函式同名：建立順序有差。依 官方 FAQ，具名函式不能和 Apps Script 自訂函式同名；若先有具名函式，之後新增同名的 Apps Script 函式，具名函式優先。
- 引數預留位置跟內建函式同名：可以，但兩個一起用時，預留位置優先。
最後這條我個人會直接避開。引數取名叫 round，公式定義裡又要呼叫 ROUND，我不想花時間確認哪一個被當成哪一個，改叫 amount 就沒事了。
具名函式裡呼叫另一個具名函式
函式可以呼叫別的具名函式。像我想在報表上顯示「含稅 58 元」，在 TAX_PRICE 外面包一層 PRICE_LABEL，公式定義是：
公式定義：在具名函式 TAX_PRICE 外面包一層文字
="含稅 "&TAX_PRICE(price, 0.05)&" 元"用 LAMBDA 把兩層都攤開驗算，單價 55：
等價的 LAMBDA 寫法，內層是 TAX_PRICE 的定義
=LAMBDA(price, "含稅 "&LAMBDA(p, r, ROUND(p*(1+r), 0))(price, 0.05)&" 元")(55)結果是「含稅 58 元」。55 × 1.05 = 57.75，四捨五入是 58。
這樣做有一個維護上的代價：PRICE_LABEL 依賴 TAX_PRICE，所以：
- 匯入
PRICE_LABEL時，Sheets 會警告並標出它依賴的函式。要把TAX_PRICE一起選進去，不然匯入後的PRICE_LABEL找不到內層。 - 刪掉或改名
TAX_PRICE，外層的函式也會壞。
層數不要疊太深，我自己是兩層就停。
遞迴
具名函式也可以呼叫它自己。舉例來說，我想把一個數字的每一位數加起來：3705 就是 3 + 7 + 0 + 5 = 15。每次拿掉最後一位，重複同一件事，直到只剩一位數。
具名函式 DIGIT_SUM 的公式定義是：
公式定義：個位數就直接回傳，否則個位加上其餘位數的總和
=IF(n<10, n, MOD(n,10)+DIGIT_SUM(INT(n/10)))n<10：只剩一位數，這是結束條件。MOD(n,10)：取出最後一位。INT(n/10)：去掉最後一位，交給自己再算一次。
沒有名字的 LAMBDA 沒辦法呼叫自己，所以直接驗算要多一個小技巧：把 LAMBDA 自己當作引數傳進去。這個寫法比較燒腦，只拿來驗證邏輯，真正使用時寫上面那個具名函式就好：
驗算用：把 LAMBDA 本身當引數 f 傳進去，f(f, ...) 就是呼叫自己
=LAMBDA(
f, n,
f(f, n)
)(
LAMBDA(f, n, IF(n<10, n, MOD(n, 10)+f(f, INT(n/10)))),
3705
)結果是 15。
遞迴最怕忘記結束條件。Google 說明中心提到，遞迴公式如果執行太久或用掉太多記憶體，會出現「Calculation limit was reached」的錯誤，解法是簡化公式。
遞迴的函式還有一個陷阱：每多一層就多算一次，資料大的時候會慢。大多數需要重複的情況，用 MAP 與 REDUCE 就夠，我會先試它們，真的需要才寫遞迴。
刪除函式之前
在面板刪掉一個具名函式之後，用到它的儲存格就找不到這個函式了，Google 說明中心沒有寫會顯示哪一種錯誤。所以刪除前先做兩件事：
- 用「尋找並取代」搜尋函式名稱，確認有沒有儲存格還在用。
- 其他試算表如果匯入過這個函式，誰還在用要先問清楚，刪除前先通知大家。
練習
替折扣率加上驗證
DISCOUNT_PRICE 的公式定義是 price*(1-off)，折扣率 off 應該填 0.12 這種 0 到 1 之間的小數。同事把它填成 12，結果變成負數。請加上驗證：off 不是數字，或不在 0 到 1 之間，就回傳「折扣請填 0 到 1 之間」；其他情況照算。用 LAMBDA 直接呼叫驗算，單價 45、折扣 12 會得到什麼？折扣 0.12 呢？
建議每個會被別人用到的函式，至少先加最基本的 ISNUMBER 檢查再分享出去。
我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章留言討論
共 0 則留言