Excelで、複数条件で抽出した複数データを別シートに出力したい

Excelで、複数条件で抽出した複数データを別シートに出力したい

sheet1のデータから、sheet2に地区と班を指定してデータを抽出したいのです。
関数でなんとか出来るでしょうか？


sheet1
A B C D
地区班番氏名
111岩井 雅功
112湯浅 将也
113滝沢 由宇
121根本 さやか
122近藤 守
123永島 ひろみ
124小峰 遥

sheet2
A B C D 　　E　　F
区班番氏名区 班
111岩井 雅功11
112湯浅 将也
113滝沢 由宇

No.2 ベストアンサー 回答者： KURUMITO 回答日時： 2010/11/04 07:21

分かりやすい方法はシート1に作業列を作って対応することでしょう。

シート1のＥ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(A2=Sheet2!F$2,B2=Sheet2!G$2),MAX(E$1:E1)+1,"")

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

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

この方法の場合にはSUMPRODUCT関数の場合と比べて、データの入力範囲に制限が無く計算が重くなることもないでしょう。

この回答へのお礼

どうもありがとうございます！

とても丁寧に式まで載せていただいて助かりました。

チラ見ではよく理解できないので、よく見て勉強させていただきます！

データの入力範囲に制限が無く計算が重くなることもないというのは

ありがたいです。

心より感謝申し上げます。m(__)m

お礼日時：2010/11/04 22:55

No.7 回答者： kagakusuki 回答日時： 2010/11/04 11:24

　Sheet1のリストの編集で、切り取りや削除を行う場合にも対応し、且つ、C列の番号が順不同に並んでいたり、欠番がある場合にも、番号順に表示される方法です。

　まず、使用していないSheet（ここでは仮にSheet3とします）のA1セルに次の数式を入力して下さい。

=IF(AND(INDEX(Sheet1!$A:$A,ROWS($1:1))=Sheet2!$F$2,INDEX(Sheet1!$B:$B,ROWS($1:1))=Sheet2!$G$2),INDEX(Sheet1!$C:$C,ROWS($1:1)),"")

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に、Sheet1のリストの行数を上回るのに充分な行数となるまで、貼り付けて下さい。

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

=IF(ROWS($1:1)>COUNT(Sheet3$A:$A),"",SMALL(Sheet3$A:$A,ROWS($1:1)))

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

=IF($C2="","",$F$2)

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

=IF($C2="","",$G$2)

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

=IF($C2="","",Sheet1!$D:$D,MATCH($C2,Sheet1!$C:$C,0)))

　次に、Sheet2のA2～D2の範囲をコピーして、同じ列の3行目以下に、Sheet1のリストの行数を上回るのに充分な行数となるまで、貼り付けて下さい。

　以上で準備は完了で、後はSheet2のF2セルに地区を、Sheet2のG2セルに班を入力すると、Sheet2のA列～D列に、抽出されたデータが、番号順に表示されます。

この回答へのお礼

どうもありがとうございます！

とても丁寧に式まで載せていただいて助かりました。

sheetをもう一つ使うのですね。

切り取りや削除を行う場合にも対応し、且つ、C列の番号が順不同に並んでいたり、欠番がある場合にも、

番号順に表示される、というのは大変ありがたいです。皆関数がいろんなところを参照してるというのを

あまり理解せずにジャンジャンいじるので困ります。

チラ見ではよく理解できないので、よく見て勉強させていただきます！

大変参考になりました。心より感謝申し上げます。m(__)m

お礼日時：2010/11/04 23:16

No.6 回答者： MackyNo1 回答日時： 2010/11/04 11:11

配列数式を使用すると再計算に時間がかかりシートの動きが重くなるので、表示データ数が多いとあまりお勧めできませんが、以下のような数式の方が計算負荷が少なそうです。

たとえば、以下のような関数を入力し、右方向に５つ下方向に適当数オートフィルすればご希望のデータを表示できます。

=INDEX(Sheet1!A:A,SMALL(INDEX(ISERROR(FIND($F$2&"-"&$G$2,Sheet1!$A$2:$A$1000&"-"&Sheet1!$B$2:$B$1000))*1000+ROW($A$2:$A$1000),),ROW(A1)))&""

数式が多いと計算負荷が高くなることを考慮すると、検索条件のA列とB列は表示する必要が無いので実用的にはC列とD列を表示する数式だけでよいと思います。
どうしても必要なら、D列のデータが「""」でない場合は「F2」セルを返すような数式にしてください。

また、上記の数式は数字データを文字列として表示していますので、数字データとして表示したい場合は、数式の最後の「＆""」を削除して、セルの書式設定をユーザー定義にして、「0;;;@」と入力して「０」を表示しない設定にしてください。

この回答へのお礼

どうもありがとうございます！

とても丁寧に式まで載せていただいて助かりました。

なるほど、C列とD列を表示する数式だけにすることで負荷を軽くできるのですね。

&の使い方も初めて知りました。

チラ見ではよく理解できないので、よく見て勉強させていただきます！

大変参考になりました。心より感謝申し上げます。m(__)m

お礼日時：2010/11/04 23:12

No.5 回答者： tom04 回答日時： 2010/11/04 10:12

こんにちは！

すでに回答は出ていますので、参考程度で・・・

↓の画像のようにSheet1に作業用の列を設けています。

作業列E2セルに
=IF(A2&B2=Sheet2!$F$2&Sheet2!$G$2,ROW(),"")
という数式を入れ、フィルハンドルの（＋）マークでダブルクリック、またはオートフィルで下へずぃ～～～！っとコピーしておきます。

そして、Sheet2のA2セルに
=IF(COUNT(Sheet1!$E:$E)<ROW(A1),"",INDEX(Sheet1!$A:$D,SMALL(Sheet1!$E:$E,ROW(A1)),MATCH(A$1,Sheet1!$A$1:$D$1,0)))
という数式を入れ、オートフィルで列方向と行方向にコピーすると
画像のような感じになります。

以上、参考になれば良いのですが・・・m(__)m

この回答へのお礼

どうもありがとうございます！

とても丁寧に式まで載せていただいて助かりました。

細かい操作まで教えていただいてありがたいです。

関数の使い方もいろいろあるのですね。

チラ見ではよく理解できないので、よく見て勉強させていただきます！

大変参考になりました。心より感謝申し上げます。m(__)m

お礼日時：2010/11/04 23:09

No.4 回答者： imogasi 回答日時： 2010/11/04 10:01

エクセルについて、関数しか知らず、関数ならやさしいと思っているように見えるが、層ではない。

エクセルの関数は、条件に夜抽出には不向きだ。
ーー
エクセルを少し経験すれば
フィルタオプションの設定
ピボットテーブル
と言う便利な、ソフト（メニューになっているが）が付いていることを知るはず。
其れでやるのが速く確実。
ーーー
同じことを繰り返すなら、マクロの記録を採って、条件部分の変え方を勉強し、実行させる手もある。
ーー
関数では条件付抜き出しの問題は
Ｇｏｏｇｌｅででも、「imogasi方式」で照会すれば、沢山の質問と回答例があり、「imogasi方式」以外の解き方も載っている。
私のimogasi方式でやれば、（作業列を使うが、式が比較的理解しやすいかと思う）本件の場合の作業列の式は
例データＡ－Ｄ列（ ソートされている必要はない）
地区班番氏名作業列
1 11岩井雅功1
1 12湯浅将也2
1 13滝沢由宇3
1 21根本さやか
1 22近藤守
1 23永島ひろみ
1 24小峰遥
作業列Ｆ列のＦ２セルに
=IF(AND(A2=1,B2=1),MAX($F$1:F1)+1,"")　　地区＝１、班＝１の抜き出しの場合
と入れて下方向に式を複写
その後別シートに該当データを持ってくる式は、上記ＷＥＢを見てください。

この回答へのお礼

どうもありがとうございます！

imogasi方式で検索して結構読ませていただきましたが、見た中で条件2つ以上のものが見つけられず、

しかも応用力もないもので、こちらで質問させていただきました。

ソートされている必要はないのですね。

チラ見ではよく理解できないので、よく見て勉強させていただきます！

心より感謝申し上げます。m(__)m

お礼日時：2010/11/04 23:06

No.3 回答者： mshr1962 回答日時： 2010/11/04 08:11

関数に縛られないなら、下記の方法があります。

1.「ピボットテーブル」を使用
http://www.officepro.jp/exceltips/pivot/

2.「フィルタオプションの設定」を使う。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filte …

この回答へのお礼

どうもありがとうございます！

実は使い手が、あまり慣れてない人で、ゆくゆくはピボットテーブルや

フィルタオプションの設定なども覚えてもらおうと思っていますが、

とりあえずは区と班を入力すればドンと出る方式にしようと思っています。

参考URL、よく読ませていただきます。

お礼日時：2010/11/04 23:01

No.1 回答者： KURUMITO 回答日時： 2010/11/04 06:58

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

=IF(SUMPRODUCT((Sheet1!$A$1:$A$1000=$F$2)*(Sheet1!$B$1:$B$1000=$G$2)*(Sheet1!$C$1:$C$1000=ROW(A1))*ROW(A$1:A$1000))=0,"",INDEX(Sheet1!$A$1:$D$1000,SUMPRODUCT((Sheet1!$A$1:$A$1000=$F$2)*(Sheet1!$B$1:$B$1000=$G$2)*(Sheet1!$C$1:$C$1000=ROW(A1))*ROW(A$1:A$1000)),COLUMN(A1)))

この回答へのお礼

どうもありがとうございます！

とても丁寧に式まで載せていただいて助かりました。

チラ見ではよく理解できないので、よく見て勉強させていただきます！

心より感謝申し上げます。m(__)m

お礼日時：2010/11/04 22:53