【EXCEL2003】期間中の月単価額を該当する月別に集計したい

質問

複数のシートにA列に総額、B列に開始日、C列に終了日、D列にBとCから算出した月数、E列にA列÷D列で算出した月単価のデータが入っています。
別の「集計用」シートのA列に年月のセルがあるのですが、B列に該当する月の合計額が自動的に反映されるするにはどうしたら良いのでしょうか。
ご教授いただければ幸いです。

例）
複数シート
　A列　B列　C列　D列　E列
　100,000　2009/1/15　2009/4/8　4ヶ月　25,000
　 45,000　2009/2/3　2009/6/30　5ヶ月　9,000
別シート（集計用）
　A列　B列
　2009年1月(2009/1/1)　25,000
　2009年2月(2009/2/1)　34,000

どうぞよろしくお願いいたします。

回答No.3

CMLT · ベストアンサー

>質問なのですが、数式にある「集計月シリアル値」とはなにを入れればよいのでしょうか？

日付入力しているセルを指定してあげればOKのはずです。

お礼

CMLT様

再度の回答ありがとうございました。
無事算出できました！！
元々の月単価セルにエラーが出ていたため、教えていただいた式は正しいのに結果が出ていないだけでした。

本当に感謝いたします。

回答No.2

CMLT

条件付き集計を取るのはそれほど難しくはありませんが、
その月に含む含まないの基準は、月がかかっていればいいだけですか？
一つのシートの複数行ならたとえば
=SUMPRODUCT((B:B<DATE(YEAR(集計月シリアル値),MONTH(集計月シリアル値)+1,1))*(C:C>=集計月シリアル値)*(E:E))
の様な集計方法があります。
複数シートそれぞれを集計して足すか、データを一つのシートに整理するか、元を一つのシートにして、条件で複数シートに振り分けるか・・・色々な方法があるかとは思いますが。

補足

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

質問なのですが、数式にある「集計月シリアル値」とはなにを入れればよいのでしょうか？
2009/1/1ですとシリアル値は「39814」となるのですが、それを入れたり集計月セルを入れても「#NUM!」と表示されてしまいます。。。
お手数ですが、ご教授願います。

回答No.1

hallo-2007

複数のシートですよね、集計シートB列複数シートの合計でしょうか？
この辺りが厄介です。
複数シート
　A列　　　B列　　　C列　　　　D列　　E列
　年月　　合計

　総額　　　開始日　　終了日　　期間　　月単価
　100,000　2009/1/15　2009/4/8　4ヶ月　25,000
　 45,000　2009/2/3　2009/6/30　5ヶ月　9,000
といった具合に、複数シートの上部に集計部分を入れておいて
B2セル以下は　
=SUMIF(B$10:B$1000,"<"&A3,E$10:E$1000)-SUMIF(B$10:B$1000,A2,E$10:E$1000)
下コピィとかで、シート単位で集計しておいて。
集計のシートには串刺し合計しては如何でしょうか。

補足

回答どうもありがとうございました。
ただ実際計算してみると終了日を過ぎた月でも月単価が加算されてしまうようです。