如何避免损坏的公式

如何避免损坏的公式

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

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

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

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

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

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

如果不确定此时该做什么或需要哪种类型的帮助,你可以在Excel 社区论坛中搜索类似问题或发布你自己的问题。

Excel 社区论坛的链接

如果你想继续,以下清单提供疑难解答步骤,帮助你弄清公式中可能已经出错的内容。

Excel 会引发各种井号 (#) 错误,如#VALUE!、#REF!、#NUM、#N/A、#DIV/0!、#NAME? 以及 #NULL!,以指示公式中的某些内容不正确。例如,#VALUE! 错误是由不正确的格式或者参数中不支持的数据类型引起的。或者,如果公式引用的单元格已被删除或已使用其他数据进行了替换,你将看到 #REF! 错误。每个错误的疑难解答指南各不相同。

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

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

参考下列与你所见的井号错误对应的任何主题:

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

Excel 中断开的引用对话框

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

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

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

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

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

  • 确保 Excel 已设置为显示电子表格中的公式。要执行此操作,单击“公式”选项卡,在“公式审核”组中,单击“显示公式”。

    提示: 也可使用键盘快捷方式 Ctrl + `(Tab 键上方的键)。执行此操作时,列将自动变宽以显示公式,但不要担心,当你切换回普通视图时,列将重设大小。

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

  • 如果在某一列中有大片区域的单元格格式设置为文本,你可选择此区域,应用你选择的数字格式,转到“数据”>“文本分列”>“完成”。这会将此格式应用到所有选定的单元格。

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

当公式不进行计算时,必须检查 Excel 中是否启用了自动计算。若果启用了手动计算,则公式将不进行计算。请按照以下步骤检查“自动计算”:

  1. 依次单击“文件”选项卡、“选项”“公式”类别。

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

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

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

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

有关循环引用的详细信息,请参阅查找和修复循环引用

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

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

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

当你在公式中使用函数时,每个左括号需要一个对应的右括号,才能保证函数正常工作,因此,请确保所有括号均成对出现。例如,公式 =IF(B5<0),"Not valid",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+3,Excel 将无法计算所输入的数据。在单元格中看到的内容就是 =2+3。若要修复此错误,请将单元格的数据类型由“文本”更改为“常规”,如下所示:

  1. 选择相应的单元格。

  2. 单击“开始”>“数字格式”旁边的箭头(或按 Ctrl + 1),然后单击“常规”。

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

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

在公式中使用 x 作为乘法运算符是很常见的,但是 Excel 仅接受将星号 (*) 用于乘法运算。如果在公式中使用常量,Excel 会显示错误消息。可以通过将 x 替换为星号 (*) 来修复公式。

询问是否用 * 代替 x 表示相乘的消息框
使用 x 而不是 * 与常量相乘时出现错误消息

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

在单元格引用中使用 x 而不是 * 代表相乘时,出现 #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,"Less than two!","More than two!") 包含 3 层函数:PI 函数嵌套在 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. 在“开始”选项卡上,在“剪贴板”组中,单击“粘贴按钮图像 ,然后单击“粘贴值”,或对于 Windows 按 Alt > E > S > V > Enter,或在 Mac 上按 option > command > V > V > enter

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

  1. 选择要求值的公式。

  2. 单击“公式”>“公式求值”。

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

  3. 单击“求值”以检查带下划线的引用的值。 求值结果将以斜体显示。

    “公式求值”对话框

  4. 如果公式的下划线部分是对另一个公式的引用,请单击“步入”以在“求值”框中显示其他公式。 单击“步出”将返回到以前的单元格和公式。

    当引用第二次出现在公式中,或者公式引用了另外一个工作簿中的单元格时,“步入”按钮不可用。

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

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

    注释: 

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

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

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

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

另请参阅

Excel 中的公式概述

检测公式中的错误

Excel 函数(按字母顺序列出)

Excel 函数(按类别列出)

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

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×