1、Excel 函數應用之邏輯函數編者語：Excel 是辦公室自動化中非常重要的一款軟件，很多巨型國際企業都是依靠Excel進行數據管理。它不僅僅能夠方便的處理表格和進行圖形分析，其更強大的功能體現在對數據的自動處理和計算，然而很多缺少理工科背景或是對Excel 強大數據處理功能不了解的人卻難以進一步深入。編者以為，對 Excel 函數應用的不了解正是阻擋普通用戶完全掌握Excel的攔路虎，然而目前這一部份內容的教學文章卻又很少見，所以特別組織了這一個Excel函數應用系列， 希望能夠對Excel 進階者有所幫助。 Excel 函數應用系列， 將每周更新，逐步系統的介紹Excel 各類函數及其應用

2、，敬請關注！用來判斷真假值，或者進行復合檢驗的Excel 函數，我們稱為邏輯函數。在Excel 中提供了六種邏輯函數。即AND 、 OR、 NOT、 FALSE、 IF、 TRUE 函數。AND 、 OR、 NOT 函數 這三個函數都用來返回參數邏輯值。詳細介紹見下：(1) AND 函數所有參數的邏輯值為真時返回TRUE ； 只要一個參數的邏輯值為假即返回FALSE 。 簡言之，就是當AND 的參數全部滿足某一條件時，返回結果為TRUE ，否則為FALSE。語法為 AND(logical1,logical2, .) ， 其中 Logical1, logical2, . 表示待檢測的1 到 30

3、 個條件值，各條件值可能為TRUE ，可能為FALSE。 參數必須是邏輯值，或者包含邏輯值的數組或引用。舉例說明：1、 在 B2 單元格中輸入數字50，在C2 中寫公式=AND(B230,B260) 。由于 B2 等于 50 的確大于30、小于60。所以兩個條件值(logical )均為真，則返回結果為TRUE 。圖 1 AND 函數示例12、 如果 B1-B3 單元格中的值為TRUE、 FALSE、 TRUE，顯然三個參數并不都為真，所以 在 B4 單元格中的公式=AND(B1:B3) 等于 FALSE圖 2 AND 函數示例2(2) OR 函數OR 函數指在其參數組中，任何一個參數邏輯值為

4、TRUE ，即返回TRUE 。它與 AND 函數的區別在于，AND 函數要求所有函數邏輯值均為真，結果方為真。而OR 函數僅需其中任何一個為真即可為真。比如，上面的示例2，如果在B4 單元格中的公式寫為=OR(B1:B3) 則結果等于TRUE圖 3 OR 函數示例Excel 函數系列 Excel 函數應用之數學和三角函數 Excel 函數應用之函數簡介(3) NOT 函數NOT 函數用于對參數值求反。當要確保一個值不等于某一特定值時，可以使用NOT 函數。簡言之，就是當參數值為TRUE 時， NOT 函數返回的結果恰與之相反，結果為FALSE.比如 NOT(2+2=4) ，由于 2+2 的結果

5、的確為4，該參數結果為TRUE ，由于是NOT 函數，因此返回函數結果與之相反，為FALSE。2、 TRUE 、 FALSE 函數TRUE 、 FALSE 函數用來返回參數的邏輯值，由于可以直接在單元格或公式中鍵入值TRUE或者FALSE 。因此這兩個函數通?？梢圆皇褂谩?、 IF 函數(1) IF 函數說明IF 函數用于執行真假值判斷后，根據邏輯測試的真假值返回不同的結果，因此If 函數也稱之為條件函數。它的應用很廣泛，可以使用函數IF 對數值和公式進行條件檢測。它的語法為IF(logical_test,value_if_true,value_if_false) 。其中 Logical_te

6、st 表示計算結果為TRUE 或 FALSE 的任意值或表達式。本參數可使用任何比較運算符。Value_if_true 顯示在 logical_test 為 TRUE 時返回的值，Value_if_true 也可以是其他公式。Value_if_false logical_test 為 FALSE 時返回的值。Value_if_false 也可以是其他公式。簡言之，如果第一個參數logical_test 返回的結果為真的話，則執行第二個參數Value_if_true的結果， 否則執行第三個參數Value_if_false 的結果。 IF 函數可以嵌套七層，用 value_if_false及 va

7、lue_if_true 參數可以構造復雜的檢測條件。Excel 還提供了可根據某一條件來分析數據的其他函數。例如，如果要計算單元格區域中某個文本串或數字出現的次數，則可使用COUNTIF 工作表函數。如果要根據單元格區域中的某一文本串或數字求和，則可使用SUMIF 工作表函數。(2) IF 函數應用1、 輸出帶有公式的空白表單圖 5 人事分析表1以圖中所示的人事狀況分析表為例，由于各部門關于人員的組成情況的數據尚未填寫，在總計欄(以單元格G5 為例)公式為：=SUM(C5:F5)我們看到計算為0 的結果。如果這樣的表格打印出來就頁面的美觀來看顯示是不令人滿意的。 是否有辦法去掉總計欄中的0 呢

8、？你可能會說，不寫公式不就行了。當然這是一個辦法，但是，如果我們利用了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) 表示字符串的內容為空，因此執行的結果是在單元格中不顯示任何字符。42、 不同的條件返回不同的結果如果對上述例子有了很好

9、的理解后，我們就很容易將IF 函數應用到更廣泛的領域。比如，在成績表中根據不同的成績區分合格與不合格?，F在我們就以某班級的英語成績為例具體說明用法。某班級的成績如圖6 所示， 為了做出最終的綜合評定，我們設定按照平均分判斷該學生成績是否合格的規則。如果各科平均分超過60 分則認為是合格的，否則記作不合格。根據這一規則，我們在綜合評定中寫公式(以單元格B12 為例) ：=IF(B1160, 合格 ,不合格 )語法解釋為，如果單元格B11 的值大于60，則執行第二個參數即在單元格B12 中顯示合格字樣，否則執行第三個參數即在單元格B12 中顯示不合格字樣。在綜合評定欄中可以看到由于C 列的同學各科

10、平均分為54 分，綜合評定為不合格。其余均為合格。3、 多層嵌套函數的應用在上述的例子中，我們只是將成績簡單區分為合格與不合格，在實際應用中，成績通常是有多個等級的，比如優、良、中、及格、不及格等。有辦法一次性區分嗎？可以使用多層嵌套的辦法來實現。仍以上例為例，我們設定綜合評定的規則為當各科平均分超過90 時，評定為優秀。如圖7 所示。圖7說明： 為了解釋起來比較方便，我們在這里僅做兩重嵌套的示例，您可以按照實際情況進行更多重的嵌套，但請注意Excel 的 IF 函數最多允許七重嵌套。根據這一規則，我們在綜合評定中寫公式(以單元格F12 為例) ：=IF(F1160,IF(AND(F1190)

11、, 優秀 ,合格 ), 不合格 )語法解釋為，如果單元格F11 的值大于60，則執行第二個參數，在這里為嵌套函數，繼續判斷單元格F11 的值是否大于90(為了讓大家體會一下AND 函數的應用，寫成AND(F1190) ，實際上可以僅寫F1190) ，如果滿足在單元格F12 中顯示優秀字樣，不滿足顯示合格字樣，如果F11 的值以上條件都不滿足，則執行第三個參數即在單元格F12 中顯示不合格字樣。在綜合評定欄中可以看到由于F 列的同學各科平均分為92 分，綜合評定為優秀。在了解了IF 函數的使用方法后，我們再來看看與之類似的Excel 提供的可根據某一條件來分析數據的其他函數。例如， 如果要計算單

12、元格區域中某個文本串或數字出現的次數，則可使用COUNTIF 工作表函數。如果要根據單元格區域中的某一文本串或數字求和，則可使用 SUMIF 工作表函數。關于SUMIF 函數在數學與三角函數中以做了較為詳細的介紹。這里重點介紹COUNTIF 的應用。COUNTIF 可以用來計算給定區域內滿足特定條件的單元格的數目。比如在成績表中計算每位學生取得優秀成績的課程數。在工資表中求出所有基本工資在2000 元以上的員工數。語法形式為COUNTIF(range,criteria) 。其中 Range為需要計算其中滿足條件的單元格數目的單元格區域。Criteria 確定哪些單元格將被計算在內的條件，其形式

13、可以為數字、表達式或文本。例如，條件可以表示為32、 32 、 32 、 apples。1、成績表這里仍以上述成績表的例子說明一些應用方法。我們需要計算的是：每位學生取得優秀成績的課程數。規則為成績大于90 分記做優秀。如圖8 所示B13 為例) ：圖8 根據這一規則，我們在優秀門數中寫公式(以單元格=COUNTIF(B4:B10,90)語法解釋為，計算B4 到 B10 這個范圍，即jarry 的各科成績中有多少個數值大于90的單元格。在優秀門數欄中可以看到jarry 的優秀門數為兩門。其他人也可以依次看到。2、 銷售業績表銷售業績表可能是綜合運用IF、 SUMIF 、 COUNTIF 非常典

14、型的示例。比如，可能希望計算銷售人員的訂單數，然后匯總每個銷售人員的銷售額，并且根據總發貨量決定每次銷售應獲得的獎金。原始數據表如圖9 所示(原始數據是以流水單形式列出的，即按訂單號排列)圖 9 原始數據表按銷售人員匯總表如圖10 所示圖 10 銷售人員匯總表如圖 10 所示的表完全是利用函數計算的方法自動匯總的數據。首先建立一個按照銷售人員匯總的表單樣式，如圖所示。然后分別計算訂單數、訂單總額、銷售獎金。（ 1 ） 訂單數-用 COUNTIF 計算銷售人員的訂單數。以銷售人員ANNIE 的訂單數公式為例。公式：=COUNTIF（$C$2:$C$13,A17）語法解釋為計算單元格A17（ 即銷售人員ANNIE） 在 銷售人員清單 $C$2:$C$13 的范圍內 （即圖 9 所示的原始數據表）出現的次數。這個出現的次數即可認為是該銷售人員ANNIE 的訂單數。（ 2） 訂單總額-用 SUMIF 匯總每個銷售人員的銷售額。以銷售人員ANNIE 的訂單總額公式為例。公式：=SUMIF（$C$2:$C$13,A17,$B$2:$B$13）此公式在銷售人員清單$C$2:$C$13 中檢查單元格A17 中的文本（即銷售人員ANNIE ） ，然后計算訂單金額列（$B$2:$B$13）中相應量的和。這個相應量的和就是銷售人員AN