今天用Excel的if函數,要用到嵌套,就在網上查到了這篇文章,比較全面。
編者語: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, ...),其中Logical1, logical2, ... 表示待檢測的 1 到 30 個條件值,各條件值可能為TRUE,可能為 FALSE。 參數必須是邏輯值,或者包含邏輯值的數組或引用。舉例說明:
1、 在B2儲存格中輸入數字50,在C2中寫公式=AND(B2>30,B2<60)。由於B2等於50的確大於30、小於60。所以兩個條件值(logical)均為真,則返回結果為TRUE。
圖1 AND函數樣本1
2、 如果 B1-B3 儲存格中的值為 TRUE、FALSE、TRUE,顯然三個參數並不都為真,所以 在B4儲存格中的公式=AND(B1:B3) 等於 FALSE
圖2 AND函數樣本2
(二)OR函數
OR
函數指在其參數組中,任何一個參數邏輯值為 TRUE,即返回
TRUE。它與AND函數的區別在於,AND函數要求所有函數邏輯值均為真,結果方為真。而OR函數僅需其中任何一個為真即可為真。比如,上面的樣本2,
如果在B4儲存格中的公式寫為=OR(B1:B3)則結果等於TRUE
圖3 OR函數樣本
Excel函數系列 |
● Excel函數應用之數學和三角函數
● Excel函數應用之函數簡介 |
(三)NOT函數
NOT函數用於對參數值求反。當要確保一個值不等於某一特定值時,可以使用 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 對數值和公式進行條件檢測。
它的文法為IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示計算結果為 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 及 value_if_true 參數可以構造複雜的檢測條件。
Excel 還提供了可根據某一條件來分析資料的其他函數。例如,如果要計算儲存格範圍中某個文本串或數字出現的次數,則可使用 COUNTIF 工作表函數。如果要根據儲存格範圍中的某一文本串或數字求和,則可使用 SUMIF 工作表函數。
(二)IF函數應用
1、 輸出帶有公式的空白表單
圖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) ""表示字串的內容為空白,因此執行的結果是在儲存格中不顯示任何字元。
圖4
2、 不同的條件返回不同的結果
如果對上述例子有了很好的理解後,我們就很容易將IF函數應用到更廣泛的領域。比如,在成績表中根據不同的成績區分合格與不合格。現在我們就以某班級的英語成績為例具體說明用法。
圖6
某班級的成績6所示,為了做出最終的綜合評定,我們設定按照平均分判斷該學產生績是否合格的規則。如果各科平均分超過60分則認為是合格的,否則記作不合格。
根據這一規則,我們在綜合評定中寫公式(以儲存格B12為例):
=IF(B11>60,"合格","不合格")
文法解釋為,如果儲存格B11的值大於60,則執行第二個參數即在儲存格B12中顯示合格字樣,否則執行第三個參數即在儲存格B12中顯示不合格字樣。
在綜合評定欄中可以看到由於C列的同學各科平均分為54分,綜合評定為不合格。其餘均為合格。
3、 多層嵌套函數的應用
在
上述的例子中,我們只是將成績簡單區分為合格與不合格,在實際應用中,成績通常是有多個等級的,比如優、良、中、及格、不及格等。有辦法一次性區分嗎?可
以使用多層嵌套的辦法來實現。仍以上例為例,我們設定綜合評定的規則為當各科平均分超過90時,評定為優秀。7所示。
圖7
說明:為瞭解釋起來比較方便,我們在這裡僅做兩重嵌套的樣本,您可以按照實際情況進行更多重的嵌套,但請注意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所示
圖8
根據這一規則,我們在優秀門數中寫公式(以儲存格B13為例):
=COUNTIF(B4:B10,">90")
文法解釋為,計算B4到B10這個範圍,即jarry的各科成績中有多少個數值大於90的儲存格。
在優秀門數欄中可以看到jarry的優秀門數為兩門。其他人也可以依次看到。
2、 銷售業績表
銷售業績表可能是綜合運用IF、SUMIF、COUNTIF非常典型的樣本。比如,可能希望計算銷售人員的訂單數,然後匯總每個銷售人員的銷售額,並且根據總發貨量決定每次銷售應獲得的獎金。
未經處理資料表9所示(未經處理資料是以流水單形式列出的,即按訂單號排列)
圖9 未經處理資料表
按銷售人員匯總表10所示
圖10 銷售人員匯總表
10所示的表完全是利用Function Compute的方法自動匯總的資料。首先建立一個按照銷售人員匯總的表單樣式,。然後分別計算訂單數、訂單總額、銷售獎金。
(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)中相應量的和。
這個相應量的和就是銷售人員ANNIE的訂單總額。
(3) 銷售獎金--用IF根據訂單總額決定每次銷售應獲得的獎金。
假定公司的銷售獎金規則為當訂單總額超過5萬元時,獎勵幅度為百分之十五,否則為百分之十。根據這一規則仍以銷售人員ANNIE為例說明。公式為:
=IF(C17<50000,10%,15%)*C17
如果訂單總額小於 50000則獎金為 10%;如果訂單總額大於等於 50000,則獎金為 15%。
至此,我們已完全瞭解了EXCEL函數的邏輯函數,相信大家在實際工作中會想出更多更有用的運用。