Excel数据分析 - 公式审计

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

  • 哪些单元格在计算活动单元格中的公式时有所贡献.

  • 哪些公式指的是活动单元格.

这些结果以图形方式显示为箭头线,使可视化简单.您可以使用单个命令显示活动工作表中的所有公式.如果您的公式引用不同工作簿中的单元格,也请打开该工作簿. Excel无法转到未打开的工作簿中的单元格.

设置显示选项

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

  • 单击 FILE>选项.

  • 在"Excel选项"对话框中,单击"高级".

  • 在工作簿的显示选项中减去;

    • 选择工作簿.

    • 检查在For objects下,show,All是否被选中.

  • 对您正在审核的所有工作簿重复此步骤.

设置显示选项

跟踪先例

先例单元格是由活动单元格中的公式引用的那些单元格.

在以下示例中,活动单元格为C2.在C2中,你有公式 = B2* C4 .

B2和C4是C2的先前单元格.

Tracing Precedents

要跟踪单元格C2的先例,

  • 单击单元格C2.

  • 单击"公式"选项卡.

  • 单击"公式"中的"跟踪先例"审核组.

Trace Precedents

将显示两个箭头,一个从B2到C2,另一个从C4到C2,跟踪先例.

显示两个箭头

请注意,对于跟踪单元格的先例,单元格应该具有带有效引用的公式.否则,您将收到错误消息.

  • 单击不包含公式的单元格或单击空单元格.

  • 单击"公式审核"组中的"跟踪先例".

您将收到一条消息.

获取消息

删除箭头

单击删除箭头在公式审计小组中.

删除箭头

所有箭头工作表将消失.

跟踪从属人员

从属单元格包含引用其他单元格的公式.这意味着,如果活动单元格对另一个单元格中的公式有贡献,则另一个单元格是活动单元格上的依赖单元格.

在下面的示例中,C2具有公式 = B2* C4 的.因此,C2是单元格B2和C4上的依赖单元格

Tracing Dependents

要跟踪单元格B2的依赖项,

  • 单击单元格B2.

  • 单击"公式"选项卡.

  • 单击"公式审核"组中的"跟踪依赖项".

公式审计中的跟踪依赖

从B2到C2出现一个箭头,显示C2依赖于B2.

跟踪单元格C4的依赖项;

  • 单击单元格C4.

  • 单击"公式"选项卡>公式审计组中的跟踪依赖项.

从C4到C2出现另一个箭头,表示C2也依赖于C4.

跟踪细胞依赖

单击"公式审核"组中的删除箭头 .工作表中的所有箭头都将消失.

注意 : 对于跟踪细胞的依赖性,细胞应该由另一个细胞中的公式引用.否则,您将收到一条错误消息.

  • 单击单元格B6未被任何公式引用或单击任何空单元格.

  • 单击"公式审核"组中的"跟踪依赖项".您将收到一条消息.

Click Trace Dependents

使用公式

您已了解先例和家属的概念.现在,考虑一个包含多个公式的工作表.

使用公式

  • 单击"考试结果"表中"通过类别"下的单元格.

  • 单击"跟踪先例".左侧的单元格(标记)和范围E4:F8将被映射为先例.

  • 对"考试结果"表中"通过类别"下的所有单元格重复.

考试成绩表

  • 单击学生成绩表中"通过类别"下的单元格.

  • 单击"跟踪依赖项".检查结果表中通过类别下的所有单元格将被映射为依赖项.

学生成绩表

显示公式

下面的工作表包含东,北地区销售人员的销售总结,南方和西方.

显示公式

  • 单击功能区上的"公式"选项卡.

  • 单击"公式审核"组中的"显示公式".将出现工作表中的公式,以便您知道哪些单元格包含公式以及公式是什么.

显示公式

  • 点击 TotalSales

  • 单击"跟踪先例".工作表图标显示在箭头的末尾.工作表图标表示先例位于不同的工作表中.

Click Trace Precedents

双击箭头.出现转到对话框,显示先例.

转到对话框

如您所见,四个不同的工作表中有四个先例.

  • 点击参考其中一个先例.

  • 参考文献出现在参考框中.

  • 单击"确定".将显示包含该先例的工作表.

评估公式

查找单元格中复杂公式的工作原理一步一步,您可以使用评估公式命令.

考虑单元格C14中的公式NPV(中年).公式是

= SQRT(1 +  C2)* C10

  • 单击单元格C14.

  • 单击功能区上的"公式"选项卡.

  • 单击"公式审核"组中的"评估公式".将出现"评估公式"对话框.

Evaluating Formula

评估公式对话框中,公式显示在评估下方的框中.通过多次单击评估按钮,可逐步评估公式.下面将始终执行带下划线的表达式.

Evaluate Formula

此处,C2在公式中加下划线.因此,它将在下一步中进行评估.点击评估.

点击评估按钮

单元格C2的值为0.2.因此,C2将被评估为0.2. 1 +  0.2 带下划线,显示为下一步.点击评估.

点击评估

1 +  0.2将被评估为1.2. SQRT(1.2)带下划线,显示为下一步.点击评估.

评估

SQRT (1.2)将被评估为1.09544511501033. C10 带下划线,显示为下一步.点击评估.

Evaluate

C10将被评估为4976.8518518515.

1.09544511501033* 4976.8518518515带有下划线,显示为下一步.点击评估.

重启按钮

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

没有更多的表达式需要评估,这就是答案. 评估按钮将更改为重新启动按钮,表示评估已完成.

错误检查

一旦您的工作表和/或工作簿准备好进行计算,最好进行错误检查.

请考虑以下简单计算.

错误检查

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

  • 单击单元格C5.

  • 单击功能区上的"公式"选项卡.

  • 单击"公式审核"组中"错误检查"旁边的箭头.在下拉列表中,您会发现循环引用已取消激活,表示您的工作表没有循环引用.

  • 从下拉列表中选择跟踪错误.

选择跟踪错误

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

激活单元格

  • 单击删除箭头.

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

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

选择错误检查

出现错误检查对话框.

错误检查对话框g框

观察以下 :

  • 如果单击帮助关于此错误,将显示有关错误的Excel帮助.

  • 如果单击显示计算步骤,请评估出现公式对话框.

  • 如果单击忽略错误,将关闭错误检查对话框,如果单击错误再次检查命令,它会忽略此错误.

  • 如果单击在公式栏中编辑,您将被带走到公式栏中的公式,以便您可以在单元格中编辑公式.