数组公式从入门到精通——提高篇
资源介绍
| 模拟AND、OR 让我们先来看看为什么要模拟AND、OR，而不用Excel的工作表函数AND()、OR()？ 建立如下图的工作表，分别在D11、D12中输入“=SUM(IF(AND(C2:C7=D9,D2:D7=D10),E2:E7))”、“=SUM(IF((C2:C7=D9)*(D2:D7=D10),E2:E7))”，并分别按“Ctrl+Shift+Enter”结束公式输入。
图2-1 (ArrayFormula_B01.bmp) 之所以创建以上公式，是因为我想对满足“Product ID”为D9，“City”为D10的记录进行汇总，很明显，从上面的返回结果表明D11中的结果是正确的，而D10中的结果是错误的。为什么会是这样呢？在接下来的演示中通过讲述AND()和OR()函数的工作原理来解释为什么D10中的公式返回了错误的结果，以及演示为什么D11中的公式可以神奇般的得到结果。 选中在上面工作表的G2:G7，输入“=OR(C2:C7=D9,D2:D7=D10)”，按“Ctrl+Shift+Enter”；选中H2:H7,输入“=AND(C2:C7=D9,D2:D7=D10)”，按“Ctrl+Shift+Enter”。
图2-2 (ArrayFormula_B02.bmp)
图2-3 (ArrayFormula_B03.bmp) 怎么G2:G7都是TRUE；而H2:H7都是FALSE？实际我们想要的是“图2-3”中的结果。为了节省篇幅，我直接把答案告诉你，G2:G7中的公式相当于“=OR(C2=D9,C3=D9,C4=D9,C5=D9,C6=D9,C7=D9,D2=D10,D3=D10,D4=D10,D5=D10,D6=D10,D7=D10”，这回知道原因了吧？“=OR(C2:C7=D9,D2:D7=D10)”返回的结果只有一个，而不是七个！同理，AND()函数类似。不信，你可以更改数据表中的一些数据来进行验证。 现在你该知道D10返回错误值的原因了吧？那为什么D11能够返回正确的结果？这正是我们要解决AND()和OR()函数在数组公式中存在问题的出发点。先看看下面这个说法：“*”相当于AND，“+”相当于OR。这是一些论坛中常见的回答，我到如今为止也这样解答了不少朋友的疑问。结论正确么？难道Excel中的“*”和“+”有两层含义？――严格的说，这是不正确的！因此，我已经误导了很多朋友，如果你曾经在某论坛中得到过我这样的解答，我在这里说声抱歉！为什么“*”和“+”可以模拟AND和OR呢？就像“图2-1”中D12的公式“=SUM(IF((C2:C7=D9)*(D2:D7=D10),E2:E7))”。 要了解其原理，就要揭开FALSE和TRUE的面纱。在一新工作表的C2中输入“=TRUE+0”，按回车键；在D2中输入“=FALSE+0”，按回车键。
“图2-4”中的结果说明：将TRUE和FALSE转换为整型后的值分别为1和0。 建立如下图中的工作表，选中D2:E3，输入“=D$1*$C2”，按“Ctrl+Enter”；同样选中D6:E7，输入“=D$5+$C6”，按“Ctrl+Enter”。
从上图中很容易看出，对于“乘”操作，只有TRUE*TRUE才会返回1（TRUE），因此“*”模拟了AND的效果；对于“加”操作，只有FALSE+FALSE才会返回0（FALSE），因此“+”模拟了OR的效果。 理解IF()
图2-6 (ArrayFormula_B06.bmp) C5中的公式为“=IF(C2:C3="Mary",ROW(D2:D3))”（为数组公式），你知道它的值为什么是FALSE而不是三么？
图2-7 (ArrayFormula_B07.bmp)
图2-8 (ArrayFormula_B08.bmp) 结果竟然是3，而不是0！这就是Excel聪明之处！为什么说聪明呢？因为在绝大部分情况下我们想要的结果是满足条件的部分，而舍弃非满足条件的部分。这对筛选数据非常有帮助！如果你坚持要将非满足条件的部分包含进来，最简单的方法可以将公式变形为“=MIN(IF(C2:C3="Mary",ROW(D2:D3),))”，简简单单的一个逗号“,”，结果却截然不同。对于如何对筛选有帮助，将在“应用篇”中给予实例解答。
图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了。 |
图2-4 (ArrayFormula_B04.bmp)
图2-5 (ArrayFormula_B05.bmp)
