Excel VBA 编程基础 -- 错误处理(六)

前面我们用四篇文章讨论了 VBA 中的错误处理，今天来讨论 Excel 中的错误。

我们在获取外部数据、使用公式计算时，总是免不了出现错误。这些错误是 Excel 内部数据获取引擎或者计算引擎引发的错误，不是 VBA 的错误。

Excel 的错误总结如下表：

图1 Excel 中的错误

其中，前两个并不是错误，而是提示性信息。

####：说明单元格宽度不够，需要增加单元格的宽度，以显示其内容
#GETTING_DATA：说明 Excel 内部正在获取外部数据，数据获取结束后，该信息即消失

#CALC!

这个错误主要发生在与数组有关的计算上，譬如公式返回空数组、公式中的发生了数组嵌套、数组运算中产生了循环依赖等。

例. 公式返回空数组

图2 FILTER 返回空数组

公式 =FILTER(B2:B5, B2:B5 <= 0) 检测 B2:B5 区域有没有小于等于 0 的值，因为 B2:B5 区域的值都大于 0，因此 FILTER 公式返回空数组，导致 #CALC! 错误。

FILTER 函数的一般格式为：

FILTER(array, include, [if_empty])

其中，

array：需要筛选的单元格区域或数组
include：含有 TRUE/FALSE 值得数组，FILTER 在 array 中筛选对应include 为 TRUE 的元素
[if_empty]：可选，如果指定，则表示若 array 筛选结果为空时的返回值

所以，我们修改上面的 FILTER 公式，以避免 #CALC! 错误。见下图：

图3 修正后的 FILTER 公式

在修正后的公式中，若 FILTER 返回空数组，我们就让 FILTER 返回“检测正常”。

#DIV/0!

被 0 除错误，经常发生在 0 作除数的场合。如下图：

例. 被 0 除错误

图4 DIV/0 错误

这个例子中，张三的销售数量为 0，在计算成交所需时间时，就发生 DIV/0 错误。

可以用 IFERROR 来检测并避免错误，如下图：

图5 IFERROR 检测并避免错误

IFERROR 函数的一般格式如下：

IFERROR(formula, value_if_error)

其中，

formula：要完成计算的公式
value_if_error：公式计算出错时的返回值

如果 formula 公式没有错误，则 IFERROR 函数返回公式的计算结果，如果发生错误，则返回 value_if_error 参数给出的值。在我们的例子中，我们让 IFERROR 在计算出错时返回 N/A，表示值不可用。

#N/A

这个错误主要表示值找不到，从而不可用。主要发生在使用 VLOOKUP、SEARCH 等函数来查找某个/些值但没找到。

例. 使用 VLOOKUP 查找

图6 #N/A 错误

我们在 A2:C5 区域中，查找姓名史蒂夫，因为没有这个名字，所以 VLOOKUP 函数返回 #N/A 错误，表明“查无此人”！

VLOOKUP 函数的一般格式为：

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

其中，

lookup_value：要查找的值
table_array：VLOOKUP 函数所要搜索的区域
col_index_num：返回值所在的列
[range_lookup]：Boolean 类型的值，指示 VLOOKUP 函数查找时的匹配方式：

TRUE：模糊匹配
FALSE：精确匹配

注意：VLOOKUP 函数中，要查找的值和要返回的值有可能不一致，但在我们的例子中是一致的，但如果写：

=VLOOKUP("李四", A2:C5, 2, FALSE)

则表示查找姓名“李四”，但返回李四的工作时长。

#NAME?

这是 Excel 中的名字错误，经常发生在函数名拼写错误、字符串忘记引号但又不是区域名或函数名等情况。

例. 函数名错误

图7 #NAME? 错误

在这个例子中，我们错把 VLOOKUP 函数名拼写成了 VLLLKUP，Excel 找不到名为 VLLLKUP 的函数，因此报告 #NAME? 错误。

#NULL!

Excel 中的 #NULL! 错误类似于数学上的空集（empty set）概念，往往发生在 Excel 以为你要求两个区域的交集，但这两个区域并不相交，从而导致区域为空。

例. 拼写错误导致的 #NULL!

图8 拼写错误导致 #NULL!

本来我们要累计 B2:B5，但公式却写成了 =SUM(B2 B5)，中间的冒号错拼成了空格，而空格表示 Excel 要求两个区域 B2 和 B5 的交集，但因这两个区域不相交，因此导致空集错误 #NULL!。

#NUM!

这个错误表示给出的值太大或太小，Excel 无法表示。

例. 值太大导致 #NUM! 错误

图9 值太大导致 #NUM!

在这个例子中，我们用 SUM(B2:B5) 的结果乘 100 的 200 次方，因为 Excel 所能表示的最大值是 10 的 308 次方，100^200 ≫ 10^308，因此导致 #NUM! 错误。

#REF！

这个错误表示所引用的对象不存在，该引用变成了无效引用。

例. VLOOKUP 发生 #REF! 错误

图10 VLOOKUP 发生 #REF! 错误

在这个例子中，公式 =VLOOKUP("李四",A2:C5,4,FALSE) 表示如果找到"李四"，则返回区域 A2:C5 中李四所在行第 4 列的内容，但第 4 列超出了 A2:C5 区域的范围，因此引发 #REF! 无效引用错误。

#VALUE!

这个表示发生了数据类型错误，譬如 Excel 期望数值，结果是字符串。

例. 数据类型导致的 #VALUE! 错误

图11 数据类型导致的 #VALUE! 错误

在这个例子中，公式 =B2*B3*B4*B5 要求 B2:B5 单元格的值都是数值类型，但 B5 单元格为字符串"无值"，且字符串“无值”无法转换为数值类型，因此导致 #VALUE! 错误。

#SPILL!

这是与动态数组有关的错误。当 Excel 的公式返回一个数组要填充到工作表中的一个区域，但此时这个区域中有的单元格含有值，就会导致这种 #SPILL! 错误。

例. 填充区域不为空导致 #SPILL! 错误

图12 填充区域不为空导致 #SPILL! 错误

在这个例子中，单元格 F2 的公式 =B2:B5*3 将返回一个数组，单元格 F2 无法接纳一个数组的结果，因此该数组将溢出至其他单元格，而这个溢出区域将是 F2:F5，但 F3 单元格有值 x，因此无法溢出，导致 #SPILL!（#溢出!）错误。

#CONNECT!

这个错误发生在 Excel 试图连接外部数据源获取数据时。外部数据源有可能是外部文件、远程服务、外部数据提供者等，如果文件访问错误或访问外部服务时发生网络错误，都会导致 #CONNECT! 错误。

#BLOCKED!

如果工作表中含有 Excel 4.0 宏，而 Excel 默认禁止 Excel 4.0 宏，则就会显示 #BLOCKED! 错误。

微软已经禁止链接到外部工作簿，如果工作表中含有到外部工作簿的链接，也会显示 #BLOCKED! 错误。

发生 #BLOCKED! 错误，说明你的工作表含有被禁止的 Excel 4.0 宏，或到外部资源的链接。

#FIELD!

这涉及到对外部结构化数据的引用，如果所引用的字段（Field）不存在，或没有值，或数据类型不正确，都会导致 #FIELD! 错误。

#UNKNOWN!

这通常发生在由于 Excel 的版本不同而无法识别某种数据类型、不兼容等的情况。因为当前版本 Excel 无法所造成的错误，从而显示 #UNKNOWN！。譬如，使用了 Excel 中新的智能数据类型如地理、股票、Python in Excel 等，但你的 Excel 版本不支持这些数据类型，就会导致这种错误。