Excel 『3つの条件があったら同じ行のセル値を反映（重複あり）』

質問

Excel 『3つの条件があったら同じ行のセル値を反映（重複あり）』

宜しくお願いします。
データーを他のセルにいかに反映させるかで困ってます。

Sheet1
Content 　　　Code　　　　　　　　Day 　　　　　　money
撮影　　　　　　　A　　　　　　　 2010/6/12 　　　　1,000
イラスト　　　　　B 　　　　　　　2010/8/20　　　　　 2,000
イラスト　　　　　B　　　　　　　 2010/08/22 　　 　　3,000

上記のようなデータが500件ほどあります。
それを他のSheet

Sheet2
Code 　 Content 　 　　 2010/06　 　 2010/07 　 2010/08 …
A　　　　　 撮影　　　　 　 　 1,000
B 　　　　 イラスト　　　　　　　　　　　　　　　　　　　　　　5,000

上記のように横に年月が伸びている表に金額部分を月ごとに反映させたいのです。
また、Code と Content と Day(月が重複)の3点が重複した場合、
Paymoney を合計して反映させたいのですが、やり方がわからず困っています。

ご教授、宜しくお願いします。

回答No.2

MackyNo1 · ベストアンサー

Sheet2のC2セルに以下の式を入力して右方向および下方向にオートフィルしてください。

=SUMPRODUCT((Sheet1!$A$2:$A$500=$B2)*(Sheet1!$B$2:$B$500=$A2)*(DATE(YEAR(Sheet1!$C$2:$C$500),MONTH(Sheet1!$C$2:$C$500),1)=C$1)*Sheet1!$D$2:$D$500)

お礼

MackyNo1　さん

さっそく試した所、希望の物が出来ました！

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

回答No.3

KURUMITO

コードの種類が増加した場合や入力の日付が変わっていく場合でもそれらの変化に対応した表を作るためには多少操作が面倒になりますが次のようにすればよいでしょう。
シート１のＥ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(B$2:B2,B2)=1,MAX(E$1:E1)+1,"")

これで新たな種類のコードが入力されればＥ列には新たな番号が付加されます。
次にコードと日付(入力されている日付の月の初日)を組み合わせたものをＦ列に表示させるためにＦ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",B2&DATE(YEAR(C2),MONTH(C2),1))

そこでお求めの表ですがシート２のＡ１セルにはCode、Ｂ１セルにはContentと入力します。
Ｃ１セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(DATE(YEAR(MIN(Sheet1!$C:$C)),MONTH(MIN(Sheet1!$C:$C))+COLUMN(A1)-1,1)>MAX(Sheet1!$C:$C),"",DATE(YEAR(MIN(Sheet1!$C:$C)),MONTH(MIN(Sheet1!$C:$C))+COLUMN(A1)-1,1))

これで入力されているデータの最も古い日付のものから最新の日付までが付きの初日の日付で表示されます。１行目については選択後にセルの表示形式で日付の中から年および月だけの表示に変更します。

次にＡ２セルには次の式を入力したのちにＢ２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet1!$E:$E),"",IF(COLUMN(A1)=1,INDEX(Sheet1!$B:$B,MATCH(ROW(A1),Sheet1!$E:$E,0)),IF(COLUMN(A1)=2,INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!$E:$E,0)),"")))

これで表１に新しいコードが入力されれば自動的に追加して表示されます。
次にＣ２セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR($A2="",C$1=""),"",SUMIF(Sheet1!$F:$F,$A2&C$1,Sheet1!$D:$D))

該当する月で、各コードごとの集計金額が表示されます。

この方法を用いることでシート２には日付やコード名を手入力するなどの操作は一切ありません。例えばそれまでにはなかった古い日付のデータがシート１に入力された場合でも自動的に対応できます。シート２でＣ１セルに2010/06などとあらかじめ入力しておく必要もありません。

お礼

KURUMITO　さん

すごいですね、入力の手間も省けるなんて！

さっそく試してみます。

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

回答No.1

tom04

こんばんは！
一例です。
↓の画像で説明させていただきます。
Sheet1に作業用の列を2列設けています。

作業列E2セルに
=IF(C2="","",MONTH(C2))
F2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(),"")
という数式をいれ、E2・F2セルを範囲指定し、F2セルのフィルハンドルで下へずぃ～～～！っとコピーしておきます。

そして、Sheet2の1行目は各月の1日（1日でなくてもかまいません）のシリアル値を入れ
セルの書式設定の表示形式からユーザー定義で
yyyy/mm　としておきます。

A2セルに
=IF(COUNT(Sheet1!$F:$F)<ROW(A1),"",INDEX(Sheet1!B:B,SMALL(Sheet1!$F:$F,ROW(A1))))
B2セルに
=IF(A2="","",INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0)))
という数式をいれ、A2・B2セルを範囲指定し、B2セルのフィルハンドルで下へコピー

最後にC2セルに（セルの書式設定から表示形式は標準にしておきます）
=IF(OR($A2="",C$1=""),"",SUMPRODUCT((Sheet1!$B$2:$B$1000=$A2)*(Sheet1!$E$2:$E$1000=MONTH(C$1))*(Sheet1!$D$2:$D$1000)))

として列方向にずぃ～～！っとコピー

最後にA2～最後の列の2行目までを範囲指定し、最後の列のフィルハンドルで下へコピーすると
画像のような感じになります。
尚、数式は元データの1000行目まで対応できるようにしています。
画像で「0」が表示されていますが、これが目障りであれば
当方使用のExcel2003の場合は
メニュー　→　ツール　→　オプション　→　「表示タブ」で「ゼロ値」のチェックを外せばOKです。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

お礼

tom04さん

回答ありがとうございます。
図入りでとてもわかりやすいです。

本当にありがとうございます！