数组公式从入门到精通——提高篇

图2-1 (ArrayFormula_B01.bmp)

图2-2 (ArrayFormula_B02.bmp)

图2-3 (ArrayFormula_B03.bmp)

图2-4 (ArrayFormula_B04.bmp)

      “图2-4”中的结果说明：将TRUE和FALSE转换为整型后的值分别为1和0。

      建立如下图中的工作表，选中D2:E3，输入“=D$1*$C2”，按“Ctrl+Enter”；同样选中D6:E7，输入“=D$5+$C6”，按“Ctrl+Enter”。

图2-5 (ArrayFormula_B05.bmp)

      从上图中很容易看出，对于“乘”操作，只有TRUE*TRUE才会返回1（TRUE），因此“*”模拟了AND的效果；对于“加”操作，只有FALSE+FALSE才会返回0（FALSE），因此“+”模拟了OR的效果。
      技术说明：

      理解IF()
      IF()还用理解？Excel Online Help中不是已经表达的很清楚了吗？也许你会这样问。
      我并非是想文字充数，请看下图：

图2-6 (ArrayFormula_B06.bmp)

      C5中的公式为“=IF(C2:C3="Mary",ROW(D2:D3))”（为数组公式），你知道它的值为什么是FALSE而不是三么？
      聪明的你可能已经想到这种类型的数组公式返回的是一个结果集，这个结果集的大小与操作对象的大小是一致的，在这里操作对象为C2:C3和D2:D3，因此返回值为两个元素。
      就是这样，由于C2=”John”，不满足条件，因此应该返回IF()函数的第三个参数值，但这里无第三个参数，所以系统返回FALSE；由于C3=”Mary”，满足条件，因此返回第二个参数值，即ROW(D2:D3)，而C3对应的是D3，所以返回值应该为3。为了验证结果，请选择C5:C6，输入“=IF(C2:C3="Mary",ROW(D2:D3))”，按“Ctrl+Shift+Enter”。结果如何？

图2-7 (ArrayFormula_B07.bmp)

 
      聪明的Excel
      先看看这个，知道“=MIN(FALSE,3)”的返回值么？结果返回0，从上面论述的知识不难理解，因为FALSE转换为整型的值为0。我们已经知道“图2-7”中“=IF(C2:C3="Mary",ROW(D2:D3))”的结果集为“{FALSE,3}”，那么，请选择“图2-7”中的D5，输入“=MIN(IF(C2:C3="Mary",ROW(D2:D3)))”，按“Ctrl+Shift+Enter”，看结果。

图2-8 (ArrayFormula_B08.bmp)

      结果竟然是3，而不是0！这就是Excel聪明之处！为什么说聪明呢？因为在绝大部分情况下我们想要的结果是满足条件的部分，而舍弃非满足条件的部分。这对筛选数据非常有帮助！如果你坚持要将非满足条件的部分包含进来，最简单的方法可以将公式变形为“=MIN(IF(C2:C3="Mary",ROW(D2:D3),))”，简简单单的一个逗号“,”，结果却截然不同。对于如何对筛选有帮助，将在“应用篇”中给予实例解答。
 
      模拟IF()
      再来看看“图2-1”中D12的公式“=SUM(IF((C2:C7=D9)*(D2:D7=D10),E2:E7))”，让我们换种形式。在E12中输入“”，按“Ctrl+Shift+Enter”。

图2-9 (ArrayFormula_B09.bmp)

      结果也是30！所以“*”可以模拟IF()！由于我们已经揭开了TRUE和FALSE的面纱，因此不难理解，对于“*”操作，只有TRUE*TRUE才会返回1，所以结果相当于“=SUM(0*12.34, 0*13.34, 1*30, 0*29, 0*103.05, 0*113.05)”，当然结果为30了。
      注意：并非所有情况下“*”与IF()效果都相同，要视具体情况而定，这就需要你灵活掌握了。
      Excel中的IF()工作表函数对条件真假的判断是这样，当条件的值为0时，认为是假；否则，全部认为是真。条件的数据类型一定是数值。比如“=IF(-3,1,0)”返回1。因此“+”的操作做到了模拟OR的效果。