close
標題:
[Excel] 有關級距和累加的公式
發問:
各位大大好: 小弟有一事相求 要算業績獎金的累積加總 但業績獎金的百分比是有級距的 業績門檻是10000 10000以下0% 10001~20000為8% 20001~50000為18% 50001~90000為30% 超過90000則為50% 一同仁業績為65000 手動計算如下 業績65000的總獎金為 10000*8%+30000*18%+15000*30% 我該怎麼填寫公式才能自動加總呢? 懇請各位大大不吝分享阿~~ Orz 更新: P.S. 非常感謝兩位大大， 不過公式跑出來似乎不是我要的~ 例如: 某A業績做了18000 那他可領(18000-10000)*8%=640 到這一步都沒有問題 可是某B業績若做了22000 公式跑出來是(22000-10000)*8%+(22000-20000)*10%=1160 但事實上我要的算法其實是 10000*8%+2000*10%=1000而已 原本10000以內的級距，重複計算了... 感謝兩位 還是再繼續徵求 更新 2: Sorry 是我看錯了 原來公式後面的百分比是累加的級數.....
最佳解答:
=SUMPRODUCT((A1-{1,2,5,9}*10000)*(A1>={1,2,5,9}*10000)*{8,10,12,20}%) 001樓的短式，觀念一樣 2012-04-19 17:34:27 補充： 業績65000的總獎金為 10000*8%+30000*18%+15000*30% 業績若做了22000 10000*8%+2000*10%=1000 這兩種算法不同? 哪一個是對的? 2012-04-20 18:09:09 補充： 可以再簡短一些 =SUM(TEXT(A1-{1,2,5,9}*10^4,"0;!0;!0")*{8,10,12,20}%) 此題麻煩鬍鬚大用原式上答說明 後面這些短式的作法都是由鬍鬚大的原式簡化來，不說明也很難看懂。 2012-04-21 00:13:41 補充： 還是Daniel 大師的公式細緻 2012-04-22 09:23:51 補充： 一、資料畫面 圖片參考：http://imgcld.yimg.com/8/n/AF02741245/o/161204190055713872561540.jpg 黃、橘、紅為公式區 二、單一公式說明 1. 費率分割圖示 圖片參考：http://imgcld.yimg.com/8/n/AF02741245/o/161204190055713872561541.jpg 2.公式重組演譯 以A1=80000為例 各階段獎金計算如下各階段獎金費率分解項目(1) 8%*(20000-10000) = 8%*(20000-10000)(2) 18%*(50000-20000) = 8%*(50000-20000) + 10%*(50000-20000)(3) 30%*(A1-50000) = 8%*(A1-50000) + 10%*(A1-50000) + 12%*(A1-50000)(1)+(2)+(3) = 8%*(A1-10000) + 10%*(A1-20000) + 12%*(A1-50000) (1)+(2)+(3)中的A1提出，並寫成EXCEL的陣列公式 =SUM((A1-{10000,20000,50000}) * {8%,10%,12%}) 再由上式(FOR A1=80000)，考慮為通式時，再增加考慮階段高於A1時，如A1-90000會造成負值，此時此項需取0，可以用TEXT(...,"0;!0")函數將負值轉為0，通式如下 =SUM(TEXT(A1-{1,2,5,9}*10^4,"0;!0")* {8,10,12,20}%) 三、也可以將C5+C6+C7+C8公式合併為單一公式 =MAX(MIN(C2-10000,10000),0)*8% +MAX(MIN(C2-20000,30000),0)*18% +MAX(MIN(C2-50000,40000),0)*30% +MAX(MIN(C2-90000,10^99),0)*50% 2012-04-22 09:35:17 補充： 上 三 的公式是以金額階段分塊計算 意見區004鬍鬚大的公式是以費率分段計算，所以公式較為簡短
其他解答:
●九州 娛樂 網站 http://ts777.cc ●●●運彩遊戲、真人遊戲、電子遊戲、對戰遊戲、對戰遊戲●●● ●新舊會員儲值就送500點 ● 真人百家樂彩金等你拿 ●線上影片直播、正妹圖、討論區免費註冊 歡迎免費體驗交流試玩! ●九州 娛樂 網站 http://ts777.cc|||||KK大的式子可簡為: =SUM((B1-{1,2,5,9}*10^4)*(B1>={1,2,5,9}*10^4)*{8,10,12,20}%) 2012-04-20 19:21:41 補充： K大果然是高招, 公式似忽可再簡為: =SUM(TEXT(A1-{1,2,5,9}*10^4,"0;!0")*{8,10,12,20}%) 呈K大之意, 麻煩鬍鬚大用原式上答說明 2012-04-21 16:33:04 補充： 只是拿K大的公式再簡化而已啦!|||||A1欄輸入業績 B1=(A1-10000)*8%+MAX((A1-20000),0)*10%+MAX((A1-50000),0)*12%+MAX((A1-90000),0)*20% 或=((A1-10000)*8+MAX((A1-20000),0)*10+MAX((A1-50000),0)*12+MAX((A1-90000),0)*20)% 2012-04-19 17:58:17 補充： 1. 更正： B1=(MAX(A1-10000,0)*8+MAX(A1-20000,0)*10+MAX(A1-50000,0)*12+MAX(A1-90000,0)*20)% 或=MAX(A1-10000,0)*8%+MAX(A1-20000,0)*10%+MAX(A1-50000,0)*12%+MAX(A1-90000,0)*20% 2. 如003意見請澄清 2012-04-20 22:39:01 補充： 還是K大上答較為恰當，=SUM(TEXT(A1-{1,2,5,9}*10^4,"0;!0")*{8,10,12,20}%)公式(D大稍作修短)簡短太多了，這問題要說明實在太繁絮了，版大若有需要再針對不清楚部分說明吧!
[Excel] 有關級距和累加的公式
發問:
各位大大好: 小弟有一事相求 要算業績獎金的累積加總 但業績獎金的百分比是有級距的 業績門檻是10000 10000以下0% 10001~20000為8% 20001~50000為18% 50001~90000為30% 超過90000則為50% 一同仁業績為65000 手動計算如下 業績65000的總獎金為 10000*8%+30000*18%+15000*30% 我該怎麼填寫公式才能自動加總呢? 懇請各位大大不吝分享阿~~ Orz 更新: P.S. 非常感謝兩位大大， 不過公式跑出來似乎不是我要的~ 例如: 某A業績做了18000 那他可領(18000-10000)*8%=640 到這一步都沒有問題 可是某B業績若做了22000 公式跑出來是(22000-10000)*8%+(22000-20000)*10%=1160 但事實上我要的算法其實是 10000*8%+2000*10%=1000而已 原本10000以內的級距，重複計算了... 感謝兩位 還是再繼續徵求 更新 2: Sorry 是我看錯了 原來公式後面的百分比是累加的級數.....
最佳解答:
=SUMPRODUCT((A1-{1,2,5,9}*10000)*(A1>={1,2,5,9}*10000)*{8,10,12,20}%) 001樓的短式，觀念一樣 2012-04-19 17:34:27 補充： 業績65000的總獎金為 10000*8%+30000*18%+15000*30% 業績若做了22000 10000*8%+2000*10%=1000 這兩種算法不同? 哪一個是對的? 2012-04-20 18:09:09 補充： 可以再簡短一些 =SUM(TEXT(A1-{1,2,5,9}*10^4,"0;!0;!0")*{8,10,12,20}%) 此題麻煩鬍鬚大用原式上答說明 後面這些短式的作法都是由鬍鬚大的原式簡化來，不說明也很難看懂。 2012-04-21 00:13:41 補充： 還是Daniel 大師的公式細緻 2012-04-22 09:23:51 補充： 一、資料畫面 圖片參考：http://imgcld.yimg.com/8/n/AF02741245/o/161204190055713872561540.jpg 黃、橘、紅為公式區 二、單一公式說明 1. 費率分割圖示 圖片參考：http://imgcld.yimg.com/8/n/AF02741245/o/161204190055713872561541.jpg 2.公式重組演譯 以A1=80000為例 各階段獎金計算如下各階段獎金費率分解項目(1) 8%*(20000-10000) = 8%*(20000-10000)(2) 18%*(50000-20000) = 8%*(50000-20000) + 10%*(50000-20000)(3) 30%*(A1-50000) = 8%*(A1-50000) + 10%*(A1-50000) + 12%*(A1-50000)(1)+(2)+(3) = 8%*(A1-10000) + 10%*(A1-20000) + 12%*(A1-50000) (1)+(2)+(3)中的A1提出，並寫成EXCEL的陣列公式 =SUM((A1-{10000,20000,50000}) * {8%,10%,12%}) 再由上式(FOR A1=80000)，考慮為通式時，再增加考慮階段高於A1時，如A1-90000會造成負值，此時此項需取0，可以用TEXT(...,"0;!0")函數將負值轉為0，通式如下 =SUM(TEXT(A1-{1,2,5,9}*10^4,"0;!0")* {8,10,12,20}%) 三、也可以將C5+C6+C7+C8公式合併為單一公式 =MAX(MIN(C2-10000,10000),0)*8% +MAX(MIN(C2-20000,30000),0)*18% +MAX(MIN(C2-50000,40000),0)*30% +MAX(MIN(C2-90000,10^99),0)*50% 2012-04-22 09:35:17 補充： 上 三 的公式是以金額階段分塊計算 意見區004鬍鬚大的公式是以費率分段計算，所以公式較為簡短
其他解答:
●九州 娛樂 網站 http://ts777.cc ●●●運彩遊戲、真人遊戲、電子遊戲、對戰遊戲、對戰遊戲●●● ●新舊會員儲值就送500點 ● 真人百家樂彩金等你拿 ●線上影片直播、正妹圖、討論區免費註冊 歡迎免費體驗交流試玩! ●九州 娛樂 網站 http://ts777.cc|||||KK大的式子可簡為: =SUM((B1-{1,2,5,9}*10^4)*(B1>={1,2,5,9}*10^4)*{8,10,12,20}%) 2012-04-20 19:21:41 補充： K大果然是高招, 公式似忽可再簡為: =SUM(TEXT(A1-{1,2,5,9}*10^4,"0;!0")*{8,10,12,20}%) 呈K大之意, 麻煩鬍鬚大用原式上答說明 2012-04-21 16:33:04 補充： 只是拿K大的公式再簡化而已啦!|||||A1欄輸入業績 B1=(A1-10000)*8%+MAX((A1-20000),0)*10%+MAX((A1-50000),0)*12%+MAX((A1-90000),0)*20% 或=((A1-10000)*8+MAX((A1-20000),0)*10+MAX((A1-50000),0)*12+MAX((A1-90000),0)*20)% 2012-04-19 17:58:17 補充： 1. 更正： B1=(MAX(A1-10000,0)*8+MAX(A1-20000,0)*10+MAX(A1-50000,0)*12+MAX(A1-90000,0)*20)% 或=MAX(A1-10000,0)*8%+MAX(A1-20000,0)*10%+MAX(A1-50000,0)*12%+MAX(A1-90000,0)*20% 2. 如003意見請澄清 2012-04-20 22:39:01 補充： 還是K大上答較為恰當，=SUM(TEXT(A1-{1,2,5,9}*10^4,"0;!0")*{8,10,12,20}%)公式(D大稍作修短)簡短太多了，這問題要說明實在太繁絮了，版大若有需要再針對不清楚部分說明吧!
此文章來自奇摩知識+如有不便請留言告知
FBEFE3C2E0474026
文章標籤
全站熱搜
留言列表
