Excelでgrepのような検索を行う方法

①「開発」タブ、②「Visual Basic」の順に選択します。

「Microsoft Visual Basic for Applications」が起動します。

①「挿入」タブ、②「標準モジュール」の順に選択します。

①モジュールに、以下のコードを貼り付けます。

Sub searchAllBooksForAnyString()

    ' 検索する文字列を指定
    Dim searchTerms As Variant
    searchTerms = Array("バナナ", "チョコ")
    
    ' 参照フォルダを選択する
    Dim folderPath As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = True Then folderPath = .SelectedItems(1)
    End With
    If Len(folderPath) = 0 Then Exit Sub
    
    ' フォルダパスに区切り文字を追加
    folderPath = folderPath & Application.PathSeparator
    
    ' 検索対象ファイルの初期化
    Dim currentFileName As String
    currentFileName = Dir(folderPath & "*.xls?")
    
    ' 画面更新とイベントを無効化
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    ' 検索結果を記載する新しいワークシートを準備
    Dim resultSheet As Worksheet
    Set resultSheet = Workbooks.Add.Worksheets(1)
    SetupResultSheet resultSheet
    
    ' ブック内から文字列を検索し、結果をまとめる
    Dim targetWorkbook As Workbook
    Dim foundCount As Long
    foundCount = 0
    
    On Error Resume Next
    Do While currentFileName <> ""
        ' フォルダ内のブックを開く
        Set targetWorkbook = Workbooks.Open(folderPath & currentFileName)
        
        ' 各ワークシート内で文字列を検索
        foundCount = SearchInWorkbook(targetWorkbook, searchTerms, resultSheet, foundCount)
        
        ' 開いたファイルは保存しないまま閉じる
        targetWorkbook.Close SaveChanges:=False
        
        ' 次のファイルに移動
        currentFileName = Dir()
    Loop
    On Error GoTo 0
    
    ' 検索結果が見つからなかった場合の処理
    If foundCount = 0 Then
        resultSheet.Parent.Close SaveChanges:=False
        MsgBox "検索文字列を含むファイルは見つかりませんでした", vbInformation
    Else
        FinalizeResultSheet resultSheet, folderPath
    End If
    
    ' 画面更新とイベントを再有効化
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Sub SetupResultSheet(resultSheet As Worksheet)
    ' 結果シートの初期設定
    resultSheet.Range("A4:C4").Value = Array("検索値", "ブック名", "シート名")
    resultSheet.Range("A1:D1,A4:D4").Interior.Color = RGB(226, 239, 218)
End Sub

Function SearchInWorkbook(targetWorkbook As Workbook, searchTerms As Variant, resultSheet As Worksheet, foundCount As Long) As Long
    Dim targetSheet As Worksheet
    Dim searchTerm As Variant
    Dim targetRange As Range
    
    ' 各ワークシート内で検索を実行
    For Each targetSheet In targetWorkbook.Worksheets
        For Each searchTerm In searchTerms
            Set targetRange = findTargetCell(targetSheet.Cells, searchTerm)
            
            ' 検索文字列を含むセルが存在する場合、データをまとめる
            If Not targetRange Is Nothing Then
                With resultSheet.Cells(5 + foundCount, "A").Resize(1, 4)
                    .Value = Array(searchTerm, targetWorkbook.Name, targetSheet.Name, targetRange.Address(0, 0))
                    foundCount = foundCount + 1
                End With
            End If
        Next searchTerm
    Next targetSheet
    
    SearchInWorkbook = foundCount
End Function

Sub FinalizeResultSheet(resultSheet As Worksheet, folderPath As String)
    ' データをまとめる際のレイアウト調整
    resultSheet.Columns(4).TextToColumns Destination:=Range("D1"), Comma:=True
    resultSheet.UsedRange.EntireColumn.AutoFit
    resultSheet.Range("A1").Value = "フォルダ"
    resultSheet.Range("A2").Value = Left$(folderPath, Len(folderPath) - 1)
    resultSheet.Range("D4").Value = "セル"
End Sub

Function findTargetCell(ByRef searchRange As Range, _
                        ByVal searchTerm As String, _
                        Optional ByVal LookIn As XlFindLookIn = xlValues, _
                        Optional ByVal LookAt As XlLookAt = xlPart, _
                        Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
                        Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
                        Optional ByVal MatchCase As Boolean = False, _
                        Optional ByVal MatchByte As Boolean = False) As Range

    Dim foundRange As Range
    Set foundRange = searchRange.Find(searchTerm, , LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte)
    
    ' 検索に一致するセルがない場合は抜ける
    If foundRange Is Nothing Then Exit Function
    
    Dim initialAddress As String
    Dim unionRange As Range
    initialAddress = foundRange.Address
    Set unionRange = foundRange
    
    Do
        Set unionRange = Union(unionRange, foundRange)
        Set foundRange = searchRange.FindNext(foundRange)
        If foundRange Is Nothing Then Exit Do
    Loop Until initialAddress = foundRange.Address
    
    Set findTargetCell = unionRange
End Function

このとき、「searchTerms = Array("バナナ", "チョコ")」に、検索したい文字列を入力してください。今回の例だと「バナナ」と「チョコ」が検索されます。

「佐藤」を検索したいなら「searchTerms = Array("佐藤")」のように変更してください。

なお、複数の文字列を検索したい場合は、「"バナナ", "チョコ"」のようにカンマ（,）で区切ってください。

②「Sub/ユーザーフォームの実行」を選択します。

「参照」ダイアログボックスが表示されます。

①検索したいファイルが格納されているフォルダ（例：管理表）を選択し、②「OK」ボタンを押します。

新しいブックが作成され、フォルダ内にあるファイルから特定の文字列（例：バナナ、チョコ）を検索できました。

ブック名、シート名、セルが表示され、どこに文字列があるのか分かりやすくなっています。