EXCEL中IF函數(shù)的運(yùn)用

夏偉峰

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

關(guān)鍵詞 EXCEL IF函數(shù) IF函數(shù)嵌套 IF函數(shù)七層外嵌套

中圖分類號：G714 文獻(xiàn)標(biāo)識碼：A 文章編號：1002-7661（2014）03-0021-02

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

一、IF函數(shù)的常規(guī)應(yīng)用

例子：作為教師對個班學(xué)生的考試成績分析和統(tǒng)計(jì)，這需要一個很大的工作量，那么能不能使用IF函數(shù)進(jìn)行自動的計(jì)算呢？

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

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

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

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

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

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

二、IF函數(shù)的嵌套應(yīng)用

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

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

例子：下圖數(shù)據(jù)，在d列顯示如下結(jié)果：如果成績大于等于90則顯示優(yōu)秀，如果大于等于80而小于90則顯示良好，如果大于等于60而小于80則顯示合格，如果小于60則顯示不合格。

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

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

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

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

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

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

三、IF函數(shù)的高級應(yīng)用

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

例1：與and進(jìn)行函數(shù)運(yùn)算

下圖數(shù)據(jù)在f列顯示如下結(jié)果：如果成績1、成績2、成績3都大于等于90則顯示優(yōu)秀，否則顯示空格。

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

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

And（logical1，logical2……）

and函數(shù)功能：所有條件都為真值，結(jié)果為真，否則結(jié)果為假。當(dāng)3個成績都滿足大于等于90，則顯示結(jié)果1“優(yōu)秀”，有1個成績不滿足則顯示結(jié)果2“空格”。代碼如下：

F2=IF（AND（C2>=90，D2>=90，E2>=90），“優(yōu)秀”， “”）

F3=IF（AND（C3>=90，D3>=90，E3>=90），“優(yōu)秀”， “”）

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

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

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

MIN（NUMBER1，NUMBER2……）

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

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

min函數(shù)功能：顯示所有區(qū)域中最小的數(shù)值。當(dāng)3個成績中最低成

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

例2：與average進(jìn)行運(yùn)算

下圖數(shù)據(jù)中，如果超過平均值的顯示合格，達(dá)不到平均值的顯示不合格。

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

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

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

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

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

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

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

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

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

參考文獻(xiàn)：

[1]徐希.計(jì)算機(jī)應(yīng)用基礎(chǔ)之函數(shù)在EXCEL中的運(yùn)算[M].北京：高等教育出版社， 2010.

[2]雷強(qiáng).巧用Excel函數(shù)[J]. 咸寧學(xué)院報， 2009， （2）.

[3]王曉斌.試論Excel的IF函數(shù)的規(guī)劃求解在經(jīng)濟(jì)管理中的應(yīng)用[J].；科技經(jīng)濟(jì)市場；2011年08期.

（責(zé)任編輯 劉凌芝）endprint

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

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

MIN（NUMBER1，NUMBER2……）

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

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

min函數(shù)功能：顯示所有區(qū)域中最小的數(shù)值。當(dāng)3個成績中最低成

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

例2：與average進(jìn)行運(yùn)算

下圖數(shù)據(jù)中，如果超過平均值的顯示合格，達(dá)不到平均值的顯示不合格。

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

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

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

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

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

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

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

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

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

參考文獻(xiàn)：

[1]徐希.計(jì)算機(jī)應(yīng)用基礎(chǔ)之函數(shù)在EXCEL中的運(yùn)算[M].北京：高等教育出版社， 2010.

[2]雷強(qiáng).巧用Excel函數(shù)[J]. 咸寧學(xué)院報， 2009， （2）.

[3]王曉斌.試論Excel的IF函數(shù)的規(guī)劃求解在經(jīng)濟(jì)管理中的應(yīng)用[J].；科技經(jīng)濟(jì)市場；2011年08期.

（責(zé)任編輯 劉凌芝）endprint

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

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

MIN（NUMBER1，NUMBER2……）

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

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

min函數(shù)功能：顯示所有區(qū)域中最小的數(shù)值。當(dāng)3個成績中最低成

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

例2：與average進(jìn)行運(yùn)算

下圖數(shù)據(jù)中，如果超過平均值的顯示合格，達(dá)不到平均值的顯示不合格。

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

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

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

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

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

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

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

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

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

參考文獻(xiàn)：

[1]徐希.計(jì)算機(jī)應(yīng)用基礎(chǔ)之函數(shù)在EXCEL中的運(yùn)算[M].北京：高等教育出版社， 2010.

[2]雷強(qiáng).巧用Excel函數(shù)[J]. 咸寧學(xué)院報， 2009， （2）.

[3]王曉斌.試論Excel的IF函數(shù)的規(guī)劃求解在經(jīng)濟(jì)管理中的應(yīng)用[J].；科技經(jīng)濟(jì)市場；2011年08期.

（責(zé)任編輯 劉凌芝）endprint