エクセルで関数の検索方法について

質問

エクセルの関数を利用した検索方法について分からないところがありますので教えてください。
キャンプの参加者リスト作成中です。
　Ａ１、Ａ２・・・には参加者の会員番号の数字が入っています。Ｂ１、Ｂ２・・・には氏名が入っています。Ｃ１、Ｃ２・・・にはキャンプの班名（Ａ、Ｂ・・）が入っています。
　次に、会員番号を入力し、ＶＬＯＯＫＵＰ関数で氏名（Ｄ１セル）及び班名（Ｄ２セル）を表示させます。仮にこの人の班名がＡ班だとすると、この人以外のＡ班の人の氏名をＥ１、Ｅ２・・・に表示させるにはどうしたらいいでしょうか。

関数の詳しい方よろしくおねがいします！！

もし関数では厳しいようならＶＢＡでもいいです。

回答No.4

kagakusuki · ベストアンサー

回答:No.3です。

　済みません、A列に存在しない会員番号が入力された場合において、エラーを表示させない様にするために、先ほどの回答の中のF2セルに入力する関数は、その一部を変更して、次の様なものとして下さい。

=IF(OR($D$2="",ROWS($2:2)>COUNTIF($C:$C,$D$2)-1),"",INDEX($A:$A,MATCH($D$2,INDEX($C:$C,IF(MATCH($D$2,INDEX($C:$C,MATCH(F1,$A:$A,0)+1):INDEX($C:$C,ROWS($A:$A)),0)+MATCH(F1,$A:$A,0)=MATCH($D$3,$A:$A,0),MATCH($D$3,$A:$A,0),MATCH(F1,$A:$A,0))+1):INDEX($C:$C,ROWS($A:$A)),0)+IF(MATCH($D$2,INDEX($C:$C,MATCH(F1,$A:$A,0)+1):INDEX($C:$C,ROWS($A:$A)),0)+MATCH(F1,$A:$A,0)=MATCH($D$3,$A:$A,0),MATCH($D$3,$A:$A,0),MATCH(F1,$A:$A,0))))

　同様に、「この人も含めたA班の全員の氏名を」という条件における、F2セルに入力する関数は次の様になります。

=IF(OR($D$2="",ROWS($2:2)>COUNTIF($C:$C,$D$2)),"",INDEX($A:$A,MATCH($D$2,INDEX($C:$C,MATCH(F1,$A:$A,0)+1):INDEX($C:$C,ROWS($A:$A)),0)+MATCH(F1,$A:$A,0)))

お礼

早速のご回答ありがとうございました！
参考にさせていただきます。

補足

すいません、教えていただいた素晴らしい回答を実践してみたのですが、すべての行に関数を入れてしまうと、ファイルが重くなってしまって動きが遅くなってしまいました。
　というのも、データ行自体が５万行あるんです。行すべてにコピーせずに、いくつかのセルだけで処理できませんか？
お忙しい中申し訳ありませんが、よろしくお願いします。

回答No.6

kagakusuki

回答:No.4です。

>行すべてにコピーせずに、いくつかのセルだけで処理できませんか？

　回答:No.3、4の関数の場合は、5万行全てにコピーされる必要は御座いません。
　最も人数が多い班のメンバー全員を表示出来るだけの行数があれば十分です。
　但し、将来的に最も人数の多い班の人数がどの位まで増えるのかが不明の場合には、余裕を持って、行数を多目に用意しておかなければなりません。

　後、あくまで念の為に申し上げておきますが、もしもの話ではありますが、同じ会員番号を持つ会員が複数人数おられる場合には、この方法では正しい結果を表示する事は出来無い場合が御座いますので御注意願います。

お礼

ありがとうございました。できました！
とても助かりました(*^_^*)

回答No.5

KURUMITO

会員番号や氏名、班名はそれぞれA,B,C列の２行目から下方にあるとします。
D２セルには会員番号を入力することでD３セルには該当の氏名をD４セルには班名を表示させるため次の式をD３セルに入力しD４セルまでドラッグコピーします。

=IF(COUNTIF(A:A,D$2)=0,"",VLOOKUP(D$2,A:C,ROW(A1)+1,FALSE))

E２セルから下方に該当する班で他の人の氏名を表示させるとして、そのための作業列をF列に作ります。F２セルには次の式を入力して下方にドラッグコピーします。

=IF(C2=D$4,IF(B2<>D$3,MAX(F$1:F1)+1,""),"")

作業列が目障りでしたらF列を選択して右クリックし「非表示」を選択すればよいでしょう。
E２セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(F:F),"",INDEX(B:B,MATCH(ROW(A1),F:F,0)))

お礼

早速のご回答ありがとうございました！
参考にさせていただきます。

補足

すいません、教えていただいた素晴らしい回答を実践してみたのですが、すべての行に関数を入れてしまうと、ファイルが重くなってしまって動きが遅くなってしまいました。
　というのも、データ行自体が５万行あるんです。行すべてにコピーせずに、いくつかのセルだけで処理できませんか？
お忙しい中申し訳ありませんが、よろしくお願いします。

回答No.3

kagakusuki

>Ａ班の人の氏名をＥ１、Ｅ２・・・に表示させる

だけでしたらまだ良かったのですが、

>この人以外の

という条件が付けられているが故に、相当長ったらしい関数となりました。

　まず、A1セルに「会員番号」等の何らかの文字列を入力して下さい。
　次に、D1セルに次の関数を入力して下さい。

=IF($D$3="","",IF(COUNTIF($A:$A,$D$3),VLOOKUP($D$3,$A:$B,2,FALSE)&"","(該当者なし)"))

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

=$A$1

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

=IF(COUNTIF($A:$A,$D$3),VLOOKUP($D$3,$A:$C,3,FALSE)&"","")

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

=IF(ROWS($2:2)>COUNTIF($C:$C,$D$2)-1,"",INDEX($A:$A,MATCH($D$2,INDEX($C:$C,IF(MATCH($D$2,INDEX($C:$C,MATCH(F1,$A:$A,0)+1):INDEX($C:$C,ROWS($A:$A)),0)+MATCH(F1,$A:$A,0)=MATCH($D$3,$A:$A,0),MATCH($D$3,$A:$A,0),MATCH(F1,$A:$A,0))+1):INDEX($C:$C,ROWS($A:$A)),0)+IF(MATCH($D$2,INDEX($C:$C,MATCH(F1,$A:$A,0)+1):INDEX($C:$C,ROWS($A:$A)),0)+MATCH(F1,$A:$A,0)=MATCH($D$3,$A:$A,0),MATCH($D$3,$A:$A,0),MATCH(F1,$A:$A,0))))

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

=IF(OR($F2="",COUNTIF($A:$A,$F2)=0),"",VLOOKUP($F2,$A:$B,2,FALSE)&"")

　次に、E2～F2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。

　因みに、

>この人以外のＡ班の人の氏名を

という条件ではなく、「この人も含めたA班の全員の氏名を」という条件であれば、F2セルに入力する関数を次の様に多少短いものとする事が出来ます。

=IF(ROWS($2:2)>COUNTIF($C:$C,$D$2),"",INDEX($A:$A,MATCH($D$2,INDEX($C:$C,MATCH(F1,$A:$A,0)+1):INDEX($C:$C,ROWS($A:$A)),0)+MATCH(F1,$A:$A,0)))

お礼

早速のご回答ありがとうございました！
参考にさせていただきます。

図まで用意していただきわざわざご丁寧にありがとうございました。

回答No.2

akki-8888

先程の回答者です。返信遅れてごめんなさい。
寝る前にエクセル弄って、イメージ通りに作ってみました。

A～Eまでは記述通りの仕様です。

★D列
D1⇒番号手入力、D2⇒名前反映、D3⇒班名反映
★E列
※⇒最後に説明
(以下参照用)
★F列(F2)
　=INDEX(B1:B$XX(最終行),MATCH(D$3(班名参照),C1:C$XX(最終行),0))
　※下の行まで引っ張ります　index(cの全体班名)　から　match(D3の班名)　を検索して返します
★G列(G2)
　=IF(COUNTIF(F$2:F2,F2)=1,ROW(),"")
　※下の行まで引っ張ります　左の参照結果から重複数字を割り出します
★H列(H2)
　=IF(D$2=F2,"",G2)
　※下の行まで引っ張ります　検索対象を左の列から除外します
★I列(I2)
　=IF(H2>0,H2,"")
　※下の行まで引っ張ります　余分なデータを削除します
(ここまでで、F列からI列は非表示にしてもOKです)

最後に、
★E列(E2)
　=IF(COUNT(I:I)<ROW(A1),"",INDEX(F:F,SMALL(I:I,ROW(A1))))
　※下の行まで引っ張ります　I列に数値があるもののみ、上詰めでF列の名前を返します

以上、複合関数よりは分かり易くまとめてみました。
いかがでしょうか?

お礼

早速のご回答ありがとうございました！
参考にさせていただきます。

何度も回答していただき、ご親切にありがとうございました。

補足

すいません、教えていただいた素晴らしい回答を実践してみたのですが、すべての行に関数を入れてしまうと、ファイルが重くなってしまって動きが遅くなってしまいました。
　というのも、データ行自体が５万行あるんです。行すべてにコピーせずに、いくつかのセルだけで処理できませんか？
お忙しい中申し訳ありませんが、よろしくお願いします。

回答No.1

akki-8888

明確な回答でないかもしれませんが、失礼を。

●リストについて
上記の場合、a1に"会員番号"、b1に"氏名"、c1に"班名"という
ヘッダーを入れたほうが良いかと思います。

●他班員名表示について
　フィルターを使ってコピぺ、や、
　ピボットを使っての抜き出しじゃいけないのでしょうか?

　vlookup利用であれば、班名を基準(左端)にして、別シートにリスト作成をし、
　if式等と組み合わせて強引に表示も可能です。
　……が、この場合、班名リストが出来てしまうのであんまり意味ないですね。

『全体リスト表示　+　個人名入力による他班員表示』、
がしたいということになるのでしょうか。

補足

早速のご回答ありがとうございます(*^^*)

ピボットやフィルタで処理を行うのではなく、会員番号を入力するだけでその人と同じ班名を持つ他の人の氏名をリストアップしたいのです。

宜しくお願い致します。