close

337

IF函數公式優化使用範例

示範檔

範例檔

337.XLSX

結果檔

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

n  把負數轉換為0

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

優化後: =MAX(A2,0)

image

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

n  累計數公式

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

image

優化後:=N(C1)+A2

image

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

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

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

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

image

點評:(A1>=100)結果是TRUEFALSE,在計算時相當於10,所以這裡就不用再加判斷了。

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,"不可比","可比")

image

點評:一個一個的判斷,不如用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})

image

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

 


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()