EXCELで複数条件に合致する全データの抽出方法

質問

任意のデータ（表）から条件に合致するデータをすべて抽出し、別表の任意の場所に表示する方法を教えていただけますでしょうか。
EXCELにあまり詳しくないので、マクロではなく関数での方法をお願いいたします。

＜原簿データ（Sheet1）＞
・講師の授業実績を日別に管理しているデータです。
・年間の日別データ（約500行）が一つの表に入っています。
・列（項目）は、これ以外にも10項目くらいあります。

　A　　　　　　　　B　　　　　C　　　　　D　　　　　　E
１　実施日　　　　曜日　　講師名　　授業時間　講師料
2　2012/8/30　　木　　　　中居　　　1　　　　　　2,000
3　2012/9/1　　　土　　　　中居　　　2　　　　　　4,000
4　2012/9/10　　月　　　　木村　　　1　　　　　　2,000
5　2012/9/14　　金　　　　中居　　　2　　　　　　4,000
6　2012/9/20　　木　　　　稲垣　　　2　　　　　　4,000
7　2012/9/30　　日　　　　中居　　　1　　　　　　2,000
8　2012/10/2　　火　　　　香取　　　2　　　　　　4,000

やりたいことは、上記データから、講師への講師料支払いの明細表を毎月、講師別に作成することです。
具体的には「実施月」と「講師名」を条件にして、一致するデータ（任意の項目のみ）をすべて別シートの支払明細表の任意の位置に挿入することです。
たとえば、支払明細表の任意の位置で「9月」、「中居」と入力した場合、原簿データの9月分の中居さんの全データを抽出し、「実施日」、「曜日」、「授業時間」を任意の位置の下方に表示するようにしたいです。

＜支払明細表＞
　A　　　　　　　　B　　　　　C　　　　　D　　　　　　E
1　【9月】　　　【中居】 ←実施月と講師名を入力
2　実施日　　　　曜日　　　授業時間
*******以下表示データ
3　2012/9/1　　　土　　　　　2
4　2012/9/14　　金　　　　　2
5　2012/9/30　　日　　　　　1

分かりにくいかもしれませんが、よろしくお願いいたします。

回答No.2

keithin · ベストアンサー

こんにちは。
初心者さんが一度はやってみたいと思いついて、出来ずに諦めるご相談の一種です。
エクセルには「条件に該当するデータを並べて表示する」関数はありません。

簡単な作成方法：
シート１に実施記録を用意する
シート２以降に次の通り作成する

Ｃ１セルに2012のように年を記入する
Ｄ１セルに9のように月を記入する
Ｅ１セルに講師名を記入する

Ａ１セルに「実施記録」と記入する
Ａ２に
=IF(AND(YEAR(Sheet1!A2)=$C$1,MONTH(Sheet1!A2)=$D$1,Sheet1!C2=$E$1),ROW(),"")
と記入、以下コピーしておく

Ｃ３セルに
=IF(ROW(C1)>COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROW(C1))))
Ｄ３セルに
=TEXT(C3,"aaa;;;")
Ｅ３セルに
=IF(ROW(C1)>COUNT(A:A),"",INDEX(Sheet1!D:D,SMALL(A:A,ROW(C1))))
とそれぞれ記入、以下コピーしておく。

何か条件を変えたいなら、Ａ列の条件式を適切に応用します。

＃補足
言わずもがなですがシート２のＡ列は別にＡ列にある必要はありません。
必要なら（目障りなら）Ｍ列でもＺ列でも、どこでも好きなところに配置して作成します。

補足

早速の回答ありがとうございます。
教えていただいたように数式を入力しているはずなのですが、どうもうまくいきません。
原簿データは月別にシートを分けたので、年と月の条件が必要ないため、講師名のみの条件に変更しました。
9月分の原簿データには、同一日、同一講師のデータが複数レコードづつ存在し、中居さんのデータは全部で60レコードあるのですが、9月1日のデータのみ（5レコード）しか表示されません。
その下のC3～D3は空白になります。
実施記録の列には、条件が一致したところの行番号が入るみたいですが、
25～28行までに25～28の数字入って、他は空白です。

C3のIF文の「ROW(C1)>COUNT(A:A)」ではじかれてしまっているのでしょうか。
そもそもこの式はどういう意味なのでしょうか。
それから、A2、C3、D3、E3の式の下方へのコピーは、抽出データ数が表示できる程度に適当でよいのでしょうか。
使ったことのない関数のため、質問ばかりで大変申し訳ございませんが、ご教示をお願いします。

回答No.3

keithin

再掲：
＞何か条件を変えたいなら、Ａ列の条件式を適切に応用します。

と回答して置いた通り、添付図のシート２のＣ３、Ｄ３、Ｅ３の数式は前回と全く同じです。

シート２のＡ２だけ修正：
=IF(Sheet1!C2=$E$1,ROW(),"")
以下沢山コピー

＞どこまでコピーしておいたらいいですか

最大限、つまり元表が仮にもしも「すべて中居さんだったら」、中居さんシートにはその行数だけデータを転記しなきゃなりませんよね。
現実にはそういう事は勿論ありませんが、「可能性として」そういう事があり得る限りは、元表の最大行数分だけ数式をコピーして備えておかなきゃいけません。

＃補足
同じ「つもり」、やった「はず」でも、勝手に間違ったやり方をしているから正しい答えが得られません。
まず「全く同じ」で作成して、キチンと出来ることを確認してください。
次のステップとして、何をやっているのかちゃんと理解して、それから実地に「同じつもり」でやり直してください。

＃そもそも
「全く同じ」で作成するとは、あなたがご相談にご自分で書いた通り、そしてわざわざ添付図まで付けて何行何列にどういう具合に記入するとご説明したその通りのレイアウトで、一回練習用にあなたもエクセルを作成してくださいという意味です。

そのあと実地でやってみたら失敗して、今は一体どこをどう直したらいいのか判らなくなったわけです。
それはつまり「例えば」で教われば、あとは自分で出来ると思ったのが間違いだったという事です。

なので今度は「ホントはこうなってます」「自分では教わった数式をこうしたらいいと思ってどこのセルにこう記入しました（けどこんな結果になってしまって上手くいきません）」というのをキチンと正しく詳しく具体的に目に見えるように情報提供して、このご相談は一回解決で閉じてから、もう一回新しいご相談として投稿し直してみてください。

お礼

ご回答ありがとうございました。
ご指摘のとおり、添付図と全く同じものを作成し、教えていただいたとおりにやってみました。
自分では正しく入力したつもりですが、間違っているものですね。
何度か修正しているうちに、やっと添付図の例がうまくいきました。
これを応用して、実際のデータでやってみましたが、これもうまくいきました。
やはり一度正しい結果がでる例でやってみてから、理解し実施をすべきでした。
おかげさまで新しい関数等も勉強になり、完全ではありませんが、だいぶ理解できた気がします。
本当にありがとうございました。

回答No.1

bin-chan

「ピボットテーブル」がご希望の結果に近いかも。