详解excel公式中if{1,0}用法(全十回)

作者:西蜀鄙夫xsbf

(2017年)

目 录

第一回 重新拼合两列数据 ........................................................................................................ 2

第二回 对数据区域的挑选 ........................................................................................................ 4

第三回 数组的长宽与数组的扩展 ............................................................................................ 6

第四回 多个阉割式if({1,0},xxx,)拼合数组................................................................................ 8

第五回 从数组的角度理解if({1,0},xxx,yyy) ............................................................................ 11

第六回 从逻辑的角度理解if({1,0},xxx,yyy) ............................................................................ 14

第七回 if({1,0},xxx,yyy)嵌套 ..................................................................................................... 17

第八回 {1,0}嵌套的宽度问题 .................................................................................................. 20

第九回 if({1,0},xxx,yyy)的一个经典实例 ................................................................................. 22

第十回 choose()函数 ................................................................................................................ 24

Excel公式函数的运用中，时见if({1,0},xxx,yyy)这样的结构出现，究竟是什么意思，如何理解呢？对于许多人来说，不太好理解，本文就此详述一二，力求最通俗最详细，欢迎诸君斧正。

1

第一回 重新拼合两列数据

1. 要想明白if({1,0},xxx,yyy)，先看个小问题，左边的数据区域中，如何把丁、辛两列挑出来到右边这样构成一个数组区域呢？

方法一:选中D3:D7复制粘贴，再操作H3:H7；

方法二：选中D3:D7按CTRL键拖动到右边。这些手动操作方法，非本文讨论的范畴，一律无视。

2. 方法三，在L3格输入=d3，然后下拉，再操作另一部分；方法四：如图输入数组公式，再操作另一部分。这两种方法，其实要用“两个”公式，能否只用一个统一的公式就能达到目的呢？

3. 试一下，图中企图用将两个数组用单纯的“+”或“&”这样来完成，显然不行。

4. 想出这个公式，先乘上数组{1，0}，再考虑相加，恭喜成功！

2

5. 但是，刚才这个公式局限太大，应付纯数字还行，但是数组中有文本，显然就不能完成任务了。由此可以看出，这个问题，决非想象中的简单容易。

6. 要如何解决这个问题呢？不必兜圈子，请出今天的主角if({1,0},xxx,xxx)，用这样的数组公式即完成任务。图中用一个统一公式把辛-丁两列提出来成了右边这样，不管原来数据是否数字还是文本均无问题。

3

第二回 对数据区域的挑选

8. 刚才用到if({1,0},xxx,yyy) 这个结构就是为了重新构造一个数组，原理是怎样的？这里且把这个结构阉割一下，去掉”yyy”，剩下if({1,0},xxx)。观察图中结果，({1,0}按“1”选择数据，其中的“1”可理解成“挑选”，“0”可理解成“不挑选”。

9. 此例，不妨把{1,0}丰富一下，夸张地搞一串。如图这个公式，先看右边结果，就把左边区域的数据“有选择地”挑选出来组成一个新的区域，挑选的方法就是，{0,0,1,0,0,1,1,0,1,0}这一句中“1”对应的丙、已、庚、壬列（图中红字已标识，辅助观察），而“0”就是没有挑选的列，对应右边显示的“false”。

10. 此例， {1,1,1,1}其实选中连续的4列，{0,0,0,0}就是没有选择，显示“false”

11. 插入一个小知识：{1,0, ……}中的‘1’指的是‘非0’，并非一定是自然数的‘1’，下图4

中，把1换成其他非0数，两种方法操作结果一样。

12. {1,0, ……}这个其实对应‘列’的选择，类似的{1;0; ……}对应‘行’的选择，更有甚者，还可以是象{1,0;0,1……}这种‘行列’的组合。如图所示例子，这个挑选，其实相当于对一个矩形数据区域，筛掉某些数据，留下的数据构成一个数组。

13. 上例中{1,0;0,1……}太复杂，可以建立辅助表格，帮助观察。

5

第三回 数组的长宽与数组的扩展

14. {1,0, ……}中的元素排列“个数”（也就这个数组的长与宽）非常重要。比如：{0,1,0,0;1,1,0,1}长为4宽为2；对于单行数组来说，大括号内有几个数，它的宽度就是多少， {1,0}长度为2，{1,1,1,1}长度为4，{0,0,1,0,0,1,1,0,1,0}长度为10。

15. 下图中{0,0,0,0}的长度为4，但设置公式范围时有6列，所以后两列显示“#n/a”出错信息。“false”跟“#n/a”是两个不同概念，前者表示未选择到数据，为“合法的空”，后者表示错误“非法的空”。

16. 出现“#n/a”是由于扩展错误，上图中，要把长度为4的数组扩展为长度为6，不足的2只好以错误值进行扩展。一个重要的特例是，数据如果是单行或单列，会得到正确的扩展。下图中，挑选{1，0，0，1}的长度为4，而被挑选的范围F3:F8列宽为1，要从‘1份’变成‘4份’，电脑并不拒绝操作。

17. 刚才的操作，其实相当于把F3:F8数据复制多个（即数组扩展），然后再进行挑选。在Excel6

中数组对于两边大小不一的运算时，都会扩展以适应，但是超过2维，扩展是以错误值扩展的，所以一般避免这种有错误值情况的发生。

18. 单行或单列数组可以正确扩充，对于1个常数（数字、文本等），相当于特殊的单行或单列，所以同样会扩充，如图所示，。

7

第四回 多个阉割式if({1,0},xxx,)拼合数组

19. 前面讲了用阉割式if({1,0},xxx,)挑选数组的方法，实质是从一个数组中选择某些部分，能否再选择另一个数组选择另一部分，然后‘阴阳互补’式的拼成一个新数组呢？这是可以的。如图这例中， {1,0}和{0,1}刚好相反。

20. 此例同样，两个数组选择区域的{1,0……}刚好相反。

21. 既然可以由两部分组合，当然也可以用三部分甚至更多的部分“&”并成。

8

22. 上图公式较长，但思路相当浅显，举例第一个分句，在m11:o16的范围内用{1，0，0}选出第一列，然后仿此，在其他地方选出几列，最后并集起来。形象比喻一下相当于几个人铺地砖，第1个人跳着铺了部分，第2个人铺前面留下的空档，第3个接下铺前面剩的空档，铺的原则是不允许有‘重叠’。抽象点看，就是{1,0,0}+{0,1,0}+{0,0,1}={1,1,1}，拼砖成功。

23. 不过，上图这个公式是局限，假如拼‘中’这块板，需要是A列，公式中{0,1,0}的‘1’对应A列，但A列已经是最靠前的列，之前就没有了，所以这一步完成不了。

24. 解决的方法，异常简单，就是数组单行单列的扩展，不去考虑实际引用范围，输入单列就行了，如图所示。

25. 再一例拼板，图中是按1-2-3-4-5这样顺序提取数据拼板，其中3和5是同一列数据。公式可以写成这样，因为2-3-4这几列是连续的，所以注意我这里写的是{0，1，1，1，0}，对应区域跟着作相应变化。

9

26. 拼块构成数组时，也别忘了，如果对象全部纯粹是数字，可以直接相乘再相加，如图所示。

27. 本回总结：

a) 这种‘拼板’式的组合数组只是一个基础方法，虽不是最优化的办法，但确是个‘万能’的办法，具体操作中有可能公式写得老长，但仔细分解一下，思路相当地简单明了。

b) 这种操作思路，一定要考虑数组长度问题，根据数组最后有多宽，再回头确定{1,0,……}，空缺的部分用0补足。

c) {1,0,……}这种长串形式，受太极八卦图的启发，体现出一种‘阴阳互补’思路，如果有多个部分，则几部分的{1,0,……}相加，应得到{1,1,1,1,1……}，也可以说是铺地砖的思路。

d) 用“&”并合，会把数字格式的‘数据’变成文本格式的‘数据’。

e) 这类公式中，if({1,0},xxx,yyy)这种结构，其实阉割了半截没用。能否让公式简化呢，就不能‘太监’了，请看下一回。

10

第五回 从数组的角度理解if({1,0},xxx,yyy)

28. 先看把if({1,0},xxx,yyy)简化一下，变成if(1,xxx,yyy)和if(0,xxx,yyy)，如图所示，这个相当简单浅显，可以理解成{1,0}变成1或0，得到长度为“1”的数组。

29. 当然，在数组扩展情况下，设置多长的区域，数组会扩展重复。

30. 理解if({1,0},xxx,yyy)，可以看成，因为{1,0}的长度为2，运算过程中，第1步就是产生长度为2的数组（h3:h7本身长度为‘1’，要放进长度为‘2’的数组中，这里就发生了扩展，重复2个h3:h7内容，如图所示第1步），然后根据{1,0}的选择（图中第2步），得到结果（图中第3步）。

11

31. 同理，if({0,1},xxx,yyy)也这样理解。

32. 最后，把这两个子公式合并， =IF({1,0},H3:H7,D3:D7)，这就是这类结构的经典用法。

33. 需要注意的是，前面的例子中，这里的条件是相反的哦。

12

34. 还是这个例子，还可以写成这样，只是把数据区域改变一下，但结果一样。这种没有数组的扩展的方法，前面已经提到，使用的可能有局限性（比如第2列恰好为a列数据），不如前面的方法。

35. 虽然结果一样，但计算过程中小细节却不尽相同，仿前，在第1步时，没有数组扩展，直接选择出h3:i7这部分，第2步通过{1，0}选择后就去掉了i3:I7的内容，最后得到跟前面一样的结果。

13

第六回 从逻辑的角度理解if({1,0},xxx,yyy)

36. 上回提到的if({1,0},xxx,yyy)可以看作两作子公式合并而成，为什么呢？估计有些人并不能一下明白其道理，这回接着说。我们先回顾一下if()函数，它的结构是可以分解的。

37. If(条件，事件1，事件2)中的“条件成立”则“事件1”，“条件不成立”则“事件2”。

a) 我们把“条件成立”表示为“a”，则“条件不成立”就是“非a”；

b) “a”和“非a”构成互补（或者说是反相）；

c) 显示{1，0}（其实是{非0，0}）的反相是{0，非0}，一般表示为{1，0}和{0，1}。

38. 如果看不懂上图，也不太清楚刚才的说法，就举个生活中通俗的例子。

比如命题I：“如果分数大于或等于60分，就为及格，否则不及格。”，这句话，相当于：

A:某人说：“如果分数大于或等于60分，就为及格，否则(此处不吱声）。”，然后缓口气，接着说“如果分数大于或等于60分，(此处不吱声），否则不及格。”

B: 某人说：“如果分数大于或等于60分，就为及格，否则(此处不吱声）。”，然后缓口气，接着说“如果分数小于60分，就为不及格，否则(此处不吱声）。”

c: 某人说：“如果分数小于60分，就为不及格，否则(此处不吱声）。”，然后缓口气，接着说“如果分数小于60分，就为不及格，否则及格。

……

39. 尽管这几种方式表现各异，但他表达的意思完全一样，不过把一句分成两句来说而已。

前几回表达的内容，其实就是上面的第2式（反相式）。注意图中的式子，if({1,0},xxx,yyy)其实等价于if({1,0},xxx)& if({0,1},yyy)，前面一直强调阴阳互补即‘反相式’。

14

40. 回头再前面这个示例，前面这个方法。

41. 把公式合并一下，至此，就得到if({1,0},xxx,yyy)结构的正规用法，上回是从{1,0}数组长度然后进行选择的角度出发，这回是从逻辑的角度出发。

42. 这个结果，还可以由其他一些形式构成，如图示例。

15

43. 把{1,0}反一下，颠倒秩序，也得到同样结果。

44. 同样的，IF还可以‘花式镶嵌’。通常来说，这种{1,0;0,1……}这种‘行列’的组合，太复杂，一般慎用，知道有这个可能就行了。

16

第七回 if({1,0},xxx,yyy)嵌套

45. if({0,1},xxx,yyy)同样跟普通if函数一样，是可以嵌套的，这就更复杂了。下图的例子按图中“1-2-3”的秩序拼接数组，就用了嵌套。

46. 如何解读这个公式呢，先看公式中的后半截if({0,1},xxx,yyy)中的yyy部分，这部分得到如图结果。

47. 详细解释下，（绿色）h3:h7列第一步单列数据扩展，第二步按{0，1，0}选择，得到第三步结果。

（黄色）D3:d7列仿此，然后再部分结果合并，得到最下图结果。需要注意的是，D3:d7的选择依据是{0,1,0}反相结果{1,0,1}，前面长篇累牍解释过这个原理。

17

48. 总的公式，其实变成图中浅青色两个数组的并合的问题了。

49. 公式第1部分，其实得到如图这样的结果，也就是扩展一下，得到一个长为3的数组（后面两列空着，预留座位）。

50. 接下来对另一块反相选择，最后拼合就是最终结果。

18

19

第八回 {1,0}嵌套的宽度问题

51. 上一回的例子中，{1,0……}这部分为什么要写这么长，“简化”点不行吗？如图所示，把公式后半截改成这样，结果就错了。

52. 刚才的公式后半截从语法上看是正确的，就是得到下图黄色这个长为2的数组，但是这个题目其实需要3列数据拼接而成，蓝色区域是产生的长度为3的数组。但问题就在于这里，这两部分并不能直接拼接。

53. 下图的公式，其实只是一个长度为2的数组结构，无法将‘1-2-3’这几列拼接而成，失败。

20

54. 总结一下：就是说，拼接多列成一个数组时，首先要考虑这个‘最后’得到的数组的长度是多少，然后根据这个量去确定{1，0，……}的长度，空余的地方用0补充。如果公式中前后几部分产生的数组长度不一致，会导致最后结果的失败。

21

第九回 if({1,0},xxx,yyy)的一个经典实例

55. 前面介绍了if({1,0},xxx,yyy)的原理，现在介绍一个实例，也就是许多人初次见到这种结构的出现的地方，是查找中，如图所示，怎样求图中‘工资’呢，当然公式很多，但对一些VLOOKUP偏执狂来说，问题就来了，VLOOKUP需要从左向右进行，而这里相反，无法直接套公式。

56. 当然，交换A、B列可以顺利完成任务。

57. 如果不准交换，就可以用if({1,0},xxx,yyy)解决，如图所示。（呵呵，这个就是最经典的实例了，许多人知道if({1,0}这个东东，大概就出现在在这种状况中。注意这个公式，最后不按CTRL+SHIFT+回车生成数组公式，而向下拉即可。)

22

58. 这个公式的核心，就是利用if({1,0},xxx,yyy)得到一个‘新的’数组，这个新数组只不过并不显示到单元格，而是存储到内存中，即内存数组；新数组的排列正好跟原来数据交换秩序。然后VLOOKUP是对这个虚拟的“新数组”进行进一步运算，得到最终结果。

59. 再看一例，比刚才复杂些，增加了‘分红’栏目，3列都颠倒了，怎样查找呢，利用前面的知识就可以完成，‘工资’‘分红’都用这个公式，右拉下拉。

23

第十回 choose()函数

60. 前面用了九回介绍if({1,0},xxx,yyy)的原理，基本就完了，相信看过的朋友，应该就懂了其原理，并且也是作一些发散性练习，本文准备到此结束。但是还有一些疑惑未解：

a) 比如上一回最后的例子，对于这种if({1,0……}嵌套的实例，其实基本上很难见到？

b) 如果需要拼凑更n列，难道不是要把{1,0……}长度扩展为n，然后反复if嵌套?估计脑袋早就绕晕了。

61. 针对上述疑惑，可以用choose()函数，如图所示，公式中{2，1}相当于是指定顺序。

62. 这一例，指定一个顺序的{1,2,3,4,5}，然后在后面按顺序指定列，完成公式。

63. 上一回的实例，可以换成这个公式，图中三个公式的结果一样。

64. 而刚才这个示例，换这个公式，这样简明多了，可读性增强。

24

65. 本文主要针对if({1,0},xxx,yyy)的原理进行了通俗到底，简单到底详解。

最后，总结一下，if({1,0},xxx,yyy)简单地说就是为了将拼凑数据，根据数据“有选择”地拼成一个内存数组，为其他函数提供一个‘新’的数据区域进行下一步操作用。

学习if({1,0},xxx,yyy)，是会涉及到大量的“数组”方面的知识。

if({1,0},xxx,yyy)特点是“万能”，只要用耐心，更复杂的数据区域都能用它拼凑出来，但可能这样的公式超长，无法卒读，光看就眼晕。

所以通常大家都只用其最基本的形式if({1,0}，对于多一点列的拼合，应优先选用CHOOSE来拼合。

看完本文，相信能比较清楚地搞明白这类数组的操作用意了。好了，闲言不多说，欢迎对本文内容斧正，诸位朋友再见。

（西蜀鄙夫xsbf 2017年2月）

25