エクセル2010 同じグループの顧客を表示

質問

エクセル2010での質問です。

顧客リスト約5000件の中から同じグループに該当する
顧客を表示したいのですがうまくいきません。

Sheet1のA列に顧客番号、B列に名前、C列にグループ番号が
あります。
Sheet2のA1にグループ番号を入力すれば
B1に名前、C1に顧客番号が表示できる方法を教えてください。

よろしくお願いします。

回答No.5

MackyNo1 · ベストアンサー

>Sheet2のB列に顧客番号、D列に名前、J列にグループ番号があります。
>Sheet1のK17にグループ番号を入力すればF26に名前、C26に顧客番号が表示できる方法を教えてください。

Excel2010をご使用なら、以下のような数式をC26に入力して顧客番号を表示するのが良いかもしれません。

=IFERROR(INDEX(Sheet2!B:B,SMALL(INDEX((Sheet2!$J$2:$J$5000<>$K$17)*100000000+ROW($J$2:$J$5000),),ROW(A1))),"")

名前も参照範囲を変えた同じような数式で表示できますが、名前と顧客番号が1対1に対応している（かつ同じ名前のデータが2行以上ない）場合は、以下のようにVLOOKUP関数で上記の顧客番号を検索する方が計算負荷が少なくなります。

=IF(C26="","",VLOOKUP(C26,Sheet2!B:D,3,0))

お礼

できました。

お忙しい中、何度もご回答いただきまして
ありがとうございました。

回答No.4

tom04

No.2・3です！
補足を読ませていただいて

Sheet2は1行目がタイトル行で、データは2行目以降にあるとします。

＞F26に名前、C26に顧客番号が・・・

とあるのですが、同グループの人が複数あるのが普通だと思いますので、
F26以降のセルに名前・C26以降のセルに顧客番号をすべて表示！
となります。
（C・F列の行はそれ以外のデータは全く入っていないという前提です）

前回同様、標準モジュールにコピー＆ペーストしてマクロを試してみてください。

Sub test()
Dim ws1, ws2 As Worksheet
Dim i As Long
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
i = ws1.Cells(Rows.Count, 3).End(xlUp).Row
Application.ScreenUpdating = False
If i > 25 Then
Range(ws1.Cells(26, "C"), ws1.Cells(i, "C")).ClearContents
Range(ws1.Cells(26, "F"), ws1.Cells(i, "F")).ClearContents
End If
ws2.Columns("B:J").AutoFilter Field:=9, Criteria1:=ws1.Range("K17")
i = ws2.Cells(Rows.Count, 2).End(xlUp).Row
Range(ws2.Cells(2, "B"), ws2.Cells(i, "B")).Copy Destination:=ws1.Cells(26, "C")
Range(ws2.Cells(2, "D"), ws2.Cells(i, "D")).Copy Destination:=ws1.Cells(26, "F")
ws2.Select
Selection.AutoFilter
ws1.Activate
Application.ScreenUpdating = True
End Sub

こんな感じではどうでしょうか？m(_ _)m

補足

おはようございます。

ご回答ありがとうございました。

Sheet1のC・F列にデータが入ってない状態で試したところ
無事データを表示することができました。

ところが、データの表示されるセルはC列がC26～C45、
F列がF26～F45（どちらも20行）になり他には文字や数値が
入っているため、表示することができませんでした。

大変申し訳ございませんが、上記条件で表示ができますよう
教えてください。

よろしくお願いします。

回答No.3

tom04

No.2です！
投稿後、もう一度質問文を読み直して
＞B1に名前、C1に顧客番号が表示できる方法を教えてください
が間違っていることに気付きました。

前回のコードは無視して、↓のコードに訂正してください。
操作方法は前回と一緒です。

Sub test() 'この行から
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
ws1.Columns("A:C").AutoFilter Field:=3, Criteria1:=ws2.Range("A1")
ws1.Columns("B").Copy Destination:=ws2.Range("B1")
ws1.Columns("A").Copy Destination:=ws2.Range("C1")
ws1.Select
Selection.AutoFilter
ws2.Activate
Application.ScreenUpdating = True
End Sub 'この行まで

何度も失礼しました。m(__)m

補足

ご回答ありがとうございます。

一度やってみたのですが、実際のセルと違うため
うまくいきませんでした。

実データのセルを記載しますので
大変お手数ですが、もう一度お願いします。

エクセル2010での質問です。

Sheet2のB列に顧客番号、D列に名前、J列にグループ番号が
あります。
Sheet1のK17にグループ番号を入力すれば
F26に名前、C26に顧客番号が表示できる方法を教えてください。

回答No.2

tom04

こんにちは！
データが約5000程度あるということなので・・・
VBAでの一例です。

Alt＋F11キー　→　画面左下の「This Workbook」をダブルクリック　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
ws1.Columns("A:C").AutoFilter Field:=3, Criteria1:=ws2.Range("A1")
ws1.Columns("B:C").Copy Destination:=ws2.Range("B1")
ws1.Select
Selection.AutoFilter
ws2.Activate
End Sub 'この行まで

※　グループ番号はSheet2のA1セルに入るものとします。

こんな感じではどうでしょうか？m(__)m

回答No.1

MackyNo1

A1セルに一致するSheet1のC列の名前を関数で表示するなら以下のような数式を入力し下方向にオートフィルコピーすることになります。

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

上記の数式は抽出するデータが多いとシートの動きが重くなりますので、抽出データが多い場合は、添付画像のようなレイアウトにしたシートでフィルタオプションの設定の機能を使うことをお勧めします。

Sheet2で並べ替えとフィルタの「詳細設定」をクリックして、リスト範囲をSheet1のA列からC列全体を選択し、検索条件範囲にA1:A2、抽出先にB1:C1（B1に名前、C1に顧客番号と入力しておく）を指定します。

この操作を自動的に行いたいなら、マクロの記録でこの操作を記録してください。

補足

ご回答ありがとうございます。

一度やってみたのですが、実際のセルと違うため
うまくいきませんでした。

実データのセルを記載しますので
大変お手数ですが、もう一度お願いします。

エクセル2010での質問です。

Sheet2のB列に顧客番号、D列に名前、J列にグループ番号が
あります。
Sheet1のK17にグループ番号を入力すれば
F26に名前、C26に顧客番号が表示できる方法を教えてください。