如何避免损坏的公式

如何避免损坏的公式

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

Excel 的"此公式的问题"对话框的图像

遗憾的是,这意味着 Excel 无法理解你尝试做什么,因此你可能只想重新开始。

首先选择"确定" 或按 ESC 关闭错误消息。

你将返回到具有损坏公式的单元格(单元格将处于编辑模式),Excel 会突出显示有问题的地方。 如果仍然不知道从该开始做什么,并且想要重新开始,可以再次按ESC,或选择编辑栏中的"取消"按钮,这将退出编辑模式。

“编辑栏取消”按钮的图像

如果要继续操作,以下清单提供了故障排除步骤来帮助你找出可能出错的地方。

注意: 如果使用Office 网页版 ,可能看不到相同的错误,或者解决方案可能不适用。

Excel 会引发各种井号 (#) 错误,例如 #VALUE!、#REF!、#NUM、#N/A、#DIV/0!、#NAME? 和 #NULL!,以指示公式中的某些内容无法正常工作。 例如,#VALUE! 错误是由参数中的格式不正确或不支持的数据类型导致的。 或者,你将看到#REF! 错误。 对于每个错误,故障排除指南会有所不同。

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

“主页”>“格式”>“自动调整列宽”的图像

请参阅以下任何与你看到的井号错误对应的主题:

每次打开包含引用其他电子表格中值的公式的电子表格时,系统将提示更新引用或保留引用。

Excel 中断开的引用对话框

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

可随时禁止在启动时显示该对话框。 为此,请转到"高级 >">">"选项",并清除"请求更新自动链接"框。

“请求更新自动链接”选项的图像

重要: 如果这是您第一次在公式中处理断开的链接,如果您需要有关解决断开的链接的刷新,或者如果您不知道是否要更新引用,请参阅"何时更新外部引用 (链接) 控件

如果公式不显示值,请按照以下步骤进行操作:

  • 确保 Excel 设置为在电子表格中显示公式。 为此,请选择"公式"选项卡,在"公式审核"组中,选择"显示公式"。

    提示: 也可使用键盘快捷方式 Ctrl + `(Tab 键上方的键)。 这样做时,列将自动变宽以显示公式,但不要担心,当您切换回普通视图时,列将调整大小。

  • 如果上述步骤仍然无法解决问题,则可能是单元格的格式设置为文本。 右键单击单元格,然后选择“设置单元格格式”>“常规”(或“Ctrl + 1”),然后按“F2”>“Enter”更改格式。

  • 如果有一个列,其中较大单元格区域的格式设置为文本,可以选择区域,应用你选择的数量格式,然后转到"数据">"列 ">"完成"。 这会将此格式应用到所有选定的单元格。

    “数据”>“文本分列”对话框的图像

当公式未计算时,您需要检查 Excel 中是否启用了自动计算。 如果启用了手动计算,公式将不会进行计算。 请按照以下步骤检查自动 计算

  1. 选择" 文件" 选项卡,选择"选项",然后选择 "公式" 类别。

  2. 在“计算选项”部分中,在“工作簿计算”下,确保选择了“自动”选项。

    “自动和手动计算”选项的图像

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

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

有关循环引用详细信息,请参阅"删除或允许循环引用"。

如果你的项未以等号开头,则它不是一个公式,也不进行计算 - 这是一个常见错误。

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

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

当您在公式中使用函数时,每个左括号需要一个对应的右括号,才能保证函数正常工作。 请确保所有括号均成对出现。 例如,公式 =IF (B5<0) ,"无效",B5*1.05) 将不起作用,因为有两个右括号,但只有一个右括号。 正确的公式如下所示:=IF(B5<0,"Not valid",B5*1.05)

Excel 函数都有必需的参数,必须提供这些值才能保证函数正常工作。 只有少数几个函数(如 PITODAY)不需要参数。 开始在函数中键入时,请务必检查公式语法,以确保函数有必需的参数。

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

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

“函数引用”工具栏的屏幕截图

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

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

不要在公式中输入用美元符号 ($) 或小数分隔符 (,) 格式的数字,因为美元符号指示绝对引用,逗号是参数分隔 符。 不要在公式中输入 $1,000,而是应该输入 1000

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

注意: 你可以在使用不带格式的数字(常量)输入公式之后使用小数分隔符和货币符号设置公式结果的格式。 在公式中输入常量通常不是一个好方法,因为如果需要以后更新,它们可能很难找到,并且更容易被错误键入。 最好将常量放在单元格中,这样它们就已打开且易于引用。

如果单元格的数据类型无法在计算中使用,公式就可能无法返回预期结果。 例如,如果在格式设置为文本的单元格中输入简单公式 =2+3,Excel 将无法计算所输入的数据。 您在单元格中看到的内容就是 =2+3。 若要修复此错误,将单元格的数据类型从“文本”更改为“常规”,如下所示:

  1. 选择相应的单元格。

  2. 选择"开始"并选择箭头以展开"数字"或"数字 (或按Ctrl + 1) 。 然后选择"常规"。

  3. F2 将单元格置于编辑模式,然后按 Enter 接受公式。

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

在公式中将 x 用作乘法运算符十分常见,但 Excel 只能接受星号 (*) 进行乘法。 如果在公式中使用常量,Excel 将显示一条错误消息,可通过将 x 替换为星号 (*) 来修复公式。

询问是否用 * 代替 x 表示相乘的消息框

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

在单元格引用中使用 x 而不是 * 代表相乘时,出现 #NAME? 错误

如果您创建了一个包含文本的公式,请将该文本用引号括起来。

例如,公式 ="Today is " & TEXT(TODAY(),"dddd, mmmm dd") 将文本“Today is”与 TEXTTODAY 函数的计算结果合并在一起,返回类似于 Today is Monday, May 30 的信息。

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

在一个函数中,您可以组合(或嵌套)最多 64 层函数。

例如,公式 =IF (SQRT (PI () ) <2,"小于 2!","大于 2!") 有 3 个级别的函数; PI 函数 嵌套在 SQRT 函数中,而 SQRT函数又嵌套在 IF 函数内

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

例如,若要返回工作簿中名为"季度数据"的工作表中单元格 D3 的值,请键入:="季度数据"!D3. 如果没有工作表名称周围的引号,公式会显示 #NAME? 错误.

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

在键入对另一个工作簿中的值或单元格的引用时,请包括工作簿名称(用方括号 ([]) 括起来),后跟具有值或单元格的工作表的名称。

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

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

例如,=ROWS('C:\My Documents\[第 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 ! 显示在目标单元格中,因为无法再引用包含公式中使用的值的单元格。

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

  1. 在工作表上,选择你想要复制的包含公式结果值的单元格。

  2. 在"主页"选项卡上的"剪贴板"组中,选择"复制 按钮图像

    Excel 功能区图像

    键盘快捷方式:按 Ctrl+C。

  3. 选择粘贴区域左上角的单元格。

    提示: 若要将所选内容移动或复制到其他工作表或工作簿,请选择另一个工作表标签或切换到另一个工作簿,然后选择粘贴区域左上角的单元格。

  4. 在"开始"选项卡上的"剪贴板"组中,选择"粘贴 按钮图像 ",然后选择"粘贴值",或者按 Alt > E > S > V > Enter for Windows,或在 Mac 上按 Option > 命令 > V > V > Enter。

要了解复杂或嵌套公式如何计算最终结果,您可以计算该公式的值。

  1. 选择要求值的公式。

  2. 选择公式>公式

    “公式”选项卡上的“公式审核”组

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

    “公式求值”对话框

  4. 如果公式的下划线部分是对另一个公式的引用,请选择"步骤输入"以在"评估"框中显示其他公式。 选择"步 出"以返回到上一个单元格和公式。

    次引用出现在公式中时,或者如果公式引用了另一个工作簿中的单元格,则"单步执行"按钮不可用。

  5. 继续操作,直到已对公式的每一部分求值。

    "公式计算公式"工具不一定告诉你公式损坏的原因,但有助于指出位置。 对于较大公式,这是非常方便的工具,因为通过其他方式找到问题可能很困难。

    注意: 

    • IFCHOOSE 函数的某些部分不会进行求值,“求值”框中可能会显示“#N/A”错误。

    • 空白引用在“求值”框中显示为零值 (0)。

    • 每次工作表更改时,都会重新计算某些函数。 这些函数(包括 RANDAREASINDEXOFFSETCELLINDIRECTROWSCOLUMNSNOWTODAYRANDBETWEEN 函数)可能会导致“公式求值”对话框显示的结果不同于工作表上的单元格中的实际结果。

需要更多帮助吗?

你始终可以在 Excel 技术社区中咨询专家或在 Answers 社区获取

另请参阅

Excel 中的公式概述

检测公式中的错误

Excel 函数(按字母顺序)

Excel 函数(按类别列出)

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

×