Excel Data Analysis 简明教程

Excel Data Analysis - Formula Auditing

您可能想要检查公式的准确性或找出错误的根源。Excel 公式审计命令为您找到以下内容提供了简便的方法

  1. 正在哪个单元格参与计算活动单元格中的公式。

  2. 哪些公式是指活动单元格。

这些发现通过箭头线使用图形方式显示，让可视化变得容易。您可以使用单一命令显示活动工作表中的所有公式。如果您的公式引用不同工作簿中的单元格，同时也打开该工作簿。Excel 不能转到未打开的工作簿中的单元格。

Setting the Display Options

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

  1. Click FILE > Options.

  2. 在 Excel 选项对话框中，单击高级。

  3. 在工作簿的显示选项中 − 选择工作簿。检查在对象下，显示中是否已选择全部。

  4. 对您正在审计的所有工作簿重复此步骤。

setting the display options

Tracing Precedents

先决单元格是活动单元格中引用公式的那些单元格。

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

B2 和 C4 是 C2 的先决单元格。

tracing precedents

若要追踪单元格 C2 的先决单元格，

  1. 单击单元格 C2。

  2. Click the Formulas tab.

  3. 单击公式审计组中的追踪先决单元格。

trace precedents

一条从 B2 到 C2 的箭头，另一条从 C4 到 C2 的箭头将显示，追踪先决单元格。

two arrows displayed

请注意，对于追踪单元格的先决单元格，该单元格应具有带有有效引用的公式。否则，您将收到一条错误消息。

  1. 单击不包含公式的单元格或单击一个空单元格。

  2. 单击公式审计组中的追踪先决单元格。

您将收到一条消息。

get message

Removing Arrows

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

removing arrows

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

Tracing Dependents

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

在下面的示例中，C2 的公式为 =B2*C4 。因此，C2 是单元格 B2 和 C4 的一个相关单元格

tracing dependents

要追踪单元格 B2 的相关单元格

  1. 单击单元格 B2。

  2. Click the Formulas tab.

  3. 单击公式审核组中的“追踪相关单元格”。

trace dependents in formula auditing

会出现从 B2 到 C2 的箭头，表示 C2 依赖于 B2。

要追踪单元格 C4 的相关单元格

  1. 单击单元格 C4。

  2. 单击“公式”选项卡>“追踪相关单元格”（在“公式审核”组中）。

会出现从 C4 到 C2 的另一个箭头，表示 C2 也依赖于 C4。

trace dependents of cell

单击公式审核组中的 Remove Arrows 。工作表中的所有箭头都会消失。

Note - 要追踪单元格的相关单元格，该单元格应由另一个单元格中的公式引用。否则，您将收到错误消息。

  1. 单击单元格 B6，它未被任何公式引用，或单击任何空白单元格。

  2. 单击公式审核组中的“追踪相关单元格”。您将收到一条消息。

click trace dependents

Working with Formulae

您已了解“先例”和“相关单元格”的概念。现在，考虑具有多个公式的工作表。

working with formulae

  1. 单击考试结果表中“及格类别”下的一个单元格。

  2. 单击“追踪先例”。其左侧的单元格（分数）和范围 E4:F8 将被映射为先例。

  3. 对考试结果表中“及格类别”下的所有单元格重复此操作。

exam results table

  1. 单击学生成绩表中“及格类别”下的一个单元格。

  2. 单击“追踪相关单元格”。考试结果表中“及格类别”下的所有单元格将被映射为相关单元格。

student grades table

Showing Formulas

下方的工作表包含东部、北部、南部和西部地区销售人员的销售总结。

showing formulas

  1. 单击功能区的 FORMULAS 选项卡。

  2. 单击公式审核组中的显示公式。工作表中的公式将会出现，以便您了解哪些单元格包含公式以及公式内容。

show formula

  1. 单击 TotalSales 下方的单元格。

  2. 单击追踪先例。一个工作表图标会出现在箭头的末端。工作表图标表示先例位于不同的工作表中。

click trace precedents

双击箭头。一个 Go TO 对话框会出现，显示先例。

go to dialog box

正如您所看到的，有四个先例，位于四个不同的工作表中。

  1. 单击一个先例的引用。

  2. 引用出现在引用框中。

  3. 单击确定。包含该先例的工作表出现。

Evaluating a Formula

要逐个步骤了解单元格中复杂公式的工作原理，您可以使用求值公式命令。

考虑单元格 C14 中的公式 NPV（年中）。公式是

=SQRT (1 + C2)*C10

  1. 单击单元格 C14。

  2. 单击功能区的 FORMULAS 选项卡。

  3. 单击公式审核组中的求值公式。求值公式对话框会出现。

evaluating formula

在 Evaluate Formula 对话框中，该公式显示在求值下的框中。通过多次单击 Evaluate 按钮，可以逐个步骤对公式进行求值。带下划线的表达式将始终是接下来要执行的表达式。

evaluate formula

此处，C2 在公式中带下划线。所以，它在下一步中求值。单击 Evaluate 。

click evaluate button

单元格 C2 的值为 0.2。因此，C2 将被求值为 0.2。 1+0.2 带下划线，表明它是下一步。单击 Evaluate 。

click evaluate

1+0.2 将被求值为 1.2。 SQRT(1.2) 带下划线，表明它是下一步。单击 Evaluate 。

evaluate

SQRT(1.2) 将评估为 1.09544511501033。 C10 带有下划线表示为下一步。单击 Evaluate 。

evaluate sqrt

C10 将评估为 4976.8518518515。

1.09544511501033*4976.8518518515 带有下划线表示为下一步。单击 Evaluate 。

restart button

1.09544511501033*4976.8518518515 将评估为 5,451.87。

没有要评估的表达式，这是答案。 Evaluate 按钮将改为 Restart 按钮，表示评估完成。

Error Checking

当您的工作表和/或工作簿准备就绪且已计算完毕时，进行错误检查是一种好习惯。

考虑以下简单计算。

error checking

单元格中的计算结果为错误 #DIV/0!。

  1. 单击单元格 C5。

  2. 单击功能区的 FORMULAS 选项卡。

  3. 单击公式审核组中错误检查旁边的箭头。在下拉列表中，您将发现 Circular References 已禁用，表明您的工作表没有循环引用。

  4. 从下拉列表中选择 Trace Error 。

select trace error

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

activate cell

  1. Click Remove Arrows.

  2. 单击错误检查旁边的箭头。

  3. 从下拉列表中选择错误检查。

select error checking

将显示 Error Checking 对话框。

error checking dialog box

观察以下内容 −

  1. 如果您单击 Help on this error ，将显示 Excel 错误帮助。

  2. 如果您单击 Show Calculation Steps ，将显示公式求值对话框。

  3. 如果您单击 Ignore Error ，将关闭错误检查对话框，如果您再次单击 Error Checking 命令，它将忽略此错误。

  4. 如果您单击 Edit in Formula Bar ，您将被带到公式栏中的公式，以便您可以编辑单元格中的公式。