Excel公式應用常見錯誤及處理

　　在利用Excel完成任務的過程中，公式被使用得非常多，能夠解決各種各樣的問題。但是，這並不意味著公式的運用總會一帆風順，如果我們運用函數和公式的時候稍微不仔細，公式就可能返回一些奇怪的錯誤代碼，這可不是我們希望得到的結果。

　　看到這些奇怪的錯誤代碼，有的朋友可能會手忙腳亂，甚至感到煩躁。其實，任何錯誤均有它內在的原因，下面我們就通過實例剖析，和大家探討根據公式返回錯誤值的代碼識別錯誤的類型和原因，以及相應的處理方法，幫助朋友們輕松地應對各種常見錯誤。

　　一、 #DIV/0! 錯誤

　　常見原因：如果公式返回的錯誤值為“#DIV/0!”，這是因為在公式中有除數為零，或者有除數為空白的單元格(Excel把空白單元格也當作0)。

　　處理方法：把除數改為非零的數值，或者用IF函數進行控制。具體方法請參見下面的實例。

　　具體實例：如圖1的所示的工作表，我們利用公式根據總價格和數量計算單價，在D2單元格中輸入的公式為“=B2/C2”，把公式復制到D6單元格後，可以看到在D4、D5和D6單元格中返回了“#DIV/0!”錯誤值，原因是它們的除數為零或是空白單元格。

　　假設我們知道“鼠標”的數量為“6”，則在C4單元格中輸入“6”，錯誤就會消失(如圖2)。

　　假設我們暫時不知道“錄音機”和“刻錄機”的數量，又不希望D5、D6單元格中顯示錯誤值，這時可以用IF函數進行控制。在D2單元格中輸入公式 “=IF(ISERROR(B2/C2)，""，B2/C2)”，並復制到D6單元格。可以看到，D5和D6的錯誤值消失了，這是因為IF函數起了作用。整個公式的含義為：如果B2/C2返回錯誤的值，則返回一個空字符串，否則顯示計算結果。

　　說明：其中ISERROR(value)函數的作用為檢測參數value的值是否為錯誤值，如果是，函數返回值TRUE，反之返回值FALSE.。

　　二、 #N/A 錯誤

　　常見原因：如果公式返回的錯誤值為“#N/A”，這常常是因為在公式使用查找功能的函數(VLOOKUP、HLOOKUP、LOOKUP等)時，找不到匹配的值。

　　處理方法：檢查被查找的值，使之的確存在於查找的數據表中的第一列。

　　具體實例：在如圖4所示的工作表中，我們希望通過在A10單元格中輸入學號，來查找該名同學的英語成績。B10單元格中的公式為 “=VLOOKUP(A10，A2：E6，5，FALSE)”，我們在A10中輸入了學號“107”由於這個學號，由於在A2：A6中並沒有和它匹配的值，因此出現了“#N/A”錯誤。

　　如果要修正這個錯誤，則可以在A10單元格中輸入一個A2：A6中存在的學號，如“102”，這時錯誤值就不見了(如圖5)。

　　說明一：關於公式“=VLOOKUP(A10，A2：E6，5，FALSE)”中VLOOKUP的第四個參數，若為FALSE，則表示一定要求完全匹配lookup_value的值;若為TRUE，則表示如果找不到完全匹配lookup_value的值，就使用小於等於 lookup_value 的最大值。

　　說明二：出現“#N/A”錯誤的原因還有其他一些，選中出現錯誤值的B10單元格後，會出現一個智能標記，單擊這個標記，在彈出的菜單中選擇“關於此錯誤的幫助”(如圖6)，就會得到這個錯誤的詳細分析(如圖7)，通過這些原因和解決方法建議，我們就可以逐步去修正錯誤，這對其他的錯誤也適用。

　　三、 #NAME?　錯誤

　　常見原因：如果公式返回的錯誤值為“#NAME?”，這常常是因為在公式中使用了Excel無法識別的文本，例如函數的名稱拼寫錯誤，使用了沒有被定義的區域或單元格名稱，引用文本時沒有加引號等。

　　處理方法：根據具體的公式，逐步分析出現該錯誤的可能，並加以改正，具體方法參見下面的實例。

　　具體實例：如圖8所示的工作表，我們想求出A1：A3區域的平均數，在B4單元格輸入的公式為“=aveage(A1：A3)”，回車後出現了 “#NAME?”錯誤(如圖8)，這是因為函數“average”錯誤地拼寫成了“aveage”，Excel無法識別，因此出錯。把函數名稱拼寫正確即可修正錯誤。

　　選中C4單元格，輸入公式“=AVERAGE(data)”，回車後也出現了“#NAME?”錯誤(如圖9)。這是因為在這個公式中，我們使用了區域名稱data，但是這個名稱還沒有被定義，所以出錯。

　　改正的方法為：選中“A1：A3”單元格區域，再選擇菜單“名稱→定義”命令，打開“定義名稱”對話框，在文本框中輸入名稱“data”單擊“確定”按鈕(如圖10)。

　　返回Excel編輯窗口後，可以看到錯誤不見了(如圖11)。

　　選中D4單元格，輸入公式“=IF(A1=12，這個數等於12，這個數不等於12)”，回車後出現“#NAME?”錯誤(如12)，原因是引用文本時沒有添加引號。

 

　　修改的方法為：對引用的文本添加上引號，特別注意是英文狀態下的引號。於是將公式改為“=IF(A1=12，"這個數等於12"，"這個數不等於12")”(如圖13)。

　　四、 #NUM! 　錯誤

　　常見原因：如果公式返回的錯誤值為“#NUM!”，這常常是因為如下幾種原因：當公式需要數字型參數時，我們卻給了它一個非數字型參數;給了公式一個無效的參數;公式返回的值太大或者太小。

　　處理方法：根據公式的具體情況，逐一分析可能的原因並修正。

　　具體實例：在如圖14所示的工作表中，我們要求數字的平方根，在B2中輸入公式“=SQRT(A2)”並復制到B4單元格，由於A4中的數字為“-16”，不能對負數開平方，這是個無效的參數，因此出現了“#NUM!”錯誤。修改的方法為把負數改為正數即可。

　　五、 #VALUE　錯誤

　　常見原因：如果公式返回的錯誤值為“#VALUE”，這常常是因為如下幾種原因：文本類型的數據參與了數值運算，函數參數的數值類型不正確;函數的參數本應該是單一值，卻提供了一個區域作為參數;輸入一個數組公式時，忘記按Ctrl+Shift+Enter鍵。

　　處理方法：更正相關的數據類型或參數類型;提供正確的參數;輸入數組公式時，記得使用Ctrl+Shift+Enter鍵確定。

　　具體實例：如圖15的工作表，A2單元格中的“壹佰”是文本類型的，如果在B2中輸入公式“=A2*2”，就把文本參與了數值運算，因此出錯。改正方法為把文本改為數值即可。

　　圖16中，在A8輸入公式“=SQRT(A5：A7)”，對於函數SQRT，它的參數必須為單一的參數，不能為區域，因此出錯。改正方法為修改參數為單一的參數即可。

　　如圖17的工作表，如果要想用數組公式直接求出總價值，可以在E8單元格中輸入公式“{=SUM(C3：C7*D3：D7)}”，注意其中的花 括號不是手工輸入的，而是當輸入完成後按下Ctrl+Shift+Enter鍵後，Excel自動添加的。如果輸入後直接用Enter鍵確定，則會出現 “#VALUE”錯誤。