excel使用公式常见问题-找工易

您已输入一个公式，但是不起作用。相反，您已获得有关“循环引用”的消息。许多人具有相同的问题，发生这种情况的原因是您的公式试图计算自身，您应关闭迭代计算功能。下图显示了它的外观：

导致循环引用的公式

公式 = D1 + D2 + D3 中断，因为它在单元格 D3 中，并且正在尝试计算自身。若要解决此问题，您可以将公式移动到另一个单元格。按ctrl + X剪切公式，选择另一个单元格，然后按Ctrl + V进行粘贴。

另一个常见错误是使用包含对其自身的引用的函数；例如，单元格 F3 包含 =SUM(A3:F3)。示例如下：

还可尝试以下方法之一：

如果刚输入公式，则从该单元格开始，检查是否引用了单元格本身。例如，单元格 A3 可能包含公式 =(A1+A2)/A3。 =A1+1（位于单元格 A1 中）等公式也会导致循环引用错误。

查找时，请检查是否存在间接引用。在单元格 A1 中输入公式时，它使用 B1 中的公式，而该公式反过来引用单元格 A1，此时会发生这种情况。如果这使您感到迷惑，则想象它会对 Excel 产生什么影响。

如果你无法找出错误，请单击“公式”选项卡，单击“错误检查”旁边的箭头，指向“循环引用”，然后单击子菜单中列出的第一个单元格。

“循环引用”命令

检查单元格中的公式。如果不能确定循环引用是否是由该单元格引起，请单击“循环引用”子菜单中的下一个单元格。

重复步骤 1 至步骤 3 中的任意步骤或所有步骤，以继续检查和更正工作簿中的循环引用，直至状态栏中不再显示“循环引用”。

提示

左下角的状态栏显示“循环引用”和循环引用的单元格地址。

如果循环引用位于其他工作表而非活动工作表中，则状态只会显示“循环引用”而不含单元格地址。

可以通过双击追踪箭头在循环引用所涉及的单元格之间移动。箭头表示影响当前所选单元格数值的单元格。依次单击“公式”、“追踪引用单元格”或“追踪从属单元格”，以显示追踪箭头。

了解循环引用警告消息

Excel 首次发现循环引用时，它将显示警报消息。单击“确定”或关闭消息窗口。

关闭消息时，Excel 将在单元格中显示零或最后计算的值。现在，您也许会说，“等等，是最后计算的值吗？ ”是的。在某些情况下，公式在尝试计算自身之前可以成功运行。例如，使用 IF 函数的公式可能会一直进行运算，直到用户输入一个让该公式计算自身的参数（公式正常运行所必须的一条数据）时为止。上述情况发生时，Excel 会保留最后一次成功计算的数值。

如果您怀疑在某个不显示零的单元格中有循环引用，请尝试以下操作：

单击编辑栏中的公式，然后按 Enter。

必要在许多情况下，如果您创建了包含循环引用的其他公式，Excel 不会再次显示警告消息。以下列表显示了将显示警告消息的部分（但不是全部）方案：

在任何打开的工作簿中创建第一个循环引用实例

删除所有打开的工作簿中的所有循环引用，然后创建新的循环引用

关闭所有工作簿，创建新工作簿，然后输入包含循环引用的公式

打开包含循环引用的工作簿

在未打开任何其他工作簿时，打开工作簿，然后创建循环引用

了解迭代计算

有时，您可能希望使用循环引用，因为它们能使函数迭代重复，直到满足特定的数值条件。这可能会减慢计算机的速度，因此，迭代计算在 Excel 中通常处于禁用状态。

除非您熟悉迭代计算，否则您可能不想使任何循环引用保持不变。如果这样做，您可以启用迭代计算，但您需要确定公式应重新计算的次数。当您启用迭代计算而未更改最大迭代次数或最大误差的值时，Excel 将在 100 次迭代后或迭代之间的所有循环引用误差值低于 0.001 后停止计算，以先满足的条件为准。但是，您可以控制最大迭代次数和可接受误差的值。

如果使用的是 Excel 2010 或更低版本，请单击“文件”>“选项”>“公式”。如果使用的是 Excel for Mac，请单击“Excel”菜单，然后单击“首选项”>“计算”。

如果您使用的是 Excel 2007，请单击 " Microsoft Office 按钮 "，单击 " Excel 选项"，然后单击 "公式" 类别。

在“计算选项”部分中，选中“启用迭代计算”复选框。在 Mac 上，单击“使用迭代计算”。

若要设置 Excel 进行重新计算的最大次数，请在“最多迭代次数”框中键入迭代次数。迭代次数越高，Excel 计算工作表所需的时间就越长。

在“最大误差”框中，键入继续迭代所需的最小值。这是所有计算值中的最小误差。数值越小，结果就越精确，Excel 计算工作表所需的时间也越长。

迭代计算可能有三种结果：

解析收敛，这意味着达到稳定的最终结果。这是理想情况。

解析分离，这意味着从一个迭代到另一个迭代的当前和先前的结果差异不断增加。

解析在两个值之间切换。例如，第一次迭代后结果为 1，下一次迭代后结果为 10，再下一次迭代后结果为 1，依此类推。

excel公式计算有误怎么办，如何使用IFERROR函数查找公式错误

可以使用 IFERROR 函数捕获和处理公式中的错误。如果公式的计算结果为错误值，则 IFERROR 返回您指定的值;否则，它将返回公式的结果。

语法

IFERROR(value, value_if_error)

IFERROR 函数语法具有下列参数：

取值必需。检查是否存在错误的参数。

value_if_error 必需。公式计算错误时返回的值。计算以下错误类型： #N/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？或 #NULL！。

备注

如果 value 或 value_if_error 为空单元格，IFERROR 将其视为空字符串值（""）。

如果 value 是数组公式，则 IFERROR 返回值中指定的区域中每个单元格的结果数组。请参阅下面的第二个示例。

示例

复制下表中的示例数据，然后将其粘贴进新的 Excel 工作表的 A1 单元格中。要使公式显示结果，请选中它们，按 F2，然后按 Enter。

配额	销售数量
210	35
55	0
	23
公式	说明	结果
=IFERROR(A2/B2,"计算中有错误")	检查第一个参数中公式的错误（210 除以 35），未找到错误，返回公式结果	6
=IFERROR(A3/B3,"计算中有错误")	检查第一个参数中公式的错误（55 除以 0），找到被 0 除错误，返回 value_if_error	计算中有错误
=IFERROR(A4/B4,"计算中有错误")	检查第一个参数中公式的错误（"" 除以 23），未找到错误，返回公式结果。	0

示例 2

配额	销售数量	比率
210	35	6
55	0	计算中有错误
	23	0
公式	说明	结果
=C2	检查数组中第一个元素的第一个参数中的公式中的错误（A2/B2 或 210 除以 35），未找到错误，返回公式结果	6
=C3	检查数组中第二个元素的第一个参数中的公式中的错误（A3/B3 或 55 除以 0），找到被 0 除错误，返回 value_if_error	计算中有错误
=C4	检查数组中第三个元素的第一个参数中的公式中的错误（A4/B4 或 "" 除以 23），未找到错误，返回公式结果	0
	注意：如果你有Microsoft 365的最新版本，则可以在输出区域左上角的单元格中输入公式，然后按 enter 以将公式确认为动态数组公式。否则，必须先选择 "输出区域"，然后在输出区域左上角的单元格中输入公式，然后按 CTRL + SHIFT + ENTER 确认公式，然后再将公式输入为旧数组公式。 Excel 将使用括号将公式括起来。

excel怎么把公式返回的错误值隐藏起来

假设电子表格公式具有您所预见到的、不需要更正的错误，但您想要改进结果的显示。可通过多种方式隐藏单元格中的错误值和错误指示器。

公式可能会返回错误的原因有很多。例如, 不允许被0除, 如果输入公式 = 1/0, Excel 将返回 #DIV/0。错误值包括 #DIV/0!、#N/A、#NAME？、#NULL!、#NUM!、#REF! 和 #VALUE!。

将错误值转换为零，并使用格式隐藏值

您可以通过将它们转换为数字（例如 0），然后应用隐藏该值的条件格式。

创建一个示例错误

打开一个空白工作簿，或创建一个新工作表。

在单元格 B1 中输入
3 , 在单元格 c1 中输入
0 , 在单元格 A1 中输入公式
= B1/c1。
#DIV/0! 单元格 A1 中出现错误。

选中 A1，然后按 F2 以编辑该公式。

在等号 (=) 后键入后跟左括号的
IFERROR。

IFERROR(

将光标移动到该公式的末尾。

键入
,0
)，即逗号后跟 0 和右括号。
公式
=B1/C1 将变为
=IFERROR(B1/C1
,0)。

按 Enter 完成该公式。
该单元格的内容现在应显示 0 而不是。错误 #DIV!。

应用条件格式

选中包含错误的单元格，然后在“开始”选项卡上，单击“设置条件格式”。

单击“新建规则”。

在“新建格式规则”对话框中，单击“只为包含以下内容的单元格设置格式”。

在
“只为满足以下条件的单元格设置格式”下，确保在第一个列表框中显示“单元格值”，在第二个列表框中显示“等于”，然后在右侧的文本框中键入 0。

单击“格式”按钮。

单击“数字”选项卡，然后在“类别”下，单击“自定义”。

在“类型”框中输入
;;;（三个分号），然后单击“确定”。再次单击“确定”。
单元格中的 0 将消失。出现此情况的原因是，
;;; 自定义格式将使单元格中的任何数字都不显示。但实际值 (0) 仍在单元格中。

-----------------------------------------------------------------------------------------------------------------------

将文本转变为白色以隐藏错误值

使用以下过程对包含错误的单元格设置格式, 以便这些单元格中的文本以白色字体显示。这将使这些单元格中的错误文本完全不可见。

选择包含错误值的单元格区域。

在“开始”选项卡上的“样式”组中，单击“条件格式”旁边的箭头，然后单击“管理规则”。
将出现“条件格式规则管理器”对话框。

单击“新建规则”。
将出现“新建格式规则”对话框。

在“选择规则类型”下，单击“只为包含以下内容的单元格设置格式”。

在“编辑规则说明”下的“只为满足以下条件的单元格设置格式”列表中，选择“错误”。

单击“格式”，然后单击“字体”选项卡。

单击箭头以打开 "颜色" 列表, 然后在 "主题颜色" 下, 选择白色颜色。


-----------------------------------------------------------------------------------------------------------------------

以短划线、#N/A 或 NA 代替错误值

有时, 你不希望在单元格中显示错误 vales, 而是希望文本字符串 (如 "#N/A"、"短划线" 或字符串 "NA") 出现。若要实现这一点，可以使用
IFERROR 和
NA 函数，如下例所述。

函数详细信息

IFERROR  使用此函数可确定单元格中是否包含错误, 或者公式结果将返回错误。

N  使用此函数可返回单元格中 #N/A 的字符串。语法为 =
NA ()。

   -----------------------------------------------------------------------------------------------------------------------

隐藏数据透视表中的错误值


单击数据透视表。
将显示 "数据透视表工具"。

Excel 2016 和Excel 2013: 在 "分析" 选项卡上的 "数据透视表" 组中, 单击 "选项" 旁边的箭头, 然后单击 "选项"。

Excel 2010 和Excel 2007: 在 "选项" 选项卡上的 "数据透视表" 组中, 单击 "选项" 旁边的箭头, 然后单击 "选项"。

单击“布局和格式”选项卡，然后执行下列一个或多个操作：


更改错误值显示选中 "格式" 下的 "为错误值显示" 复选框。然后在框中，键入要替代错误值的值。若要将错误值显示为空单元格，请删除框中的所有字符。


更改空单元格显示选中“对于空单元格，显示”复选框。然后在框中，键入要在空单元格中显示的值。若要显示空白单元格，请删除框中的所有字符。若要显示零，请清除该复选框。


-----------------------------------------------------------------------------------------------------------------------

隐藏单元格中的错误指示器

如果单元格包含结果为错误值的公式，则该单元格的左上角将出现一个三角形（错误指示器）。您可使用以下过程来防止出现这些指示器。

单元格中的公式有问题

在Excel 2016 、 Excel 2013 和Excel 2010 中: 单击 "文件>选项>公式"。

在Excel 2007 中: 单击 " Microsoft Office 按钮 > Excel 选项>公式"。

在“错误检查”下，清除“允许后台错误检查”复选框。