複数のドロップダウンリストでの絞り込み

質問

Sheet1に参照用リストがあり、Sheet2でそのリストの中から情報を抽出したいです。

例えば、A1・A2・A3（画像Sheet2）という箱があり、それぞれに複数条件で選んだもの（画像Sheet2"品番"に表示）を入れようと思います。

複数条件でものを選ぶには、ドロップダウンリストで絞り込みをします。
（セルに名前をつけ、データの入力規則（INDIRECT）を使用
　INDIRECTに必要な項目（内容が重複し、セルの名前を変更する必要があるるもの）は追加で作成して対応しています）

A1だけの設定でしたら、その方法でできるのですが、
A2、A3、と設定しようと思うと、名前を変更しなければならなくなり
分類名など、同じ表記が必要でもできなくなります。

他の絞り込み方法、又は上記絞り込み方法に加えて何か追加作業で解決できる方法はありますでしょうか？
※項目に該当するものがない場合は、空白欄ができることもあります。

よろしくお願いします。

回答No.3

tom04 · ベストアンサー

No.1・2です。

Excel2007をお使いだというコトですので、苦肉の策として
前回の数式はリストに余分な空白を表示させないための数式です。
Excel2007までの場合別Sheetを数式で参照できないと思いますので、
Sheet3のF～H列をそれぞれ「名前の定義」の設定をしておきます。
各列とも「名前定義」行数が多すぎるとリストに表示される空白が多すぎて
探したいデータが上の方になり窓の表示されなくなり、スクロールバーで上にスクロールする必要があります。
その辺の行数は適宜合わせてください。

F列の1行目以降を範囲指定　→　仮に　分類2　と名前定義
G列の1行目以降を範囲指定　→　仮に　品名　と名前定義
H列の1行目以降を範囲指定　→　仮に　番号　と名前定義

以上の設定が完了するとSheet2の各列のリストの「元の値」の式が変わります。

B列の「元の値」の欄に
=分類2

C列の「元の値」の欄に
=品名

D列の「元の値」の欄に
=番号

としてみてください。
おそらくこれで前回のマクロが使用できるはずです。m(_ _)m

お礼

tom04さん

できました!!!
選択していって表示が変わった瞬間、感動でした。

マクロはまだこれから勉強が必要ですので
これを機にもっと頑張ろうと思います。

何度もありがとうございました。

回答No.2

tom04

No.1です。

続けてお邪魔します。
コードを載せます。

画面左下のSheet2のSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてA列からD列まで順番にリストで選択してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim i As Long, cnt As Long, c As Range, wS1 As Worksheet, wS3 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS3 = Worksheets("Sheet3")

If Intersect(Target, Range("A:D")) Is Nothing Or Target.Count <> 1 Then Exit Sub
Application.ScreenUpdating = False
wS3.Range("A:D").Clear
With Target
Select Case .Column
Case 1
If .Value <> "" Then
wS1.Range("A1").AutoFilter field:=1, Criteria1:=.Value
wS3.Range("A:E").ClearContents
wS1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wS3.Range("A1")
End If
wS3.Range("F:F").Clear
For i = 2 To wS3.Cells(Rows.Count, "B").End(xlUp).Row
Set c = wS3.Range("F:F").Find(what:=wS3.Cells(i, "B"), LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cnt = cnt + 1
wS3.Cells(cnt, "F") = wS3.Cells(i, "B")
End If
Next i
Case 2
If .Value <> "" Then
wS1.Range("A1").AutoFilter field:=2, Criteria1:=.Value
End If
wS3.Range("A:E").ClearContents
wS1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wS3.Range("A1")
wS3.Range("G:G").Clear
cnt = 0
For i = 2 To wS3.Cells(Rows.Count, "C").End(xlUp).Row
Set c = wS3.Range("G:G").Find(what:=wS3.Cells(i, "C"), LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cnt = cnt + 1
wS3.Cells(cnt, "G") = wS3.Cells(i, "C")
End If
Next i
Case 3
If .Value <> "" Then
wS1.Range("A1").AutoFilter field:=3, Criteria1:=.Value
End If
wS3.Range("A:E").ClearContents
wS1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wS3.Range("A1")
wS3.Range("H:H").Clear
cnt = 0
For i = 2 To wS3.Cells(Rows.Count, "D").End(xlUp).Row
Set c = wS3.Range("H:H").Find(what:=wS3.Cells(i, "D"), LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cnt = cnt + 1
wS3.Cells(cnt, "H") = wS3.Cells(i, "D")
End If
Next i
Case Else
wS1.Range("A1").AutoFilter field:=4, Criteria1:=.Value
wS3.Range("A:E").ClearContents
wS1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wS3.Range("A1")
.Offset(, 1) = wS3.Cells(2, "E")
End Select
i = .Row
If WorksheetFunction.CountBlank(Range(Cells(i, "A"), Cells(i, "D"))) = 0 Then
wS3.Cells.Clear
wS1.AutoFilterMode = False
End If
End With
Application.ScreenUpdating = True
End Sub 'この行まで

※　かならずA列　→　B列　→　C列　→　D列
という順にリスト選択してください。

尚、保存する時は「ファイルの種類」　→　「マクロ有効ブック」として保存してください。m(_ _)m

お礼

返信が遅くなり申し訳ありません。

>次にB2～D列のリスト表示させたいセルを範囲指定　→　入力規則　→　元の値の欄に
>=OFFSET(Sheet3!F$1,0,,COUNTA(Sheet3!F:F))
>という数式を入れてOK
>（この数式部分でExcel2007までのバージョンの場合、動かないかもしれません）

この部分がやはり2007では対応しておらず、前に進むことができませんでした。。。
計算式は入力できないようです。
画像を載せていただいたりマクロのコードまで教えていただいたり、ご丁寧にありがとうございました。
また別の方法を探します。。。

回答No.1

tom04

こんばんは！
Sheet1の表のレイアウトを変えれば少しは簡単になりそうですが、
とりあえずお示しの配置通りでやってみました。
関数では難しいのでVBAになってしまいます。

尚、Excel2010でやっていますので、Excel2007までのバージョンの場合は
動くかどうか検証できていません。

＞※項目に該当するものがない場合は、空白欄ができることもあります。
とありますが、空白セルがあると何かと厄介ですので、↓の画像（右側がSheet1です）のように
空白セルにハイフン（－）を入力しておいてください。
そうしたうえでの一例です。
Sheet3を作業用のSheetとして使用していますので、Sheet3はまっさらな状態にしておいてください。

まずSheet2のA列リスト表示させたいセルを範囲指定　→　入力規則　→　リスト　→　元の値の欄に
赤,黄色,緑・・・
と表示させたいすべての色をカンマで区切って入力しておきます。

次にB2～D列のリスト表示させたいセルを範囲指定　→　入力規則　→　元の値の欄に
=OFFSET(Sheet3!F$1,0,,COUNTA(Sheet3!F:F))
という数式を入れてOK
（この数式部分でExcel2007までのバージョンの場合、動かないかもしれません）

※　おそらくコードを載せると文字数がオーバーすると思いますので、
この後もう一度コードを載せてみます。
とりあえず今回は画像だけ・・・m(_ _)m