EXCEL中IF函數的運用

夏偉峰

摘 要 EXCEL系列函數中IF是一種功能強大的函數，其使用范圍也非常的廣泛。本文不僅將IF函數七層嵌套之內的函數方法進行了詳細的介紹，還將IF函數七層嵌套之外的IF語句，放在另外的單元格內來處理的方法和自定義函數的方法來解決任意多分支的計算問題。

關鍵詞 EXCEL IF函數 IF函數嵌套 IF函數七層外嵌套

中圖分類號：G714 文獻標識碼：A 文章編號：1002-7661（2014）03-0021-02

IF函數是EXCEL中最常見、使用最為廣泛的函數之一，執行真假值判斷，根據邏輯計算的真假值，返回不同結果。可以使用函數 IF 對數值和公式進行條件檢測，用好IF函數可以幫助我們完成很多功能。

一、IF函數的常規應用

例子：作為教師對個班學生的考試成績分析和統計，這需要一個很大的工作量，那么能不能使用IF函數進行自動的計算呢？

下圖數據在d 列顯示如下結果：如果成績大于等于60則顯示合格，否則顯示不合格。那么在d2單元格輸人以下公式：

D2=IF（C2>60，“合格”，“不合格”）

D3=IF（C3>60，“合格”，“不合格”）

然后向下自動填充，生成數據，如下圖d列效果：

這里需要注意的是if函數必須的條件：每一個if函數必須使用英文的括號括起來，如IF（C2>60，“合格”，“不合格”）；括號內為三個數據，第一數據為滿足第一個數據后返回的結果，通常使用英文的引號括起來，如“及格”，第三個數據是不滿足第一個數據時需要返回的結果，也用英文的引號括起來，如“不及格”。

經常出現的錯誤：其中的符號如逗號和引號皆為英文，即所謂的半角：f的右括號放在了條件的后面，這是在多個條件使用if函數進行嵌套時非常容易犯的錯誤。

二、IF函數的嵌套應用

嵌套的含義：C語言中函數的定義都是相互平行、相互獨立的，也就是說在函數定義時，函數體內不能包含另一個函數的定義，即函數不能嵌套定義，但可以嵌套調用。嵌套函數，就是指在某些情況下，您可能需要將某函數作為另一函數的參數使用，這一函數就是嵌套函數。例如公式如果使用了嵌套的 AVERAGE 函數，并將結果與 50 相比較。這個公式的含義是：如果單元格F2到F5的平均值大于50，則求F2到F5的和，否則顯示數值0。又如，在一個程序中，主函數調用了sum函數，而在sum函數中又調用了mul函數。在一個函數被調用的過程中又調用另一個函數，這就是函數的嵌套調用。如果是函數本身嵌套調用函數本身，那就是函數遞歸調用了。

對學生的成績單只簡單地分為“及格”和“不及格”，顯然太過于粗略，能不能再進一步分析呢？

例子：下圖數據，在d列顯示如下結果：如果成績大于等于90則顯示優秀，如果大于等于80而小于90則顯示良好，如果大于等于60而小于80則顯示合格，如果小于60則顯示不合格。

這是經典的if嵌套應用例子，需要我們使用if函數的嵌套。if嵌套書寫前，首先需要理解要求，并將要求數學化，也就是使用數學的模式表達出來，if函數多重嵌套一般情況下我們可以將它看作分段函數，那么問題就很容易解決了。例子可以在d2單元格使用如下代碼：

D2=IF（C2>=90，“優秀”，IF（C2>=80，“良好”，IF（C2>=60，“及格”，“不及格”）））

D3=IF（C3>=90，“優秀”，IF（C3>=80，“良好”，IF（C3>=60，“及格”，“不及格”）））

然后向下自動填充，生成數據，如下圖d列效果：

需要注意的是IF嵌套函數書寫，我們一般把它分解成幾段IF常規函數。如【D2=IF（C2>=90，“優秀”，IF（C2>=80，“良好”，IF（C2>=60，“及格”，“不及格”）））】，它表示，當分數低于60時，顯示為不合格，這時在“不及格”逗號的左側默認就是大于60的情況，那么根據題意，只需再滿足低于80即可顯示良好，于是我們將最簡單的IF函數的第三個數據變成了一個IF函數，依次類推，每一次可以將一個IF函數作為每一個基木函數的第三個數據，從而形成多種嵌套。

另外，在輸人公式的時候要注意后括號要和前括號相對應，有幾前括號，后面就要輸人幾個后括號。

三、IF函數的高級應用

IF函數除了可以引用單元格的數據之外，還可以引用函數值或者其他表格甚至是文件的數據。Excel中if函數經常和其他函數組合使用即為IF函數的高級應用，可將if函數與and， min， average等函數同時進行運算。

例1：與and進行函數運算

下圖數據在f列顯示如下結果：如果成績1、成績2、成績3都大于等于90則顯示優秀，否則顯示空格。

上述例子是一個典型的if函數使用，滿足條件則顯示一個結果，不滿足顯示另一個結果，但是在輸人測試條件的時候，不再是一個簡單的條件，它需要對3個數據進行條件測試，在這里我們就可以確定主函數是if函數，另外測試條件一也需要一個函數。

方法一：測試條件選擇邏輯函數中的and函數。and函數語法：

And（logical1，logical2……）

and函數功能：所有條件都為真值，結果為真，否則結果為假。當3個成績都滿足大于等于90，則顯示結果1“優秀”，有1個成績不滿足則顯示結果2“空格”。代碼如下：

F2=IF（AND（C2>=90，D2>=90，E2>=90），“優秀”， “”）

F3=IF（AND（C3>=90，D3>=90，E3>=90），“優秀”， “”）

然后向下自動填充，生成數據，如下圖F列效果：endprint

其中的空格也是文本數據，用雙引號括起來。

方法二：測試條件選擇常用函數中的min函數。min函數語法：

MIN（NUMBER1，NUMBER2……）

由上題可知，將公式寫為：F2=IF（MIN（C2：E2）>=90，“優秀”，“”）F3=IF（MIN（C3：E3）>=90，“優秀”，“”）

然后向下自動填充，生成數據，如下圖F列效果可得：

min函數功能：顯示所有區域中最小的數值。當3個成績中最低成

績大于等于90，則滿足條件所有成績大于等于90。

例2：與average進行運算

下圖數據中，如果超過平均值的顯示合格，達不到平均值的顯示不合格。

函數為：F2=IF（C2>AVERAGE（C2：E2），“合格”，“不合格”）

F3=IF（C3>AVERAGE（C3：E3），“合格”，“不合格”）

然后向下自動填充，生成數據，如下圖F列效果可得：

這個函數是這樣理解的，當數據c2大于c2到e2所有數據的平均數時，返回合格，否則返回不合格。其中average（ ）是求平均數函數。

四、超過七層的多條件判斷

函數嵌套不能超過7層，不僅是針對IF函數，而是對所有的函數嵌套都要受到這個限制。解決這個問題的方法，不僅適用于IF函數，也使用于其他函數。解決超過7層的判斷可有2種方法：一種是通過“+”或“&”連接判斷條件，另一種方法是通過定義名稱的方法。其實利用VLOOKUP函數或者其他函數可以更簡便些，但利用IF函數設置的公式卻更容易理解。

方法1：利用“+”或“&”的方法

共有幾個判斷條件則分為幾個獨立的部分。每部分都要判斷是否成立，如果成立就進行相應的計算，如果不成立就返回FALSE值。因為這些區間沒有重復的，所以對于給定的數值只能有一個IF語句是成立的，這個成立的IF語句進行運算并返回值。不成立的IF語句的結果為FALSE值，不影響最后結果。如果判斷返回的是文本類型內容，這種情況需要把“+”號改成用“&”連接。使用&連接多個IF函數突破IF函數的7層嵌套的限制，但前提是包含公式的單元格長度不可以超過1024個宇符如果超過1024個宇符，可以通過定義名稱來減少公式的長度，但名稱的長度最多只可以包含255個宇符；如果使用了自定義名稱后公式長度仍然超過1024個宇符，可以將七層之外的IF語句，放在另外的單元格內來處理。公式中的每個IF是個獨立的條件，不是嵌套，所以書寫時特別注意條件的寫法，一定要把每個區間的條件寫完整。

另外，if函數除了遵守一般函數的通用規則以外，還有其特有的注意事項。首先括號必須成對出現，前后對應。其次，if函數有N個條件則有N+1個結果，即若結果只有3種情況的，那么條件只要2個就夠了。再次，多個if嵌套時，盡量使用同一種邏輯運算符。即：統一使用大于號或者統一使用小于號。避免出現不必要的錯誤。

參考文獻：

[1]徐希.計算機應用基礎之函數在EXCEL中的運算[M].北京：高等教育出版社， 2010.

[2]雷強.巧用Excel函數[J]. 咸寧學院報， 2009， （2）.

[3]王曉斌.試論Excel的IF函數的規劃求解在經濟管理中的應用[J].；科技經濟市場；2011年08期.

（責任編輯 劉凌芝）endprint

其中的空格也是文本數據，用雙引號括起來。

方法二：測試條件選擇常用函數中的min函數。min函數語法：

MIN（NUMBER1，NUMBER2……）

由上題可知，將公式寫為：F2=IF（MIN（C2：E2）>=90，“優秀”，“”）F3=IF（MIN（C3：E3）>=90，“優秀”，“”）

然后向下自動填充，生成數據，如下圖F列效果可得：

min函數功能：顯示所有區域中最小的數值。當3個成績中最低成

績大于等于90，則滿足條件所有成績大于等于90。

例2：與average進行運算

下圖數據中，如果超過平均值的顯示合格，達不到平均值的顯示不合格。

函數為：F2=IF（C2>AVERAGE（C2：E2），“合格”，“不合格”）

F3=IF（C3>AVERAGE（C3：E3），“合格”，“不合格”）

然后向下自動填充，生成數據，如下圖F列效果可得：

這個函數是這樣理解的，當數據c2大于c2到e2所有數據的平均數時，返回合格，否則返回不合格。其中average（ ）是求平均數函數。

四、超過七層的多條件判斷

函數嵌套不能超過7層，不僅是針對IF函數，而是對所有的函數嵌套都要受到這個限制。解決這個問題的方法，不僅適用于IF函數，也使用于其他函數。解決超過7層的判斷可有2種方法：一種是通過“+”或“&”連接判斷條件，另一種方法是通過定義名稱的方法。其實利用VLOOKUP函數或者其他函數可以更簡便些，但利用IF函數設置的公式卻更容易理解。

方法1：利用“+”或“&”的方法

共有幾個判斷條件則分為幾個獨立的部分。每部分都要判斷是否成立，如果成立就進行相應的計算，如果不成立就返回FALSE值。因為這些區間沒有重復的，所以對于給定的數值只能有一個IF語句是成立的，這個成立的IF語句進行運算并返回值。不成立的IF語句的結果為FALSE值，不影響最后結果。如果判斷返回的是文本類型內容，這種情況需要把“+”號改成用“&”連接。使用&連接多個IF函數突破IF函數的7層嵌套的限制，但前提是包含公式的單元格長度不可以超過1024個宇符如果超過1024個宇符，可以通過定義名稱來減少公式的長度，但名稱的長度最多只可以包含255個宇符；如果使用了自定義名稱后公式長度仍然超過1024個宇符，可以將七層之外的IF語句，放在另外的單元格內來處理。公式中的每個IF是個獨立的條件，不是嵌套，所以書寫時特別注意條件的寫法，一定要把每個區間的條件寫完整。

另外，if函數除了遵守一般函數的通用規則以外，還有其特有的注意事項。首先括號必須成對出現，前后對應。其次，if函數有N個條件則有N+1個結果，即若結果只有3種情況的，那么條件只要2個就夠了。再次，多個if嵌套時，盡量使用同一種邏輯運算符。即：統一使用大于號或者統一使用小于號。避免出現不必要的錯誤。

參考文獻：

[1]徐希.計算機應用基礎之函數在EXCEL中的運算[M].北京：高等教育出版社， 2010.

[2]雷強.巧用Excel函數[J]. 咸寧學院報， 2009， （2）.

[3]王曉斌.試論Excel的IF函數的規劃求解在經濟管理中的應用[J].；科技經濟市場；2011年08期.

（責任編輯 劉凌芝）endprint

其中的空格也是文本數據，用雙引號括起來。

方法二：測試條件選擇常用函數中的min函數。min函數語法：

MIN（NUMBER1，NUMBER2……）

由上題可知，將公式寫為：F2=IF（MIN（C2：E2）>=90，“優秀”，“”）F3=IF（MIN（C3：E3）>=90，“優秀”，“”）

然后向下自動填充，生成數據，如下圖F列效果可得：

min函數功能：顯示所有區域中最小的數值。當3個成績中最低成

績大于等于90，則滿足條件所有成績大于等于90。

例2：與average進行運算

下圖數據中，如果超過平均值的顯示合格，達不到平均值的顯示不合格。

函數為：F2=IF（C2>AVERAGE（C2：E2），“合格”，“不合格”）

F3=IF（C3>AVERAGE（C3：E3），“合格”，“不合格”）

然后向下自動填充，生成數據，如下圖F列效果可得：

這個函數是這樣理解的，當數據c2大于c2到e2所有數據的平均數時，返回合格，否則返回不合格。其中average（ ）是求平均數函數。

四、超過七層的多條件判斷

函數嵌套不能超過7層，不僅是針對IF函數，而是對所有的函數嵌套都要受到這個限制。解決這個問題的方法，不僅適用于IF函數，也使用于其他函數。解決超過7層的判斷可有2種方法：一種是通過“+”或“&”連接判斷條件，另一種方法是通過定義名稱的方法。其實利用VLOOKUP函數或者其他函數可以更簡便些，但利用IF函數設置的公式卻更容易理解。

方法1：利用“+”或“&”的方法

共有幾個判斷條件則分為幾個獨立的部分。每部分都要判斷是否成立，如果成立就進行相應的計算，如果不成立就返回FALSE值。因為這些區間沒有重復的，所以對于給定的數值只能有一個IF語句是成立的，這個成立的IF語句進行運算并返回值。不成立的IF語句的結果為FALSE值，不影響最后結果。如果判斷返回的是文本類型內容，這種情況需要把“+”號改成用“&”連接。使用&連接多個IF函數突破IF函數的7層嵌套的限制，但前提是包含公式的單元格長度不可以超過1024個宇符如果超過1024個宇符，可以通過定義名稱來減少公式的長度，但名稱的長度最多只可以包含255個宇符；如果使用了自定義名稱后公式長度仍然超過1024個宇符，可以將七層之外的IF語句，放在另外的單元格內來處理。公式中的每個IF是個獨立的條件，不是嵌套，所以書寫時特別注意條件的寫法，一定要把每個區間的條件寫完整。

另外，if函數除了遵守一般函數的通用規則以外，還有其特有的注意事項。首先括號必須成對出現，前后對應。其次，if函數有N個條件則有N+1個結果，即若結果只有3種情況的，那么條件只要2個就夠了。再次，多個if嵌套時，盡量使用同一種邏輯運算符。即：統一使用大于號或者統一使用小于號。避免出現不必要的錯誤。

參考文獻：

[1]徐希.計算機應用基礎之函數在EXCEL中的運算[M].北京：高等教育出版社， 2010.

[2]雷強.巧用Excel函數[J]. 咸寧學院報， 2009， （2）.

[3]王曉斌.試論Excel的IF函數的規劃求解在經濟管理中的應用[J].；科技經濟市場；2011年08期.

（責任編輯 劉凌芝）endprint