【Excel VBA】表内のデータ簡単検索ツール（コピペですぐ使える）
日常業務を行うにあたって、過去の蓄積されたデータをエクセル表で管理し、必要に応じて都度検索することがあると思います。
その際には、エクセルで管理したデータを検索するときにエクセル機能の「検索」を利用していましたが、検索結果として大量に一致した際の確認がしにくいと感じたので、検索結果が見やすいツールを作成しましたので、ご活用頂けたらと思います。
わたしは、過去の質疑回答集を蓄積データとしてエクセルで管理し、当ツールを利用して検索結果をスムーズに表示できるようにしていました。
＜更新履歴＞
（2022年5月13日：更新）
・検索実行時に検索対象の文字が存在しない場合は、検索対象がない旨のメッセージを表示させてフィルターしないように変更
・検索方法として「AND検索」「OR検索」を選択可能に変更
記事の目次
『表内のデータ簡単検索ツール』の概要
エクセルの表を作成し、その中に日々業務で使用しているデータを入れ込んでいきます。
そのデータがある程度の数分だけ蓄積された状態で、そのデータ内から任意の文字を検索したい場合に、検索ワードを入れて検索処理を行えば対象行のみが赤く表示され、検索が容易にできるようになります。
当ツールの使用方法
基本的な機能、操作方法の説明
１．蓄積データ検索マクロのVBAコードを含んだExcelファイル（***.xlms）を開きます。
２．対象の表内に検索対象とするデータを入れ込んだ状態にします。
３．「検索ワード」の欄に検索したい文字を入力して「検索」ボタンを押す。
４．検索ワードの内容での部分一致で検索結果が表示されました。
留意事項
検索文字間にスペースを入れた場合は、「AND条件」、「OR条件」にて検索
AND条件の場合
例えば、「問合せ ５」で検索ワードを入れた場合は、一つのセル内で”問合せ” という文字が入っていて、かつ “５” という文字が入っているセルを検索します。
検索結果として、両方を満たす値が入っているセルが検索され、表示されます。
OR条件の場合
同様に「問合せ ５」で検索ワードを入れた場合は、一つのセル内で”問合せ” と “５” のどちらかの文字が入っているセルを検索します。
検索対象の列について
標準では、表内のタイトル行の色が濃紺になっている箇所（D , E , F , J 列）が検索対象として設定されていますが、VBAコード内の値を変えれば、検索対象列を自由に変更することができます。
表の項目の列数を変更する場合
現行のツールでは表の列が「J列」までで作成されていますが、項目数を増減する場合は、以下の対応をしてください。
＜項目を減らす場合＞
＜項目を増やす場合＞
使用する際の事前準備
このページの下部にある「Excelファイルのサンプル」からExcelファイルを取得し、「準備の手順」内にあるVBAコードを取得したExcelファイルに記載して保存すれば、すぐに当ツールを使用できます。
以下にその手順を説明していきます。
準備の手順
１．「サンプルのダウンロードはこちら」からサンプル（Excelファイル）をダウンロードします。
※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています
２．Excelを開き、「開発」タブをクリックして、「Visial Basic」をクリックします。
２．「Microsoft Excel Object」にて右クリックして「標準モジュール」を挿入します。
３．右側の空欄スペースに下記のVBAコードをコピペで入力します。
以下のVBAコードを「Module1」内のエディターにコピーして貼り付けます。
※VBAコードの右上のアイコンをクリックするとソースコードをコピーできます
Option Explicit
'---------（設定値）---------------------------
Private Const KEYWORD_POINT = "C4" '１．検索ワードを入力するセル
Private Const SEARCH_TYPE_POINT = "J4" '２．検索条件を指定するセル
Private Const HEAD_ROW = 8 '３．テーブル見出し行の行番号
Private Const HEAD_COL_NUM = 1 '４．テーブル見出し列の列番号
Private Const WORK_COL_NUM = 11 '５．作業列の番号
Private Const SEARCH_TGT_COL = "D,E,F,J" '６．検索対象の列をカンマ区切りで指定する
'---------（メッセージ）-----------------------
Private Const Msg1 = "検索ワードを入力してください。"
Private Const Msg2 = "検索対象が見つかりませんでした。"
'----------------------------------------------
Dim SearchType As String
Dim HeadCellPoint As String
Dim DataCellPoint As String
Dim WorkCol As String
'===============================================
'検索ボタンを押した時に実行される検索メイン処理
'===============================================
Sub 検索_Click()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
If Trim(Range(KEYWORD_POINT).Value) = "" Then
MsgBox Msg1
Exit Sub
End If
Dim sht As Worksheet
Set sht = ActiveSheet
Dim lastRow, iRow, iCol
Dim keyword As Variant
Dim keywordArr As Variant
Dim tgtCol() As String
Dim tgtWord(0) As String
Dim searchResult() As String
Dim blnExist As Boolean: blnExist = True
Dim blnSearchExist As Boolean: blnSearchExist = False
WorkCol = Split(Cells(1, WORK_COL_NUM).Address, "$")(1)
HeadCellPoint = Split(Cells(1, HEAD_COL_NUM).Address, "$")(1) & HEAD_ROW
DataCellPoint = Split(Cells(1, HEAD_COL_NUM).Address, "$")(1) & HEAD_ROW + 1
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
Range(HeadCellPoint).AutoFilter
End If
Call ClearSheetColor
'検索ワードを取得する
keyword = Trim(Worksheets(sht.Name).Range(KEYWORD_POINT).Value)
'検索条件を取得する
SearchType = Worksheets(sht.Name).Range(SEARCH_TYPE_POINT).Value
'検索ワードの間にスペースがあった場合は文字を分割して取得する
keyword = Replace(keyword, " ", " ")
keywordArr = Split(keyword)
'検索対象の列を格納する
tgtCol = Split(SEARCH_TGT_COL, ",")
lastRow = sht.UsedRange.Rows.Count + sht.UsedRange.Row - 1
'検索対象の行がある分だけ繰り返す
For iRow = HEAD_ROW + 1 To lastRow
'検索対象の列がある分だけ繰り返す
For iCol = 0 To UBound(tgtCol)
tgtWord(0) = Range(tgtCol(iCol) & iRow).Value
For Each keyword In keywordArr
searchResult = Filter(tgtWord, keyword)
'AND検索の条件で検索する場合
If InStr(SearchType, "AND") <> 0 Then
If UBound(searchResult) = -1 Then
blnExist = False
Exit For
End If
'OR検索の条件で検索する場合
Else
blnExist = False
If UBound(searchResult) <> -1 Then
blnExist = True
Exit For
End If
End If
Next keyword
If blnExist Then
Range(WorkCol & iRow).Value = "1"
Range(tgtCol(iCol) & iRow).Font.Color = RGB(255, 0, 0)
blnSearchExist = True
End If
blnExist = True
Erase tgtWord, searchResult
Next iCol
Next iRow
'検索対象が存在する場合はフィルターし、存在しない場合はメッセージを表示させる
If blnSearchExist Then
sht.Range(HeadCellPoint & ":" & WorkCol & Format(HEAD_ROW)).AutoFilter Field:=WORK_COL_NUM, Criteria1:="<>"
Else
MsgBox Msg2
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
'===================================================================
'フィルター解除ボタンを押した時に実行されるフィルター解除メイン処理
'===================================================================
Sub フィルター解除_Click()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
HeadCellPoint = Split(Cells(1, HEAD_COL_NUM).Address, "$")(1) & HEAD_ROW
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
Range(HeadCellPoint).AutoFilter
End If
Call ClearSheetColor
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
'-------------------------------------
'色の変更、作業行の初期化等を行う
'-------------------------------------
Sub ClearSheetColor()
Dim sht As Worksheet
Set sht = ActiveSheet
Dim iRow
Dim iCol
iRow = sht.UsedRange.Rows.Count + sht.UsedRange.Row - 1
iCol = sht.UsedRange.Columns.Count + sht.UsedRange.Column - 1
sht.Range(Range(DataCellPoint), Cells(iRow, iCol)).Font.ColorIndex = 1
sht.Range(WorkCol & HEAD_ROW + 1 & ":" & WorkCol & iRow).Clear
End Sub
４．ファイルを保存する際は「Excelマクロ有効ブック」を選択して、保存します。
７．「検索ボタン」と「オートフィルター解除ボタン」のそれぞれにマクロを設定します。
＜検索ボタン＞
＜オートフィルター解除ボタン＞
それぞれのボタンを押せば、検索とフィルター解除が実施できるようになります。
これで事前準備は完了です。
あとは「当ツールの使用方法」で記載された方法でツールを実行できます。
また、このようなExcelVBAを用いた業務効率化を行うときに、以下の書籍が初心者向けでとても参考になると思いますので、良かったらご参照ください。
なお、当マクロの開発環境として、OSは “Windows10” 、Excelのバージョンは “Microsoft Office 365″ で行っており、当環境では動作確認ができていますが、他のすべての環境で正常に動作するかは確認できていません。
正常に動作しない場合は、コメントいただければ幸いです。
サンプルのダウンロードはこちら
表内データ簡単検索ツール
上記よりダウンロードして、記事の途中にありましたVBAコードを組み込んでツールを使用してください。
※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています
他に要望等ありましたら、可能な限り改修等を対応しますのでコメント頂ければと思います。
＜このツールが『結構使える！』と思ったら、下のグッドボタンを押していただけたら幸いです＞
作業列をKではない列に変更することは可能でしょうか。
AQまでの列を使用したいと考えております。
管理人のRHです。
ご返答が遅れまして、申し訳ありません。
yさん、ツールのご使用とコメントありがとうございます。
「AQ列」まで使用するとのことですので、作業列をその次の列である「AR列」にすると考えて、以下を修正頂ければと思います。
・5行目を「Private Const WorkCol = “AR”」に変更
・8行目を「Private Const WorkColRow = “AR5:AR”」に変更
・89行目を「sht.Range(“A4:AR4″).AutoFilter Field:=44, Criteria1:=”<>“」に変更
上記で目的が実現できない場合は、再度ご連絡をお願いします。
ご返信いただきありがとうございます。
3点の修正をしたところ、問題なくマクロが実行できました。
とても有益な情報をありがとうございました。
仕事で活用させていただきます。
管理人のRHです。
問題なくマクロが実行でき、目的が達成されたようで良かったです。
また何かありましたらコメント願います。
こちらのマクロを使わせていただいているのですが、検索の際、「123AB/AA」とあった場合に検索で「123AA」と検索しても「123AB/AA」をヒットさせる方法はないでしょうか。コードなど教えて頂けると幸いです。
再コメントです。こちらのマクロを使わせていただいているのですが、検索の際、「123AB/AA」とあった場合に検索で「123AA」と検索しても「123AB/AA」をヒットさせる方法はないでしょうか。コードなど教えて頂けると幸いです。
管理人のRHです。
コメントいただきありがとうございます。
返信が遅くなり、申し訳ありません。
さ様が最終的に実現したいこととしては少し違っているかもしれませんが、
今のコードですと、「123AB/AA」を見つけたい場合に「123 AA」（※間に空白）を入れるとand条件で検索されて検索した結果として表示されますが、それで解決しますでしょうか。
＞「123AB/AA」とあった場合に検索で「123AA」と検索しても「123AB/AA」をヒットさせる方法はないでしょうか。
質問なのですが、検索を行う際に検索ワードが無かった場合最下層にある空欄のセルが(データが入っていないところのセル)最上部に出てくるのですが、そのあと検索しても該当がないのかと思いオートフィルター解除のボタンや検索のボタンを押してもエクセルがフリーズしてしまいエクセルを再起動しないといけない状態になります。
検索のキーワードが該当ない場合、該当ありませんでリセットできるような状態にできませんでしょうか？
管理人のRHです。
コメントいただきありがとうございます。
検索を行った際に検索ワードが無かった場合、その後にオートフィルター解除のボタンや検索のボタンを押してもエクセルがフリーズしてしまうという現象は、当方の環境では確認できませんでした。
そのため、当ツールを検索対象がない場合に、「該当ありません」という旨のメッセージを表示してフィルターしないよう仕様変更しましたので、これでこ様の要望が実現できるかと思いますので、是非お試しください。
おはようございます。
早急の対応ありがとうございます。
フリーズが解消されました。
数万件のリスト検索をしているので処理が追い付かなかったのかもしれません。
本当にありがとうございます。
こんにちは。
使わせていただきたいと思い、ダウンロードさせていただきました。
教えてください。
2021年10月15日 10:53 AMの質問のように、R行を作業列としたく、返信の内容を行ってみましたが、上手くいきません。お手数おかけしますが、お教え願います。
どうぞよろしくお願いします。
管理人のRHです。
ｙｎさん、ツールのご使用とコメントありがとうございます。
2021年10月15日 10:53 AM時の質問が当記事を更新する前のVBAコードに対しての回答になっておりましたので、同様に記載しても上手く動かなかったものと思われます。
本日改めてメンテナンス性が高くなるよう記事のVBAコード部分を更新しました。
ｙｎさんが実現したい内容（R行を作業列とする）は、当記事（2022/4/20時点）のVBAコードにおいて以下の２か所を修正頂ければ動作すると思いますので、ご確認ください。
・9行目を「Private Const WorkCol = “R”」に変更
・10行目を「Private Const WorkcolNum = 18」に変更
上記で目的が実現できない場合は、再度ご連絡をお願いします。
ご返答いただき、ありがとうございます。
検索出来ました！
ありがとうございます。
あと、もう1点だけ、お教え願いますでしょうか。
検索範囲をD行・E行にしているのですが、それぞれ検索をかけるとちゃんと引っかかってきますが、例えば、
D行に会社名「㈱ABC」、E行に商品名「DEF」を入力
↓
検索に「ABC DEF」と検索 すると、「検索対象は見つかりませんでした」と出てきました。
and検索の意味を私がしっかり理解していないのかもしれませんが、両方引っかかるというより、それぞれの項目に引っかかると考えていいでしょうか？
よろしくお願いします。
管理人のRHです。
ｙｎさん、無事使用できたようで良かったです。
追加のお問い合わせに関して、
ｙｎさんが行った下記の動作はツールのルールであるAND条件としては、そのような動きになってしまいます。
＞D行に会社名「㈱ABC」、E行に商品名「DEF」を入力
＞↓
＞検索に「ABC DEF」と検索 すると、「検索対象は見つかりませんでした」と出てきました。
AND検索というのが、「複数の条件をいずれも満たすもの」を検索結果として表示させるものになります。
※今回の場合ですと、一つのセルに「ABC」と「DEF」を含むセルを検索結果に出させる挙動
ｙｎさんがおっしゃる挙動で動かす場合は、OR検索というものになり「複数の条件のうち少なくともいずれか一つを満たすもの」を検索結果として表示させることになります。
もし、そのような動作（OR検索）が必要でしたら、必要に応じてカスタマイズできるようにツールを変更しますので、その旨追加でコメント頂ければと思います。
ご返答いただき、ありがとうございます。
OR検索というものがあるのですね、ＡＮＤ検索と混同しておりました、お教えいただきありがとうござます。
希望としては、ＯＲ検索がしたいです。
現在、２つのデーター用に活用させていただいています。
１）B、D、E行
２）B、D、E、F、G、M行
各上記行が、検索に引っかかるように設定しています。
ややこしくて済みませんが、お教えいただけますでしょうか？
よろしくお願いします。
管理人のRHです。
ｙｎさん、コメントありがとうございます。
（返信できる件数の上限を超えましたので、新規でコメントします。）
ご希望された「ＯＲ検索」ができるように改修しましたが、要望に応えられるものになっていますでしょうか。
なお、１）B、D、E行 ２）B、D、E、F、G、M行を検索対象にする場合は、以下のとおり修正していただければ動作するかと思います。
・VBAコードの11行目を Private Const SearchTgtCol = “B,D,E,F,G,M” に変更する。
・Excelファイルを再度ダウンロードしていただき、「J3」セルを「OR検索」に変更する。
（ダウンロードできるExcelファイルを新しいバージョンに変更しました）
一度ご確認をお願いします。
何度もご返答いただき、ありがとうございます。
とても分かりやすくタブをつけていただいて、誰でも使いやすいようになっており、とても助かります。
もう1点だけ、確認させてください。何度も済みません。
複数条件に合致したものだけを抽出、としたいのですが、それはまたOR検索でもAND検索でもなく、別検索になりますでしょうか。
例えば、B,D,F行が検索対象行とすると、Bに会社名、Dに商品名、Fに単価を入れています。検索ボックス「アマゾン 〇〇商品」と入力すると、「アマゾン 〇△商品」「楽天市場 〇〇商品」も含まれて検索結果に出てきます。
「アマゾン」で買った「〇〇商品」の単価はいくらだったか？が出てきてほしい場合は、また違った検索になりますでしょうか。
私の質問の仕方がイマイチなため、何度も質問重ねて申し訳ございません。
よろしくお願いします。
管理人のRHです。
ｙｎさん、ご確認ありがとうございます。
なるほど、そういうことですか、理解できず失礼しました。
イメージとしては、項目をまたいだAND検索を行いたいという意味だと理解しました。
おそらく、B列とD列の値が一つのセルに入ってる状態なら、AND条件で検索をすればｙｎさんの行いたいことを実現できるのかなと思います。
現状としては当ツールは「一つのセルに対して検索」をする機能しかないので、ｙｎさんが行いたい仕様になっていません。
可能なら、その機能を実現したツールを作りますので、少しお待ちいただけますでしょうか。
管理人のRHです。
ｙｎさんへ、項目をまたいだAND検索ができるツールを用意できましたので、以下のページからツールをご確認ください。
https://resthill.blog/excel-vba-tool9/
必要に応じて、表内の列等が多くなった場合などは、VBAコード内の（設定値）を変えることで対応できるかと思います。
不明点等ありましたら、改めてコメントをください。
早々にご返答いただき、ありがとうございます。
私こそ、上手く質問出来ておらず、大変失礼しました。
ツール確認いたしました。とても分かりやすく、希望通りです！
何度もお付き合いいただき、ありがとうございました。
管理人さんのサイトで、マクロの凄さを知りました。
マクロって、メンドクサソウ・何書いてるか分からないし、関数は分かるからそれで何とかしよう・・としていました。つまづいた結果、検索して、こちらのサイトに辿り着きました。
他サイトと比べて、大変見やすい・分かりやすい・ど素人でも試しやすかったです。マクロを知って楽しかったので、サイトでも勉強させていただきます。
ど素人の私に対して親身になって、迅速にお答えいただきありがとうございました！
管理人のRHです。
ｙｎさん、ご返答ありがとうございます。
（返信できる件数の上限を超えましたので、新規でコメントします。）
ご希望通りのものを作ることができてよかったです。そしてお褒め頂き大変ありがとうございます。
VBAマクロは細かな業務効率化をするために非常に有効な方法だと思うので、是非、当サイトも含めて色々なもので学んでいただき、日々の業務にご活用いただけたらと思います。