エクセルで文字を入れたら表を自動検索して結果を一覧表示するセルを作る方法

あなたは住所録や商品リスト、価格リストなど、大きな表から該当箇所をフィルターで検索して探すのが面倒だと思ったことはありませんか？

エクセルのフィルター機能は便利ですが、いちいちフィルターの三角ボタンを押して、条件設定するのが面倒ですよね。

エクセルマクロを使えば、セルに文字を入れるだけで、自動で検索して、自動で結果表示するところまでやってくれます。

今回は「エクセルで文字を入れたら表を自動検索して結果を一覧表示するセルを作る方法」を紹介します。

エクセルで文字を入れたら表を自動検索して結果を一覧表示するセルを作る方法

エクセルで文字を入れたら表を自動検索して結果を一覧表示するセルを作る方法です。

今回は、下のような表を検索するのを例に紹介します。

B1セルに文字入力すると、B5~B100セルの中を検索し、ヒットした行だけを表示します。

１．Alt　+　f11　でマクロ画面を開く

２．モジュールを選択して開く。ここでは「Sheet89(Sheet83)」をダブルクリック

３．コードを入力

コードを入力します。

B1セルに文字入力すると、B5~B100セルの中を検索し、ヒットした行だけを表示します。

赤枠部のコードです。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim searchText As String
    Dim cell As Range
    Dim targetRange As Range
    
    ' B1セルが変更された場合のみ実行
    If Not Intersect(Target, Range("B1")) Is Nothing Then
        ' B1セルの内容を取得します
        searchText = Range("B1").Value
        
        ' 検索範囲を設定します
        Set targetRange = Range("B5:B100")
        
        ' すべての行を表示します
        targetRange.EntireRow.Hidden = False
        
        ' 検索範囲の各セルをチェックし、一致しない行を非表示
        For Each cell In targetRange
            If InStr(1, cell.Value, searchText, vbTextCompare) = 0 Then
                cell.EntireRow.Hidden = True
            End If
        Next cell
    End If
End Sub

コードを入力したら、コードの入力画面を閉じて下さい。

４．B1セルに文字を入力

B1セルに文字を入力します。

５．自動検索され、該当する行のみが表示された

B1セルに入力後、enterを押すと、B1セルの文字で、自動検索されて、該当する行のみが表示されました。

これで、検索が簡単になりますね！

「A」と「B」の両方を含む行だけを表示したい場合　AND検索

検索するときは、1つの文字だけでなく、2つの文字が入っているものを探したいときもありますよね？

例えば、「A」と「B」の両方を含む行だけを表示したい場合です。

下の画像で、赤丸がA、青丸がBです。

両方含むのが、青矢印で示した４つです。

このAとBを両方含む行だけを表示したいとします。

１．Alt　+　f11　でマクロ画面を開く

２．モジュールを選択して開く。ここでは「Sheet89(Sheet83)」をダブルクリック

　先程と同じです。

３．コードを入力

　コードを入れます。

　B1セルの値を、B4~B100セルの範囲でフィルタリングする、という意味です。

赤枠部のコードです。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim searchValue As String
    Dim searchKeywords As Variant
    Dim rng As Range
    Dim lastRow As Long
    Dim cell As Range
    Dim matchAll As Boolean
    Dim i As Integer

    ' セルB1が変更された場合
    If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
        ' B1セルの値を取得
        searchValue = Me.Range("B1").Value
        
        ' フィルタリング範囲を設定
        lastRow = Me.Cells(Me.Rows.Count, "B").End(xlUp).Row
        Set rng = Me.Range("B5:B100") ' データがB5からB100
        
        ' 既存のフィルターをクリア
        Me.AutoFilterMode = False
        
        ' すべての行を表示（フィルタリング解除）
        rng.EntireRow.Hidden = False
        
        ' B1セルが空でない場合のみフィルターを適用
        If searchValue <> "" Then
            ' キーワードをスペースで分割
            searchKeywords = Split(searchValue, " ")
            
            ' 各セルでキーワードをチェック
            For Each cell In rng
                matchAll = True
                For i = LBound(searchKeywords) To UBound(searchKeywords)
                    If InStr(1, cell.Value, searchKeywords(i), vbTextCompare) = 0 Then
                        matchAll = False
                        Exit For
                    End If
                Next i
                If Not matchAll Then
                    cell.EntireRow.Hidden = True
                End If
            Next cell
        End If
    End If
End Sub

これで、B1セルに「A B」を入力すると・・・

AとBの両方が入った行だけが表示されました！

これでより使いやすくなりましたね！

ちなみに、BとAを入れ替えて、「B A」と入れても、同じ結果が出ます。

「A」と「B」と「C」の3つを含む行を表示したい場合　AND検索

検索するときは、A・B・Cの3つを含む行を表示したい場合もありますよね？

そのときのコードは・・・同じです。

先程作ったコードで、B1セルに「A B C」と入れると、AとBとCが入った行が表示されます。

これなら便利ですよね。

「A」または「B」を含む行を表示したい場合　OR検索

検索するときは、AとBのどちらかを含む場合もあると思います。

スラッシュ「/」で区切ると、「A」または「B」で検索できるようにしてみます。

そのときのコードはこちらです。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim searchValue As String
    Dim searchKeywords As Variant
    Dim rng As Range
    Dim lastRow As Long
    Dim cell As Range
    Dim matchAll As Boolean
    Dim matchAny As Boolean
    Dim i As Integer

    ' セルB1が変更された場合
    If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
        ' B1セルの値を取得
        searchValue = Me.Range("B1").Value
        
        ' フィルタリング範囲を設定
        lastRow = Me.Cells(Me.Rows.Count, "B").End(xlUp).Row
        Set rng = Me.Range("B5:B100") ' データがB5からB100
        
        ' 既存のフィルターをクリア
        Me.AutoFilterMode = False
        
        ' すべての行を表示（フィルタリング解除）
        rng.EntireRow.Hidden = False
        
        ' B1セルが空でない場合のみフィルターを適用
        If searchValue <> "" Then
            ' キーワードをスペースで分割
            If InStr(1, searchValue, "/") > 0 Then
                searchKeywords = Split(searchValue, "/")
                matchAny = True
            Else
                searchKeywords = Split(searchValue, " ")
                matchAny = False
            End If
            
            ' 各セルでキーワードをチェック
            For Each cell In rng
                matchAll = True
                For i = LBound(searchKeywords) To UBound(searchKeywords)
                    If matchAny Then
                        ' A/B のように / で区切った場合はいずれかを含むかチェック
                        If InStr(1, cell.Value, searchKeywords(i), vbTextCompare) > 0 Then
                            matchAll = True
                            Exit For
                        Else
                            matchAll = False
                        End If
                    Else
                        ' A B のようにスペースで区切った場合はすべてを含むかチェック
                        If InStr(1, cell.Value, searchKeywords(i), vbTextCompare) = 0 Then
                            matchAll = False
                            Exit For
                        End If
                    End If
                Next i
                If Not matchAll Then
                    cell.EntireRow.Hidden = True
                End If
            Next cell
        End If
    End If
End Sub

B1セルに、「A/B」と入れると、AまたはBを含む行だけが表示されました。

これで、AとBのどちらかを含むものを検索するのも簡単になりましたね！

まとめ

エクセルで文字を入れたら表を自動検索して結果を一覧表示するセルを作る方法です。

１．Alt　+　f11　でマクロ画面を開く

２．モジュールを選択して開く。ここでは「Sheet89(Sheet83)」をダブルクリック

３．コードを入力

４．B1セルに文字を入力

５．自動検索され、該当する行のみが表示された

これで、検索結果が自動表示されるので、楽になります。

ちなみに,元に戻すときはB1セルを空欄にしてEnterを押すと、表全体を表示するのに戻ります。