本文作者丨水星釣魚 - Excel 研究院

本文由「秋葉 Excel」原創(chuàng)發(fā)布

如需轉(zhuǎn)載，請(qǐng)?jiān)诠娞?hào)回復(fù)關(guān)鍵詞「轉(zhuǎn)載」查看說明

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

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

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

- 1 -

錯(cuò)誤值生成的原因

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

如下圖所示，當(dāng)銷售人員計(jì)算「同比」時(shí)，出現(xiàn)了 #DIV/0! 錯(cuò)誤值。

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

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

=(C5-B5)/B5

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

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

? 生成「#NAME?」錯(cuò)誤值的原因

當(dāng) Excel 無法識(shí)別公式中的文本時(shí)，將出現(xiàn)「#NAME?」錯(cuò)誤值。

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

再比如下圖↓

在 D 列輸入了公式，也生成了錯(cuò)誤值「#NAME?」。

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

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

? 生成「#REF!」錯(cuò)誤值的原因

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

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

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

=INDEX(A1:C9,10,1)

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

? 生成「#NULL!」錯(cuò)誤值的原因

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

=SUM(B2:D7 C6:F11)

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

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

=SUM(B2:C5 D8:F11)

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

? 生成「#NUM! 」錯(cuò)誤值的原因

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

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

? 生成「#N/A」錯(cuò)誤值的原因

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

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

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

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

? 生成「#VALUE! 」錯(cuò)誤值的原因

#VALUE! 錯(cuò)誤值的生成原因有多種，其中最常見的有以下兩種↓

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

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

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

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

由于一開始的公式沒有以【Shitf Ctrl Enter】組合快捷鍵結(jié)束，所以公式生成了「#VALUE!」錯(cuò)誤值。

當(dāng)換成以【Shitf Ctrl Enter】組合快捷鍵結(jié)束公式的輸入時(shí)，公式才返回正確的值，這個(gè)就是數(shù)組公式的要點(diǎn)。

? 生成「#####」錯(cuò)誤值的原因

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

當(dāng)單元格中出現(xiàn)「#####」時(shí)，一般有以下兩個(gè)原因：

a.當(dāng)我們?cè)趩卧褫斎胴?fù)數(shù)，然后將單元格格式顯示為日期或者時(shí)間格式時(shí)，單元格內(nèi)容會(huì)顯示「#####」。

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

b.當(dāng)單元格的列寬不足以顯示所有單元格內(nèi)容時(shí)，也會(huì)出現(xiàn)「#####」錯(cuò)誤值。

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

- 2 -

糾正錯(cuò)誤值的方法

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

? 總原則

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

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

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

? 遇到錯(cuò)誤值，替換顯示原則

有些錯(cuò)誤值是不可避免的會(huì)生成。

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

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

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

=IFERROR(value,value_if_error)

其中第一個(gè)參數(shù) value 為返回錯(cuò)誤值的公式，value_if_error 參數(shù)為當(dāng)公式返回錯(cuò)誤值時(shí)要設(shè)置的返回值。

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

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

如下圖所示↓

再比如上文中的#N/A 錯(cuò)誤值，可以使用如下的公式替代：

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

如下圖所示↓

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

下次，我們也就能從容應(yīng)對(duì)這些錯(cuò)誤了~