Excel欄位公式常見錯誤原因與處理

　　在公司中，常常聽到使用Excel的同事大喊『我明明用的很好，輸入都正確，怎麼跑出#NUM!來了，這是甚麼啦～』『哇靠！快來幫我看這個#REF!怎麼辦啦，老闆再催了。』『你的報表裡面怎麼一大堆的#N/A呢！這是甚麼？重做！』，這些亂七八糟的符號各代表了甚麼？該怎麼處理呢？

說明：

#########

發生原因：1.欄寬度不夠

　　　　　2.日期或時間為負數

處理方式：1.拉大欄位寬度到可以顯示內容為止；在欄位邊緣點選滑鼠兩次也可達到效果。

　　　　　2.確認日期或時間的運算結果不能為負數即可解決。

--------------------　分隔線　--------------------

#NUM!

發生原因：公式中使用不正確的參數，或是公式結果的值太大或太小。

處理方式：逐步檢查並修正公式中的錯誤。

範例

--------------------　分隔線　--------------------

#REF!

發生原因：公式引用的欄位被刪除，或是欄位中有錯誤。

處理方式：修正欄位中的錯誤，或是點選上一步復原鍵復原正確的狀態。

範例

--------------------　分隔線　--------------------

#VALUE!

發生原因：1.公式中本該數字與數字運算卻將數字與文字作運算，便會造成數值錯誤。如A1=123、A2=ABC、A3=A1+A2。

　　　　　2.公式本該引用單一數值，卻不小心引用了一個區域。

處理方式：1.修改公式中的文字格式，重新輸入為數字即可。

　　　　　2.改變公式為引用區域函數，或是改變引用的區域為單一數值。

範例

--------------------　分隔線　--------------------

#DIV/0!

發生原因：公式中發生除數為0或為空白值的狀況，如100/0。

處理方式：避免除數為0或空白的狀況，或是利用If等等函數控制。

範例

--------------------　分隔線　--------------------

#NAME?

發生原因：在公式中有無法辨識的格式或文字，如函數的名稱錯誤，或是使用沒有被定義的範圍名稱等。

處理方式：逐步分析公式中的錯誤，並加以修正。

範例

--------------------　分隔線　--------------------

#NULL!

發生原因：使用了不正確的範圍運算子或引用的範圍沒有相交集。

處理方式：改善範圍運算子或改變引用的範圍互相交集。

範例

--------------------　分隔線　--------------------

#N/A

發生原因：使用VLOOKUP、HLOOKUP、LOOKUP等函數時，找不到相配合的數值。

處理方式：檢查查詢的欄位，填入正確的值。

範例

--------------------　分隔線　--------------------

　　小小動作，正確多多！

　

========== 大條分隔線 ==========

　

#NUM!  [回標題]

案例：

　　當我們求A欄數字的平方根，在B2輸入『=SQRT(A2)』並填充整個B欄。因為A5中的值為負數，違反了不可對付數開根號的規則。換句話說，公式中使用了不正確的參數，造成欄位顯示『#NUM!』的錯誤。只要將欄位中的負號去除就可以了。

#REF!  [回標題]

 

#VALUE!  [回標題]

案例：

　　1.如下圖所示，運算單價的公式為總價除以數量，也就是D2欄位中數入C2/B2並填滿整個D欄。第3列美國珍珠小葡萄出現#VALUE!錯誤，原因為B3欄為文字呈現，無法與數值作乘除運算，只要將欄位中的文字改為數字就可以解決。

　　2.如下圖，無條件捨去函數INT()規定其中的參數必須是唯一的。但E6欄位中進口草莓的無條件捨去公式卻打成了『=INT(D4:D5)』參照了一個區域，造成了這樣的錯誤產生。只要將當中的參數改為單一值就可以解決了。

　　3.如下圖的後三項合計，如果不直接用總價相加的方式求出合計，而是利用數量與單價的矩陣公式運算後三項合計。在C7欄位中輸入『=SUM(B4:B6*D4:D6)』，單擊Enter鍵後，發現出現了#VALUE!的錯誤。

　　4.這時候我們應該回到C7欄位，在輸入相同的公式後，合併單擊鍵盤上的Ctrl+Shift+Enter鍵，將公式改為以大括號{}包覆的矩陣公式就可以解決這樣的問題。

#DIV/0!  [回標題]

案例：

　　1.如下圖所示，單價的公式是利用總價除以數量所得，在D2的欄位中填入C2/B2並填滿D欄，發現發生了#DIV/0!的狀況。原因為B4欄位的值為0，B5與B6則為空值，造成除數為0的錯誤。

　　2.若在紐西蘭奇異果的數量上手動填入50，錯誤就會消失。

　　3.如果我們並不知道黃金奇異果與進口草莓的數量，其實我們也可以利用If的函數來規避除數為0的錯誤。比如說我們在D5的欄位中填入『=IF(ISERROR(C5/B5),"",C5/B5』,也就是說『如果C5/B5的值為錯誤值，就顯示"空白"，否則就顯示值。』

#NAME!  [回標題]

案例：

　　1.如下圖所示，我們要求得單價的平均值，利用AVERAGE()函數，卻手誤輸入成AVERGE()，錯誤的拼字造成Excel無法是別，而顯示『#NAME!』的錯誤訊息。只要將函數的名稱改正即可正確顯示。

　　2.我們再作個試驗。把平均值公式改為AVERAGE(NUM)，結果也輸出了『#NAME!』的錯誤。因為在函數裡我們使用了儲存格的範圍名稱『NUM』，但是我們卻沒有在名稱定義中定義這個名稱所代表的欄位範圍。

　　3.這時我們只要將單價欄中的各個欄位D2~D6選擇起來。

　　4.點選Excel公式功能區中以定義之名稱區塊的定義名稱。

　　5.給予該範圍一個名稱NUM(可自行命名)。

　　6.回到活頁表中，就可以發現問題已經解決了。

　　7.我們再來做個簡單的判斷。在E4欄位中輸入『=IF(D4=D3,小葡萄等於奇異果,小葡萄不等於奇異果』來判斷小葡萄與奇異果的單價是否相同，卻發現發生『#NAME!』的錯誤。原因是因為使用文字時沒有加上""符號。

　　8.只要加上""符號，將公式改為『=IF(D4=D3,"小葡萄等於奇異果","小葡萄不等於奇異果"』，問題就可以解決了。

#NULL!  [回標題]

案例：

　　1.如下圖，我們要計算總價的合計，其實可以利用SUM(C2:C6)來解決，為了說明這個問題我們把它拆為兩組(C2:C3與C4:C6)，我們要將這兩組合計，公式輸入『=SUM(C2:C3
C4:C6)』卻發生『#NULL!』的錯誤。原因是要引用兩個區間必須利用『，』來做連結。

　　2.只要將公式加上『，』改為『=SUM(C2:C3,C4:C6)』就可以解決。

#N/A  [回標題]

案例：

　　1.如下圖，利用VLOOKUP函數來查詢數量。在B10欄位輸入公式『=VlOOKUP(A10,$A$2:$B:$6,2,FALSE)』，卻因為A10中的產品進口香蕉並不在查詢的列表當中，查詢不到相對應的值，所以顯示了『#N/A』錯誤。

　　2.只要將A10欄位中的產品名稱更改為列表中對應得到的產品，如進口草莓就能夠解決了。

　　其實，除了上述較常出現錯誤的原因之外，還是有其他的狀況會產生這樣的情況。只要點選錯誤的欄位，在欄位的附近會出現一個『驚嘆號』標識，單擊後會出現欄位，選擇關於這個錯誤的說明(H)。

　　便會跳出Excel說明視窗，說明該錯誤的原因與修正方式。

　　以上是較常見的經驗分享，若讀者有任何的發現與經驗要與大家分享，也請不吝賜教喔！