Excel, SpreadSheet 試算表中的 Evaluation 實現方式
儲存格 Evaluation 的使用情境
自己的記帳習慣，為了能清楚記錄費用明細又不需要在備註上面打太多字，我通常不會直接在 Excel 記上最終的消費金額，而是記錄算式搭配簡單的說明。像是以下兩種情境：
- 匯款花了台幣 1014 元。如果直接寫 1014 元我事後無從得知這筆費用是否包含手續費。而如果寫的是
1000+14很清楚地知道這筆費用已涵蓋了手續費。 - 出差花了人民幣 100 元。如果寫的是
100*4.89，我們就可以很清楚看出當日匯率是 4.89，花的是人民幣不是台幣。
Excel 是支援儲存格內運算的，不過必須填入 =1000+14 而不是 1000+14。這樣的寫法就會讓算式不可讀，只剩下運算後的結果，每筆費用明細需要到各自的儲存格才能查看。
若能有公式幫忙做 Evaluation 同時顯示消費明細並且自動計算得出運算結果，可以大大提升記帳的效率，也不需要在備註中寫太多冗餘的文字。
按照這樣的思路尋找解決方法，原先以為只要找到對應的 Evaluation 函數就可以輕鬆地滿足我的需求，研究了一下沒想到事情沒有想像中的單純。最後還是靠萬能的 Google 才拼湊出相應的解決方案。或許也有人會有跟我相同的需求，索性寫篇文章分享也作為自己的備忘：
Excel 上的 Evaluation 方式
因為 Excel 內建就有這個函數，其實我們只要簡單的呼叫函數就可以，但是 Evaluation 函數呼叫的寫法沒辦法直接寫在儲存格 =Evaluate(A1) 這麼單純。
- 首先在「公式」的頁籤中定義名稱。翻譯可能有點混淆，這裡指的名稱是公式的名稱。
- 定義好名稱之後選定你的儲存格，作為
EVALUTE的函數參數輸入 - 在儲存格的內容填上剛剛定義好的公式名稱
=doMatch
Google SpreadSheet
如果是簡單的記帳，為了透過雲端快速同步，我們很可能不是用 Excel 來記錄。SpreadSheet 功能相較 Excel 又簡化這麼多，該怎麼辦呢？我們只能透過自己寫的 Google App Script 來擴充函數。幸好我們要的功能也不是特別複雜，在 StackOverflow 上面就有人提供程式碼的片段（看來這需求確實是存在的！）：
function doMath( formula ) {
// Strip leading "=" if there
if (formula.charAt(0) === '=')
formula = formula.substring(1);
return eval(formula)
}
步驟：
- 開啟指令編輯器
- 貼上程式碼
- 回到試算表中
- 直接使用剛剛在編輯器中定義好的函數
doMath
Google App Script 除錯方式
不過在我的試算表中，很多儲存格轉換仍然有問題，這時候就需要 debug Google App Script 了，Google App Script 提供原生的 Debugger 可以使用，但我們的其實是儲存格的函數，formula 實際上是儲存格內的物件，我們不知道型態是什麼，只知道每次總是會報錯，這時候就要依賴下面這段程式碼，讓它開啟工作表讀出對應的儲存格，並且透過 Logger 來記錄我們的除錯訊息。
function doMath( formula ) {
var sheet = SpreadsheetApp.openById('16D1n_JunTTSNeL0LkukrfYhf0Qm6vX8GuuoXztWODo0').getSheetByName('工作表1');
Logger.log(sheet.getRange('A1').getValue());
// Strip leading "=" if there
if (formula.charAt(0) === '=')
formula = formula.substring(1);
return eval(formula)
}
除錯訊息可以透過執行記錄看到：
透過訊息可以發現，其實問題就出在算式所在的儲存格，格式必須為純文字，否則就會出現 undefined charAt 的錯誤。