Excel Data Analysis 简明教程

Excel Data Analysis - Formula Auditing

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

You might want to check formulas for accuracy or find the source of an error. Excel Formula Auditing commands provide you an easy way to find

  1. Which cells are contributing in the calculation of a formula in the active cell.

  2. Which formulas are referring to the active cell.

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

These findings are shown graphically by arrow lines that makes the visualization easy. You can display all the formulas in the active worksheet with a single command. If your formulas refer to cells in a different workbook, open that workbook also. Excel cannot go to a cell in a workbook that is not open.

Setting the Display Options

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

You need to check whether the display options for the workbooks you are using are correctly set.

  1. Click FILE > Options.

  2. In the Excel Options dialog box, click Advanced.

  3. In Display options for the workbook − Select the workbook.Check that under For objects, show, All is selected.

  4. Repeat this step for all the workbooks you are auditing.

setting the display options

Tracing Precedents

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

Precedent cells are those cells that are referred to by a formula in the active cell.

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

In the following example, the active cell is C2. In C2, you have the formula =B2*C4.

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

B2 and C4 are precedent cells for C2.

tracing precedents

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

To trace the precedents of the cell C2,

  1. Click in the cell C2.

  2. Click the Formulas tab.

  3. Click Trace Precedents in the Formula Auditing group.

trace precedents

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

Two arrows, one from B2 to C2 and another from C4 to C2 will be displayed, tracing the precedents.

two arrows displayed

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

Note that for tracing precedents of a cell, the cell should have a formula with valid references. Otherwise, you will get an error message.

  1. Click in a cell that does not contain a formula or click in an empty cell.

  2. Click Trace Precedents in the Formula Auditing group.

您将收到一条消息。

You will get a message.

get message

Removing Arrows

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

Click Remove Arrows in the Formula Auditing group.

removing arrows

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

All the arrows in the worksheet will disappear.

Tracing Dependents

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

Dependent cells contain formulas that refer to other cells. That means, if the active cell contributes to a formula in another cell, the other cell is a dependent cell on the active cell.

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

In the example below, C2 has the formula =B2*C4. Therefore, C2 is a dependent cell on the cells B2 and C4

tracing dependents

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

To trace the dependents of the cell B2,

  1. Click in the cell B2.

  2. Click the Formulas tab.

  3. Click Trace Dependents in the Formula Auditing group.

trace dependents in formula auditing

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

An arrow appears from B2 to C2, showing C2 is dependent on B2.

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

To trace the dependents of the cell C4 −

  1. Click in the cell C4.

  2. Click the Formula tab > Trace Dependents in the Formula Auditing group.

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

Another arrow appears from C4 to C2, showing C2 is dependent on C4 also.

trace dependents of cell

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

Click Remove Arrows in the Formula Auditing group. All the arrows in the worksheet will disappear.

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

Note − For tracing dependents of a cell, the cell should be referenced by a formula in another cell. Otherwise, you will get an error message.

  1. Click in the cell B6 is not referenced by any formula or click in any empty cell.

  2. Click Trace Dependents in the Formula Auditing group. You will get a message.

click trace dependents

Working with Formulae

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

You have understood the concept of Precedents and Dependents. Now, consider a worksheet with several formulae.

working with formulae

  1. Click in a cell under Pass Category in Exam Results table.

  2. Click Trace Precedents. The cell to its left (Marks) and the range E4:F8 will be mapped as the precedents.

  3. Repeat for all the cells under Pass Category in Exam Results table.

exam results table

  1. Click in a cell under Pass Category in Student Grades table.

  2. Click Trace Dependents. All the cells under Pass Category in Exam Results table will be mapped as the dependents.

student grades table

Showing Formulas

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

The worksheet below contains the summary of sales by the salespersons in the regions East, North, South, and West.

showing formulas

  1. Click the FORMULAS tab on the Ribbon.

  2. Click Show Formulas in the Formula Auditing group. The Formulas in the worksheet will appear, so that you will know which cells contain formulas and what the formulas are.

show formula

  1. Click in a cell under TotalSales.

  2. Click Trace Precedents. A worksheet icon appears at the end of the arrow. The worksheet icon indicates that the precedents are in a different worksheet.

click trace precedents

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

Double-click on the arrow. A Go TO dialog box appears, showing the precedents.

go to dialog box

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

As you observe, there are four precedents, on four different worksheets.

  1. Click a reference of one of the precedents.

  2. The reference appears in the Reference box.

  3. Click OK. The worksheet containing that precedent appears.

Evaluating a Formula

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

To find how a complex formula in a cell works step by step, you can use Evaluate Formula command.

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

Consider the formula NPV (Middle Year) in the cell C14. The formula is

=SQRT (1 + C2)*C10

=SQRT (1 + C2)*C10

  1. Click in the cell C14.

  2. Click the FORMULAS tab on the Ribbon.

  3. Click Evaluate Formula in the Formula Auditing group. The Evaluate Formula dialog box appears.

evaluating formula

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

In the Evaluate Formula dialog box, the formula is displayed in the box under Evaluation. By clicking the Evaluate button several times, the formula gets evaluated step-wise. The expression with an underline will always be executed next.

evaluate formula

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

Here, C2 is underlined in the formula. So, it is evaluated in the next step. Click Evaluate.

click evaluate button

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

Cell C2 has value 0.2. Hence, C2 will be evaluated as 0.2. 1+0.2 is underlined showing it as the next step. Click Evaluate.

click evaluate

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

1+0.2 will be evaluated as 1.2. SQRT(1.2) is underlined showing it as next step. Click Evaluate.

evaluate

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

SQRT(1.2) will be evaluated as 1.09544511501033. C10 is underlined showing it as next step. Click Evaluate.

evaluate sqrt

C10 将评估为 4976.8518518515。

C10 will be evaluated as 4976.8518518515.

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

1.09544511501033*4976.8518518515 is underlined showing it as next step. Click Evaluate.

restart button

1.09544511501033*4976.8518518515 将评估为 5,451.87。

1.09544511501033*4976.8518518515 will be evaluated as 5,451.87.

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

There are no more expressions to evaluate and this is the answer. The Evaluate button will be changed to Restart button, indicating completion of evaluation.

Error Checking

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

It is a good practice to do an error check once your worksheet and/or workbook is ready with calculations.

考虑以下简单计算。

Consider the following simple calculations.

error checking

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

The calculation in the cell has resulted in the error #DIV/0!.

  1. Click in the cell C5.

  2. Click the FORMULAS tab on the Ribbon.

  3. Click the arrow next to Error Checking in the Formula Auditing group. In the drop-down list, you will find that Circular References is deactivated, indicating that your worksheet has no circular references.

  4. Select Trace Error from the drop-down list.

select trace error

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

The cells needed to compute the active cell are indicated by blue arrows.

activate cell

  1. Click Remove Arrows.

  2. Click the arrow next to Error Checking.

  3. Select Error Checking from the drop-down list.

select error checking

将显示 Error Checking 对话框。

The Error Checking dialog box appears.

error checking dialog box

观察以下内容 −

Observe the following −

  1. If you click Help on this error, Excel help on the error will be displayed.

  2. If you click Show Calculation Steps, Evaluate Formula dialog box appears.

  3. If you click Ignore Error, the Error Checking dialog box closes and if you click Error Checking command again, it ignores this error.

  4. If you click Edit in Formula Bar, you will be taken to the formula in the formula bar, so that you can edit the formula in the cell.