如何在EXCEL中使用IFERROR处理公式错误值

来源:澳洲CPA 时间:2022-01-21 作者:澳洲CPA编辑:无忧草
打印 RSS |
导读:Excel公式错误如何解决?你可以使用这个函数!

EXCEL中的IFERROR函数是一个通用的错误处理函数,可在Excel中处理不同类型的错误,非常有用!

如果创建公式后需要处理错误,只需在该公式前面插入IFERROR函数。具体怎么做呢?

您可以扫描二维码收听播客,在线学习也可以看下方的文字版内容:

√ 让我们来看看下面这个简单公式。

=A1/B1

√ 如果单元格B1为零,则上述公式会返回#DIV/0!错误值。

IFERROR函数可用于校验该公式,处理B1为零时的错误。

=IFERROR(A1/B1,0)

IFERROR函数有两个参数。第一个参数是要进行的计算,第二个参数是计算结果为错误值时返回的值。如果计算结果没有返回错误值,则显示计算结果。如果返回错误值,则显示第二个参数。

IFERROR可以处理Excel中的几乎所有公式错误。如果A1出现#VALUE错误值,上述IFERROR函数依然显示0。

在某种程度上,IFERROR函数会屏蔽或隐藏错误值,因为它以相同的方式处理所有错误。

温馨提示:

在早期版本的Excel中,IFERROR可以处理所有错误。在订阅版Excel中,IFERROR无法处理与动态数组有关的#SPILL错误。

将IFERROR函数插入现有公式可能非常耗时且需要重复操作,建议用宏命令来解决这一问题。

需要注意的是,宏命令会清除撤销列表中的项目,这意味着操作无法撤销,而且无法撤销运行宏命令之前的所有操作。

因此,在使用新的宏命令之前,务必要保存文件,这样即便发生错误,直接关闭文件,不保存即可,或者也可在文件副本上测试宏命令。

添加IFERROR宏命令

图1显示了在现有公式中添加IFERROR函数的宏命令。

该宏命令适用于当前显示错误值的公式。本文的配套视频将具体说明该宏命令的安装和使用方法。图1中绿色文字为解释性文本,也称为“注释”,用于解释说明代码,但不执行任何操作。

*下文中的项目编号是指图1左侧的蓝白色编号。

 

 

图1

01

定义两个变量。变量c用于引用选定区域内的每个单元格,strFormula变量用于提取现有公式的文本。

02

此宏命令包含一条通用的错误处理命令,可启动错误处理。

如果遇到错误,代码会跳到下方的ErrorHandler标签(第11项)。如果工作表受保护且单元格被锁定,宏命令就会产生错误。

03

这段代码会关闭屏幕更新并停止计算。这些设置可加快宏在较大文件中或处理较大区域时的运行速度。我们会在第9项中重新开启这些设置。

04

TypeName函数可用于识别运行宏命令前选择的单元格区域。如果用户选择了一张图表,后续代码将产生错误。我们可以在此时停止宏命令并显示第8项的警告信息。

05

“For Each c in Selection”命令功能强大,可要求Excel在用户选定区域的每个单元格内依次查找。

这会赋予该宏命令极大的灵活性,因为用户可以选择不同大小的区域来添加IFERROR函数。

06

“If statement”可检查单元格中是否含有公式。所有Excel公式均以“=”开头。

Left函数可查看公式的第一个字符,以确认是否为“=”。如果单元格并非以“=”开头,则不会对该单元格执行操作,后面的值、日期和文本均会忽略。

07

strFormula变量会提取“=”之后的公式文本。

Right函数可从右侧提取文本。

Len函数是“length”(长度)的缩写,可计算公式中的字符数。

通过将Len函数的返回值减去1,就可以从右侧提取“=”之后的所有公式文本。

接下来,在前面输入“=IFERROR (”,后面是原本的公式,最后输入“,0)”,这样就创建好IFERROR公式。“&”符号用于将文本合并在一起。

08

该命令会弹出对话框,提醒用户需要在运行宏命令之前选择一个区域。用户须点击“OK”,否则该对话框会一直在屏幕上显示。

请注意,第一行末尾的下划线符号可用于将一行代码拆分成两行。

09

HandleExit:是标签,冒号后面是其文本,可使用GoTo或Resume命令将代码跳到该位置。

这是处理关闭宏的部分。我们先将Excel恢复到标准设置,开启屏幕更新和计算。

10

c变量被清除,宏命令停止。

11

HandleError:也是标签。这部分的操作仅在遇到错误时执行。显示弹出对话框,用户须点击“OK”。接着会跳到HandleExit标签,关闭宏命令。

使用宏命令

这个宏命令十分灵活,可用于任何文件。

保存这类通用宏命令的最佳位置是个人宏工作簿(Personal Macro Workbook),其名称为PERSONAL.xlsb,保存在系统文件夹XLSTART中,每次打开Excel时都会自动打开。在录制宏时,保存位置可选个人宏工作簿。

INTHBLACK官网还提供含有这个宏命令的文件,您可点击这里下载。

点击这里,可查阅文章的英文原文。

点击这里,阅读微信原文。

沪公网安备 31011802001002号