excel IF函數怎么用

啊南 585閱讀 2020.06.18

【導語】: IF函數在EXCEL中是一個基礎函數,多用于條件判斷,然后根據條件判斷的結果返回對應的內容。IF函數的使用非常廣泛,IF函數的使用方法你都知道嗎?

excel IF 函數使用技巧,IF 函數怎么用

IF 函數允許通過測試某個條件并返回該條件為 True 或 False 的結果,從而對某個值和預期值進行邏輯對比。

=IF(內容為 True,則執行某些操作,否則就執行其他操作)

但如果需要測試多個條件,例如我們假設所有條件都需要為 True 或 False (AND),或只有一個條件需要為 True 或 False (OR),或者如果想要檢查某個條件是否不 (NOT) 符合你的條件,這時該怎么做呢? 這三個函數均可以單獨使用,但它們更常見于與 IF 函數成對使用。

------------------------------------------------------------------------------------

技術細節


將 IF 函數與 AND、OR 和 NOT 配合使用以執行條件是否為 True 或 False 的多項計算。

語法
  
IF(AND()) - IF(AND(logical1, [logical2], ...), value_if_true, [value_if_false]))
  
IF(OR()) - IF(OR(logical1, [logical2], ...), value_if_true, [value_if_false]))
  
IF(NOT()) - IF(NOT(logical1), value_if_true, [value_if_false]))

參數名稱

說明

logical_test(必需)

要測試的條件。

value_if_true(必需)

logical_test 的結果為 TRUE 時你希望返回的值。

value_if_false(可選)

logical_test 的結果為 FALSE 時你希望返回的值。

                      
下面是如何分別構造 AND、OR 和 NOT 函數的概述。 當將它們分別與 IF 語句組合使用時,應按如下方式表達:

AND – =IF(AND(Something is True, Something else is True), Value if True, Value if False)

OR – =IF(OR(Something is True, Something else is True), Value if True, Value if False)

NOT – =IF(NOT(Something is True), Value if True, Value if False)

示例

下面是一些常見的嵌套 IF(AND())、IF(OR()) 和 IF(NOT()) 語句的示例。 AND 和 OR 函數最多可支持 255 個單獨條件,但并不建議使用多個條件,因為構建、測試和維護復雜的嵌套公式是非常難的。 NOT 函數僅采用一個條件。
將 IF 函數與 AND、OR 和 NOT 函數配合使用計算數字值和文本的示例
下面是根據它們的邏輯寫出的公式:

公式

說明

=IF(AND(A2>0,B2<100),TRUE, FALSE)

如果 A2 (25) 大于 0,并且 B2 (75) 小于 100,則返回 TRUE,否則返回 FALSE。 在本例中,兩個條件都為 TRUE,因此返回 TRUE。

=IF(AND(A3="Red",B3="Green"),TRUE,FALSE)

如果 A3(“藍色”)=“紅色”,并且 B3(“綠色”)等于“綠色”,則返回 TRUE,否則返回 FALSE。 在本例中,只有第一個條件為 TRUE,因此返回 FALSE。

=IF(OR(A4>0,B4<50),TRUE, FALSE)

如果 A4 (25) 大于 0,或者 B4 (75) 小于 50,則返回 TRUE,否則返回 FALSE。 在本例中,只有第一個條件為 TRUE,但由于 OR 只要求一個參數為 TRUE,因此公式返回 TRUE。

=IF(OR(A5="Red",B5="Green"),TRUE,FALSE)

如果 A5(“藍色”)=“紅色”,或者 B5(“綠色”)等于“綠色”,則返回 TRUE,否則返回 FALSE。 在本例中,第二個參數為 TRUE,因此公式返回 TRUE。

=IF(NOT(A6>50),TRUE,FALSE)

如果 A6 (25) 不大于 50,則返回 TRUE,否則返回 FALSE。 在本例中,25 不大于 50,因此公式返回 TRUE。

=IF(NOT(A7="Red"),TRUE,FALSE)

如果 A7(“藍色”)不等于“紅色”,則返回 TRUE,否則返回 FALSE。

  
請注意,在所有示例中,輸入各自的條件后都加了右括號。 其余 True/False 參數將作為外部 IF 語句的一部分。 你也可以將“文本”或“數字”值替換為示例中返回的 TRUE/FALSE 值。

以下是使用 AND、OR 和 NOT 函數計算日期的示例。
將 IF 函數與 AND、OR 和 NOT 函數配合使用計算日期的示例

下面是根據它們的邏輯寫出的公式:


公式

說明

=IF(A2>B2,TRUE,FALSE)

如果 A2 大于 B2,則返回 TRUE,否則返回 FALSE。 14/03/12 大于 14/01/01,因此公式返回 TRUE。

=IF(AND(A3>B2,A3<C2),TRUE,FALSE)

如果 A3 大于 B2,并且 A3 小于 C2,則返回 TRUE,否則返回 FALSE。 在本例中,兩個參數均為 TRUE,因此公式返回 TRUE。

=IF(OR(A4>B2,A4<B2+60),TRUE,FALSE)

如果 A4 大于 B2,或 A4 小于 B2 + 60,則返回 TRUE,否則返回 FALSE。 在本例中,第一個參數為 TRUE,但第二個為 FALSE。 由于 OR 僅需要一個參數為 TRUE,因此公式返回 TRUE。 如果使用“公式”選項卡中的公式求值向導,則將看到 Excel 計算公式的方式。

=IF(NOT(A5>B2),TRUE,FALSE)

如果 A5 不大于 B2,則返回 TRUE,否則返回 FALSE。 在本例中,A5 大于 B2,因此公式返回 FALSE。

   公式求值向導示例

將 AND、OR 和 NOT 與條件格式配合使用


你也可以使用 AND、OR 和 NOT 通過公式選項設置“條件格式”條件。 這樣做可以省略 IF 函數,并可以獨立使用 AND、OR 和 NOT。

在“開始”選項卡上,單擊“條件格式”>“新規則”。 接下來,選擇“使用公式確定要設置格式的單元格”選項,輸入公式并應用你選擇的格式。
條件格式 > 編輯規則對話框(顯示公式方法)
使用早期版本的“日期”示例時,公式應如下所示。
將 AND、OR 和 NOT 函數用作條件格式測試的示例


公式

說明

=A2>B2

如果 A2 大于 B2,則設置單元格格式,否則不執行任何操作。

=AND(A3>B2,A3<C2)

如果 A3 大于 B2 且 A3 小于 C2,則設置單元格格式,否則不執行任何操作。

=OR(A4>B2,A4<B2+60)

如果 A4 大于 B2 或 A4 小于 B2 超過 60(天),則設置單元格格式,否則不執行任何操作。

=NOT(A5>B2)

如果 A5 不大于 B2,則設置單元格格式,否則不執行任何操作。 在本例中,A5 大于 B2,因此結果返回 FALSE。 如果打算將公式更改為 =NOT(B2>A5),則會返回 TRUE,并且會設置單元格格式。

excel IF函數怎么用,如何使用IF函數快速標注單元格

IF 函數允許通過測試某個條件并返回 True 或 False 的結果,從而對某個值和預期值進行邏輯比較。

=IF(內容為 True,則執行某些操作,否則就執行其他操作)

因此 IF 語句可能有兩個結果。 第一個結果是比較結果為 True,第二個結果是比較結果為 False。

IF 語句非常強大,其構成了許多電子表格模型的基礎,但也是導致許多電子表格問題的根本原因。 理想情況下,IF 語句應適用于最小條件(例如 Male/Female 和 Yes/No/Maybe),但是對更復雜情況求值時則需要同時嵌套* 3 個以上的 IF 函數。

*“嵌套”是指在一個公式中連接多個函數的做法。

--------------------------------------------------------------------------------------------------------

技術細節


使用邏輯函數 IF 函數時,如果條件為真,該函數將返回一個值;如果條件為假,函數將返回另一個值。

語法

IF(logical_test, value_if_true, [value_if_false])

例如:
  
=IF(A2>B2,"超出預算","正常")
  
=IF(A2=B2,B4-A4,"")
      

參數名稱

說明

logical_test   

(必需)

要測試的條件。

value_if_true   

(必需)

logical_test 的結果為 TRUE 時,您希望返回的值。

value_if_false   

(可選)

logical_test 的結果為 FALSE 時,您希望返回的值。

    
備注

雖然 Excel 允許嵌套最多 64 個不同的 IF 函數,但不建議這樣做。 原因如下。

要正確地構建多個 IF 語句需要花大量心思,并要確保其邏輯在直至結尾的每個條件下都能計算正確。 如果嵌套公式不是 100% 準確,那么計算過程可能花 75% 的時間,而返回結果可能花 25% 的時間,并且結果并不理想。 但是得出這 25% 結果的幾率很小。

多個 IF 語句維護起來非常困難,特別是過一段時間后回頭再看,想要了解當時你(其他人的話更糟糕)想要做什么時。

如果發現 IF 語句似乎在無窮無盡地不斷增加,這時候應放下鼠標,重新思考策略。

我們來了解一下如何使用多個 IF 正確創建一個復雜的嵌套 IF 語句,以及何時應使用 Excel 庫中的其他工具。

示例

以下示例介紹了一個相對標準的嵌套 IF 語句,該語句將學生考試成績轉化為等效字母等級。
復雜的嵌套 IF 語句 - E2 中的公式為 =IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-",IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))
=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

此復雜嵌套 IF 語句遵循一個簡單邏輯:
  
如果 Test Score(單元格 D2)大于 89,則學生獲得 A

如果 Test Score 大于 79,則學生獲得 B

如果 Test Score 大于 69,則學生獲得 C

如果 Test Score 大于 59,則學生獲得 D

否則,學生獲得 F

這個具體示例比較安全,因為考試成績和字母等級之間的相關性不可能改變,所以不需要太多維護。 但想想 - 如果需要在 A+、A 和 A- 等等之間劃分成績應該怎么辦? 現在 IF 語句包含 4 個條件,需要將其重寫為包含 12 個條件! 公式如下所示:

=IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))

該公式仍具有準確的功能并按預期工作,但需要花很長時間編寫并花更長時間進行測試,才能確保該公式可完成所需操作。 另一個明顯的問題是必須手動輸入分數和等效字母等級。 不小心輸錯字的幾率是多少? 想象一下,需要使用更復雜的條件 64 次! 當然這是可能實現的,但你真的想給自己帶來這種麻煩和難以察覺的可能錯誤嗎?

提示: Excel 中的每個函數都需要使用左括號和右括號 ()。 編輯時,Excel 會通過對公式的不同部分著色來幫助你定位。 例如,如果要編輯上面的公式,將光標移過每個右括號“)”時,它的相應左括號會顯示相同顏色。 在復雜嵌套公式中檢查是否擁有足夠的匹配括號時,此方法尤其有用。

更多示例

下面是一個十分常見的示例 - 根據銷售額等級計算銷售傭金
單元格 D9 中的公式為 IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))
=IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))

此公式表示如果 (C9 大于 15,000 則返回 20%,如果 (C9 大于 12,500 則返回 17.5% 等等…

雖然該公式與前面的“成績”示例非常相似,但它很好地說明了維護大型 IF 語句的難度 - 如果組織決定增加新的薪酬等級,甚至改變現有美元或百分比值,那么你需要做些什么? 必須手動完成大量工作!

提示: 為了使長公式更易于閱讀,可在編輯欄中插入換行符。 只需在將文本換到新行前按 Alt+Enter。

下面是一個包含混亂邏輯的傭金方案示例:
D9 中的公式順序顛倒,變為 =IF(C9>5000,10%,IF(C9>7500,12.5%,IF(C9>10000,15%,IF(C9>12500,17.5%,IF(C9>15000,20%,0)))))
發現問題了嗎? 將銷售額比較的順序與上一示例的順序進行比較。 此示例用的是哪種方式? 此示例采用自下而上(從 5,000 美元到 15,000 美元)而不是自上而下的方式。 但為什么說這是個大問題? 因為公式無法通過對任何超過 5,000 美元的值的第一次求值。 假設銷售額為 12,500 美元 - IF 語句將返回 10%,因為該值大于 5,000 美元,所以公式將在此處停止。 此類問題很嚴重,因為在許多情況下,此類錯誤容易被忽視,直到產生負面影響才會被發現。 既然知道復雜嵌套 IF 語句具有嚴重缺陷,你能做些什么? 在大多數情況下,可使用 VLOOKUP 函數,而不是使用 IF 函數構建復雜公式。 若要使用 VLOOKUP,首先需要創建一個引用表:
單元格 D2 中的公式為 =VLOOKUP(C2,C5:D17,2,TRUE)
=VLOOKUP(C2,C5:D17,2,TRUE)

此公式表示在 C5:C17 區域中查找 C2 的值。 如果找到值,則從 D 列的同一行返回相應值。
單元格 C9 中的公式為 =VLOOKUP(B9,B2:C6,2,TRUE)
=VLOOKUP(B9,B2:C6,2,TRUE)

類似地,此公式將在 B2:B22 區域中查找單元格 B9 的值。 如果找到值,則從 C 列的同一行返回相應值。

注意: 這兩個 VLOOKUP 公式在公式末尾使用 TRUE 參數,這表示需要它們查找適當的匹配項。 也就是說,它將匹配查找表中的精確值以及范圍內的任何值。 在這種情況下,查找表需要按升序??排序(從小到大)。

此處更詳細地介紹 VLOOKUP,但這確保比12級復雜的嵌套 IF 語句更簡單! 還有其他一些不太明顯的優點:

VLOOKUP 引用表是開放的,易于查看。

條件更改后,可輕松更新表值,無需更改公式。

如果不希望他人查看或更改引用表,只需將其置于其他工作表。


你知道嗎?

目前 IFS 函數可使用單個函數替代多個嵌套 IF 語句。 因此,對于最初的包含 4 個嵌套 IF 函數的成績示例:

=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

可使用單個 IFS 函數使其變得更簡潔:

=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

IFS 函數十分有用,因為無需擔心所有這些 IF 語句和括號帶來的麻煩。

注意: 僅當具有 Microsoft 365 訂閱時,此功能才可用。 如果你是 Microsoft 365 訂閱者,請確保擁有最新版本的 Office。

如何使用excel快速標注成績及格還是不及格,IF函數怎么用

使用邏輯函數 IF函數時,如果條件為真,該函數將返回一個值;如果條件為假,函數將返回另一個值。

語法

IF(logical_test, value_if_true, [value_if_false])

logical_test:必需,要測試的條件。

value_if_true:必需,logical_test 的結果為 TRUE 時,您希望返回的值。

value_if_false:可選,logical_test 的結果為 FALSE 時,您希望返回的值。

人事招聘時,可以對應聘者發送面試邀請。

已知合格條件是分數大于等于6,那如何將面試結果轉換成文字呢?

具體方法與步驟如下:

將光標放在I3處,點擊插入IF函數。

在測試條件中輸入H3>=6,真值和假值中分別輸入“合格”“不合格”。

此處要注意,輸入的文字需要在英文狀態下的雙引號中。

點擊確定得出結果,將光標放在I3右下角呈十字形下拉填充公式。

就可以批量將面試結果轉換成文字了。