IF 函數 – 巢狀公式及避免易犯的錯誤

IF 函數 – 巢狀公式及避免易犯的錯誤

附註: 我們想要以您的語言,用最快的速度為您提供最新的說明內容。本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。讓這些內容對您有所幫助是我們的目的。希望您能在本頁底部告訴我們這項資訊是否有幫助。此為英文文章出處,以供參考。

IF 函數可讓您測試條件並傳回 True 或 False 的結果,藉以在值與預期值之間進行邏輯比較。

  • =IF(項目為 True,則執行某項目,反之則執行其他項目)

因此,IF 陳述式可以有兩種結果。第一個結果是比較為 True,第二個結果是比較為 False。

IF 陳述式極度強固,並且形成許多試算表模型的基礎,但它們也是許多試算表問題的根本原因。在理想的情況下,IF 陳述式應可套用至最基本的情況,例如男士/女士、是/否/也許 (這些只是其中幾個例子),但有時您可能需評估要求將超過 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 陳述式遵循易懂的邏輯:

  1. 如果測驗分數 (在儲存格 D2 中) 大於 89,學生則會得到 A

  2. 如果測驗分數大於 79,學生則會得到 B

  3. 如果測驗分數大於 69,學生則會得到 C

  4. 如果測驗分數大於 59,學生則會得到 D

  5. 如果是其他的分數,學生則會得到 F

這個特定範例相當安全,因為測驗分數和字母成績之間的相互關聯不太可能會變更,所以這不需要經常維護。不過試想,如果您需要將成績細分為 A+、A 和 A- (依此類推) 呢?現在您四個條件的 IF 陳述式就需要重新撰寫成具有 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)))))

此公式表示 IF(C9 大於 15,000 則傳回 20%, IF(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 的更多詳細資料,請參閱這裡,不過 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 陳述式和括號。

附註: 您必須有 Office 365 訂閱才能使用此功能。 如果您是 Office 365 訂閱者,請確定您有最新版的 Office

試用 Office 365 或最新版本的 Excel

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

相關主題

影片: 進階 IF 函數
IFS 函數 (Office 365、 Excel 2016 及更新版本)
COUNTIF 函數會計算根據單一條件的值
COUNTIFS 函數會計算根據多個值準則
SUMIF 函數會加總根據單一條件的值
SUMIFS 函數將加總根據多個準則的值
AND 函數
OR 函數
VLOOKUP 函數
在 Excel 中的公式概觀
如何避免中斷的公式
偵測公式中的錯誤
邏輯函數
Excel 函數 (依英文字母)
Excel 函數 (依類別)

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×