巢狀 IF 與 AND / OR 的陷阱：順序、ARRAYFORMULA 與空白儲存格

為什麼我的成績分級公式，明明每個括號都配對了，93 分卻拿到 D？這篇整理我寫 IF、AND、OR 時踩過的坑。IF 的基本寫法在 IF 函式入門 講過了，這邊直接從會出事的地方開始。

前半段用這份成績，放在 A1:B8，B 欄是分數：

分級的標準是 90 分以上 A、80 分以上 B、70 分以上 C、60 分以上 D，其他 F。

條件順序寫反

巢狀 IF 是由上往下檢查，第一個成立的條件就直接回傳，後面不會再看。所以條件的順序就是邏輯本身。

我把順序寫成由低到高：

來看看結果：

93 分大於等於 60，第一關就成立，直接回傳 D，後面的條件永遠沒機會執行。更好笑的是 59 分，三個條件全部不成立，掉到最後的「A」。這種錯誤不會報錯，公式看起來又很整齊，所以特別難發現。

改法是把最嚴格的條件放最前面：

結果依序是 A、B、C、D、F、A、B。

另一個做法是反過來，由低到高，但條件要跟著換成「小於」：

結果和上面完全一樣。重點是方向要一致，大於配由高到低，小於配由低到高。

邊界值：> 還是 >=

這個題目裡我故意放了 90 和 80 分，就是為了測邊界。如果一不小心把 >= 都寫成 >：

90 分會變成 B、80 分變成 C，其他列維持 A、B、C、D、F 不變。只有剛好踩在門檻上的人會被降一級，很容易漏掉，所以我寫完分級公式，都會特地塞一筆剛好等於門檻的資料去測。

AND 和 OR 放進 ARRAYFORMULA

AND 和 OR 在單一儲存格裡好好的，一包進 ARRAYFORMULA 就會出事。先看資料，這是五張訂單，放在 A1:C6：

我想標出「金額大於等於 3000，而且出貨天數 3 天以內」的急件。平常會寫 =IF(AND(B2>=3000, C2<=3), "急件", "一般")，往下複製五列，結果是急件、一般、一般、急件、一般，沒問題。

但我想要一條公式搞定，不用往下複製，於是包上 ARRAYFORMULA，放在 D2：

結果只有 D2 一格出現「一般」，下面四格都是空的，沒有逐列判斷。AND 的工作是把所有條件收攏成「一個」TRUE 或 FALSE，丟給它一整排的比較結果，它也只會吐回一個答案，那五列不是全部都成立，所以是 FALSE，IF 就只拿到一個值。OR 也是一樣的狀況。

修法是用乘法代替 AND、加法代替 OR。乘法的意思是兩邊都要成立，TRUE 在運算時當成 1、FALSE 當成 0，1 乘 1 才是 1，其他都是 0：

每個比較都要自己用括號包起來。來看看結果，從 D2 溢出到 D6：

IF 看到 0 會當成 FALSE，看到 1 或其他非 0 的數字，就當成 TRUE。

OR 的對應寫法是加號，只要有一個成立，加起來就大於 0。這個留給文末的練習。

如果你的資料不多，我個人其實比較常用最樸素的做法：不用 ARRAYFORMULA，每列各寫一條 IF(AND(...))，往下複製就好。公式短、好讀，要用 ARRAYFORMULA 的理由通常是「新增資料時公式要自動延伸」，這個場合再換成乘號寫法。延伸的細節可以看 ARRAYFORMULA 自動延伸。

文字和數字比較

這個坑比較陰險：比較結果不報錯，只是答案不是你想的。

我拿成績欄的「59 分」，用 LEFT 切出前兩個字當分數：

=LEFT("59分", 2)

切出來是 59，看起來是數字，其實是文字。假設有另一張表，把這個結果放在 B2，再問它及不及格：

結果是「及格」。Sheets 比大小時，文字會被當成比任何數字都大，所以文字的「59」大於等於 60 成立。而且 ="85"=85 會得到 FALSE，文字的 85 和數字的 85 不是同一個東西。

懷疑是這個問題時，先用 =ISTEXT(B2) 看一眼。確認之後把它轉成數字再比：

這次是「不及格」。VALUE 就是把看起來像數字的文字轉成真的數字，英文的 value 就是「值」。

文字之間的比較也有兩個小細節。="VIP"="vip" 會是 TRUE，大小寫不影響；但 ="VIP "="VIP" 是 FALSE，尾巴多一個空格就不一樣了。從別的系統貼來的資料特別容易有，整理方式在 文字清理函式。

空白儲存格會被當成 0

同一份成績表再往下一列，第 9 列的人還沒考，B9 是空的。我寫：

結果是「不及格」。空白在比較時被當成 0，0 小於 60，所以成立。連 =B9=0 都是 TRUE，同時 =B9="" 也是 TRUE，空白儲存格同時等於 0 和空字串。

還沒考試的人被標成不及格，應該不是你要的。解法是先判斷是不是空白：

這次 B9 顯示空白。另外還有 ISBLANK 函式，語意更貼近「這格真的是空的」。差別在於，如果 A1 裡放的是公式 =""，它回傳的是空字串，=A1="" 是 TRUE，ISBLANK(A1) 卻是 FALSE。我自己日常用 ="" 比較多，因為不管是真空白還是公式吐出的空字串，都能一次擋下來。

層數多了怎麼辦：IFS 與 SWITCH

巢狀超過三四層，我就會開始想換寫法。這邊先簡單看一下 IFS 與 SWITCH 的語法。

IFS 把巢狀拆成一組一組的「條件, 值」，語法是 =IFS(條件1, 值1, [條件2, 值2, ...])。成績分級可以寫成：

結果一樣是 A、B、C、D、F、A、B。最後的 TRUE, "F" 是兜底，因為 IFS 如果所有條件都不成立，官方說明寫的是回傳 #N/A。把它拿掉，59 分那一列就會變成 #N/A。順序的問題也還在，IFS 一樣是由上往下，第一個成立的就回傳。

SWITCH 是另一種情況，判斷「某個值等於什麼，就對應什麼」，語法是 =SWITCH(判斷值, 比對值1, 結果1, [比對值2, 結果2, ...], [預設值])。假設 A2 是門市「左營」：

得到「南區」。如果門市換成「竹北」，沒有任何一個比對值符合，就回傳最後的預設值「未分區」。SWITCH 只能比「等於」，要比大於小於，還是得靠 IFS。如果沒有任何符合、又沒給預設值，會得到 #N/A（Excel 一樣），所以我習慣都先補一個預設值。

兩張會員卡的四種狀態，先列真值表

方格子 AND／OR 實作舊文的雙卡折扣，可以用四列測資檢查順序。規則是雙卡 8 折、單卡 9 折、無卡原價，不是把兩個 9 折相乘。

另開空白表，A1:B5 放以下布林值，C2 輸入下式後填到 C5；表中第三欄的預期折扣率請放在 D2:D5，不要放進 C 欄，才不會被公式蓋掉。若換成核取方塊，先維持預設 TRUE／FALSE，不混用文字「有」。

四列結果與上表一致。AND 是兩張都要有，OR 是其中一張有即可；雙卡那列兩個條件其實都成立，所以 AND 分支必須先寫。最後 TRUE,1 明確處理無卡。換活動規則時，先更新四列預期，再改公式並逐列比對。

練習

想看 IFS、SWITCH 在會員分級和代碼對照的實際用法，以及什麼時候該改用對照表，可以接著看 會員分級與代碼對照：IFS、SWITCH 還是查表？。

把成績表的分級公式改寫成 IFS，再故意塞一筆剛好 90 分的資料測看看吧！

我是喜特先生，Mr. Sheet，我們下個教學見！