エクセル メンバー表からグループメンバーを抽出

質問

縦に名前が並び、その横の列に男女、その横の列にＡ・Ｂ・Ｃの3グループが入力されている表があります。
その表からＡグループ・Ｂグループ・Ｃグループのメンバーを抽出して各グループのメンバー表を作成したいです。

条件として…
(1)今後メンバーが増える可能性がある。
(2)メンバーの所属グループは変更になる。
(3)グループの人数構成はバラバラである。
(4)メンバー表の名前は上から縦に並べたい。
(5)メンバー表はそれぞれ別シートに作成したい。
(6)ＡＢどちらかに所属する男性のみ（女性のみ）のグループのメンバー表も作成したい。

毎日作成するので日々の手間を最小限にしたいです。
関数等を利用して一発で表作成することはできないでしょうか？

よろしくお願いします。

回答No.6

hallo-2007 · ベストアンサー

関数案については、既に回答が出ていますが
さて、提示された関数をご理解できましたでしょうか。
条件が変更されたときに、ご自身で式を変更するには
意外と、VBA以上の知識が必要な位複雑な式です。
別案ですが、フィルターオプション　の機能を紹介しておきます。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm
のサイトを参考にしてください。
機能を理解できれば、色々な応用が可能です。
毎日、実行するのであれば、一度、マクロの記録を実行すればよいです。
記録される内容は、ほんの１行ですので、VBAを理解するのも楽です。

お礼

皆さん回答ありがとうございます。
関数で、と思っていたのですが想像以上に複雑な式でよく理解できませんでした。

マクロをしてみたいと思います。

回答No.5

MackyNo1

配列数式になりますので表示するセルが多いと、再計算に時間がかかり重くなるのであまりお勧めしませんが、以下のような関数で該当データの名前を表示することができます（元データがSheet1にある場合）。

＞Ａグループ・Ｂグループ・Ｃグループのメンバーを抽出して各グループのメンバー表を作成したいです。
（Aグループを抽出する場合）

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

＞ＡＢどちらかに所属する男性のみ（女性のみ）のグループのメンバー表も作成したい。

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

回答No.4

tom04

No.1です！
補足に
＞今後なんらかのエラーが生じた時にリカバリーできないのではと・・・
とありましたが、今回の場合は仮に不具合が出た場合はコードを作成している訳ではありませんので、
もう一度マクロの記録をとれば大丈夫だと思います。

ただ関数での方法をご希望のようなので↓の画像のような方法ではどうでしょうか？
（画像が小さくて見にくいかもしれません）
Sheet1が入力Sheetで、Sheet2～Sheet4が「A～C」のSheet・Sheet5が最後の条件のSheetとします。

Sheet1に作業用の列を4列設けます。
E2セルに
=IF(OR($A2="",$C2<>E$1),"",ROW())
という数式を入れ、G2セルまでオートフィルでコピー！
H2セルには
=IF(OR(A2="",Sheet5!$A$1=""),"",IF(AND(C2<>"C",B2=Sheet5!$A$1),ROW(),""))
という数式を入れ、E2～H2を範囲指定しH2セルのフィルハンドルでずぃ～～～！っと下へコピー！

次にSheet2～Sheet4をグループ化（各SheetのA1セルにはグループ名を入力しておきます）
（Sheet2を開きShiftキーを押しながらSheet4のSheet見出しをクリックこれでSheet2～Sheet4がグループ化されます）

Sheet2のA4セルに
=IF(COUNT(OFFSET(Sheet1!$D:$D,,MATCH($A$1,Sheet1!$E$1:$G$1,0),,1))<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(OFFSET(Sheet1!$D:$D,,MATCH($A$1,Sheet1!$E$1:$G$1,0),,1),ROW(A1))))
という数式を入れ列方向と行方向にオートフィルでコピー！
これでSheet2～Sheet4には各グループごとの表示ができているはずです。

Sheet5だけは数式が変わります。（A1セルには条件を入力）
Sheet5のA4セルに
=IF(COUNT(Sheet1!$H:$H)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$H:$H,ROW(A1))))
という数式を入れ列方向と行方向にコピー！

これで画像のような感じになります。

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

回答No.3

KURUMITO

回答No2です。
メンバー表をシート2に作成するとしたらNo2で回答した表の作成の部分でG列からS列までの項目など、1行目と2行目の部分はそのままコピーしてシート2のA1セルに貼り付けます。
その後にA3セルには次の式を入力してF3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ISERROR(INDEX(Sheet1!$A:$B,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000+ROW(A1),Sheet1!$D:$D,0),MOD(COLUMN(A1)-1,2)+1)),"",INDEX(Sheet1!$A:$B,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000+ROW(A1),Sheet1!$D:$D,0),MOD(COLUMN(A1)-1,2)+1))

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

=IF(ISERROR(INDEX(Sheet1!$A:$A,MATCH(ROUNDUP(COLUMN(A1)/2,0)*10000+(MOD(COLUMN(A1)-1,2)+1)*1000+ROW(A1),Sheet1!$E:$E,0))),"",INDEX(Sheet1!$A:$A,MATCH(ROUNDUP(COLUMN(A1)/2,0)*10000+(MOD(COLUMN(A1)-1,2)+1)*1000+ROW(A1),Sheet1!$E:$E,0)))

これでシート1で新たにデータが追加される、または変更されることが有っても瞬時にシート2の表に反映されます。

回答No.2

KURUMITO

シートのA1セルに名前、B1セルに性別、C1セルにグループ名の項目名がそれぞれあり、データは2行目以降に入力されているとします。
そこで作業列を作って対応することにします。
D2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(C2="A",1,IF(C2="B",2,IF(C2="C",3,0)))*1000+COUNTIF(C$2:C2,C2))

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

=IF(A2="","",IF(C2="A",1,IF(C2="B",2,IF(C2="C",3,0)))*10000+IF(B2="男",1,IF(B2="女",2,0))*1000+COUNTIF(E$1:E1,">="&IF(C2="A",1,IF(C2="B",2,IF(C2="C",3,0)))*10000+IF(B2="男",1,IF(B2="女",2,0))*1000)-COUNTIF(E$1:E1,">="&IF(C2="A",1,IF(C2="B",2,IF(C2="C",3,0)))*10000+IF(B2="男",1,IF(B2="女",2,0))*1000+999)+1)

そこでお求めの表ですが各グループのメンバー表をG列からL列を使って表示させることにします。
例えばG1セルにはA、H1セルにはグループ、I1セルにはB、J1セルにはグループ、K1セルにはC、L1セルにがグループとそれぞれ文字列を入力します。またG2セルには名前、H2セルには性別、と繰り返しながらL2セルまで入力します。
G3セルには次の式を入力してL3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ISERROR(INDEX($A:$B,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000+ROW(A1),$D:$D,0),MOD(COLUMN(A1)-1,2)+1)),"",INDEX($A:$B,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000+ROW(A1),$D:$D,0),MOD(COLUMN(A1)-1,2)+1))

各グループで男女を分けた表ですがN列からS列を使って表示することにします。
N1セルにはA、O1セルにはグループ、P1セルにはB、Q1セルにはグループ、R1セルにはC、L1セルにはグループと入力します。N2セルには男、O2セルには女、これを繰り返してL2セルまで入力します。
N3セルには次の式を入力してL3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ISERROR(INDEX($A:$A,MATCH(ROUNDUP(COLUMN(A1)/2,0)*10000+(MOD(COLUMN(A1)-1,2)+1)*1000+ROW(A1),$E:$E,0))),"",INDEX($A:$A,MATCH(ROUNDUP(COLUMN(A1)/2,0)*10000+(MOD(COLUMN(A1)-1,2)+1)*1000+ROW(A1),$E:$E,0)))

回答No.1

tom04

こんにちは！
＞毎日作成するので日々の手間を最小限にしたいです・・・

とありますので、
一番簡単なのは最初に「マクロの記録」をとっておき、データ入力後マクロを実行する方法だと思います。

Sheet1にデータを入力し、別Sheetにデータを表示するとします。
当方使用のExcel2003での操作方法です。

メニュー　→　ツール　→　マクロ　→　「新しいマクロの記録」を選択し、マクロ名は好みの名前にしておきます。

Sheet1のA列～最終列を範囲指定（今度データが増える可能性があると思いますので、列すべてを範囲指定しておきます）

メニュー　→　データ　→　オートフィルタ　→　それぞれの条件でフィルタをかけます　→　
範囲指定されている列内で右クリック　→　コピー　→　表示したいSheetのA1セルを選択　→　貼り付け

この操作を表示したいSheet数だけ行います。
最後にSheet1のオートフィルタを解除（データ　→　フィルタ　→　「オートフィルタ」をもう一度クリック）
これで　ツール　→　マクロ　→　「記録終了」　で完了です。

後はSheet1のデータ変更があるたびにマクロを実行すればOKだと思います。
（Alt＋F8キー　→　マクロ　→　先ほどのマクロを実行　です）

尚、Excel2007以降のバージョンでは
リボンの「開発」　をクリックすると同様の操作でできると思います。

どうしても関数での方法がご希望であればごめんなさいね。m(_ _)m

補足

回答ありがとうございます。
マクロ使用も考えたのですが、職場にマクロに詳しい人間がおらず、最初の設定はできても今後なんらかのエラーが生じた時にリカバリーできないのではと心配に思い、実施しませんでした。