Excel怎样避免公式毛病 excel公式毛病缘由有哪些-英雄云拓展知识分享

　　Excel作为电脑上的办公软件，大家肯定没少用，它帮我们解决了很多复杂的公式计算和统计，在平时计算的时候小e字符大家肯定没少碰到过，这便是代表Excel 中函数公式返回的毛病值，今天主要就是告知Excel怎样避免公式毛病。

　　下图中带 # 字符的奇怪语句，小 E 相信你一定没少遇到过。其实，这些都是 Excel 中常见的函数公式返回的毛病值！

　　每次看到它们的时候，很多人内心恐怕都是下面这样的场景——

　　今天，小 E 将为大家全面介绍这些毛病值产生的缘由，并且还有应对的办法，帮助大家往后可以轻松地处理函数公式返回的毛病值！

　　- 1 -毛病值生成的缘由

　　❶ 生成「#DIV/0！」毛病值的缘由

　　以下图所示，当销售人员计算「同比」时，出现了 #DIV/0！ 毛病值。

　　在 Excel 中出现「#DIV/0！」毛病值缘由是在于在公式中使用了除法运算，同时除数为 0。

　　比如上图中，当计算「叔玉」的同比值时，由于他上期的完成数为 0，当用公式

　　=（C5-B5）/B5

　　计算同比时，B5 单元格的值为 0，所以出现了「#DIV/0！」毛病值。

　　其中 DIV 是除数的英文（divisor）缩写，而/0 表示除数为 0。

　　❷ 生成「#NAME？」毛病值的缘由

　　当 Excel 没法辨认公式中的文本时，将出现「#NAME？」毛病值。

　　比以下图中，本来是要对 B2:B9 单元格区域进行求和，但是把 SUM 函数写成了 sume 函数，Excel 没法辨认这个函数，所以生成了「#NAME？」毛病值。

　　在 D 列输入了公式，也生成了毛病值「#NAME？」。

　　=本月同比 & （C2-B2）/B2

　　在 Excel 函数公式中，如果要输入文本值，需要用英文双引号括起来。如果没有括起来，Excel 会认为这个文本是自定义内涵的公式名称，或是函数名称，结果没找到，就会生成「#NAME？」毛病值。

　　❸ 生成「#REF！」毛病值的缘由

　　#REF！ 毛病值也是一种常见的函数公式返回的毛病值，当函数公式中的单元格援用被删除时，将会生成「#REF！」毛病值。

　　其中「#REF！」毛病值中的 REF 是援用的英文缩写（reference），除删除原有公式中的单元格援用，凡是函数公式中要返回一个无效的单元格援用时，都会生成「#REF！」毛病值。

　　单元格 A1:C9 只有 9 行数据，而 E2 此处的公式是

　　=INDEX（A1:C9，10，1）

　　含义是要返回 A1:C9 单元格区域中的第 10 行第 1 列的数据，明显这个是不存在的援用，所以也生成了「#REF！」毛病值。

　　❹ 生成「#NULL！」毛病值的缘由

　　以下图所示，为了求两个黄色填充单元格区域的交叉堆叠部份（即蓝色填充单元格）的和，可使用下面的公式

　　=SUM（B2:D7 C6:F11）

　　其中公式中 B2:D7 和 C6:F11 之间的空格符是一个单元格区域运算符，用于求出两个单元格区域的交叉部份。

　　当两个单元格区域没有交叉部份时，函数就将生成「 #NULL！」毛病值。下面的公式就将生成「#NULL！」毛病值。

　　=SUM（B2:C5 D8:F11）

　　由于 B2:C5 单元格区域和 D8:F11 单元格区域之间没有相交堆叠的单元格区域。

　　❺ 生成「#NUM！ 」毛病值的缘由

　　若在 Excel 中输入了函数中不支持的数值参数时，会生成「#NUM！ 」毛病值。

　　当用 DEC2BIN 函数将10进制值转化为2进制值时，由于 DEC2BIN 函数的第 2 个参数使用了超越可以显示范围的参数，所以生成了「#NUM！」毛病值。

　　❻ 生成「#N/A」毛病值的缘由

　　#N/A 毛病值也是一种常见的毛病值，如果常常使用 VLOOKUP 函数，就一定不会陌生！用 VLOOKUP 函数查找不到要查找的值的对应信息时，就会返回 #N/A 毛病值。

　　以下图所示，在 G2 单元格中输入公式

　　=VLOOKUP（F2，$A$1：$C$8，3，0）

　　在 A 列的销售人员中并没有「叔玉」这个人，所以 G2 单元格生成了「#N/A」毛病值。不单单是 VLOOKUP 函数，在查找援用函数中如果某个被查找值没法找到，都将生成「#N/A」毛病值。

　　❼ 生成「#VALUE！ 」毛病值的缘由

　　#VALUE！ 毛病值的生成缘由有多种，其中最多见的有以下两类↓

　　a.文本参与了数值运算

　　C5 单元格中计算折后价时，由于工作人员忽视，在 A5 单元格价格 70 中添加了文本「元」，变成了「70 元」，Excel 将该 A5 单元格的数据视为文本，文本参与乘法运算，致使生成了「#VALUE！」毛病值。

　　b.输入了一个数组公式，没有按【Shitf+Ctrl+Enter】组合键结束

　　下面是一个经典的求单列不重复值的公式。

　　由于开始的公式没有以【Shitf+Ctrl+Enter】组合快捷键结束，所以公式生成了「#VALUE！」毛病值。

　　当换成以【Shitf+Ctrl+Enter】组合快捷键结束公式的输入时，公式才返回正确的值，这个就是数组公式的要点。

　　❽ 生成「#####」毛病值的缘由

　　准地说，「#####」毛病值其实不是函数公式产生的毛病值，它是 Excel 中的一种显示预警。

　　当单元格中出现「#####」时，一般有以下两个缘由：

　　a.当我们在单元格输入负数，然后将单元格格式显示为日期或时间格式时，单元格内容会显示「#####」。

　　这类况的解决办法就是把单元格格式改成常规。

　　b.当单元格的列宽不足以显示所有单元格内容时，也会出现「#####」毛病值。

　　由于 D 列的列宽不足以显示 D5 的公式生成的值，所以显示「####」，当双击调剂列宽后便可显示正确的值。

　　- 2 -纠正毛病值的方法

　　上面详细地介绍了 Excel 中 8 种毛病值的生成缘由，接下来小 E 要告知大家如何纠正这些毛病值。

　　❶ 总原则

　　所有的毛病值都有具体的生成缘由，要想避免生成毛病值，重要原则就是保证输入的函数名称和函数参数要正确。

　　比如 #NAME？ 毛病值的生成缘由，主要就是由于输入了 Excel 没法辨认的函数名称或参数，这时候候只需修改函数名称便可。

　　再比如 #REF！ 毛病值需要注意，不要误删除有援用的单元格，另外要援用的单元格必须存在。不能只有 10 行的单元格区域，想要返回第 11 行的。

　　❷ 遇到毛病值，替换显示原则

　　有些毛病值是难以避免的会生成。

　　比如 VLOOKUP 找不到值时，返回的#N/A 毛病值，再比如算同比时的除数确切为 0。

　　遇到这些难以避免的毛病值，我们可以用一个万能函数 IFERROR，将结果显示为其它更成心义的值。

　　IFERROR 函数的语法以下

　　=IFERROR（value，value_if_error）

　　其中第一个参数 value 为返回毛病值的公式，value_if_error 参数为当公式返回毛病值时要设置的返回值。

　　比如上文中的「#DIV/0！」毛病值，可使用以下的公式替换

　　=IFERROR（（C5-B5）/B5，“上期完成数为 0”）

　　再比如上文中的#N/A 毛病值，可使用以下的公式替换：

　　=IFERROR（VLOOKUP（F2，$A$1：$C$8，3，0），“没有该成员的销售信息”）

　　即便在输入公式时没有注意，造成了这样的毛病也不要慌，相信你看完文章已对这 8 个难缠毛病的缘由和解决办法都了然于心了！

　　下次，我们也就可以从容应对这些毛病了~

　　以上便是Excel避免公式毛病的教程了，大家可以多看几遍，希望内容对大家会有所帮助，大家可以多关注我们，后面还有更多精彩教程带给大家。