DATEIF関数の算出結果の合計平均を出す方法の件

質問

品名No.毎の平均期間の算出を希望致します。

収穫日(Ｄ列)と購入日(Ｃ列)から期間を算出し(算出結果：Ｅ列)、
算出結果(Ｅ列)を活かし、品名No.毎の期間平均の算出を希望しております。

結果が下記のようになります。
000101⇒平均期間(0年0１ヶ月)
000103⇒平均期間(0年0１ヶ月)
000104⇒平均期間(2年１0ヶ月)

Ｅ列に入っている関数は下記になります。
=(DATEDIF(D4,C4+1,"Y")+(DATEDIF(D4,C4+1,"YM")=11))&"年"&MOD(DATEDIF(D4,C4+1,"YM")+ 　 (DATEDIF(D4,C4+1,"MD")>0),12)&"ヶ月"

　Ａ列　　　Ｂ列　　　Ｃ列　　　　　　Ｄ列　　　　　　Ｅ列　　
品名NO 商品名 購入日 　　収穫日　　　　期間
000101 みかん 2005/10/30 2005/10/01 0年1ヶ月
000101 みかん 2005/12/20 2005/12/01 0年1ヶ月
000101 みかん 2005/12/19 2005/12/01 0年1ヶ月
000101 みかん 2006/01/11 2006/01/01 0年1ヶ月
000101 みかん 2006/01/11 2006/01/01 0年1ヶ月
000101 みかん 2006/11/02 2006/11/01 0年1ヶ月
000101 みかん 2006/11/02 2006/11/01 0年1ヶ月
000101 みかん 2006/11/06 2006/11/01 0年1ヶ月
000103 ばなな 2007/10/25 2007/10/01 0年1ヶ月
000103 ばなな 2007/10/25 2007/10/01 0年1ヶ月
000103 ばなな 2007/10/25 2007/10/01 0年1ヶ月
000103 ばなな 2008/08/22 2008/07/01 0年2ヶ月
000103 ばなな 2010/11/18 2010/11/01 0年1ヶ月
000103 ばなな 2010/12/22 2010/12/01 0年1ヶ月
000103 ばなな 2004/12/27 2004/12/01 0年1ヶ月
000103 ばなな 2005/01/12 2005/01/01 0年1ヶ月
000103 ばなな 2010/12/01 2010/11/01 0年2ヶ月
000104 りんご 2003/10/08 2001/07/01 2年4ヶ月
000104 りんご 2004/06/01 1998/04/01 6年3ヶ月
000104 りんご 2005/09/01 2000/04/01 5年6ヶ月
000104 りんご 2005/12/14 2005/12/01 0年1ヶ月
000104 りんご 2005/12/19 2005/12/01 0年1ヶ月

フィルタをかけて抽出していけば良いのですが、数が大量すぎて手作業では
膨大な時間がかかってしまいます。

本当に分からなくご質問した次第です。
よろしくお願い致します。

回答No.1

MackyNo1 · ベストアンサー

商品番号がK２セルに入っているなら、以下の式を入力すれば（E列の数式の算出方法に準じた計算）K2セルの平均が算出できます。
この場合、E列は不要で直接平均年月が表示できます。

=INT(SUMPRODUCT(($A$4:$A$1000=K2)*((DATEDIF($D$4:$D$1000,$C$4:$C$1000+1,"Y")+(DATEDIF($D$4:$D$1000,$C$4:$C$1000+1,"YM")=11))*12+MOD(DATEDIF($D$4:$D$1000,$C$4:$C$1000+1,"YM")+(DATEDIF($D$4:$D$1000,$C$4:$C$1000+1,"MD")>0),12)))/12/COUNTIF($A$4:$A$1000,K2))&"年"&INT(MOD((SUMPRODUCT(($A$4:$A$1000=K2)*((DATEDIF($D$4:$D$1000,$C$4:$C$1000+1,"Y")+(DATEDIF($D$4:$D$1000,$C$4:$C$1000+1,"YM")=11))*12+MOD(DATEDIF($D$4:$D$1000,$C$4:$C$1000+1,"YM")+(DATEDIF($D$4:$D$1000,$C$4:$C$1000+1,"MD")>0),12)))/12/COUNTIF($A$4:$A$1000,K2)),1)*12)&"ヵ月"

お礼

ありがとうございました。
提出する資料が無事作成出来ました。
私にはまだ解読出来ませんが１つ１つ解読して別の場面でも活かしていきたいと思います。
本当に助かりましたありがとうございます!!

回答No.3

mt2008

G2:G4に品名NOを入れておきます。
で、H2に以下を……

=INT(SUMPRODUCT((DATEDIF(D$2:D$23,C$2:C$23,"M")+1)*(A$2:A$23=G2))/COUNTIF(A$2:A$23,G2)/12)&"年"&INT(MOD(SUMPRODUCT((DATEDIF(D$2:D$23,C$2:C$23,"M")+1)*(A$2:A$23=G2))/COUNTIF(A$2:A$23,G2),12))&"ヶ月"

なお、平均で小数点以下の月数が出た場合は切捨てています。

お礼

画像まで添付して頂きご丁寧な回答ありがとうございます。
この方法も今後活かしていきたいと思います。
本当にありがとうございました!!

回答No.2

shinkami

エクセルでは日付はシリアルNo.という整数です。（時分秒は少数）
日付どうしの引き算で期間が求められます。
求めた結果は表示形式を数値にすると日数で、
日付にすると年月日の表示になりますが 大の月、小の月で紛らわしいですね

お礼

私も最初その方法で行ったのですが○○年12ヶ月という変な表示になってしまったりしたので、諦めました;
ご回答ありがとうございます。