处理空白单元格

在较旧版本的软件中,空白单元格在某些上下文中强制设为数字 0,在另一些地方强制设为空字符串,但在直接比较中有例外情况,如果 A1 为空,那么 =A1=0 和 =A1="" 的结果都是 TRUE。空值一直延续到对该值的调用才发生改变,所以当 lookup 的结果是返回一个空单元格时就有 =VLOOKUP(...)=0 和 =VLOOKUP(...)="" 的值是 TRUE。

对一个空白单元格的简单引用仍显示为数字 0,但不再必须为数字类型。因此,与引用单元格的比较如所期望的那样。

对于以下示例,A1 包含一个数字,B1 为空,C1 包含对 B1 的引用:

Case

Formula

Results and comments

A1: 1
B1: <Empty>

C1: =B1

Displays 0

=B1=0

TRUE

=B1=""

TRUE

=C1=0

TRUE

=C1=""

TRUE (previously was FALSE)

=ISNUMBER(B1)

FALSE

=ISNUMBER(C1)

FALSE (previously was TRUE)

=ISNUMBER(VLOOKUP(1;A1:C1;2))

FALSE (B1)

=ISNUMBER(VLOOKUP(1;A1:C1;3))

FALSE (C1, previously was TRUE)

=ISTEXT(B1)

FALSE

=ISTEXT(C1)

TRUE

=ISTEXT(VLOOKUP(1;A1:C1;2))

FALSE (B1, previously was TRUE)

=ISTEXT(VLOOKUP(1;A1:C1;3))

FALSE (C1)

=ISBLANK(B1)

TRUE

=ISBLANK(C1)

TRUE

=ISBLANK(VLOOKUP(1;A1:C1;2))

TRUE (B1, previously was FALSE)

=ISBLANK(VLOOKUP(1;A1:C1;3))

FALSE (C1)


批注图标

请注意,在 Microsoft Excel 中的处理方式有所不同,它总是返回一个数字作为对一个空白单元格或者返回值是空白单元格的公式的引用结果。例如:


Case

Formula

Results and comments

A1: <Empty>

B1: =A1

Displays 0, but is just a reference to an empty cell.

=ISNUMBER(A1)

FALSE

=ISTEXT(A1)

FALSE

=A1=0

TRUE

=A1=""

TRUE

=ISNUMBER(B1)

FALSE (Microsoft Excel: TRUE)

=ISTEXT(B1)

FALSE

=B1=0

TRUE

=B1=""

TRUE (Microsoft Excel: FALSE)

C1: =VLOOKUP(...) with empty cell result

displays empty (Microsoft Excel: displays 0)

=ISNUMBER(VLOOKUP(...))

FALSE

=ISTEXT(VLOOKUP(...))

FALSE

=ISNUMBER(C1)

FALSE (Microsoft Excel: TRUE)

=ISTEXT(C1)

FALSE