Excel, SpreadSheet 試算表中的 Evaluation 實現方式

儲存格 Evaluation 的使用情境

自己的記帳習慣,為了能清楚記錄費用明細又不需要在備註上面打太多字,我通常不會直接在 Excel 記上最終的消費金額,而是記錄算式搭配簡單的說明。像是以下兩種情境:

  1. 匯款花了台幣 1014 元。如果直接寫 1014 元我事後無從得知這筆費用是否包含手續費。而如果寫的是 1000+14 很清楚地知道這筆費用已涵蓋了手續費。
  2. 出差花了人民幣 100 元。如果寫的是 100*4.89,我們就可以很清楚看出當日匯率是 4.89,花的是人民幣不是台幣。

Excel 是支援儲存格內運算的,不過必須填入 =1000+14 而不是 1000+14。這樣的寫法就會讓算式不可讀,只剩下運算後的結果,每筆費用明細需要到各自的儲存格才能查看。
若能有公式幫忙做 Evaluation 同時顯示消費明細並且自動計算得出運算結果,可以大大提升記帳的效率,也不需要在備註中寫太多冗餘的文字。

按照這樣的思路尋找解決方法,原先以為只要找到對應的 Evaluation 函數就可以輕鬆地滿足我的需求,研究了一下沒想到事情沒有想像中的單純。最後還是靠萬能的 Google 才拼湊出相應的解決方案。或許也有人會有跟我相同的需求,索性寫篇文章分享也作為自己的備忘:

Excel 上的 Evaluation 方式

因為 Excel 內建就有這個函數,其實我們只要簡單的呼叫函數就可以,但是 Evaluation 函數呼叫的寫法沒辦法直接寫在儲存格 =Evaluate(A1) 這麼單純。

  1. 首先在「公式」的頁籤中定義名稱。翻譯可能有點混淆,這裡指的名稱是公式的名稱。
  2. 定義好名稱之後選定你的儲存格,作為 EVALUTE 的函數參數輸入
  3. 在儲存格的內容填上剛剛定義好的公式名稱 =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)
}

步驟:

  1. 開啟指令編輯器
  2. 貼上程式碼
  3. 回到試算表中
  4. 直接使用剛剛在編輯器中定義好的函數 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 的錯誤。