找軟件用軟件,就到華軍軟件園! 軟件發(fā)布
所在位置: 首頁 — 教程攻略 — 教程 — 電腦軟件教程  —  Excel如何...

Excel如何避免公式錯誤?

作者:秋葉PPT 日期:2018-12-14

 下圖中帶 # 字符的奇怪語句,小 E 相信你一定沒少遇到過。其實,這些都是 Excel 中常見的函數(shù)公式返回的錯誤值!

每次看到它們的時候,很多人內(nèi)心恐怕都是下面這樣的場景——

今天,小 E 將為大家全面介紹這些錯誤值產(chǎn)生的原因,并且還有應(yīng)對的辦法,幫助大家日后可以輕松地處理函數(shù)公式返回的錯誤值!

- 1 -錯誤值生成的原因

? 生成「#DIV/0!」錯誤值的原因

如下圖所示,當銷售人員計算「同比」時,出現(xiàn)了 #DIV/0! 錯誤值。

在 Excel 中出現(xiàn)「#DIV/0!」錯誤值原因是在于在公式中使用了除法運算,同時除數(shù)為 0。

比如上圖中,當計算「叔玉」的同比值時,由于他上期的完成數(shù)為 0,當用公式

=(C5-B5)/B5

計算同比時,B5 單元格的值為 0,所以出現(xiàn)了「#DIV/0!」錯誤值。

其中 DIV 是除數(shù)的英文(divisor)縮寫,而/0 表示除數(shù)為 0。

微信圖片_20181214233826.jpg

? 生成「#NAME?」錯誤值的原因

當 Excel 無法識別公式中的文本時,將出現(xiàn)「#NAME?」錯誤值。

比如下圖中,本來是要對 B2:B9 單元格區(qū)域進行求和,但是把 SUM 函數(shù)寫成了 sume 函數(shù),Excel 無法識別這個函數(shù),所以生成了「#NAME?」錯誤值。

在 D 列輸入了公式,也生成了錯誤值「#NAME?」。

=本月同比 & (C2-B2)/B2

在 Excel 函數(shù)公式中,如果要輸入文本值,需要用英文雙引號括起來。如果沒有括起來,Excel 會認為這個文本是自定義的公式名稱,或是函數(shù)名稱,結(jié)果沒找到,就會生成「#NAME?」錯誤值。

? 生成「#REF!」錯誤值的原因

#REF! 錯誤值也是一種常見的函數(shù)公式返回的錯誤值,當函數(shù)公式中的單元格引用被刪除時,將會生成「#REF!」錯誤值。

其中「#REF!」錯誤值中的 REF 是引用的英文縮寫(reference),除了刪除原有公式中的單元格引用,凡是函數(shù)公式中要返回一個無效的單元格引用時,都會生成「#REF!」錯誤值。

單元格 A1:C9 只有 9 行數(shù)據(jù),而 E2 此處的公式是

=INDEX(A1:C9,10,1)

含義是要返回 A1:C9 單元格區(qū)域中的第 10 行第 1 列的數(shù)據(jù),顯然這個是不存在的引用,所以也生成了「#REF!」錯誤值。

微信圖片_20181214234019.jpg

? 生成「#NULL!」錯誤值的原因

如下圖所示,為了求兩個黃色填充單元格區(qū)域的交叉重疊部分(即藍色填充單元格)的和,可以使用下面的公式

=SUM(B2:D7 C6:F11)

其中公式中 B2:D7 和 C6:F11 之間的空格符是一個單元格區(qū)域運算符,用于求出兩個單元格區(qū)域的交叉部分。

當兩個單元格區(qū)域沒有交叉部分時,函數(shù)就將生成「 #NULL!」錯誤值。下面的公式就將生成「#NULL!」錯誤值。

=SUM(B2:C5 D8:F11)

因為 B2:C5 單元格區(qū)域和 D8:F11 單元格區(qū)域之間沒有相交重疊的單元格區(qū)域。

? 生成「#NUM! 」錯誤值的原因

若在 Excel 中輸入了函數(shù)中不支持的數(shù)值參數(shù)時,會生成「#NUM! 」錯誤值。

當用 DEC2BIN 函數(shù)將十進制值轉(zhuǎn)化為二進制值時,由于 DEC2BIN 函數(shù)的第 2 個參數(shù)使用了超出可以顯示范圍的參數(shù),所以生成了「#NUM!」錯誤值。

? 生成「#N/A」錯誤值的原因

#N/A 錯誤值也是一種常見的錯誤值,如果經(jīng)常使用 VLOOKUP 函數(shù),就一定不會陌生!用 VLOOKUP 函數(shù)查找不到要查找的值的對應(yīng)信息時,就會返回 #N/A 錯誤值。

如下圖所示,在 G2 單元格中輸入公式

=VLOOKUP(F2,$A$1:$C$8,3,0)

在 A 列的銷售人員中并沒有「叔玉」這個人,所以 G2 單元格生成了「#N/A」錯誤值。不僅僅是 VLOOKUP 函數(shù),在查找引用函數(shù)中如果某個被查找值無法找到,都將生成「#N/A」錯誤值。

微信圖片_20181214234055.jpg

? 生成「#VALUE! 」錯誤值的原因

#VALUE! 錯誤值的生成原因有多種,其中最常見的有以下兩種↓

a.文本參與了數(shù)值運算

C5 單元格中計算折后價時,由于工作人員疏忽,在 A5 單元格價格 70 中添加了文本「元」,變成了「70 元」,Excel 將該 A5 單元格的數(shù)據(jù)視為文本,文本參與乘法運算,導致生成了「#VALUE!」錯誤值。

b.輸入了一個數(shù)組公式,沒有按【Shitf+Ctrl+Enter】組合鍵結(jié)束

下面是一個經(jīng)典的求單列不重復值的公式。

由于開始的公式?jīng)]有以【Shitf+Ctrl+Enter】組合快捷鍵結(jié)束,所以公式生成了「#VALUE!」錯誤值。

當換成以【Shitf+Ctrl+Enter】組合快捷鍵結(jié)束公式的輸入時,公式才返回正確的值,這個就是數(shù)組公式的要點。

? 生成「#####」錯誤值的原因

準地說,「#####」錯誤值并不是函數(shù)公式產(chǎn)生的錯誤值,它是 Excel 中的一種顯示預(yù)警。

當單元格中出現(xiàn)「#####」時,一般有以下兩個原因:

a.當我們在單元格輸入負數(shù),然后將單元格格式顯示為日期或者時間格式時,單元格內(nèi)容會顯示「#####」。

這種況的解決辦法就是把單元格格式改成常規(guī)。

b.當單元格的列寬不足以顯示所有單元格內(nèi)容時,也會出現(xiàn)「#####」錯誤值。

由于 D 列的列寬不足以顯示 D5 的公式生成的值,所以顯示「####」,當雙擊調(diào)整列寬后即可顯示正確的值。

微信圖片_20181214232218.gif

- 2 -糾正錯誤值的方法

上面詳細地介紹了 Excel 中 8 種錯誤值的生成原因,接下來小 E 要告訴大家如何糾正這些錯誤值。

? 總原則

所有的錯誤值都有具體的生成原因,要想避免生成錯誤值,首要原則就是保證輸入的函數(shù)名稱和函數(shù)參數(shù)要正確。

比如 #NAME? 錯誤值的生成原因,主要就是由于輸入了 Excel 無法識別的函數(shù)名稱或者參數(shù),這時候只需修改函數(shù)名稱即可。

再比如 #REF! 錯誤值需要注意,不要誤刪除有引用的單元格,另外要引用的單元格必須存在。不能只有 10 行的單元格區(qū)域,想要返回第 11 行的。

? 遇到錯誤值,替換顯示原則

有些錯誤值是不可避免的會生成。

比如 VLOOKUP 找不到值時,返回的#N/A 錯誤值,再比如算同比時的除數(shù)確實為 0。

遇到這些不可避免的錯誤值,我們可以用一個萬能函數(shù) IFERROR,將結(jié)果顯示為其它更有意義的值。

微信圖片_20181214234213.jpg

IFERROR 函數(shù)的語法如下

=IFERROR(value,value_if_error)

其中第一個參數(shù) value 為返回錯誤值的公式,value_if_error 參數(shù)為當公式返回錯誤值時要設(shè)置的返回值。

比如上文中的「#DIV/0!」錯誤值,可以使用如下的公式替代

=IFERROR((C5-B5)/B5,"上期完成數(shù)為 0")

再比如上文中的#N/A 錯誤值,可以使用如下的公式替代:

=IFERROR(VLOOKUP(F2,$A$1:$C$8,3,0),"沒有該成員的銷售信息")

即使在輸入公式時沒有注意,造成了這樣的錯誤也不要慌,相信你看完文章已經(jīng)對這 8 個難纏錯誤的原因和解決辦法都了然于心了!

下次,我們也就能從容應(yīng)對這些錯誤了~

相關(guān)文章

最新教程 更多

google怎么登錄?-google登錄的方法 google怎么登錄?-google登錄的方法

google怎么注冊?-google注冊的操作流程 google怎么注冊?-google注冊的操作流程

flash怎么扣圖?-flash扣圖的方法 flash怎么扣圖?-flash扣圖的方法

flash怎么導出gif動圖?-flash導出gif動圖的方法 flash怎么導出gif動圖?-flash導出gif動圖的方法

flash怎么旋轉(zhuǎn)圖形?-flash旋轉(zhuǎn)圖形的方法 flash怎么旋轉(zhuǎn)圖形?-flash旋轉(zhuǎn)圖形的方法

熱門教程 更多

電腦重裝系統(tǒng)后沒聲音怎么辦-驅(qū)動精靈教程 電腦重裝系統(tǒng)后沒聲音怎么辦-驅(qū)動精靈教程

金山打字通怎么練打字-金山打字通練習打字方法 金山打字通怎么練打字-金山打字通練習打字方法

bootcamp驅(qū)動如何升級?bootcamp驅(qū)動升級方法 bootcamp驅(qū)動如何升級?bootcamp驅(qū)動升級方法

qq音樂皮膚怎么換?-qq音樂換皮膚方法 qq音樂皮膚怎么換?-qq音樂換皮膚方法

驅(qū)動總裁下載-驅(qū)動軟件下載很慢如何解決 驅(qū)動總裁下載-驅(qū)動軟件下載很慢如何解決