检测公式中的错误

下表概括了用户在输入公式时可能会犯的一些最常见错误，并说明了如何更正这些错误。

请确保	更多信息
每个函数都以等号 (=) 开头	如果省略等号，则键入的内容可以显示为文本或日期。 例如，如果键入 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, or =‘123’!A1。
在公式中引用工作表名称时，在其后放置一个感叹号 (!)	例如，若要在同一工作簿中名为 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 显示的选项解决错误，也可以通过选择“忽略错误”来忽略错误。 如果忽略特定单元格中的某个错误，则该单元格中的该错误就不会再出现在以后的错误检查中。 但是，你可以重置以前忽略的所有错误以使其重新出现。

1. 对于 Windows 上的 Excel，请转到“文件 > 选项 ”> 公式，或
   对于 Mac 上的 Excel，请选择“ Excel”菜单 >“首选项”>“错误检查”。

2. 在“错误检查”中，选中“启用后台错误检查”。 找到的任何错误都用单元格左上角的三角形标记。

   

3. 若要更改标记错误发生位置的三角形的颜色，请在“使用此颜色标识错误”框中，选择所需的颜色。

4. 在“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。

     

   • 在表格中输入的数据无效：表格中存在有效性错误。 请检查单元格的有效性设置，方法是在“数据”选项卡上的“数据工具”组中单击“数据有效性”。

1. 选择要检查错误的工作表。

2. 如果工作表是手动计算的，请按 F9 重新计算。

   如果未显示 “错误检查 ”对话框，请选择“公式 > 公式审核 > 错误检查”。

3. 如果以前忽略了任何错误，可以通过执行以下操作再次检查这些错误：转到“文件 > 选项 > 公式”。 对于 Mac 版 Excel，请选择 “Excel”菜单，>“首选项”>“错误检查”。

   在 “错误检查 ”部分中，选择“重置忽略的错误 ”> “确定”。

   

   注意: 重置忽略的错误会重置当前工作簿中的所有工作表。

   提示: 将“错误检查”对话框移动到编辑栏正下方可能会有帮助。

   

4. 选择对话框右侧的操作按钮之一。 可用的操作会因每种错误类型而有所不同。

5. 选择 下一步。

1. 在单元格旁边，选择“ 错误检查 ”，然后选择所需的选项。 可用的命令会因每种错误类型而有所不同，并且第一个条目会对错误进行描述。

   如果选择“忽略错误”，则每个连续检查都会将错误标记为忽略。

   

如果公式无法正确计算结果，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)
更正 #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) 可解决该问题。

当单元格在工作表上不可见时，可以在“监视窗口”工具栏中watch这些单元格及其公式。 使用“监视窗口”可以方便地在大型工作表中检查、审核或确认公式计算及其结果。 通过使用“监视窗口”，你无需反复滚动或转到工作表的不同部分。

该工具栏同其他任何工具栏一样，可移动或停靠。 例如，你可将其停靠在窗口底部。 该工具栏会跟踪以下单元格属性：1) 工作簿、2) 工作表、3) 名称（如果该单元格具有对应的命名区域）、4) 单元格地址、5) 值和 6) 公式。

向“监视窗口”中添加单元格

1. 选择要监视的单元格。

   若要选择包含公式的工作表上的所有单元格，请转到“开始 > 编辑”> 选择“查找 & 选择 (，或者可以在 Mac 上使用 Ctrl+G 或 Control+G) > 转到 特殊 > 公式。

   

2. 转到“公式 ”> “公式审核”> 选择“监视窗口”。

3. 选择“添加监视”。

   

4. 确认已选择要watch的所有单元格，然后选择“添加”。

   

5. 若要更改某一“监视窗口”列的宽度，请拖动列标题的右侧边界。

6. 若要显示“监视窗口”工具栏中的条目所引用的单元格，请双击该条目。

   注意: 仅当其他工作簿处于打开状态时，包含指向这些工作簿的外部引用才会显示在“监视窗口”工具栏中。

从“监视窗口”中删除单元格

1. 如果未显示“监视窗口”工具栏，请转到“公式”>“公式审核”> 选择“监视窗口”。

2. 选择要删除的单元格。

   若要选择多个单元格，请按 Ctrl，然后选择单元格。

3. 选择“删除监视”。

   

有时，理解嵌套公式如何计算最终结果比较难，因为存在若干中间计算和逻辑测试。 但是，通过使用“公式求值”对话框，你可以按计算公式的顺序查看嵌套公式的不同求值部分。 例如，当可以看到以下中间结果时，公式 =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 时才会返回。

1. 选择要求值的单元格。 一次只能对一个单元格进行求值。

2. 转到公式 > 公式审核 > 计算公式。

3. 选择“求值”以检查带下划线的引用的值。 求值结果将以斜体显示。

   如果公式的带下划线部分是对另一个公式的引用，请选择“ 单步执行 ”以在“ 计算 ”框中显示另一个公式。 选择“步出”将返回到以前的单元格和公式。

   当引用第二次出现在公式中，或者公式引用了另外一个工作簿中的单元格时，“步入”按钮不可用。

4. 继续选择“计算” ，直到计算公式的每个部分。

5. 若要再次查看评估，请选择“重启”。

6. 若要结束评估，请选择“关闭”。