Excel 数据分析 - 公式审计

---

---

您可能需要检查公式的准确性或查找错误的来源。Excel 公式审计命令提供了一种简单的方法来查找

• 哪些单元格参与了活动单元格中公式的计算。
• 哪些公式引用了活动单元格。

这些查找结果以箭头线的方式图形化显示，使可视化变得更容易。您可以使用单个命令显示活动工作表中的所有公式。如果您的公式引用了不同工作簿中的单元格，请打开该工作簿。Excel 无法转到未打开的工作簿中的单元格。

设置显示选项

您需要检查正在使用的所有工作簿的显示选项是否设置正确。

• 单击文件 > 选项。
• 在 Excel 选项对话框中，单击高级。
• 在工作簿的显示选项中 -
  • 选择工作簿。
  • 检查“对于对象，显示”下是否选择了“全部”。
• 对要审计的所有工作簿重复此步骤。

跟踪前导单元格

前导单元格是指活动单元格中的公式引用的那些单元格。

在以下示例中，活动单元格为 C2。在 C2 中，您有公式=B2*C4。

B2 和 C4 是 C2 的前导单元格。

要跟踪单元格 C2 的前导单元格，

• 单击单元格 C2。
• 单击公式选项卡。
• 单击“公式审计”组中的“跟踪前导单元格”。

将显示两条箭头，一条从 B2 到 C2，另一条从 C4 到 C2，跟踪前导单元格。

请注意，要跟踪单元格的前导单元格，该单元格应包含具有有效引用的公式。否则，您将收到错误消息。

• 单击不包含公式的单元格或单击空单元格。
• 单击“公式审计”组中的“跟踪前导单元格”。

您将收到一条消息。

移除箭头

单击“公式审计”组中的“移除箭头”。

工作表中的所有箭头都将消失。

跟踪后继单元格

后继单元格包含引用其他单元格的公式。这意味着，如果活动单元格参与了另一个单元格中的公式计算，则另一个单元格是活动单元格的后继单元格。

在下面的示例中，C2 有公式=B2*C4。因此，C2 是单元格 B2 和 C4 的后继单元格。

要跟踪单元格 B2 的后继单元格，

• 单击单元格 B2。
• 单击公式选项卡。
• 单击“公式审计”组中的“跟踪后继单元格”。

将显示一条从 B2 到 C2 的箭头，表明 C2 依赖于 B2。

要跟踪单元格 C4 的后继单元格 -

• 单击单元格 C4。
• 单击公式选项卡 > “公式审计”组中的“跟踪后继单元格”。

将显示另一条从 C4 到 C2 的箭头，表明 C2 也依赖于 C4。

单击“公式审计”组中的“移除箭头”。工作表中的所有箭头都将消失。

注意 - 要跟踪单元格的后继单元格，该单元格应被另一个单元格中的公式引用。否则，您将收到错误消息。

• 单击单元格 B6，它没有被任何公式引用，或者单击任何空单元格。
• 单击“公式审计”组中的“跟踪后继单元格”。您将收到一条消息。

使用公式

您已经了解了前导单元格和后继单元格的概念。现在，考虑一个包含多个公式的工作表。

• 单击“考试成绩”表中“及格类别”下方的单元格。
• 单击“跟踪前导单元格”。其左侧的单元格（分数）和区域 E4:F8 将被映射为前导单元格。
• 对“考试成绩”表中“及格类别”下方的所有单元格重复此操作。

• 单击“学生成绩”表中“及格类别”下方的单元格。

• 单击“跟踪后继单元格”。“考试成绩”表中“及格类别”下方的所有单元格都将被映射为后继单元格。

显示公式

下面的工作表包含了销售人员在东、北、南、西四个区域的销售汇总。

• 单击功能区上的“公式”选项卡。

• 单击“公式审计”组中的“显示公式”。工作表中的公式将显示出来，以便您知道哪些单元格包含公式以及公式是什么。

• 单击“总销售额”下方的单元格。

• 单击“跟踪前导单元格”。工作表图标出现在箭头的末端。工作表图标表示前导单元格在不同的工作表中。

双击箭头。“转到”对话框出现，显示前导单元格。

如您所见，有四个前导单元格，分布在四个不同的工作表中。

• 单击其中一个前导单元格的引用。
• 该引用将显示在“引用”框中。
• 单击“确定”。包含该前导单元格的工作表将显示。

评估公式

要逐步了解单元格中复杂公式的工作原理，可以使用“评估公式”命令。

考虑单元格 C14 中的公式 NPV（中间年份）。该公式为

=SQRT (1 + C2)*C10

• 单击单元格 C14。
• 单击功能区上的“公式”选项卡。
• 单击“公式审计”组中的“评估公式”。“评估公式”对话框出现。

在“评估公式”对话框中，公式显示在“评估”下的框中。通过多次单击“评估”按钮，公式将逐步进行评估。带下划线的表达式将始终是下一步执行的表达式。

这里，公式中 C2 带有下划线。因此，它将在下一步进行评估。单击“评估”。

单元格 C2 的值为 0.2。因此，C2 将被评估为 0.2。“1+0.2”带下划线，表示它是下一步。单击“评估”。

1+0.2 将被评估为 1.2。“SQRT(1.2)”带下划线，表示它是下一步。单击“评估”。

SQRT(1.2) 将被评估为 1.09544511501033。“C10”带下划线，表示它是下一步。单击“评估”。

C10 将被评估为 4976.8518518515。

1.09544511501033*4976.8518518515 带下划线，表示它是下一步。单击“评估”。

1.09544511501033*4976.8518518515 将被评估为 5,451.87。

没有更多表达式需要评估，这就是答案。“评估”按钮将更改为“重新开始”按钮，表示评估已完成。

错误检查

在工作表和/或工作簿准备好进行计算后，进行错误检查是一个好习惯。

考虑以下简单的计算。

单元格中的计算导致错误 #DIV/0！。

• 单击单元格 C5。

• 单击功能区上的“公式”选项卡。

• 单击“公式审计”组中“错误检查”旁边的箭头。在下拉列表中，您会发现“循环引用”已停用，表示您的工作表中没有循环引用。

• 从下拉列表中选择“跟踪错误”。

计算活动单元格所需的单元格以蓝色箭头表示。

• 单击“移除箭头”。
• 单击“错误检查”旁边的箭头。
• 从下拉列表中选择“错误检查”。

“错误检查”对话框出现。

观察以下内容 -

• 如果单击“有关此错误的帮助”，将显示 Excel 有关该错误的帮助。

• 如果单击“显示计算步骤”，将出现“评估公式”对话框。

• 如果单击“忽略错误”，“错误检查”对话框将关闭，如果再次单击“错误检查”命令，它将忽略此错误。

• 如果单击“在公式栏中编辑”，您将转到公式栏中的公式，以便您可以编辑单元格中的公式。