Excel #REF! 错误

MS Excel 或 Microsoft Excel 是由著名公司 Microsoft 开发的一款知名的电子表格程序或软件。它使用户能够记录财务数据，并使用各种现有的函数或公式对特定的记录数据集执行所需的计算。然而，有时我们可能无法从应用的函数/公式中获得预期的结果。相反，Excel 会在相应的单元格中返回一些随机的意外代码。这些随机的意外代码称为 Excel 错误。

Excel 中有许多错误代码，它们是 Excel 错误的一部分，这些代码通常是逐例出现的。一个工作表可能包含一个或多个这些错误。当我们处理工作表中的函数和公式时，#REF! 是我们经常遇到的 Excel 错误之一。与其他错误代码一样，#REF! 错误也是由于特定原因出现的，我们应该遵循正确的指南来相应地修复或删除此错误。

Excel 中的 #REF! 错误是什么？

Excel 中的 #REF! 错误是公式错误之一，它可能由于与使用的单元格引用相关的几个原因而发生。#REF! 本身代表引用的含义。具体来说，#REF! 错误是工作表中公式内部使用的无效或不存在的引用的结果。

Excel 在以下情况下将应用的单元格视为无效

• 删除单元格、行、列或工作表时。
• 公式包含不正确的单元格引用时，例如复制了相对引用。

在这两种情况下，由于公式（在删除之前引用了单元格或范围）不知道指向哪里，因此公式中使用的原始引用会变为 #REF!。

如何在 Excel 中查找 #REF! 错误？

在我们学习如何修复或删除工作表中的 #REF! 错误之前，我们必须知道如何查找它们或它们对应的单元格。

当我们处理小型数据集时，#REF! 错误更容易检测。我们可以滚动浏览工作表，找到包含 #REF! 代码而不是根据使用的公式预期的结果的单元格。如果我们点击这样的单元格，Excel 会显示一个带有内部感叹号/符号的矩形错误框。此外，还会显示一条简短消息，说明单元格或应用的公式有什么问题。

另一方面，当我们的工作表中有大量数据，并且存在多个 #REF! 错误时，我们必须尝试以下任何一种方法来检测错误并按照相应的正确步骤进行修复。

使用查找和替换工具

当我们需要在工作表中查找任何特定错误时，Excel 的“查找和替换”工具效果很好。该工具会搜索整个工作表以查找并选择指定的错误。使用“查找和替换”工具的优点是我们可以单独查看每个错误单元格，以相应地修复或格式化它。

我们需要执行以下步骤来使用“查找和替换”工具查找和选择包含 #REF! 错误的单元格

• 首先，我们需要选择要在此查找 #REF! 错误的单元格范围。我们可以按键盘快捷键“Ctrl + A”一次选择整个工作表。
• 选择数据后，我们需要导航到“开始”选项卡 > “查找和选择” > “查找”。
  
  或者，我们可以使用键盘快捷键“Ctrl + F”。这些方法将显示带有“查找”选项卡选中的“查找和替换”窗口。在这里，我们需要点击“选项”按钮，如下图所示
  
• 在“查找”选项卡下，我们需要在“查找内容”旁边输入“#REF!”，并在“查找范围”选项旁边的下拉列表中选择“值”。进行更改后，我们必须点击“全部查找”按钮。
  
• 点击“全部查找”按钮后，Excel 将在当前“查找和替换”窗口下方显示一个列表。该列表显示所选数据范围内所有包含 #REF! 错误的单元格的详细信息。
  
  我们可以从列表中选择一个或多个错误单元格，并采取必要的措施，例如纠正相应错误或完全删除它们。此外，我们还可以使用“查找下一个”按钮而不是“全部查找”按钮来逐个浏览每个 #REF! 错误单元格。

使用定位到特殊工具

定位 #REF! 错误的另一种方法是使用 Excel 的“定位到特殊”工具。使用此工具，我们可以定位所有包含错误的单元格并相应地选择它们。该工具不仅查找包含 #REF! 错误的单元格，还查找其他 Excel 错误。这样，如果我们有多种不同类型的错误，此方法将不适合仅筛选出 #REF! 错误。我们必须手动筛选 #REF! 错误与其他错误。除此之外，如果错误以文本形式存在于工作表中，该工具将不会选择此类单元格。

我们需要执行以下步骤来使用“定位到特殊”工具查找和选择包含 #REF! 错误的单元格

• 与前一种方法一样，我们首先需要选择要在此查找 #REF! 错误的单元格范围。我们也可以相应地选择整个工作表或连续/不连续的单元格。
• 选择有效范围后，我们需要转到“开始”选项卡，点击“查找和选择”按钮，然后从菜单列表中选择“定位到特殊”选项。这有助于打开“定位到特殊”窗口。
  
• 打开“定位到特殊”窗口后，我们必须点击与“公式”选项相关的单选按钮，并选择“错误”框。我们必须按以下方式取消选择所有其他框
  
• 最后，我们需要点击“确定”按钮来应用首选项。这将选择所有包含错误的单元格。我们可以逐个检查找到的错误，以相应地修复或删除它们。

导致 #REF! 错误的根本原因是什么以及如何修复此错误？

当我们的工作表中出现 #REF! 错误时，这意味着公式中使用的一个或多个引用无效或不可用。此特定错误最常发生的原因是删除了行、列或工作表。但是，还有其他一些原因会导致 #REF! 错误。修复 #REF! 错误只是编辑相应公式并更正其引用的问题。

让我们了解 #REF! 错误在 Excel 工作表中发生的常见原因以及消除此错误的各种方法。它们是

情况 1：删除引用

当我们在工作簿中的公式中删除任何已提供或引用的单元格、行、列或工作表时，Excel 会在所有使用相应公式的单元格中显示 #REF! 错误。这是因为我们在公式中尝试引用的元素不再存在（单元格引用已被删除）。因此，Excel 无法找到或使用相应的_数据来生成公式结果。

例如，考虑一个包含一些销售数据的表，我们在其中应用了加法运算符来计算总销售额。

在上面的表中，公式工作正常并提供总销售额。现在，如果我们删除任何一行（假设是第 4 行），总销售额将立即变为 #REF! 错误。删除第 4 行后，公式中的相应单元格变为 #REF!，导致结果/目标单元格中出现 #REF!。

如何修复此问题？

要在此特定情况下修复 #REF! 错误，我们必须编辑公式并包含有效引用，或者在公式中直接在引用单元格或 #REF! 的位置添加所需数据。

在我们的示例中，如果我们编辑公式并删除 #REF!，我们将获得相应项目的总销售额。

但是，如果我们不小心删除了单元格、行或列，我们可以立即撤销上一步操作，错误就会被修复。撤销功能在意外删除工作表时无法帮助删除 #REF! 错误。在这种情况下，我们必须删除引用或添加数据。如果我们从第 4 行（即 555）中添加数据直接到公式中而不是 #REF!，我们将获得预期的结果。

情况 2：复制具有相对单元格引用的公式

当我们从一个位置将公式复制到另一个位置并突然遇到 #REF! 错误时，这很可能是相对单元格引用的情况。这意味着我们复制到另一个单元格的公式包含相对引用，这些引用仅适用于原始单元格。一旦将其复制到另一个单元格，引用就会自动更改并导致 #REF! 错误。

例如，再次考虑之前的示例表。在这种情况下，使用 SUM 函数计算总销售额。

但是，如果我们从单元格 B7 将 SUM 函数复制到 E5，则会出现 #REF! 错误。在这种情况下，Excel 假定我们指的是 E 列中的相应数据。

如何修复此问题？

要在此特定情况下修复 #REF! 错误，我们必须编辑或更改单元格引用，使它们成为绝对引用。这样，Excel 将始终引用公式中使用的相同单元格。这可以通过在所需引用前添加美元符号来完成。在我们的示例中，我们必须编辑原始单元格 (B7) 中的公式并包含单元格引用中的美元符号。然后，我们的公式将如下所示

现在，我们可以将公式复制粘贴到工作表中的任何其他单元格，结果将与原始单元格相同。

情况 3：错误使用 VLOOKUP 函数

导致 #REF! 错误的另一个常见情况是错误地使用 VLOOKUP 函数。但是，导致错误的根本原因或变体略有不同。也就是说，在使用 VLOOKUP 函数时，#REF! 错误通常不是由于错误的指定范围引起的。相反，当我们在指定列时出错。

例如，让我们再次考虑具有一些项目销售额的相同示例。但是，我们不再对销售额求和，而是应用 VLOOKUP 函数来查找任何特定数据的销售额。在下面的示例表中，VLOOKUP 函数返回 #REF! 错误而不是相应项目的指定销售额。

在上面的表中，VLOOKUP 函数返回 #REF! 错误是因为我们在 col_index_num 参数中引用了第三列 (3)。该特定参数不是公式中第二个参数指定的数组的一部分。因此，相应函数由于缺少列（第二列）而找不到相关数据，从而导致 #REF! 错误。

与 VLOOKUP 函数类似，在使用 INDEX 函数中的错误行或列引用时，我们也可能遇到 #REF! 错误。

如何修复此问题？

在使用 VLOOKUP 函数时，我们必须使用正确的语法并提供正确的参数和范围。在我们的示例中，我们需要引用 col_index_num 参数中的第二列，因为第三列没有相关数据。因此，如果我们编辑 VLOOKUP 函数并将第三个参数从 3 更改为 2，公式将正常工作而不会出现错误。

避免或防止 #REF! 错误

避免 #REF! 错误（s）的最佳选择是首先防止其发生。如果需要删除单元格、行、列或工作表，我们必须确保或检查它们不是工作表中已应用公式的一部分。此外，如果我们正在将现有公式复制粘贴到其他单元格，我们通常需要相应地将某些单元格引用转换为绝对引用。

尽快修复或删除 #REF! 错误始终是一个好习惯。在某些情况下，修复此错误可能会非常困难。假设我们不小心删除了某一行，并且工作表中突然出现 #REF! 错误。通常我们可以按键盘快捷键“Ctrl + Z”来撤销更改，即撤销行删除。这也会修复 #REF! 错误，因为公式所需的_数据又可用了。

但是，如果我们确实需要删除特定行、列或单元格，我们必须编辑相应公式以排除需要删除的特定行、列或单元格。另外，我们可以将数据移动到其他位置来使用所需数据。之后，我们可以删除列并确保没有留下任何 #REF! 错误单元格。

要记住的重要事项

• 在删除一个或多个工作表之前，我们必须始终保留工作簿的副本。这是因为我们无法恢复已删除的工作表。我们能找回数据的唯一机会是重新打开 Excel 从上次保存的位置打开文件。但是，这并不可靠。
• 当我们的工作表中出现多个 #REF! 错误并且需要一次性清除或删除它们时，我们可以使用“查找和替换”工具。我们必须在“查找内容”框中键入 #REF!，将“替换为”框留空，然后点击“全部替换”按钮。我们还可以根据具体情况将错误替换为零 (0)、连字符 (-) 或任何其他有意义的文本或格式。