VBA・マクロツール

【知らなくても使える】テーブル内のデータを項目毎に検索して表示するマクロツール(Excel VBA)

Excelのテーブル(表)内の各項目の値をそれぞれ指定して検索したいと思ったことはないでしょうか。

 

その際には、エクセルで管理したデータを検索するときにエクセル機能の「検索」を何度も実行して、対象のデータがある行を確認しては、再度検索、検索、検索…というように非常に手間がかかる作業をしている方も多くいらっしゃるのではないでしょうか。

 

そんな場面で無駄な作業が効率化できる、一度の実行で対象のデータがある行が検索できる『テーブル内のデータを項目毎に検索して表示するマクロツール』をExcel VBAで作りました。

なお、当ツールは検索対象は表でもテーブルでも動作します。

 

当記事にはマクロの元になるExcelファイルがダウンロード可能で、VBAのコードも記事内に記載がありますので、Excel VBAやマクロが良く分からないという方でもすぐに使用できます

当ツールを使用すれば定例業務の業務効率化が図れるかと思いますので、是非ご活用頂けたらと思います。

 

また、このツールのように各項目毎に検索条件を設定して検索するのでなく、複数項目に対して一つの検索条件で検索したい場合は、以下のツールを使用してみてください。

【知らなくても使える】表内のデータを簡単に検索するマクロツール(Excel VBA) 日常業務を行うにあたって、過去の蓄積されたデータをエクセルの表やテーブルで管理し、必要に応じて都度検索することがあると思います。...

 

 

『テーブル内のデータを項目毎に検索して表示するマクロ』の概要

エクセルの表を作成し、その中に日々業務で使用したデータを入れ込んでいきます。

 

そのデータが蓄積された際に、各項目を対象とした検索ワードを入れて、検索を実行すれば対象セルのみが赤く表示され、検索対象が一度で容易に確認できるようになります。

 

基本的な機能、操作方法の説明

  1. 実行するマクロが入ったExcelファイル(当ツールのマクロ付き)を開きます。
  2. 対象の表内に検索したいデータを入れた状態にします。(※この表を対象にして検索をします)

 

  1. 各項目を対象にした検索ワード(7行目のセル)にそれぞれ検索したい文字を入力します。
  2. 「検索」アイコンをクリックします。

 

入力した検索ワードに一致する値があるセルが赤色で表示され、対象の行のみ表示されます。

 

オプションとして、検索する際の条件を指定でき、検索結果の違いは以下になります。

 

各項目の検索

複数項目を入力した際の検索条件を下記の二つから指定できます。

(※複数の項目に値が入力された場合にAND条件またはOR条件で表示されます)

  • AND条件:入力した各項目の検索ワードが全て当てはまる行を検索して表示します。

 

  • OR条件:入力した各項目の検索ワードが一つでも当てはまる行を検索して表示します。

 

セル内の検索条件

一つの項目内の検索条件を以下の二つから指定します。

  • 完全一致:それぞれの項目に入力した値が全て一致する行を検索して表示します。

 

  • 部分一致:それぞれの項目に入力した値の一部が一致する行を検索して表示します。

 

留意事項

検索ワード内のワイルドカード等は使用できません

検索ワード内に空白やワイルドカードの対応していませんので、その場合は、オプションにある「各項目の検索」を『部分一致』にして検索してください

 

表の項目の列数を変更する場合

現行のツールでは表の列が「J列」までで作成されていますが、項目数を増減する場合は、以下の対応をしてください。

 

項目を増やす場合(例:K、L列を増やす)

  1. 作業列である「K列」を再表示してください。
  2. 検索ワード(7行目)を増やす列にコピーで作成してください。
  3. 表の項目とデータ(10行目、11行目以降)に値を入力して下さい。

 

項目を減らす場合(例:H、I列を減らす)

  1. 使用しない検索ワード(7行目)に斜線を引いてください。(※実施しなくても動作しますが、混乱を防ぐため行います。)
  2. 表の対象部分を選択して、削除してください。(右クリック→「削除」→「左方向へシフト」を選択して下さい。)

 

使用する際の事前準備

このページの下部にある「Excelファイルのツール」からExcelファイルを取得し、「準備の手順」内にあるVBAコードを取得したExcelファイルに記載して保存すれば、すぐに当ツールを使用できます。

 

以下にその手順を説明していきます。

 

準備の手順

当ツールのダウンロードはこちら」からサンプル(Excelファイル)をダウンロードします。

※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、設定画面(設定シート)が入っているマクロ無しExcelファイルを公開しています

 

<手順1>

ダウンロードしたExcelファイルを開いて、VBE(Visual Basic Editor)を起動させます。

  1. Excelファイルの上部にあるリボンの「開発」タブを選択します。
  2. 「Visual Basic」をクリックして、VBE(Visual Basic Editor)を起動させます。

リボンに「開発」タブが表示されていない場合は、「Excel VBAを始める前に、最初にやっておくべき初期設定内容の『開発』タブを表示させる」を参照ください。

 

<手順2>

  1. 「Microsoft Excel Object」を右クリックします。
  2. 「挿入」を選択します。
  3. 「標準モジュール」をクリックします。

 

<手順3>

  1. 「Module1」をダブルクリックします。
  2. 表示されている右側の欄(エディター)に以下のVBAコードを記載します。

 

VBAのソースコードはこちら

以下のVBAコードをコピーして、「Module1」内のエディターに貼り付けます。

※VBAコードの右上のアイコンをクリックするとソースコードをコピーできます

Option Explicit

'---------(設定値)---------------------------
Private Const KEYWORD_ROW = 7                   '1.検索ワードを入力する行番号
Private Const HEAD_ROW = 10                     '2.テーブル見出し行の行番号
Private Const HEAD_COL_NUM = 1                  '3.テーブル見出し行の列番号
Private Const SEARCH_TYPE_POINT = "B3"          '4.検索条件を指定するセル(AND検索、OR検索)
Private Const SEARCH_TYPE2_POINT = "C3"         '5.検索条件を指定するセル(完全一致、部分一致)
Private Const SETTING_SHEET_NM = "setting"      '6.設定シートの名称
Private Const WORK_COL_NM = "作業列"            '7.設定シートの名称

'---------(メッセージ)-----------------------
Private Const Msg2 = "検索対象が見つかりませんでした。"
'----------------------------------------------

Dim SearchType1 As String
Dim SearchType2 As String
Dim HeadCellPoint As String
Dim DataCellPoint As String
Dim WorkColNum As Long              '作業列の列番号
Dim WorkCol As String               '作業列
Dim BaseSht As Worksheet            'ツールの本体シート
Dim SettingSht As Worksheet         '設定シート

'===============================================
'項目をまたいで検索ボタンを押した時に実行される検索メイン処理
'===============================================
Sub 検索_Click()

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
        
    Set BaseSht = ActiveSheet
    
    Dim lastRow, lastColumn, iRow, iCol
    
    Dim keyword As Variant
    Dim tgtWord(1) As String
    Dim searchResult() As String
    Dim searchResult2 As Integer
    
    Dim blnSearchExist As Boolean: blnSearchExist = False
    
    Dim keywordCnt As Long
    Dim findCnt As Long
    
    ''設定情報を取得する
    Call GetSetting
    
    ''色の変更、作業行の初期化等を行う
    Call ClearSheetColor
    
    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
        Range(HeadCellPoint).AutoFilter
    End If
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    lastColumn = Cells(HEAD_ROW, HEAD_COL_NUM).End(xlToRight).Column
    
    '検索対象の行がある分だけ繰り返す
    For iRow = HEAD_ROW + 1 To lastRow
        
        keywordCnt = 0
        findCnt = 0
        
        '検索対象の列がある分だけ繰り返す
        For iCol = 1 To lastColumn
            
            keyword = Worksheets(BaseSht.Name).Cells(KEYWORD_ROW, iCol).Value
            
            If keyword <> "" Then
            
                keywordCnt = keywordCnt + 1
                tgtWord(1) = Cells(iRow, iCol).Value
                
                '完全一致で検索する場合
                If InStr(SearchType2, "完全") <> 0 Then
                    searchResult2 = StrComp(tgtWord(1), keyword)
                    
                    If searchResult2 = 0 Then
                        Cells(iRow, iCol).Font.Color = RGB(255, 0, 0)
                        findCnt = findCnt + 1
                    End If
                    
                '部分一致で検索する場合
                Else
                    searchResult = Filter(tgtWord, keyword)
                    
                    If UBound(searchResult) <> -1 Then
                        Cells(iRow, iCol).Font.Color = RGB(255, 0, 0)
                        findCnt = findCnt + 1
                    End If
                End If
            End If
        Next
        
        'AND条件で検索する場合
        If InStr(SearchType1, "AND") <> 0 Then
            If keywordCnt = findCnt Then
                Range(WorkCol & iRow).Value = "1"
                blnSearchExist = True
            End If
            
        'OR条件で検索する場合
        Else
            If findCnt > 0 Then
                Range(WorkCol & iRow).Value = "1"
                blnSearchExist = True
            End If
        End If
    Next
    
    '検索対象が存在する場合はフィルターし、存在しない場合はメッセージを表示させる
    If blnSearchExist Then
        BaseSht.Range(HeadCellPoint & ":" & WorkCol & Format(HEAD_ROW)).AutoFilter Field:=WorkColNum, Criteria1:="<>"
    Else
        Call ClearSheetColor
        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 iRow
    Dim iCol
    
    iRow = Cells(Rows.Count, 1).End(xlUp).Row
    iCol = Cells(HEAD_ROW, HEAD_COL_NUM).End(xlToRight).Column
    
    BaseSht.Range(Range(DataCellPoint), Cells(iRow, iCol)).Font.ColorIndex = 1
    BaseSht.Range(WorkCol & HEAD_ROW + 1 & ":" & WorkCol & iRow).Clear
    
End Sub

'-------------------------------------
'設定情報を取得する
'-------------------------------------
Sub GetSetting()

    Set SettingSht = Worksheets(SETTING_SHEET_NM)
    Dim workColNm As String
    
    Dim c As Range
    Set c = Cells(HEAD_ROW, HEAD_COL_NUM).CurrentRegion
    
    '最終列の列番号と列を取得する
    WorkColNum = c.Columns(c.Columns.Count).EntireColumn.Column
    workColNm = BaseSht.Cells(HEAD_ROW, WorkColNum).Value
    
    HeadCellPoint = Split(Cells(1, HEAD_COL_NUM).Address, "$")(1) & HEAD_ROW
    DataCellPoint = Split(Cells(1, HEAD_COL_NUM).Address, "$")(1) & HEAD_ROW + 1
    
    '最終列の項目名が「作業列」でない場合は作業列を新たに作る
    If WORK_COL_NM <> workColNm Then
        WorkColNum = BaseSht.Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
        BaseSht.Cells(HEAD_ROW, WorkColNum).Value = WORK_COL_NM
    End If
    
    '検索対象が表でなくテーブルで記載されている場合の対応
    If BaseSht.ListObjects.Count <> 0 Then
        If BaseSht.Range(HeadCellPoint).ListObject.Range.Columns.Count <> WorkColNum Then
    
            '作業列がテーブル内に入っていない場合は、追加する
            With BaseSht.Range(HeadCellPoint).ListObject
                .Resize .Range.Resize(, .Range.Columns.Count + 1)
            End With
        End If
    End If

    WorkCol = Split(Cells(1, WorkColNum).Address, "$")(1)
    
    '作業列が非表示になっていない場合は、非表示にする
    If Columns(WorkColNum).Hidden <> True Then Columns(WorkColNum).Hidden = True
    
    '検索条件を取得する
    SearchType1 = SettingSht.Range(SEARCH_TYPE_POINT).Value
    
    '条件の対象を取得する
    SearchType2 = SettingSht.Range(SEARCH_TYPE2_POINT).Value
    
End Sub

 

<手順4>

マクロを含んだExcelとして、ファイルを保存します。

  1. 左上にある「保存」アイコンをクリックします。
  2. 「ファイルの種類」から「Excelマクロ有効ブック」を選択します。
  3. 「保存」をクリックします。

 

<手順5>

Excelファイル内にある「検索」ボタンと「フィルター解除」ボタンに対して、それぞれマクロを設定します。

  1. 「検索」アイコンを右クリックします。
  2. 「マクロの登録」をクリックします。
  3. 「検索_Click」を選択します。
  4. 「OK」をクリックします。

 

「フィルター解除」ボタンには「フィルター解除_Click」を指定して、「OK」を押します。

  1. 「フィルター解除」アイコンを右クリックします。
  2. 「マクロの登録」をクリックします。
  3. 「フィルター解除_Click」を選択します。
  4. 「OK」をクリックします。

各ボタンに対して正常にマクロが登録できた場合は、各アイコンの上にカーソルを当てて、指の形になっていることを確認します。

 

これで事前準備は完了です。

あとは「当ツールの使用方法」で記載された方法でツールを実行できます。

 

また、このようなExcelVBAを用いた業務効率化を行うときに、以下の書籍が実際のVBAコードを書く際に参考になると思いますので、良かったらご参照ください。

 

なお、当マクロの開発環境として、OSは “Windows10” 、Excelのバージョンは “Microsoft Office 365″ で行っており、当環境では動作確認ができていますが、他のすべての環境で正常に動作するかは確認できていません。

 

正常に動作しない場合は、コメントいただければ幸いです。
また、このような業務効率化できるツールを以下に一覧でまとめてありますので、ご興味のある方はご覧ください。

業務効率化ツールの一覧(まとめ)

操作ごとにまとめて一覧で表示してあります。

Excel VBAを活用した便利マクロツール(まとめ)"Microsoft Excel"を使って業務効率化する際に使用できる便利なマクロツールを一覧で紹介します。 ...

 

当ツールのダウンロードはこちら

下記よりExcelファイルをダウンロードして、記事の途中にありました VBAのソースコードをツール内に組み込んで使用してください。

【Excel VBA】テーブル内の各項目データ検索ツール

ツールのダウンロードはこちら

※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています

他に要望等ありましたら、可能な限り改修等を対応しますのでコメント頂ければと思います

 

<このツールが『結構使える!』と思ったら、下のグッドボタンを押していただけたら幸いです>

COMMENT

メールアドレスが公開されることはありません。 が付いている欄は必須項目です