ㄚ晟的IT筆記
ㄚ晟與巧克力的旅遊筆記

2008年10月14日 星期二

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

加入書籤: HemiDemi MyShare Baidu Google Bookmarks Yahoo! My Web Del.icio.us Digg technorati furl
  在公司中,常常聽到使用Excel的同事大喊『我明明用的很好,輸入都正確,怎麼跑出#NUM!來了,這是甚麼啦~』『哇靠!快來幫我看這個#REF!怎麼辦啦,老闆再催了。』『你的報表裡面怎麼一大堆的#N/A呢!這是甚麼?重做!』,這些亂七八糟的符號各代表了甚麼?該怎麼處理呢?

A01


說明:

#########


發生原因: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!』的錯誤。只要將欄位中的負號去除就可以了。


A05



#REF!  [回標題]



 



#VALUE!  [回標題]


案例:

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



A06


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



A07


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



A08


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



A09



#DIV/0!  [回標題]



案例:


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



A02



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



A03


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



A04



#NAME!  [回標題]



案例:


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



A10


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



A11



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



A12


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



A13



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



A14



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



A15


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



A16



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



A17



#NULL!  [回標題]



案例:


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



A18


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


A19


#N/A  [回標題]


案例:


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



A20


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



A21



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



A22



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



A23



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




 



5 則留言:

Excel讓我頭痛 提到...

有實例說明真的滿清楚的,謝啦!

小妖 提到...

我也常有 Excel 的問題,以後可能會常來問你喔 ^_^"

ㄚ晟 提到...

在能力範圍內及時間許可下,一定兩肋插刀的啦!

匿名 提到...

您好,想補充關於#N/A錯誤的引發原因,若參照的儲存格內含有(例:~)可能與excel內部設定之快捷鍵或函數等特殊字元,也將引起無法參照的情況發生。^^

ㄚ晟 提到...

感謝補充唷^^

加入書籤(訂閱文章)

HemiDemi Yahoo! My Web MyShare Add this page to Search 2.0 Add this page to FunP Add this page to Search 2.0 YouPush Baidu Google Bookmarks Del.icio.us Digg technorati furl