數組公式里怎麼表達「和」與「或」？

Excel在單個單元格中可以用AND()和OR()函數分別表示同時滿足多個條件或滿足一個或任意若干個條件。但是在數組公式中如果用AND()或者OR()函數判斷多行多列數據是否符合條件會出錯，因為這兩個函數只能返回一個TRUE或者FALSE的結果。

1

數組公式里怎麼表達「和」

如下圖，要求A列姓名為「A」並且B列數量大於150的項目對應的數量合計，也就是說同時滿足這兩個條件。

在任意單元格輸入公式：

=SUMPRODUCT((A2:A7="A")*(B2:B7>150)*B2:B7)

回車後即可得到如下圖的計算結果。

在上面的例子中，我們用「*」，也就是乘號代表了同時滿足兩個條件。

(A2:A7="A")*(B2:B7>150)，其中(A2:A7="A")和(B2:B7>150)分別判斷兩個條件是否成立，如果成立則返回TRUE，反之返回FALSE。而在兩個返回數組相乘的時候，TRUE會相當於1，FALSE相當於0，也就是只有兩個數組中同樣位置的返回值都是TRUE，結果才返回1，否則返回0。然後再分別乘以B2:B7就使同時滿足兩個條件的1乘以B2:B7，不滿足的0也乘積B2:B7。然後將返回的數組求和即可。

2

數組公式里怎麼表達「或」

如下圖，我們求A列姓名為「B」或者「C」對應的B列數量合計。這個可以用SUMIFS或者SUMIF等函數完成，但是本例主要用SUM和IF函數完成來體會IF第一參數滿足「或者」條件的用法。

在任意單元格輸入：

=SUM(IF((A2:A7="B") (A2:A7="C"),B2:B7))

左手按住Ctrl Shift，右手按Enter運行公式即可得到結果。

上面的例子用(A2:A7="B") (A2:A7="C")來表示滿足「B」或「C」其中一個條件就計算B列數量合計。原理是這樣的：

(A2:A7="B")返回TRUE或者FALSE，(A2:A7="C")同樣返回TRUE或者FALSE的數組。在參與加法運算時TRUE相當於1，FALSE相當於0。只要兩個條件中有一個返回結果是TRUE，相加返回的數組結果就是1，兩個都不滿足，返回數組元素是0。然後利用IF函數第一參數非0即返回第二參數，是0就返回第三參數的特性將符合條件的B2:B7中數據記錄下來，不滿足條件的返回FALSE。這樣用SUM求返回數組中的數字合計就是想要的結果了。

了解數組公式運行如何表達「和」與「或」的關係是非常重要的，接下來就可以進行更複雜的多條件運算了。

超人一籌