337 | IF函數公式優化使用範例 | ||||
示範檔 | 無 | 範例檔 | 337.XLSX | 結果檔 | 無 |
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。
留言列表