Excelで検索ボックスを作る方法！検索機能も実装！

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

名簿から目的の人を探すとき、毎回Ctrl+Fを開いて検索するのは手間がかかります。
今回は
・セルに検索キーを入れるだけで、ヒット件数と該当行が分かる「検索ボックス」を作る方法
をゴールにします。
検索キーを入れるセルを1つ用意するだけで、件数の表示・該当行の強調・VBAでの一覧化までできるようになります。

上の画像が完成形です。G2セルに「佐藤」と入力すると、ヒット件数が3件と表示され、該当する行が色で強調され、下部の結果エリアに該当行だけが一覧表示されます。

なお、該当行だけを目立たせる条件付き書式の考え方や、うまくいかないときの対処法（トラブルシュート）もあわせてご紹介します。

STEP1. 顧客名簿を用意する

まずは検索したい顧客名簿を用意します。
A列がNo、B列が氏名、C列がふりがな、D列が部署、E列が電話番号です。
1行目が見出しで、2行目以降がデータ、件数は12件です。
この名簿を元にして、いつでも使える検索ボックスを作っていきましょう。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. 検索キーを入れるセル（検索ボックス）を作る

名簿の右側に、検索ボックスを用意します。
まず G1セル に「検索キー」、G3セル に「ヒット件数」と入力します。
次に G2セル を選択し、薄い青色に塗っておきます。
G2セルが、これから検索キーを入力する「検索ボックス」になります。
見出しのG1・G3セルは太字にして背景色を付けると見やすくなります。
たったこれだけで、いつでも使える検索ボックスのできあがりです。

STEP3. 検索キーを入力する

作った検索ボックス（G2セル）に、探したいキーを入力します。
ここでは例として 佐藤 と入力してみましょう。
G2セルをクリックして「佐藤」と入力し、Enterキーを押します。
この検索ボックスは、氏名（B列） を対象に検索する形にしていきます。
なお、この時点ではまだ件数表示も強調表示も出ていません。
次のSTEPで、ヒット件数を自動で表示させましょう。

STEP4. ヒットした件数をCOUNTIF関数で表示する

次に、検索キーに何件ヒットしたかを自動で表示させます。
G4セル に次の数式を入力します。

=COUNTIF(B2:B13,"*"&$G$2&"*")

COUNTIF関数は、範囲の中で条件に一致するセルの個数を数える関数です。
検索条件の “*”&$G$2&”*” は「G2セルの文字を含む」という意味になります。
前後にあるアスタリスク（*）は「0文字以上のどんな文字」を表すワイルドカードです。
前後に付けることで、部分一致の検索になるのがポイントですね。

STEP5. 該当する行を目立たせる

件数だけでなく、該当する行そのものを目立たせると分かりやすくなります。
上の画像は、佐藤さんに該当する3行（2・5・10行目）を色で強調した状態です。
この「該当行だけを色付けする」動きは、実際には 条件付き書式 で自動化できます。
画像では見た目が伝わるように、該当行を手で塗って再現しています。
設定の手順は、次のとおりです。

条件付き書式の設定手順

① 名簿の範囲 A2:E13 を選択します。
② ホーム タブ → 条件付き書式 → 新しいルール をクリックします。
③ 「数式を使用して、書式設定するセルを決定」を選びます。
④ 数式に =COUNTIF($B2,”*”&$G$2&”*”)>0 と入力します。
⑤ 「書式」から塗りつぶしの色（黄色など）を選んでOKします。
数式の $B2 は「B列を固定して行だけ動かす」という指定がポイントです。
これで、検索キーを変えるたびに、該当行の色が自動で切り替わります。

名前ボックスやCtrl+Fとの違い

ここで、よく使われる検索方法との違いを整理しておきましょう。
Ctrl+F は、その場でキーワードを検索して該当セルへ移動する機能です。
手軽ですが、探すたびに検索ウィンドウを開く必要があり、結果は残りません。
名前ボックス は、セル番地や名前を指定してジャンプする機能で、検索とは役割が違います。
一方、今回作った検索ボックスは、決まったセルにキーを入れるだけです。
件数・強調・一覧が常に同じ場所に表示されるので、繰り返し使う業務に向いています。
「毎回Ctrl+Fを開くのが手間」と感じるなら、検索ボックスの出番です。

4つの検索方法の比較

それぞれの方法の特徴を表にまとめました。
探し方や残したい形に合わせて選ぶと迷いません。

STEP6. VBAで検索して結果を別エリアに一覧化する

件数と色だけでは、該当データをコピーして別の資料に使うことはできません。
そんなときは VBA で、検索結果を別エリアに一覧化しましょう。
Alt+F11でVBAエディターを開き、標準モジュールに次のコードを貼り付けます。

Sub SearchBox()
    Dim key As String, lastRow As Long
    Dim r As Long, outRow As Long

    key = Range("G2").Value
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row

    Range("A16:E16").Value = Array("No", "氏名", "ふりがな", "部署", "電話番号")
    outRow = 17

    For r = 2 To lastRow
        If InStr(1, Cells(r, 2).Value, key, vbTextCompare) > 0 Then
            Range("A" & outRow & ":E" & outRow).Value = _
                Range("A" & r & ":E" & r).Value
            outRow = outRow + 1
        End If
    Next r

    Range("G4").Value = outRow - 17
End Sub

これを実行すると、16行目の見出しの下に該当する行だけが上から順に並びます。
件数もG4セルに自動で入るので、検索ボックスと結果一覧が一度に完成します。
ボタンに登録しておけば、クリック1つで検索と一覧化が完了します。

オートフィルターを使う場合

VBAでは、オートフィルターで絞り込んでからコピーする方法もあります。

Range("A1:E13").AutoFilter Field:=2, Criteria1:="*" & key & "*"
Range("A2:E13").SpecialCells(xlCellTypeVisible).Copy Range("A17")

絞り込み結果をコピーするときは SpecialCells(xlCellTypeVisible) を使うのがポイントです。

タカヒロ

InStrの最後にある vbTextCompare がミソです。
ここを vbBinaryCompare にすると、大文字と小文字を区別して検索してしまいます。
日本語の名簿なら vbTextCompare のままで問題ないので、そのまま使ってくださいね。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったようにいかないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

関数の説明

今回使うCOUNTIF関数と、VBAのAutoFilter・Findの概要です。

COUNTIF関数（カウントイフ）

=COUNTIF(B2:B13,"*"&$G$2&"*")

AutoFilterメソッド（オートフィルター）

Range("A1:E13").AutoFilter Field:=2, Criteria1:="*" & key & "*"

Findメソッド（検索）

Set hit = Range("B2:B13").Find(What:=key, LookAt:=xlPart)

完成

G2セルに「佐藤」と入力すると、ヒット件数が 3件 と表示されました。
該当する行は色で強調され、下部には該当行だけの一覧が並んでいます。
思ったとおりに動きましたか？
検索キーを「鈴木」に変えると1件だけになり、件数と一覧が自動で切り替わります。
検索ボックスを1つ作っておくだけで、名簿の調べものがぐっとラクになります。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプル名簿で、ぜひ一度試してみてください。
さっと確認したいならCOUNTIFと条件付き書式、結果を一覧で残したいならVBAと使い分けてみてくださいね。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。

ぜひ試してみてください。