曾聽一位同學這樣說:我的一個同事excel水平很高,公式寫的好長。小能手看了公式,果然好長。不過,公式越長,excel水平越高嗎?其實結果正好相反,因為大部分長公式都有很大的優化餘地。下面我們就一起看一下最常見的長公式。
一、隔列求和
如下圖所示,需要對1~12月份的計劃和實際數進行求和(為方便截圖)
長公式:=B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3
短公式:=SUMIF(B$2:Y$2,Z$2,B3:Y3)
點評:本例巧用sumif條件求和,解決了隔列求和的問題。
二、總計行
如下圖所示,需要對下表設置總計行。
長公式:
C27 =C5+C8+C11+C14+C17+C20+C23+C26
短公式:C27 =SUM(C2:C26)/2
點評:(所有明細+小計)/2 的結果正好是總計數額。妙!
三、多表求和
如下圖所示,需要對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函數具有多表同位置求和功能,大家一定要記住。
四、判斷條件
如下圖所示,需要根據銷售量來使用提成比率。
長公式:=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個參數省略時,可以實現區間查找。
五 、計算完成率
如下表在計算完成率時,如果實際和計劃數有一方為空或0,則公式返回空。
長公式:D2 =IF(OR(B2=0,C2=0),"",B2/C2)
短公式:D2 =IF(B2*C2,B2/C2,"")
點評:巧用相乘來判斷是否其中一個為0
六、 獎勵封頂
如下圖所示的“實際獎勵”計算表中,如果“應獎勵”數大於“最高獎勵”,則按“最高獎勵”金額,如果小於則按“應獎勵”金額。
長公式:D2 =IF(B2>=C2,D2,B2)
短公式:D2 =MIN(B2,C2)
點評:min和max都可以實現比大小的判斷。
excel短公式看起來清爽,只是對新手來說有點難懂。什麼樣的公式最好的,覺得有以下幾點來判斷:
1 是否容易理解。
2 是否易修改,易維護,公式能否表格的變化而自動調整。
3 計算量儘可能的少,不會拖慢表格。
看完別忘收藏轉發哦~
閱讀更多 輕鬆工作快樂生活 的文章