相信用vlookup做过反向查询的都见过if({1,0},X1,X2)这个公式，那么怎么理解这个公式呢，我在excel中尝试了很多组合，总结出了一些规律。
先说结论吧：
if({1,0},X1,X2)，作用就是构建一个两列数组，在excel所有用到区间的函数，比如sum函数，用到比如A1:A10这种写法的地方都可以用这个公式。
if函数中的{1,0}这个数组，实际是让if进行一次次的循环，遇到分号或者整条计算一遍之后再进行一次新的计算，循环次数为后面两个数组中行数最大的那个的行数。每一次循环，后面的两个数组的数字也会跟着循环，比如第一次循环，对应后面两个数组的第一个数字，第二次循环对应第二个数字。而一旦{}中出现了分号，如果后面没有新的判断条件，会将后续的数组值全部设为#n/a
首先，if函数大家都知道是什么意思，if（逻辑判断，逻辑为真输出结果，逻辑为假输出结果），上面这个数组公式也符合这个规律。
{1,0}这个数组，1表示真，0表示假，if（{1,0}，X1，X2）的意思就是把数组里面的1和0分别拿来运算，因为1代表真，真的时候返回X1数组，所以1这个数字，用X1数组代替，1运算完了，再用0来运算一次，0这个数字表示假，if函数运算为假的时候，用X2数组代替，这两个都运算完了之后就组成了一个两列的新数组。
为证明以上理解方式没问题，我稍微变形了一下上面的公式：if（{1,0,1,0,1,0},X1,X2）,这个公式表示用X1和X2交替生成一个6列的数组，实际结果是正确的：
变形的公式
其他思考
一、三列反转
但还有别的无法理解的地方，比如上面是将两列反转，那我要把三列反转，是不是可以用if嵌套，但我尝试了很多种思路都没成功，比如：
if嵌套
首先是用if嵌套，嵌套里面的if显然是没有问题的，能正常运算：
F9查看运算结果
但到外层if运算的时候，就忽略掉了后面这个数组中前一列的数值，只取了最后的数值，为了验证是不是只取最后一列数值，我做了个新的实验：
直接选取区间
因为内层嵌套的if返回的就是一个数组，所以我直接选取了一个三列的区间做数组，最后的运算结果还是只取了第二列的数值。
把选取的区间增加到4列、5列，最后选取的值依旧是2列，这个尚不清楚为什么。而且选取了3/4/5列，最后的计算结果和2列稍有不同，当后面这个数组是2列的时候，最终结果只是省略了第一列，比如：
后一个参数为2列
上面这个公式，返回结果就是一个2列的数组（下图），第三列是#n/a的原因是拉取数组的时候把这一列带上了，这一列没有值，就显示了#n/a
上图的计算结果
而当选取的区域变成3列及以上时，比如5列：
嵌套数组为5列
上面公式转换后
最终的计算结果也是5列，只是第二列之后的值都是#n/a
后面三列数据都是#n/a
最后进行一种尝试，两个输出都用区间：
两个区间都用2列
当两个区间都用两列时，最后的输出结果取了前一个区间的第一列和后一个区间的第二列：
结果为区间1的第一列和区间2的第二列
当选区区域为3列时：
选取区间为3列
最后的输出结果使用了第1个区间的第一列和第2个区间的第二、三列，只是第三列是#n/a：
选取了区间1的第一列和区间2的第二、三列，第三列为#n/a
以上所有的尝试，总结出了一个规律：因为if判断只支持2种，是和否，所以固定了最终生成的数组只能是2列
二、纵向组合
在excel的数组里，逗号,表示横向显示，分号;表示折行，比如{1,2,3；4,5,6}表示一个2行3列的数组，超过这个区域的单元格被选上了，显示就是#n/a
2行3列数组
有了这个基础知识，再带入到if({1,0},X1,X2)公式中，因为{1,0}中间是逗号，最终结果为2列数组，那将{1,0}换成{1;0}，按数组的特性，是不是直接将后面两个数组合并呢，结果并不是：
将{1,0}换为{1;0}
结果为1列数组，只是数组的第一个值是数组1的第一个值，第二个值是数组2的第二个值，后面都是#n/a：
1列数组，后面均为#n/a
这时候，自然产生了一个新的想法：{1;0;1}：
{1;0;1}
结果如我所料，最终数组的第三个值用了数组1的第三个值，继续用0和1尝试了一下，都是正确的。
这时候，我产生了一个猜想：
if函数中的{1,0}这个数组，实际是让if进行一次次的循环，遇到分号或者整条计算一遍之后再进行一次新的计算，循环次数为后面两个数组中行数最大的那个的行数。每一次循环，后面的两个数组的数字也会跟着循环，比如第一次循环，对应后面两个数组的第一个数字，第二次循环对应第二个数字。而一旦{}中出现了分号，如果后面没有新的判断条件，会将后续的数组值全部设为#n/a（至于为啥是行数最大的，在本位最后进行尝试，这地方不打断）
所以，对if({1,0},X1,X2)来说，表示把后面两个数组都循环一次，最终数组的第一行，是X1的第一个数字和X2的第一个数字，第二行是X1的第二个数字和X2的第二个数字.
如果上面 这个猜想正确，那{1,0;1,0}就是只选取后面两个数组的前两行数据，后面的数据全部是#n/a：
{1,0;1,0}
结果正确
看到{1,0;1,0}，大家就明白了，{1,0}是最初的公式，多了个1,0之后反而返回结果更少了，所以我的猜想里面说如果{}中有分号，没有判断条件的后续循环都会设为#n/a。
最后，来试一试为啥循环的时候是以行数最大的数组进行的：
数组1为5行，数组2为3行
上面这个公式，数组1为5行，数组2为3行，实际计算结果为：
最终结果为5行
虽然最终结果为5行，但没有数据的部分全部用#n/a替代，这和选中区域稍有不同，假设后一个区域选中的也是5行，虽然后面两个位置没有数据，但是最终会用0代替：
后一个选中4行
后一个数组多选了一列，被选到的这个位置没有值，会用0代替，没有勾选的就是#n/a
最终结果为0和#n/a的区别
以上就是我对if({1,0},X1,X2)的整个尝试过程，还有些没弄懂的地方，以后遇到再尝试吧。
网友评论