Excel数常见错误及其处理

1、在利用 Excel 完成任务的过程中，公式被使用得非常多，正如前面所介绍的，公式能够解决各种各样的问 题（参阅 Excel 中自定义函数实例剖析 ）。但是，这并不意味着公式的运用总会一帆风顺，如果我们 运用函数和公式的时候稍微不仔细， 公式就可能返回一些奇怪的错误代码， 这可不是我们希望得到的结果。看到这些奇怪的错误代码，有的朋友可能会手忙脚乱，甚至感到烦躁。其实，任何错误均有它内在的 原因，下面我们就通过实例剖析，和大家探讨根据公式返回错误值的代码识别错误的类型和原因，以及相 应的处理方法，帮助朋友们轻松地应对各种常见错误。 文章末尾提供原文件供大家下载参考。文章导读：错误常见原因处理方法实

2、例剖析#DIV/0!在公式中有除数为零，或者有除数为空白 的单元格 （Excel 把空白单元格也当作 0） 。把除数改为非零的数值，或 者用 IF 函数进行控制。实例#N/A在公式使用查找功能的函数 （VLOOKUP 、 HLOOKUP 、LOOKUP 等）时，找不到匹配的 值。检查被查找的值，使之的确 存在于查找的数据表中的第一 列。实例#NAME?在公式中使用了 Excel 无法识别的文本， 例如函数的名称拼写错误，使用了没有被定义 的区域或单元格名称，引用文本时没有加引号 等。根据具体的公式，逐步分析 出现该错误的可能， 并加以改正。实例#NUM!当公式需要数字型参数时，我们却给了它 一

3、个非数字型参数；给了公式一个无效的参 数；公式返回的值太大或者太小。根据公式的具体情况，逐一分析可能的原因并修正。实例#VALUE文本类型的数据参与了数值运算，函数参 数的数值类型不正确；函数的参数本应该是单一值，却提供了一 个区域作为参数；输入一个数组公式时，忘记按 Ctrl Shift Enter 键。更正相关的数据类型或参数 类型；提供正确的参数； 输入数组公式时，记得使用 Ctrl Shift Enter 键确定。实例#REF!公式中使用了无效的单元格引用。通常如 下这些操作会导致公式引用无效的单元格：删 除了被公式引用的单元格；把公式复制到含有 引用自身的单元格中。避免导致引用无效的

4、操作， 如果已经出现错误，先撤销，然 后用正确的方法操作。实例#NULL!使用了不正确的区域运算符或引用的单元格区域的交集为空。改正区域运算符使之正确； 更改引用使之相交。实例文中选用的实例都是平时出现最多的情况，请大家注意体会。文中图 6 提到的帮助更正错误的智能标 记非常有用，如果利用介绍的方法都还未解决问题时，可以借助它进一步的分析。总之，只要思路正确、 耐心仔细， Excel 的公式错误最后就会被我们一一 “歼灭 ”。一、#DIV/0! 错误常见原因： 如果公式返回的错误值为 “#DIV/0! ，”这是因为在公式中有除数为零，或者有除数为空白的 单元格 （Excel 把空白单元格也当作

5、 0） 。处理方法： 把除数改为非零的数值，或者用 IF 函数进行控制。具体方法请参见下面的实例。具体实例： 如图 1 的所示的工作表，我们利用公式根据总价格和数量计算单价，在 D2 单元格中输入 的公式为 “=B2/C2”，把公式复制到 D6 单元格后， 可以看到在 D4、D5 和 D6 单元格中返回了 “#DIV/0! 错”误 值，原因是它们的除数为零或是空白单元格。假设我们知道 “鼠标 ”的数量为 “6，”则在 C4 单元格中输入 “6，”错误就会消失 (如图 2)。假设我们暂时不知道 “录音机”和“刻录机”的数量，又不希望 D5、D6 单元格中显示错误值，这时可以用 IF 函数进行控制

6、。 在 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 等 )时，找

7、不到匹配的值。处理方法： 检查被查找的值，使之的确存在于查找的数据表中的第一列。具体实例： 在如图 4 所示的工作表中，我们希望通过在 A10 单元格中输入学号，来查找该名同学的英 语成绩。 B10 单元格中的公式为 “=VLOOKUP(A10,A2:E6,5,FALSE”) ，我们在 A10 中输入了学号 “107”由于 这个学号，由于在 A2:A6 中并没有和它匹配的值，因此出现了 “#N/A”错误。如果要修正这个错误，则可以在 A10 单元格中输入一个 A2:A6 中存在的学号，如 “102”，这时错误值 就不见了 (如图 5) 。说明一： 关于公式 “=VLOOKUP(A10,A2:E

8、6,5,FALSE”) 中 VLOOKUP 的第四个参数， 若为 FALSE ，则 表示一定要求完全匹配 lookup_value 的值；若为 TRUE ，则表示如果找不到完全匹配 lookup_value 的值， 就使用小于等于 lookup_value 的最大值。说明二：出现 “#N/A”错误的原因还有其他一些，选中出现错误值的B10 单元格后，会出现一个智能标记，单击这个标记，在弹出的菜单中选择 “关于此错误的帮助 ”如(图 6)，就会得到这个错误的详细分析 ( 如图 7)，通过这些原因和解决方法建议，我们就可以逐步去修正错误，这对其他的错误也适用。三、#NAME? 错误 常见原因：如果

9、公式返回的错误值为 “#NAME”? ，这常常是因为在公式中使用了 Excel 无法识别的文本， 例如函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。处理方法： 根据具体的公式，逐步分析出现该错误的可能，并加以改正，具体方法参见下面的实例。 具体实例： 如图 8 所示的工作表，我们想求出 A1:A3 区域的平均数，在 B4 单元格输入的公式为“ =aveage(A1:A3) ，”回车后出现了 “ #NAME?”错误 (如图 8) ，这是因为函数 “ average错”误地拼写成了 “ aveage，”Excel 无法识别，因此出错。把函数名称拼写正确即可修正错误

10、。选中 C4 单元格， 输入公式 “=AVERAGE(data”) ，回车后也出现了 “#NAME”? 错误 (如图 9)。这是因为在 这个公式中，我们使用了区域名称 data ，但是这个名称还没有被定义，所以出错。改正的方法为：选中 “A1:A3”单元格区域，再选择菜单 “名称 定义 ”命令，打开 “定义名称 ”对话框，在 文本框中输入名称 “data单”击 “确定”按钮 （如图 10）。返回 Excel 编辑窗口后，可以看到错误不见了 （如图 11）选中 D4 单元格， 输入公式 “=IF（A1=12,这个数等于 12,这个数不等于 12）”，回车后出现 “#NAME”? 错误 （如 12

11、） ，原因是引用文本时没有添加引号。修改的方法为：对引用的文本添加上引号，特别注意是英文状态下的引号。于是将公式改为=IF（A1=12,这个数等于 12, 这个数不等于 12） ”如（图 13） 。四、 #NUM! 错误常见原因： 如果公式返回的错误值为 “#NUM”! ，这常常是因为如下几种原因：当公式需要数字型参数 时，我们却给了它一个非数字型参数；给了公式一个无效的参数；公式返回的值太大或者太小。处理方法： 根据公式的具体情况，逐一分析可能的原因并修正。具体实例： 在如图 14 所示的工作表中，我们要求数字的平方根，在B2 中输入公式 “=SQRT(A2”) 并复制到 B4 单元格，由于

12、 A4 中的数字为 “ 16”，不能对负数开平方， 这是个无效的参数， 因此出现了 “#NUM”! 错误。修改的方法为把负数改为正数即可。五、#VALUE 错误常见原因： 如果公式返回的错误值为 “#VALU”E ，这常常是因为如下几种原因： 文本类型的数据参与了 数值运算，函数参数的数值类型不正确；函数的参数本应该是单一值，却提供了一个区域作为参数；输入 一个数组公式时，忘记按 Ctrl Shift Enter 键。处理方法： 更正相关的数据类型或参数类型； 提供正确的参数； 输入数组公式时， 记得使用 Ctrl Shift Enter 键确定。具体实例： 如图 15 的工作表， A2 单元

13、格中的 “壹佰”是文本类型的，如果在 B2 中输入公式 “=A2*2”， 就把文本参与了数值运算，因此出错。改正方法为把文本改为数值即可。图 16 中，在 A8 输入公式 “ =SQRT(A5:A7”) ，对于函数 SQRT ，它的参数必须为单一的参数，不能为 区域，因此出错。改正方法为修改参数为单一的参数即可。如图 17 的工作表，如果要想用数组公式直接求出总价值，可以在 E8 单元格中输入公式“ =SUM(C3:C7*D3:D7) ”，注意其中的花括号不是手工输入的，而是当输入完成后按下CtrlShift Enter键后， Excel 自动添加的。如果输入后直接用 Enter 键确定，则会

14、出现 “#VALU”E 错误。修改的方法为：选中 E8 单元格后激活公式栏，按下 Ctrl Shift Enter 键即可，这时可以看到 Excel 自动添加了花括号 (如图 18) 。六、#REF! 错误常见原因： 如果公式返回的错误值为 “#REF!”，这常常是因为公式中使用了无效的单元格引用。通常如下这些操作会导致公式引用无效的单元格：删除了被公式引用的单元格；把公式复制到含有引用自身的单 元格中。处理方法： 避免导致引用无效的操作，如果已经出现错误，先撤销，然后用正确的方法操作。具体实例： 如图 19 的工作表，我们利用公式将代表日期的数字转换为日期，在 B2 中输入了公式 “ =DA

15、TE（LEFT（A2,4）,MID（A2,5,2）,RIGHT（A2,2） 并”复制到 B4 单元格。这时如果把 A2:A4 单元格删除，则会出现 “#REF!”错误（如图 20） ，这是因为删除了公式中引用的单元 格。（图片较大，请拉动滚动条观看） 先执行“撤消 删除”命令，然后复制 B2:B4 单元格区域到 A2:A4 ，也会出现 “#REF!”错误（如图 21），这 是因为把公式复制到了含有引用自身的单元格中。（图片较大，请拉动滚动条观看）由于这时已经不能撤销， 所以我们先把 A2:A4 中的数据删除， 然后设置单元格格式为 “常规”，在 A2:A4 中输入如图 19 所示的数据。为了得

16、到转换好的日期数据，正确的操作方法为：先把 B2:B4 复制到一个恰当的地方，如 D2:D4 ，粘 贴的时候执行选择性粘贴，把 “数值”粘贴过去。这时 D2:D4 中的数据就和 A列及 B列数据“脱离关系 ”了， 再对它们执行删除操作就不会出错了 （如图 22） 。说明：要得到图 22 的效果，需要设置 D2:D4 的格式为 “日期 ”。七、#NULL! 错误导致原因： 如果公式返回的错误值为 “#NULL!”，这常常是因为使用了不正确的区域运算符或引用的单 元格区域的交集为空。处理方法： 改正区域运算符使之正确；更改引用使之相交。具体实例： 如图 23 所示的工作表中， 如果希望对 A1:A10 和 C1:C10 单元格区域求和， 在 C11 单元格 中输入公式 “=SUM(A1:A10 C1:C10)”