複数のシートやワークブックで値を迅速に検索するにはどうすればよいですか？

特定の値がExcel内の異なるシートや、複数のワークブックにまたがって存在する可能性がある場合、それを検索する必要がありましたか？これは、特に大規模なプロジェクト、月次レポートを扱う場合や、複数のファイルで管理されている情報を統合する際に一般的なシナリオです。各シートやファイルを手動で見るのは、時間のかかるだけでなくエラーも発生しやすいものです。このチュートリアルでは、1つのワークブック内、複数のワークブック間、または数式ベースや統合データソリューションを使用して効率的にデータを検索するためのいくつかの効果的な方法をご紹介します。これらのアプローチは、日常のExcelワークフローやデータ分析プロジェクトで遭遇する実用的なニーズに対応しています。

検索と置換機能を使用してワークブック内の複数のシートで値を検索する

VBAを使用してフォルダ内のすべてのワークブックで値を検索する

Kutools for Excelを使用して開いている複数のワークブックで値を迅速に検索する

Excelの数式を使用して複数のシートで値を検索する

検索と置換機能を使用してワークブック内の複数のシートで値を検索する

Excelの「検索と置換」機能は、同じワークブック内の複数のワークシートで特定の値を素早く見つけるための基本的でありながら効果的な方法です。この方法は、どのシートを検索したいかがわかっている場合や、データが1つのファイル内で比較的整然と構造化されている場合に最も役立ちます。ただし、異なるファイルや閉じられたワークブックを横断して検索することはできませんが、簡単な検索方法を提供します。

1. 検索に含めたいシートタブを選択するために、Ctrlキーを押しながらシートタブバーにある各ワークシートを個別にクリックします。これにより、選択されたすべてのシートに対して同時に検索が行われます。スクリーンショットをご覧ください:

2. 目的のシートを選択したら、Ctrl + F を押して「検索と置換」ダイアログボックスを開きます。「検索」タブの「検索内容」テキストボックスに探したい値を入力し、「すべて検索」ボタンをクリックします。Excelは、選択したシート上にある検索値を含むすべてのセルとその場所をすぐに表示します。スクリーンショットをご覧ください:

ヒント: 「検索と置換」ツールは、開いていて表示されているシートのみを検索対象とします。追加のシートで検索したい場合は、上記のようにそれらを選択してください。もしシートを見逃した場合は、選択に含めた後に操作を繰り返してください。

注意事項: この方法では、閉じられたまたは非表示のワークブックを検索することはできず、自動的にセルをハイライトすることもありません。結果はナビゲーション用にリストアップされるだけです。

トラブルシューティング: 期待される結果が表示されない場合は、シートの選択を再確認し、フィルタやセル保護が検索を隠したり制限したりしていないか確認してください。

複数のシートやワークブックにわたって値を簡単に検索して置換

Kutools for Excelの高度な「検索と置換」機能は、複数のシートや開いているすべてのワークブックにわたって値を検索して置換する効率的な方法を提供します。この高度な機能を使用することで、大規模なデータセットを扱う際に時間を節約し、エラーを排除でき、Excelでの作業をより迅速かつ正確に行うことができます。

Kutools for Excel - 必要なツールを300以上搭載し、Excelの機能を大幅に強化します。永久に無料で利用できるAI機能もお楽しみください！今すぐ入手

VBAを使用してフォルダ内のすべてのワークブックで値を検索する

まだ開いていないファイルを含む、フォルダに保存されている複数のワークブックで特定の値を検索する必要がある場合、Excelの組み込みツールではこれを直接行うことはできません。このような場合、VBA（Visual Basic for Applications）マクロを使用することで、フォルダ内の各ワークブックを順番に開き、すべてのワークシートをスキャンし、一致する箇所を記録するプロセスを自動化できます。このアプローチは、定期的な監査やアーカイブされたバッチファイル内の値チェックに非常に実用的です。

1. Excelで新しい（または空白の）ワークブックを開き、検索結果を表示させたいセル（通常はセルA1）を選択します。Alt + F11を押してMicrosoft Visual Basic for Applicationsエディタウィンドウを起動します。

2. VBAエディタ内で、挿入 > モジュールに移動して新しいモジュールを作成し、次のVBAコードをモジュールウィンドウに貼り付けます。

VBA: フォルダ内のすべてのワークブックで値を検索する

Sub SearchFolders()
'UpdatebyKutoolsforExcel20200913
    Dim xFso As Object
    Dim xFld As Object
    Dim xStrSearch As String
    Dim xStrPath As String
    Dim xStrFile As String
    Dim xOut As Worksheet
    Dim xWb As Workbook
    Dim xWk As Worksheet
    Dim xRow As Long
    Dim xFound As Range
    Dim xStrAddress As String
    Dim xFileDialog As FileDialog
    Dim xUpdate As Boolean
    Dim xCount As Long
    Dim xAWB As Workbook
    Dim xAWBStrPath As String
    Dim xBol As Boolean
    Set xAWB = ActiveWorkbook
    xAWBStrPath = xAWB.Path & "\" & xAWB.Name
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a forlder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    xStrSearch = "KTE"
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Set xOut = Worksheets.Add
    xRow = 1
    With xOut
        .Cells(xRow, 1) = "Workbook"
        .Cells(xRow, 2) = "Worksheet"
        .Cells(xRow, 3) = "Cell"
        .Cells(xRow, 4) = "Text in Cell"
        Set xFso = CreateObject("Scripting.FileSystemObject")
        Set xFld = xFso.GetFolder(xStrPath)
        xStrFile = Dir(xStrPath & "\*.xls*")
        Do While xStrFile <> ""
            xBol = False
            If (xStrPath & "\" & xStrFile) = xAWBStrPath Then
                xBol = True
                Set xWb = xAWB
            Else
                Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
            End If
            For Each xWk In xWb.Worksheets
                If xBol And (xWk.Name = .Name) Then
                Else
                Set xFound = xWk.UsedRange.Find(xStrSearch)
                If Not xFound Is Nothing Then
                    xStrAddress = xFound.Address
                End If
                Do
                    If xFound Is Nothing Then
                        Exit Do
                    Else
                        xCount = xCount + 1
                        xRow = xRow + 1
                        .Cells(xRow, 1) = xWb.Name
                        .Cells(xRow, 2) = xWk.Name
                        .Cells(xRow, 3) = xFound.Address
                        .Cells(xRow, 4) = xFound.Value
                    End If
                    Set xFound = xWk.Cells.FindNext(After:=xFound)
                Loop While xStrAddress <> xFound.Address
                End If
            Next
            If Not xBol Then
            xWb.Close (False)
            End If
            xStrFile = Dir
        Loop
        .Columns("A:D").EntireColumn.AutoFit
    End With
    MsgBox xCount & " cells have been found", , "Kutools for Excel"
ExitHandler:
    Set xOut = Nothing
    Set xWk = Nothing
    Set xWb = Nothing
    Set xFld = Nothing
    Set xFso = Nothing
    Application.ScreenUpdating = xUpdate
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

3. F5キーを押すか、実行ボタンをクリックしてこのマクロを実行します。「フォルダの選択」ダイアログウィンドウが表示され、検索したいワークブックが含まれるフォルダを選択できます。スクリーンショットをご覧ください:

4. [OK]をクリックします。検索が完了すると、指定した値が含まれているセルの総数がメッセージボックスに表示されます。スクリーンショットをご覧ください:

5. メッセージを閉じるには[OK]をクリックします。見つかったすべての場所が新しいワークシートにリストアップされ、ワークブック名、ワークシート名、セル参照、および正確なセルの内容がレビューのために表示されます。

ヒント: 現在の検索語はVBAで「KTE」と設定されています。コード内の xStrSearch = "KTE" の行を変更することで、任意の検索したい値にカスタマイズできます。

注意事項: 実行前に、関連するすべてのワークブックが保存され、閉じられていることを確認してください（マクロを実行するワークブックは開いたままでも構いません）。多くのまたは複雑なExcelファイルがある大きなフォルダの処理には時間がかかる場合がありますので、実行中にマクロを中断しないでください。

トラブルシューティング: エラーが発生した場合、すべてのファイルが本物のExcelワークブックである（破損していない、またはパスワード保護されていない）ことと、マクロのセキュリティ設定がコードの実行を許可していることを確認してください。マクロが終了しない場合は、まず少数のファイルで試してみてください。

Kutools for Excelを使用して開いている複数のワークブックで値を迅速に検索する

すでに開いている複数のワークブックで検索を行いたい場合、Kutools for Excelでは専用の「検索と置換」ペインが提供され、プロセスをより簡単かつ整理されたものにします。これは、スクリプト作成や複雑な設定なしにワークブックを迅速にスキャンするのに特に役立ち、複数のファイルを同時に扱うことが多いユーザーにとって使いやすく、直感的なツールです。

1. 開いているワークブックのいずれかで、「 Kutools 」タブに移動し、「 ナビゲーション」を選択します。そして、「 検索と置換 」ボタン  をクリックして、「 検索と置換 」ペインを表示します。通常、このペインはExcelウィンドウの左側にあります。スクリーンショットをご覧ください:

2. 「 検索 」タブで、検索したい値を「 検索内容 」テキストボックスに入力します。「 すべてのワークブック 」を「 対象範囲 」ドロップダウンメニューから選択して、すべての開いているワークブックで検索を実行します。次に、「 すべて検索 」をクリックして、一致するすべてのセルとその位置を即座に表示します。スクリーンショットをご覧ください:

ヒント: Kutools for Excelの高度な「検索と置換」ユーティリティを使用すると、すべての開いているワークブックだけでなく、選択したシート、アクティブなワークブック、現在のワークシート、または現在の選択範囲内でのデータ検索や置換が可能です。これにより、ニーズに応じて柔軟性のある制御が可能です。

注意事項: 検索したいすべてのワークブックがExcel内で開かれていることを確認してください。このツールでは、現在開いていないファイルを検索することはできません。

トラブルシューティング: 検索結果に一部のファイルが表示されない場合は、それらが完全にExcelに読み込まれており、検索アクセスを制限する保護ビューまたは読み取り専用モードになっていないか確認してください。

デモ: Kutools for Excelを使用して開いている複数のワークブックで値を検索する

Excelの数式を使用して複数のシートで値を検索する

ワークブック内に複数の既知のシート名があり、それらのシートの中で特定の値がどこにあるかを確認する必要がある場合、Excelの数式を使用して動的に検索を行うことができます。このアプローチは、検索結果を自動的に更新したい場合や、シートの一覧が比較的静的であるか、別のテーブルで管理されている場合に特に適しています。

この方法では、検索対象となるすべてのシート名を事前に把握しているかリスト化している必要があります。これは、自動化されたチェック、ダッシュボード、または毎回フルスキャンを実行せずに参照サマリを作成したい場合に最も効果的です。

利点: データが変更されると結果も自動的に更新されます。スクリプトやアドインは不要で、すべてワークブック内で処理されます。
欠点: シート名が頻繁に変わる場合や、非常に多くのシートがある場合には適していません。

例のシナリオ: Sheet1、Sheet2、Sheet3という3つのシートがあると仮定します。これらのシートのいずれかに、たとえば「Invoice123」という特定の値がセルA1に表示されているかどうか、またはその値がどこかに存在するかどうかを確認したい場合です。

ステップ1. D2:D4（D2: Sheet1, D3: Sheet2, D4: Sheet3）にシート名のリストがあるとします。E1に検索したい値（例: 「Invoice123」）を入力します。次に、F2に以下の数式を入力します:

=IF(COUNTIF(INDIRECT("'"&D2&"'!A:A"), $E$1) >0, "Found", "Not Found")

ステップ2. F2からF4まで数式をドラッグして、D2:D4にリストされているすべてのシートを確認します。これにより、各シートについて「見つかりました」または「見つかりませんでした」が表示されます。

動作の仕組み: この数式はINDIRECTを使用して各リストされたワークシートへの参照を作成し、COUNTIFを使ってE1の値が各シートの列Aに現れるかどうかを確認します。必要な場合は、範囲A:Aを他の列や特定の範囲（例: A1:Z100）に調整します。

追加のヒント: 値が含まれているシート名を取得するには、次の配列数式を使用します（古いExcelバージョンではCtrl + Shift + Enterで入力するか、Microsoft 365/Excel 2021+では通常の数式として入力します）:

=TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&D2:D4&"'!A:A"), $E$1)>0, D2:D4, ""))

これにより、値が見つかったすべてのシート名がカンマ区切りのリストで返されます。INDIRECTは開いているワークブックでのみ動作し、閉じたファイルでの検索はできないことに注意してください。

注意事項: シート名が変更または削除された場合、数式は#REF!エラーを返します。シート名リストが正しいことを常に確認してください。大規模なワークブックの場合、INDIRECTベースの数式はパフォーマンスを低下させる可能性があります。

トラブルシューティング: エラーが表示される場合は、参照されているすべてのシートが存在し、検索範囲が正しいことを確認してください。動的なシートリストについては、名前付き範囲やデータ検証を使用してシートリストを自動的に更新することを検討してください。

関連記事:

• 複数のワークシートや全体のワークブックで迅速に検索して置換するにはどうすればよいですか？
• 複数の開いているExcelファイルで迅速に検索して置換するにはどうすればよいですか？
• Excelで最大の負の値（0未満）を見つけるにはどうすればよいですか？
• Excelで列からすべての重複を抽出するにはどうすればよいですか？