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 陳述式遵循易懂的邏輯：

如果測驗分數 (在儲存格 D2 中) 大於 89，學生則會得到 A
如果測驗分數大於 79，學生則會得到 B
如果測驗分數大於 69，學生則會得到 C
如果測驗分數大於 59，學生則會得到 D
如果是其他的分數，學生則會得到 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%, 依此類推...

雖然它與先前的 Grades 範例非常相似，但這個公式很好地說明了維持大型 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，首先您需要建立一個參照表：