Excel公式應(yīng)用常見錯(cuò)誤及處理方法 處理常見錯(cuò)誤，這個(gè)問題是我們很多人都很困惑的一個(gè)問題，處理常見錯(cuò)誤是我們非常常用的一個(gè)東西，也是我們工作和學(xué)習(xí)中必不可少的一個(gè)數(shù)據(jù)公式，那么具體我們要怎么做呢？下面就來看看這篇Excel公式應(yīng)用常見錯(cuò)誤及處理方法 處理常見錯(cuò)誤的文章吧！

在利用Excel完成任務(wù)的過程中，公式被使用得非常多，能夠解決各種各樣的問題。但是，這并不意味著公式的運(yùn)用總會(huì)一帆風(fēng)順，如果我們運(yùn)用函數(shù)和公式的時(shí)候稍微不仔細(xì)，公式就可能返回一些奇怪的錯(cuò)誤代碼，這可不是我們希望得到的結(jié)果。

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

一、 #DIV/0! 錯(cuò)誤

常見原因：如果公式返回的錯(cuò)誤值為#DIV/0!，這是因?yàn)樵诠街杏谐龜?shù)為零，或者有除數(shù)為空白的單元格(Excel把空白單元格也當(dāng)作0)。

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

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

圖1

假設(shè)我們知道鼠標(biāo)的數(shù)量為6，則在單元格中輸入6，錯(cuò)誤就會(huì)消失(如圖2)。

圖2

假設(shè)我們暫時(shí)不知道錄音機(jī)和刻錄機(jī)的數(shù)量，又不希望D5、D6單元格中顯示錯(cuò)誤值，這時(shí)可以用IF函數(shù)進(jìn)行控制。在D2單元格中輸入公式 =IF(ISERROR(B2/C2)，""，B2/C2)，并復(fù)制到D6單元格?？梢钥吹?，D5和D6的錯(cuò)誤值消失了，這是因?yàn)镮F函數(shù)起了作用。整個(gè)公式的含義為：如果B2/C2返回錯(cuò)誤的值，則返回一個(gè)空字符串，否則顯示計(jì)算結(jié)果。

圖3

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

二、 #N/A 錯(cuò)誤

常見原因：如果公式返回的錯(cuò)誤值為#N/A，這常常是因?yàn)樵诠绞褂貌檎夜δ艿暮瘮?shù)(VLOOKUP、HLOOKUP、LOOKUP等)時(shí)，找不到匹配的值。

處理方法：檢查被查找的值，使之的確存在于查找的數(shù)據(jù)表中的第一列。

具體實(shí)例：在如圖4所示的工作表中，我們希望通過在A10單元格中輸入學(xué)號(hào)，來查找該名同學(xué)的英語成績。B10單元格中的公式為 =VLOOKUP(A10，A2：E6，5，F(xiàn)ALSE)，我們在A10中輸入了學(xué)號(hào)107由于這個(gè)學(xué)號(hào)，由于在A2：A6中并沒有和它匹配的值，因此出現(xiàn)了#N/A錯(cuò)誤。

圖4

如果要修正這個(gè)錯(cuò)誤，則可以在A10單元格中輸入一個(gè)A2：A6中存在的學(xué)號(hào)，如102，這時(shí)錯(cuò)誤值就不見了(如圖5)。

圖5

說明一：關(guān)于公式=VLOOKUP(A10，A2：E6，5，F(xiàn)ALSE)中VLOOKUP的第四個(gè)參數(shù)，若為FALSE，則表示一定要求完全匹配lookup_value的值;若為TRUE，則表示如果找不到完全匹配lookup_value的值，就使用小于等于 lookup_value 的最大值。

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

圖6

圖7

三、 #NAME? 錯(cuò)誤

常見原因：如果公式返回的錯(cuò)誤值為#NAME?，這常常是因?yàn)樵诠街惺褂昧薊xcel無法識(shí)別的文本，例如函數(shù)的名稱拼寫錯(cuò)誤，使用了沒有被定義的區(qū)域或單元格名稱，引用文本時(shí)沒有加引號(hào)等。

處理方法：根據(jù)具體的公式，逐步分析出現(xiàn)該錯(cuò)誤的可能，并加以改正，具體方法參見下面的實(shí)例。

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

圖8

選中單元格，輸入公式=ERAGE(data)，回車后也出現(xiàn)了#NAME?錯(cuò)誤(如圖9)。這是因?yàn)樵谶@個(gè)公式中，我們使用了區(qū)域名稱data，但是這個(gè)名稱還沒有被定義，所以出錯(cuò)。

圖9

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

圖10

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

圖11

選中D4單元格，輸入公式=IF(A1=12，這個(gè)數(shù)等于12，這個(gè)數(shù)不等于12)，回車后出現(xiàn)#NAME?錯(cuò)誤(如12)，原因是引用文本時(shí)沒有添加引號(hào)。

圖12

修改的方法為：對引用的文本添加上引號(hào)，特別注意是英文狀態(tài)下的引號(hào)。于是將公式改為=IF(A1=12，"這個(gè)數(shù)等于12"，"這個(gè)數(shù)不等于12")(如圖13)。

圖13

四、 #NUM! 錯(cuò)誤

常見原因：如果公式返回的錯(cuò)誤值為#NUM!，這常常是因?yàn)槿缦聨追N原因：當(dāng)公式需要數(shù)字型參數(shù)時(shí)，我們卻給了它一個(gè)非數(shù)字型參數(shù);給了公式一個(gè)無效的參數(shù);公式返回的值太大或者太小。

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

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

圖14

五、 #VALUE 錯(cuò)誤

常見原因：如果公式返回的錯(cuò)誤值為#VALUE，這常常是因?yàn)槿缦聨追N原因：文本類型的數(shù)據(jù)參與了數(shù)值運(yùn)算，函數(shù)參數(shù)的數(shù)值類型不正確;函數(shù)的參數(shù)本應(yīng)該是單一值，卻提供了一個(gè)區(qū)域作為參數(shù);輸入一個(gè)數(shù)組公式時(shí)，忘記按Ctrl+Shift+Enter鍵。

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

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

圖15

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

圖16

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

圖17

修改的方法為：選中E8單元格后激活公式欄，按下Ctrl+Shift+Enter鍵即可，這時(shí)可以看到Excel自動(dòng)添加了花括號(hào)(如圖18)。

圖18

六、 #REF! 錯(cuò)誤

常見原因：如果公式返回的錯(cuò)誤值為#REF!，這常常是因?yàn)楣街惺褂昧藷o效的單元格引用。通常如下這些操作會(huì)導(dǎo)致公式引用無效的單元格：刪除了被公式引用的單元格;把公式復(fù)制到含有引用自身的單元格中。

處理方法：避免導(dǎo)致引用無效的操作，如果已經(jīng)出現(xiàn)錯(cuò)誤，先撤銷，然后用正確的方法操作。

具體實(shí)例：如圖19的工作表，我們利用公式將代表日期的數(shù)字轉(zhuǎn)換為日期，在B2中輸入了公式=DATE(LEFT(A2，4)，MID(A2，5，2)，RIGHT(A2，2))并復(fù)制到B4單元格。

圖19

這時(shí)如果把A2：A4單元格刪除，則會(huì)出現(xiàn)#REF!錯(cuò)誤(如圖20)，這是因?yàn)閯h除了公式中引用的單元格。

圖20

先執(zhí)行撤消 刪除命令，然后復(fù)制B2：B4單元格區(qū)域到A2：A4，也會(huì)出現(xiàn)#REF!錯(cuò)誤(如圖21)，這是因?yàn)榘压綇?fù)制到了含有引用自身的單元格中。

圖21

由于這時(shí)已經(jīng)不能撤銷，所以我們先把A2：A4中的數(shù)據(jù)刪除，然后設(shè)置單元格格式為常規(guī)，在A2：A4中輸入如圖19所示的數(shù)據(jù)。

為了得到轉(zhuǎn)換好的日期數(shù)據(jù)，正確的操作方法為：先把B2：B4復(fù)制到一個(gè)恰當(dāng)?shù)牡胤剑鏒2：D4，粘貼的時(shí)候執(zhí)行選擇性粘貼，把數(shù)值粘貼過去。這時(shí)D2：D4中的數(shù)據(jù)就和A列及B列數(shù)據(jù)脫離關(guān)系了，再對它們執(zhí)行刪除操作就不會(huì)出錯(cuò)了(如圖22)。

圖22

說明：要得到圖22的效果，需要設(shè)置D2：D4的格式為日期。

七、 #NULL! 錯(cuò)誤

導(dǎo)致原因：如果公式返回的錯(cuò)誤值為#NULL!，這常常是因?yàn)槭褂昧瞬徽_的區(qū)域運(yùn)算符或引用的單元格區(qū)域的交集為空。

處理方法：改正區(qū)域運(yùn)算符使之正確;更改引用使之相交。

具體實(shí)例：如圖23所示的工作表中，如果希望對A1：A10和C1：C10單元格區(qū)域求和，在C11單元格中輸入公式=SUM(A1：A10 C1：C10)，回車后出現(xiàn)了#NULL!錯(cuò)誤，這是因?yàn)楣街幸昧瞬幌嘟坏膬蓚€(gè)區(qū)域，應(yīng)該使用聯(lián)合運(yùn)算符，即逗號(hào) (，)。

圖23

改正的方法為：在公式中的兩個(gè)不連續(xù)的區(qū)域之間添加逗號(hào)，改正后的效果為圖24.

圖24

關(guān)于Excel公式常見錯(cuò)誤的處理方法就介紹到這里。文中選用的實(shí)例都是平時(shí)出現(xiàn)最多的情況，請大家注意體會(huì)。文中圖6提到的幫助更正錯(cuò)誤的智 能標(biāo)記非常有用，如果利用介紹的方法都還未解決問題時(shí)，可以借助它進(jìn)一步的分析。總之，只要思路正確，耐心仔細(xì)，Excel的公式錯(cuò)誤最后就會(huì)被一一殲 滅。

以上就是Excel公式應(yīng)用常見錯(cuò)誤及處理方法 處理常見錯(cuò)誤全部內(nèi)容了，希望大家看完有所啟發(fā)，對自己的工作生活有所幫助，想要了解更多跟Excel公式應(yīng)用常見錯(cuò)誤及處理方法 處理常見錯(cuò)誤請關(guān)注我們文章營銷官網(wǎng)！

推薦閱讀

幾種常見的excel錯(cuò)誤及其解決方法 excel常見錯(cuò)誤 | 文軍營銷解決方法:將數(shù)值區(qū)域改為單一數(shù)值。修改數(shù)值區(qū)域,使其包含公式所在的數(shù)據(jù)行或列。 3.#DIV/O! 當(dāng)公式被零除時(shí),將會(huì)產(chǎn)生錯(cuò)誤值#DIV/O!。在具體操作中主要表現(xiàn)為以下兩種原因。 1)在公式中,除數(shù)使用了指向空單元格或包含零值單元格的單元格引用(在Excel中如果運(yùn)算對象是空白單元格,Excel將此空值當(dāng)作零值)。 解決方法:修改單元vlookup函數(shù)出現(xiàn)錯(cuò)誤怎么辦 vlookup函數(shù)常見錯(cuò)誤有哪些 | 文軍營銷第1種:沒有正確的使用引用方式,造成在復(fù)制公式后區(qū)域發(fā)生變動(dòng)引起錯(cuò)誤。 如下圖所示,當(dāng)C9的公式復(fù)制到C10和C11后,C10公式返回錯(cuò)誤值。 錯(cuò)誤原因:由于第二個(gè)參數(shù)A2:D6是相對引用,所以向下復(fù)制公式后會(huì)自動(dòng)更改為A3:D7,而A10中的工號(hào)A01所在的行,不在AO3:D7區(qū)域中,從而造成查找失敗。 解決方案:把第二個(gè)參數(shù)...