會員分級與代碼對照：IFS、SWITCH 還是查表？

會員要分銀卡、金卡、白金，付款方式的代碼要轉成中文，門市代碼要對到區域，這幾件事我都做過，三種寫法也都用過：IFS、SWITCH，還有把規則寫成一張對照表再用 XLOOKUP 查。這篇就用同一份資料，把三種寫法各做一次，看看什麼時候用哪一種。

IF 的基本寫法在 IF 函式入門，條件順序、邊界值、空白儲存格這些坑在 巢狀 IF 與 AND / OR 講過了，這邊不重講。

先看資料

這是十位會員的本年累積消費，放在 A1:D11。C 欄是付款代碼，D 欄是門市代碼：

我要在 E 欄標出會員等級，F 欄把付款代碼翻成中文，G 欄標出門市所在的區域。分級的規則是：累積消費 2500 以上是銀卡，8000 以上是金卡，18000 以上是白金，其他是一般。

我故意放了 2500、8000、18000 剛好踩門檻的三筆，加上差 1 的 7990 和 17999，等一下看公式有沒有切對。

用 IFS 分會員等級

IFS 是一組一組的「條件, 值」，由上往下檢查，第一個成立的就回傳。分級這種「大於某個數字就是某一級」的規則，我覺得它比巢狀 IF 好讀很多：

最後的 TRUE, "一般" 是兜底，前面三個都不成立才會走到這一組；不寫這一組，所有條件都不成立時 IFS 會回 #N/A。在 E2 輸入公式，按下 Enter，複製到 E11。來看看結果：

2500 剛好是銀卡，7990 還是銀卡，8000 才升金卡，17999 差 1 還是金卡，邊界都落在該有的那一邊，因為每個條件都寫了 >=。

名單裡如果有還沒消費、累積消費是空白的會員，空白會被當成 0，會被分到「一般」。想讓它保持空白，在最前面加一組：

用 SWITCH 轉付款代碼

付款方式只有三種代碼，CA 是現金、CC 是信用卡、LP 是 LINE Pay。這種「某個值等於什麼，就換成什麼」的情況，SWITCH 比 IFS 更直接，不用每一組都重複寫 C2=：

=SWITCH(判斷值, 比對值1, 結果1, [比對值2, 結果2, ...], [預設值])

最後單獨一個、沒有成對的 "未知代碼" 就是預設值。來看看 F 欄的結果：

M08 的 PP 是店裡新加的付款方式，公式裡沒有列到，就落到預設值。預設值我一定會寫，把它拿掉的話，對不到的那一列會得到 #N/A，整欄看起來像壞掉了，而「未知代碼」至少一眼就知道是哪一格有新代碼要補。

要補的話，就是回到公式裡多加一組 "PP", "街口支付"，再把整欄的公式更新一遍。付款方式一年也不會換幾次，我覺得這樣的維護成本可以接受，這種情況我就留在 SWITCH。

代碼變多：改成對照表

門市代碼就不一樣了。目前只有 S01 到 S05，每開一家新店就多一個代碼，用 SWITCH 寫會變成：

=SWITCH(D2, "S01", "北區", "S02", "北區", "S03", "中區", "S04", "南區", "S05", "南區", "未設定")

五組還撐得住，到了十五組、二十組，公式會長到沒人想打開，而且每開一家店就得改一次公式。這時候我會把對照關係搬到儲存格裡，做成一張小表。

在 I1:J6 建一張門市對照表：

G 欄用 XLOOKUP 去查，語法和參數在 XLOOKUP 基礎：

第四個參數 "未設定" 是找不到時顯示的文字，作用等於 SWITCH 的預設值。來看看 G 欄的結果：

開了新的門市 S06，先把查找公式的來源由 $I$2:$I$6、$J$2:$J$6 一起延伸到包含第 7 列，再在 I7:J7 補 S06 與區域。原本固定到第 6 列的公式不會自動查到第 7 列；若預留到第 20 列，查找欄與回傳欄也都要同步延伸。

假設 S06 補在 I7:J7 是「東區」，而 D12 有一位 S06 的會員，G12 這條公式查到的就是「東區」；如果忘了補表，G12 會顯示「未設定」，也很容易找出來。

等級門檻也能放進表裡

回到會員分級。假設行銷部門決定把銀卡門檻從 2500 改成 3000，用 IFS 的話，公式裡的 2500 要改掉，而且這條公式如果複製到好幾張工作表、好幾個檔案，每個地方都得找出來改。

我會把門檻也做成表，放在 L1:N5。第三欄順便放折扣率，等級跟折扣就能一起查：

等級的公式，用 XLOOKUP 的近似比對。第五個參數 -1 是「找不到完全相符的，就取比它小的最近一個」，用法在 XLOOKUP 進階 有講：

M05 是 8000，剛好等於金卡的門檻，結果是金卡。M04 是 7990，比 8000 小，取最近的比它小的那一列 2500，結果是銀卡。表裡的 0 那一列就是兜底，等於 IFS 最後的 TRUE。結果跟前面 IFS 那張表完全一樣，十筆都對得上。

折扣率只要把第三個參數換成 N 欄，同一張表就可以餵兩個欄位：

來看看十位會員的折扣率：

這時候再把銀卡門檻改成 3000，只要改 L3 這一格。M02 的 2500 沒到 3000，等級變成一般、折扣率變 1，M03 的 4380 還是銀卡，兩欄一起更新，公式不用動。

要用 IFS 做同樣的事，就得寫兩條，一條出等級、一條出折扣，門檻數字在兩條公式裡各出現一次。

怎麼選

我自己的分界線大概是這樣：

• IFS：條件是「大於、小於」這類範圍比較，階數不多（大約三到五級），規則只用在一個地方，之後也不太會調整。
• SWITCH：單一個值對照，項目不多（大約三到五個），幾乎不會增減，一看公式就知道在做什麼。
• 對照表加 XLOOKUP：項目很多、會增加或調整、好幾處共用同一套規則、一個規則要同時出好幾個欄位（等級加折扣），或是維護的人不想改公式。

代價是對照表是另外一塊東西，要有人記得它在哪裡，所以我會把它放在同一張工作表的旁邊，或獨立一張命名清楚的工作表。

要把公式帶到 Excel 的話，IFS 和 SWITCH 在 Excel 2019、2021、2024 與 365 才有，2016 以前的版本沒有；XLOOKUP 則是 Excel 365、2024、2021 才有，2019 以前沒有。IFS 沒有任何條件成立、SWITCH 沒有預設值又比對不到時，Excel 一樣回 #N/A。

如果你的規則只有三級、而且你就是唯一維護它的人，直接用 IFS 沒什麼不好，不必為了「正確」而硬做一張表。

動手練習

把你手上那條最長的巢狀 IF 找出來，看看它適合留在 IFS，還是該搬到一張表裡吧！我是喜特先生，Mr. Sheet，我們下個教學見！