経過年月の平均を求めたい

質問

基準日を2008年6月1日とし、
各人、入社後の経過年月をDATEDIF関数で求めました。

式：
DATEDIF(H5,$M$3,"Y")&"年"&DATEDIF(H5,$M$3,"YM")&"ヶ月"
セルにはX年XXヶ月と表示されます。

ここから、全社員の平均経過年月を出そうとすると、
文字列のため数値を読みません。

次に、
式：
DATEDIF(H5,$M$3,"D")
セルにはXXXXと、数字のみ表示されます。

で日数表示し、平均経過年月を出すと、１ヶ月多く出てしまいます。
例：
4475日　→　12年3ヶ月のころ、12年4ヶ月と表示されてしまうのです。
というのも、セルの表示形式をユーザー設定「yy"年"mm"ヶ月"」で表示するようにしているからです。
アバウトな数字ではなく、きちんと計算できる何かいい方法はないでしょうか。
宜しくお願いします。

回答No.1

shimix · ベストアンサー

>日数表示し、平均経過年月を出すと、１ヶ月多く出てしまいます。

日数から年月への変換はどうされていますか？「yy"年"mm"ヶ月"」だとおかしいような・・。

＃「yyyy"年"mm"ヶ月"」にしてみるとわかりますが、1912年4ヶ月じゃないですか？

本来はDATEDIF(H5,$M$3,"M")で月数を求めておいて、その平均を12で割った商と剰余にするのがスジだと思います。

　　=INT(AVERAGE(K1:K5)/12)&"年"&MOD(AVERAGE(K1:K5),12)&"ヶ月"

お礼

ありがとうございます。
確かにyy"年"mm"ヶ月"だと西暦に変換されてしまいましたが、
２，３セルをひろって試したとき、実際の数字と大差なかったんです。
（もちろん、100近いセルの平均を出すと全く違う数字がでてしまいました。）

教えていただいた関数で、問題解決ができました。
ちなみに端数を切り下げる為、ROUNDDOWNと組み合わせました☆

回答No.5

shimix

ANo.1です。

他の方の回答にもあるように「日数」を求めてしまうと、1900/01/01の経過日数による年月日になります。なので「月数の平均」という要求であれば、ANo.1で書いたように各個人別の月数を求めるべきだと思います。入社年月日が違えば日数が同じでも経過月数が違うこともあるわけですから。

で　月数の平均→年と月にばらす　が普通じゃないかと思います。

お礼

再度、ありがとうございました。
教えていただいたやり方で、納得のいく答えが得られました！
関数やマクロ、書式設定などエクセルで出来る範囲を考えるだけじゃなく、
いかに簡潔にできるか、その考え方も磨いていくのも今後の題です・・・

回答No.4

noname#79209

#3です。
多くの方が誤解しているのですが、エクセルには日付、時刻の概念はあっても、
長さとしての年、月、日、時間の概念はありません。
書式設定で、「yyyy」「mｍ」「dd」は1900/1/1からの累計日数を日付として表示させているだけです。

4475を何年何ヶ月で表示するには365で割ったりその余りを出すしかありませんが、
閏年、大の月、小の月をどう考えるかを決めないと出せませんね。
日数で出さずに年*12＋月で月単位で計算させる方が良いかもしれませんね。

お礼

おっしゃるとおり、年・月・日・時間の概念がないんですね・・
それに見合う関数がないか、と思って質問しました。
閏年まで正確に、となると、
今回他の方に答えていただいた関数でも狂いが出てきまね。
エクセルに前述の概念があればいいのですが・・！
ともあれ、ありがとうございました。

回答No.3

noname#79209

> 4475日　→　12年3ヶ月のところ、12年4ヶ月と表示されてしまうのです。
これは当たり前のはなしで、4475は1912/4/1を示すシリアル値として働いているからです。
これを西暦年の下二桁と月で表示させているので、仕方ありません。

補足

そうなんです。
が、西暦と月が反映されているといえども、
正解に近い年数と月数が出るので、
アバウトな数字ならこれでいいかとも思ったのですが・・・
西暦と経過年月の数字が近いことが驚きです。

一度、皆さんが言われているような処理をやってみます。

回答No.2

mu2011

例えば、H5:H100を従業員範囲とすれば、SUMPRODUCT(DATEDIF($M$3,H5:H100,"Y"))で年数
SUMPRODUCT(DATEDIF($M$3,H5:H100,"YM"))で月数を計数できます。
よって、平均年月は、INT(全年数/従業員数)&"年"&INT(全月数/従業員数)&"月"ではだめでしょうか。
因みに従業員数はCOUNT(H5:H100)でカウントできます。

お礼

ありがとうございます。
セルを別にすると、平均で多少の誤差が生じてしまう為、
今回はNO.1の方の関数を使用します。

SUMPRODUCTを知らなかったので、今後の参考に勉強させていただきます☆