Excel 中,函数与公式无疑是最具有魅力的功能之一。使用函数与公式,能帮助用户完成多种要求的数据运算、汇总、提取等工作。函数与公式同数据验证功能相结合,能限制数据的输入内容或类型,还可以制作动态更新的下拉菜单。函数与公式同条件格式功能相结合,能根据单元格中的内容,显示出用户自定义的格式。在高级图表、透视表等应用中,也少不了函数与公式的身影。

虽然学习函数与公式没有捷径,但也是讲究方法的。小编总结了无数 Excel 高手的学习心得,以便教给大家正确的学习方法和思路。今天我们来了解 Excel 使用公式中的常见问题及处理方法。

检查公式中的错误

使用公式进行计算时,可能会因为某种原因而无法得到正确结果,并在单元格中返回错误值信息。当公式的结果返回错误值时,应及时查找错误原因并修改公式以解决问题。

Excel 提供了后台错误检查的功能。在【Excel 选项】对话框【公式】选项卡的【错误检查】区域中,选中【允许后台错误检查】复选框,并在【错误检查规则】区域根据实际需求来选中各个规则前的复选框,最后单击【确定】按钮,如图 1-1 所示。

excel公式以及技巧(Excel中使用公式的常见问题)(1)

图 1-1 设置错误检查规则

当单元格中的公式或值与上述情况相符时,单元格左上角将显示一个三角形智能标记。选定包含该智能标记的单元格,单元格左侧将出现感叹号形状的【错误提示器】下拉按钮,扩展菜单中包括公式错误的类型、关于此错误的帮助、显示计算步骤等信息,如图 1-2 所示。

excel公式以及技巧(Excel中使用公式的常见问题)(2)

图 1-2 错误提示器

示例1-1 使用错误检查工具

如图 1-3 所示,在 C10 单元格使用 AVERAGE 函数计算 C2:C9 单元格的平均值,但结果显示为错误值“#DIV/0!”。

excel公式以及技巧(Excel中使用公式的常见问题)(3)

图 1-3 公式返回错误值

可以使用以下三种方法进行排查处理。

第一种方法是在【公式】选项卡下单击【错误检查】按钮,弹出【错误检查】对话框。提示单元格 C2 中出错误,错误原因是“以文本形式存储的数字”,并提供了关于【此错误的帮助】【显示计算步骤】【忽略错误】【在编辑栏中编辑】等选项,方便用户选择所需执行的操作。

单击【转换为数字】按钮,可依次将各单元格中的文本型数字转换为数值,也可以通过单击“上一个”或“下一个”按钮查看其他单元格中的公式错误情况,如图 1-4 所示。

excel公式以及技巧(Excel中使用公式的常见问题)(4)

图 1-4 执行错误检查

第二种方法是选定公式所在单元格,在【公式】选项卡中依次单击【错误检查误】命令,将在 C2 单元格中出现蓝色的追踪箭头,表示错误可能来源于 C2 单元格,由此可以判断 C2 单元格格式可能存在错误,如图 1-5 所示。

excel公式以及技巧(Excel中使用公式的常见问题)(5)

图 1-5 追踪错误来源

如不再需要显示追踪箭头,可依次单击【公式】→【删除箭头】命令来取消显示。

第三种方法是选中 C2:C9 单元格区域,单击选中区域左上角的【错误指示器】下拉按钮,在扩展菜单中单击【转换为数字】,则 C10 单元格可正确计算出平均值, 如图 1-6 所示。

excel公式以及技巧(Excel中使用公式的常见问题)(6)

图1-6 使用错误提示器转换文本型数字

处理意外循环引用

当公式计算返回的结果需要依赖公式自身所在的单元格的值时,无论是直接还是间接引用,都称为循环引用。如 A1 单元格输入公式:=A1 1,或是 B1 单元格输入公式“=A1”,而 A1 单元格公式为“=B1”,都会产生循环引用。

当在单元格中输入包含循环引用的公式时,Excel 将弹出循环引用警告对话框,如图 1-7 所示。

excel公式以及技巧(Excel中使用公式的常见问题)(7)

图 1-7 循环引用警告

默认情况下,Excel 禁止使用循环引用,因为公式中引用自身的值进行计算,将永无休止地计算而得不到答案。

如果公式计算过程中与自身单元格的值无关,仅与自身单元格的行号、列标或文件路径等属性相关,则不会产生循环引用。例如,在 A1 单元格输入以下 3 个公式,分别用于提取 A1 单元格的行号、列号及文件名称,均不会产生循环引用。

=ROW(A1)

=COLUMN(A1)

=CELL("fifilename",A1)

示例1-2 查找包含循环引用的单元格

图 1-8 是某公司外贸交易的部分记录,E14 单元格使用以下公式计算总金额。

=SUM(E:E)

由于公式中引用了 E14 自身的值,公式无法得出正确的计算结果,结果显示为 0,并且在状态栏的左下角出现文字提示“循环引用:E14”。

在【公式】选项卡中依次单击【错误检查】→【循环引用】命令,将显示包含循环引用的单元格地址。单击该地址,将跳转到对应单元格。如果工作表中包含多个循环引用,此处仅显示一个循环引用的单元格地址。

excel公式以及技巧(Excel中使用公式的常见问题)(8)

图1-8 快速定位循环引用

解决方法是将 E14 单元格公式的引用区域修改为实际的E2:E13 数据区域,公式即可正常计算。

了解公式使用中的常见问题,有助于对公式的各类故障进行判断和处置,之后在上手过程中也会更加得心应手。

,