あなたはエクセル内のデータを検索するときに、検索のやり方を別の人に何度も聞かれて対応が大変だと困ったことはありませんか？
検索は、「ctrl」+「f」で検索ボックスが出るのですが、エクセルを普段使わない人だと、中々使わないんですよね。
PC苦手人「Aさん、検索のやり方どうやるんだっけ？」
Aさん「・・・前にも言いましたよね・・・」
みたいな会話は、あちこちで繰り広げられています。
エクセルのマクロを使えば、検索ボックスを作れるので、誰でも簡単に検索できるようになります。
今回は「エクセルで検索ボックスを作り誰でも簡単に検索できるようにする方法」を紹介します。
Excel基本の検索方法「ctrl」+「F」
検索ボックスを作る前に、エクセルには基本の検索機能があるので、まずはその使い方になります。
「ctrl」+「F」を押すと、検索のウィンドウが開くので、検索したい文字を入れて、「次を検索」すると、ヒットしたセルにカーソルが飛びます。
ちょっとした検索ならこれで十分なのですが、表が大きいと検索に時間がかかったりします。
そんなとき、「ここにデータがありそうだな」というのがわかっていれば、範囲を限定して検索すると、検索時間が短くなります。
下の画像では、B列を選択しているところです。
「B」の部分を左クリックすると、B列の選択ができるので、これで「ctrl」+「F」を押して検索します。
次は、複数列の範囲を検索したい場合です。
先程のB列の他にD列も選択してみます。
Bのところを左クリックした後、「ctrl」を押しながら、「D」を左クリックすると、BとDの両方の列を選択できます。
同じように、範囲を選択できれば、検索が速くなるので、検索時間を短くしたい方は、試してみて下さい。
フィルター検索方法
次にフィルター機能で検索する方法です。
（→参考：エクセルでフィルターをかける方法）
エクセル上部のリボンの右端の方にある、「並べ替えとフィルター」→「フィルター」を順にクリックします。
そうすると表の上部に下向きの三角「▼」が表示されます。
「▼」をクリックし、検索の欄にキーワードを入れると検索できます。
これでも簡単な検索はできますよね。
探したいものの列が分かっている場合は、このフィルターを使えば十分なことも多いです。
ちなみに、フィルター機能はいちいち設定しなくても、表の作り方の段階で設定しておくこともできます。
→エクセルで表計算機能を最大限に生かす！初心者でもできる「表の作り方」完全ガイド
話を元に戻しますと、「ctrl+F」検索やフィルター検索では、簡単な検索はできるのですが、複雑な検索はできません。
例えば、『「A」と「B」のいずれかを含む』とか、『「A」と「B」を含み、「C」は含まない』とか。
そういう場合は、VBAの方が得意です。
エクセルVBAで検索ボックスを作り検索する方法
さていよいよ、エクセルで検索ボックスを作り誰でも簡単に検索できるようにする方法です。
下のような商品台帳から、商品「ABC」の販売実績を検索する場合を例に紹介します。
マクロを作るときは、「開発」タブを使います。
開発タブが出ていない方は、下のリンクで出し方を紹介していますので、まずは準備をお願いします。
→エクセルで開発タブを表示する方法
１．「開発」タブをクリック →「挿入」をクリック →「ボタン」をクリック
２．「新規作成」をクリック
３．コード入力画面が開く
４．コードを入力
コードを入力します。
検索ボックスを表示して、キーワードを入力、ヒットした件数を表示、ヒットしたセルに順番に移動する、という意味です。
赤枠部のコードです。
Dim searchText As String
Dim rng As Range
Dim cell As Range
Dim hitCount As Long
Dim firstCell As Range
' 検索ボックスの表示とキーワードの入力
searchText = InputBox("検索するキーワードを入力してください:", "検索")
' 入力が空白の場合は処理しない
If searchText = "" Then Exit Sub
' 検索範囲をアクティブシートの全範囲に設定
Set rng = ActiveSheet.UsedRange
' ヒットしたセルの数をカウント
hitCount = Application.WorksheetFunction.CountIf(rng, "*" & searchText & "*")
' ヒットした数を表示
MsgBox "検索結果: " & hitCount & " 件が見つかりました。", vbInformation, "検索結果"
' ヒットしたセルに順番に移動
For Each cell In rng
If InStr(1, cell.Value, searchText, vbTextCompare) > 0 Then
If firstCell Is Nothing Then Set firstCell = cell
cell.Select
If MsgBox("次のヒットを探しますか？", vbYesNo + vbQuestion) = vbNo Then Exit For
End If
Next cell
' 最初のヒットしたセルに移動
If Not firstCell Is Nothing Then firstCell.Select
コードを入力したら、コードの入力画面を閉じて下さい。
５．ボタンができた →ボタンをクリック
ボタンができたら、ボタンをクリックします。
６．検索ボックスが表示される →キーワードを入力 →「OK」をクリック →ヒットする
「検索するキーワードを入力してください」と出ます。
ここでは「ABC」を入れて、「OK」をクリックしてみます。
「1件が見つかりました」！
「OK」をクリックして表を見ると、ちゃんと「ABC」のところにカーソルがきています。
これで「ABC」の販売実績がわかりました。
もし複数のヒットがあると、「次のヒットを探しますか？」→「はい」で、次のヒットしたセルに移動します。
７．ボタンの表示を「検索」に変更
「ボタン」だと何のボタンかわからないので、「検索」に変更してみます。
ボタンにカーソルを合わせ、右クリックメニューで、「テキストの編集」。
「検索」ボタンになりました。
これなら見た人は、検索だな！ってわかりますよね。
これなら検索のショートカットキーを忘れてしまっても大丈夫です。
複数条件で検索する方法 OR検索
ついでに、複数条件で検索する方法を紹介します。
使う表を変えて、この中から、「A」or「Y」を含むセルを検索していきます。
品名に「A」または「Y」が含まれているものの販売実績を調べたい場合等に使います。
OR検索です。
コードは下のようになります。
先程と同様に、ボタンができるのでクリックすると、検索ボックスがでます。
キーワードをスペースで区切って入力します。
ここでは「A Y」と入れました。
19件ヒット！
ヒットしたところを見ていきます。
AとYが入ったセルがヒットしています。
Aだけが入ったセルもヒットしています。
Yから始まるセルもヒットしています。
複数条件でもちゃんと検索できました。
最後に赤枠部のコードの紹介です。
Dim searchKeywords As Variant
Dim rng As Range
Dim cell As Range
Dim hitCount As Long
Dim firstCell As Range
' 検索ボックスの表示とキーワードの入力
searchKeywords = Split(InputBox("検索するキーワードをスペースで区切って入力してください:", "複数条件検索"), " ")
' 入力が空白の場合は処理しない
If UBound(searchKeywords) < LBound(searchKeywords) Then Exit Sub
' 検索範囲をアクティブシートの全範囲に設定
Set rng = ActiveSheet.UsedRange
' ヒットしたセルの数をカウント
hitCount = 0
' 各キーワードで検索し、ヒットしたセルの数をカウント
For Each cell In rng
For Each keyword In searchKeywords
If InStr(1, cell.Value, keyword, vbTextCompare) > 0 Then
hitCount = hitCount + 1
Exit For
End If
Next keyword
Next cell
' ヒットした数を表示
MsgBox "検索結果: " & hitCount & " 件が見つかりました。", vbInformation, "検索結果"
' ヒットしたセルに順番に移動
For Each cell In rng
For Each keyword In searchKeywords
If InStr(1, cell.Value, keyword, vbTextCompare) > 0 Then
If firstCell Is Nothing Then Set firstCell = cell
cell.Select
If MsgBox("次のヒットを探しますか？", vbYesNo + vbQuestion) = vbNo Then Exit For
End If
Next keyword
Next cell
' 最初のヒットしたセルに移動
If Not firstCell Is Nothing Then firstCell.Select複数条件で検索する方法 AND検索
次は、AND検索です。
「A」と「Y」を含むセルを検索するのを例に紹介します。
商品名「A」で、オプション「Y」がついたセルを検索する場合などに使います。
マクロのボタンを作る方法は、先程と同じです。
コードを入力します。
ボタンを作り、「and検索」に名前を変えておきました。
次にボタンを押します。
「A」と「Y」を入力します。
半角スペースで区切ります。
「A Y」を入力したら、OKをクリック。
6件ヒットしました。
先程「A」or「Y」で検索したら、19件ヒットだったので、「A」and「Y」だとそれより少なくなっています。
ちゃんと検索できているか、確認してみます。
「次のヒットを探しますか？」と出るので、順に「はい」をクリックしていくと、カーソルが動いていきます。
これで、「AND検索」もできましたね！
複数条件で検索する方法 NOT検索
次は、NOT検索です。
「A」を含むが「Y」は含まないセルを検索するのを例に紹介します。
商品名「A」でオプション「Y」以外のセルを検索する場合などに使います。
マクロのボタンを作る方法は、先程と同じなので、割愛します。
コードを入力します。
ボタンができたら押します。
ボタンは「and検索 除外項目あり」にしておきました。
検索するキーワードを入力します。
ここでは「A」です。
次に、除外するキーワードを入力します。
「Y」を入力しました。
入力したら「OK」をクリック。
13件ヒットしました！
「次のヒットを探しますか？」で「はい」を押すと、一つずつヒットしたセルにカーソルが移動していきます。
これで「Aは含まれているけど、Yは含まれていないもの」が検索できました！
複数条件で検索する方法 完全一致検索
次は、完全一致検索です。
セルが「ABC」と完全に一致するものを検索するときに使います。
「ABC-1」とか「2026ABC」は含みません。「ABC」だけを検索します。
マクロのボタンを作る方法は、先程と同じなので、割愛します。
コードを入力します。
ボタンができました。
「完全一致検索 除外項目あり」という表記に変更しています。
ボタンを押します。
完全一致で検索したいキーワードを入力します。
2件ヒットしました！
「次のヒットを探しますか？」で「はい」を押していくと、検索でヒットしたセルに飛んで行きます。
これで完全一致の検索も簡単にできるようになりましたね！
AND検索、OR検索、NOT検索、完全一致検索をまとめる
それでは次に、AND検索、OR検索、NOT検索、完全一致検索をまとめてみます。
マクロのボタンを作る方法は、先程と同じなので、割愛します。
コードを入力します。
ボタンができたら、ボタンを押します。
「いろいろな検索」にしておきました。
AND検索なら「1」、
OR検索なら「2」、
NOT検索なら「3」を入力します。
完全一致検索なら「1」、
そうでないなら「2」を入力します。
今回はOR検索を選択するので、2を入力します。
「A Y」を入力し、AまたはYがあるセルを検索してみます。
19件ヒット！
「次のヒットを探しますか？」 →「はい」 でヒットを探していきます。
次々ヒットを追っていくと、順番にカーソルが移動していきます。
これで、AND検索、OR検索、NOT検索、完全一致検索と、ボタンを４つ作らなくてもよくなり、すっきりしましたね。
ユーザーフォームを使った検索ボックスでさらに検索しやすく
AND検索、OR検索、NOT検索、完全一致検索をまとめる方法では、ユーザーフォームを使った検索ボックスを作ると、さらに検索しやすくなります。
ユーザーフォームの作り方を紹介します。
１．alt + f11でマクロの画面を開く
２．ユーザーフォームをクリックする
３．ユーザーフォーム入力画面が開く
４．チェックボックス、テキストボックス、コマンドボックスを挿入する
５．それぞれサイズ、文字を入力して、配置を整列する
チェックボックスは4つ作り、AND検索、OR検索、NOT検索、完全一致検索、を配置しました。
キーワードボックスは2つ作り、上の段が「検索キーワードを入力！」、下の段は「除外キーワードを入力！」とし、何を入力したらよいかを書いておきます。
コマンドボックスは、「検索開始！」として、検索するのをわかりやすくしました。
６．コマンドボックスをダブルクリックし、コードを入力する
コマンドボックスをダブルクリックすると、コード入力画面になるので、コードを入力します。
７．新しくボタンを作り、コードを入力する
ユーザーフォームを開くためのボタンを作ります。
８．ボタンができる →クリックする
ボタンの名前は「検索ボタン」にしました。
検索ボタンをクリックします。
９．フォームが開く
これならチェックを入れるのと、キーワードを入れるので、わかりやすいですよね。
１０．必要なチェックやキーワードを入力したら、検索開始！
ここでは、AND検索とし、AとYが含まれるものを検索してみます。
１１．検索結果が表示される
6件ヒットしました。
ここからは前述までの確認結果と同じように、結果を見られます。
フォームを使うと検索の入力がしやすくなって、いいですよね！
あとは検索結果を確認しましょう！
まとめ
エクセルで検索ボックスを作り誰でも簡単に検索できるようにする方法のまとめです。
１．「開発」タブをクリック →「挿入」をクリック →「ボタン」をクリック
２．新規作成をクリック
３．コードを入れる画面が出る
４．コードを入力
５．ボタンができた →ボタンをクリック
６．検索ボックスが表示される →キーワードを入力 →「OK」をクリック →ヒットする
７．ボタンの表示を「検索」に変更
これでこのページで「検索ボックスの作成」→「AND検索、OR検索、NOT検索、完全一致検索」ができるようになりました。
この検索ボックスなら、誰でも簡単に検索できますね！
Excelの検索は、やり方次第で驚くほどラクになります。
今回の方法以外にも、レベル別に使える検索テクニックをまとめています。
▶ Excel検索のまとめページはこちら
最後に、ユーザーフォームを使った検索で使ったコードを紹介します。
Private Sub CommandButton1_Click()
Dim searchKeywords As Variant
Dim excludeKeywords As Variant
Dim rng As Range
Dim cell As Range
Dim hitCells As Collection
Dim keyword As Variant
Dim excludeKeyword As Variant
Dim allKeywordsFound As Boolean
Dim anyExcludeKeywordsFound As Boolean
Dim i As Long
Dim searchMode As String
' 検索キーワードをスペースで区切る
searchKeywords = Split(TextBox1.Value, " ")
' 除外するキーワードもスペースで区切る
excludeKeywords = Split(TextBox2.Value, " ")
' 検索範囲をアクティブシートの全範囲に設定
Set rng = ActiveSheet.UsedRange
' ヒットしたセルを保存するためのコレクションを作成
Set hitCells = New Collection
' チェックボックスの状態に基づいて検索モードを決定
If CheckBox1.Value Then
searchMode = "AND"
ElseIf CheckBox2.Value Then
searchMode = "OR"
ElseIf CheckBox3.Value Then
searchMode = "NOT"
ElseIf CheckBox4.Value Then
searchMode = "Exact"
Else
MsgBox "検索方法を選択してください。"
Exit Sub
End If
' 各セルで選択した検索モードに従って検索を実行
For Each cell In rng
allKeywordsFound = False
anyExcludeKeywordsFound = False
Select Case searchMode
Case "AND"
' AND検索: すべてのキーワードが含まれているかをチェック
allKeywordsFound = True
For Each keyword In searchKeywords
If InStr(1, cell.Value, keyword, vbTextCompare) = 0 Then
allKeywordsFound = False
Exit For
End If
Next keyword
Case "OR"
' OR検索: 少なくとも1つのキーワードが含まれているかをチェック
For Each keyword In searchKeywords
If InStr(1, cell.Value, keyword, vbTextCompare) > 0 Then
allKeywordsFound = True
Exit For
End If
Next keyword
Case "NOT"
' NOT検索: 除外キーワードが含まれていないかをチェック
allKeywordsFound = True
For Each excludeKeyword In excludeKeywords
If InStr(1, cell.Value, excludeKeyword, vbTextCompare) > 0 Then
allKeywordsFound = False
Exit For
End If
Next excludeKeyword
Case "Exact"
' 完全一致検索: セルの値がキーワードと完全一致するかをチェック
If UBound(searchKeywords) = 0 Then
allKeywordsFound = (cell.Value = searchKeywords(0))
End If
End Select
' 検索条件に一致する場合、セルをコレクションに追加
If allKeywordsFound Then
hitCells.Add cell
End If
Next cell
' ヒットしたセルの数を表示
MsgBox "検索結果: " & hitCells.Count & " 件が見つかりました。", vbInformation, "検索結果"
' ヒットしたセルに順番に移動
If hitCells.Count > 0 Then
For i = 1 To hitCells.Count
hitCells(i).Select
If MsgBox("次のヒットを探しますか？", vbYesNo + vbQuestion) = vbNo Then Exit For
Next i
End If
End Sub