【VBA】大量データの文字列を一括置換する方法｜配列・For Eachで高速処理

Excel VBAで数百行、数千行のデータを処理していると、「複数の文字列をまとめて置換したい」という場面がよくあります。

少量のデータであればセルを一つずつ処理しても大きな問題はありませんが、データ件数が増えると実行時間が長くなり、マクロがなかなか終わらない原因になります。

また、単純な一括置換だけであればRange.Replaceメソッドが便利ですが、「セルごとに条件を確認したい」「複数の置換条件を順番に適用したい」といった処理では別の設計が必要です。

このような大量データの文字列置換では、For Eachや配列を目的に応じて使い分けることが重要です。

この記事では、大量データを安全かつ効率的に置換するための考え方と、後から変更しやすい実務向けのコードを解説します。

✅ VBAで大量データを文字列置換するときの基本的な考え方

大量データの置換では、「Replaceを使えばよい」と考えるだけでは十分ではありません。

Replace関数は文字列を加工するために便利ですが、Excelではセルへの読み書きそのものが処理速度へ大きく影響します。

数十セルでは気にならなくても、数千・数万セルを一つずつ読み書きすると処理時間が急に長くなることがあります。

さらに、置換対象の範囲を必要以上に広げれば、意図しないデータまで変更してしまう危険があります。

そのため、実務では「何を置換するか」だけでなく、「どの範囲を、どの方法で処理するか」まで考えて設計する必要があります。

まずは、大量データ処理で押さえておきたい基本から確認しましょう。

・大量データではセルへのアクセス回数が重要

例えば、A2:A10000に商品名が入力されているとします。

それぞれのセルをVBAから取得し、置換して、再びセルへ書き戻す場合、Excelとのやり取りが何度も発生します。

処理の流れは次のようになります。

セルから値を取得する
文字列を置換する
結果をセルへ書き戻す
次のセルへ移動する
最終行まで同じ処理を繰り返す

データ件数が多いほど、この読み書き回数も増えていきます。

そのため、大量データでは単純にコードを短くするだけでなく、セルへアクセスする回数を減らすことが高速化の重要なポイントになります。

大量データでは、セルを1つずつ処理するよりも、範囲をまとめて配列へ読み込む方が高速になるケースがあります。配列への読み込み方法について詳しく知りたい方は、「範囲指定のセルの値を配列に格納する方法」も参考にしてください。

・Range.Replaceだけでは対応しにくい処理もある

単純に、

「旧商品名」を「新商品名」へ変更する

といった処理であれば、Range.Replaceメソッドを利用する方法が効率的です。

しかし実務では、

空白セルは処理しない
特定の列だけ条件を変える
複数の文字列を順番に置換する
置換前の値を確認してから変更する
加工した結果を別の列へ出力する

といった要件が加わることがあります。

このような場合は、For Eachや配列を利用して、セルの値を取得してからReplace関数で加工する方が柔軟に対応できます。

つまり、「一括置換だから常にRange.Replace」というわけではなく、処理内容によって方法を選ぶことが大切です。

単純な文字列の一括置換であれば、セルを1つずつ処理せずにRange.Replaceメソッドでまとめて変更できます。セル範囲を直接一括置換する方法や、部分一致・完全一致の使い分けについては、「Range.Replaceメソッドの使い方」もあわせてご覧ください。

✅ For Eachで大量データの文字列を置換する方法

大量データの置換で最初に理解しておきたいのが、For Eachを利用したセル単位の処理です。

For Eachはセルを一つずつ取得できるため、条件判定を組み込みやすく、コードの流れも理解しやすい方法です。

一方で、データ件数が非常に多い場合は、セルへのアクセス回数が増えるため速度面では不利になることがあります。

そのため、「For Eachは遅いから使わない」と決めつけるのではなく、可読性や必要な条件処理とのバランスで判断することが重要です。

まずは安全で分かりやすい基本形を作り、その後に必要に応じて配列へ置き換える考え方がおすすめです。

・For Eachでセルの文字列を順番に置換する

次の例では、A2:A1000の「旧商品」を「新商品」へ置換します。

Sub ReplaceTextWithForEach()

    Dim targetWorksheet As Worksheet
    Dim targetRange As Range
    Dim targetCell As Range

    Set targetWorksheet = ThisWorkbook.Worksheets("商品一覧")
    Set targetRange = targetWorksheet.Range("A2:A1000")

    '対象範囲をセル単位で処理する
    For Each targetCell In targetRange.Cells

        '空白セルは処理しない
        If Len(targetCell.Value) > 0 Then
            targetCell.Value = Replace( _
                CStr(targetCell.Value), _
                "旧商品", _
                "新商品")
        End If

    Next targetCell

End Sub

この書き方では、処理対象となるワークシートとセル範囲を最初に変数へ格納しています。

Range("A2:A1000")をコード中に何度も直接記述するより、後から対象列や範囲を変更しやすくなります。

また、targetCellという変数名にすることで、「現在処理しているセル」であることも読み取りやすくしています。

・For Eachが向いているケース

For Eachは、次のような処理に向いています。

空白セルを除外したい
数値と文字列で処理を変えたい
セルごとに条件判定したい
一部のセルだけ置換したい
セルの書式なども同時に変更したい

単純な文字列置換だけならRange.Replaceの方が簡潔ですが、セルごとに判断が必要な場合はFor Eachの方が処理意図を表現しやすくなります。

データ件数が毎回変わる場合は、固定範囲ではなく最終行を取得して処理する設計がおすすめです。詳しくは、「For文で最終行まで繰り返す方法」もあわせてご覧ください。

✅ 配列を使って大量データの置換を高速化する

数千行以上の大量データでは、セルを一つずつ読み書きする処理がボトルネックになることがあります。

そこで有効なのが、対象範囲を一度配列へ読み込み、メモリ上で置換してからまとめてセルへ書き戻す方法です。

配列を使えば、Excelワークシートとのやり取りを「読み込み」と「書き戻し」の少ない回数に抑えられます。

ただし、コードはFor Eachより少し複雑になるため、少量データまで無理に配列化する必要はありません。

処理速度が問題になったときに選べる方法として理解しておくと、実務での選択肢が広がります。

・配列に読み込んでから置換する理由

例えばA2:A10000を処理するとき、セル単位の処理では何千回もワークシートへアクセスします。

配列を使う場合は、

A2:A10000をまとめて配列へ読み込む
配列内の文字列を置換する
処理後の配列をまとめてセルへ戻す

という流れになります。

ワークシートとVBAの間で発生するデータの受け渡しを減らせるため、大量データほど効果が出やすくなります。

・配列で大量データを一括置換するコード

Sub ReplaceTextWithArray()

    Dim targetWorksheet As Worksheet
    Dim targetRange As Range
    Dim dataValues As Variant
    Dim rowIndex As Long

    Set targetWorksheet = ThisWorkbook.Worksheets("商品一覧")
    Set targetRange = targetWorksheet.Range("A2:A10000")

    '対象範囲を一度に配列へ読み込む
    dataValues = targetRange.Value

    '配列上で文字列を置換する
    For rowIndex = LBound(dataValues, 1) To UBound(dataValues, 1)

        If Len(dataValues(rowIndex, 1)) > 0 Then
            dataValues(rowIndex, 1) = Replace( _
                CStr(dataValues(rowIndex, 1)), _
                "旧商品", _
                "新商品")
        End If

    Next rowIndex

    '処理結果を一度にワークシートへ書き戻す
    targetRange.Value = dataValues

End Sub

このコードでは、セルを直接一つずつ変更せず、dataValues配列の中で置換しています。

対象範囲へのアクセスをまとめられるため、大量データの処理ではFor Eachより高速になるケースがあります。

また、rowIndexのように役割が分かる変数名を使うことで、配列処理でも流れを追いやすくしています。

配列へ読み込んだデータは、文字列置換だけでなく集計や条件判定などにも応用できます。配列を使ったループ処理について詳しく知りたい方は、「セルの値を変数配列に取得する方法」も参考にしてください。

✅ 複数の置換条件をまとめて処理する方法

大量データを扱う実務では、1つの文字列だけを置換すれば終わるケースは多くありません。

例えば、

「株式会社」を「(株)」へ変更する
「有限会社」を「(有)」へ変更する
「御中」を削除する

といったように、複数のルールをまとめて適用したいことがあります。

このような場合に、Replace関数を何度も並べて記述すると、後から条件を追加・修正するのが難しくなります。

そこで実務では、配列やDictionaryへ置換条件をまとめて管理する方法がよく利用されます。

・配列で複数の置換条件を管理する

置換条件が少ない場合は、配列を利用すると分かりやすく管理できます。

Sub ReplaceMultipleText()

    Dim replaceList As Variant
    Dim index As Long
    Dim targetText As String

    replaceList = Array( _
        Array("株式会社", "(株)"), _
        Array("有限会社", "(有)"), _
        Array("御中", "") _
    )

    targetText = Range("A2").Value

    For index = LBound(replaceList) To UBound(replaceList)

        targetText = Replace( _
            targetText, _
            replaceList(index)(0), _
            replaceList(index)(1))

    Next index

    Range("B2").Value = targetText

End Sub

この方法なら、新しい置換条件を追加するときも、配列へ1行追加するだけで済みます。

また、置換ルールがまとまっているため、コード全体の見通しも良くなります。

・Dictionaryで管理すると保守しやすい

置換条件がさらに増える場合は、Dictionaryを利用する方法もおすすめです。

Dictionaryなら、設定シートやCSVから置換条件を読み込む設計にも発展させやすくなります。

部署名や商品名の統一など、置換ルールが頻繁に変わる業務では、Dictionaryを利用した方が保守性が高くなるケースもあります。

Dictionaryは重複削除だけでなく、今回のように置換条件を管理する用途にも活用できます。Dictionaryの基本的な使い方については、「値だけを基準に重複削除する方法」の記事でも解説しています。

✅ 大量データを処理するときの注意点

大量データでは処理速度だけでなく、データの安全性も考慮する必要があります。

数千件のデータを一括で更新した後に誤りへ気付くと、元へ戻すのに多くの時間がかかります。

また、数式セルやエラー値が混在している場合は、Replace関数をそのまま適用するとエラーになることもあります。

そのため、実務では処理前にデータの状態を確認し、必要に応じて条件分岐を追加することが重要です。

・数式セルを誤って変更しない

大量データには、値だけでなく数式が入力されているセルも含まれることがあります。

数式セルを文字列として置換すると、計算式そのものが変更されてしまう可能性があります。

そのため、値だけを対象にする場合は、数式セルを除外する設計が安全です。

例えば、次のように判定できます。

If Not targetCell.HasFormula Then

    targetCell.Value = Replace( _
        CStr(targetCell.Value), _
        "旧商品", _
        "新商品")

End If

数式を保持したい帳票では、このような条件を追加するだけでも事故を防ぎやすくなります。

数式セルや空白セルを避けながら処理するには、対象範囲を適切に限定することも重要です。可変データの範囲指定については、「範囲指定を最終行まで指定する方法」をご覧ください。

・エラー値を考慮する

セルによっては、

#N/A
#VALUE!
#DIV/0!

などのエラー値が含まれていることがあります。

そのままReplace関数へ渡すとエラーになるため、事前に判定しておくと安全です。

If Not IsError(targetCell.Value) Then

    targetCell.Value = Replace( _
        CStr(targetCell.Value), _
        "旧商品", _
        "新商品")

End If

このような小さな判定を入れるだけでも、大量データ処理の安定性は大きく向上します。

✅ Range.Replace・For Each・配列の使い分け

大量データの置換では、「どの方法を使うか」が処理速度や保守性へ大きく影響します。

どれか1つが常に優れているわけではなく、目的に応じて使い分けることが重要です。

・Range.Replaceが向いている場面

次のようなケースでは、Range.Replaceが最も簡潔です。

シート全体を一括置換したい
条件分岐が不要
Excelの検索と置換を自動化したい

・For Eachが向いている場面

セルごとの判定が必要な場合は、For Eachが適しています。

例えば、

空白セルは除外する
数式セルだけ除外する
特定の列だけ処理する
条件に一致したセルだけ置換する

といった処理では、For Eachの方がコードの意図が伝わりやすくなります。

・配列が向いている場面

数千行以上のデータを処理する場合は、配列を利用することでセルへのアクセス回数を減らせます。

特に、

大量データの前処理
CSVの整形
マスターデータの更新
商品名や顧客名の一括変更

などでは、配列による高速処理が効果を発揮します。

「高速だから配列を使う」のではなく、「処理件数が多く、速度が問題になるときに選択する」という考え方がおすすめです。

大量データでは処理件数が毎回変わることも多いため、Rows.Countを利用して最終行を取得する設計がよく使われます。詳しくは、「Rows.Countプロパティで行数を取得する方法」も参考にしてください。

✅ Excel VBAの自動化をさらに効率化するポイント

大量データを扱うマクロでは、文字列置換だけでなく、周辺処理も合わせて見直すことで、さらに効率的な自動化ができます。

例えば、

最終行を取得して処理範囲を限定する
データがある範囲だけを配列へ読み込む
ScreenUpdatingやCalculationを一時停止する
必要な列だけを処理対象にする

といった工夫を組み合わせることで、処理時間を短縮しながら安全性も高められます。

また、置換処理の前後でデータをコピーしたり、別シートへ出力したりする処理を組み合わせることで、日々の定型業務をさらに自動化できます。

配列へ読み込む範囲を毎回固定にするのではなく、最終行・最終列を取得して可変データへ対応すると、さらに保守しやすいコードになります。詳しくは、「Endメソッドで最終行・最終列を取得する方法」をご覧ください。

1つのシートだけでなく、同じ置換処理をブック内の複数シートへまとめて適用したい場合は、シートを順番に処理する設計が必要です。ブック全体を安全に更新する方法については、「複数シートの文字列を一括置換する方法」も参考にしてください。

✅ まとめ：大量データの文字列置換は処理方法の選択が重要

今回紹介したように、大量データの文字列置換では、単純にReplace関数を使うだけでなく、データ量や処理内容に応じて方法を選ぶことが重要です。

大量データではセルへのアクセス回数を減らすことが重要
条件分岐が必要な場合はFor Eachが扱いやすい
数千件以上の処理では配列による高速化が効果的
複数の置換条件は配列やDictionaryで管理すると保守しやすい
数式セルやエラー値は事前に判定すると安全
Range.Replace・For Each・配列は目的に応じて使い分ける
最終行取得や画面更新停止なども組み合わせるとさらに効率化できる

大量データを扱うVBAでは、コードが動くだけではなく、「速く、安全で、後から修正しやすい」設計が求められます。

今回紹介した考え方を取り入れることで、文字列置換だけでなく、CSV加工やマスターデータ更新など、さまざまな業務へ応用しやすくなるでしょう。