Excel中常见的错误值和解决方法Word格式.docx
《Excel中常见的错误值和解决方法Word格式.docx》由会员分享，可在线阅读，更多相关《Excel中常见的错误值和解决方法Word格式.docx（21页珍藏版）》请在冰豆网上搜索。
Excel2007之前的Excel版本，您可以使用IF（ISERROR（））方法︰=IF（ISERROR（A2/A3）,0,A2/A3）（请参阅函数）。
注意︰IFERROR和IF（ISERROR（））方法是总错误处理程序，他们将取消显示所有错误，而不仅仅是#DIV/0！
。
您需要确保公式的工作方式正确应用的任何错误处理之前，否则可能无法实现按预期方式公式无法正常工作。
如何更正###错误
如果列因为不够宽而无法显示单元格的所有内容，MicrosoftExcel可能在单元格中显示“#####”。
将日期和时间返回为负值的公式也可能显示为“#####”。
要增加列宽以显示所有单元格内容，请双击列标题的右边缘，或将其拖动到所需宽度。
您也可以尝试下列途径：
∙要缩小单元格内容，请单击“对齐方式”旁边的“开始”>
“
”，然后选中“设置单元格格式”对话框内的“缩小字体填充”框。
∙如果数字的小数位数太多，请单击“开始”>
“减少小数位数”。
∙如果日期太长，请单击“开始”>
“数字格式”旁边的箭头，然后选择“短日期”。
更正负日期或时间
如果由于单元格有负日期或时间值而导致Excel显示“#####”，请确保：
∙使用1900日期系统时验证日期和时间为正值。
∙使用公式正确减去日期或时间以避免负日期或时间值结果。
∙将格式设置为日期或时间的负公式结果更改为其他数字格式。
如何更正#N/A错误
#N/A错误通常表示公式找不到要求查找的内容。
原因
∙查阅值不存在：
VLOOKUP、HLOOKUP、LOOKUP或MATCH函数的#N/A错误的最常见原因是公式找不到引用值。
例如，源数据中不存在查阅值。
源数据中找不到项
在此情况下，查阅表格中未列有“Banana”，因此VLOOKUP返回#N/A错误。
解决方案：
可确保源数据中存在查阅值，或在公式中使用IFERROR等错误处理程序。
例如，=IFERROR（FORMULA（）,0），即：
=IF（公式求值错误，显示0，其他情况显示公式的结果）
可使用“”不显示内容，或替换你自己的文本：
=IFERROR（FORMULA（）,”ErrorMessagehere”）
注释:
IFERROR仅适用于Excel2007和更高版本。
对于早期版本的Excel，你可以使用其中一种IS函数。
管理#N/A的常见方法是使用ISNA（）函数︰IF（ISNA（FORMULA（））,0,FORMULA（））.
=IF（ISNA（FORMULA（））,0,FORMULA（）））
但是，请注意ISNA会强制计算两次公式，第一次计算公式是为了确定是否导致错误，如果公式未计算错误，第二次则是显示公式。
如果工作簿中有大量公式使用此方法，则会导致巨大开销。
∙不正确的值类型：
查阅值和源数据是不同数据类型。
例如，你尝试让VLOOKUP引用数字，但源数据却存储为文本。
不同数据类型导致的#N/A错误
确保数据类型相同。
可通过选择单元格或单元格范围查看单元格格式，然后右键单击并选择“格式化单元格”>
“数字”（或按Ctrl+1），如有需要，可更改数字格式。
提示:
如果需要对整列强制更改格式，首先应用所需的格式，然后可使用“数据”>
“文本分列”>
“完成”。
∙单元格中有多余的空格：
可使用TRIM函数删除前导或尾随空格。
以下示例使用嵌套在VLOOKUP函数中的TRIM删除A2:
A7中的名称的前导空格，然后返回部门名称。
注意:
这是数组公式，必须使用Ctrl+Shift+Enter输入。
Excel将用括号{}自动将公式括起来。
如果尝试自己输入，Excel会将公式作为文本显示。
{=VLOOKUP（D2,TRIM（A2:
B7）,2,FALSE）}
∙使用近似匹配与精确匹配方法的对比（TRUE/FALSE）-默认情况下，查阅表格中信息的函数必须按升序排列。
但是VLOOKUP和HLOOKUP工作表函数包括range_lookup参数，此参数命令函数即使在未对表格排序的情况下，也要查找精确匹配。
若要查找精确匹配，请将range_lookup参数设置为FALSE。
请注意：
如果使用TRUE（表示命令函数查找近似匹配），不仅会导致#N/A错误，还会返回如以下示例显示的错误结果。
由于在未排序的表格中使用近似匹配参数，VLOOKUP失败
在此示例中，不仅“香蕉”返回#N/A错误，而且“梨”也返回错误价格。
导致这种结果的原因是使用TRUE参数，此参数命令VLOOKUP查找近似匹配而非精确匹配。
“香蕉”没有接近的匹配，而“梨”的字母顺序比“桃”靠前。
在这种情况下，将VLOOKUP与FALSE参数配合使用将返回“梨”的正确价格，但“香蕉”仍导致#N/A错误，因为查阅列表中没有对应的“香蕉”。
如果使用的是MATCH函数，请尝试更改match_type参数的值以指定表格的排序顺序。
若要查找精确匹配项，请将match_type参数设置为0（零）。
∙数组公式引用的区域中的行数或列数与数组公式所在区域中的行数或列数不相同
若要修复此错误，请确保数组公式引用的区域中的行数和列数与其中输入了数组公式的单元格区域中的行数和列数相同，或在更少或更多单元格中输入该数组公式以匹配公式中的区域引用。
在此示例中，单元格E2引用了不匹配的范围：
{=SUM（IF（A2:
A11=D2,B2:
B5））}
要使公式正确计算，需要对其进行更改，使两个范围反映行2–11。
B11））}
∙如果因为缺少数据而在单元格中手动输入了#N/A或NA（），那么只要一有实际数据，则应替换为实际数据。
在此之前，引用这些单元格的公式不能计算值，因此将改为返回#N/A错误。
在此情况下，五月到十二月有#N/A值，所以“总计”无法计算，而是返回#N/A错误。
∙使用预定义或用户定义的函数的公式缺少一个或多个必需参数。
若要修复此错误，请检查所用公式的函数语法，然后在返回了错误的公式中输入所有必需参数。
这可能需要使用VisualBasic编辑器（VBE）检查函数。
可从“开发工具”选项卡，或使用ALT+F11访问VBE。
∙输入的用户定义函数不可用。
若要修复此错误，请验证包含用户定义函数的工作簿是否已打开，以及该函数是否工作正常。
∙运行的宏所用函数返回了#N/A
若要修复此错误，请验证该函数中的参数是否正确，以及是否用在正确的位置中。
∙编辑包含CELL等函数的受保护文件，然后单元格的内容变为N/A错误
若要修复此错误，请按Ctr+Alt+F9以重新计算工作表
理解函数的参数时是否需要帮助？
如果不确定是否是恰当的参数，可使用函数向导获得帮助。
选择具有问题公式的单元格，转到功能区上的“公式”选项卡，按“插入函数”。
Excel将为你自动加载向导：
单击每个参数时，Excel将为你提供每个参数的相应信息。
对图表使用#N/A
#N/A可能很有用！
使用如下图表示例时，使用#N/A是通常做法，因为#N/A值不会绘制在图表上。
下面是分别使用0和#N/A时，图表的外观的示例。
在上一个示例中，你将看到已绘制0值，并在图表的底部显示为平整线，然后迅速增长以显示“总计”。
在下面的示例中，你将看到0值替换为#N/A。
如何更正#NAME?
错误
MicrosoftExcel无法识别公式中的名称或文本时，将显示此错误。
以下是导致显示#NAME?
错误的部分公式问题：
∙公式引用了不存在的名称。
若要修复此错误，请确保公式中所用名称确实存在：
a.单击“公式”>
“名称管理器”。
b.验证其中是否列出了该名称。
如果未列出该名称，请关闭该对话框，然后定义该名称（“公式”>
“定义名称”）。
∙公式引用的名称拼写不正确。
若要修复此错误，请将该引用替换为正确拼写的名称：
c.选择包含您要检查拼写错误的名称的公式的单元格。
d.在编辑栏中，选择公式中拼写错误的名称，然后按F3键。
e.在“粘贴名称”框中，单击要使用的名称，然后单击“确定”。
∙公式中所用函数的名称拼写不正确。
若要修复此错误，请在公式中插入正确的函数名称：
f.选择包含使用拼写错误的函数的公式的单元格。
g.在编辑栏中，选择拼写错误的函数名称。
h.在“名称框”（位于编辑栏左侧）中，单击箭头并选择正确名称的函数。
单击“其他函数”以查看所有函数（如果需要）。
i.检查参数值，然后单击“确定”完成公式。
∙公式中包含的某个文本项缺少括起双引号（"
若要修复此错误，请在公式中该文本项的两侧输入双引号。
例如，在组合使用文本和单元格B50:
的公式="
Thetotalamountis"
&
B50中，
∙区域引用中遗漏了冒号（:
若要修复此错误，请确保公式中的所有区域引用都有冒号（:
例如，SUM（A1:
C10）。
∙对另一个工作表的引用未用单引号（'
）引起来。
若要修复此错误，请确保在公式中工作表引用两侧输入单引号（'
在公式中，始终需要使用单引号将对以下对象的引用括起来：
其他工作表或工作簿中的值或单元格、以及其他工作表或工作簿的包含非字母字符或空格的名称。
∙在公式中使用了EUROCONVERT函数，但Excel附带的“欧元工具”加载项未启用。
若要修复此错误，请启用该加载项：
j.单击“文件”>
“选项”。
k.单击“加载项”。
l.在“管理”列表框中，选择“Excel加载项”，然后单击“转到”。
m.选中“欧元工具”框，然后单击“确定”。
如何更正#NULL!
如果在公式中使用了不正确的区域运算符，或者在区域引用之间使用了交叉运算符（空格字符）来指定不相交的两个区域的交集，将显示此错误。
交集是工作表中数据跨两个或多个区域的点。
如果您使用了错误的区域运算符，请确保使用：
∙冒号（:
）来分隔第一个单元格和最后一个单元格（如果在公式中引用了连续的单元格区域）。
A10）引用其中包含单元格A1到单元格A10的区域。
∙逗号（,）来充当联合运算符（如果引用不相交的两个区域）。
例如，如果公式是对两个区域求和，请确保使用逗号来分隔这两个区域（SUM（A1:
A10,C1:
C10））。
如果因为在不相交的区域之间使用了空格而导致发生此错误，请更改引用，以便让区域相交。
例如，在公式=CELL（“address”,（A1:
A5C1:
C3））中，区域A1:
A5和C1:
C3不相交，所以该公式返回#NULL!
如果将该公式更改为=CELL（"
address"
（A1:
A5A3:
C3）），CELL函数将返回这两个区域的相交单元格地址，即单元格A3。
如何更正#NUM！
公式或函数中包含无效数值时，Excel会显示此错误。
如果在公式的参数部分中输入的数值所用数据类型或数字格式不受支持，通常会出现此错误。
例如，不能输入类似$1,000的货币格式值，因为在公式中，美元符号用作绝对引用标记，而逗号则用作分隔符。
若要避免#NUM!
错误，请改为输入未设置格式的数字形式的值，如1000。
以下情况下，Excel也可能会显示#NUM!
错误：
∙公式使用了迭代函数（如IRR或RATE），但无法找到结果。
若要修复此错误，请更改Excel对公式执行迭代的次数：
a.单击文件>
选项。
如果您使用的Excel2007中，选择MicrosoftOffice按钮
>
Excel选项。
b.在“公式”选项卡上“计算选项”下，选中“启用迭代计算”框。
c.在“最多迭代次数”框中，键入希望Excel重新计算的次数。
迭代数越大，Excel计算工作表所需时间就越长。
d.在“最大误差”框中，键入可接受的计算结果误差量。
数值越小，结果就越精确，Excel重新计算工作表所需的时间也越长。
∙公式产生的数字过大或过小都无法在Excel中显示。
若要修复此错误，请更改公式，以使其结果介于-1*10307与1*10307之间。
如何更正#REF！
#REF！
错误显示当公式引用的单元格不是有效。
发生这种情况最常时所引用的公式的单元格获取删除，或者通过粘贴。
示例-#REF！
通过删除列导致错误
下面的示例使用公式=SUM（B2,C2,D2）在列e。
如果您要删除列B，C或D会导致##REF！
错误。
在此例中，我们将删除C列（2007年销售额），并且公式现在读取为=SUM（B2,#REF!
C2）。
当您使用显式单元格引用，如下所示（单独引用每个单元格，在其中用逗号分隔）和删除引用的行或列，以便它返回#REFExcel无法解决，！
这是为什么在函数中使用显式单元格引用不建议的主要原因。
解决方案
∙如果您意外地删除行或列，您可以立即单击快速访问工具栏上的撤消按钮（或按CTRL+Z）以将其还原。
∙调整公式，以便它，而不是单个单元格，如=SUM（B2:
D2）使用区域引用。
现在，您可以删除任何列求和范围内的，Excel将自动调整公式。
您还可以使用的行总和=SUM（B2:
B5）。
示例-不正确的区域引用使用VLOOKUP
在下面的示例中，=VLOOKUP（A8,A2:
D5,5,FALSE）将返回##REF！
因为它正在寻找值返回从列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已关闭的工作簿
在下面的示例，INDIRECT函数试图引用的工作簿已关闭，导致##REF！
打开引用的工作簿
OLE/DDE问题
如果您使用了返回##REF对象链接和嵌入（OLE）链接！
错误，然后开始链接调用的程序。
注意︰OLE是一种技术，您可以使用程序之间共享信息。
如果您使用了返回##REF的动态数据交换（DDE）主题！
错误，请确保您正在引用正确的主题。
注意︰DDE是用于Microsoft基于Windows的程序之间交换数据建立的协议。
宏问题
如果宏函数，上面的单元格引用工作表上输入的函数，包含该函数的单元格为第1行中，该函数将返回##REF！
因为没有第1行上方的单元格。
选中要查看是否参数是指单元格或区域的单元格不是有效的函数。
这可能需要编辑的宏在VisualBasic编辑器（VBE）才能考虑这种情况。
如何更正#VALUE!
#VALUE!
错误的最常见原因是公式需要单元格中的数字，却找到空格、文本或其他字符。
以下是一些可能会对你有所帮助的解决方案。
请尝试使用函数，而不是运算符
具有+、-、*和/等数学运算符的公式可能无法计算包含文本或空格的单元格。
在这种情况下，请尝试改为使用函数。
函数通常将忽略文本值，并将所有内容表述为数字。
使用计算公式向导查找公式断开的位置
如果你的公式引发了#VALUE!
错误，但你不知道错误在哪里，可以使用计算公式向导。
选择包含错误的单元格，然后转到“公式”>
“公式求值”，将显示交互式对话框：
“公式求值”对话框
每次单击“计算”按钮，Excel将单步执行公式并分别计算每个部分，然后显示结果。
这一操作不会修复你的公式，但可帮助你确定中断的位置，这样你就可以修复它。
在这种情况下，公式=A2+B2+C2由于单元格A2中的前导空格而中断，但你看不到它。
但计算公式向导能够看到，它将显示值为="
"
+B2+C2，其中"
表示空白。
检查单元格内的隐藏空格
有时单元格看起来是空白，实际上可能具有隐藏空格。
双击公式所引用的单元格（或按F2），然后检查空格。
在下图中，单元格A2中的光标左侧存在多余空格。
尝试删除空格，或选中单元格并按Delete，查看错误是否消失。
如果要检查多行，可在空列中使用ISBLANK（）函数以了解单元格是否真正空白。
在下图中，单元格A2中具有看不到的隐藏空格，E2中的ISBLANK函数返回FALSE。
而A3真正空白，因此ISBLANK函数返回TRUE。
当单元格中具有隐藏空格时，ISBLANK返回FALSE。
提示：
使用自动筛选批量删除空白单元格。
可单击“数据”>
“筛选”以在包含空白的列中“筛选空白”（自动筛选将把具有前导空格的单元格也视为空白）。
接下来，选择整列，然后按Ctrl+G（“转到”）>
“选择性”>
“仅可见单元格”，然后按Delete以清除所有内容。
使用自动筛选以删除空白单元格
在列标题上使用筛选器，以检查该列中是否存在任何空白单元格
使用CLEAN函数清除文本
有时当你从外部源导入数据时，数据可能带着非打印字符（如^或'
）进入Excel。
遗憾的是，这些字符可能会导致公式中出现问题，并且可能很难删除它们。
但是，可以使用CLEAN函数来去除这些字符，并将值从文本转换为数字。
然后，可以复制CLEAN函数区域，仅将单元格值粘贴回原始区域。
方法如下：
选择CLEAN函数区域，然后按Ctrl+C。
选择原始单元格，然后单击“开始”>
“粘贴”>
“选择性粘贴”>
“值”（对于PC，可以使用键盘快捷方式Alt>
E>
S>
V，对于MAC，可以使用
+option+V>
V>
enter）。
CLEAN函数将删除单元格中的非打印字符
使用TRIM函数清除文本
有时前导或尾随空格可能导致问题。
Excel通常将尝试删除简单值中的这些空格。
例如，如果你键入“123”，Excel将删除空格，但可能无法对“1/1/16”等日期值执行相同操作。
在这种情况下，你可以使用TRIM函数删除这些前导空格和尾随空格，然后在你的计算中引用转换后的TRIM值。
或使用“复制”>
“值”的方法替换原始值。
在此示例中，A2中日期中的前导空格将导致=B2-A2出现#VALUE!
但使用=TRIM（A2）后，我们就可以使用=B2-D2解决该错误。
TRIM函数将删除单元格内的多余空格
检查公式是否引用了包含文本的单元格
右键单击公式引用的单元格，然后单击“设置单元格格式”（或使用Ctrl+1）。
确保格式不为“文本”。
有时此操作对于多个单元格并不适用，因此如果要检查多个单元格，请插入新列，然后使用ISTEXT（）函数查看是否已将单元格格式设置为“文本”。
在下面的示例中，“1730”不是有效数字，因此Excel会将其视为文本。
这常常是拼写错误造成的。
请注意，ISTEXT不会解决该错误，它仅会告诉你是不是文本导致了问题。
ISTEXT函数可确定公式是否引用了包含文本的单元格
你的工作簿使用了不可用的数据连接
若要修复此错误，请还原数据连接，或考虑导入数据（如果可以）。
将工作簿发送给没有数据连接的用户时，分发的工作簿中可能经常出现这种情况。
在这样的情况下，建议创建一个分发副本，复制整个工作簿并在其中使用“粘贴”>
“值”（此操作将清除公式和链接）。
使用其他内容替换#VALUE!
有时你只是想要将#VALUE错误替换其他内容，如自己的文本、零或空白单元格。
在这种情况下，你可以将IFERROR（）函数添加到公式。
IFERROR（）将检查是否存在错误，如果存在，则将其替换为你选择的另一个值。
如果没有错误，将计算原始公式。
对于早期版本，可以使用IF（ISERROR（））。
提醒：
IFERROR是综合错误处理程序，这意味着它将抑制所有错误，而不仅仅是#VALUE!
不建议使用IFERROR，除非你确信公式以你希望的方式执行。
否则，你将无法看到可能会指示你遇到问题的潜在的有价值的错误消息。
下面是具有由于单元格D2中的前导空格而导致的#VALUE!
错误的公式的示例：
由单元格中的空格导致的#VALUE!
下面是同一个示例，其中使用了IFERROR将#VALUE!
替换为零：
IFERROR函数让你可以将#V
升级会员 