Excel函數應用之邏輯函數編者語：Excel是辦公室自動化中非常重要的一款軟件，很多巨型國際企業都是依靠Excel進行數據管理。它不僅僅能夠方便的處理表格和進行圖形分析，其更強大的功能體現在對數據的自動處理和計算，然而很多缺少理工科背景或是對Excel強大數據處理功能不了解的人卻難以進一步深入。編者以為，對Excel函數應用的不了解正是阻擋普通用戶完全掌握Excel的攔路虎，然而目前這一部份容的教學文章卻又很少見，所以特別組織了這一個《Excel函數應用》系列，希望能夠對Excel進階者有所幫助。《Excel函數應用》系列，將每周更新，逐步系統的介紹Excel各類函數及其應用，敬請關注！用來判斷真假值，或者進行復合檢驗的Excel函數，我們稱為邏輯函數。在Excel中提供了六種邏輯函數。即AND、OR、NOT、FALSE、IF、TRUE函數一、AND、OR、NOT函數這三個函數都用來返回參數邏輯值。詳細介紹見下：（一）AND函數所有參數的邏輯值為真時返回TRUE；只要一個參數的邏輯值為假即返回FALSE。簡言之，就是當AND的參數全部滿足某一條件時，返回結果為TRUE，否則為FALSE。.下載可編輯.語法為AND(logical1,logical2,...),其中Logicall,logical2,...表示待檢測的1到30個條件值，各條件值可能為TRUE，可能為FALSE。參數必須是邏輯值，或者包含邏輯值的數組或引用。舉例說明：1、在B2單元格中輸入數字50，在C2中寫公式=AND(B2>30,B2<60)。由于B2等于50的確大于30、小于60。所以兩個條件值(logical)均為真，則返回結果為TRUE。圖1AND函數示例12、如果B1-B3單元格中的值為TRUE、FALSE、TRUE，顯然三個參數并不都為真，所以在B4單元格中的公式二AND(B1:B3)等于FALSE5二,二AMI(EL呼AIB「~CD|TRUEITRUE| |FALSE圖2AND函數示例2(二)OR函數OR函數指在其參數組中，任何一個參數邏輯值為TRUE，即返回TRUE。它與AND函數的區別在于，AND函數要求所有函數邏輯值均為真，結果方為真。而OR函數僅需其中任.下載可編輯.何一個為真即可為真。比如，上面的示例2，如果在B4單元格中的公式寫為=OR(B1:B3)1則結果等于TRUE希J菱一…J：/尸*|」.二 二| ：|二。\B|C.TRUSTRUE|FALSEj | ITRUEI圖3OR函數示例(三)NOT函數 Excel函數系列?Excel函數應用之數學和三角函數NOT函數用于對參數值求反。當要確保一個?Excel函數應用之函數簡介值不等于某一特定值時，可以使用NOT函數。簡言之，就是當參數值為TRUE時，NOT函數返回的結果恰與之相反，結果為FALSE.比如NOT(2+2=4)，由于2+2的結果的確為4，該參數結果為TRUE,由于是NOT函數,因此返回函數結果與之相反，為FALSE。二、TRUE、FALSE函數TRUE、FALSE函數用來返回參數的邏輯值，由于可以直接在單元格或公式中鍵入值TRUE或者FALSE。因此這兩個函數通常可以不使用。三、訐函數(一)IF函數說明.下載可編輯.IF函數用于執行真假值判斷后，根據邏輯測試的真假值返回不同的結果，因此If函數也稱之為條件函數。它的應用很廣泛，可以使用函數IF對數值和公式進行條件檢測。它的語法為IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示計算結果為TRUE或FALSE的任意值或表達式。本參數可使用任何比較運算符。Value_if_true顯示在logical_test為TRUE時返回的值，Value_if_true也可以是其他公式。Value_if_falselogical_test為FALSE時返回的值。Value_if_false也可以是其他公式。簡言之，如果第一個參數logical_test返回的結果為真的話，則執行第二個參數Value_if_true的結果，否則執行第三個參數Value_if_false的結果。IF函數可以嵌套七層，用value_if_false及value_if_true參數可以構造復雜的檢測條件。Excel還提供了可根據某一條件來分析數據的其他函數。例如，如果要計算單元格區域中某個文本串或數字出現的次數，則可使用COUNTIF工作表函數。如果要根據單元格區域中的某一文本串或數字求和，則可使用SUMIF工作表函數。(二)IF函數應用1、輸出帶有公式的空白表單.下載可編輯.315 1|_口|三A4DEFG人事狀況分析表——部門;總計工程部辦公室財務部,宜鼎魚4-」----25歲以下02&飛。歲0-3「35歲0；3口45歲0-45歲以上0L.高級職稱■ 0：中級職稱Q：初級職前： 0：見習期」高級職稱6中級職稱0.初級職稱作見習期0圖5人事分析表1以圖中所示的人事狀況分析表為例，由于各部門關于人員的組成情況的數據尚未填寫，在總計欄(以單元格G5為例)公式為：=SUM(C5:F5)我們看到計算為0的結果。如果這樣的表格打印出來就頁面的美觀來看顯示是不令人滿意的。是否有辦法去掉總計欄中的0呢？你可能會說，不寫公式不就行了。當然這是一個辦法，但是，如果我們利用了IF函數的話，也可以在寫公式的情況下，同樣不顯示這些0。如何實現呢？只需將總計欄中的公式(僅以單元格G5為例)改寫成：=IF(SUM(C5:F5),SUM(C5:F5),"").下載可編輯.

通俗的解釋就是：如果SUM(C5:F5)不等于零，則在單元格中顯示SUM(C5:F5)的結果，否則顯示字符串。幾點說明：(1)SUM(C5:F5)不等于零的正規寫法是SUM(C5:F5)<>0，在EXCEL中可以省略<>0；(2)""表示字符串的容為空，因此執行的結果是在單元格中不顯示任何字符。G5-I=|二IF(日則版:阿，35仁5于5)尸A■B-CDEFG人事狀況分析表部門總計銷售部工程部辦公室財務部25歲以下2曠3。歲3廠35歲36~45歲45歲以上1嫁能級職稱中級職必■⑸初級職祚見習期高領.工R-稱中級職孫i:初級職祚見習躺2、不同的條件返回不同的結果如果對上述例子有了很好的理解后，我們就很容易將IF函數應用到更廣泛的領域。比如，在成績表中根據不同的成績區分合格與不合格。現在我們就以某班級的英語成績為例具體說.下載可編輯.明用法。目haBL2?副y1宋體 ▼■=1二工F（EllMO「合相-12.廣〃二TBZU室強至左圉E，AB.,翳,豆.EFGHI1,3janyMikeHenyAnnieJackyAndy^mile且數學.955675651-0068785語乂75608575959885英語6554諭■WO§5-9QSO物理100■557040加70- 65化學86: 78：508889二799政治7859= 8070S890SO10歷史9Q5086T585958511平均分S453SI6S92S67912券合評定座格!不合格合格合格合彳合格合格13某班級的成績如圖6所示，為了做出最終的綜合評定，我們設定按照平均分判斷該學生成績是否合格的規則。如果各科平均分超過60分則認為是合格的，否則記作不合格。根據這一規則，我們在綜合評定中寫公式（以單元格B12為例）：=IF（B11>60,“合格","不合格”）語法解釋為，如果單元格B11的值大于60，則執行第二個參數即在單元格B12中顯示合格字樣，否則執行第三個參數即在單元格B12中顯示不合格字樣。.下載可編輯.在綜合評定欄中可以看到由于C列的同學各科平均分為54分，綜合評定為不合格。其余均為合格。3、多層嵌套函數的應用在上述的例子中，我們只是將成績簡單區分為合格與不合格，在實際應用中，成績通常是有多個等級的，比如優、良、中、及格、不及格等。有辦法一次性區分嗎？可以使用多層嵌套的辦法來實現。仍以上例為例，我們設定綜合評定的規則為當各科平均分超過90時，評定為優秀。如圖7所示。30|川口一￡1?L赳 ?■日『口等三立口蹄％.■白勺-錄制酒昨caw中財ujv合1AcD?,1GHi J3MdctHeriyAjisuf「AjidjrSmiteq,S5.I5匕75651麗施’TK5，唾—..7560357595玳:近6&5的9?工的甑和BQ7物理1典砧?040綱70由8低手4。78E.U盟,麻_799政茹.73酩時IPBE其10第56S675二弼0511平均分劑5,■:8168品7312合節強根吉拄童鼠尚8_13說明：為了解釋起來比較方便，我們在這里僅做兩重嵌套的示例，您可以按照實際情況進行更多重的嵌套，但請注意Excel的IF函數最多允許七重嵌套。根據這一規則，我們在綜合評定中寫公式（以單元格F12為例）:.下載可編輯.=IF(F11>60,IF(AND(F11>90),"優秀","合格)"不合格")語法解釋為，如果單元格F11的值大于60，則執行第二個參數，在這里為嵌套函數，繼續判斷單元格F11的值是否大于90(為了讓大家體會一下AND函數的應用，寫成AND(F11>90)，實際上可以僅寫F11>90),如果滿足在單元格F12中顯示優秀字樣，不滿足顯示合格字樣，如果F11的值以上條件都不滿足，則執行第三個參數即在單元格F12中顯示不合格字樣。在綜合評定欄中可以看到由于F列的同學各科平均分為92分，綜合評定為優秀。(三)根據條件計算值在了解了IF函數的使用方法后，我們再來看看與之類似的Excel提供的可根據某一條件來分析數據的其他函數。例如，如果要計算單元格區域中某個文本串或數字出現的次數，則可使用COUNTIF工作表函數。如果要根據單元格區域中的某一文本串或數字求和，則可使用SUMIF工作表函數。關于SUMIF函數在數學與三角函數中以做了較為詳細的介紹。這里重點介紹COUNTIF的應用。COUNTIF可以用來計算給定區域滿足特定條件的單元格的數目。比如在成績表中計算每位學生取得優秀成績的課程數。在工資表中求出所有基本工資在2000元以上的員工數。語法形式為COUNTIF(range,criteria)。其中Range為需要計算其中滿足條件的單元格數.下載可編輯.目的單元格區域。Criteria確定哪些單元格將被計算在的條件，其形式可以為數字、表達式或文本。例如，條件可以表示為32、"32"、">32"、"apples"。1、成績表這里仍以上述成績表的例子說明一些應用方法。我們需要計算的是：每位學生取得優秀成績的課程數。規則為成績大于90分記做優秀。如圖8所示B13二 ==CQUNTIF-CQ9Q")■ABCDE,,搖.HjanyMikeHerryAnnieTackyAndy-Smile數學955675:,65100687S語文75608575能施85一英語6554WW09500SO物理100557040907065訛學8640785。888979政若78598070889080歷史905536碼3505甌平均分S45481GS928679維,蕓評定合格不合格合格合格優秀合格合格優秀門數112!l00132圖8根據這一規則，我們在優秀門數中寫公式(以單元格B13為例):=COUNTIF(B4:B10,">90")語法解釋為，計算B4到B10這個圍，即jarry的各科成績中有多少個數值大于90的單元格。.下載可編輯.在優秀門數欄中可以看到jarry的優秀門數為兩門。其他人也可以依次看到。2、銷售業績表銷售業績表可能是綜合運用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望計算銷售人員的訂單數，然后匯總每個銷售人員的銷售額，并且根據總發貨量決定每次銷售應獲得的獎金。原始數據表如圖9所示（原始數據是以流水單形式列出的，即按訂單號排列）?A - C訂單號訂單金額銷售人員22QQ104015j000.00JARRY■20010402幻500.GOHELEN42001040325,.'000；00.MICHAEL520010404.200.00JARRY62.0010405500..00/ANNIE72001040S：.2/500,00ANNIEg20010407"i氏bo值66JARRY,92口。104/：95,m.00MICHAEL1020Q104091,000.00HELEN■20010410叫990.00HELEN12200104112,530'.00;JARRY11320010412.200.00ANNIE14乳耳蛇0.0T圖9原始數據表按銷售人員匯總表如圖10所示.下載可編輯.BIT 二| ==COI.1NTIF3C$2：-.$C$1^Al7)'ABICD銷售人員訂單數訂單總顫TEzuANNIETQ/ZDO.0C1J02Q.00-JARRY26,7B0.002.673.00HELEN355,50Q.00：:除羽工00MICHAEL2120,000.DO：18.000,00訂單總數12212,480-0030.餞3.00趾$8湖$13；A1，獨$2：部$1即=If.I；C17<50000,10%；15%>ClT圖10銷售人員匯總表如圖10所示的表完全是利用函數計算的方法自動匯總的數據。首