【エクセル】従業員リストから複数条件を満たすものを抽出したいです。

エクセルに詳しい方教えて下さい！

あるシート（「集計用」シート）には全従業員の「事業部」「グループ」「雇用形態」「名前」「アドレス」が記載されたデータがあります。
この「集計用」シートのデータから別のシート（「抽出用」シート）に行ごと抽出したいのですが、条件が1つではなく複数条件になります。
例えば、
「事業部」　→　大阪
「グループ」　→　開発
「雇用形態」　→　正社員
を満たす従業員を「抽出用」シートに関数で該当する行を丸ごと上から順に詰めて抽出したいと思っています。
この例の場合は添付画像の「佐々木真理子」「佐藤加奈子」が該当します。

フィルタで抽出してしまえば済んでしまう話なのですが、当方従業員の入退社又は部署の移動が多く、その都度データ（「集計用」シートと「抽出用」シート）を更新するのは手間が掛かり、ミスが生まれる可能性あるため、「集計用」シートを更新すれば、自動的に「抽出用」シートの内容が更新されるといった仕組みを作りたいと思っています。
（新たに入社した人は「集計用」シートの1番下に追加していき、退社した人は行ごと削除し、所属が変わった場合は「集計用」シートの該当部分を変更するようにしています。）


抽出用シートのＡ４セルに以下の式を入れて、Ｅ４セルまでコピーして、Ａ４～Ｅ４のセルを下の行にコピーをするしたところ、2名分は上手く抽出できるのですが、「グループ」と「雇用形態」がうまく条件に合致せずにどう修正してよいのか分かりません。

=IF(COUNTIFS(データ!$A$2:$A$201,$A$2,データ!$B$2:$B$201,$B$2,データ!$C$2:$C$201,$C$2)<ROW(A1),"",INDEX(データ!A$2:A$201,SMALL(IF(データ!$A$2:$A$201=$A$2,ROW(データ!A1:A200)),ROW(A$1))))

どこをどのように直せばよいか、または上記の条件を満たす式を教えて頂けると大変助かります。
どうぞよろしくお願いいたします。

No.5 ベストアンサー 回答者： yuji3690 回答日時： 2017/01/29 16:09

その場合補助セルとしてG列にA&Cを表示させ、

抽出シートのF2もA&Cにします。
抽出シートのF4〜には
=SUMPRODUCT((データ!$G$2:$G$201=$F$2)*1,(COUNTIF(INDIRECT("データ!$G$2:$G"&ROW(データ!$G$2:$G$201)),$F$2)=ROW()-ROW($A$3))*1,ROW(データ!$G$2:$G$201))
でどうでしょうか？
データシート参照のFのみGに変更しています。
A4〜E13？は、Fの結果を利用しているので修正不要です。

この回答へのお礼

なるほど、新たにＧ列を使えば事足りたるんですね。
上記方法で試したところ、上手く修正が出来さらに管理しやすくなりました。

一連の分かりやすいご説明大変助かりました。
お陰様で全ての問題を解決することが出来ました。
ご親切にどうもありがとうございました。

お礼日時：2017/01/30 00:11

No.4 回答者： yuji3690 回答日時： 2017/01/29 14:40

とりあえず補助セル込みのバージョンを記載しておきますね。

データシートのF2〜201=A2&B2&C2
抽出シートのF2=A2&B2&C2
抽出シートのF4〜13？=SUMPRODUCT((データ!$F$2:$F$201=$F$2)*1,(COUNTIF(INDIRECT("データ!$F$2:$F"&ROW(データ!$F$2:$F$201)),$F$2)=ROW()-ROW($A$3))*1,ROW(データ!$F$2:$F$201))
抽出シートのA4〜E13？=IF($F4>0,INDIRECT("データ!"&ADDRESS($F4,COLUMN())),"")

でどうでしょうか？
最初のものより軽くなってると思うのですが…
エラーは表示されません。
ただ、抽出シートのF列（該当する行を表示している）が0になります。
Fを非表示にするか、
F=IF(式=0,"",式)にして、A〜Eの中の>0を<>""にして下さい。
重さを軽減を重視して、前者（非表示の方）をお勧めします。

この回答へのお礼

早速、補助セル込みのバージョンで試してみたところ劇的に処理が早くなりました。
また、行削除と挿入の問題も一緒に解決出来、
非常に使い勝手の良いファイルにすることが出来ました。
行き詰っていたので大変助かりました。
ありがとうございます！

今回のケースではＡ列Ｂ列Ｃ列を満たすものの抽出でしたが、
例えば、Ａ列とＣ列のみ満たす場合は上記の補助セル込みバージョンですと、
どのように数式を組めばよろしいのでしょうか？
実は、20シート中1シートのみがその対象となるのですが、
その対象となるデータシートの行のＦ列に既に
=A2&B2&C2
と入力しており、データシートと抽出用シートのF列の数式の「&B2」の部分だけ削除することが出来ず、最初に紹介して頂いた方法（No.1でご回答頂いた方法）のままで運用しています。
もしこれが解消されればさらに処理が早くなると思うのですが、
何か良い方法はないでしょうか？
（とはいえ、現状でもかなり処理が早くなっているので満足しております）

お礼日時：2017/01/29 15:36

No.3 回答者： yuji3690 回答日時： 2017/01/29 11:06

200^4は試行錯誤中の式でした(汗)

書き込んだ式は、「データの一致するセルで、上からN(行番号に応じて変化)番目のもの、その行番号」で200^3でしたね。
その行番号に一致する、データのシートの対応する列、にあるデータを取得しています。

行の挿入削除の件も含め確認してみますね。

この回答へのお礼

夜分遅い中ご回答いただきありがとうございました。
No.2のご回答と合わせて返信させていただきます。

＞　もし、データシートのF列等に補助のセルを作ってよいのなら、そのセルにABCの3列を連結させたものを表示させることで、ABC列が全て一致するかどうかを一度に計算できます。
＞　つまり200*200*200*200を200*200で済ませられます。
＞　補助セルを追加することは問題ないでしょうか？

こちらは問題ありません。
仰る通り、列を非表示にするなどして対応できますので。
簡略化出来るのであれば願ったり叶ったりです。


＞　行の挿入削除の件も含め確認してみますね。
ご親切にありがとうございます。
お恥ずかしい話ですが、式の構造を理解出来ていないので下手に削除したり挿入したりして、復元出来なくなった場合にどこが原因なのかわからないため、出来れば修正出来ない状況を避けたいと思っています。

お礼日時：2017/01/29 14:23

No.2 回答者： yuji3690 回答日時： 2017/01/29 10:36

寝る直前でしたので説明が雑になって申し訳なかったです。

この関数は配列を使っています。
今回約200行のデータをAが一致しているか、Bが一致しているか、Cが一致しているか、行番号は何か、という4つの条件で掛け算し、合計を集める。という計算が行われています。
つまり200*200*200*200個の式が1つのセルに入っているようなものなので、それが10行？×20シートとなれば、やはり重くなってしまいます。
もし、データシートのF列等に補助のセルを作ってよいのなら、そのセルにABCの3列を連結させたものを表示させることで、ABC列が全て一致するかどうかを一度に計算できます。
つまり200*200*200*200を200*200で済ませられます。
補助セルを追加することは問題ないでしょうか？

No.1 回答者： yuji3690 回答日時： 2017/01/29 03:10

抽出用のA4に入れる式が

=INDIRECT("データ!"&ADDRESS(SUMPRODUCT((データ!$A$2:$A$201&データ!$B$2:$B$201&データ!$C$2:$C$201=$A$2&$B$2&$C$2)*1,(COUNTIFS(INDIRECT("データ!$A$2:$A"&ROW($A$2:$A$201)),$A$2,INDIRECT("データ!$B$2:$B"&ROW($B$2:$B$201)),$B$2,INDIRECT("データ!$C$2:$C"&ROW($C$2:$C$201)),$C$2)=ROW()-ROW($A$3))*1,ROW($A$2:$A$201)),COLUMN()))

でどうでしょう？～E4にコピーして、行ごと必要な行数コピーで。
該当がない場合エラーになるので、
=IF(ISERROR(式)=TRUE(),"",式)
の式の部分に最初に書いた式の=より後を入れれば、エラーの時空白。という式にもできます。

式の中身については必要であれば追記します。
1つのセルに全部まとめてつっこむ為に長くなってしまっているので、説明も長くなりますが…

こういう場合は補助用の列を作って、そのセルを参照しながらやる方が、中身を分割できてミスも減りますし、後で修正する時もわかりやすいですよ。
表示させたくなければ列ごと非表示にすれば済みますし

この回答へのお礼

早急なご回答ありがとうございます。
早速、記載の通り式を組み直したところ、まさにこういう関数を組みたいと思っていたものでしたので、非常に助かりました。
ありがとうございます！

＞　=IF(ISERROR(式)=TRUE(),"",式)
＞　の式の部分に最初に書いた式の=より後を入れれば、エラーの時空白。という式にもできます。

こちらの方法を採用しました。
エラー時に空白にすることによって見栄えも良くなりましたし、元となるデータシートに1人追加すると、その人が空白行に自動的に反映されるようになりましたので、至れり尽くせりな式でした。
ただ、数式がとてつもなく長くなってしまったのと、複数のシート（20シートくらい）にデータを抽出したためか、データの読み込みが重くなってしまったのが玉にキズでした。
（私が我慢すればいいだけの話ですが、もし他にこれ以上に巧いアイディアはあれば取り入れたいと思っています。）

また、元データの誰か1人を行ごと削除したり、ある行とある行の間に1行挿入したりすると、抽出したシートの他の人全てが空白行になってしまいました。
（そういう仕様なのでしょうか？）

お礼日時：2017/01/29 05:49