数组索引必须为正整数或逻辑值_Excel逻辑运算和数组公式

前言

在我之前的如何<<深入学习Excel>>一文中已经讲到了学习公式的重点, 由于引用和函数嵌套并不复杂(注意简单并不代表不重要), 主要是难在逻辑思维上, 所以这里不再赘述, 感兴趣的可以去看一下.

当你深入学习公式时, 你会发现很多奇葩的公式, 比如

=VLOOKUP(A1,IF({1,0}, C1:C11,B1:B11),2,0)
=SUM((G1:I1<2)*G3:I4)

WTF???????这都是什么鬼?

下面让我们逐一去理解逻辑运算和数组公式

1. 逻辑运算与算术运算

1.1逻辑值与数字的关系

不只是Excel中, 在很多的编程语言中, 我们都可以认为

FALSE 等价于 0, TRUE 等价于 1
0 等价于 FALSE, 非0 等价于 TRUE

但是万事总有例外, 在VBA中只有位运算, 又因为-1的补码是1111 1111 ..., 所以True在数字上就等价于-1了~

1.2逻辑运算与数学运算

逻辑运算常用的也就只有AND和OR, 我们重温一下他们的规则

AND: 有假则为假
OR: 有真则为真

对于AND, 既然有假则为假, 那么FALSE等价于0, 所以就是有0则为0, 好熟悉?????? 0乘以任何数都为0, AND等价于乘法

对于OR, 有真则为真, 由于TRUE等价于1, 所以就是大于0就可以, 又因为任何非0的数字都是真, 所以, OR等价于加法, 那有没有可能加法引发数字溢出, 变成0, 这里并不会, 因为Excel一旦浮点数溢出, 就会产生#NUM!错误

AND(A, B, C) = A * B * C OR(A, B, C) = A + B + C

2.数组公式

一般情况下我们可以认为数组等价于单元格区域. 那么什么是数组?

数组公式需要Ctrl+Shift+Enter完成, 最外面的大括号不需要输入

数组就是用大括号包围的一段数据, 用逗号分隔列, 分号分隔行

其实讲到这里数组已经讲完了~~~

3.案例

3.1单价求和

总计==SUM(B2:D2*B5:D7)
单价<3==SUM((B2:D2<3)*B2:D2*B5:D7)

对于B2:D2*B5:D7的结果其实是这样的~

那么(B2:D2<3)*B2:D2*B5:D7就是多了一个{1, 1, 0}

3.2VLOOKU查询

我们都知道VLOOKUP要求查找列必须在第一列, 如果不在怎么办?

=VLOOKUP(B2,IF({1,0}, D:D,C:C),2,0)

其中最难以理解的就是IF({1,0}, D:D,C:C), 那我们简化一下, 看看它到底返回什么

至此你应该能够明白, {1, 0}决定了先D2:D12然后拼接上C2:C12的一个整体结果.