錦子老師

IF函數是Excel工作表中最常用的函數之一，但很多同學會把IF函數公式寫的老長老長，今天本單元用5個範例教大家優化成簡短的公式。

n  把負數轉換為0

優化前： =IF(A2<0,0,A1)

優化後： =MAX(A2,0)

點評：該公式巧妙的利用MAX函數求最大值的原理，對比兩個數，如果小於0，就取最大值0.

n  累計數公式

優化前：=IF(ISNUMBER(B1),A2+B1,A2)

優化後：=N(C1)+A2

點評：N函數可以把文字轉換成0值，所以也不用再判斷是不是數字。直接相加即可。

n  如果A1大於等於100取前兩位，小於100取前一位

優化前：=IF(A1>=100,LEFT(A1,2),LEFT(A1,1))

優化後：=LEFT(A1,(A1>=100)+1)

點評：(A1>=100)結果是TRUE或FALSE，在計算時相當於1或0，所以這裡就不用再加判斷了。

n  如果有任一個為空，公式傳回「不可比」，否則顯示「可比」。

優化前：=IF(C2="","不可比",IF(D2="","不可比",IF(E2="","不可比",IF(F2="","不可比",IF(G2="","不可比",IF(H2="","不可比",IF(I2="","不可比",IF(J2="","不可比",IF(K2="","不可比",IF(L2="","不可比",IF(M2="","不可比",IF(N2="","不可比","可比"))))))))))))

優化後：=IF(COUNT(C10:N10)<12,"不可比","可比")

點評：一個一個的判斷，不如用COUNT函數統計非空值個數。

n  區間判斷

優化前：

=IF(AND(C2>0,C2<=9),3,IF(AND(C2>9,C2<=19),1.5,IF(AND(C2>19,C2<=29),0.7,IF(AND(C2>29,C2<=39),1,IF(AND(C2>39,C2<=49),1.6,IF(AND(C2>49,C2<=59),1.7,IF(AND(C2>59,C2<=80),2.4,IF(AND(C2>80,C2<=90),3.2,IF(C2>90,4,0)))))))))

優化後：=LOOKUP(C2-1%%,{0;2;8;19;29;80;100;150;200},{3;1.5;0.7;1;1.6;1.7;2.4;3.2;4})

點評：這是一個典型的區間判斷。LOOKUP函數可以返回 比查找的值小且和它最接近的值或對應第3個參數的值。因範圍是>和<=形式，所以這裡必須把值減去一個小數(C2-1%%)。如果不減，當C2=2時，傳回的不是3，而是1.5。