【Excel】エクセルで全シートを検索する方法（マクロ）
ブック内のシート数が増えると、特定の文字列や数値がどのシートに入力されているのかを探すだけでも時間がかかります。
Excelの検索機能でも対象をブック全体に変更できますが、検索結果を一覧化したい場合や、複数条件で繰り返し確認したい場合にはVBAマクロが便利です。
この記事では、エクセルで全シートを検索する方法を、検索範囲、VBAコード、結果の出力、エラーを防ぐ設定まで順番に解説します。
全シート検索をマクロ化すると、手作業でシートを切り替える時間を大幅に減らせます。
検索語句、見つかったシート名、セル番地、セルの内容を一覧シートへ出力する形にすると、後から確認しやすくなります。
なお、サンプルデータでは1行目に見出しがあり、2行目以降にデータが入力されているものとして説明します。
エクセルで全シートを検索するマクロの基本操作
それではまず、全シートを対象に文字列を検索し、結果を別シートへまとめる基本的な方法について解説していきます。
| シート名 | A列 見出し | B列 見出し | 検索対象 |
|---|---|---|---|
| 東京支店 | 担当者 | 商品名 | 商品名に「プリンタ」 |
| 大阪支店 | 担当者 | 商品名 | 商品名に「プリンタ」 |
| 検索結果 | シート名 | セル番地 | 検索結果を記録 |
ここでは、東京支店や大阪支店など複数のワークシートを巡回し、指定したキーワードを含むセルを検索結果シートへ転記します。
マクロを使う利点は、検索結果が表示されるだけでなく、どのシートのどのセルで見つかったかを記録できる点です。
検索用シートと結果用シートの準備
まず、検索語句を入力するためのシートと、検索結果を保存するためのシートを用意しましょう。
たとえば、検索条件シートのB2セルに検索したい文字を入力し、検索結果シートにはシート名、セル番地、見つかった値を出力する構成にします。
検索結果という名前のシートがない場合は、あらかじめ追加しておくとコードが分かりやすくなります。
検索対象のデータシートには、1行目に担当者、商品名、金額などの見出しを配置し、2行目以降に明細を入力しておくと管理しやすいでしょう。
検索結果シート自体を検索対象に含めると、前回の結果まで再検索するおそれがあります。
そのため、コードでは検索条件と検索結果のシートを除外する設定にします。
【操作のポイント】検索条件と検索結果のシート名は、VBAコード内の表記と完全に一致させます。
全シートを順番に調べるVBAコード
続いて、全てのシートを順番に確認するVBAコードを入力します。
AltキーとF11キーを押してVisual Basic Editorを開き、挿入から標準モジュールを選択してください。
標準モジュールに次のコードを貼り付けます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
Sub 全シートを検索する() Dim ws As Worksheet Dim resultWs As Worksheet Dim foundCell As Range Dim firstAddress As String Dim searchWord As String Dim resultRow As Long searchWord = Worksheets("検索条件").Range("B2").Value Set resultWs = Worksheets("検索結果") resultWs.Range("A2:D" & resultWs.Rows.Count).ClearContents resultWs.Range("A1:D1").Value = Array("シート名", "セル番地", "見つかった値", "行番号") resultRow = 2 For Each ws In ThisWorkbook.Worksheets If ws.Name <> "検索条件" And ws.Name <> "検索結果" Then Set foundCell = ws.Cells.Find(What:=searchWord, LookIn:=xlValues, LookAt:=xlPart) If Not foundCell Is Nothing Then firstAddress = foundCell.Address Do resultWs.Cells(resultRow, 1).Value = ws.Name resultWs.Cells(resultRow, 2).Value = foundCell.Address resultWs.Cells(resultRow, 3).Value = foundCell.Value resultWs.Cells(resultRow, 4).Value = foundCell.Row resultRow = resultRow + 1 Set foundCell = ws.Cells.FindNext(foundCell) Loop While Not foundCell Is Nothing And foundCell.Address <> firstAddress End If End If Next ws MsgBox "検索が完了しました。" End Sub |
このコードは、検索条件シートのB2セルにある文字列を取得し、各シートのセルから部分一致で探す内容です。
FindメソッドのLookAtにxlPartを指定すると、セル内容の一部に検索語句が含まれる場合も検出できます。
たとえば検索語句が「プリンタ」であれば、「カラープリンタ」「プリンタ用紙」なども検索対象になります。
【操作のポイント】標準モジュールに貼り付けた後は、ブックをExcelマクロ有効ブック形式で保存します。
マクロの実行と検索結果の確認
次に、検索条件シートのB2セルへ調べたいキーワードを入力します。
開発タブのマクロから全シートを検索するを選択し、実行ボタンをクリックしましょう。
開発タブが表示されていない場合は、ファイル、オプション、リボンのユーザー設定から開発にチェックを入れると表示できます。
マクロの実行後には、検索結果シートのA列からD列へ結果が並びます。
検索結果が0件であってもエラーにはならず、見出しだけが残る仕様です。
一覧に出力されたセル番地を見れば、該当データの場所へすぐ移動できます。
【操作のポイント】検索語句を変更するたびに同じマクロを実行すれば、以前の結果を消して新しい結果だけを確認できます。
検索範囲と一致条件の指定方法
続いては、検索範囲や完全一致と部分一致の違いを確認していきます。
| 指定内容 | 主な設定 | 検索例 |
|---|---|---|
| 部分一致 | LookAt:=xlPart | 「東京」で「東京都」を検出 |
| 完全一致 | LookAt:=xlWhole | 「東京」のみを検出 |
| 値の検索 | LookIn:=xlValues | 計算結果や表示値を確認 |
検索マクロでは、何を探すかだけでなく、どの範囲をどの条件で探すかを決める必要があります。
特に部分一致と完全一致を取り違えると、期待より多い結果または少ない結果になるかもしれません。
部分一致で文字列を探す設定
商品名や顧客名など、セルの一部に含まれる語句を見つけたいときは部分一致を選びます。
コードのLookAt:=xlPartが、この部分一致を指定する記述です。
たとえば、検索条件に「東京」と入力すれば、「東京支店」「東京都」「東京営業所」といったセルをまとめて取得できます。
表記ゆれを含めて広く確認したい業務では、部分一致が実用的です。
ただし、「田中」を探した際に「山田中学校」のような意図しない文字列までヒットすることがあります。
結果一覧を確認して不要な行を除くか、検索語句をより具体的に入力するとよいでしょう。
【操作のポイント】複数の候補を広く洗い出す初回検索では、部分一致から始めると見落としを減らせます。
完全一致で入力値を絞り込む設定
コード番号、社員番号、支店コードのように、セルの内容そのものを比較したい場合は完全一致を使います。
LookAt:=xlPartをLookAt:=xlWholeへ書き換えるだけで、完全一致の検索へ変更できます。
検索語句が「A-100」の場合、部分一致では「A-1000」も対象になりますが、完全一致なら「A-100」だけを抽出できます。
コード体系を扱う検索では、完全一致のほうが誤判定を抑えやすい設定です。
数値を検索する場合も、セルの表示形式によって検索結果が変わることがあります。
先頭のゼロを含むコードは文字列として管理すると、意図した検索を行いやすくなります。
完全一致へ変更する箇所は、LookAt:=xlWholeです。
部分一致へ戻す箇所は、LookAt:=xlPartです。
【操作のポイント】商品コードや管理番号では、検索前にセルの表示形式と入力形式を統一します。
列を限定して検索するコード
全セルを検索すると、メモ欄や計算用の補助列まで対象となり、結果が多くなりすぎる場合があります。
商品名がB列、担当者名がA列のように検索列が決まっているなら、Cellsではなく特定の列範囲を指定しましょう。
|
1 2 |
Set foundCell = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row).Find( _ What:=searchWord, LookIn:=xlValues, LookAt:=xlPart) |
この記述では、B列の2行目から最終データ行までが検索範囲になります。
1行目は見出しとして扱うため、検索対象から外れる構成です。
対象列を絞ると、処理時間の短縮と不要なヒットの削減を同時に期待できます。
【操作のポイント】見出し行を除くため、範囲の開始セルは通常B2のように2行目から指定します。
検索結果を一覧表へ出力する仕組み
続いては、見つかったデータを検索結果シートへ整理して出力する仕組みを確認していきます。
| シート名 | セル番地 | 見つかった値 | 行番号 |
|---|---|---|---|
| 東京支店 | B5 | カラープリンタ | 5 |
| 大阪支店 | B8 | プリンタ用紙 | 8 |
一覧形式にしておくと、検索結果をコピーして報告資料へ使ったり、フィルターで絞り込んだりできます。
画面に検索ダイアログだけを表示するより、履歴として確認しやすい点もメリットです。
シート名とセル番地を記録する方法
検索後に確認したい情報は、見つかった値だけではありません。
元のデータがどのシートにあり、どのセルに保存されているかも記録することで、修正作業へすぐ移れます。
ws.Nameは現在検索しているシート名を返し、foundCell.Addressはヒットしたセル番地を返します。
検索結果の一覧には、値と位置の両方を出力することが実務では重要です。
セル番地がB5のように表示されれば、該当シートを開いて名前ボックスにB5と入力するだけで移動できます。
行番号も同時に出しておくと、元データの行を確認する場面で役立ちます。
【操作のポイント】検索結果の列見出しは固定し、A列をシート名、B列をセル番地のように役割を決めます。
検索前の結果を消去する方法
同じ検索結果シートを繰り返し使う場合、前回の結果が残ると新旧のデータを見分けにくくなります。
resultWs.Range(“A2:D” & resultWs.Rows.Count).ClearContentsという記述は、2行目以降の内容を消去するためのものです。
1行目の見出しは消さずに残すため、実行後も一覧表の意味を把握しやすくなります。
ClearContentsはセルの値だけを消し、罫線や塗りつぶしなどの書式を維持できる点が便利です。
出力列を増やした場合は、A2:DのDを実際の最終列へ変更してください。
たとえばE列まで使うなら、A2:Eと指定します。
結果を消す範囲は、見出し行を除いた2行目以降です。
書式も消したいときはClearContentsではなくClearを使いますが、通常はClearContentsのほうが扱いやすいでしょう。
【操作のポイント】検索結果シートを手入力でも使う場合は、マクロ専用の出力範囲を別に確保します。
見つかったセルを元シートへ移動する方法
一覧を確認した後、元データを直接開きたい場合は、検索結果の行を選択して移動する処理を追加できます。
一覧のA列にシート名、B列にセル番地を保存しているため、その情報を使って該当セルを選択します。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub 検索結果から元のセルへ移動する() Dim targetSheet As String Dim targetAddress As String targetSheet = ActiveCell.EntireRow.Cells(1, 1).Value targetAddress = ActiveCell.EntireRow.Cells(1, 2).Value Worksheets(targetSheet).Activate Range(targetAddress).Select End Sub |
検索結果シートで任意の結果行を選んでから実行すると、その行に記録されたシートとセルへ移動します。
検索から修正までの流れを短縮したい場合に便利な補助マクロです。
【操作のポイント】移動用マクロは検索結果の見出し行ではなく、2行目以降のデータ行を選択して実行します。
複数キーワードと数値を検索する応用
続いては、複数のキーワードや数値を条件として検索する応用方法を確認していきます。
| 検索条件シート | 入力例 | 用途 |
|---|---|---|
| B2 | プリンタ | 商品名の検索 |
| B3 | 東京 | 支店名や住所の検索 |
| B4 | 10000 | 金額の確認 |
単一キーワード検索に慣れたら、複数語の検索や数値の検索にも対応させると用途が広がります。
ただし、条件を増やすほど検索結果の意味を明確にし、出力列を整理することが大切です。
複数の検索語句を順番に処理する方法
検索条件シートのB2からB10に複数のキーワードを入力しておき、空白セルまで順番に検索する方法があります。
この場合は、検索語句ごとに結果へ検索語句の列を追加すると、どの条件で見つかったのかを判別できます。
複数キーワードを一括処理すると、商品名、取引先名、担当者名を連続で調べる作業を自動化できます。
For文を使ってB2から最終行まで繰り返し、各値をsearchWordへ代入して検索処理を実行します。
検索語句が空白の場合は処理を飛ばす条件を入れると、余分な検索を防げます。
検索語句ごとに結果を分ける場合は、結果表へ検索語句列を追加します。
検索語句を同じ結果表へまとめる場合は、検索日時も出力すると管理しやすくなります。
【操作のポイント】検索条件の一覧では、空白行を途中に作らず、上から連続してキーワードを入力します。
数値と日付を検索する場合の注意点
数値や日付を検索する場合は、表示形式と実際に保存されている値の違いに注意が必要です。
たとえば日付は画面上で2026年9月21日と表示されていても、Excel内部では連続した数値として管理されています。
LookIn:=xlValuesを使えば画面に表示されている値を対象にしやすくなります。
日付検索では、検索条件セルにも元データと同じ表示形式を設定すると失敗が減ります。
金額のようにカンマ付きで表示している数値も、検索条件の入力方法によっては一致しないことがあります。
検索結果が出ないときは、文字列として入力されていないか、全角と半角が混在していないかを確認しましょう。
【操作のポイント】日付や金額は、検索語句を直接コードに書かず、条件セルから読み込む形にすると変更が簡単です。
大文字小文字と全角半角の確認
英字を含むデータでは、大文字と小文字を区別して検索したいことがあります。
FindメソッドのMatchCase:=Trueを追加すると、ABCとabcを別の文字列として扱えます。
通常はMatchCase:=Falseまたは指定なしでよく、大文字と小文字を区別しない検索になります。
全角と半角、スペースの有無、記号の違いは検索漏れの原因になりやすいポイントです。
表記ゆれが多いデータでは、検索前にTRIM関数や置換機能で不要な空白を整理する方法もあります。
元データを修正できない場合は、複数の検索語句を登録して対応する方法が現実的です。
【操作のポイント】検索漏れが疑われる場合は、完全一致ではなく部分一致で候補を広げて確認します。
全シート検索マクロのエラー対策
続いては、全シート検索マクロで起こりやすいエラーと対策を確認していきます。
| 症状 | 主な原因 | 確認内容 |
|---|---|---|
| インデックスが有効範囲にありません | シート名の不一致 | 検索条件と検索結果の名前 |
| 検索結果が出ない | 条件や範囲の相違 | 部分一致、対象列、表記 |
| 処理が遅い | 対象範囲が広すぎる | 使用列と最終行 |
VBAのエラーは、コードそのものよりもシート名や対象範囲の設定違いで起こるケースが少なくありません。
表示されたメッセージと、強調表示されたコード行を確認すると原因を絞り込みやすくなります。
シート名が一致しない場合の確認
Worksheets(“検索条件”)のように書いた部分は、実際のシート名と完全に一致している必要があります。
検索条件というシート名が検索条件表になっている場合、マクロは対象を見つけられません。
シート名に末尾スペースが含まれている場合も、見た目では気付きにくいため注意しましょう。
シート名はタブ上の文字をコピーして、VBAコードの引用符内へ貼り付けると入力ミスを防げます。
ブック保護が設定されていると、結果用シートの内容を消去または書き込みできないことがあります。
必要に応じて保護の状態も確認してください。
【操作のポイント】シート名を変更したときは、検索対象から除外する条件の名前も一緒に更新します。
検索結果が0件になる場合の確認
検索結果が出ない場合は、まず検索条件シートのB2セルが空白ではないかを確認します。
次に、対象データが数式の結果なのか、セルに直接入力された値なのかを確かめましょう。
検索語句に不要な空白が含まれていると、完全一致ではヒットしません。
最初は部分一致で検索し、候補が見つかることを確認してから条件を厳しくする方法が安全です。
非表示シートもWorksheetsコレクションに含まれるため、通常のコードでは検索対象になります。
非表示シートを除外したい場合は、ws.Visible = xlSheetVisibleという条件を追加します。
【操作のポイント】検索条件にコピーした文字列を使う場合は、前後の空白や改行が混ざっていないか確認します。
処理時間を短縮する設定
数十枚のシートや数万行のデータを全セル検索すると、マクロの完了まで時間がかかることがあります。
対象列を限定することに加えて、実行中の画面更新と自動計算を一時停止すると処理を軽くできます。
|
1 2 3 4 5 6 7 |
Application.ScreenUpdating = False Application.Calculation = xlCalculationManual '検索処理 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True |
処理の最後には、停止した設定を必ず元へ戻します。
画面更新を戻さないと、マクロ終了後もExcel画面が更新されないように見えるため注意が必要です。
高速化の基本は、検索範囲を必要なデータ列だけに絞ることです。
高速化の設定は補助的な方法として使い、終了時に元へ戻す記述をセットで入れます。
【操作のポイント】大量データで試す前に、コピーしたブックで少量のデータを使って動作確認します。
まとめ エクセルで全シートを検索するマクロの方法
エクセルで全シートを検索するマクロは、複数のワークシートに散らばった文字列、数値、商品名、担当者名などを確認したいときに役立ちます。
まずは検索条件シートのセルにキーワードを入力し、全ワークシートを巡回するVBAを実行する方法から始めましょう。
検索結果をシート名、セル番地、値、行番号の一覧として残すと、見つけた後の確認や修正が格段に進めやすくなります。
部分一致は幅広く候補を探したい場面に、完全一致はコードや管理番号を正確に照合したい場面に向いています。
データ量が多い場合は、検索対象を必要な列に絞ることで、不要なヒットと処理時間を減らせます。
シート名の不一致、検索語句の空白、全角半角の違いは、検索できないときに優先して確認したい項目です。
マクロ有効ブックとして保存し、信頼できるファイルだけでVBAを実行することも忘れないようにしましょう。
