69

ear All,

寫完 Excel 公式函數完成後,結果顯示錯誤提醒, 然後也不知道錯在哪裡。每一次都非常懊惱,別擔心! Excel  提供「公式稽核」錯誤檢查,可以讓你快速找到問題源頭。

image

  一、自動公式稽核  

1. 公式速速「現身」

如前提所述,當輸入公式完成後,只會顯示結果。如果出現錯誤的話,必須逐一檢查公式的正確性。所以,直接顯示公式的話,有效幫助公式查核和互相比較公式的正確性。

公式稽核位置: 「公式」→「公式稽核」→「顯示公式」。

恢復結果狀態: 再按一次「顯示公式」即可恢復。

如對下圖公式設定有興趣,請點選:

52. EXCEL教學_自動 民國轉西元年, 西元轉民國年 (TEXT,DATE,MID)

image

2. 精準發現錯誤值

有些產生錯誤值的原因 ,來自於公式名稱的錯誤的。在此提供給讀者解決稽核此部分的方法。這一次同樣是使用:  點選「錯誤值」後→「公式稽核」→「評估值公式」。

對話框內有點選「評估值」後,功能會自動提供公式本身的錯誤位置。

image

  二、8大常見「錯誤」顯示  

經常性使用 MS Excel 的讀者,在執行公式結果後,偶爾出現一些錯誤值。以下介紹8大常見的錯誤值

1.「#####」

1.1 出現原因:

日期計算的結果是負值;日期數列的結果超過允許數值;儲存格長度不足無法顯示數值全部。

1.2 解決方法:

更正計算日期的公式,讓結果為正值。

讓日期結果在系統允許的數值內 (1 ~ 2958465)。

拉寬儲存格長度。

2. 「NULL!」

2.1 出現原因:

函數運算公式中,使用了不正確的公式函數;使用不正確的運算子和引用儲存格;使用2個不相干的區域作為交點等等。

2.2 解決方法:

使用「公式稽核」進行錯誤值查詢。

正確使用引用儲存格。如: SUM(B1:B10),使用拖曳儲存格方式避免此錯誤。

解決不相干區域問題,只要使用「,」逗號符號,就較能避免此問題。

3. 「#VALUE!」

3.1 出現原因:

您輸入公式的方式錯誤。或,您參照的儲存格錯誤。這是最常見發生的儲存格錯誤,同時這也是很難找到發生錯誤原因。

3.2 解決方法:

使用「公式稽核」來查找問題。

輸入完成後,如果是陣列函數的話,可以使用Ctrl + Shift + Enter複合鍵來進行確認問題。

4. 「#DIV/0!」

4.1 出現原因:

數字除以「0」或「空白格」時,就會出現此錯誤。例如: =32/0。儲存格會顯示錯誤。

3.2 解決方法:

讓儲存格數字非為0或是空白即可。

5. 「#REF!」

5.1 出現原因:

儲存格公式的引用值無效/錯誤時,則會出現此錯誤。

5.2 解決方法:

使用顯示公式修正/比對錯誤。

確認公式連接的儲存格數值的正確性。

6. 「#NAME?」

6.1 出現原因:

使用「名稱管理員」功能來定義名稱後,當讀者使用了「名稱管理員」無法辨別的名稱時,則出現此錯誤。例如: 員工姓名輸入成員工性名…等等。

6.2 解決方法:

更正/確認名稱使用的正確性。

檢查公式使用的物件是否正確。

名稱管理員位置: 「公式」→「已定義之名稱」→「名稱管理員」

image

7. 「#N/A」

7.1 出現原因:

經常出現於函數公式的結果顯示,表示「無法得到有效值」。當函數或公式無法成立時,則顯示此錯誤。

此錯誤最常見的地方: 是使用VLOOKUPHLOOKUPLOOKUP MATCH 函數時候

7.2 解決方法:

點選錯誤值之儲存格,點選「公式稽核」→「檢查錯誤」。

使用「評估值公式」,更精確查找哪一個儲存格數值是錯誤的。

8. 「#NUA!」

8.1 出現原因:

簡而言之,公式中使用了無效的數值。所以才產生此錯誤。

8.2 解決方法:

使用數值中,修改數值格式成符合的格式。例如: 文字格式數字格式、貨幣格式數字格式…等。

若要修正此問題,請變更 Excel 反覆運算公式的次數

使用計算選項。位置: 「檔案」「選項」→Excel中「公式」「計算選項」「選取啟用反覆運算」打勾。

image

8.3 說明:

在 [最高次數] 方塊中,輸入您要 Excel 重新計算的次數。 反覆運算的次數愈高,Excel 計算工作表所需的時間愈長。

在 [最大誤差] 方塊中,輸入計算結果之間您可以接受的變更量。 數字愈小,結果的精準度愈高,但 Excel 計算工作表所需的時間也愈長。 

 

後記: 

> 如果我的說明令讀者感到困惑。請點選以下官網連結:

> MS EXCEL 公式/函數說明

 

延伸閱讀:

68. Excel 教學_將日期數字 轉成 中文大寫 (TEXT)

67. Excel教學-強制第一字母大寫, 其餘小寫方法 (PROPER, LOWER, UPPER )


    愛偷懶先生 發表在 痞客邦 留言(1) 人氣()