錯誤成因
#VALUE! 表示公式中的 參數型別或運算元型別錯誤。常見情況:
- 期望數值卻提供文字:
="文字"+10、=A1*B1 (A1 為文字)。
- 期望邏輯值卻提供文字:
=IF("文字", 1, 0)。
- 日期時間運算對象為文字格式日期。
- 陣列公式未按 Ctrl+Shift+Enter (CSE) 確認(舊版 Excel)。
- 函數參數數量/型別不符:
=LEFT(123, 1)(第一參數需為文字)。
三大解決方案
方案一:修正資料型別(根治)
- 儲存格綠色三角 → 點選圖示 → 「轉換為數值」。
- 資料 → 分欄 → 完成(快速批次轉換整欄)。
- 公式法:
=VALUE(A1)、=A1*1、=--A1。
- 文字轉日期:
=DATEVALUE(A1)、=--A1、分欄→日期格式。
- 移除不可見字元:TRIM()、CLEAN()、SUBSTITUTE(A1, CHAR(160), "")(不中斷空格)。
方案二:修正公式邏輯(就地修正)
- 為函數參數加入型別轉換:
=SUM(VALUE(A1:A10)) (CSE) 或 =SUMPRODUCT(--(A1:A10))。
- 用 IFERROR/IF 包裹:
=IFERROR(原公式, 替代值)。
- 修正運算子:文字連結用
& 不用 +。
方案三:陣列公式正確確認 (舊版 Excel)
- 選取含陣列公式的儲存格/區域。
- 按 F2 進入編輯模式(大括號消失)。
- 按 Ctrl+Shift+Enter (CSE) 確認,大括號重現。
※ Excel 365/2021+ 動態陣列自動濾溢,無需 CSE。
快速定位 #VALUE! 來源
- 「公式」 → 「公式稽核」 → 「錯誤檢查」 → 逐個定位。
- 條件格式:新規則 → 「公式」
=ISERROR(A1) → 設定紅底白字,視覺化標記所有錯誤儲存格。
- 尋找 (Ctrl+F) → 「選項」 → 「查看公式」 → 搜尋 "#VALUE!"。
小結
#VALUE! 本質是「型別不匹配」。解決策略優先序:源頭清洗資料型別 > 公式端防禦性轉型 > 錯誤捕獲替代值。養成「資料進表先標準化(Ctrl+T、分欄轉數值、TRIM/CLEAN)」習慣,能從源頭消除 90% 的 #VALUE! 錯誤。