VBAで自動化 VBA一覧 抽出・検索処理 条件一致・行列処理

【VBA】特定の文字を含む列を抽出する方法｜InStrで見出しを部分一致検索

ExcelのデータをVBAで処理していると、「見出しに特定の文字が含まれている列だけを抽出したい」という場面があります。

たとえば、「東京売上」「大阪売上」「福岡売上」のように複数の見出しが並んでいる表から、「売上」という文字を含む列だけを取り出したいケースです。

見出しが完全に同じであれば=による比較でも判定できます。しかし、「売上」という共通文字は含まれていても、見出し全体が異なる場合は完全一致では対象を正しく抽出できません。

このような部分一致による判定には、VBAのInStr関数を利用できます。

この記事では、見出しに特定の文字を含む列をInStrで判定し、該当した列のデータを別シートへ抽出する方法を、実務で変更・流用しやすいコードを意識して解説します。

✅ VBAで特定の文字を含む列を抽出する仕組み

部分一致による列抽出では、いきなりコピー処理を書くのではなく「何を検索対象にするのか」を明確にすることが重要です。実務では、セルのデータを検索したい場合と、列の見出しを検索したい場合では処理の意味が大きく異なります。ここが曖昧なままコードを作ると、「売上を含むセルが1つでもあれば列全体を抽出する」といった意図しない処理になることがあります。

今回対象にするのは、見出しに特定の文字を含む列です。また、複数の列が条件に一致する可能性があることも、完全一致による列抽出との大きな違いです。まずは、どのような順序で対象列を判定するのか確認しておきましょう。

・見出しを左から順番に調べて対象列を判定する

たとえば「元データ」シートに、次のような表があるとします。

A列B列C列D列E列
商品名東京売上大阪売上東京原価福岡売上
商品A1500001200008000090000
商品B18000014000095000110000
商品C13000016000070000125000

ここから、見出しに「売上」という文字を含む列を抽出するとします。

対象になるのは、

  • 東京売上
  • 大阪売上
  • 福岡売上

の3列です。

一方、「東京原価」には「売上」が含まれていないため対象外になります。

処理の基本的な流れは次のとおりです。

  1. 元データと抽出先のシートを取得する
  2. 見出しが入力されている最終列を取得する
  3. 1行目の見出しを左から順番に確認する
  4. InStrで検索文字が含まれているか判定する
  5. 条件に一致した列の必要範囲を取得する
  6. 抽出先へ順番に転記する

今回のポイントは、条件に一致する列が1つとは限らないことです。

「売上」を含む列が3列あれば3列とも対象になるため、一致した時点で検索を終了せず、最終列まで判定を続ける必要があります。

・完全一致と部分一致を使い分ける

たとえば次の判定では、

If sourceSheet.Cells(1, currentColumn).Value = "売上" Then

見出し全体が「売上」と一致した場合だけTrueになります。

そのため、「東京売上」は対象になりません。

一方、InStrを使用して、

If InStr(1, sourceSheet.Cells(1, currentColumn).Value, "売上") > 0 Then

とすれば、見出しの中に「売上」が存在するかを判定できます。

「東京売上」であれば「売上」が含まれているため、条件に一致します。

部分一致の方が柔軟に見えますが、常に部分一致を使えばよいわけではありません。

たとえば「売上」と完全に一致する列だけが必要なのに部分一致にすると、「売上予測」「売上前年差」「売上目標」といった別の列まで対象になる可能性があります。

そのため、

特定の見出しだけを対象にする → 完全一致

共通する文字を持つ複数の見出しを対象にする → 部分一致

というように、元データの構造に合わせて判定方法を選ぶことが重要です。

見出しに特定の文字を「含む」列ではなく、「売上」のように指定した見出しと完全に一致する列だけを転記したい場合は、「条件に一致する列だけを別シートへ抽出する方法」で、完全一致による列の判定とデータ転記を詳しく解説しています。

✅ VBAのInStrで列見出しを部分一致検索する方法

InStrは文字列の中から指定した文字列を検索できる関数ですが、戻り値の意味を理解せずに使用すると条件判定が分かりにくくなることがあります。特に今回の目的は、文字が何文字目にあるかを取得することではなく、「検索文字が含まれているか」を判定することです。そのため、検索位置そのものを後続処理で使用する必要はありません。

また、空白の見出しや想定していない表記が含まれるデータでは、検索条件を曖昧にすると必要以上の列が対象になる場合があります。実務ではInStrの機能を使うこと自体より、戻り値をどのように条件判定へ利用するかが重要です。ここでは、列抽出に必要な範囲に絞ってInStrの使い方を確認します。

・InStrの戻り値から文字が含まれているか判断する

今回使用する基本的な形は次のとおりです。

InStr(1, 検索対象, 検索文字)

たとえば、

InStr(1, "東京売上", "売上")

とすると、「東京売上」の中から「売上」が始まる位置を取得します。

検索文字が見つかった場合は1以上、見つからなかった場合は0が返ります。

したがって、列を抽出する条件としては、

If InStr(1, sourceSheet.Cells(1, currentColumn).Value, targetText) > 0 Then

と判定できます。

targetTextには、

Dim targetText As String

targetText = "売上"

のように検索する文字を格納しておきます。

検索文字を変数へ分けておけば、後から「売上」ではなく「原価」や「実績」を抽出したくなった場合でも変更箇所が明確です。

また、

If InStr(1, sourceSheet.Cells(1, currentColumn).Value, "売上") > 0 Then

と直接記述するより、「何を検索しているコードなのか」を把握しやすくなります。

InStrの引数や戻り値、文字が見つからない場合の動作など、関数そのものの仕組みを詳しく確認したい場合は、「InStr関数で特定の文字の最初の位置を検索する方法」も参考にしてください。

・大文字と小文字を区別するかも意識する

日本語の「売上」のような検索では意識する機会は少ないですが、英字を含む見出しを扱う場合は文字の比較方法にも注意が必要です。

InStrでは第4引数に比較方法を指定できます。

InStr(1, 検索対象, 検索文字, vbTextCompare)

vbTextCompareを指定すると、大文字・小文字を区別しない文字列比較ができます。

たとえば、

If InStr( _
    1, _
    CStr(sourceSheet.Cells(1, currentColumn).Value), _
    targetText, _
    vbTextCompare _
) > 0 Then

とすれば、英字を含む見出しでも大文字・小文字の違いを吸収しやすくなります。

ここでCStrを使用しているのは、見出しの値を文字列として扱う意図を明確にするためです。

実務では日本語だけの帳票とは限らず、「Sales」「SALES」「sales」のように表記が統一されていないデータを扱うこともあります。

検索対象となるファイルのルールが統一されているなら厳密な比較でも構いませんが、入力元が複数存在する場合は、表記揺れをどこまで許容するのかを先に決めておくと安全です。

✅ InStrで一致した複数列を別シートへ抽出するVBAコード

部分一致で複数列を抽出するときは、見つけた列を毎回同じ場所へコピーしないよう注意が必要です。たとえば3列が条件に一致しているのに、すべてA列へ転記すると、後から処理したデータによって前の結果が上書きされてしまいます。そのため、元データ側の検索位置だけでなく、抽出先で「次にどの列へ書き込むか」も管理する必要があります。

また、列全体を無条件にコピーすると、使用していない大量のセルまで処理対象になります。見出しと実データが存在する範囲を取得し、必要な部分だけを転記する方が処理目的も明確です。ここでは、複数の一致列を左から順番に並べて抽出する基本コードを確認します。

・一致した列を抽出先へ左から順番に転記する実務コード

次のコードでは、「元データ」シートの1行目を検索し、「売上」を含む列だけを「抽出結果」シートへ転記します。

Sub ExtractColumnsByPartialMatch()

    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim targetText As String
    Dim lastColumn As Long
    Dim lastRow As Long
    Dim currentColumn As Long
    Dim destinationColumn As Long

    '処理対象となるシートを明示する
    Set sourceSheet = ThisWorkbook.Worksheets("元データ")
    Set destinationSheet = ThisWorkbook.Worksheets("抽出結果")

    '見出しから検索する文字
    targetText = "売上"

    '1行目を基準に最終列を取得する
    lastColumn = sourceSheet.Cells(1, sourceSheet.Columns.Count) _
                            .End(xlToLeft).Column

    '抽出先はA列から開始する
    destinationColumn = 1

    '見出しを左から順番に確認する
    For currentColumn = 1 To lastColumn

        If InStr( _
            1, _
            CStr(sourceSheet.Cells(1, currentColumn).Value), _
            targetText, _
            vbTextCompare _
        ) > 0 Then

            '条件に一致した列の最終行を取得する
            lastRow = sourceSheet.Cells( _
                sourceSheet.Rows.Count, currentColumn _
            ).End(xlUp).Row

            '見出しを含め、データが存在する範囲だけを転記する
            sourceSheet.Range( _
                sourceSheet.Cells(1, currentColumn), _
                sourceSheet.Cells(lastRow, currentColumn) _
            ).Copy Destination:=destinationSheet.Cells(1, destinationColumn)

            '次に一致した列は右隣へ転記する
            destinationColumn = destinationColumn + 1

        End If

    Next currentColumn

End Sub

このコードでは、currentColumnとdestinationColumnを別々に管理しています。

currentColumnは元データで現在調べている列、destinationColumnは抽出結果を次に書き込む列です。

たとえば元データのB列・C列・E列が条件に一致した場合でも、抽出結果ではA列・B列・C列と左側から詰めて配置できます。

また、完全一致で1列だけを探す処理とは異なり、条件に一致した後もExit Forを使用していません。

「売上」を含む列が複数存在することを前提としているため、最後の見出しまで検索を続けることが今回のコード設計では重要です。

このように、「部分一致にするためInStrへ変更する」だけではなく、複数列が一致した場合に抽出結果をどう配置するかまで考えることで、実務で利用しやすい列抽出処理になります。

✅ InStrの部分一致で意図しない列まで抽出しないための注意点

InStrによる部分一致は柔軟に列を検索できる反面、検索文字を含んでいれば想定していない見出しまで条件に一致します。たとえば「売上」を検索すると、「売上」「東京売上」だけでなく「売上目標」や「売上前年差」なども対象になります。マクロ自体は正常に動作するため、こうした誤抽出はエラーとして発見できない点が厄介です。

特に列数の多い業務データでは、抽出結果だけを見ても間違いに気付かない可能性があります。そのため、部分一致を使う場合は「検索文字を含む列をすべて対象としてよいか」を事前に確認することが重要です。便利だからという理由だけで完全一致を部分一致へ変更するのではなく、元データの見出しルールに合わせて使い分けましょう。

・「売上」を含むすべての列が対象になることを理解する

前半で使用した、

If InStr( _
    1, _
    CStr(sourceSheet.Cells(1, currentColumn).Value), _
    targetText, _
    vbTextCompare _
) > 0 Then

という条件は、targetTextが見出しのどこかに存在すれば条件成立となります。

たとえば、

  • 東京売上
  • 大阪売上
  • 売上目標
  • 売上予測
  • 前年売上

は、すべて「売上」を含んでいます。

したがって、targetText = "売上"ならすべて抽出対象です。

もし「東京売上」「大阪売上」「福岡売上」だけを対象にしたいのであれば、「売上」という文字だけでは条件が広すぎる可能性があります。

このような場合、検索文字を変更するだけでは対応できず、データ側の命名ルールや別の条件も考慮する必要があります。

ただし、AND・ORなど複数条件による列抽出は、1つの文字を部分一致で検索する今回の処理とは目的が異なります。

まずは、InStrは「検索文字を含んでいるか」を判定しているのであって、見出しの意味まで判断しているわけではないことを押さえておきましょう。

・空白の見出しをそのまま検索対象にしない

実務データでは、1行目のすべてのセルに必ず見出しが設定されているとは限りません。

空白列が途中に存在する場合は、見出しが入力されているか確認してからInStrを実行する方法もあります。

If Len(Trim$(CStr(sourceSheet.Cells(1, currentColumn).Value))) > 0 Then

    If InStr( _
        1, _
        CStr(sourceSheet.Cells(1, currentColumn).Value), _
        targetText, _
        vbTextCompare _
    ) > 0 Then

        '条件に一致した列を抽出する

    End If

End If

Trim$で前後の半角スペースを除き、Lenで文字数を確認しています。

文字数が0より大きい場合だけ検索するため、見出しが空白の列を検索対象から除外できます。

なお、検索文字targetText自体が空文字になっていないかも確認しておくと、より安全です。

利用者が検索文字をセルから指定するようなマクロへ発展させる場合は、検索開始前に入力値を確認する設計も検討しましょう。

 

✅ VBAで部分一致する列が見つからない場合も判定する

部分一致検索では「何件見つかったか」を把握できるようにしておくと、実務での確認がしやすくなります。特に見出し名が変更された場合、InStrはエラーを発生させず、単純に条件不一致となります。そのまま処理を終了すると、利用者から見れば「該当する列がなかった」のか「マクロが正しく動かなかった」のか判断できません。

また、今回の処理では複数列が一致する可能性があるため、Boolean型で有無だけを記録するより、一致件数を数える方法も利用できます。抽出結果を確認する仕組みまで用意しておけば、元データの変更にも気付きやすくなります。検索・抽出処理では、正常に見つかった場合だけでなく0件だった場合も設計しておくことが大切です。

・一致した列数をカウントして0件を検出する

一致件数を管理するため、Long型の変数を用意します。

Dim matchedColumnCount As Long

matchedColumnCount = 0

InStrによる条件が成立したときに、

matchedColumnCount = matchedColumnCount + 1

を実行します。

そして、すべての見出しを確認した後に、

If matchedColumnCount = 0 Then
    MsgBox "「" & targetText & "」を含む見出しが見つかりませんでした。", _
           vbExclamation
End If

とすれば、該当列が1つも存在しなかったことを利用者へ伝えられます。

Boolean型でも「見つかった・見つからなかった」の判定はできますが、カウンターにしておけば、

MsgBox matchedColumnCount & "列を抽出しました。", vbInformation

のように抽出件数を確認する用途にも使えます。

複数の対象を検索する処理では、一致したかどうかだけでなく、一致件数を管理する方法も実務では便利です。

✅ VBAで部分一致した列の値だけを別シートへ抽出する方法

前半ではCopyメソッドを使って対象列を転記しましたが、抽出後のデータを集計や別処理へ利用するだけなら、書式までコピーする必要がない場合があります。数式や書式をそのまま引き継ぐのか、計算結果を含む値だけを残すのかによって適した転記方法は変わります。

特に大量のデータを繰り返し扱う処理では、クリップボードを利用せず値を直接代入する方法も選択肢になります。ただし、「値だけ転記する方が常に優れている」ということではありません。帳票として見た目も引き継ぎたい場合はCopyが適しています。転記後のデータを何に使うのかを考えて方法を選びましょう。

・RangeのValueを使って値だけを転記する

条件に一致した列の値だけを転記する場合は、元データと転記先で同じ大きさの範囲を用意して値を代入できます。

Dim sourceRange As Range
Dim destinationRange As Range

Set sourceRange = sourceSheet.Range( _
    sourceSheet.Cells(1, currentColumn), _
    sourceSheet.Cells(lastRow, currentColumn) _
)

Set destinationRange = destinationSheet.Cells(1, destinationColumn) _
    .Resize(sourceRange.Rows.Count, 1)

destinationRange.Value = sourceRange.Value

この方法なら、コピー＆ペーストを介さずに値をまとめて転記できます。

たとえば元データに数式が含まれている場合、.Valueによる代入では基本的に数式そのものではなく、その時点の計算結果が転記されます。

一方、

sourceRange.Copy Destination:=destinationSheet.Cells(1, destinationColumn)

なら、数式や書式なども含めてコピーできます。

したがって、使い分けの目安は、

  • 抽出後も数式や書式を利用したい → Copy
  • 集計や加工用として値だけ必要 → .Value
  • 抽出結果を固定データとして残したい → .Value

となります。

実務では、処理速度だけを理由に決めるのではなく、転記後のデータをどのように利用するのかを基準に選ぶことが重要です。

抽出結果を毎回作り直すのではなく、転記先にある既存データを残したまま右端へ追加していきたい場合は、「条件に一致する列を別シートの最終列へ追加する方法」で、最終列を取得して追記する方法を解説しています。

✅ 特定文字を含む列の抽出を実務で使いやすくするポイント

部分一致による列抽出は、月次データや部署別帳票など、似た名前の項目をまとめて取り出す処理に応用できます。しかし、検索条件をコードの複数箇所へ直接書いてしまうと、後から条件を変更するときに修正漏れが起きやすくなります。

また、抽出条件が増えるたびに1つのマクロへ処理を追加すると、何を目的としたコードなのか分かりにくくなることもあります。今回の処理では「1つの検索文字を含む複数列を抽出する」という役割を明確にしておくことが大切です。完全一致や複数条件による抽出とは役割を分けておけば、後から仕様を確認するときにも判断しやすくなります。最後に、今回のコードを実務へ応用するときの考え方を整理します。

・検索文字と処理対象を分かりやすく管理する

今回のコードでは、

Set sourceSheet = ThisWorkbook.Worksheets("元データ")
Set destinationSheet = ThisWorkbook.Worksheets("抽出結果")

targetText = "売上"

のように、処理対象と検索文字を分けています。

この構成なら、「売上」ではなく「原価」を含む列へ変更する場合も、

targetText = "原価"

と変更するだけで判定条件を切り替えられます。

検索条件をセルから取得する仕組みに変更すれば、コードを編集せずに検索文字を変更することも可能です。

ただし、条件を自由入力できるようにすると、空文字や想定外の文字が指定される可能性も増えます。

利便性だけでなく、誰がどのようにマクロを使用するのかまで考えて条件の指定方法を決めることが大切です。

・完全一致・部分一致・複数条件を目的によって分ける

列抽出では、条件によって適した判定方法が異なります。

見出しが「売上」と完全に一致する列だけを取り出すのであれば、=による完全一致で十分です。

「東京売上」「大阪売上」のように共通する文字を含む複数列をまとめて取り出すなら、今回のInStrによる部分一致が適しています。

さらに、「売上を含み、かつ特定の条件も満たす」といった判定が必要になれば、複数条件を組み合わせる処理が必要になります。

すべてを1つの条件式へ詰め込むのではなく、

完全一致 → 部分一致 → 複数条件

と必要に応じて処理を発展させると、コードの目的を保ちやすくなります。

今回の基本となるのは、「見出しを検索する→InStrで部分一致を判定する→一致した列を取得する→抽出先へ順番に転記する」という流れです。

この流れを理解しておけば、検索する文字や転記方法が変わった場合にも応用しやすくなります。

1つの検索文字だけではなく、「売上を含み、さらに別の条件も満たす」「売上または原価を含む」といった条件で列を絞り込みたい場合は、「複数条件に一致する列を抽出する方法」で、AND・OR条件を使った判定方法を詳しく解説しています。

 

✅ まとめ：VBAのInStrで特定の文字を含む列を抽出しよう

VBAのInStrを使えば、見出しが完全に一致していなくても、特定の文字を含む列を部分一致で判定して抽出できます。

今回のポイントをまとめると、次のとおりです。

  • InStrを使うと見出しを部分一致で検索できる
  • 戻り値が0より大きければ検索文字が含まれている
  • 条件に一致する列が複数あっても最終列まで検索を続ける
  • 元データ側と抽出先側の列番号を別々に管理する
  • 抽出先では一致した列を左から順番に並べられる
  • 部分一致では意図しない見出しまで対象になる可能性がある
  • 一致件数を管理すると0件だった場合も判定できる
  • 書式や数式も必要ならCopy、値だけなら.Valueによる転記を使い分ける
  • 完全一致・部分一致・複数条件は目的に応じて使い分ける

「売上」のような共通文字を持つ列が多数あるデータでは、列位置を固定して個別に処理するより、見出しを検索して対象列を自動判定する方がレイアウト変更にも対応しやすくなります。

一方で、部分一致は検索範囲が広くなるため、どの見出しまで対象にするのかを事前に整理しておくことも重要です。

まずは1つの検索文字による部分一致を基本として、必要になった段階で完全一致や複数条件による判定へ使い分けていきましょう。

    -VBAで自動化, VBA一覧, 抽出・検索処理, 条件一致・行列処理