曾聽一位同學這樣說:我的一個同事excel水平很高,公式寫的好長。的確公式很長在不懂excel的人那裡看起來很厲害。不過,公式越長,excel水平越高嗎?
其實結果正好相反,因為大部分長公式都有很大的優化餘地。Excel水平越是高超,寫出來的才越精簡。下面我們就一起看一下最常見的長公式和短工式區別。
一、求和公式
1、隔列求和
如下圖所示,需要對1~12月份的計劃和實際數進行求和(為方便截圖)
長公式:=B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3
短公式:=SUMIF(B$2:Y$2,Z$2,B3:Y3)
點評:本例巧用sumif條件求和,解決了隔列求和的問題。
2、總計行求和
如下圖所示,需要對下表設置總計行。
長公式:=C5+C8+C11+C14+C17+C20+C23+C26
短公式:=SUM(C2:C26)/2
點評:(所有明細+小計)/2 的結果正好是總計數額。妙!
3、多表求和
如下圖所示,需要對1~31日的報表進行匯總
長公式:=’1′!B4+’2′!B4+’3′!B4+’4′!B4+’5′!B4+’6′!B4+’7′!B4+’8′!B4+’9′!B4+’10’!B4+’11’!B4+’12’!B4+’13’!B4+’14’!B4+’15’!B4+’16’!B4+’17’!B4+’18’!B4+’19’!B4+’20’!B4+’21’!B4+’22’!B4+’23’!B4+’24’!B4+’25’!B4+’26’!B4+’27’!B4+’28’!B4+’29’!B4+’30’!B4+’31’!B4
短公式:=SUM(‘1:31’!B4)
點評:sum函數具有多表同位置求和功能,大家一定要記住。
二、判斷公式
1、計算提成比率
如下圖所示,需要根據銷售量來使用提成比率。
長公式:=IF(B11<11,1%,IF(B11<21,2%,IF(B11<51,3%,IF(B11<81,4%,IF(B11<101,5%,6%)))))
短公式:=VLOOKUP(B11,A3:B8,2)
提成比率表格式稍調整一下:
點評:vlookup第4個參數省略時,可以實現區間查找。
2、區間判斷提取字符
如果A1大於等於100取前兩位,小於100取前一位。
長公式:=IF(A1>=100,LEFT(A1,2),LEFT(A1,1))
短公式:=LEFT(A1,(A1>=100)+1)
點評:(A1>=100)結果是TRUE或FALSE,在計算時相當於1或0,所以這裡就不用再加判斷了。
3、多單元判斷
如果有任一個為空,公式返回不可比,否則顯示”不可比”。
長公式:
=IF(C11=””,”不可比”,IF(D11=””,”不可比”,IF(E11=””,”不可比”,IF(F11=””,”不可比”,IF(G11=””,”不可比”,IF(H11=””,”不可比”,IF(I11=””,”不可比”,IF(J11=””,”不可比”,IF(K11=””,”不可比”,IF(L11=””,”不可比”,IF(M11=””,”不可比”,IF(N11=””,”不可比”,”可比”))))))))))))
短公式:=IF(COUNT(C10:N10)<12,”不可比”,”可比”)
點評:一個一個的判斷,不如用COUNt函數統計非空值個數。
4、計算完成率
如下表在計算完成率時,如果實際和計劃數有一方為空或0,則公式返回空。
長公式:=IF(OR(B2=0,C2=0),””,B2/C2)
短公式:=IF(B2*C2,B2/C2,””)
點評:巧用相乘來判斷是否其中一個為0。
5、獎勵封頂
如下圖所示的“實際獎勵”計算表中,如果“應獎勵”數大於“最高獎勵”,則按“最高獎勵”金額,如果小於則按“應獎勵”金額。
長公式:=IF(B2>=C2,D2,B2)
短公式:=MIN(B2,C2)
點評:min和max都可以實現比大小的判斷。
最後
什麼樣的公式最好的,喵醬覺得有以下幾點來判斷:
- 容易理解
- 易修改,易維護,公式能否隨表格的變化而自動調整。
- 計算量盡可能的少,不會拖慢表格。