用上這個Excel公式,再也不用加班算提成了

2021-02-24 Excel函數與公式

點擊上方藍字  免費關注

置頂公眾號或設為星標，否則可能收不到文章

個人微信號 | （ID：LiRuiExcel520）

微信服務號 | 跟李銳學Excel（ID：LiRuiExcel）

微信公眾號 | Excel函數與公式（ID：ExcelLiRui）

經學員反饋，由於單位的銷售提成採用了分區間對應不同提成率的算法，所以每個月初都要為上千業務員的提成加班，他雖然學過一些Excel函數但一點也用不上，到底該怎麼辦？

本教程教你藉助Excel分分鐘搞定千人提成的複雜計算。而且提供多種解法供你選擇，讓你再也不用為提成計算發愁了。

在講具體算法之前，我們先來看一下提成計算的規則要求。

從上圖可見提成計算規則，以及舉例拆分算法。當然表中只給出16名業務員是為了舉例，不可能把上千人羅列完整。

請大家先自己獨立思考兩分鐘，再看下面的解析和算法答案，印象會更深刻，具體算法下面會分別按步驟介紹。

本教程內容擔心記不全的話，可以分享到朋友圈給自己備份一份。

除了本文內容，還想全面、系統、快速提升Excel技能，少走彎路的同學，請從「跟李銳學Excel」底部菜單進「知識店鋪」。

更多不同內容、不同方向的Excel視頻課程

長按識別二維碼↓進知識店鋪

（長按識別二維碼）

這個問題無非就是面對不同多條件判斷採取不同算法，絕大多數人第一直覺都是用IF函數，沒問題，可以解決，雖然不是最優算法，但是這是最簡單的。

用IF函數從大到小按照每個區間依次計算，Excel公式如下

=IF(B2>50000,(B2-50000)*5%+30000*3%+15000*2%+5000*1%,IF(B2>20000,(B2-20000)*3%+15000*2%+5000*1%,IF(B2>5000,(B2-5000)*2%+5000*1%,B2*1%)))

場景效果如下圖所示。

這種公式寫法雖然簡單，但是不容易擴展，當計算規則的區間很多時，IF的嵌套層級也隨之增加，公式會越來越長。

所以給出算法二，改善這種不足。

算法二的思路是藉助LOOKUP函數實現IF多層級判斷，前提是計算好臨界點和速算扣除數。

臨界點很好確定，直接看下圖，速算扣除數是為了簡化公式計算而設定的，計算方法公式如下

=(G3-G2)*H3+I2

做好以後，效果如下圖所示。

做好數據準備以後，剩下的就很簡單了，用LOOKUP函數最基礎用法即可搞定。

=LOOKUP(B2,{0;5000;20000;50000},B2*{1;2;3;5}%-{0;50;250;1250})

來上一張場景示意圖，幫助大家更好理解，黃色區域都是公式自動生成的。

這個公式已經大幅簡化IF公式了，但還可以進一步簡化，繼續看算法三。

算法三是觀察LOOKUP函數計算原理之後，發現提取結果應該是數組各元素中的最大值，所以採用MAX函數進一步簡化LOOKUP公式。

公式如下所示

=MAX(B2*{1;2;3;5}%-{0;50;250;1250})

不同解法都可以解決問題，喜歡哪種用哪種即可。這些公式都不難，很多同學反饋學了很久卻寫不出，是因為火候還不到，這需要一個過程是很正常的，慢慢積累+思考總結，總會達到的。

希望這篇文章能幫到你！怕記不住可以發到朋友圈自己標記。

更多方向和領域的Excel實戰技術，從入門到高級都有超清視頻精講，請從微信公眾號「跟李銳學Excel」進底部菜單的「知識店鋪」查看

更多經典的實戰技能，已整理成超清視頻的系統課程，方便你系統提升。

如果你喜歡超清視頻同步演示講解的課程，下方掃碼查看↓

（點擊圖片可放大查看）

長按識別二維碼↓進知識店鋪

（長按識別二維碼）

今天就先到這裡吧，希望這篇文章能幫到你！更多乾貨文章加下方小助手查看。

如果你喜歡這篇文章

歡迎點個在看，分享轉發到朋友圈

乾貨教程 · 信息分享

歡迎掃碼↓添加小助手進朋友圈查看

>>推薦閱讀 <<

（點擊藍字可直接跳轉）

史上最全VLOOKUP函數套路大全

Excel萬能函數SUMPRODUCT

IF函數強大卻不為人知的實戰應用技術

SUM函數到底有多強大，你真的不知道！

史上最全條件求和函數SUMIF教程

最具價值日期函數DATEDIF套路大全

Excel高手必備函數INDIRECT的神應用

飛簷走壁的函數裡數她輕功最好！她就是...

COUNTIF，堪比統計函數中的VLOOKUP，你會用嗎？

這個函數堪稱統計之王，會用的都是高手！

更多的Excel實戰技術，我已經整理到Excel特訓營中以超清視頻演示並同步講解，不但有具體場景，還講解思路和方法，更有配套的課件下載和社群互動。

想系統學習的同學長按下圖識別二維碼。

長按下圖  識別二維碼，進入知識店鋪

按上圖↑識別二維碼，查看詳情

請把這個公眾號推薦給你的朋友：）

長按下圖 識別二維碼

關注微信公眾號 「跟李銳學Excel」，每周有乾貨

▼

關注後每周都可以收到Excel乾貨教程

請把這個公眾號推薦給你的朋友

↓↓↓點擊「閱讀原文」進知識店鋪

     全面、專業、系統提升Excel實戰技能

相關焦點

  • 如何用excel算出員工工齡並核算銷售提成工資
    公司銷售部門新設定一向銷售提成計劃，規定工齡為0-1年（含1年）的銷售提成比例為1%，工齡為2-3年（含3年）的銷售提成比例為2%，工齡為3-4（含4年）年的銷售提成為3%，工齡為5-6（含6年）的銷售提成為5%，工齡大於6年以上的銷售提成為6%。財務部向人事部門調取銷售部門員工入職時間，接下來用excel計算銷售部門2020年1月份銷售提成工資。
  • 還在用Excel手動算薪?夥伴雲工資結算系統讓發薪效率提升90%
    「我們要通過excel表格，一條一條的截出來，眼睛得全神貫注的盯著電腦屏幕，時間長了，肩膀就疼的要命，眼睛也會不自覺的流眼淚……工資條發放的時候也得小心翼翼，生怕發錯，對公司造成不好的影響……」當然這還不是最難的時候，最難的是當工資條發放出去，「問題工資」員工的狂轟亂炸： 「曉霞，我這月的提成算的不對吧？」
  • 測量員實用excel公式計算表,參數輸入直接出結果,美美地不加班
    測量員實用excel公式計算表，參數輸入直接出結果，美美地不加班測量員在工作中往往需要進行大量參數的計算，一旦遇到大量的數據要處理，測量員的工作就會變得複雜，那麼加班便是常有的事。這套常用的測量公式計算表，是excel版本表格，只要將相關參數輸入，就能馬上得到計算的結果，非常的實用。裡面的內容也是非常的全面，不想加班就多使用excel計算表。
  • 工作這麼久才發現,原來PDF轉Excel這麼簡單,學會後再也不用加班
    "工作這麼久才發現，原來PDF轉Excel這麼簡單，學會後再也不用加班平時在工作中大家都會遇到各種文件之間的轉換格式，但是很少有人知道該怎樣將文件相互轉換，比如PDF轉excel，很少有人知道這兩個文件該如何轉換，今天小黑就來教大家兩個快速轉換的方法。
  • 圖文篇:Excel製作等級提成表,根據銷量計算比例,算出提成多少
    作者：圖文設計師東東圖文篇：Excel製作等級提成表，根據銷量計算比例，算出提成有多少錢。第三步、製作公式在提成下面單元格輸入公式（=vlookup(銷售額，輔助內容，F4,3,1)這樣一個公式。F4代表絕對引用，3代表比例列，1代表模糊查詢（如不明白大家可以翻看視頻篇課程去我的主頁就可以找到）如下圖。
  • 中秋節加班工資怎麼算中秋節加班工資計算公式一覽
    國慶、中秋將至，不少單位已經提前安排好節日的加班計劃。節日期間單位要求員工加班，必須徵得員工同意，否則員工有權拒絕加班。其中，用人單位在10月4日中秋節當天、10月1日到3日安排加班。那麼，中秋節加班工資怎麼算呢？中秋節加班工資怎麼算？
  • 你若會用Excel裡的SUM函數,又何須加班到半夜!
    在這個表格中需要對A列進行編號，規則是部門發生變化時序號才遞增。接到這個任務之後，大家又開始各自琢磨，有人開始嘗試各種公式，有人開始琢磨用操作技巧完成，小姐姐直接用SUM秒殺：對於這個公式，經理也有點發懵，考慮到大家看到這個公式後的不同反應，對公式的要點進行解析：1、B2-{0,2,3.5,5,7}*100，用客戶年銷售額分別減去0萬，200萬，350萬，500萬，700萬；
  • Excel公式助HR一鍵計算加班工資
    我先把公式先寫在下方，如果想了解公式由來可以繼續觀看，如果只想了解公式，可以直接拿走了。表格以及公式如下：=IF(B2>=35,35*6.5+(B2-35)*9,B2*6.5)依據不同加班時常計算加班費俗話說授人以魚不如授人以漁，給大家解釋一下我的思路：首先我們沒有公式會怎麼算呢？
  • excel函數公式應用:時間日期提取公式匯總,你用過哪些?
    今天老菜鳥針對上述在日常工作中經常會遇到的問題，總結了20個常用的關於日期和時間的公式，趕緊來看看吧！學習更多技巧，請收藏關注部落窩教育excel圖文教程。在實際工作中，經常需要進行日期和時間的計算，如：工作日天數、入職天數、合同到期日期、員工生日提醒、計算加班時間........
  • 新手老師必學技巧：Excel最變態的4個函數公式，卻好用到爆
    鑑於不同地域不同階段的學校,在實現智慧校園的過程中都會遇到各種問題和挑戰,當前教學模式也考驗著老師們線上教學的操作技能,今天就給大家分享老師們辦公中最常用的工具Excel的操作技巧,其中運營最典型的函數公式!
  • excel數據查找：內容查找統計的函數公式
    公式解析：COUNTIF(D2,"*加班*")是這個公式的核心部分，這個函數主要實現的是條件計數功能，基本格式為COUNTIF(條件區域,條件)。公式解析：這個公式的核心部分是FIND("加班",D2)，FIND函數的基本格式為FIND(要找的內容,在哪找,從第幾個字開始找)，如果第三參數不寫，表示從首字符開始找。
  • excel常用函數公式案例:VLOOKUP+IF嵌套
    excel常用函數：IF+VLOOKUP函數編寫公式計算銷售提成。
  • 提高工作效率,與加班說拜拜,Excel公式篇(四)
    第一體現了你的一部分能力，第二可以給領導一個好的印象，以技術提高個人形象，總比靠嘴上功夫要好。上篇文章，我們講了Excel優先級的問題，這章我們講下公式的複製的問題，這個內容比較簡單，今天是2018年的最後一天，咱們簡簡單單的聊，不用動多少腦細胞哈。
  • EXCEL函數公式大全用SUM函數IF函數HOUR函數MINUTE函數計算加班費
    EXCEL函數公式大全之利用SUM函數、IF函數、HOUR函數與MINUTE函數的組合計算員工加班費。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數SUM函數、IF函數、HOUR函數和MINUTE函數。
  • excel數據統計:三個公式提高統計工作效率
    在日常的辦公中，我們經常會統計excel裡各種數據。在excel裡關於統計的函數也是數不勝數，SUM、SUIMIF、SUMIFS、COUNT、COUNTIFS等等。可以使用公式=100*MIN(15,DATEDIF(B2,C2,"y"))計算出每個人的工齡工資。在這個公式中，用到了MIN和DATEDIF函數。
  • Excel案例精解：TEXT函數巧算加班時間換來的約會
    黴黴：嗯，領導讓我統計員工的總的加班時間？要求<30分，不算加班，>=30且<60的算30分鐘，>=60且<90的算60，依次類推，這怎麼計算啊？黴黴：這個公式裡我們就認識3，4個函數，其他的函數都是幹什麼的？之前你的寫的吧，我還能看明白，今天的這個公式徹底蒙圈啦！你趕緊跟我講講吧。說著黴黴已經拿出小本本，準備好記筆記啦！
  • Excel計算公式大全(1)
    excel 怎麼寫一個公式？假如當一個值等於這個值時顯示這個值或者顯示字母，不相同時向下累計加1顯示。112.我想實現一個excel的排序功能，需要怎麼寫公式？113.$A$1:$D$100是以貨號列為開始的，2是從區域的首列開始算，到單價所在列的數字（這裡假設B列就是單價，所以為2），千萬別忘了$符號37.excel表格中多個數據組合式查找的公式？
  • excel表格中的公式,你真的會用嗎?
    我發現很多同行都沒有用round公式保留小數，而是直接只顯示幾位小數，這樣做有什麼問題呢，最後匯總的數據可能不一致，導致花大量的時間去核對，沒必要，我們這樣把問題扼殺在搖籃裡面。如何使用round呢？DATEDIF(B2,C2,"d") （可直接複製哈），DATEDIF（開始日期，結束日期，計算單位（可以是年、月、日）），這樣工期就簡簡單單計算出來了，完全不用腦算時間，我的最愛。
  • 學會Excel的這三個技巧,再也不用加班篩選數據了!
    想知道為什麼小王整天加班到深夜，為工作熬壞眼睛幾年卻沒晉升嗎？（以Excel為例）查找與替換是Excel中最為簡單實用的功能之一，幾乎用過Excel的人都知道這個功能，也都會簡單使用這個功能，它可以幫助你節約大量的時間與精力，並保證工作質量不打折扣。
  • excel超級表:不用寫公式,也不用數據透視表,自動匯總統計!
    課程信息卡課程：《Excel天天訓練營》2.0圖文版章節：第1章-提高效率內容：excel超級表有的時候，可能你不需要對excel這個時候，可以既不用寫公式，也不用數據透視表，就能快速完成匯總統計，這就是超級表。excel本身就是一個表格，但功能裡有一個選項也叫「表格」，因為它功能比普通表格強大，所以就被稱之為「超級表」。首先，如何轉換為超級表？要將圖中案例表格轉換為超級表。