文字の検索　味はソコソコ？Excel Tips（しお味）

文字の検索

様々なデータの中で、特定の文字を検索する操作は頻繁に使われる重要な処理の一つです。 Excel関数であればvlookup関数が有名ですね。vbaでの検索の方法も色々ありますが、ここではこれも有名な「Find」メソッドを用いての文字の検索の例を説明します。

●Range.Find メソッド (Excel)
microsoftの基本情報

https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.find

構文
expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

※expression は Range オブジェクトを表す変数です。

各パラメータの説明

名前	必須/オプション	データ型	説明
What	必須	バリアント	検索するデータです。指定できるのは、文字列、または任意の Microsoft Excel のデータ型です。
After	省略可	バリアント	このセルの後から検索を開始します。これは、ユーザーインターフェイスから検索が実行されたときにアクティブなセルの場所に対応しています。 After は範囲内の 1 つのセルにする必要があることに注意してください。このセルの後から検索が開始されるため、メソッドによって範囲内の他のセルがすべて検索され、このセルに戻るまで、指定されたセルは検索されません。この引数を指定しない場合は、範囲の左上端のセルの後から検索が開始されます。
LookIn	省略可	バリアント	使用できるのは、XlFindLookIn 定数: xlFormulas、xlValues、xlComments あるいは xlCommentsThreaded のいずれかです。
LookAt	省略可	バリアント	次の XlLookAt 定数:xlWhole あるいは xlPart のいずれかになります。
SearchOrder	省略可	バリアント	次の XlSearchOrder 定数:xlByRows あるいは xlByColumns のいずれかになります。
SearchDirection	省略可	バリアント	次の XlSearchDirection 定数: xlNext あるいは xlPrevious のいずれかになります。
MatchCase	省略可	バリアント	大文字と小文字を区別するには、True を指定します。既定値は False です。
MatchByte	省略可	バリアント	2 バイトの言語サポートが選択またはインストールされている場合にのみ使用されます。 2 バイト文字が 2 バイト文字とだけ一致するようにする場合は、True を指定します。 2 バイト文字が同等の 1 バイト文字とも一致するようにする場合は、False を指定します。
SearchFormat	省略可	バリアント	検索の書式を指定します。

●具体的な記述例

■■■有無の検索：最初に見つかった文字の情報■■■
この検索方法は、検索文字が、対象エリアにあるかどうかだけを調べます。

Worksheets("DATA")：検索対象のワークシート
sechr:検索する文字列（文字列）

[検索対象]
列単位の検索：Worksheets("DATA").Columns(***)
範囲の検索：Worksheets("DATA").Range(***)
ワークシート全体の検索：Worksheets("DATA").cells

resrng：検索結果（レンジ）

完全一致の場合
resrng＝検索対象.Find(sechr, LookAt:=xlWhole)
部分一致の場合
resrng＝検索対象.Find(sechr, LookAt:=xlPart)

結果の取り出し例
見つかった文字のある行数：resrng.row
見つかった文字のある列数：resrng.Column

■■■抽出検索：検索条件にあったデータを全て抽出します■■■
この検索方法は、検索条件にあった対象エリアにあるすべてのデータを抽出します。

検索を繰り返すには、FindNext および FindPrevious メソッドを使用します。
検索が指定された検索範囲の最後に達すると、範囲の最初に戻ります。
最初に戻ったときに検索を停止するには、最初に見つかったセルの位置を保存しておき、
セルが見つかるたびに、その位置と保存しておいた位置を比較します。

今回は、findメソッドではなく、順次対象データを検索するFindNextメソッドを使います。
パラメータ関係はFindメソッドと同じです。

Worksheets("DATA")：検索対象のワークシート
sechr:検索する文字列（文字列）

[検索対象：serange]
列単位の検索：Worksheets("DATA").Columns(***)
範囲の検索：Worksheets("DATA").Range(***)
ワークシート全体の検索：Worksheets("DATA").cells

ループ処理で全てのデータを抽出
***最初に見つかった行に戻るまでループして検索***

1）まず。最初に見つかった行を取得する
res1st = serange.Find(sechr)
res1st：最初に見つかった行

2）最初に見つかった行に戻るまでループして検索

Do ========================================

Set res1st = serange.FindNext(res1st)-----> (Findnext検索)

k = k + 1 -----> (件数カウンター)

Loop While res1st.Row <> resrng.Row ==================

全てのデータを取得する場合、変数に収容するのが一般的ですが下記に示すように、セルの色を変えたり、対象データだけを別のシートにコピーしたりします。

有無の検索：最初に見つかった文字の情報

設定画面	検索結果

有無の検索：最初に見つかった文字の情報
このサンプルは、列単位での有無検索を行います。
検索モードとしては
・完全一致
・部分一致
が選択出来ます

検索対象範囲：serange
検索対象列：seclm
Set serange = Worksheets("DATA").Columns(seclm)

範囲での検索の場合
Set serange = Worksheets("DATA").range
シート単位での検索の場合
Set serange = Worksheets("DATA").cells

有無の検索：最初に見つかった文字の情報

  Sub serchip()

  'findメソッドを用いた検索（列単位検索）///////////////////////////////////////////////////

  Dim serange As Range      '検索範囲
  Dim sechr As String       '検索文字
  Dim seclm As Integer      '検索列
  Dim resrng As Range       '検索結果の収容


  '検索する文字の取得***************************
  sechr = Worksheets("sheet1").Cells(3, 5)

  '検索する列数の取得***************************
  seclm = Worksheets("sheet1").Cells(5, 5)

  '検索条件設定（検索範囲）*********************

            Set serange = Worksheets("DATA").Columns(seclm)

            Worksheets("DATA").Cells.ClearFormats    '着色列のクリア
            Worksheets("DATA").Columns(seclm).Interior.Color = RGB(225, 255, 255)


  '検索条件設定（検索モード）*********************

  If Worksheets("sheet1").OptionButton1.Value = True Then

              Set resrng = serange.Find(sechr, LookAt:=xlWhole)    '完全一致

         Else

              Set resrng = serange.Find(sechr, LookAt:=xlPart)    '部分一致

  End If


  '見つからない場合の処理**********************
      If resrng Is Nothing Then

            MsgBox "'" & sechr & "'はありませんでした"

            Exit Sub

          Else

      End If

  '検索文字が見つかった最初の行***************
  Worksheets("sheet1").Cells(8, 5) = resrng.Row


  'データの表示*******************************
  Worksheets("DATA").Select
  Worksheets("DATA").Cells(resrng.Row, resrng.Column).Interior.Color = RGB(225, 155, 225)


  End Sub

文字の検索_有無検索（列単位検索）(ダウンロード)

文字の検索（全数検索,検索対象：全文書）

設定画面	検索結果1	検索結果2

このサンプルは、全文書（シート単位）の全数検索を行います。
検索モードとしては
・完全一致
・部分一致
が選択出来ます

検索の範囲
シート単位での検索
Set serange = Worksheets("DATA").cells

文字の検索（全数検索,検索対象：全文書）

  Sub serchip2()

  'findnextメソッドを用いた全数検索///////////////////////////////////////////////////

  Dim serange As Range      '検索範囲
  Dim sechr As String       '検索文字
  Dim seclm As Integer      '検索列
  Dim resrng As Range       '検索結果の収容
  Dim res1st As Range       '最初に検索された行数
  Dim sendn As Integer      'RESULTシートの行数


  '検索する文字の取得***************************
  sechr = Worksheets("sheet1").Cells(3, 5)

  If sechr <> "" Then  '文字未入力処理

     Else

     Exit Sub

  End If


  '検索する列数の取得***************************
  'seclm = Worksheets("sheet1").Cells(14, 5)

  '検索条件設定（検索範囲）*********************

  'If Worksheets("sheet1").OptionButton3.Value = True Then

           ' Set serange = Worksheets("KEN_ALL").Columns(seclm)

           ' Worksheets("KEN_ALL").Cells.ClearFormats    '着色列のクリア
           ' Worksheets("sheet1").Range(Cells(14, 5), Cells(14, 6)).Interior.Color = RGB(255, 255, 255)
           ' Worksheets("KEN_ALL").Columns(seclm).Interior.Color = RGB(225, 255, 255)


        ' Else

            Set serange = Worksheets("DATA").Cells

  'End If

  '検索条件設定（検索モード）*********************

  If Worksheets("sheet1").OptionButton1.Value = True Then

              Set resrng = serange.Find(sechr, LookAt:=xlWhole)    '完全一致

         Else

              Set resrng = serange.Find(sechr, LookAt:=xlPart)     '部分一致

  End If


  '見つからない場合の処理**********************
      If resrng Is Nothing Then

            MsgBox "'" & sechr & "'はありませんでした"

            Exit Sub

          Else

      End If

  '検索文字が見つかった最初の行***************
  'Worksheets("sheet1").Cells(8, 4) = resrng.Row

  Set res1st = resrng      '最初の行を収容

  Worksheets("SRESULT").Cells.Clear        'RESULTシート初期化
  Worksheets("DATA").Cells.ClearFormats    'KEN_ALLシート初期化
  Worksheets("sheet1").Cells(8, 5) = ""    '件数初期化


  '最初に見つかった行に戻るまでループして検索*************

   Do   '/////////////////////////////////////////////////////////////////////////////

        Worksheets("DATA").Rows(res1st.Row).Interior.Color = RGB(255, 255, 0)                   '行の着色
        Worksheets("DATA").Cells(res1st.Row, res1st.Column).Interior.Color = RGB(225, 155, 225) 'セルの着色

        'sendn = Worksheets("SRESULT").Cells(Rows.Count, 1).End(xlUp).Row + 1                   'RESULTシートの現在の行数

        Worksheets("DATA").Rows(res1st.Row).Copy Worksheets("SRESULT").Cells(k + 1, 1)          '順次見つかった行をRESULTシートにコピー

        Set res1st = serange.FindNext(res1st)          'Findnext検索

          k = k + 1                                      '件数カウンター

   Loop While res1st.Row <> resrng.Row    '////////////////////////// 最初の行までLOOPする


  Worksheets("sheet1").Cells(8, 5) = k                    '見つかった件数


  'データの表示*******************************
  Worksheets("DATA").Select
  'Worksheets("KEN_ALL").Cells(resrng.Row, 1).Interior.Color = RGB(255, 255, 0)
  'Worksheets("KEN_ALL").Rows(resrng.Row).Interior.Color = RGB(255, 255, 0)
  ActiveWindow.ScrollRow = resrng.Row

  'シートの移動*******************************
  Worksheets("SRESULT").Select
  ActiveWindow.ScrollRow = 1

  End Sub

文字の検索_全数検索（シート単位）(ダウンロード)

文字の検索（まとめ）

設定画面	検索結果1	検索結果2

文字の検索（まとめ）サンプルプログラムです。
少し実用的になりました。
検索モード
有無検索（部分一致、完全一致）
全数検索（部分一致、完全一致）
検索対象
検索対象（列単位、シート全体）

以上の文字検索のサンプルプログラムです。
対象データとしては、郵政省の郵便番号関係のデータを用いています。（124508件）
住所・地名のデータベース的にも使えます。
https://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
※読み仮名データの促音・拗音を小書きで表記するもの(zip形式)

文字の検索（まとめ）（ダウンロード）