[[20100625115129]]　『期間の平均の求め方』（じょう）　ページの最後に飛ぶ

『期間の平均の求め方』（じょう）

[[20091025132009]]

[[20040522101025]]など見たのですが、わからないので教えてください。

    A     B   ･･･ D        E         F･     ･･･BD  BE   BF     BG
 1 No　 氏名　   始        終      年限　    　始  終  年限　 合計
 2 1　山田一郎 1974/4/1 1976/3/31 2年0ヶ月　　　            28年0ヶ月

 という表があります。
 (データは1000行以上あります。)

 それぞれの年限の式は（例えばセルF2の場合)
 =IF(E2="","0年0ヶ月",DATEDIF(D2,E2+1,"y") & "年" & MOD(DATEDIF(D2,E2+1,"m"),12) & "ヶ月")
 という式が入っています。

 最後のBG合計列の式は
 {=SUBSTITUTE(TEXT(SUM(IF(MOD(COLUMN(F5:BF5),4)=2, (0&SUBSTITUTE(SUBSTITUTE(F5:BF5,"年"," "),"ヶ月","/12"))+0)),"0年0/12"),"/12","ヶ月")}
 です。

 他人様の作成した表なのですが、
 この合計の平均を求めなければなりません。
 これだけ沢山の関数が重なっていると、どういう意味でどうさわっていいのかもわからず・・・。

 どのようにして平均を求めるのが一番簡単な方法なのでしょうか？

 G列、K列にはどんなデータが入っていますか？

 G、K列には施設名が入っています。

 施設名、(開)始(年月日)、終(了年月日)、(その期間の)年限
 の繰り返しで14セット、
 そしてBG列がその、１４セットの期間の年限の合計
 という風になっております。

 これで伝わってるでしょうか？

 (じょう)

 >施設名、(開)始(年月日)、終(了年月日)、(その期間の)年限
 >の繰り返しで14セット、

 年月日以外のところにはすべて文字列が入力されているというぜんていです。
 BH列に平均を求めることして、BI列を作業列にして見ました。

 BI2=SUM(IF(ISERROR(DATEDIF(D2:H2,(E2:I2)+1,"y")),0,DATEDIF(D2:H2,(E2:I2)+1,"y"))*12)
+SUM(IF(ISERROR(DATEDIF(D2:H2,(E2:I2)+1,"ym")),0,DATEDIF(D2:H2,(E2:I2)+1,"ym")))

 CtrlとShiftキーを押しながら、Enterで確定します。数式が{   }で囲まれます。

 BH2=INT(BI2/COUNT(D2:BF2)*2/12)&"年"&MOD(BI2/COUNT(D2:BF2)*2,12)&"ケ月"

 ちなみにこの作業列を利用すれば、合計も

 BG2=INT(BI2/12)&"年"&MOD(BI2,12)&"ケ月"

 で求められると思います。
 (sato)

 追記：開始と終了の日付によっては端数が出る場合もあるので注意（sato)

 すみません、範囲が違ってました。訂正します。(sato)

 =SUM(IF(ISERROR(DATEDIF(D2:BD2,(E2:BE2)+1,"y")),0,DATEDIF(D2:BD2,(E2:BE2)+1,"y"))*12)
+SUM(IF(ISERROR(DATEDIF(D2:BD2,(E2:BE2)+1,"ym")),0,DATEDIF(D2:BD2,(E2:BE2)+1,"ym")))

（sato)さま

 回答ありがとうございます。
 でもﾜﾀｼの説明が悪く伝わっていないようでした。

 1000件以上あるﾃﾞｰﾀの合計の、列の平均を求めたかったのです。
 説明不足でも申し訳ありませんでした。
 再度、列の平均を出す関数のご教示お願いいたします。

 ちなみに、（sato)さまにいただいた関数も使用してみました。
 しっかり行の平均出てます♪♪♪

 これ、いただきです♪しっかり使わせていただきます。

 (じょう)

 F列の平均は、

 =INT(SUM(DATEDIF(D2:D1000,(E2:E1000)+1,"m"))/COUNT(D2:D1000)/12)&"年"
&MOD(SUM(DATEDIF(D2:D1000,(E2:E1000)+1,"m"))/COUNT(D2:D1000),12)&"ケ月"

 で大丈夫でしょうか。前回同様、配列数式にします。
 なお、配列数式を多用すると重くなる（再計算に時間がかかる)
 場合がありますのでご注意下さい。
 (sato)

 各セットの【年限】はいただいたもので出来ますが、
 BG列の合計の場合はどうなるのでしょうか？

 いただいた式だと、【開始】と【終了】列から出すと言うことですよね？
 合計列は『各セットの年限の合計』というふうになっているようなので
 【開始】も【終了】もないのですが・・・。

 (じょう)

 BJ列なら先ほどのBI列の作業列を使って、

 =INT(AVERAGE(BI2:BI10)/12)&"年"&MOD(AVERAGE(BI2:BI10),12)&"ケ月"

 でいいかと。
 列はBI列は各行の始と終の差の合計を月数で表したものですから、
 それを応用します。(sato)

 F列の平均
 =INT(DATEDIF(SUM(D2:D1000),SUM(E2:E1000),"m")/COUNT(D2:D1000)/12)&"年"&MOD(INT(DATEDIF(SUM(D2:D1000),SUM(E2:E1000),"m")/COUNT(D2:D1000)),12)&"ケ月"
 Enterで確定   (NB)

 質問の条件が本当にそれでいいのか疑問が残りますがとりあえず

 >他人様の作成した表なのですが、
 >この合計の平均を求めなければなりません
 BG列の値の平均を求めるのですよね

 (sato) さんの回答　
 =INT(AVERAGE(BI2:BI10)/12)&"年"&MOD(AVERAGE(BI2:BI10),12)&"ケ月"
 がいいと思います

 質問者が提示した式を利用して

 =SUBSTITUTE(TEXT(SUM((0&SUBSTITUTE(SUBSTITUTE(BG2:BG1000,"年"," "),"ヶ月","/12"))/COUNTA(BG2:BG1000)),"0年0/12"),"/12","ヶ月")

 =SUBSTITUTE(TEXT(SUM((0&SUBSTITUTE(SUBSTITUTE(BG2:BG1000,"年"," "),"ヶ月","/12"))/999),"0年0/12"),"/12","ヶ月")

 >【開始】と【終了】列から出すと言うことですよね？
 >合計列は『各セットの年限の合計』というふうになっているようなので
 >【開始】も【終了】もないのですが・

 行ごとのデータ数が違ったり、端数処理（端数の日数を切り捨て）後の平均　を求めることに意義を感じません

 他の回答者が回答しているように、、元データの【開始】と【終了】を基に平均を求めた方がいいと思います

By

 皆様、ご回答ありがとうございます。

 今日は朝から他表作成で忙しく、まだこの表まで手が回らず
 詳しくは見ておりませんが・・・・。
 ぱっとコピーで式を入れたところ、うまくいったようです。

 他表作成終わり次第、この関数をじっくり勉強しようとおもいますので、
 また困った際にはよろしくお願いいたします。

 本当にありがとうございました。

 (じょう)

コメント返信：

[ 一覧(最新更新順) ]

[[20100625115129]] 『期間の平均の求め方』（じょう） ページの最後に飛ぶ

[[20100625115129]]　『期間の平均の求め方』（じょう）　ページの最後に飛ぶ