計(jì)算有上下限制的獎(jiǎng)金提成,還在死抱著IF函數(shù)不放?那真的太Out!
作者:郅龍來源:部落窩教育發(fā)布時(shí)間:2021-10-20 10:05:53點(diǎn)擊:1551
編按:
Hello小伙伴們,公式寫得越長(zhǎng)就越厲害嗎?顯然不是,真正的Excel高手都是用最簡(jiǎn)單實(shí)用的函數(shù)公式解決最實(shí)際的問題。工作中,為了激勵(lì)員工拿下更多的訂單,領(lǐng)導(dǎo)們會(huì)設(shè)置獎(jiǎng)金、提成,但是為了和諧還會(huì)設(shè)置獎(jiǎng)金的上下限。那么此時(shí)我們應(yīng)該如何計(jì)算員工提成獎(jiǎng)金?很多小伙伴會(huì)想到用IF函數(shù)公式嵌套,但是實(shí)際操作起來時(shí),會(huì)把自己套暈。今天給大家介紹三個(gè)函數(shù)公式解決這個(gè)問題,簡(jiǎn)單又實(shí)用!
工作中經(jīng)常會(huì)遇到獎(jiǎng)金和提成計(jì)算之類的問題。例如,某企業(yè)獎(jiǎng)金提成按業(yè)績(jī)的5%進(jìn)行發(fā)放,即獎(jiǎng)金提成=業(yè)績(jī)*5%,但是會(huì)根據(jù)不同時(shí)期設(shè)置保底或者封頂?shù)恼?,一般?huì)有這樣的三種情況:
①獎(jiǎng)金提成按業(yè)績(jī)的5%計(jì)算,但是有保底獎(jiǎng)金200元;
②獎(jiǎng)金提成按業(yè)績(jī)的5%計(jì)算,但是最高獎(jiǎng)金為1000元封頂;
③獎(jiǎng)金提成按業(yè)績(jī)的5%計(jì)算,但是獎(jiǎng)金不得低于200元同時(shí)不能超過1000元。
對(duì)于這些問題,很多人首先想到的就是用IF函數(shù)來回嵌套,把自己搞得暈頭轉(zhuǎn)向不說,結(jié)果對(duì)不對(duì)都不敢保證。實(shí)際上針對(duì)這三類情況有專門的公式套路可以使用的,今天就來和大家分享這方面的萬(wàn)能公式。
情況一:有保底金額的獎(jiǎng)金計(jì)算公式
對(duì)于這種情況的萬(wàn)能公式是:MAX(保底金額,計(jì)算的獎(jiǎng)金提成)
結(jié)合實(shí)際要求來說,公式就是=MAX(200,B2*5%)。
這個(gè)公式可以將不足200元的獎(jiǎng)金按200元計(jì)算,例如孫永全的業(yè)績(jī)是3814,本來獎(jiǎng)金是190.7,但是用了MAX函數(shù),就會(huì)取獎(jiǎng)金和200的最大值,因此就實(shí)現(xiàn)了保底200元的效果。
情況二:有封頂金額的獎(jiǎng)金計(jì)算公式
對(duì)于這種情況的萬(wàn)能公式是:MIN(封頂金額,計(jì)算的獎(jiǎng)金提成)
結(jié)合實(shí)際要求來說,公式就是=MIN(1000,B2*5%)。
這個(gè)公式可以將超過1000元的獎(jiǎng)金按1000元計(jì)算,例如姚芳召的業(yè)績(jī)是20511,本來獎(jiǎng)金是1025.55,但是用了MIN函數(shù),就會(huì)取獎(jiǎng)金和1000的最小值,因此就實(shí)現(xiàn)了封頂1000元的效果。
情況三:同時(shí)存在保底和封頂?shù)莫?jiǎng)金計(jì)算公式
對(duì)于這種情況,有三個(gè)公式套路都可以用。
先來看第一個(gè)公式:MIN(封頂金額,MAX(保底金額,計(jì)算的獎(jiǎng)金提成))。
結(jié)合實(shí)際要求來說,公式就是=MIN(1000,MAX(200,B2*5%))。
這個(gè)公式首先是用實(shí)際獎(jiǎng)金和200比較,取最大值,這就解決了保底的問題,再用這個(gè)最大值和1000比較,取最小值,這就解決了封頂?shù)膯栴}。
公式實(shí)際上是結(jié)合了前兩種情況,分兩步實(shí)現(xiàn)了保底和封頂?shù)男Ч?/span>
如果把順序顛倒一下,就有了第二個(gè)公式:=MAX(保底金額,MIN(封頂金額,計(jì)算的獎(jiǎng)金提成))
結(jié)合實(shí)際要求來說,公式就是=MAX(200,MIN(1000,B2*5%))。
這兩個(gè)公式的原理都差不多,要和大家重點(diǎn)推薦的是第三個(gè)公式套路:=MEDIAN(保底金額,封頂金額,計(jì)算的獎(jiǎng)金提成)。
結(jié)合實(shí)際要求來說,公式就是=MEDIAN(200,1000,B2*5%)。
很多同學(xué)可能還是第一次見到這個(gè)函數(shù),下面隆重介紹一下。
MEDIAN函數(shù)的功能是得到一組數(shù)字中的中位數(shù)。中位數(shù)是一組數(shù)據(jù)中居于中間的數(shù)。換句話說,在這組數(shù)據(jù)中,有一半的數(shù)據(jù)比它大,有一半的數(shù)據(jù)比它小。當(dāng)參數(shù)個(gè)數(shù)為奇數(shù)時(shí),返回中間的那一個(gè)數(shù);當(dāng)參數(shù)個(gè)數(shù)為偶數(shù)時(shí),返回中間的兩個(gè)數(shù)再進(jìn)行求平均值。
函數(shù)的用法很簡(jiǎn)單,和SUM、MAX、MIN這些函數(shù)都一樣。參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。參數(shù)中的邏輯值會(huì)參與計(jì)算。數(shù)組或引用中的邏輯值或空白單元格,將被忽略;但包含零值的單元格將計(jì)算在內(nèi)。如果參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本,將會(huì)返回錯(cuò)誤值。
在今天的例子中,MEDIAN用到了三個(gè)參數(shù),分別是保底金額、封頂金額、計(jì)算的獎(jiǎng)金提成,函數(shù)就會(huì)得到三個(gè)數(shù)字中既不是最大也不是最小的那個(gè)數(shù),這樣用一個(gè)函數(shù)就實(shí)現(xiàn)了保底和封頂?shù)男Ч?/span>
掌握了今天這些萬(wàn)能公式以后在遇到這樣的問題就不要死抱著IF不放了。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
Excel運(yùn)用規(guī)范1:一個(gè)單元格只記錄一條信息
9條最實(shí)用的計(jì)算excel中關(guān)于日期的公式?。ńㄗh收藏)
版權(quán)申明:
本文作者郅龍;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)