年月毎の集計の方法について。

質問

下記の様にシート1にデータがあり、シート2にシート1のデータをコード別、年月別に集計したいのですが、どの様な計算式を作成すればよいでしょうか？SUMPRODUCTを使っていますが、「シート1の年月日」と「シート2の年月日（1日付けにしています）」をどの様にマッチさせるのかが分かりません。ご教示お願いいたします。

シート1
　A(コード) 　B(年月日)　 C(金額)
1 001 　 　 2008/12/20 　 2,000
2 001　 　　 2009/1/20 　 1,000
3 003 　　 2009/12/25 　 3,000
4 003 　　 2009/2/10 　 6,000
5 002 　　 2009/1/10 　 3,000
6 001 　　 2009/1/10 　 2,000
7 003 　　 2009/2/25 　 1,000

シート2
　A(コード) 　　　B 　　　　 C　　　　　　　D
1 　　 2008/12/1 　 2009/1/1 　 2009/2/1
2 001 　　 2,000 　 3,000 　 0
3 002 　　 3,000 　 0 　 0
4 003 　　 3,000 　 0 　 7,000

回答No.1

tom04 · ベストアンサー

こんばんは！
参考になるかどうか分かりませんが・・・

↓の画像のように表をつくってみました。

コードNOはすでに入っているものとします。
結論として、年月日が入っているその月すべて（1か月分）を
合計すればいいわけですよね？
一応、そういうことだと解釈させてもらっての回答です。

Sheet2のB2セルに
=SUMPRODUCT((Sheet1!$A$2:$A$8=Sheet2!$A2)*(Sheet1!$B$2:$B$8>=Sheet2!B$1)*(Sheet1!$B$2:$B$8<=EOMONTH(Sheet2!B$1,0)),Sheet1!$C$2:$C$8)
という数式をいれて行・列方向へオートフィルでコピーすると
画像のような結果になります。

尚、質問ではSheet1に2008年の12月のデータは一つしかないので
質問内容の下側のような結果にはならないと思います。

そしてEOMONTH関数を使っていますので
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れておいてください。

以上、当方使用のExcel2003での回答になります。
参考になれば幸いですが
的外れなら軽く読み流してくださいね。m(__)m

お礼

ありがとうございました。
EOMONTH関数は初めて使います。
こちらの式で対応してみたいと思います。

回答No.3

cistronezk

シート１のD列を作業列として使う例です。

シート１のセルD2に以下の式を入れ、下へコピーします。
=CONCATENATE(A2,"_",TEXT(B2,"yyyy/m"))

シート２のセルB2に以下の式を入れ、全セルにコピーします。
=SUMIF(Sheet2!$D$2:$D$8,CONCATENATE($A2,"_", TEXT(B$1,"yyyy/m")),Sheet2!$C$2:$C$8)

以上です。式は、質問の例を基につくりました。実情に合わせて修正してください。

お礼

CONCATENATE(A2,"_",TEXT(B2,"yyyy/m"))
と計算式を入れるとコードと年月が表示されるのですね。
驚きでした。ありがとう御座いました。

回答No.2

KURUMITO

シート1では1行目が項目名で2行目からデータがあるとします。
シート2ではＢ1セルから右横方向に2008/12/1、2009/1/1などが入力されているとします。
Ａ2セルから下方にはコード番号があるとします。
Ｂ2セルには次の式を入力し右方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((YEAR(Sheet1!$B$2:$B$100)=YEAR(B$1))*(MONTH(Sheet1!$B$2:$B$100)=MONTH(B$1))*(Sheet1!$A$2:$A$100=$A2)*Sheet1!$C$2:$C$100))
この式ではシート1でのデータが100行までに対応しています。適宜修正してください。
なお、余談ですがシート2の1行目での日付ですが2008/12/1と入力しますが、セルの書式設定で表示形式のユーザー定義でyyyy年mm月として2008年12月と表示させるようにしてもよいですね。

お礼

ありがとうございました。
ご教示頂いた式でOKでした。
ただ計算式の長さが微妙ですが、No.1の方の式が短く済みそうでしたので、そちらを使ってみます。
また、年月表示の件もありがとう御座いました。