關於Excel字串公式回傳值#VALUE!探討
最近剛好要處理學生的讀卡成績，因為讀卡機附的多選題的計分方式不符合我的需求，想要利用Excel來處理，以往Excel2003都是自己寫一個字串處理函數來處理，這次想直接利用Excel2007提供的字串函數來進行處理多選題評分，舉例下表
評分的方式是標準答案如果是AC，則學生的答案只要沒有超過兩項，答對1項給一半的分數，例如學生回答AB則得1分，在工作表中C2資料格建立以下公式:
=IF(B2=B3,2,IF(SEARCH(MID(B3,1,1),B2,1)>0,1,IF( SEARCH(MID(B3,2,1),B2,1)>0,1,0)))
但是如果將學生答案改為BC，則得分變成
這個#VALUE!在Excel中代表是公式錯誤的回傳值，可是我們公式的內容邏輯並沒有錯，把各部分的公式拆開檢查其計算值，才發現Excel 字串公式在某些方面跟直覺的想法是不太一樣，例如SEARCH這個函數可以用來搜尋一個字串在另一個字串的位置，一般我們會想如果這個字串不在搜尋的字串中，應該會傳回0，可是它是傳回#VALUE!，而這個值代表的是錯誤值，所以後面的公式無法正常運作，最後產生的值還是#VALUE!。
如果要得到正確的結果必須把上述的公式改成：
=IF(B2=B3,2,IF(IFERROR(SEARCH(MID(B3,1,1),B2,1),0)>0,1,IF( IFERROR(SEARCH(MID(B3,2,1),0)>0,1,0)))
這樣才能得到正確的計算值，也就是每一個公式必須進行IFERROR的檢查，不過這樣的公式變得太複雜，看樣子不能用這個為處理方案。
歡迎引用，請注明來源出處!