有时，公式不仅会返回意外结果，还会产生错误值。 下面是可以用来查找和调查这些错误的原因并确定解决方法的一些工具。
注意
本主题包含可帮助你更正公式错误的方法。 它不是用于纠正所有可能的公式错误的方法的详尽列表。 有关特定错误的帮助，你可在 Excel 社区论坛中搜索或发布你的问题。
了解如何输入简单公式
公式是对工作表中的值执行计算的等式。 公式以等号 (=) 开头。 例如，下面的公式计算 3 加 1 的值。
=3+1
公式还可包含下列所有内容或其中之一：函数、引用、运算符和常量。
公式的各部分
函数：包含在 Excel 中，函数是执行特定计算的工程公式。 例如，PI() 函数返回 pi 的值：3.142...
引用：引用单个单元格或单元格区域。 A2 返回单元格 A2 中的值。
常量：直接输入到公式中的数字或文本值，例如 2。
运算符：^（脱字号）运算符表示数字的乘方，而 *（星号）运算符表示数字相乘。 使用 + 和 - 对值做加减运算，使用 / 做除法运算。
注意
一些函数需要参数。 参数是某些函数用于执行计算的值。 需要时，参数将放在函数的括号 () 之间。 PI 函数不需要任何参数，这就是它为空的原因。 某些函数需要一个或多个参数，并且可以为其他参数留出空间。 需使用逗号或分号 (;) 分隔参数，具体取决于你的位置设置。
例如，SUM 函数仅需一个参数，但总共可包含 255 个参数。
=SUM (A1：A10) 是单个参数的一个示例。
=SUM(A1:A10, C1:C10) 是多个参数的示例。
更正输入公式时的常见错误
下表概括了用户在输入公式时可能会犯的一些最常见错误，并说明了如何更正这些错误。
| 请确保 | 更多信息 |
|---|---|
| 每个函数都以等号 (=) 开头 | 如果省略等号，键入的内容可能会显示为文本或日期。 例如，如果键入 SUM (A1：A10) ，Excel 将显示文本字符串 SUM (A1：A10) ，而不执行计算。 如果键入 11/2，则 Excel 会显示 11-2（假设单元格格式为“常规”），而不是 11 除以 2。 |
| 使所有左括号和右括号相匹配 | 请确保所有括号都成对出现（左括号和右括号）。 在公式中使用函数时，每个括号必须位于正确的位置，这样函数才能正常工作。 例如，公式 =IF (B5<0) ，“Not valid”，B5*1.05) 将不起作用，因为这里有两个右括号而只有一个左括号，而每个括号应该只有一个。 公式应如下所示： =IF (B5<0，“Not valid”，B5*1.05) 。 |
| 用冒号表示区域 | 引用单元格区域时，请使用冒号 (:) 分隔对单元格区域中第一个单元格的引用和对最后一个单元格的引用。 例如，应为 =SUM(A1:A5)，而不是 =SUM(A1 A5)，后者将返回 #NULL! 错误。 |
| 输入所有必需参数 | 有些函数包含必需的参数。 此外，还要确保没有输入过多的参数。 |
| 输入正确类型的参数 | 有些函数（例如 SUM）要求使用数值参数。 而有些函数（例如 REPLACE）则要求其至少有一个参数为文本值。 如果使用错误类型的数据作为参数，Excel 可能会返回意外的结果或显示错误。 |
| 函数的嵌套不超过 64 层 | 可以在某个函数中输入或嵌套不超过 64 层的函数。 |
| 将其他工作表名称包含在单引号中 | 如果公式引用其他工作表或工作簿中的值或单元格，而另一个工作簿或工作表的名称包含空格或非字母字符，则必须将其名称括在单引号 (“ ) ，如 ='Quarterly Data'！D3，或 ='123'！解答 1： |
| 在公式中引用工作表名称时，在其后放置一个感叹号 (!) | 例如，若要在同一工作簿中名为 Quarterly Data 的工作表中返回单元格 D3 的值，请使用此公式：='Quarterly Data'!D3。 |
| 包含外部工作簿的路径 | 请确保每个外部引用都包含工作簿的名称和路径。 对工作簿的引用包括该工作簿的名称且必须用方括号括起来 ([Workbookname.xlsx])。 此引用还必须包含工作簿中相应工作表的名称。 如果要引用的工作簿在 Excel 中未打开，您仍然可以在公式中包含对此工作簿的引用。 您可以提供此文件的完整路径，示例如下：=ROWS('C:\My Documents\[Q2 Operations.xlsx]Sales'!A1:A8)。 此公式将返回另一工作簿中 A1 至 A8 单元格区域内的行数 (8)。 注意： 如果完整路径包含空格字符，如上例所示，必须将路径括在单引号中， (在路径的开头和工作表名称之后的感叹号) 之前。 |
| 输入无格式的数字 | 在公式中输入数字时不要设置数字格式。 例如，如果要输入的值为 ￥1,000，则在公式中输入 1000。 如果你在数字中输入逗号，Excel 会将其视作分隔符。 如果希望显示数字，以便它们显示千位、百万位分隔符或货币符号，请在输入数字之后设置单元格格式。 例如，如果要将 3100 加到单元格 A3 中的值，并且输入公式 =SUM (3,100，A3) ，则 Excel 将数字 3 和 100 相加，然后将该总和加到 A3 中的值，而不是将 3100 加到 A3，即 =SUM (3100，A3) 。 或者，如果输入公式 =ABS (-2,134) ，Excel 将显示错误，因为 ABS 函数只接受一个参数： =ABS (-2134) 。 |
更正公式中的常见问题
你可实施某些规则来检查公式中的错误。 这些规则并不能保证工作表中不存在错误，但它们有助于发现常见的错误。 你可以单独打开或关闭其中的任何规则。
可以通过以下两种方式标记和更正错误：依次标记和更正（类似于拼写检查器），或者在输入数据时在工作表中即时标记和更正。
可以使用 Excel 显示的选项来解决错误，也可以通过选择 “忽略错误”来忽略错误。 如果忽略特定单元格中的某个错误，则该单元格中的该错误就不会再出现在以后的错误检查中。 但是，你可以重置以前忽略的所有错误以使其重新出现。
打开或关闭错误检查规则
对于 Windows 上的 Excel，请转到“ 文件>”、“选项>”、“公式”或
对于 Excel on Mac，请选择 Excel 菜单 > “首选项 > ”错误检查“。在“错误检查”中，选中“启用后台错误检查”。 发现的任何错误都将在单元格左上角用三角形标记。
若要更改标记错误发生位置的三角形的颜色，请在“使用此颜色标识错误”框中，选择所需的颜色。
在“Excel 检查规则”下，选择或清除以下任意规则所对应的复选框：
包含导致错误的公式的单元格：公式未使用预期的语法、参数或数据类型。 错误值包括 #DIV/0！、#N/A、#NAME？、#NULL！、#NUM！、#REF！、#VALUE！。 每个错误值都有不同的原因，并且以不同的方式解决。
注意
如果直接在单元格中输入错误值，它将存储为该错误值，但不会标记为错误。 但是，如果另一个单元格中的公式引用了该单元格，则该公式会从该单元格返回错误值。
表格中不一致的计算列公式：计算列可能包含与主列公式不同的独立公式，这会导致异常。 执行下列任一操作时，将导致计算列异常：
- 在计算列单元格中键入数据而不是公式。
- 在计算列单元格中键入公式，然后使用 Ctrl +Z 或选择快速访问工具栏上的“撤消撤
。 - 在已经包含一个或多个异常的计算列中键入一个新公式。
- 将数据复制到与计算列公式不匹配的计算列中。 如果复制的数据包含公式，该公式就会覆盖计算列中的数据。
- 移动或删除计算列中某一行引用的另一个工作表区域中的某个单元格。
包含以两位数字表示的年份的单元格：单元格中包含的文本日期在公式中使用时可能会被解释成错误的世纪。 例如，=YEAR("1/1/31") 公式中的日期既可以是 1931，也可以是 2031。 使用此规则可以检查出不明确的文本日期。
文本格式的数字或者前面有撇号的数字：单元格中包含存储为文本的数字。 从其他源导入数据时，通常会存在这种现象。 以文本形式存储的数字可能会导致意外的排序结果，因此最好将其转换为数字。 ‘=SUM(A1:A10) 会被视作文本。
与区域中的其他公式不一致的公式：公式与其他相邻公式的模式不匹配。 在许多情况下，与其他公式相邻的公式仅在使用的引用上有所不同。 在以下四个相邻公式的示例中，Excel 在单元格 D4 中的公式 =SUM (A10：C10) 旁边显示一个错误，因为相邻公式递增一行，而该公式递增 8 行，因此 Excel 要求公式 =SUM (A4：C4) 。
如果公式中使用的引用与相邻公式中的引用不一致，则 Excel 将显示错误。
遗漏了区域中的单元格的公式：一个公式可能无法自动包含对你在原始数据区域与包含该公式的单元格之间插入的数据的引用。 此规则将公式中的引用与包含该公式的单元格的相邻单元格的实际区域进行比较。 如果相邻单元格包含其他值并且不为空，则 Excel 会在该公式旁边显示错误。
例如，应用此规则时，Excel 会在公式 =SUM (D2：D4) 旁边插入一个错误，因为单元格 D5、D6 和 D7 与公式中引用的单元格和包含公式 D8) 的单元格相邻 (，并且这些单元格包含公式中应引用的数据。
包含公式的未锁定单元格：未对该公式进行锁定保护。 默认情况下，工作表上的所有单元格都处于锁定状态，因此在工作表受到保护时无法更改这些单元格。 这有助于避免意外删除或更改公式等偶然错误。 该错误表示此单元格已设置为未锁定，但相应工作表未处于受保护状态。 检查以确保不希望锁定单元格。
引用空单元格的公式：公式包含对空单元格的引用。 这可能会导致意外结果，如下面的示例所示。
假设你要对以下一列单元格中的数字计算平均值。 如果第三个单元格为空，则不包含在计算中，结果为 22.75。 如果第三个单元格包含 0，就会得到正确结果 18.2。
在表格中输入的数据无效：表格中存在有效性错误。 通过转至“数据”选项卡>“数据工具”组>“数据验证”检查单元格的验证设置。
逐个更正常见公式错误
选择要检查错误的工作表。
如果工作表是手动计算的，请按 F9 重新计算。
如果未显示“ 错误检查 ”对话框，请选择“ 公式”>、“公式”、“公式审核”、“错误>检查”。如果您之前忽略了任何错误，则可以通过执行以下操作再次检查这些错误：转到>文件选项>公式。 对于 Excel on Mac，请选择 Excel 菜单 > 首选项错误 > 检查。
在错误检查部分中，选择重置忽略的错误确定>。
注意
重置忽略的错误会重置当前工作簿中的所有工作表。
提示
将“错误检查”对话框移动到编辑栏正下方可能会有帮助。
选择对话框右侧的操作按钮之一。 可用的操作会因每种错误类型而有所不同。
选择“下一步”。
注意
如果选择“忽略错误”，则将标记为在每次连续检查中忽略错误。
分别更正常见公式错误
在单元格旁边，选择错误 检查
，然后选择所需选项。 可用的命令会因每种错误类型而有所不同，并且第一个条目会对错误进行描述。
如果选择“忽略错误”，则将标记为在每次连续检查中忽略错误。
更正 # 错误值
如果公式无法正确计算结果，Excel 将显示错误值，如 #####、#DIV/0！、#N/A、#NAME？、#NULL！、#NUM！、#REF！ 和 #VALUE！。 每种错误类型都有不同的原因和不同的解决方案。
下表包含指向详细描述这些错误的文章的链接，以及帮你入门的简要说明。
| 主题 | 说明 |
|---|---|
| 更正 #### 错误 | 当某列不够宽而无法在单元格中显示所有字符时，或者单元格包含负的日期或时间值时，Excel 将显示此错误。 例如，用过去的日期减去将来的日期的公式（如 =06/15/2008-07/01/2008）将得到负的日期值。 提示： 尝试通过在列标题之间双击来自动调整单元格。 如果显示 ### 是因为 Excel 无法显示所有字符，这将更正它。 
|
| 更正 #DIV/0! 错误 | 当一个数除以零 (0) 或不包含任何值的单元格时，Excel 将显示此错误。 提示： 添加一个错误处理程序，如以下示例所示，即 =IF (C2，B2/C2,0) IF 
|
| 更正 #N/A! 错误 | 当某个值不可用于函数或公式时，Excel 将显示此错误。 如果使用的是 VLOOKUP 之类的函数，则你尝试查找的内容是否在查找范围内具有匹配项？ 大多数情况下没有。 尝试使用 IFERROR 来抑制 #N/A。 在此例中，你可使用： =IFERROR (VLOOKUP (D2，$D$6：$E$8,2，TRUE) ,0) 
|
| 更正 #NAME? 错误 | 当 Excel 无法识别公式中的文本时，将显示此错误。 例如，区域名称或函数名称可能拼写错误。 注意： 如果使用的是函数，请确保函数名称拼写正确。 此例中，SUM 拼写错误。 删除“e”，Excel 会更正它。  错误
|
| 更正 #NULL! 错误 | 当你指定两个不相交的区域的交集时，Excel 将显示此错误。 交集运算符是分隔公式中的引用的空格字符。 注意： 请确保区域正确分隔 - 区域 C2：C3 和 E4：E6 不相交，因此输入公式 =SUM (C2：C3 E4：E6) 返回 #NULL！ 错误。 在 C 和 E 范围之间添加逗号更正 =SUM (C2：C3，E4：E6) 
|
| 更正 #NUM! 错误 | 当公式或函数包含无效数值时，Excel 将显示此错误。 你是否正在使用 IRR 或 RATE 等迭代函数？ 如果是的话，出现 #NUM! 错误可能是因为该函数找不到结果。 有关解决步骤，请参阅帮助主题。 |
| 更正 #REF! 错误 | 当单元格引用无效时，Excel 将显示此错误。 例如，你可能删除了其他公式引用的单元格，或者你可能已粘贴了移动到其他公式引用的单元格之上的单元格。 是否曾意外删除行或列？ 我们删除公式 =SUM(A2,B2,C2) 中的 B 列，然后查看发生了什么变化。 请使用 “撤消 ” (Ctrl+Z) 撤消删除、重新生成公式，或使用如下所示的连续范围引用：=SUM (A2：C2) ，这将在删除列 B 时自动更新。当 
|
| 更正 #VALUE! 错误 | 如果公式中包含含有不同数据类型的单元格，则 Excel 会显示此错误。 是否正对不同数据类型使用数学运算符 (+, -, *, /, ^)？ 如果是，请尝试改为使用一个函数。 在这种情况下，按 =SUM (F2：F5) 可纠正此问题。 
|
使用监视窗口来监视公式及其结果
当单元格在工作表上不可见时，可以在“监视窗口”工具栏中观察这些单元格及其公式。 使用“监视窗口”可以方便地在大型工作表中检查、审核或确认公式计算及其结果。 通过使用“监视窗口”，你无需反复滚动或转到工作表的不同部分。
此工具栏可以像任何其他工具栏一样移动或停靠。 例如，你可将其停靠在窗口底部。 该工具栏会跟踪以下单元格属性：1) 工作簿、2) 工作表、3) 名称（如果该单元格具有对应的命名区域）、4) 单元格地址、5) 值和 6) 公式。
注意
每个单元格只能有一个监视点。
向“监视窗口”中添加单元格
选择要监视的单元格。
若要选择包含公式的工作表上的所有单元格，请转到“ 主页>编辑> ”，选择“ 查找”&“选择 (”，或者可以在 Mac 上使用 Ctrl+G 或 Control+G ，) > 转到 特殊>公式。
转到 “公式”、“>公式审核> ”，选择“ 监视窗口”。
选择 “添加监视”。
确认已选择要观看的所有单元格，然后选择“ 添加”。
若要更改某一“监视窗口”列的宽度，请拖动列标题的右侧边界。
若要显示“监视窗口”工具栏中的条目所引用的单元格，请双击该条目。
注意
仅当其他工作簿处于打开状态时，包含指向这些工作簿的外部引用才会显示在“监视窗口”工具栏中。
从“监视窗口”中删除单元格
如果“监视窗口”工具栏未显示，请转至“ 公式”、“>公式审核> ”，选择“ 监视窗口”。
选择要删除的单元格。
若要选择多个单元格，请按 Ctrl，然后选择这些单元格。选择 “删除监视”。
对嵌套公式进行分步求值
有时，理解嵌套公式如何计算最终结果比较难，因为存在若干中间计算和逻辑测试。 但是，通过在 Excel for Windows 中使用 “公式求值 ”，可以看到嵌套公式的不同部分按公式计算顺序进行求值。 例如，当你可以看到以下中间结果时，公式 =IF (AVERAGE (D2：D5) >50，SUM (E2：E5) ,0) 会更容易理解：
| 在“公式求值”对话框中 | 说明 |
|---|---|
| =IF (AVERAGE (D2：D5) >50，SUM (E2：E5) ,0) | 最先显示的是此嵌套公式。 AVERAGE 函数和 SUM 函数嵌套在 IF 函数内。 单元格区域 D2:D5 包含值 55、35、45 和 25，因此 AVERAGE(D2:D5) 函数的结果为 40。 |
| =IF (40>50，SUM (E2：E5) ,0) | 单元格区域 D2:D5 包含值 55、35、45 和 25，因此 AVERAGE(D2:D5) 函数的结果为 40。 |
| =IF(False,SUM(E2:E5),0) | 由于 40 小于 50，因此 IF 函数的第一个参数（logical_test 参数）中的表达式为 False。 IF 函数返回第三个参数（value_if_false 参数）的值。 SUM 函数不会进行求值，因为它是 IF 函数的第二个参数（value_if_true 参数），它只有当表达式为 True 时才会返回。 |
- 在 Excel for Windows 中，选择要计算值的单元格。 一次只能对一个单元格进行求值。
- 转到 “公式”、“>公式审核”、“公式>求值”。
- 选择“求值”以检查带下划线的引用的值。 求值结果将以斜体显示。
如果公式的下划线部分是对另一个公式的引用，请选择单 步执行 以在 “求值 ”框中显示其他公式。 选择“步出”将返回到以前的单元格和公式。
当引用第二次出现在公式中，或者公式引用了另外一个工作簿中的单元格时，“步入”按钮不可用。 - 继续选择 “求值” ，直到公式的每个部分都已求值。
- 要再次查看评估，请选择“ 重启”。
- 若要结束评估，请选择“ 关闭”。
注意
- 使用 IF 和 CHOOSE 函数的公式某些部分不会进行计算，在这些情况下，“ 求值 ”框中会显示 #N/A。
- 如果引用为空，则会在“求值”框中显示零值 (0)。
- 每次更改工作表时，都会重新计算以下函数，这些函数可能导致“公式求值”对话框提供的结果与单元格中显示的结果不同：RAND、AREAS、INDEX、OFFSET、CELL、INDIRECT、ROWS、COLUMNS、NOW、TODAY、RANDBETWEEN。
需要更多帮助吗？
你随时可以在 Excel 技术社区 中咨询专家，或在 社区中获取支持。