Excel置換を自動化する方法｜対応表・Power Query・VBA・正規表現

2026年2月2日2026年8月17日

SkillStack Lab 運営者のスタックです。

Excelで同じ置換を毎週・毎月繰り返していませんか。

1回だけなら［Ctrl］＋［H］の「検索と置換」で十分ですが、商品名の表記統一、旧部署名から新部署名への変更、CSV取り込み時のデータクレンジングなど、同じルールを繰り返すなら置換ルールそのものを仕組み化した方が管理しやすくなります。

Excelの置換自動化は、「何を置き換えるか」だけでなく、「元データを残すか」「毎回同じ処理か」「その場で直接書き換える必要があるか」で方法を選ぶのがポイントです。

例えば、元データを残して別列へ変換結果を出したいならSUBSTITUTEやREGEXREPLACE、毎月届くCSVへ同じルールを適用するならPower Query、既存セルそのものを一括変更するならVBAが候補になります。

スタック

私が置換を仕組み化するときに重視しているのは、複雑なルールを数式やコードの中だけに埋め込まず、「置換前→置換後」の対応表として見える形に残すことです。後から修正するときや引き継ぐときにも確認しやすくなります。

この記事では、関数・対応表・正規表現・Power Query・VBAを使って、繰り返す置換を再利用できる処理へ変える方法を解説します。

この記事で分かること

Excelの置換を自動化する方法の選び方
SUBSTITUTE・REPLACE・REGEXREPLACEの違い
置換前・置換後の対応表を使う方法
Power Queryで毎月同じ置換を再実行する方法
VBAで選択範囲を対応表どおりに一括置換する方法

Excel置換自動化は4つの方法から選ぶ

最初に、置換方法の使い分けを確認しましょう。

やりたいこと	第一候補	元データ
一度だけ直接置換	Ctrl＋H	直接変更
別列へ変換結果を出す	SUBSTITUTE・REPLACE	残せる
文字パターンで置換する	REGEXREPLACE	残せる
毎月同じ取込データを整形	Power Query	元ソースは変更しない
既存セルを繰り返し直接変更	VBA	直接変更

単発の数セルを直すだけなのに、VBAやPower Queryを作る必要はありません。

反対に、毎月同じ10種類の置換を手で繰り返しているなら、Ctrl＋Hを速く操作するより、置換ルールを再利用できる形にした方が作業を標準化しやすくなります。

全角数字やスペースなどを一度だけ直接修正したい場合は、Excelで全角を半角に関数なしで変換する方法も参考にしてください。

置換前・置換後の対応表を最初に作る

置換ルールが複数あるなら、まず「置換前」と「置換後」を2列で管理するのがおすすめです。

置換前	置換後
第一営業部	営業1課
第二営業部	営業2課
旧商品A	商品A

Excelで置換前と置換後を対応表として管理し、置換ルールを確認しやすくする方法

対応表にしておくと、

どの値を何へ変更したのか確認できる
ルールを追加・削除しやすい
Power QueryやVBAから同じマスターを参照できる
後任者へ処理内容を説明しやすい

というメリットがあります。

Excelのテーブル機能で対応表をテーブル化し、「置換表」など分かりやすい名前を付けておくと、後から行を追加した場合にも参照しやすくなります。

完全一致の置換ならXLOOKUPで対応表を引く方法もある

XLOOKUPが利用できる環境で、セル全体の値を対応表どおりに変換したい場合は、別列へ次のような式を入れる方法もあります。

=XLOOKUP(A2,置換表[置換前],置換表[置換後],A2)

対応表に一致する値があれば置換後の値を返し、見つからなければ元のA2をそのまま返します。

元データを直接変更しないため、変換前後を比較しながら確認できるのがメリットです。

XLOOKUPによる対応表方式は「セル全体が一致する値」を置き換える用途に向いています。文章中に含まれる特定文字だけを変更したい場合はSUBSTITUTEやREGEXREPLACEを使います。

SUBSTITUTE関数で文字列の一部を自動置換する

元データを残し、別セルへ置換後の結果を出したい場合に使いやすいのがSUBSTITUTE関数です。

=SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])

例えばA2に「商品A_旧」が入っている場合、

=SUBSTITUTE(A2,"_旧","")

とすれば「商品A」を返せます。

第4引数を指定すると、同じ文字が複数回登場するときに、何番目だけを置換するか指定できます。

例えば、

=SUBSTITUTE(A2,"-","/",2)

なら、2番目に登場する「-」だけを「/」へ変更します。

SUBSTITUTEのネストを増やしすぎない

SUBSTITUTEの中へさらにSUBSTITUTEを入れれば、複数ルールを1つの数式へまとめられます。

ただし、ルールが増えるほど、どの順番で何を置き換えているのか確認しにくくなります。

置換ルールが増えてきたら、長いネストを追加し続けるより、対応表＋Power QueryやVBAへ移すことを検討してください。

REPLACE関数は文字の位置を指定して置換する

SUBSTITUTEは「何という文字を探すか」を指定しますが、REPLACEは何文字目から何文字分を書き換えるかを指定します。

=REPLACE(古い文字列,開始位置,文字数,新しい文字列)

例えばA2に「AB123456」があり、3文字目から3文字を「***」へ変更するなら、

=REPLACE(A2,3,3,"***")

結果は「AB***456」になります。

ExcelのSUBSTITUTE関数とREPLACE関数について文字指定と位置指定の違いを比較した図

商品コードの一部分だけ書き換えるなど、文字数と位置が固定されたデータで使いやすい方法です。

REGEXREPLACEなら文字ではなくパターンで置換できる

「数字が何文字続くか分からない」「文字列の先頭や末尾だけを処理したい」といったケースでは、正規表現を利用できるREGEXREPLACEが候補になります。

Microsoft 365の対応するExcelでは、次の構文で利用できます。

=REGEXREPLACE(text,pattern,replacement,[occurrence],[case_sensitivity])

例えばA2に「商品12345-A」が入っており、連続する半角数字を「#」へまとめて置換するなら、

=REGEXREPLACE(A2,"[0-9]+","#")

結果は「商品#-A」になります。

一方、数字を1文字ずつ「#」へ変えたいなら、

=REGEXREPLACE(A2,"[0-9]","#")

とします。

REGEXREPLACEは、文字列そのものではなく「数字」「文字列の先頭」「連続する文字」といったパターンを指定できるのが特徴です。ExcelのREGEX系関数ではPCRE2形式の正規表現が使用されます。

REGEXREPLACEの対応環境、構文、正規表現トークンは、MicrosoftサポートのREGEXREPLACE関数で確認できます。

正規表現が使えるからといって、すべての置換をREGEXREPLACEへ変える必要はありません。「東京都→東京」のような固定値ならSUBSTITUTEや対応表の方が読みやすいことがあります。

毎月同じ置換をするならPower Queryへ処理を残す

毎月届くCSVやExcelファイルへ同じ置換を繰り返すなら、Power Queryと相性があります。

Power Queryでは、列を選んで［値の置換］を実行すると、その操作がクエリの変換ステップとして残ります。

翌月は元データを更新してクエリを再実行すれば、記録しておいた変換を再度適用できます。

Power Queryの置換は、元の外部データそのものを書き換えるのではなく、取り込んだクエリ上で変換します。元CSVを残したまま処理結果を作りたい業務にも向いています。

Power Queryの［値の置換］はExcel 2016以降やMicrosoft 365などで利用でき、元の外部データソース自体は変更しません。詳しくはMicrosoftサポートのReplace values (Power Query)を確認してください。

置換ルールが多いなら対応表をPower Queryでマージする

10種類、20種類と置換ルールが増える場合、［値の置換］を大量に追加するより、対応表を別クエリとして読み込んでマージする方法を検討できます。

Power Queryで元データと置換対応表をマージし、置換後の値を取得する処理の流れ

元データと置換対応表をExcelテーブルにします。
両方をPower Queryへ読み込みます。
置換対象の列と「置換前」列をキーにしてクエリをマージします。
対応表の「置換後」列を展開します。
一致した場合は置換後、一致しない場合は元の値を採用します。

例えばカスタム列なら、考え方は次のようになります。

if [置換後] = null then [部署名] else [置換後]

対応表だけ変更すれば置換ルールを更新できるため、処理内容を数式やMコードの中だけに埋め込みにくくなります。

マージ前にキー列のデータ型をそろえ、対応表の「置換前」が重複していないか確認してください。同じキーが対応表に複数存在すると、展開時に元データの行が増える場合があります。

Power Queryの取り込み、複数ファイル結合、マージまで詳しく確認したい場合は、Power Queryの実務での使い方で解説しています。

既存セルを直接一括置換するならVBA

Power Queryや関数は変換結果を別に作る方法ですが、「現在選択しているセルそのものを対応表どおりに書き換えたい」というケースではVBAが候補になります。

次のサンプルでは、シート「置換表」のA列に置換前、B列に置換後を用意し、現在選択している範囲の文字列の定数セルだけを完全一致で置き換えます。

Option Explicit

Sub 対応表で選択範囲を一括置換()

    Dim wsMap As Worksheet
    Dim target As Range
    Dim textCells As Range
    Dim lastRow As Long
    Dim i As Long

    Set target = Selection
    Set wsMap = ThisWorkbook.Worksheets("置換表")

    On Error Resume Next
    Set textCells = target.SpecialCells(xlCellTypeConstants, xlTextValues)
    On Error GoTo ErrorHandler

    If textCells Is Nothing Then
        MsgBox "置換できる文字列セルがありません。"
        Exit Sub
    End If

    lastRow = wsMap.Cells(wsMap.Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False

    For i = 2 To lastRow

        If Len(wsMap.Cells(i, "A").Value2) > 0 Then

            textCells.Replace _
                What:=CStr(wsMap.Cells(i, "A").Value2), _
                Replacement:=CStr(wsMap.Cells(i, "B").Value2), _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                MatchCase:=False, _
                MatchByte:=False, _
                SearchFormat:=False, _
                ReplaceFormat:=False

        End If

    Next i

CleanUp:
    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    Application.ScreenUpdating = True
    MsgBox "処理を中止しました。置換表と選択範囲を確認してください。"

End Sub

LookAt:=xlWholeとしているため、「営業部」を「営業1課」へ変更する場合でも、「営業部門会議」のように文字列の一部へたまたま含まれているセルまでは変更しません。

文字列の一部分も対象にしたい場合はxlPartへ変更できますが、意図しない箇所まで書き換える可能性が高くなるため、対象データを確認してから使用してください。

VBAで直接書き換えた内容は、通常のCtrl＋Zで簡単に元へ戻せません。本番ファイルではなくコピーしたファイルでテストし、変換件数や結果を確認してから利用してください。

また、Range.Replaceでは検索方法、大文字・小文字、全角・半角などの設定が以前の検索操作から引き継がれることがあります。そのため、VBAでは必要な引数を省略せず指定する方が予期しない動作を減らせます。

VBAの保存方法や本番ファイルで使う前の確認ポイントは、Excel便利マクロ8選も参考にしてください。

置換の順番によって結果が変わる「連鎖置換」に注意

対応表を使った一括置換では、ルールの順番にも注意が必要です。

例えば、

置換前	置換後
A	B
B	C

というルールを上から順番に直接適用すると、元々Aだった値が最初にBになり、その後B→Cの置換対象となって、最終的にCになることがあります。

これを「AはBまでで止めたい」と考えていた場合は意図しない結果です。

大量のルールを直接置換する場合は、置換後の文字が別の「置換前」と重複していないか確認してください。必要なら、元データを基準に1回だけ参照するXLOOKUPやPower Queryのマージ方式を使う方が安全です。

PythonやPower Automateまで使うべきケース

Excelブック内の文字列置換だけなら、最初からPythonやPower Automateへ進む必要はありません。

次のようにExcelの外まで処理範囲が広がった場合に検討します。

処理	候補
Excel内の置換・セル更新	関数・Power Query・VBA
大量のCSV・外部ファイルを処理	Pythonも比較
Web画面や別アプリも操作	Power Automateも比較
Excel操作をスクリプト化して共有	Office Scriptsも比較

Python、Power Automate、Office Scriptsまで含めて比較したい場合は、Excel自動化7選で用途別に整理しています。

Excel置換自動化で失敗しない確認手順

置換は便利ですが、間違ったルールも高速に適用してしまいます。

特に直接書き換えるVBAでは、次の順番で確認してください。

元ファイルを複製する
5～20件程度の小さなデータで試す
置換前と置換後の件数を確認する
未変換・誤変換がないか確認する
本番データへ適用する
置換表や処理手順を保存する

自動化の目的は、処理を速くすることだけではありません。

「誰が実行しても同じルールになる」「どの値を変更したか確認できる」「来月も同じ処理を再現できる」という状態にすることが重要です。

Excel置換自動化に関するよくある質問

複数の文字を一括置換する一番簡単な方法は？: 1回だけならCtrl＋Hを必要な回数実行する方法が簡単です。同じ置換を今後も繰り返すなら、置換前・置換後の対応表を作り、XLOOKUP、Power Query、VBAなどから参照する方法を検討してください。
SUBSTITUTEとREPLACEの違いは？: SUBSTITUTEは「特定の文字」を探して置き換え、REPLACEは「何文字目から何文字分」という位置を指定して置き換えます。
Excelで正規表現を使って置換できますか？: 対応するMicrosoft 365版ExcelではREGEXREPLACEを利用できます。半角数字なら[0-9]、連続する半角数字なら[0-9]+のようにパターンを指定できます。
Power Queryの置換で元CSVも変更されますか？: Power Queryの［値の置換］は、読み込んだクエリのデータを変換する処理です。元の外部データソース自体を書き換える処理ではありません。
対応表が多い場合はVBAとPower Queryのどちらがよいですか？: 毎月取り込むデータを整形して別の結果表を作るならPower Query、既存シートのセルそのものを直接変更する必要があるならVBAが候補です。どちらが上というより、元データを残すか直接編集するかで判断します。
VBAで置換したあとCtrl＋Zで戻せますか？: マクロで行った変更は通常のUndoで元へ戻せないため、実行前にファイルを複製してテストしてください。