エクセルで複数の抽出条件から別シートに氏名を記載

質問

シート名「社員名簿」に個人情報のリフトがあります。別シートに抽出する条件を設定しておくと、名前のみが記載されているという計算式を教えて頂きたいです。

シート名:社員名簿
　A 氏名　
B　職場　←製造1課・製造2課・製造3課・製造4課・人事課・総務課・・・
C　役職　←社長・課長・係長・班長・空白は役職なし
D　男女別　男は1　女は2
E　雇用形態　←正社員1　準社員2　パート3　アルバイト4　派遣社員5
F　F以下は抽出条件でない社員情報
G
H
I

別のシートに抽出条件を設定すると氏名のみが記載されるようにしたいのです。
例えば
　A 製造1課の係長　正社員　男　の全て
B　製造1課の役職のついていない正社員の男全て
C　製造1課のパートの女

エクセル初心者で、上司から今週中に考えなさいと言われるものの相談する方がいなくて・・・
申し訳ないのですが、教えて頂きたく、お願い申し上げます。

回答No.7

MackyNo1

質問をよく見たら、「社員名簿」シートから名前だけを抽出すればよいのですね。

その場合は（名簿が1000行以内の場合）、以下の式を入力して（コピー貼り付けして）下方向にオートフィルコピーしてください。

=INDEX(社員名簿!A:A,SMALL(INDEX(($B$2&$C$2&$D$2&$E$2<>社員名簿!$B$2:$B$1000&社員名簿!$C$2:$C$1000&社員名簿!$C$2:$C$1000&社員名簿!$D$2:$D$1000&社員名簿!$E$2:$E$1000)*10000+ROW($A$2:$A$1000),),ROW(A1)))&""

回答No.6

imogasi

エクセルといえば関数と頭が行くようだが、エクセルの基本は操作です。
データーフィルタオプションの設定の操作を使ったことが無いのだろうが。
関数では抜き出しの課題は難しい。
そんなレベルのものが、既に出ている関数での回答の複雑な式が理解できるはずが無いように思う。
ーー
データーフィルタオプションの設定を使う場合、条件がある方のシートで操作を始める。
リスト範囲は　Sheet1（社員名簿）、条件はSheet2にあるとする。抽出結果もSheet2。
リスト範囲は社員名簿!A1:○○のようになる（他シートのセル範囲指定）
検索条件範囲はSheet2で項目見出しと条件内容を入れたセル範囲を指定。
「指定した範囲」と「抽出範囲」は、条件を入れたシートと同じシートを指定しないとダメなようだ（エクセルの制約）。
例データ　Sheet1
氏名 職場 職位
山田 営業 課長
佐藤 営業
近藤 営業
鈴木 営業
田中 総務 課長
田中真 営業 部長
ーー
Sheet2
条件
職場 職位
営業 課長
結果
Sheet2
氏名 職場 職位
山田 営業 課長
ーー
あえて関数でやる１方法として、自称imogasi方式
例データ
氏名 職場 職位
山田 営業 課長 1
佐藤 営業
近藤 営業
鈴木 営業
田中 総務 課長
田中真 営業 部長
木村 営業 課長 2
E3の式は　=IF(AND(B3=Sheet2!$A$2,C3=Sheet2!$B$2),MAX($E$1:E2)+1,"")
結果
上記の通り
やっていることは、該当分に連続番号を（上の行から）振っている。作業列を使う事になる。
ーー
別シートで例えばSheet3で
A2に　=INDEX(Sheet1!$A$1:$C$12,MATCH(ROW()-1,Sheet1!$E$1:$E$100,0),COLUMN())
右方向に式を複写
A2:C2の式を下方向に複写
結果　Sheet2のA2:C3
山田 営業 課長
木村 営業 課長
その他に注記する必要なこともあるが略（Googleで「imogasi方式」で照会すれば、関数を使う方法やその他の抜き出し問題の過去の質問回答例が判る）
（#N/A #N/A #N/A　を見えなくする式とか。）

回答No.5

MackyNo1

Sheet1のA2セル以下にデータベースのデータがあり(項目名が1行目）、別シートのB2セルから右方向に検索条件が入力されているなら、以下の数式を名前を表示するセルに入力して右方向および下方向にオートフィルコピーすればご希望のデータを表示することができます（添付画像参照）。

=INDEX(Sheet1!A:A,SMALL(INDEX(($B$2&$C$2&$D$2&$E$2<>Sheet1!$B$2:$B$1000&Sheet1!$C$2:$C$1000&Sheet1!$C$2:$C$1000&Sheet1!$D$2:$D$1000&Sheet1!$E$2:$E$1000)*10000+ROW($A$2:$A$1000),),ROW(A1)))&""

回答No.4

KURUMITO

次のようにしてはどうでしょう。
ここでは社員名簿のシートをシート１として、抽出の条件およびその該当者の名前を表示させるためのシートをシート２として述べます。。
シート１ではA１セルからI１セルまでに項目名が入力されていてそれぞれのデータが２行目以降に入力されているとします。
シート２ではA２セルに職場、A３セルに役職、A４セルに男女別、A５セルに雇用形態の文字列がそれぞれ入力されているとします。抽出の条件はA列の項目に対応したB列の行に入力することにします。もしも、それ以外の条件で抽出をするのであれば対応したC列の行にその条件を入力します。第３の抽出をする場合にはD列にその条件を入力します。横の列を利用することで抽出の条件をいろいろに変えた名簿を表示させることができます。
なお、それぞれの抽出の条件での該当者の名簿はそれぞれの列の７行目以降に表示させることにします。

初めに作業列を作って対応することとしてシート１のJ１セルにはシート２B列条件、H１セルにはシート２C列条件などと入力します。
J２セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(COUNTA(Sheet2!B$2:B$5)=0,"",IF(ASC(Sheet2!B$2&Sheet2!B$3&Sheet2!B$4&Sheet2!B$5)=ASC(IF(Sheet2!B$2<>"",$B2,"")&IF(Sheet2!B$3<>"",$C2,"")&IF(Sheet2!B$4<>"",$D2,"")&IF(Sheet2!B$5<>"",$E2,"")),MAX(J$1:J1)+1,""))

シート２では例えばB２セルには製造１課、B３セルには係長、B４セルには１、B５セルには正社員などと入力します。
B７セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR(ROW(A1)>MAX(Sheet1!J:J),COUNTA(B$2:B$5)=0),"",INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!J:J,0)))

抽出の条件をシート２の２行目から５行目の間で列を変えて入力することでその該当者を７行目以降に表示することができます。

回答No.3

Mingguwu

はじめまして。おはようございます。

＞例えば
　A 製造1課の係長　正社員　男　の全て
B　製造1課の役職のついていない正社員の男全て
C　製造1課のパートの女

このように複数の社員名を一発検索できれば良いと思うのですが、関数を使用しても複数の社員名を検索するのは難しいと思います。
そこで、提案ですが「フィルタ」を使用してはいかがでしょうか？

「Excel 2003」の場合
(1)．抽出したい項目
　B　職場
　C　役職
　D　男女別
　E　雇用形態　　を選択します。
(2)．メニューバーの「データ（Ｄ）」⇒「フィルタ（Ｆ）」⇒「オートフィルタ（Ｆ）」にチェックを入れます。
(3)．抽出したい項目横に表示されたマーク部をクリックして、抽出条件をクリックすると表示されます。
　（人事課と総務課のように複数選択する場合は、「オプション」をクリックし、条件を選択してください。）
(4)．抽出された社員名を選択し、ＣＯＰＹ⇒貼り付けをすればどうでしょうか？

「Excel 2007」の場合
(1)、(4)は2003に同じです。
(2)は、「ホーム」⇒「編集」⇒「並べ替えとフィルタ」⇒「フィルタ（Ｆ）」です。
(3)は、抽出したい項目横に表示されたマーク部をクリックして、抽出条件を選択し「ＯＫ」をクリックします。

それと、該当する社員が何名居るかは、「DCOUNT関数」「DCOUNTA関数」で求められます。

切貼りですがEXCEL画像添付します。

回答No.2

kagakusuki

回答番号ANo.1です。
　関数と作業列（補助的な計算処理を行うために使用する列）を使用して、別シートに抽出結果を表示させる方法に関しても、一応述べさせて頂きます。

　今仮に、Sheet1のA1セルに「氏名」、B1セルに「所属」、C1セルに「役職」、D1セルに「性別」、E1セルに「雇用形態」、と入力されていて、Sheet１の２行目以下には、各社員の各項目ごとのデータが入力されているものとします。
　そして、Sheet2のA1セルに「所属」、B1セルに「役職」、C1セルに「性別」、D1セルに「雇用形態」、A4セルに「氏名」と入力されていて、
Sheet2のA2セルに抽出する所属、B2セルに抽出する役職、C2セルに抽出する性別、D2セルに抽出する雇用形態、を入力すると、
Sheet3のA列を作業列として使用して、
Sheet2のA5以下に、抽出結果を表示するものとします。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(AND(OR(Sheet2!$A$2="",INDEX(Sheet1!$B:$B,ROW())=Sheet2!$A$2),OR(Sheet2!$B$2="",INDEX(Sheet1!$C:$C,ROW())=Sheet2!$B$2,AND(Sheet2!$B$2="役職なし",INDEX(Sheet1!$C:$C,ROW())="")),OR(Sheet2!$C$2="",INDEX(Sheet1!$D:$D,ROW())=Sheet2!$C$2),OR(Sheet2!$D$2="",INDEX(Sheet1!$E:$E,ROW())=Sheet2!$D$2)),ROW(),"")

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

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

=IF(ROWS($5:5)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,SMALL('Sheet3!$A:$A,ROWS($5:5))))

　次に、Sheet2のA5セルをコピーして、Sheet2のA6以下に貼り付けて下さい。

　後は、Sheet2のA2セルに抽出する所属、B2セルに抽出する役職、C2セルに抽出する性別、D2セルに抽出する雇用形態、を入力すると、Sheet2のA5以下に、抽出結果が自動的に表示されます。
　尚、上記の関数では、Sheet2のA2～D2の範囲に未入力のセルが存在すると、その欄の項目は抽出条件とはしないものとして、入力済みの条件にのみ合致する社員の氏名を表示する様になっています。
　例えば、性別を指定するC2セルに何も入力されていない場合には、男女両方が表示されます。
　そのため、Sheet１のリストにおいて、「役職」の欄に何も入力されていない役職の無い社員のみを抽出する場合に、Sheet２のC2セルに何も入力せずに抽出すると、役職の無い社員だけではなく、他の全ての役職の社員も抽出されてしまいます。
　ですから、「役職」の欄に何も入力されていないか、或いは「役職なし」と入力されている、社員のみを抽出する場合には、Sheet２のC2セルに「役職なし」（鉤括弧は不要）と入力する事で、抽出を行って下さい。

回答No.1

kagakusuki

別のシートではなく、同じシートで、抽出条件に合わない行を飛ばして表示させるのでは駄目なのでしょうか？
　Excelの[オートフィルター]と言う機能を使用すれば、同じシートを使用して（別シートに記載する事は出来ません）、条件に合うものだけを表示させる事が出来ます。
　オートフィルターの使い方に関しては、以下の参考URLを御覧下さい。
　尚、Excelのバージョンが、Excel2007以降のバージョンと、それよりも前のバージョンでは、操作方法が多少異なっていますので御注意下さい。

【参考URL】
　よねさんのWordとExcelの小部屋 > エクセル基本講座 > オートフィルタ(データ抽出)
　　http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter.htm

　よねさんのWordとExcelの小部屋 > エクセル2007基本講座 > オートフィルタの使い方
　　http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-filter.html