Excel 中的公式错误

MS Excel 或 Microsoft Excel 是目前最强大的电子表格软件，它使我们能够将大量数据记录在由行和列组成的单元格中。一旦数据被记录下来，我们就可以应用各种公式或函数来获得期望的结果。但有时，即使处理最普通的公式，我们也会在 Excel 单元格中遇到一些错误代码，而不是期望的值或结果。这清楚地表明有些地方出了问题。这些意外的结果或代码被定义为Excel 公式错误。

虽然公式错误令人沮丧，但它们也有助于我们理解在应用相应公式时我们做错了什么。此外，通过一些知识和经验，更容易发现和修复公式错误。要查找 Excel 工作表中的所有错误，我们需要通过使用键盘快捷键Ctrl + G启动转到特殊对话框，然后单击特殊按钮。在对话框中的公式下，我们需要选择错误复选框，然后单击确定按钮。这将一次性显示所有错误。

注意：Excel 有许多不同的函数来帮助捕获和处理各种错误类型。两个常用的函数是 IFERROR 和 ISERROR，它们可以捕获 Excel 中的特定类型错误。但是，它们不能帮助追踪逻辑 Excel 错误。

Excel 中有多少种常见的公式错误？

在使用 Excel 公式时，我们通常会遇到各种错误。公式错误也是在 Excel 中工作时最常见的错误的一部分。公式错误会阻止函数的执行，并使我们的公式、仪表板或报告完全无用。每种类型的公式错误都有其特定原因。我们应该了解这些错误发生的原因，以便能够轻松地捕获它们并修复它们以获得正确的结果。

以下是 Excel 中最常见的公式错误：

• #NULL! 错误
• #DIV/0! 错误
• #VALUE! 错误
• #REF! 错误
• #NAME? 错误
• #NUM! 错误
• #N/A 错误
• #### 错误
• #SPILL! 错误
• #CALC! 错误

让我们详细讨论每种公式错误

#NULL! 错误

虽然很少见，但 #NULL! 错误是由于在参数位置提供了错误的值而发生的。通常，这是由于输入错误或在 Excel 单元格引用之间的逗号 (,) 或冒号 (:) 位置使用了空格字符。

例如，下图显示了单元格 D2 中的 #NULL! 错误。如果我们检查单元格 D2 中的公式，A2、B2 和 C2 中的值是使用 SUM 公式计算的。但是，我们错过了冒号，在单元格引用之间放置了空格字符。此处出现 #NULL! 错误是因为空格字符是“范围交叉”运算符。该错误表明指定的范围（A2 和 C2）不相交。这意味着 SUM 函数内使用了不正确的范围运算符。

如何修复 Excel 中的 #NULL! 错误类型？

要修复 #NULL! 错误，我们必须使用正确的参数分隔符。在我们的示例中，我们可以通过在第一个参数 (A2) 之后将空格字符替换为冒号来消除 NULL! 错误。最好避免使用所应用公式的错误语法，以消除 Excel 中出现 #NULL! 错误的几率。

尽管有上图，我们也可以如下所示使用逗号 (,) 添加所有三个参数：

为了使用正确的参数分隔符或运算符，我们必须根据以下规则选择 Excel 引用运算符：

• 范围运算符 (冒号): 用于指定对多个单元格范围的引用。
• 并集运算符 (逗号): 用于将两个不同的引用合并/组合成一个引用。
• 交集运算符 (空格): 用于返回两个范围交叉处的引用。

#DIV/0! 错误

顾名思义，#DIV/0! 错误通常在应用的公式中包含被零或具有等效于零的值的单元格引用除以零的值时发生。此外，当未提供数据或数据尚未完成时，也可能发生此错误。这意味着公式试图除以一个空单元格的值。在 Excel 中，空单元格被计算为零。

换句话说，当我们删除 Excel 工作表中部门公式依赖的某些现有值时，就会出现 #DIV/0! 错误。特别是，此错误是计算方法错误的结果。同样，如果我们尝试用手动或计算器除以零 (0)，它将无法正常工作。

例如，下图显示了单元格 D3 中的 #DIV/0! 错误。如果我们检查单元格 D3 中的公式，单元格 B3 中的值除以单元格 C3 中的值，而 C3 是一个空单元格或空白单元格。同样，单元格 D6 给出 #DIV/0! 错误，其中分子和分母都为空（单元格 B6 除以单元格 C6）。

虽然 #DIV/0! 错误是在除以零时发生的，但它也可能出现在其他应用公式中，这些公式引用了具有 #DIV/0! 错误的单元格。例如，当我们尝试将单元格 B3 的值与单元格 D3（带有错误的单元格）相加时，SUM 公式也将给出 #DIV/0! 错误。

如何修复 Excel 中的 #DIV/0! 错误类型？

要修复 #DIV/0! 错误，我们必须找到公式并提供任何不等于零或为空的值。在我们的示例中，如果我们提供单元格 C3 中的值，错误就会被修复，如下所示：

当缺失输入值时，修复 #DIV/0! 错误非常容易。然而，当我们想使用空单元格作为分母稍后输入值，但又不想显示 #DIV/0! 错误时，我们可以使用逻辑函数 IF 显示自定义消息。

在上图中，当单元格 C6（本例中的分母）为零时，会显示定义的字符串（自定义消息）。否则，会显示 B6/C6 的除法结果。

#VALUE! 错误

当公式未按其语法接收正确的值（或参数）时，通常会出现 #VALUE! 错误。特别是，此错误是由于在函数需要数值时提供了错误的数字类型或非数值值而导致的。例如，当公式中使用的单元格为空，在需要数值的函数中提供了文本值，或者日期被评估为文本等。

在下图中，单元格 D3 中出现 #VALUE! 错误。如果我们检查单元格 D3 中的公式，单元格 B3 中的值与单元格 C3 中的值相加，其中两个单元格（B3 和 C3）都包含文本值。由于我们需要数值来求和，Excel 会因使用不受支持/错误的值或参数而给出 #VALUE! 错误。

同样，如果我们使用 MONTH 函数从任何文本字符串获取月份值而不是日期，该函数会给出 #VALUE! 错误。

如何修复 Excel 中的 #VALUE! 错误？

要修复 #VALUE! 错误，我们必须找到带有问题值的公式并提供正确的值类型。在我们的示例中，如果我们为单元格 B3 和 C3 提供数值，错误就会被修复，如下所示：

虽然修复 #VALUE! 错误很容易，但有时很难发现问题。例如，在下图中，即使我们提供了日期，MONTH 公式也会给出 #VALUE! 错误。

在上图中，给出的日期不是 Excel 支持的正确格式。因此，它被评估为文本。因此，要避免 #VALUE! 错误，我们必须仔细检查公式和提供的参数。提供的参数必须是受支持的格式，没有不受支持的特殊字符。

#REF! 错误

当我们处理电子表格中的数据时，#REF! 错误是我们遇到的常见 Excel 公式错误之一。当定义的引用变得无效或无法识别时，通常会出现此错误。通常，当我们删除在另一个单元格中的 Excel 公式中用作引用的任何单元格、行、列或工作表时，就会发生这种情况。此外，当我们复制粘贴带有相对引用的公式到完全新的位置，而定义的引用变得无效时，也会显示 #REF! 错误。

例如，单元格 D3 中出现 #REF! 错误。单元格 D3 中使用的公式是从单元格 B8 复制的，其中定义的范围 B2:B7 是相对的。在新的位置（单元格 D3）对应的范围无效，公式给出 #REF! 错误。

同样，如果我们向单元格 C2 添加单元格 A2 和 B2 的值，然后突然删除整个 B 列，则定义的引用将变得无效，从而导致 #REF! 错误。

如何修复 Excel 中的 #REF! 错误？

当我们粘贴公式到一组单元格后出现 #REF! 错误时，我们需要找到这些单元格并仔细检查它们的引用。稍后，我们必须编辑公式并将 #REF! 替换为有效引用。

最好防止 #REF! 错误因无效引用而发生，而不是以后再修复。当我们想复制公式并在工作表内的其他位置使用它们时，我们可以使用绝对引用（锁定引用）来轻松复制粘贴公式而不会出错。

修复 #REF! 错误有时很简单，取决于我们得到 #REF! 错误之前删除了什么。如果我们删除了一行或一列并看到 #REF! 错误，我们可以撤销上一步操作，然后仅将公式结果复制并粘贴为值。

但是，如果我们删除了工作表并看到此错误，则定义的单元格引用将永远丢失，因为工作表无法恢复。在这种情况下，我们必须重新开始。

#NAME? 错误

#NAME? 错误通常在公式不识别某些重要内容时发生。尽管其名称如此，#NAME? 错误并非因为错误的姓名而产生。相反，当 Excel 无法识别我们在单元格中应用的公式名称时，就会出现此错误。在大多数情况下，公式拼写错误。此外，#NAME? 错误可能由于公式中的错误命名范围或不正确的单元格引用而发生。

例如，下图显示了单元格 C2 中的 #NAME? 错误。如果我们检查单元格 C2 中的公式，我们可以看到我们提供的用于添加值的函数不符合 Excel 中定义的函数标准。这意味着我们拼写错误了公式，使用了 'SUMM' 而不是 'SUM'，如下所示：

如何修复 Excel 中的 #NAME? 错误？

修复 Excel 中的 #NAME? 错误很容易。我们必须找到错误单元格，并仔细检查相应单元格中应用的函数拼写或语法。我们可以选择特定的单元格，查看公式栏，以确保我们输入的内容是否正确。如果不正确，我们可以从公式栏更改公式拼写或更正语法。

由于 'SUMM' 是不正确的，我们将其替换为 'SUM'，因此工作表中不存在 #NAME? 错误。

最好避免手动输入公式，并使用 Excel 的自动填充选项。每当我们开始在 Excel 单元格中输入等号后面的字符时，Excel 都会列出相关的公式。我们可以从列表中向下滚动到所需的函数名称，然后在键盘上按 Tab 键来使用该函数。按 Tab 键后，将插入函数，并显示其语法。这样，我们可以消除函数输入错误的几率，避免 #NAME? 错误。

#NUM! 错误

当 Excel 公式包含无效的数值时，通常会出现 #NUM! 错误。在大多数情况下，数值要么太小，要么太大。此外，当使用不符合公式标准或无法执行计算的数值时，也可能出现此错误。

例如，下图显示了单元格 B2 中的 #NUM! 错误。如果我们检查单元格 B2 中的公式，我们可以看到 SQRT 函数用于计算负数的平方根，这在数学上是不可能的。

此外，假设我们需要在 Excel 中将货币或其他数字格式与公式结果一起使用。但是，如果我们不在公式中使用仅值（例如 1000），而是使用货币格式（例如 $1000）的值，那么，在公式使用的单元格中，公式将返回 #NUM! 错误，因为在公式中无法使用货币符号进行计算。

如何修复 Excel 中的 #NUM! 错误？

修复 Excel 中的 #NUM! 错误很容易。为了使计算成为可能，我们必须找到错误单元格，并根据公式的要求调整给定输入。在我们的示例中，如果我们去掉负号，计算将完成，错误将消失。

如果我们确实需要负值，那么我们可以将负号放在函数前面，如下所示：

此外，我们必须确保在公式中没有使用任何格式化的货币、日期或特殊符号，这些符号与相应公式所需的参数不同。如果使用，我们需要从应用公式中删除此类格式或字符，并只保留数值。建议在计算完成后调整格式。

#N/A 错误

当 Excel 公式包含不可用值时，通常会出现 #N/A! 错误。该错误表示“不可用”，表明某些内容丢失或拼写错误。此错误的原因可能是公式中使用的行号被意外删除。此外，在查找函数中，由于额外的空格字符、拼写错误的值或不完整的表格，也可能发生此错误。

用户在使用 LOOKUP、VLOOKUP、HLOOKUP 等经典查找函数时，通常会遇到 #N/A。当 VLOOKUP 在查找表中找不到指定的单元格或引用的单元格时，就会发生这种情况。

例如，下图显示了单元格 E2 中的 #N/A! 错误。如果我们检查单元格 E2 中的公式，我们可以看到 VLOOKUP 函数正在尝试查找“Eggs”，而它在查找表中不存在。

如何修复 Excel 中的 #N/A 错误？

要修复 #N/A 错误，我们必须仔细检查所有公式，并仔细查看引用的单元格。我们必须确保在公式中没有删除任何工作表、行或单元格，也没有错误地引用它们。当 Excel 工作表中链接了多个公式时，我们必须确保每个公式都根据语法具有正确的值。

在我们的示例中，如果我们把单元格 E2 中的值从“Eggs”改为“Coffee”，VLOOKUP 将按预期工作。

#### 错误

在大多数情况下，#### 错误通常是由于格式问题引起的。当我们输入具有长格式结果的公式到 Excel 单元格中，并且列宽不够时，Excel 会显示 #### 错误。

在上图中，单元格 A2 显示 #### 错误，因为单元格的宽度不够。

有时，我们认为 #### 错误是由宽度不足引起的；但事实并非总是如此。当我们尝试显示负时间、日期等时，也可能导致此错误，因为这些值不能为负。

如何修复 Excel 中的 #### 错误？

虽然这看起来很困难，但修复 #### 错误很容易。我们只需要使列宽变宽。

#SPILL! 错误

当应用的公式尝试返回多个结果但无法将它们记录到相应网格时，就会出现 #SPILL! 错误。换句话说，公式输出一个溢出范围，该溢出范围通过一个已经包含数据的单元格执行。这意味着溢出数组公式的溢出范围不是空白的。

例如，下图显示了单元格 D5 中的 #SPILL! 错误。UNIQUE 函数尝试从选定范围 (B5:B10) 中提取唯一名称到从单元格 D5 开始的溢出范围。由于单元格 D8 已经包含值 'x'，执行被阻止，并返回 #SPILL! 错误。

如何修复 Excel 中的 #SPILL! 错误？

要修复 #SPILL! 错误，我们必须找到所有阻碍公式执行的障碍单元格。接下来，我们需要检查每个障碍单元格并清除其值。一旦障碍值被移除，数组公式将正常溢出。在我们的示例中，如果我们从单元格 D8 中移除 'x'，公式将按预期溢出。

#CALC! 错误

当应用的 Excel 公式遇到数组计算错误时，会出现 #CALC! 错误。从技术上讲，Excel 的计算引擎遇到了不支持的场景。

例如，FILTER 函数过滤范围 B5:B11 中的源数据。但是，公式正在查找工作表中不存在的类别 'x' 中的数据。因此，在单元格 F5 中出现 #CALC! 错误。

如何修复 Excel 中的 #CALC! 错误？

要修复 #CALC! 错误，我们必须确保公式和提供的值存在于指定的范围内。在我们的示例中，如果我们修改公式并将过滤器应用于组 'A' 而不是 'x'，则公式将按预期工作。

要记住的重要事项

• 我们可以使用 IFERROR 来处理任何公式错误。
• SPILL 和 CALC 两个错误与“动态数组”有关，并且仅在 Excel Office 365 中找到。