エクセル 同じコードの人を表示させる

質問

エクセルほぼ初心者です。いろいろ検索しましたが理解ができなかったので教えてください。

sheet1
A,B,C,D,E
販売店コード,販売店名,被保険者名,加入コース,保険料
11111,○○店,スズキタロウ,1コース,3500
11111,○○店,ヤマダジロウ,1コース,3500
11112,□□店,サトウイチロウ,2コース,4200
11113,△△店,タカハシサブロウ,4コース,6800
11113,△△店,タカハシハナコ,2コース,4200
11113,△△店,スズキイチロウ,3コース,5200
　　　　・
　　　　・
　　　　・

といった感じで1000名ほどの入力があります。

sheet2に販売店コードを入力すると
販売店名、被保険者名、加入コース、保険料を全員分
表示されるようにしたいのです。
どのようにしたらよいでしょうか。お願いします。

回答No.2

suekun · ベストアンサー

提示されているデーターベースは1行目が表題で（販売店コード・・・）
2行目から1000行まで実際のデーターが入っている物と解釈して説明します。

まず、COUNTIFで抽出する対象のデーターが何件あるか算出します。
仮に、その抽出先をsheet2のA1としましょう。
その下に該当するデーターが存在する行番号を該当分表示出来る様にします。

抽出したい販売店コードをsheet2のB1に記入します。

sheet2のA2には、
=IF((ROW()-1)<=A1,MATCH($B$1,Sheet1!A1:A1000,0),"")

内容は、集計した該当データー数とこの数式が入っている行の位置を
比較して、データーが存在するなら行番号を、存在しないなら
空白にする内容です。

行番号の取得は、MATCHで検索します。
但し、この関数は何件該当する値があっても範囲の中で一番
最初に該当した行番号しか返してくれません。

ですからA3からは、上のA2で返された行番号も使う事になります。
検出された行番号は検索範囲から削除して次の値を探しに行きます。

A3
=IF((ROW()-1)<=$A$1,MATCH($B$1,OFFSET(Sheet1!$A$1,A2,0):Sheet1!A$1000,0)+A2,"")

以降、下方向に必要なだけ（最大抽出される件数以上）コピーします。

これでA列の2行目から、コード番号がヒットしたデーターがある
行番号がすべて表示されます。

あとはその行番号から列を合わせてデーターを参照するだけです。
B2セルに
=IF($A2<>"",INDEX(Sheet1!$A$1:$E$1000,$A2,COLUMN(A1)),"")
これを下方向に先ほど同じ分だけ必要量を考えてコピーし
そのまま右方向に、F列までコピーします。

範囲は最初に述べた様に仮に2行目から1000行目までで設定しているので
この分は実情に合わせて変更して下さい。

お礼

エクセル初心者なので不安でしたが、コピペしただけですが綺麗に出来ました。説明もつけていただいて助かりました。
ありがとうございます。

回答No.1

mu2011

次の方法は如何でしょうか。
（１）sheet2のA1に「販売店コード」名称を入力、A2にコードを入力
（２）データ→フィルタ→フィルタオプションの設定
（３）指定した範囲を選択→リスト範囲欄にSheet1!$A:$E、検索範囲欄に$A$1:$A$2、抽出範囲欄に$A$5を設定→ＯＫ
この（２）（３）の操作をマクロ記録しておけば、A2にコード入力した後、マクロ実行するだけで抽出できますのでお試しください。
但し、再抽出する場合、前抽出内容は削除してからマクロ実行して下さい。