エクセル 期間内で一番多い月をカウントする方法

エクセルを使い、複数の項目に関係する期間で、一番多い月（または各月の重複数）を把握したいと思っています。

例）
　　開始月,終了月
A　2011/4,2011/7
B　2011/6,2011/12
C　2011/7,2011/10
の場合、4,5,11,12は1、6,8,9,10は2、7は3という感じです。
開始月と終了月は出ているのですが、ここから途中の月数のカウント・・・となると、
関数で出来るのかと悩みます。

アドバイスをいただけると助かります。

No.1 ベストアンサー 回答者： CoalTar 回答日時： 2011/09/16 11:39

F2セルに =SUMPRODUCT((B$2:B$4<=E2)*(E2<=C$2:C$4))

下へオートフィル 添付図参照

年月は書式設定 表示形式 ユーザー定義で
yyyy/m

この回答へのお礼

回答ありがとうございます。
そっか、こうシンプルに考えればいいんだな、と勉強になりました。

お礼日時：2011/09/16 13:24

No.5 回答者： imogasi 回答日時： 2011/09/16 14:35

質問を一見して判るように書いてほしい。

＞の場合、4,5,11,12は1、6,8,9,10は2、7は3という感じです。
始終わり４月５月６月７月８月９月１０月１１月１２月
2011年4月2011年7月1111
2011年6月2011年12月--1111111
2011年7月2011年10月---1111
112322211
のような例を挙げると良いのだ。最下行はΣで出す。
ーーー
こういう表を作ることが許されるなら
C1の4月は2011/4/1にして表示形式をm月、D1の５月は2011/4/1にして表示形式をm月、この料セルを範囲指定して＋ハンドルを引っ張ると見た目が上記のようになる。
C2の式は　=IF(AND(C$1>=$A2,C$1<=$B2),1,"")　
12月まで式を横方向に複写
C2:：K2の式をC4:K4まで式を縦方向に複写。
ーー
結果
上記と同じ
C2:K4を範囲指定してΣ
C5:K5の数の最大値の月（だい１行目）を見れば良い。関数式でもできるが、よく載っていることなので略。
具体的の上記のようなシート作成が許されない場合は式が複雑化する。
ーー
VBAで各行の期間に対し、第１行目の各列の月が含まれて居るかカウントするのは、ロジックが簡単。
ーー
上記データの合計月数は
C9セルに　=SUMPRODUCT(($A$2:$A$4<=C$1)*($B$2:$B$4>=C$1)*1)　と入れて右方向に式を複写すれば、
上記と同じく４－１２月は
112322211
となる。上記の表の１は作らなくても良い。

この回答へのお礼

少しわかりにくかったですね・・。
２０００ちょいのデータから、月毎の分析をするのに、一瞬ぽかんとして、悩んだ挙句質問となりました。
少しぼんやりしていたのかもしれません。
ご指摘ありがとうございます。

んー。みなさん、思い浮かぶのがすごいですねえ・・。

お礼日時：2011/09/16 18:59

No.4 回答者： MackyNo1 回答日時： 2011/09/16 14:08

No3の訂正と補足です。

数式でCOLUMN(A:X)とCOLUMN(A:Q)の部分がありますが、すべてCOLUMN(A:X)に統一してください。

また添付画像はF2セルが通常の日付形式で表示されていますが、元データと同じように、ユーザー定義で「yyyy/m」に設定してください。

また数式は開始時期が2011年1月から24か月間（2012年１２月まで）に設定してます。
もし、その開始時期や期間を変更したい場合は「DATE(2011,COLUMN(A:X),1)」の2011の部分で開始年を、COLUMN(A:X)の部分で期間を設定していますので、この部分を適宜変更してください（たとえば36か月間ならCOLUMN(A:AK)にしてください）。

この回答へのお礼

了解です。
ちょっと自分で試して、噛み砕かないと使いこなせなさそうですが（汗）

ありがとうございます。

お礼日時：2011/09/16 18:57

No.3 回答者： MackyNo1 回答日時： 2011/09/16 13:52

ご質問のタイトルのように、補助列などを使わずに、期間内で一番多い月をカウントするなら以下のような関数で表示できます。

最も多い月（F2セル）
配列数式なので、入力後Ctrl+Shift+Enterで確定してください。
=MODE(IF((DATE(2011,COLUMN(A:X),1)>=B2:B10)*(DATE(2011,COLUMN(A:X),1)<=C2:C10),DATE(2011,COLUMN(A:Q),1),""))



その回数（G2セル）
=SUMPRODUCT((INDEX((DATE(2011,COLUMN(A:Q),1)>=B2:B10)*(DATE(2011,COLUMN(A:Q),1)<=C2:C10)*DATE(2011,COLUMN(A:Q),1),)=F2)*1)

この回答へのお礼

ふむふむ、なるほど。
これもまた、今までの自分にはない関数ですね・・・。
勉強になります。ありがとうございます。

お礼日時：2011/09/16 18:56

No.2 回答者： kagakusuki 回答日時： 2011/09/16 12:29

　まず、E2セルとE3セルの書式設定の表示形式を[ユーザー定義]の

yyyy/m

として下さい。
　次に、E2セルに次の数式を入力して下さい。

=IF(COUNT($B:$B)=0,"",TEXT(MIN($B:$B),"yyyy年m月")+0)

　次に、E3セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($E2),MAX($E$1:$E2)<TEXT(MAX($B:$C),"yyyy/m")+0),DATE(YEAR(E2),MONTH(E2)+1,1),"")

　次に、F2セルに次の数式を入力して下さい。

=IF($E2="","",SUMPRODUCT((TEXT(INDEX($B:$B,ROW($B$1)+1):INDEX($B:$B,MATCH(9^9,$B:$B)),"yyyy/m")+0<=$E2)*((TEXT(INDEX($C:$C,ROW($B$1)+1):INDEX($C:$C,MATCH(9^9,$B:$B)),"yyyy/m")+0>=$E2)+(INDEX($C:$C,ROW($B$1)+1):INDEX($C:$C,MATCH(9^9,$B:$B))=""))))

　次に、F2セルをコピーして、F3セルに貼り付けて下さい。
　次に、E3～F3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

この回答へのお礼

おお、回答ありがとうございます。
なるほど、あまり知らない関数がありました。勉強になります。

お礼日時：2011/09/16 13:23