Excel中常见的错误值和解决方法Word格式.docx

1、Excel 2007 之前的 Excel 版本，您可以使用 IF（ISERROR（） 方法=IF（ISERROR（A2/A3）,0,A2/A3）（请参阅函数）。注意 IFERROR 和 IF（ISERROR（） 方法是总错误处理程序，他们将取消显示所有错误，而不仅仅是 #DIV/0 ！。您需要确保公式的工作方式正确应用的任何错误处理之前，否则可能无法实现按预期方式公式无法正常工作。如何更正 # # # 错误如果列因为不够宽而无法显示单元格的所有内容，Microsoft Excel 可能在单元格中显示“#”。将日期和时间返回为负值的公式也可能显示为“#”。要增加列宽以显示所有单元格内容，请双击列

2、标题的右边缘，或将其拖动到所需宽度。您也可以尝试下列途径： 要缩小单元格内容，请单击“对齐方式”旁边的“开始”“”，然后选中“设置单元格格式”对话框内的“缩小字体填充”框。 如果数字的小数位数太多，请单击“开始”“减少小数位数”。 如果日期太长，请单击“开始”“数字格式”旁边的箭头，然后选择“短日期”。更正负日期或时间如果由于单元格有负日期或时间值而导致 Excel 显示“#”，请确保： 使用 1900 日期系统时验证日期和时间为正值。 使用公式正确减去日期或时间以避免负日期或时间值结果。 将格式设置为日期或时间的负公式结果更改为其他数字格式。如何更正 #N/A 错误#N/A 错误通常表示公式

3、找不到要求查找的内容。原因 查阅值不存在：VLOOKUP、HLOOKUP、LOOKUP 或 MATCH 函数的 #N/A 错误的最常见原因是公式找不到引用值。例如，源数据中不存在查阅值。源数据中找不到项在此情况下，查阅表格中未列有“Banana”，因此 VLOOKUP 返回 #N/A 错误。解决方案：可确保源数据中存在查阅值，或在公式中使用 IFERROR 等错误处理程序。例如，=IFERROR（FORMULA（）,0），即：= IF（公式求值错误，显示 0，其他情况显示公式的结果）可使用“”不显示内容，或替换你自己的文本：=IFERROR（FORMULA（）,”Error Message h

4、ere”）注释:IFERROR 仅适用于 Excel 2007 和更高版本。对于早期版本的 Excel，你可以使用其中一种 IS 函数。管理 #N/A 的常见方法是使用 ISNA（） 函数IF（ISNA（FORMULA（）,0,FORMULA（）.=IF（ISNA（FORMULA（）,0,FORMULA（） 但是，请注意 ISNA 会强制计算两次公式，第一次计算公式是为了确定是否导致错误，如果公式未计算错误，第二次则是显示公式。如果工作簿中有大量公式使用此方法，则会导致巨大开销。 不正确的值类型：查阅值和源数据是不同数据类型。例如，你尝试让 VLOOKUP 引用数字，但源数据却存储为文本。不同

5、数据类型导致的 #N/A 错误确保数据类型相同。可通过选择单元格或单元格范围查看单元格格式，然后右键单击并选择“格式化单元格”“数字”（或按 Ctrl+1），如有需要，可更改数字格式。提示:如果需要对整列强制更改格式，首先应用所需的格式，然后可使用“数据”“文本分列”“完成”。 单元格中有多余的空格：可使用TRIM函数删除前导或尾随空格。以下示例使用嵌套在 VLOOKUP 函数中的 TRIM 删除 A2:A7 中的名称的前导空格，然后返回部门名称。注意:这是数组公式，必须使用 Ctrl+Shift+Enter 输入。Excel 将用括号 自动将公式括起来。如果尝试自己输入，Excel 会将公式

6、作为文本显示。=VLOOKUP（D2,TRIM（A2:B7）,2,FALSE） 使用近似匹配与精确匹配方法的对比 （TRUE/FALSE） - 默认情况下，查阅表格中信息的函数必须按升序排列。但是 VLOOKUP 和 HLOOKUP 工作表函数包括range_lookup参数，此参数命令函数即使在未对表格排序的情况下，也要查找精确匹配。若要查找精确匹配，请将range_lookup参数设置为 FALSE。请注意：如果使用 TRUE（表示命令函数查找近似匹配），不仅会导致 #N/A 错误，还会返回如以下示例显示的错误结果。由于在未排序的表格中使用近似匹配参数，VLOOKUP 失败在此示例中，不仅

7、“香蕉”返回 #N/A 错误，而且“梨”也返回错误价格。导致这种结果的原因是使用 TRUE 参数，此参数命令 VLOOKUP 查找近似匹配而非精确匹配。“香蕉”没有接近的匹配，而“梨”的字母顺序比“桃”靠前。在这种情况下，将 VLOOKUP 与 FALSE 参数配合使用将返回“梨”的正确价格，但“香蕉”仍导致 #N/A 错误，因为查阅列表中没有对应的“香蕉”。如果使用的是 MATCH 函数，请尝试更改match_type参数的值以指定表格的排序顺序。若要查找精确匹配项，请将match_type参数设置为0（零）。 数组公式引用的区域中的行数或列数与数组公式所在区域中的行数或列数不相同若要修复此

8、错误，请确保数组公式引用的区域中的行数和列数与其中输入了数组公式的单元格区域中的行数和列数相同，或在更少或更多单元格中输入该数组公式以匹配公式中的区域引用。在此示例中，单元格 E2 引用了不匹配的范围：=SUM（IF（A2:A11=D2,B2:B5）要使公式正确计算，需要对其进行更改，使两个范围反映行 2 11。B11） 如果因为缺少数据而在单元格中手动输入了 #N/A 或 NA（），那么只要一有实际数据，则应替换为实际数据。在此之前，引用这些单元格的公式不能计算值，因此将改为返回 #N/A 错误。在此情况下，五月到十二月有 #N/A 值，所以“总计”无法计算，而是返回 #N/A 错误。 使用

9、预定义或用户定义的函数的公式缺少一个或多个必需参数。若要修复此错误，请检查所用公式的函数语法，然后在返回了错误的公式中输入所有必需参数。这可能需要使用 Visual Basic 编辑器 （VBE） 检查函数。可从“开发工具”选项卡，或使用 ALT+F11 访问 VBE。 输入的用户定义函数不可用。若要修复此错误，请验证包含用户定义函数的工作簿是否已打开，以及该函数是否工作正常。 运行的宏所用函数返回了 #N/A若要修复此错误，请验证该函数中的参数是否正确，以及是否用在正确的位置中。 编辑包含 CELL 等函数的受保护文件，然后单元格的内容变为 N/A 错误若要修复此错误，请按 Ctr+Alt+

10、F9 以重新计算工作表理解函数的参数时是否需要帮助？如果不确定是否是恰当的参数，可使用函数向导获得帮助。选择具有问题公式的单元格，转到功能区上的“公式”选项卡，按“插入函数”。Excel 将为你自动加载向导：单击每个参数时，Excel 将为你提供每个参数的相应信息。对图表使用 #N/A#N/A 可能很有用！使用如下图表示例时，使用 #N/A 是通常做法，因为 #N/A 值不会绘制在图表上。下面是分别使用 0 和 #N/A 时，图表的外观的示例。在上一个示例中，你将看到已绘制 0 值，并在图表的底部显示为平整线，然后迅速增长以显示“总计”。在下面的示例中，你将看到 0 值替换为 #N/A。如何更

11、正 #NAME? 错误Microsoft Excel 无法识别公式中的名称或文本时，将显示此错误。以下是导致显示 #NAME? 错误的部分公式问题： 公式引用了不存在的名称。若要修复此错误，请确保公式中所用名称确实存在：a. 单击“公式”“名称管理器”。b. 验证其中是否列出了该名称。如果未列出该名称，请关闭该对话框，然后定义该名称（“公式”“定义名称”）。 公式引用的名称拼写不正确。若要修复此错误，请将该引用替换为正确拼写的名称：c. 选择包含您要检查拼写错误的名称的公式的单元格。d. 在编辑栏中，选择公式中拼写错误的名称，然后按 F3 键。e. 在“粘贴名称”框中，单击要使用的名称，然后单

12、击“确定”。 公式中所用函数的名称拼写不正确。若要修复此错误，请在公式中插入正确的函数名称：f. 选择包含使用拼写错误的函数的公式的单元格。g. 在编辑栏中，选择拼写错误的函数名称。h. 在“名称框”（位于编辑栏左侧）中，单击箭头并选择正确名称的函数。单击“其他函数”以查看所有函数（如果需要）。i. 检查参数值，然后单击“确定”完成公式。 公式中包含的某个文本项缺少括起双引号 （若要修复此错误，请在公式中该文本项的两侧输入双引号。例如，在组合使用文本和单元格 B50: 的公式 =The total amount is &B50 中， 区域引用中遗漏了冒号 （:若要修复此错误，请确保公式中的所有

13、区域引用都有冒号 （:例如，SUM（A1:C10）。 对另一个工作表的引用未用单引号 （） 引起来。若要修复此错误，请确保在公式中工作表引用两侧输入单引号 （在公式中，始终需要使用单引号将对以下对象的引用括起来：其他工作表或工作簿中的值或单元格、以及其他工作表或工作簿的包含非字母字符或空格的名称。 在公式中使用了 EUROCONVERT 函数，但 Excel 附带的“欧元工具”加载项未启用。若要修复此错误，请启用该加载项：j. 单击“文件”“选项”。k. 单击“加载项”。l. 在“管理”列表框中，选择“Excel 加载项”，然后单击“转到”。m. 选中“欧元工具”框，然后单击“确定”。如何更正

14、 #NULL!如果在公式中使用了不正确的区域运算符，或者在区域引用之间使用了交叉运算符（空格字符）来指定不相交的两个区域的交集，将显示此错误。交集是工作表中数据跨两个或多个区域的点。如果您使用了错误的区域运算符，请确保使用： 冒号 （:） 来分隔第一个单元格和最后一个单元格（如果在公式中引用了连续的单元格区域）。A10） 引用其中包含单元格 A1 到单元格 A10 的区域。 逗号 （,） 来充当联合运算符（如果引用不相交的两个区域）。例如，如果公式是对两个区域求和，请确保使用逗号来分隔这两个区域 （SUM（A1:A10,C1:C10）。如果因为在不相交的区域之间使用了空格而导致发生此错误，请更

15、改引用，以便让区域相交。例如，在公式 =CELL（“address”,（A1:A5 C1:C3） 中，区域 A1:A5 和 C1:C3 不相交，所以该公式返回 #NULL!如果将该公式更改为 =CELL（address,（A1:A5 A3:C3），CELL 函数将返回这两个区域的相交单元格地址，即单元格 A3。如何更正 #NUM ！公式或函数中包含无效数值时，Excel 会显示此错误。如果在公式的参数部分中输入的数值所用数据类型或数字格式不受支持，通常会出现此错误。例如，不能输入类似$1,000的货币格式值，因为在公式中，美元符号用作绝对引用标记，而逗号则用作分隔符。若要避免 #NUM! 错误

16、，请改为输入未设置格式的数字形式的值，如 1000。以下情况下，Excel 也可能会显示 #NUM! 错误： 公式使用了迭代函数（如IRR或RATE），但无法找到结果。若要修复此错误，请更改 Excel 对公式执行迭代的次数：a. 单击文件选项。如果您使用的 Excel 2007 中，选择Microsoft Office 按钮Excel 选项。b. 在“公式”选项卡上“计算选项”下，选中“启用迭代计算”框。c. 在“最多迭代次数”框中，键入希望 Excel 重新计算的次数。迭代数越大，Excel 计算工作表所需时间就越长。d. 在“最大误差”框中，键入可接受的计算结果误差量。数值越小，结果就越

17、精确，Excel 重新计算工作表所需的时间也越长。 公式产生的数字过大或过小都无法在 Excel 中显示。若要修复此错误，请更改公式，以使其结果介于 -1*10307 与 1*10307 之间。如何更正 #REF ！#REF ！错误显示当公式引用的单元格不是有效。发生这种情况最常时所引用的公式的单元格获取删除，或者通过粘贴。示例-#REF ！通过删除列导致错误下面的示例使用公式=SUM（B2,C2,D2）在列 e。如果您要删除列 B，C 或 D 会导致 # #REF ！错误。在此例中，我们将删除 C 列（2007年销售额），并且公式现在读取为=SUM（B2,#REF!,C2）。当您使用显式单元

18、格引用，如下所示（单独引用每个单元格，在其中用逗号分隔）和删除引用的行或列，以便它返回 #REF Excel 无法解决，！这是为什么在函数中使用显式单元格引用不建议的主要原因。解决方案 如果您意外地删除行或列，您可以立即单击快速访问工具栏上的撤消按钮（或按 CTRL + Z）以将其还原。 调整公式，以便它，而不是单个单元格，如=SUM（B2:D2）使用区域引用。现在，您可以删除任何列求和范围内的，Excel 将自动调整公式。您还可以使用的行总和=SUM（B2:B5） 。示例-不正确的区域引用使用 VLOOKUP在下面的示例中，=VLOOKUP（A8,A2:D5,5,FALSE）将返回 # #R

19、EF ！因为它正在寻找值返回从列 5，但参考范围错误是 A:D，仅为 4 的列。调整的范围为较大，或减少列查阅值，以匹配参考范围。=VLOOKUP（A8,A2:E5,5,FALSE）将要有效引用区域中，如一样= VLOOKUP（A8,A2:D5,4,FALSE）。示例-具有不正确的行或列引用的索引在此示例中，公式 =INDEX（B2:E5,5,5） 返回 #REF ！错误由于索引范围是通过 4 列 4 行而要求返回第 5 行和第五列中的公式。调整行或列引用，以便它们索引查找区域内。=INDEX（B2:E5,4,4）将返回有效的结果。示例 引用 INDIRECT 已关闭的工作簿在下面的示例，IN

20、DIRECT 函数试图引用的工作簿已关闭，导致 # #REF ！打开引用的工作簿OLE/DDE 问题如果您使用了返回 # #REF 对象链接和嵌入 （OLE） 链接！错误，然后开始链接调用的程序。注意OLE 是一种技术，您可以使用程序之间共享信息。如果您使用了返回 # #REF 的动态数据交换 （DDE） 主题！错误，请确保您正在引用正确的主题。注意DDE 是用于 Microsoft 基于 Windows 的程序之间交换数据建立的协议。宏问题如果宏函数，上面的单元格引用工作表上输入的函数，包含该函数的单元格为第 1 行中，该函数将返回 # #REF ！因为没有第 1 行上方的单元格。选中要查看

21、是否参数是指单元格或区域的单元格不是有效的函数。这可能需要编辑的宏在 Visual Basic 编辑器 （VBE） 才能考虑这种情况。如何更正 #VALUE!#VALUE! 错误的最常见原因是公式需要单元格中的数字，却找到空格、文本或其他字符。以下是一些可能会对你有所帮助的解决方案。请尝试使用函数，而不是运算符具有 +、-、* 和 / 等数学运算符的公式可能无法计算包含文本或空格的单元格。在这种情况下，请尝试改为使用函数。函数通常将忽略文本值，并将所有内容表述为数字。使用计算公式向导查找公式断开的位置如果你的公式引发了 #VALUE! 错误，但你不知道错误在哪里，可以使用计算公式向导。选择包含

22、错误的单元格，然后转到“公式”“公式求值”，将显示交互式对话框：“公式求值”对话框每次单击“计算”按钮，Excel 将单步执行公式并分别计算每个部分，然后显示结果。这一操作不会修复你的公式，但可帮助你确定中断的位置，这样你就可以修复它。在这种情况下，公式=A2+B2+C2由于单元格 A2 中的前导空格而中断，但你看不到它。但计算公式向导能够看到，它将显示值为= +B2+C2，其中 表示空白。检查单元格内的隐藏空格有时单元格看起来是空白，实际上可能具有隐藏空格。双击公式所引用的单元格（或按 F2），然后检查空格。在下图中，单元格 A2 中的光标左侧存在多余空格。尝试删除空格，或选中单元格并按 D

23、elete，查看错误是否消失。如果要检查多行，可在空列中使用 ISBLANK（） 函数以了解单元格是否真正空白。在下图中，单元格 A2 中具有看不到的隐藏空格，E2 中的 ISBLANK 函数返回 FALSE。而 A3 真正空白，因此 ISBLANK 函数返回 TRUE。当单元格中具有隐藏空格时，ISBLANK 返回 FALSE。提示：使用自动筛选批量删除空白单元格。可单击“数据”“筛选”以在包含空白的列中“筛选空白”（自动筛选将把具有前导空格的单元格也视为空白）。接下来，选择整列，然后按Ctrl+G（“转到”）“选择性”“仅可见单元格”，然后按 Delete 以清除所有内容。使用自动筛选以删

24、除空白单元格在列标题上使用筛选器，以检查该列中是否存在任何空白单元格使用 CLEAN 函数清除文本有时当你从外部源导入数据时，数据可能带着非打印字符（如 或 ）进入 Excel。遗憾的是，这些字符可能会导致公式中出现问题，并且可能很难删除它们。但是，可以使用 CLEAN 函数来去除这些字符，并将值从文本转换为数字。然后，可以复制 CLEAN 函数区域，仅将单元格值粘贴回原始区域。方法如下：选择 CLEAN 函数区域，然后按Ctrl+C。选择原始单元格，然后单击“开始”“粘贴”“选择性粘贴”“值”（对于 PC，可以使用键盘快捷方式 Alt E S V，对于 MAC，可以使用+ option +

25、V V enter）。CLEAN 函数将删除单元格中的非打印字符使用 TRIM 函数清除文本有时前导或尾随空格可能导致问题。Excel 通常将尝试删除简单值中的这些空格。例如，如果你键入“ 123”，Excel 将删除空格，但可能无法对“ 1/1/16”等日期值执行相同操作。在这种情况下，你可以使用 TRIM 函数删除这些前导空格和尾随空格，然后在你的计算中引用转换后的 TRIM 值。或使用“复制”“值”的方法替换原始值。在此示例中，A2 中日期中的前导空格将导致= B2-A2出现 #VALUE!但使用=TRIM（A2）后，我们就可以使用=B2-D2解决该错误。TRIM 函数将删除单元格内的多

26、余空格检查公式是否引用了包含文本的单元格右键单击公式引用的单元格，然后单击“设置单元格格式”（或使用 Ctrl+1）。确保格式不为“文本”。有时此操作对于多个单元格并不适用，因此如果要检查多个单元格，请插入新列，然后使用 ISTEXT（） 函数查看是否已将单元格格式设置为“文本”。在下面的示例中，“173 0”不是有效数字，因此 Excel 会将其视为文本。这常常是拼写错误造成的。请注意，ISTEXT 不会解决该错误，它仅会告诉你是不是文本导致了问题。ISTEXT 函数可确定公式是否引用了包含文本的单元格你的工作簿使用了不可用的数据连接若要修复此错误，请还原数据连接，或考虑导入数据（如果可以）

27、。将工作簿发送给没有数据连接的用户时，分发的工作簿中可能经常出现这种情况。在这样的情况下，建议创建一个分发副本，复制整个工作簿并在其中使用“粘贴”“值”（此操作将清除公式和链接）。使用其他内容替换 #VALUE!有时你只是想要将 #VALUE 错误替换其他内容，如自己的文本、零或空白单元格。在这种情况下，你可以将 IFERROR（） 函数添加到公式。IFERROR（） 将检查是否存在错误，如果存在，则将其替换为你选择的另一个值。如果没有错误，将计算原始公式。对于早期版本，可以使用IF（ISERROR（）。提醒：IFERROR 是综合错误处理程序，这意味着它将抑制所有错误，而不仅仅是 #VALUE!不建议使用 IFERROR，除非你确信公式以你希望的方式执行。否则，你将无法看到可能会指示你遇到问题的潜在的有价值的错误消息。下面是具有由于单元格 D2 中的前导空格而导致的 #VALUE! 错误的公式的示例：由单元格中的空格导致的 #VALUE!下面是同一个示例，其中使用了 IFERROR 将 #VALUE! 替换为零：IFERROR 函数让你可以将 #V