如何避免损坏的公式

网友投稿 · 2023-08-29 08:08 · 云表格 · 阅读 513

如何避免损坏的公式

如果Excel无法解析你正在尝试创建的公式，你可能会收到以下类似错误消息：

遗憾的是，这意味着Excel无法理解你尝试的操作，因此你可能需要停止操作并重新开始。

首先单击“确定”或按“ESC”，关闭错误消息。

你将返回到具有损坏公式的单元格（单元格将处于编辑模式），Excel会突出显示有问题的地方。如果你仍然不知道该做什么且想要重新开始，可再次按“ESC”，或单击编辑栏中的“取消”按钮，你将退出编辑模式。

如果想要向前移动，请按照以下清单提供疑难解答步骤，帮助您了解可能出现的问题。

您是否看到了一个井号（#）错误？Excel将引发各种磅（#）错误，例如#VALUE！、#REF！、#NUM、#N/A、#DIV/0！、#NAME？和#NULL！，以指示公式中的内容不能正常工作。例如，#VALUE！错误由参数中的格式不正确或不受支持的数据类型导致。或者，你将看到#REF！如果公式引用了已删除或替换为其他数据的单元格，则出现错误。故障排除指南将因每个错误而异。

注意: ####不是与公式相关的错误。它仅表示列宽不够，无法显示单元格内容。只需拖动列以使其变宽，或前往“主页”>“格式”>“自动调整列宽”。

请参阅以下与您看到的英镑错误对应的任何主题：

更正#NUM!错误更正#VALUE!错误更正#N/A错误更正#DIV/0!错误更正#REF!错误更正#NAME?错误更正#NULL!错误

公式中存在断开的链接每次打开包含公式引用其他电子表格中的值的电子表格时，系统会提示您更新引用或将其保留原样。

Excel将显示上面的对话框，以确保当前电子表格中的公式始终指向最高更新的值，以防引用值发生更改。你可以选择更新引用，如果不想更新也可选择跳过。即使你选择不更新引用，也可根据需要随时手动更新电子表格中的链接。

可随时禁止在启动时显示该对话框。若要执行此操作，请转到“文件”>“选项”>“高级”>“常规”，并取消选中“请求自动更新链接”。

重要: 如果你首次处理公式中的断开的链接，并需要有关解决断开的链接的补习课程，或者你不确定是否要更新引用，请参阅控制何时更新外部引用（链接）。

公式将显示语法，而不是值如果公式不显示值，请按照以下步骤进行操作：确保Excel已设置为显示电子表格中的公式。要执行此操作，单击“公式”选项卡，在“公式审核”组中，单击“显示公式”。提示: 也可使用键盘快捷方式Ctrl+`（Tab键上方的键）。执行此操作时，列将自动变宽以显示公式，但不要担心，当你切换回普通视图时，列将重设大小。如果以上步骤仍不能解决此问题，则单元格的格式有可能设置为文本。右键单击单元格，然后选择“设置单元格格式”>“常规”（或“Ctrl+1”），然后按“F2”>“Enter”更改格式。如果在某一列中有大片区域的单元格格式设置为文本，你可选择此区域，应用你选择的数字格式，转到“数据”>“文本分列”>“完成”。这会将此格式应用到所有选定的单元格。

公式不进行计算当公式不进行计算时，必须检查Excel中是否启用了自动计算。若果启用了手动计算，则公式将不进行计算。请按照以下步骤检查“自动计算”：依次单击“文件”选项卡、“选项”和“公式”类别。在“计算选项”部分中，在“工作簿计算”下，确保选择了“自动”选项。

有关计算的详细信息，请参阅更改公式的重新计算、迭代或精度。

公式中存在一个或多个循环引用当公式引用其所在的单元格时，将出现循环引用。解决方法是将公式移动到另一个单元格或更改公式语法，以避免循环引用。但是，在某些情况下，可能需要使用循环引用，因为它们能使函数迭代，即重复到满足特定数值条件为止。在这种情况下，你将需要启用”删除”或”允许循环引用”

有关循环引用的详细信息，请参阅删除或允许循环引用

函数是否以等号(=)开头？如果你的项未以等号开头，则它不是一个公式，也不进行计算-这是一个常见错误。

键入类似SUM(A1:A10)的内容时，Excel将显示文本字符串SUM(A1:A10)，而不是公式结果。或者，如果您键入11/2，Excel将显示一个日期，例如2月11日或11/02/2009，而不是11除以2。

若要避免这些意外的结果，请始终以等号开头键入函数。例如，键入：=SUM(A1:A10)和=11/2

左括号和右括号是否匹配？当你在公式中使用函数时，每个左括号需要一个对应的右括号，才能保证函数正常工作，因此，请确保所有括号均成对出现。例如，公式=IF(B5<0),”Notvalid”,B5*1.05)将不能工作，因为此处有两个右括号而只有一个左括号。正确的公式如下所示：=IF(B5<0,”Notvalid”,B5*1.05)。

是否所有必需的参数都在语法中？Excel函数都有必需的参数，必须提供这些值才能保证函数正常工作。只有少数几个函数（如PI或TODAY）不需要参数。开始在函数中键入时，请务必检查公式语法，以确保函数有必需的参数。

例如,UPPER函数只接受一个文本字符串或单元格引用作为其参数：=UPPER(“hello”)or=UPPER(C2)

注意: 键入函数时，你将看到其参数列出在公式下方的浮动函数引用工具栏中。

另外，一些函数（如SUM）仅需要数值参数，而其他函数（如REPLACE）则要求至少有一个参数为文本值。如果使用错误的数据类型，函数可能会返回意外结果或显示#VALUE！错误。

如果需要快速查找某个特定函数的语法，请参阅Excel函数（按类别列出）列表。

公式中是否有任何不带格式的数字？在公式中请勿输入带美元符号($)或小数分隔符的数字(,)，因为美元符号表示绝对引用，逗号将用作参数分隔符。不要在公式中输入,000，而是应该输入1000。

如果在参数中使用带格式的数字，你将收到意外的计算结果，但你可能还会看到#NUM！错误。例如，如果输入公式=ABS(-2,134)来获取-2134的绝对值，Excel将显示#NUM!错误，因为ABS函数仅接受一个参数，它将-2和134视为单独的参数。

注意: 你可以在使用不带格式的数字（常量）输入公式之后使用小数分隔符和货币符号设置公式结果的格式。通常不建议在公式中使用常量，因为如果稍后需要更新，将很难找到它们，并且它们更容易输入错误。最好将常量放入单元格，这样常量便一目了然，也便于引用。

引用的单元格是否属于正确的数据类型？如果单元格的数据类型无法在计算中使用，公式就可能无法返回预期结果。例如，如果在格式设置为文本的单元格中输入简单公式=2+3，Excel将无法计算所输入的数据。您在单元格中看到的内容就是=2+3。若要修复此错误，将单元格的数据类型从“文本”更改为“常规”，如下所示：选择相应的单元格。单击“开始”>“数字格式”旁边的箭头（或按Ctrl+1），然后单击“常规”。按F2将单元格置于编辑模式，然后按Enter接受公式。

在使用“数字”数据类型的单元格输入的日期可能会显示为数字日期值，而非日期。要将数字显示为日期，请在“数字格式”库中选择“日期”格式。

是否尝试进行乘法运算但没有使用*符号？在公式中使用x作为乘法运算符非常常见，但Excel只能接受星号（*）进行乘法运算。如果在公式中使用常量，Excel将显示一条错误消息，并且可以通过将x替换为星号（*）来修复公式。

但是，如果使用单元格引用，Excel将返回#NAME?错误。

公式文本的两侧是否缺少引号？如果您创建了一个包含文本的公式，请将该文本用引号括起来。

例如，公式=”Todayis”&TEXT(TODAY(),”dddd,mmmmdd”)将文本“Todayis”与TEXT和TODAY函数的计算结果合并在一起，返回类似于TodayisMonday,May30的信息。

在该公式中，“Todayis”的右引号之前有一个空格；这将在“Todayis”和“Monday,May30”之间提供所需空格。如果没有用引号括起文本，公式可能会显示#NAME?错误.

公式中的函数是否有多于64个？在一个函数中，您可以组合（或嵌套）最多64层函数。

例如，公式=IF(SQRT(PI())<2,”Lessthantwo!”,”Morethantwo!”)包含3层函数：PI函数嵌套在SQRT函数内，后者又嵌套在IF函数内。

工作表名称是否包含在单引号中？当您键入另一个工作表中的值或单元格的引用，并且该工作表的名称包含非字母字符（例如空格）时，请用单引号(‘)将名称引起。

例如，若要返回工作簿中名为”季度数据”的工作表中单元格D3的值，请键入：=”季度数据”！D3。如果工作表名称周围没有引号，公式将显示”#NAME”错误.

您也可以单击另一个工作表中的值或单元格以在公式中引用它们。然后，Excel会自动在工作表名称周围添加引号。

如果公式引用了外部工作薄，是否正确引用的到工作薄的路径？在键入对另一个工作簿中的值或单元格的引用时，请包括工作簿名称（用方括号([])括起来），后跟具有值或单元格的工作表的名称。

例如，要引用已在Excel中打开的“第2季度运营”工作簿的“销售”工作表上的单元格A1至A8，请键入：=[第2季度运营.xlsx]销售!A1:A8。如果不带方括号，则公式显示#REF!错误.

如果工作簿未在Excel中打开，请键入该文件的完整路径。

例如，=ROWS(‘C:\MyDocuments\[第2季度运营.xlsx]销售’!A1:A8)。

注意: 如果完整路径包含空格字符，请将路径用单引号引起来（在路径开头处、工作表名称后面、感叹号之前）。

提示: 获取另一工作簿的路径的最简方法是打开相应工作簿，在原始工作簿中键入=，然后使用Alt+Tab切换到相应工作簿，并选择所需工作表上的任意单元格。然后关闭源工作簿。你的公式将自动更新，以显示完整文件路径和工作表名称以及所需语法。你甚至可以复制并粘贴该路径，并将其用于所需的任意位置。

是否要将数字值除以零？将某个单元格除以包含零(0)值或无值的单元格将得到#DIV/0!错误.

若要避免此错误，可以直接进行处理，并测试是否存在分母。

=IF(B1,A1/B1,0)

它表示如果B1存在，则用B1除A1，否则返回0。

公式是否引用已被删除的数据？在删除任何内容之前，请始终检查您是否有任何公式引用单元格、区域、已定义名称、工作表或工作簿中的数据。然后，您可以将这些公式替换为其结果，然后再删除所引用的数据。

如果无法将公式替换为其结果，请查看下面有关错误和可能的解决方案的信息：

如果公式引用已被删除或替换为其他数据的单元格，并且返回了#REF!错误，请选择出现#REF!错误的单元格。在编辑栏中，选择#REF!并将其删除。然后，重新输入公式的区域。如果某个已定义名称丢失并且引用该名称的公式返回#NAME?错误，请定义一个引用所需区域的新名称，或者更改公式以直接引用单元格区域（例如A2:D8）。如果某个工作表丢失并且引用它的公式返回#REF!错误，则无法修复此错误，很遗憾的是，已删除的工作表无法恢复。如果某个工作簿丢失，则引用它的公式将保持不变，直到您更新公式为止。例如，如果公式为=[Book1.xlsx]Sheet1′!A1并且您不再有Book1.xlsx，则该工作簿中引用的值将保持可用。但是，如果编辑并保存某个引用该工作簿的公式，则Excel会显示“更新值”对话框并提示您输入文件名。单击“取消”，然后通过将引用丢失的工作簿的公式替换为公式结果来确保此数据不会丢失。

是否已复制并粘贴电子表格中与公式相关联的单元格？有时，在你复制单元格的内容时，你只是想粘贴值而不是显示在编辑栏中的基本公式。

例如，你可能想要将公式的结果值复制到另一个工作表上的单元格。或者，在将结果值复制到工作表上的另一个单元格后，你可能想要删除公式中所使用的值。这两种操作均会导致目标单元格内显示无效的单元格引用(#REF！将显示在目标单元格中，因为无法再引用包含公式中所用值的单元格。

可以通过在目标单元格中粘贴公式的结果值而不粘贴公式以避免此错误。

在工作表上，选择你想要复制的包含公式结果值的单元格。在“开始”选项卡上的“剪贴板”组中，单击“复制”。键盘快捷方式：按Ctrl+C。选择粘贴区域左上角的单元格。提示: 若要将所选内容移动或复制到其他工作表或工作簿，请单击“另一个工作表”标签或切换到“另一个工作簿”，然后选择位于粘贴区域左上角的单元格在”开始”选项卡上的”剪贴板”组中，单击”粘贴”，然后单击”粘贴值”，或按Alt>E>S>v>enterForWindows，或按AltESv>v>v>在Mac上输入。

如果有嵌套的公式，请对公式进行分步求值要了解复杂或嵌套公式如何计算最终结果，您可以计算该公式的值。选择要求值的公式。单击“公式”>“公式求值”。单击“求值”以检查带下划线的引用的值。求值结果将以斜体显示。如果公式的下划线部分是对另一个公式的引用，请单击“步入”以在“求值”框中显示其他公式。单击“步出”将返回到以前的单元格和公式。当引用第二次出现在公式中，或者公式引用了另外一个工作簿中的单元格时，“步入”按钮不可用。继续操作，直到已对公式的每一部分求值。 “公式求值”工具不一定告诉你公式损坏的原因，但会帮助指出损坏的位置。对于较大公式，这是非常方便的工具，因为通过其他方式找到问题可能很困难。注意: IF和CHOOSE函数的某些部分不会进行求值，“求值”框中可能会显示“#N/A”错误。空白引用在“求值”框中显示为零值(0)。每次工作表更改时都会重新计算的函数。这些函数（包括RAND、AREAS、INDEX、OFFSET、CELL、INDIRECT、ROWS、COLUMNS、NOW、TODAY和RANDBETWEEN函数）可能会导致“公式求值”对话框显示的结果不同于工作表上的单元格中的实际结果。

需要更多帮助吗？可随时在Excel技术社区中咨询专家，在解答社区获得支持，或在ExcelUserVoice上建议新功能或功能改进。

另请参阅Excel中的公式概述

检测公式中的错误

Excel函数（按字母顺序）

Excel函数（按类别列出）

注意: 本页面是自动翻译的，可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。能否告知我们此信息是否有所帮助？下面是该参考内容的英文版。