學習Excel技術,關注微信公眾號:
excelperfect
導言:本文為《精通Excel數組公式(學習筆記版)》中的一部分內容節選。如果你想要系統學習並熟練掌握Excel數組公式,可以到知識星球App的完美Excel社群下載這本電子書學習。
布爾(Boolean)是一種數據類型,僅有兩個值,即TRUE或FALSE,或者1或0:
TRUE = 1
FALSE = 0
在Excel公式中,經常要用到邏輯條件。對於AND條件來說,只有所有的邏輯測試都為TRUE時,結果才為TRUE;對於OR條件來說,只要有一個邏輯測試為TRUE,結果就是TRUE。下圖1列出了3個條件時的所有邏輯測試。
圖1
AND條件
當執行AND邏輯測試時,所有的測試都必須為TRUE,最終的AND邏輯測試結果才是TRUE。如果任何一個邏輯測試為FALSE,AND邏輯測試的結果為FALSE。
下面展示了一些AND條件的示例。
圖2:公式[1]至[4]使用了AND條件。公式[5]使用布爾乘法的AND條件。公式[6]使用布爾乘法和除法的AND條件。公式[7]使用IF函數和布爾AND條件。
圖3:帶篩選的數據透視表執行AND條件的求和運算。
圖4:具有已應用篩選和總計行的表功能可以使用AND條件進行平均值計算。
圖5:篩選功能可以使用AND條件篩選表。
圖6:高級篩選功能可以使用AND條件提取記錄。
圖7:在輔助列中使用了帶有4個邏輯測試的AND函數。
圖8:使用邏輯測試相乘來創建布爾輔助列。
正如在上述例子中所看到的,諸如像SUMIFS函數、使用布爾運算或IF函數的數組公式、數據透視表、帶有篩選和匯總行的表、篩選、高級篩選、以及輔助列解決方法都可以使用AND條件運算。
OR條件
當執行OR邏輯測試時,只要有一個測試為TRUE,最終的OR邏輯測試結果就是TRUE。只有當所有的邏輯測試都為FALSE時,OR邏輯測試的結果才為FALSE。我們經常使用「至少有一個」和「一個或多個」邏輯測試來描述OR邏輯測試。
下面是兩個OR條件的示例。
圖9:單元格中的姓名是否是「Gidget」或「Rodger」。
圖10:測試客戶的淨資產大於100000,或者信用評級大於或等於3.5。
上圖10所示的例子中,OR條件測試獲得了兩個TRUE值,此時必須小心,特別是使用其作為另一公式的元素時。
1.OR邏輯測試結果為1個TRUE值:通常指向單個單元格且遍歷單列。
2.OR邏輯測試結果多於1個TRUE值:通常指向不同的單元格且遍歷多列。
示例:使用不能返回多個TRUE值的OR邏輯測試統計
如下圖11所示,使用了5個公式分別統計滿足條件的項目數量。
圖11:OR條件統計在單個單元格且單列中查找。
示例:使用返回多個TRUE值的OR邏輯測試統計
如下圖12所示,如果在創建OR條件公式時不細心,那麼可能會統計兩次。示例統計淨資產大於100000或者信用評級大於等於3.5的客戶數。因為兩個問題在兩列中查詢,對於特定的客戶可能會返回兩個TRUE值,導致該客戶被統計兩次,例如Fruits Inc.的淨資產大於100000且信用評級大於等於3.5,在公式[4]和[5]中對該公司統計了兩次,返回不正確的結果。而公式[1]、[2]和[3]只統計一次,返回正確的結果。
圖12:OR邏輯測試指向兩個不同的單元格,因此可能返回兩個TRUE值;OR條件統計公式查找兩列。
用於求和、求平均值和查找最小或最大值的OR條件
示例如下圖13至圖15所示。
圖13:使用應用到單列的OR條件來求和和求平均值。
圖14:使用應用到不同列的OR條件來求和和求平均值。單個的OR邏輯測試可能產生多個TRUE值。
圖15:使用應用到不同列的OR條件來求最小值和最大值。單個的OR邏輯測試可能產生多個TRUE值。在AGGREGATE函數的公式中,使用除法剔除0值。
在公式中同時使用AND條件和OR條件:OR邏輯測試不會返回多個TRUE值
當在公式中同時使用AND條件和OR條件時,仍然取決於OR邏輯測試是否返回多個TRUE值。
如下圖16所示,求區域West和Midwest在2019年3月18日和2019年5月12日之間的數量、營業額之和、最小營業額和平均營業額。
圖16:AND和OR條件,此時OR邏輯測試指向單列。
在公式中同時使用AND條件和OR條件:OR邏輯測試會返回多個TRUE值
如下圖17所示,求淨資產大於100000,淨收入大於等於37500,信用評級1大於等於3.5或信用評級2大於等於6的客戶數、最大淨資產和平均淨資產。
圖17:AND和OR條件,此時OR邏輯測試指向多列。
註:如果有多個OR條件,可以使用ISNUMBER函數和MATCH函數的組合。
歡迎在下面留言,完善本文內容,讓更多的人學到更完美的知識。
歡迎到知識星球:完美Excel社群,進行技術交流和提問,獲取更多電子資料。