Excel中破坏的公式怎么办？一文详解故障排除与解决方法
Excel中破坏的公式怎么办？一文详解故障排除与解决方法
在使用Excel时，经常会遇到公式无法正常解析的情况。本文将详细介绍各种可能导致公式错误的原因，并提供相应的解决方案。
是否在公式中使用正确的列表分隔符？
具有多个参数的公式会使用列表分隔符来分隔其参数。使用的分隔符可能因操作系统区域设置和Excel设置而异。最常见的列表分隔符是逗号“,”和分号“;”。
如果公式的任何函数使用了不正确的分隔符，则公式将会中断。有关详细信息，请参阅：列表分隔符设置不正确时的公式错误
是否看到井号 (#) 错误？
Excel 会引发各种井号 (#) 错误，如 #VALUE!、#REF!、#NUM、#N/A、#DIV/0!、#NAME? 以及 #NULL!，以指示公式中的某些内容不正确。例如 #VALUE! 错误是由不正确的格式或者参数中不支持的数据类型引起的。或者，如果公式引用的单元格已被删除或已使用其他数据进行了替换，你将看到 #REF! 错误。每个错误的疑难解答指南各不相同。
注意: #### 不是与公式相关的错误。它仅表示列宽不够，无法显示单元格内容。只需拖动列以使其变宽，或前往**“主页”>“格式”>“自动调整列宽”**。
参考下列与你所见的井号错误对应的任何主题：
公式中存在断开的链接
每次打开包含引用其他电子表格中值的电子表格时，系统将提示你更新引用或按原样保留引用。
Excel 显示以上对话框以确保当引用值发生更改时，当前电子表格中的公式始终指向最新值。你可以选择更新引用，如果不想更新也可选择跳过。即使你选择不更新引用，也可根据需要随时手动更新电子表格中的链接。
可随时禁止在启动时显示该对话框。 若要执行此操作，请转到**“文件”>“选项”>“高级”>“常规”，并清除“请求自动更新链接**”框。
重要: 如果是首次处理公式中的断开的链接、需要有关解决断开的链接的补习课程，或者不确定是否要更新引用，请参阅控制何时更新外部引用（链接）。
公式将显示语法，而不是值
如果公式不显示值，请按照以下步骤进行操作：
- 确保 Excel 已设置为显示电子表格中的公式。 要执行此操作，请选择“公式”选项卡，在“公式审核”组中，选择“显示公式”。
提示: 也可使用键盘快捷方式Ctrl + `（Tab 键上方的键）。 执行此操作时，列会自动扩大以显示公式，但不必担心，当你切换回普通视图时，列将调整大小。
如果以上步骤仍不能解决此问题，则单元格的格式有可能设置为文本。 右键单击单元格，然后选择**“设置单元格格式”>“常规”（或“Ctrl + 1”），然后按“F2”>“Enter”**更改格式。
如果在某一列中有大片区域的单元格格式设置为文本，则可选择此区域，应用你选择的数字格式，转到**“数据”>“文本分列”>“完成”**。 这会将此格式应用到所有选定的单元格。
公式不进行计算
当公式不进行计算时，需要检查 Excel 中是否启用了自动计算。 若果启用了手动计算，则公式不会进行计算。 请按照以下步骤检查自动计算。
选择“文件”选项卡，选择“选项”，然后选择“公式”类别。
在“计算选项”部分中，在“工作簿计算”下，确保选择了“自动”选项。
有关计算的详细信息，请参阅更改公式的重新计算、迭代或精度。
公式中存在一个或多个循环引用
当公式引用其所在的单元格时，将出现循环引用。 解决方法是将公式移动到另一个单元格或更改公式语法以避免循环引用。 但是，在某些情况下，可能需要使用循环引用，因为它们能使函数迭代，即重复到满足特定数值条件为止。 在这种情况下，需要启用删除或允许循环引用。
有关循环引用的详细信息，请参阅删除或允许循环引用。
函数是否以等号 (=) 开头？
如果你的条目不以等号开头，则它不是公式，并且不会进行计算，这是一个常见的错误。
键入类似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) ，“无效”，B5*1.05)不起作用，因为有两个右括号，但只有一个左括号。 正确的公式如下所示：=IF(B5<0,"Not valid",B5*1.05)**。
是否所有必需的参数都在语法中？
Excel 函数都有必需的参数，必须提供这些值才能保证函数正常工作。 只有少数几个函数（如PI或TODAY）不需要参数。 开始在函数中键入时，请务必检查公式语法，以确保函数有必需的参数。
例如,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。 若要解决此问题，请将单元格的数据类型从“文本”更改为“常规”**，如下所示：
选择相应的单元格。
选择“开始”，然后选择箭头以展开“数字”或“数字格式”组（或按Ctrl + 1）。 然后选择“常规”。
按F2将单元格置于编辑模式，然后按Enter接受公式。
在使用“数字”数据类型的单元格中输入的日期可能会显示为数字日期值，而不是日期。 要将数字显示为日期，请在“数字格式”库中选择“日期”格式。
是否尝试进行乘法运算但没有使用 * 符号？
在公式中使用x作为乘法运算符是很常见的，但是 Excel 仅接受将星号 () 用于乘法运算。 如果在公式中使用常量，Excel 将显示一条错误消息，并且可以通过将x替换为星号 () 来修复公式。
但是，如果使用单元格引用，Excel 将返回 #NAME? 错误。
公式文本的两侧是否缺少引号？
如果您创建了一个包含文本的公式，请将该文本用引号括起来。
例如，公式**="Today is " & TEXT(TODAY(),"dddd, mmmm dd")将文本“Today is”与TEXT和TODAY函数的计算结果合并在一起，返回类似于Today is Monday, May 30**的信息。
在该公式中，“Today is ”的右引号之前有一个空格；这将在“Today is”和“Monday, May 30”之间提供所需空格。 如果没有用引号括起文本，公式可能会显示#NAME? 错误.
公式中的函数是否有多于 64 个？
在一个函数中，您可以组合（或嵌套）最多 64 层函数。
例如，公式**=IF(SQRT(PI())<2,"Less than two!","More than two!")**包含 3 层函数；PI 函数嵌套在SQRT 函数内，后者又嵌套在IF 函数内。
工作表名称是否包含在单引号中？
当您键入另一个工作表中的值或单元格的引用，并且该工作表的名称包含非字母字符（例如空格）时，请用单引号 (') 将名称引起。
例如，若要返回工作簿中 Quarterly Data 工作表中的单元格 D3 的值，请键入：='Quarterly Data'!D3。 如果工作表名称不加引号，公式会显示#NAME? 错误.
也可以选择另一个工作表中的值或单元格以在公式中引用它们。 然后，Excel 会自动在工作表名称周围添加引号。
如果公式引用了外部工作薄，是否正确引用了该工作薄的路径？
在键入对另一个工作簿中的值或单元格的引用时，请包括工作簿名称（用方括号 ([]) 括起来），后跟具有值或单元格的工作表的名称。
例如，若要引用在 Excel 中打开的 Q2 Operations 工作簿中“销售”工作表上的单元格 A1 到 A8，请键入：=[Q2 Operations.xlsx]Sales！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 ！ ) 错误，因为不再可以引用包含公式中所使用的值的单元格。
可以通过在目标单元格中粘贴公式的结果值而不粘贴公式以避免此错误。
在工作表上，选择你想要复制的包含公式结果值的单元格。
在“主页”选项卡上的“剪贴板”组中，选择“复制”。
键盘快捷方式：按 Ctrl+C。
- 选择粘贴区域左上角的单元格。
提示: 若要将选定区域移动或复制到其他工作表或工作簿，请选择另一个工作表标签或切换到另一个工作簿，然后选择位于粘贴区域左上角的单元格。
- 在“主页”选项卡上的“剪贴板”组中，选择“粘贴”，然后选择“粘贴值”，或在 Mac 上按Alt > E > S > V > Enter，或在 Mac 上按**“选项 > 命令 > V > V > Enter**”。
如果有嵌套的公式，请对公式进行分步求值
要了解复杂或嵌套公式如何计算最终结果，您可以计算该公式的值。
选择要求值的公式。
选择“公式”>“公式求值”。
选择“求值”以检查带下划线的引用的值。 求值结果将以斜体显示。
如果公式的下划线部分是对另一个公式的引用，请选择“步入”以在“求值”框中显示其他公式。 选择“步出”将返回到以前的单元格和公式。
引用第二次出现在公式中时，或者如果公式引用了另一个工作簿中的单元格，则“单步执行”按钮不可用。
- 继续操作，直到已对公式的每一部分求值。
“公式求值”工具不一定告诉你公式损坏的原因，但会帮助指出损坏的位置。 对于较大公式，这是非常方便的工具，因为通过其他方式找到问题可能很困难。
注意:
不会计算 IF和CHOOSE函数的某些部分，并且“评估”框中可能会出现#N/A错误。
空白引用在“求值”框中显示为零值 (0)。
每次工作表更改时都会重新计算某些函数。 这些函数（包括RAND、AREAS、INDEX、OFFSET、CELL、INDIRECT、ROWS、COLUMNS、NOW、TODAY和RANDBETWEEN函数）可能会导致“公式求值”对话框显示的结果不同于工作表上的单元格中的实际结果。