[[20141202183605]]　『昇給シミレーションを作成したい』（実務）　ページの最後に飛ぶ

『昇給シミレーションを作成したい』（実務）

はじめまして　エクセルド素人です。
現在昇給シミレーションを作成中です。
昇給は
号数　　金額　　　期間
1　　　281,500　9か月後
2　　　300.150　6か月後
…という表に従って昇給していきます。
　これを用いて
1　超過勤務を400時間として
2　夏冬賞与があったとして
3　他の手当て等を加えて
退職までにいくらもらえるのかを計算したいと考えています。
それに加えて、入社~ある日(知りたい日）を入れると該当する号数と金額がわかるようにしたいです。　例えば入社から12か月なら2号数300,150円

マクロは使えません。

よろしくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows8 ＞

 賞与のデータはどこにあるのでしょう？
 退職日のデータはどこにあるのでしょう？
（カリーニン） 2014/12/02(火) 19:38

返信ありがとうございます。説明が下手だったの　どなたからもいただけないかと思っていました。
賞与のデータは　基本給×2.67等式で計算します　退職日データはありません。　一応60歳が定年なのでその都度手入力しようと思います。　
（実務） 2014/12/03(水) 10:14

 １．20歳から60歳までで40年なので、480か月分　下に示したような表を作ればいいと思うのですけど、
 　　それは大変なので、計算式で求めたいと云うことなんですか？

 ２．にしても、計算方法が明確じゃないので、取りあえず14箇月しか居ない予定の社員のサンプルを
 　　埋めていただけませんか？

 　　14箇月分ならそんなに大変じゃないと思いますので・・（それで、説明不足がかなり解消します）

  行 _______A_______ ____B____ ____C____ __D__ ___E___ __F__ __G__ _H_ __I__ ___J___ __K__ __L__
   1 生年月日        1994/8/10                                                                  
   2 入社日          2014/12/1                                                                  
   3 退職日          2015/1/31                                                                  
   4 知りたい日→              期間            号数          金額                               
   5 退職日まで幾ら                                                                             
   6                                                                                            
   7 経過月数        基本給    超過勤務  手当  その他  賞与  月収      号数  金額    期間  通算 
   8     1           281,500  　　　　　　　　　　　　　　　　　         1  281,500    1     1 
   9     2           281,500                                             2  300,150    9    10 
  10     3           281,500                                             3             6    16 
  11     4           281,500                                             4                     
  12     5           281,500                                             5                     
  13     6           281,500                                             6                     
  14     7           281,500                                             7                     
  15     8           281,500                                             8                     
  16     9           281,500                                             9                     
  17    10           300,150                                            10                     
  18    11           300,150                                            11                     
  19    12           300,150                                                                   
  20    13           300,150                                                                   
  21    14           300,150

（半平太） 2014/12/03(水) 15:04

大変遅くなりましたが、サンプル作ってみました。
入社日4月1日
退社日6月30日

基本給281,500円が9か月300,150が5か月
○超過勤務手当の計算式はひと月あたり「基本給単価÷1,900×1.25×400」
○期末勤勉手当　夏(６月）基本給×1.85　
冬(１２月）基本給×2.3　の年２回
・在職が支給前６か月に満たない場合は在籍月を６か月換算する。
・基本給は夏は5月の基本給　冬は11月の基本給が単価になります。

　～退職までにもらえる金額は～

※基本給281,500×9+300,150×5＝4,034,250
超過勤務手当　281,500÷1,900×1.25×400×9+300,150÷1,900×1.25×400×5＝1,061,644(最終答え一円未満切り捨て）
※他手当月3,000×14か月＝42,000
※賞与　第1回6月支給281,500×1.85×2÷6＝173,591(4月入社のため2か月分支給）
　　　　第２回１２月支給281,500×2.3＝647,450
　　　　第3回6月支給300,150×1.85＝555,277
合計　6,514,212
になります。
遅くなりましたがよろしくお願いいたします。
（実務） 2014/12/09(火) 11:47

 シミュレーションなので、在籍月数の計算とか端数丸めは、そんなに厳密にしなくていいのでしょうけど、
 2014/4/1～2015/6/30は、いくらなんでも合計15箇月が正しいのではないですか？
 そちらの計算は、合計14箇月とお見受けしましたが・・・

（半平太） 2014/12/09(火) 20:35

大変失礼しました。　退職日が5月30日で
賞与第3回が300,150×1.85×5÷6＝462,731に訂正で　6,421,666合計になります。

申し訳ございませんでした。

式で計算することはできますでしょうか？
（実務） 2014/12/13(土) 12:34

 ＞退職日が5月30日で

 ちょっと待ってくださいよ。6月に在籍していなくても賞与って出るんですか？
 そちらの会社の賞与支給ルールが分からないと作式できないです。

 それとも、あくまでシミュレーションなので、支給総額の傾向が知りたいから、
 何月に退職しようとも、按分で算出する方が却って目的に叶っているってことなんですか？

（半平太） 2014/12/13(土) 15:01

実際には一部の役員等には在籍しなくても賞与は按分計算で出ます。　
一般職員も病気やけがなどでやめた場合は出ます。
半平太様のおっしゃるとおり　シミュレーションなので最後は何月に退職しても按分計算で賞与を出す計算でお願いします。
（実務） 2014/12/13(土) 16:01

 (1) D3セル =IF(B3*1,DATEDIF(B$2-DAY(B$2),EOMONTH(B3,0)+1,"m"),"")
 (2) F3セル =IF(B3*1,LOOKUP(D3,N$2:N$9,K$2:K$9),"")
 (3) H3セル =IF(B3*1,INDEX(L$2:L$9,F3),"")
 (4) J3セル =IF(B3*1,INT(SUMPRODUCT(LOOKUP(ROW(INDIRECT("A1:A"&D3)),N$2:N$9,L$2:L$9))*24/19)+3000*D3+IF(D3=1,0,INT(SUMPRODUCT(LOOKUP(ROW(INDIRECT("A1:A"&D3-1))+MOD(4-MOD(ROW(INDIRECT("A1:A"&D3-1))+MONTH(B$2)-2,12),6),N$2:N$9,L$2:L$9)*((MOD(ROW(INDIRECT("A1:A"&D3-1))+MONTH(B$2)-1,12)+1<7)*1.85+(MOD(ROW(INDIRECT("A1:A"&D3-1))+MONTH(B$2)-1,12)+1>6)*2.3))/6)),"")

 <結果図>
 行 ______A______ ____B____ __C__ _D_ __E__ _F_ ___G___ ___H___ ____I____ ____J____ __K__ ___L___ __M__ _____N_____
  1 生年月日      1994/8/10                                                         号数  金額    期間  通算(以上) 
  2 入社日        2014/4/1                                                             1  281,500    0           0 
  3 退職日        2015/5/31 月数   14 号数    2 基本給  300,150 通算給金  6,421,666    2  300,150    9          10 
  4 知りたい日→  2014/6/30         3         1         281,500           1,249,327    3  ?          6          16 
  5                                                                                    4  ?       ?     ?          
  6                                                                                    5  ?       ?     ?          
  7                                                                                    6  ?       ?     ?          
  8                                                                                    7  ?       ?     ?          
  9                                                                                    8  ?       ?     ?

（半平太） 2014/12/13(土) 20:32

コメント返信：

[ 一覧(最新更新順) ]

[[20141202183605]] 『昇給シミレーションを作成したい』（実務） ページの最後に飛ぶ

[[20141202183605]]　『昇給シミレーションを作成したい』（実務）　ページの最後に飛ぶ