0
(0)

曾聽一位同學這樣說:我的一個同事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)

excel計算多格總和

點評:本例巧用sumif條件求和,解決了隔列求和的問題。

2、總計行求和

如下圖所示,需要對下表設置總計行。

長公式:=C5+C8+C11+C14+C17+C20+C23+C26

短公式:=SUM(C2:C26)/2

excel計算多格總和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)

excel計算多格總和3

點評:sum函數具有多表同位置求和功能,大家一定要記住。

二、判斷公式

1、計算提成比率

如下圖所示,需要根據銷售量來使用提成比率。

長公式:=IF(B11<11,1%,IF(B11<21,2%,IF(B11<51,3%,IF(B11<81,4%,IF(B11<101,5%,6%)))))

excel查詢提成比率1

短公式:=VLOOKUP(B11,A3:B8,2)

提成比率表格式稍調整一下:

excel查詢提成比率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,””)

EXCEL計算完成率

點評:巧用相乘來判斷是否其中一個為0。

5、獎勵封頂

如下圖所示的“實際獎勵”計算表中,如果“應獎勵”數大於“最高獎勵”,則按“最高獎勵”金額,如果小於則按“應獎勵”金額。

長公式:=IF(B2>=C2,D2,B2)

短公式:=MIN(B2,C2)

EXCEL計算封頂獎勵

點評:min和max都可以實現比大小的判斷。

最後

什麼樣的公式最好的,喵醬覺得有以下幾點來判斷:

  1. 容易理解
  2. 易修改,易維護,公式能否隨表格的變化而自動調整。
  3. 計算量盡可能的少,不會拖慢表格。

擡擡小手賞個5星吧?

單擊星星進行評價

平均分 0 / 5. 評價計數: 0

還沒有人評價哦~

抱歉喲~如果您覺得不好,可以聯絡我們進行質量提升喲!

期待您的反饋

麻煩告知我們該如何改進,謝謝!

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *