Excel函數(shù)應(yīng)用之邏輯函數(shù)

1、Excel函數(shù)應(yīng)用之邏輯函數(shù)用來(lái)判斷真假值，或者進(jìn)行復(fù)合檢驗(yàn)的Excel函數(shù)，我們稱(chēng)為邏輯函數(shù)。在Excel中提供了六種邏輯函數(shù)。即AND、OR、NOT、FALSE、IF、TRUE函數(shù)。一、AND、OR、NOT函數(shù)這三個(gè)函數(shù)都用來(lái)返回參數(shù)邏輯值。詳細(xì)介紹見(jiàn)下：（一）AND函數(shù)所有參數(shù)的邏輯值為真時(shí)返回 TRUE；只要一個(gè)參數(shù)的邏輯值為假即返回 FALSE。簡(jiǎn)言之，就是當(dāng)AND的參數(shù)全部滿足某一條件時(shí)，返回結(jié)果為T(mén)RUE，否則為FALSE。語(yǔ)法為AND(logical1,logical2, .)，其中Logical1, logical2, . 表示待檢測(cè)的 1 到 30 個(gè)條件值，各條件值可能為

2、TRUE，可能為 FALSE。 參數(shù)必須是邏輯值，或者包含邏輯值的數(shù)組或引用。舉例說(shuō)明：1、 在B2單元格中輸入數(shù)字50，在C2中寫(xiě)公式=AND(B230,B260)。由于B2等于50的確大于30、小于60。所以兩個(gè)條件值（logical）均為真，則返回結(jié)果為T(mén)RUE。 圖1 AND函數(shù)示例12、 如果 B1-B3 單元格中的值為 TRUE、FALSE、TRUE，顯然三個(gè)參數(shù)并不都為真，所以 在B4單元格中的公式=AND(B1:B3) 等于 FALSE 圖2 AND函數(shù)示例2（二）OR函數(shù)OR函數(shù)指在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為 TRUE，即返回 TRUE。它與AND函數(shù)的區(qū)別在于，AND

3、函數(shù)要求所有函數(shù)邏輯值均為真，結(jié)果方為真。而OR函數(shù)僅需其中任何一個(gè)為真即可為真。比如，上面的示例2，如果在B4單元格中的公式寫(xiě)為=OR(B1:B3)則結(jié)果等于TRUE 圖3 OR函數(shù)示例（三）NOT函數(shù)NOT函數(shù)用于對(duì)參數(shù)值求反。當(dāng)要確保一個(gè)值不等于某一特定值時(shí)，可以使用 NOT 函數(shù)。簡(jiǎn)言之，就是當(dāng)參數(shù)值為T(mén)RUE時(shí)，NOT函數(shù)返回的結(jié)果恰與之相反，結(jié)果為FALSE.比如NOT(2+2=4)，由于2+2的結(jié)果的確為4，該參數(shù)結(jié)果為T(mén)RUE，由于是NOT函數(shù)，因此返回函數(shù)結(jié)果與之相反，為FALSE。二、TRUE、FALSE函數(shù)TRUE、FALSE函數(shù)用來(lái)返回參數(shù)的邏輯值，由于可以直接在單元格

4、或公式中鍵入值TRUE或者FALSE。因此這兩個(gè)函數(shù)通?？梢圆皇褂?。三、IF函數(shù)（一）IF函數(shù)說(shuō)明IF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測(cè)試的真假值返回不同的結(jié)果，因此If函數(shù)也稱(chēng)之為條件函數(shù)。它的應(yīng)用很廣泛，可以使用函數(shù) IF 對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)。它的語(yǔ)法為IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示計(jì)算結(jié)果為 TRUE 或 FALSE 的任意值或表達(dá)式。本參數(shù)可使用任何比較運(yùn)算符。Value_if_true顯示在logical_test 為 TRUE 時(shí)返回的值，Value_if_true 也可以是其他公

5、式。Value_if_false logical_test 為 FALSE 時(shí)返回的值。Value_if_false 也可以是其他公式。簡(jiǎn)言之，如果第一個(gè)參數(shù)logical_test返回的結(jié)果為真的話，則執(zhí)行第二個(gè)參數(shù)Value_if_true的結(jié)果，否則執(zhí)行第三個(gè)參數(shù)Value_if_false的結(jié)果。IF函數(shù)可以嵌套七層，用 value_if_false 及 value_if_true 參數(shù)可以構(gòu)造復(fù)雜的檢測(cè)條件。Excel 還提供了可根據(jù)某一條件來(lái)分析數(shù)據(jù)的其他函數(shù)。例如，如果要計(jì)算單元格區(qū)域中某個(gè)文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用 COUNTIF 工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一

6、文本串或數(shù)字求和，則可使用 SUMIF 工作表函數(shù)。（二）IF函數(shù)應(yīng)用1、 輸出帶有公式的空白表單 圖5 人事分析表1以圖中所示的人事?tīng)顩r分析表為例，由于各部門(mén)關(guān)于人員的組成情況的數(shù)據(jù)尚未填寫(xiě)，在總計(jì)欄（以單元格G5為例）公式為：=SUM(C5:F5)我們看到計(jì)算為0的結(jié)果。如果這樣的表格打印出來(lái)就頁(yè)面的美觀來(lái)看顯示是不令人滿意的。是否有辦法去掉總計(jì)欄中的0呢？你可能會(huì)說(shuō)，不寫(xiě)公式不就行了。當(dāng)然這是一個(gè)辦法，但是，如果我們利用了IF函數(shù)的話，也可以在寫(xiě)公式的情況下，同樣不顯示這些0。如何實(shí)現(xiàn)呢？只需將總計(jì)欄中的公式（僅以單元格G5為例）改寫(xiě)成：=IF(SUM(C5:F5),SUM(C5:F5)

7、,)通俗的解釋就是：如果SUM(C5:F5)不等于零，則在單元格中顯示SUM(C5:F5)的結(jié)果，否則顯示字符串。幾點(diǎn)說(shuō)明：（1） SUM(C5:F5)不等于零的正規(guī)寫(xiě)法是SUM(C5:F5)0，在EXCEL中可以省略0；（2） 表示字符串的內(nèi)容為空，因此執(zhí)行的結(jié)果是在單元格中不顯示任何字符。 圖42、 不同的條件返回不同的結(jié)果如果對(duì)上述例子有了很好的理解后，我們就很容易將IF函數(shù)應(yīng)用到更廣泛的領(lǐng)域。比如，在成績(jī)表中根據(jù)不同的成績(jī)區(qū)分合格與不合格?，F(xiàn)在我們就以某班級(jí)的英語(yǔ)成績(jī)?yōu)槔唧w說(shuō)明用法。 圖6某班級(jí)的成績(jī)?nèi)鐖D6所示，為了做出最終的綜合評(píng)定，我們?cè)O(shè)定按照平均分判斷該學(xué)生成績(jī)是否合格的規(guī)則。

8、如果各科平均分超過(guò)60分則認(rèn)為是合格的，否則記作不合格。根據(jù)這一規(guī)則，我們?cè)诰C合評(píng)定中寫(xiě)公式（以單元格B12為例）：=IF(B1160,合格,不合格)語(yǔ)法解釋為，如果單元格B11的值大于60，則執(zhí)行第二個(gè)參數(shù)即在單元格B12中顯示合格字樣，否則執(zhí)行第三個(gè)參數(shù)即在單元格B12中顯示不合格字樣。在綜合評(píng)定欄中可以看到由于C列的同學(xué)各科平均分為54分，綜合評(píng)定為不合格。其余均為合格。3、 多層嵌套函數(shù)的應(yīng)用在上述的例子中，我們只是將成績(jī)簡(jiǎn)單區(qū)分為合格與不合格，在實(shí)際應(yīng)用中，成績(jī)通常是有多個(gè)等級(jí)的，比如優(yōu)、良、中、及格、不及格等。有辦法一次性區(qū)分嗎？可以使用多層嵌套的辦法來(lái)實(shí)現(xiàn)。仍以上例為例，我們?cè)O(shè)定

9、綜合評(píng)定的規(guī)則為當(dāng)各科平均分超過(guò)90時(shí)，評(píng)定為優(yōu)秀。如圖7所示。 圖7說(shuō)明：為了解釋起來(lái)比較方便，我們?cè)谶@里僅做兩重嵌套的示例，您可以按照實(shí)際情況進(jìn)行更多重的嵌套，但請(qǐng)注意Excel的IF函數(shù)最多允許七重嵌套。根據(jù)這一規(guī)則，我們?cè)诰C合評(píng)定中寫(xiě)公式（以單元格F12為例）：=IF(F1160,IF(AND(F1190),優(yōu)秀,合格),不合格)語(yǔ)法解釋為，如果單元格F11的值大于60，則執(zhí)行第二個(gè)參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格F11的值是否大于90（為了讓大家體會(huì)一下AND函數(shù)的應(yīng)用，寫(xiě)成AND(F1190)，實(shí)際上可以僅寫(xiě)F1190），如果滿足在單元格F12中顯示優(yōu)秀字樣，不滿足顯示合格字

10、樣，如果F11的值以上條件都不滿足，則執(zhí)行第三個(gè)參數(shù)即在單元格F12中顯示不合格字樣。在綜合評(píng)定欄中可以看到由于F列的同學(xué)各科平均分為92分，綜合評(píng)定為優(yōu)秀。（三）根據(jù)條件計(jì)算值在了解了IF函數(shù)的使用方法后，我們?cè)賮?lái)看看與之類(lèi)似的Excel提供的可根據(jù)某一條件來(lái)分析數(shù)據(jù)的其他函數(shù)。例如，如果要計(jì)算單元格區(qū)域中某個(gè)文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用 COUNTIF 工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用 SUMIF 工作表函數(shù)。關(guān)于SUMIF函數(shù)在數(shù)學(xué)與三角函數(shù)中以做了較為詳細(xì)的介紹。這里重點(diǎn)介紹COUNTIF的應(yīng)用。COUNTIF可以用來(lái)計(jì)算給定區(qū)域內(nèi)滿足特定條件的單

11、元格的數(shù)目。比如在成績(jī)表中計(jì)算每位學(xué)生取得優(yōu)秀成績(jī)的課程數(shù)。在工資表中求出所有基本工資在2000元以上的員工數(shù)。語(yǔ)法形式為COUNTIF(range,criteria)。其中Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 32、32、32、apples。1、成績(jī)表這里仍以上述成績(jī)表的例子說(shuō)明一些應(yīng)用方法。我們需要計(jì)算的是：每位學(xué)生取得優(yōu)秀成績(jī)的課程數(shù)。規(guī)則為成績(jī)大于90分記做優(yōu)秀。如圖8所示 圖8根據(jù)這一規(guī)則，我們?cè)趦?yōu)秀門(mén)數(shù)中寫(xiě)公式（以單元格B13為例）：=COUNTIF(B4:B

12、10,90)語(yǔ)法解釋為，計(jì)算B4到B10這個(gè)范圍，即jarry的各科成績(jī)中有多少個(gè)數(shù)值大于90的單元格。在優(yōu)秀門(mén)數(shù)欄中可以看到j(luò)arry的優(yōu)秀門(mén)數(shù)為兩門(mén)。其他人也可以依次看到。2、 銷(xiāo)售業(yè)績(jī)表銷(xiāo)售業(yè)績(jī)表可能是綜合運(yùn)用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望計(jì)算銷(xiāo)售人員的訂單數(shù)，然后匯總每個(gè)銷(xiāo)售人員的銷(xiāo)售額，并且根據(jù)總發(fā)貨量決定每次銷(xiāo)售應(yīng)獲得的獎(jiǎng)金。原始數(shù)據(jù)表如圖9所示（原始數(shù)據(jù)是以流水單形式列出的，即按訂單號(hào)排列） 圖9 原始數(shù)據(jù)表按銷(xiāo)售人員匯總表如圖10所示 圖10 銷(xiāo)售人員匯總表如圖10所示的表完全是利用函數(shù)計(jì)算的方法自動(dòng)匯總的數(shù)據(jù)。首先建立一個(gè)按照銷(xiāo)售人員匯總的表單

13、樣式，如圖所示。然后分別計(jì)算訂單數(shù)、訂單總額、銷(xiāo)售獎(jiǎng)金。（1） 訂單數(shù) -用COUNTIF計(jì)算銷(xiāo)售人員的訂單數(shù)。以銷(xiāo)售人員ANNIE的訂單數(shù)公式為例。公式：=COUNTIF($C$2:$C$13,A17)語(yǔ)法解釋為計(jì)算單元格A17(即銷(xiāo)售人員ANNIE)在銷(xiāo)售人員清單$C$2:$C$13的范圍內(nèi)（即圖9所示的原始數(shù)據(jù)表）出現(xiàn)的次數(shù)。這個(gè)出現(xiàn)的次數(shù)即可認(rèn)為是該銷(xiāo)售人員ANNIE的訂單數(shù)。（2） 訂單總額-用SUMIF匯總每個(gè)銷(xiāo)售人員的銷(xiāo)售額。以銷(xiāo)售人員ANNIE的訂單總額公式為例。公式：=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在銷(xiāo)售人員清單$C$2:$C$13中檢

14、查單元格A17 中的文本（即銷(xiāo)售人員ANNIE），然后計(jì)算訂單金額列（$B$2:$B$13）中相應(yīng)量的和。這個(gè)相應(yīng)量的和就是銷(xiāo)售人員ANNIE的訂單總額。（3） 銷(xiāo)售獎(jiǎng)金-用IF根據(jù)訂單總額決定每次銷(xiāo)售應(yīng)獲得的獎(jiǎng)金。假定公司的銷(xiāo)售獎(jiǎng)金規(guī)則為當(dāng)訂單總額超過(guò)5萬(wàn)元時(shí)，獎(jiǎng)勵(lì)幅度為百分之十五，否則為百分之十。根據(jù)這一規(guī)則仍以銷(xiāo)售人員ANNIE為例說(shuō)明。公式為：=IF(C1750000,10%,15%)*C17如果訂單總額小于 50000則獎(jiǎng)金為 10%；如果訂單總額大于等于 50000，則獎(jiǎng)金為 15%。至此，我們已完全了解了EXCEL函數(shù)的邏輯函數(shù)，相信大家在實(shí)際工作中會(huì)想出更多更有用的運(yùn)用。例如在

15、A1單元格中有一個(gè)18位的身份證號(hào)，如果在A2單元格中輸入“=MID（A1，7，6）”就能把其中的出生年月提取出來(lái)。請(qǐng)以此為例講下這個(gè)函數(shù)的意思與用法。MID是從第幾位到第幾位取值的問(wèn)題，我建議用right函數(shù)，=IF(right(E3,1)=1,0,E3),而且取出來(lái)的是文本格式，因此，等號(hào)后面的數(shù)值需用。Excel用Max函數(shù)和IF函數(shù)實(shí)現(xiàn)求其他數(shù)據(jù)表的最大值 如下圖：上圖，為Sheet1表，僅有兩列，分別為“班級(jí)”和“最高分”，大家仔細(xì)看，分明就是求對(duì)應(yīng)班級(jí)號(hào)的最高分！如下圖，為Sheet2表！也僅有兩列，一為班級(jí)，二為成績(jī)總分，在此，為了方便學(xué)習(xí)，為了更能簡(jiǎn)單直觀的表述問(wèn)題，僅使用測(cè)試

16、數(shù)據(jù)！我們要解決的問(wèn)題就是，根據(jù)表一的班級(jí)號(hào)，通過(guò)表二求得最高分，填充到表一最高分列！要解決這個(gè)問(wèn)題，可以使用簡(jiǎn)單的辦法，那就是Max函數(shù)和IF函數(shù)；下面我們?cè)敿?xì)介紹如何使用這兩個(gè)函數(shù)以求最高分！如下圖，首先選擇表一中的B2單元格，之后在fx函數(shù)輸入框中這樣的代碼：“=MAX(IF(A2=Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100)”輸入代碼之后，切勿急躁，這不等同于以往的操作，輸入好之后，按Ctrl+Shift+Enter組合鍵進(jìn)行確認(rèn)，以保證輸入的代碼為數(shù)組函數(shù)，而非普通函數(shù)的使用！通過(guò)以上的組合鍵確認(rèn)之后，代碼的兩端自動(dòng)加上大括號(hào)，注意哦，大括號(hào)是自動(dòng)加入

17、的，而不是手工敲打鍵盤(pán)輸入的！B2單元格得出數(shù)據(jù)之后，直接使用填充功能進(jìn)行填充即可，其他班級(jí)的最高分自然會(huì)填充上！上圖，為最終結(jié)果！下面介紹下代碼：=MAX(IF(A2=Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100)A2=Sheet2!$A$2:$A$100代表檢測(cè)表一A2單元格的值，即1班的班級(jí)名稱(chēng)，其比較范圍是表二的A2單元格到A100單元格，在此，其范圍可根據(jù)需要修改，一般，數(shù)據(jù)范圍大于預(yù)定范圍即可！Sheet2!$B$2:$B$100代表檢測(cè)表二成績(jī)總分的數(shù)據(jù)區(qū)域，即B2單元格到B100單元格的數(shù)據(jù)，在此，其范圍可根據(jù)需要修改，一般，數(shù)據(jù)范圍大于預(yù)定范圍即

18、可！上述兩個(gè)代碼，綜合IF而言，指的就是根據(jù)表一特定單元格的班級(jí)名稱(chēng)，在表二設(shè)定數(shù)據(jù)區(qū)域中的查找該班級(jí)的成績(jī)，而其他班級(jí)的成績(jī)則不作為參考對(duì)象！綜合外圍的MAX函數(shù)，即是求該班級(jí)的最高分！倘若我們要查找的是最低分，可將MAX函數(shù)改為MIX函數(shù)即可！如：=MIX(IF(A2=Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100)倘若我們查找的不是某班級(jí)的最高分，而是所有班級(jí)的最高分，那么，只需將IF函數(shù)名稱(chēng)及其對(duì)應(yīng)的大括號(hào)對(duì)去掉便可！如：=MAX(A2=Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100)注意哦，如果是求最小值的話，倘若表二中有個(gè)別數(shù)

19、據(jù)為空的話，其最小值當(dāng)0計(jì)算如果在公式中運(yùn)算使用不等號(hào),輸入.例如,如果單元格A1值不等于10,則B1單元格值等于20,否則等于0,在B1中輸入:=IF(A110,20,0)不超過(guò)“第七關(guān)”是可用if嵌套的，以三關(guān)為例： =IF(OR(B51=1,2),第一關(guān),IF(OR(B51=3,4),第二關(guān),IF(OR(B51=5,6),第三關(guān)) 如果關(guān)數(shù)超過(guò)7，可考慮用lookup函數(shù)。例：=lookup(b51,2,10,18,26,34,42,50,58,第一關(guān),第二關(guān),第三關(guān),第四關(guān),第五關(guān),第六關(guān),第七關(guān),第八關(guān))求助如何在一個(gè)IF中實(shí)現(xiàn)多個(gè)條件下求值?各位高手:在下想實(shí)現(xiàn)在一個(gè)IF中實(shí)現(xiàn)多個(gè)條件下求值?如:圖 我想在A2中實(shí)現(xiàn)=IF(A1=1或B1=2或C1=3或D1=4或E1=5或F1=6或G1=7或H1=8或I1=9或J1=10.,謝謝,非常感謝) =IF(A1=1和B1=2和C1=3和D1=4和E1=5和F1=6和G1=7和H1=8和I1=9和J1=10.,謝謝,非常感謝)一般用多個(gè)IF可實(shí)現(xiàn),A2=IF(A1=1,非常感謝,IF(B1=2,非常感謝,IF(C1=3,非常感