如何避免损坏的公式

如果 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 函数(按类别列出)

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×