精通Excel數組公式012:布爾邏輯:AND和OR

2021-02-13 完美Excel

學習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社群,進行技術交流和提問,獲取更多電子資料。

相關焦點

  • 精通Excel數組公式005:比較數組運算及使用一個或多個條件的聚合計算
    :=  等於<> 不等於> 大於>= 大於等於< 小於<= 小於等於 在諸如基於條件查找最小值或最大值、計算標準偏差等情形時,Excel沒有提供相應的內置函數,必須編寫數組公式,其中往往涉及到在數組中使用比較運算符。
  • Excel公式與函數之美07:公式中的王者——數組公式
    使用數組公式,可以判斷數據是否與指定區域中的數據相匹配,可以統計單元格區域中不重複值的數量,可以提取單元格區域中的不重複數據,將列數據轉換為行數據,…,等等。本文只是簡單地介紹數組公式,讓感興趣的朋友對其有所了解,為進一步的研究和應用打下基礎。
  • 精通Excel數組公式15:使用INDEX函數和OFFSET函數創建動態單元格區域(續)
    導語:本文為《精通Excel數組公式14:使用INDEX函數和OFFSET函數創建動態單元格區域》的後半部分。圖6:部分數據缺失,需要公式來定義單元格區域A2:C6 可以使用數組公式:=$A$2:INDEX($C$2:$C$7,MAX((ROW($A$2:$C$7)-ROW($A$2)+1)*($A$2:$C$7<>""))) 也可以使用數組公式:=$A$2:INDEX($C$2:$C
  • excel函數公式:空值和邏輯值實用案例技巧解讀!
    最近今天小編也在準備年會和年底各項業績數據統計,忙的是不可開交啊。這兩天都沒有更新啦。同時在這裡也祝福各位粉絲們年會都能抽中大獎,年終獎數收到手軟哈。今天小編要給大家分享的是 ISBLANK和ISLOGICAL這兩個函數。
  • excel如何實現一對多查詢?學會數組公式,老闆為你點讚
    課程信息卡課程:《Excel天天訓練營》2.0圖文版章節:第2章-精通函數內容:數組基礎(small\large)在excel函數公式裡,大家最頭疼的莫過於數組公式。但數組公式就好像一道題有N個答案,比較複雜。但正因為複雜,它的功能也就更強大,可以根據條件獲得多條結果。為了更好的使用數組公式,我們需要先掌握數組裡常用的配套函數small和large。如圖中案例表格,要根據月份查詢倒數前3名,這種一個條件要獲得多條結果的,就要使用數組函數。
  • 帶你一步步精通EXCEL數組:普通公式與數組公式的本質區別
    今日我將講解數組應用專題的第十一講,內容是EXCEL中數組公式與普通公式的區別。這節中我們要充分認識EXCEL中普通的公式和數組公式。如圖:a)普通公式: b)數組公式:四 :普通公式和數組公式的錄入;普通公式在錄入的時候要以「=」為開始,錄入完成後要以ENTER為結束。輸入數組公式:也要以「=」為開始,但要用Ctrl+Shift+Enter結束公式的輸入。
  • excel數組公式進階:按列查找的時候,記得要使用這個函數!
    課程信息卡課程:《Excel天天訓練營》2.0圖文版章節:第2章-精通函數內容:數組公式(列查找)用excel數組公式,如果遇上按列查找的時候,你會發現公式會出問題。現在,我們就來用函數公式實現excel自動化辦公。一、掌握「transpose函數」的用法transpose函數是將內容由行變成列。
  • 精通Excel數組公式14:使用INDEX函數和OFFSET函數創建動態單元格區域
    動態單元格區域是指當添加或刪除源數據時,或者隨著包含單元格區域的公式被向下複製時根據某條件更改,可以自動擴展或收縮的單元格區域,可以用於公式、圖表、數據透視表和其他位置。 圖1:查找並求2月份的數值之和 注意,圖1所示的公式並不需要按Ctrl+Shift+Enter組合鍵,雖然INDEX函數返回的是一個單元格區域,其原因是沒有執行直接數組操作。
  • Row函數在Excel數組公式中的應用
    絕大多數excel數組公式中,都可以看到row函數的身影,那麼row函數到底起到什麼作用呢?我們先從它的基本用法說起。
  • javaScript 數組經典布爾類型判斷問題
    題目1、 console.log([]==[]);解釋:數組也是對象,兩個數組引用地址不同,所以結果為false2、console.log(!會將後面的數組強轉換為布爾類型 數組為對象類型------>所以空數組轉換為布爾類型也為true------> ![]為false. 如果數組前無!
  • 布爾代數——邏輯代數創始人布爾
    這種變換的有效性不依賴於人們對符號的解釋,只依賴於符號本身的組合規律。這一邏輯體系被人們稱為「布爾代數」。20世紀30年代,邏輯代數在電路系統上獲得應用,隨後,由於電子技術與計算機的發展,出現各種複雜的大系統,它們的變換規律無不遵從布爾所揭示的邏輯體系。布爾著有《邏輯數學分析》、《思維規律的研究》。他的小女兒伏尼契也因《牛虻》一書而聞名。
  • Excel中的高科技:數組公式
    圖一 封面圖一、什麼是數組公式對學過編程的人士來說,變量和數組是一個很常見的概念,一般一個變量只能儲存一個值,而數組卻可以儲存一組值,並可以進行總體或個別操作。類似的,Excel中的數組公式是相對於普通公式而言的,普通公式的計算結果是單一值,數組公式計算結果卻可以是多個值,而且數組不必存儲在單元格中,也可以使用只儲存在Excel內存中的數組。
  • EXCEL中數組專題之八:數組及數組公式的制約性與集合性(一)
    今日給大家講的是數組及數組公式的制約性和集合性的第一講,也是數組應用的第八講,這一講的內容主要是回顧一下之前的內容,難度不是很大。一: 數組及數組公式引用大小制約性 我們先看上面的截圖,在C5中的錄入公式為「=IF(C2:C3="Mary",ROW(D2:D3))」(為數組公式),你知道它的返回值為什麼是FALSE?這裡就要理解一個概念,數組的大小的制約性。或許你不是很理解,下面我慢慢的講解。
  • Excel公式技巧妙用:Large,if組合使用
    說到excel公式,很多人都是持一種敬而遠之的態度。很多人會覺得公式太難了,覺得用不上,或是覺得不需要。小編excel公式並沒有大家想像的那麼神秘,那麼高級,並且使用excel公式是平時工作做不可避免的。
  • 批處理數據神器:Excel數組公式全解析丨免費微課
    一小時精通職場辦公必備Excel函數!
  • EXCEL中數組的應用之五:什麼是函數公式,什麼是數組公式
    今日講數組的應用之五,今日主要是再次和大家講解幾個概念,什麼是數組、什麼是公式、什麼是數組公式、什麼是函數等概念。我在文章中反覆的講解這些概念,但概念很抽象,是否掌握了概念的實質,可以從對概念的理解上得到確認。
  • 教你使用Excel數組公式
    Excel中數組公式非常有用,尤其在不能使用工作表函數直接得到結果時,數組公式顯得特別重要,它可建立產生多值或對一組值而不是單個值進行操作的公式。  編輯或刪除數組公式編輯數組公式時,須選取數組區域並且激活編輯欄,公式兩邊的花括號將消失,然後編輯公式,最後按Ctrl+Shift+Enter鍵。選取數組公式所佔有的全部區域後,按Delete鍵即可刪除數組公式。  數組常量的使用數組公式中還可使用數組常量,但必須自己鍵入花括號「{}」將數組常量括起來,並且用「,」和「;」分離元素。
  • 什麼是數組公式?你真的了解嗎?一起來認識Excel數組公式
    上篇文章數組的基本知識,可回顧一下:可是,什麼是數組公式?你真的了解嗎?一起來認識Excel數組公式經過對數組公式有了一個簡單的了解之後,我們將通過一些簡單的例子來進一步認識數組公式。我們今天一起對數組公式來一個簡單的了解吧!
  • excel查找技巧:數組函數在區間查找中的應用解析
    但excel最大的魅力就是它的多元性,任何一道題都是一題多解的。本篇是區間查找系列的最後一篇教程——數組函數篇,同時它也是本次系列教程中最難的一篇。快跟著小編一起來學習吧!這裡也教大家一個學習數組函數的小竅門,就是如何看到那些看不到的內存數據。以C2單元格為例,我們可以通過工具欄中公式——公式審核——公式求值來看到這些內容。當我們選中C2單元格,然後滑鼠單擊「公式求值」按鈕,就會彈出公式求值窗口,此時就可以看到我們設置的函數內容。
  • excel查找技巧:數組函數在區間查找中的應用解析
    但excel最大的魅力就是它的多元性,任何一道題都是一題多解的。本篇是區間查找系列的最後一篇教程——數組函數篇,同時它也是本次系列教程中最難的一篇。快跟著小編一起來學習吧!這裡也教大家一個學習數組函數的小竅門,就是如何看到那些看不到的內存數據。以C2單元格為例,我們可以通過工具欄中公式——公式審核——公式求值來看到這些內容。