エクセルで複雑な退職金の計算をしたいのですが

Excel2007で次の退職金の計算をしたいのですがご教授願います。

起算日　A1
退職日　A2
在職期間　=DATEDIF(A1,A2,"ｙ")&"年"&DATEDIF(A1,A2,"ｙｍ")&"ヶ月"&DATEDIF(A1,A2,"md")&"日"　　　　　　　←この計算式で○年○月○日を計算しました。

その後次の計算が必要になります。
(1)在職年数に端数がある時は月割りで計算し、1ヶ月未満の端数は1ヶ月に切り上げる。
　端数日数を1ヶ月単位に切り上げ、○年○ヶ月を求めます。
(2)在職年月に基本給を掛けて、更に係数を掛けて退職金を求める。
　（○年＋○ヶ月/12）×基本給×係数　となります。

そこで(1)をトライしてみましたが、(2)の方法がわからないので、(1)の検証もできません。
(1)の計算は次のように求めてみました。
　=DATEDIF(A1,A2,"ｙ")&"年"&DATEDIF(A1,A2,"ｙｍ")+(DATEDIF(A1,A2,"md")>0)&"ヶ月"
この計算では在職期間が○年11ヶ月○日となった場合、求めた在職年数は○年12ヶ月となってしまい、年に繰り上がりません。従って(2)の方法に影響が出そうですが、(2)の方法自体がわからないのでお手上げ状態です。

(1)と(2)、あるいは最初の方法からからでも構いませんので、ご教授を頂きたいと思います。

No.1 回答者： neKo_deux 回答日時： 2013/08/29 17:35

> 在職期間

> 在職年数
> 在職年月

これらは別々のものなのか？同じものを指すのか？ってな所から、問題を整理した方が良いと思いますが。



> 在職期間　=DATEDIF(A1,A2,"ｙ")&"年"&DATEDIF(A1,A2,"ｙｍ")&"ヶ月"&DATEDIF(A1,A2,"md")&"日"　　　　　　　←この計算式で○年○月○日を計算しました。

> (1)の計算は次のように求めてみました。
> 　=DATEDIF(A1,A2,"ｙ")&"年"&DATEDIF(A1,A2,"ｙｍ")+(DATEDIF(A1,A2,"md")>0)&"ヶ月"

せっかく質問文の上から３行目の式で在職期間の年、月、日を計算してるのに、改めてDATEDIFする必要は無いのでは。
３行目の式で在職期間を計算し、A3にでも入れたのなら、
A4に在職期間の年数
A5に在職期間の月数
A6に在職期間の日数
とかって数字を取り出せば、
A8に端数日数を切り上げた場合の月数
A7に端数日数を切り上げた場合の年数
が計算しやすいのでは？

式一本で書ければシンプルではありますが、エラーなんかがあっても原因が分かりづらいし、無理する必要性って無いと思いますが。

この回答へのお礼

早速のご回答ありがとうございます。
私も結局はご回答のような方法がベストかなと思いつつ、もう少し深入りした手立てを模索してしまいました。参考にさせて頂きます。ありがとうございました。

お礼日時：2013/08/30 14:41

No.2 回答者： kmetu 回答日時： 2013/08/29 20:43

> (2)在職年月に基本給を掛けて、更に係数を掛けて退職金を求める。

> 　（○年＋○ヶ月/12）×基本給×係数　となります。

が出せればいいんですよね。

=YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1)

で全月数が求められます。端数日数を1ヶ月単位に切り上げる部分は質問者さんの式を利用して

=((YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1))+(DATEDIF(A1,A2,"md")>0)

として切り上げた月数を求めます。

以降上記の式そのまま書くと長くなるので、便宜上以後 X と表示します。

（○年＋○ヶ月/12）×基本給×係数

の○年は

=INT(X)

で求められます。

○ヶ月は

=MOD(X)

で求められます。

こういう出し方でいかがでしょうか。

各関数の意味はHELPなどで調べてくださいね。

No.3 回答者： kmetu 回答日時： 2013/08/29 21:00

補足追加です

切り上げた全月数は

=DATEDIF(A1,A2,"ｍ")+(DATEDIF(A1,A2,"md")>0)

としたほうが短くていいですね。

No.4 回答者： kmetu 回答日時： 2013/08/29 21:24

たびたびすみません

さっき他で見たんですが
なんかエクセル2007のDATEDIFにはバグがあるみたいですね。

https://www.google.co.jp/search?q=datedif+%E3%83 …


=((YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1))+(DAY(A2-A1)>0)

こちらの式のほうがいいかもしれません。

No.5 回答者： kmetu 回答日時： 2013/08/29 21:39

またまた　ごめんなさい

DAY(A2-A1)はダメでしたすみません。

代替の式は
http://www.geocities.jp/chiquilin_site/data/1005 …
を参考にしてみてください。

この回答へのお礼

kmetuさん、本当に色々試して下さり有り難うございます。バグ情報も助かります。目下色々なご回答を頂き試しているところです。取りあえず御礼申し上げます・

お礼日時：2013/08/30 14:44

No.6 回答者： tom04 回答日時： 2013/08/29 21:40

こんばんは！

＞在職年数に端数がある時は月割りで計算し、1ヶ月未満の端数は1ヶ月に切り上げる。

すなわち「起算日」と「退職日」の日付部分が同じ日以外はすべて切り上げと考えてみてはどうでしょうか？

そういった方法での一案です。

↓の画像のA4セルに
=IF(DAY(A1)=DAY(A2),DATEDIF(A1,A2,"Y")&"年"&DATEDIF(A1,A2,"YM")&"ヶ月",DATEDIF(EDATE(A1,-1),A2,"Y")&"年"&DATEDIF(EDATE(A1,-1),A2,"YM")&"ヶ月")

という数式を入れています。
「起算日」の日付と「退職日」の日付が異なる場合は「起算日」の1か月前からの計算としています。

A5セルはA4セルの文字列を利用して
=LEFT(A4,FIND("年",A4)-1)*12+MID(A4,FIND("年",A4)+1,FIND("ヶ月",A4)-FIND("年",A4)-1)

という数式を入れています。
後はA5セルの数値を利用して
A5×基本給×係数
としてみてはどうでしょうか？m(_ _)m

この回答へのお礼

tom04さん、ご回答有り難うございました。
早速試して見ました。
端数日数を切り上げる事に成功しました。

但しA5セルは月数の和を求めるのでなく、あくまで「年」と「月数」を出して計算します。
そうしないと、「月数の合計×基本給」になってしまい、大変な金額になってしまいます。

またNo.8のbunjiiさんのご指摘通り、「入社日と退職日は在職日数に含む」とすれば、求めた在職日数が-1日になってしまい悩んでいます。

お礼日時：2013/08/30 16:47

No.7 回答者： kmetu 回答日時： 2013/08/29 22:42

No6さんの考え方「「起算日」と「退職日」の日付部分が同じ日以外はすべて切り上げ」もいいかもですね。

とすればDATEDIFを使わない方法としては

=((YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1))+(DAY(A2)<>DAY(A1))

もありかも

ただ、起算日2013/1/31退職日2013/2/1で2ヶ月と計算されてしまいますが…

この回答へのお礼

kmetuさん、度々のご回答有り難うございます。
最後の「…2ヶ月と計算されてしまいますが」は困ってしまいますね。。残念です(T-T)

お礼日時：2013/08/30 16:35

No.8 回答者： bunjii 回答日時： 2013/08/30 08:30

>この計算では在職期間が○年11ヶ月○日となった場合、求めた在職年数は○年12ヶ月となってしまい、年に繰り上がりません。

基本的なことだけお伝えします。
日付の計算には0日がありませんので期間の計算に日付の値を使うことで思わぬ落とし穴に落ちます。
あなたが使っているDATEIF(A1,A2,"ym")は年と月の値で差を求めています。
従って端数の扱いはできません。
また、日数の計算でDATEIF(A1,A2,"md")としていますが、この式では起算日が含まれませんので、入社日の前日を起算日にしなければならず、誤解を招きます。（入社日と退職日は在職日数に含む）

端数繰り上げはROUNDUP関数を使うと余分な手数を必要としません。
A1セルに入社日、A2セルに退職日が入力されているとして
勤続年数（端数切捨て）=ROUNDDOWN(YEARFRAC($A$1,$A$2+1,1),0)
端数月数（端数繰り上げ）=ROUNDUP(MOD(YEARFRAC($A$1,$A$2+1,1),1)*12,0)-1

貼付図の上は勤続年数を算出したセルの状態です。
同じく下は端数の月数を算出しています。
後はあなたの応用力次第です。

この回答へのお礼

bunjiiさん、ご回答有り難うございました。

ご指摘通り「入社日と退職日は在職日数に含む」を考慮し試して見ました。
Ａ1セルに2012/8/29
Ａ2セルに2013/8/28 と試したところ、Ｄ3が「-1」となってしまいました。
ROUNDUP関数のコマンド自体がわかりませんので、原因がつかめません。

またご回答頂きましたＡ3セル（在職期間）の関数は何を使われたのでしょうか？
よろしくお願いします。

お礼日時：2013/08/30 17:32

No.9 回答者： tom04 回答日時： 2013/08/30 17:14

No.6です。

まず
＞「入社日と退職日は在職日数に含む」とすれば、求めた在職日数が-1日になってしまい悩んでいます
の件について

入社日・退社日も含むのであれば、入社日の1日前からの計算にしてみてはどうでしょうか？

=IF(DAY(A1)=DAY(A2),DATEDIF(A1,A2,"Y")&"年"&DATEDIF(A1-1,A2,"YM")&"ヶ月",DATEDIF(EDATE(A1-1,-1),A2,"Y")&"年"&DATEDIF(EDATE(A1-1,-1),A2,"YM")&"ヶ月")

といった感じになります。

次に
＞月数の合計×基本給」になってしまい、大変な金額になってしまいます。
について・・・

おそらく前回の結果を12で割ったもので計算すればお望みの数値になると思いますが、
敢えて、その数値をセルに表示させるとすれば

=LEFT(A4,FIND("年",A4)-1)+MID(A4,FIND("年",A4)+1,FIND("ヶ月",A4)-FIND("年",A4)-1)/12

ではどうでしょうか？m（_ _）m

この回答へのお礼

tom04さん、有り難うございました。

端数切り上げの（入社日・退職日を含む）問題、解決しました。

もう一つ教えて下さい。
この時のＡ3セルの在職期間について、入社日の1日前からとする場合、式のどこに-1としたら良いのでしょうか？

金額計算について、「おそらく前回の結果を12で割ったもので計算すれば…」ご指摘の通りです。
お恥ずかしい限りですm(_ _)m　単純な事でした。難しく考えすぎていました。
丁寧なご指導有り難うございます。

お礼日時：2013/08/30 18:33

No.10 回答者： tom04 回答日時： 2013/08/30 20:45

No.6・9です。

＞入社日の1日前からとする場合、式のどこに-1としたら良いのでしょうか？
は前回の数式で示したように

DATEDIFの　A1　の部分を　A1-1　とするだけで1日前になり、
開始日（入社日）の1日前になります。

おそらくこれをA3セルに当てはめるとお望みの結果になるのではないでしょうか？
細かい検証はしていませんので、
ご希望通りにならなかったらごめんなさいね。m(_ _)m

この回答へのお礼

有り難うございました。
単純な質問ばかりですみませんでした。

お礼日時：2013/08/30 22:39