エクセルで複数条件で別シートに抽出する方法を教えてください。

質問

エクセルで複数条件で別シートに抽出する方法を教えてください。

sheet1にデータ
氏名　年齢　　通算勤務年数
鈴木　47 歳　　　20 年
福田　46 歳　　　24 年
横山　46 歳　　　20 年
大高　35 歳　　　17 年

sheet2に抽出後のデータ
35歳かつ通算勤務年数10年以上の職員
大高　35 歳　　　17 年
46歳以上かつ通算勤務年数20年の職員
横山　46 歳　　　20 年
としたいのですが，抽出する方法を教えてください。

回答No.7

tom04

No.5です！
前回の数式はE2セルに入力した数値以上かつ、F2セルに入力した数値以上のデータを表示する数式でしたので、
E2セルの数値でかつ、F2以上の場合は下記の数式に変更してみてください。
Sheet1の作業用の列の数式の不等号の部分が一つ不要になるだけです。

Sheet1のD2セルに
=IF(COUNTBLANK(Sheet2!$E$2:$F$2),"",IF(AND(B2=Sheet2!$E$2,C2>=Sheet2!$F$2),ROW(),""))

これを下へオートフィルでコピー！

Sheet2の数式はそのままでOKです。

以上、何度も失礼しました。m(__)m

回答No.6

imogasi

複数条件による抜き出しは、過去にも最近も週に何回かぐらい出てくる。常連読者回答者には飽き飽きしたテーマ。
Googleで「imogasi方式」で照会すれば、同類の質問が沢山出てくる。（もちろん私の答えてないこれ以外の同類質問も沢山在るだろう）。
それらの回答は（１）（２）（３）のタイプが多い。
（１）フィルタオプションの設定
（２）作業列を使う関数式による方式（imogasi方式）
（３）作業列を使わない　　関数式による方法
（４）VBAでプログラムを組む。やり方も数種あろう。
　　　SQLを用いてプログラムを組む。
（５）MSクエリデーター外部データの取り込みー新しいデータベースクエリ
（６）その他
のやり方を思いつく。
（１）（２）が判りやすいのではないかな。

回答No.5

tom04

こんばんは！
すでに回答は出ていますので、参考程度です。

↓の画像で説明します。
Sheet1に作業用の列を設け、Sheet2には年齢と勤続年数を入力するセルを設けています。

Sheet2の年齢・勤続年数を入力するとその数値以上の人のデータが表示されるようにしています。

Sheet1の作業列D2セルに
=IF(COUNTBLANK(Sheet2!$E$2:$F$2),"",IF(AND(B2>=Sheet2!$E$2,C2>=Sheet2!$F$2),ROW(),""))
という数式を入れ、オートフィルでずぃ～～～！っと下へコピーします。

そして、Sheet2のA2セルに
=IF(COUNT(Sheet1!$D:$D)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$D:$D,ROW(A1))))

という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
これで、E2・F2セルにデータを入力するたびにデータが表示されると思います。

以上、参考になれば幸いです。m(__)m

お礼

ご指導ありがとうございました。

付け加えて質問なのですが，46歳で「かつ」20年以上で抽出したい場合は，どうなりますでしょうか。
鈴木は，抽出したくないので。
よろしくお願いします。

回答No.4

mu2011

一例です。
フィルタオプションの設定から抽出する方法は如何でしょうか。
（１）35歳かつ17年以上
　　・sheet2にA1に「年齢」、A2に=35、B1に「通算勤務年数」、B2に>=10を入力
　　・フィルタオプションの設定で「指定した範囲」を選択、
　　　リスト範囲欄にSheet1!$A:$C、検索条件範囲欄に$A$1:$B$2、抽出範囲欄にA4

（２）46歳以上かつ20年以上
　　・sheet2にA2に>=46、B2に>=20を入力、以降は（１）と同様

回答No.3

MackyNo1

条件が変動するデータを抽出したいならフィルタオプションの設定を利用します。

Sheet2のE1:F2セルに以下のようなリストを作成しておきます。

年齢　通算勤務年数
>=46　　　　20

Sheet2から開始して「データ」タブの「詳細設定」をクリックし（Excel2003なら「データ」「フィルタ」「フィルタオプションの設定」）、リスト範囲にSheet1のリスト範囲（データの追加に対応できるようにデータ範囲を大きめに設定しておくとよい）を指定し、検索条件範囲に「E1:F2」、抽出先にA1セルを指定しOKします。

これでE2セルとF2セルに記載した条件のデータが抽出されます。

この操作を「マクロの記録」で記録すれば、検索条件を変更して、このマクロを実行すれば一発でこの条件に合致するデータを抽出することができます。

回答No.2

KURUMITO

回答Ｎｏ1です。
46歳以上かつ通算勤務年数20年の職員
となれば横山さんばかりでなく鈴木さんも対象になりますね。

回答No.1

KURUMITO

作業列を作って対応するのが判り易く的確な方法でしょう。
シート1では1行目に項目名があり2行目から下方にデータがあるとします。
Ｅ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(B2=35,C2>=10),MAX(E$1:E1)+1,"")

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

=IF(AND(B2>=46,C2=20),MAX(F$1:F1)+1,"")

シート2ではＡ1セルに次の文字を入力します。

35歳かつ通算勤務年数10年以上の職員

Ｅ1セルには次の式を入力します。

46歳以上かつ通算勤務年数20年の職員

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

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

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

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