【Excel】置換リストを使った関数置換のやり方｜SUBSTITUTEやXLOOKUPで一括変換

Excelでのデータ加工や修正作業において、「特定の語句を別の語句にまとめて置換したい」と思うことは多いのではないでしょうか。
特に複数の置換ルールがある場合は、「置換リスト（変換前・変換後の一覧表）」を使って関数で自動置換する方法が便利です。

たとえば…

• 「(株)」を「株式会社」に、「(有)」を「有限会社」にまとめて変換したい
• 「A1」「B2」といった略号を部署名に変換したい
• 手入力の誤字を一括で修正したい

このようなケースに対し、Excel関数だけでできる「置換リストによる一括置換」の方法を、この記事では徹底解説します。VBAは一切使わず、関数のみで実現する実践的な内容です。

✅ 置換リストとは？事前に作る変換ルール表

まず、置換リストとは次のような「変換前」と「変換後」のペアを並べた表のことです。

この表を使って、データ中に含まれる語句を関数で自動的に変換していきます。

✅ XLOOKUP関数でセル内容を丸ごと置換する方法

最もシンプルな一括置換は、XLOOKUP関数を使ってセルの値そのものを置換する方法です。

・XLOOKUP関数の使い方：例

1. Sheet2に以下のような置換リストを作成：

2. Sheet1のA列に元データがあると仮定し、B列に以下の式を入れます：

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, A2)

この式のポイント：

• 一致する語句があれば → 変換後の語句を返す
• 一致しない語句があれば → 元の値をそのまま表示

✅ この方法は、セルの値が完全に一致する場合の置換に最適です。略称→正式名称変換などに活用できます。

【Excel】XLOOKUPで完全一致モードでも一致してしまう理由とは？全角・半角やスペースに潜む落とし穴を徹底解説

✅ SUBSTITUTE関数を使ってセル内の一部を置換する方法

SUBSTITUTE関数は、文字列の中の特定の一部を別の語句に置き換える関数です。
こちらはセルの中にある一部の文字だけを変換したいときに向いています。

・SUBSTITUTE関数の基本構文

=SUBSTITUTE(元の文字列, 検索文字列, 置換文字列)

・「A1-001」のような文字列から「A1」を「営業部」に変換する場合

=SUBSTITUTE(A2, "A1", "営業部")

✅ これにより「A1-001」→「営業部-001」に変換できます。

ただし、SUBSTITUTE単体ではリスト化された複数の置換ルールには非対応のため、次項で紹介する工夫が必要です。

【Excel】文字を置き換える関数まとめ｜SUBSTITUTE・REPLACE・応用例と注意点

✅ 複数の置換をSUBSTITUTE関数で重ねる（入れ子）

SUBSTITUTE関数を入れ子（ネスト）にすることで、複数語句の置換も可能です。

・「(株)」「(有)」をそれぞれ「株式会社」「有限会社」に変換

=SUBSTITUTE(SUBSTITUTE(A2, "(株)", "株式会社"), "(有)", "有限会社")

✅ 簡単なパターンであればこの方法でも対応可能です。

注意点：

• 置換語が3語以上になると、式がどんどん長くなり可読性が低下
• 誤置換やダブル変換のリスクがあるので、扱う語句が多い場合には別の方法を検討しましょう

✅ FILTER関数で置換対象の語句に絞り込んで処理（Excel365～）

FILTER関数を使えば、条件に合う語句のみを動的にリストアップし、その結果をもとに変換対象を制御することも可能です。

応用構文の例：

=IFERROR(XLOOKUP(A2, FILTER(Sheet2!A:A, Sheet2!A:A<>""), Sheet2!B:B), A2)

✅ これにより、空白や不要な語句を排除したうえで正しく置換できます。

【VBA】複数列にフィルターをかける方法｜応用例・エラー対策も解説

✅ LET関数で式を簡潔に整理する（Excel365～）

多段のSUBSTITUTEやXLOOKUPが重なると、式が見づらくなります。
その際に有効なのが LET関数 です。変数に名前をつけて式を整理できるため、可読性が向上します。

例：

=LET(
    text, A2,
    step1, SUBSTITUTE(text, "(株)", "株式会社"),
    result, SUBSTITUTE(step1, "(有)", "有限会社"),
    result
)

✅ LETは複雑な処理を整理し、メンテナンス性も高めることができます。

【Excel】重複の削除を“行単位”で行う方法｜1行全体の重複チェックと削除手順を徹底解説

✅ 部分一致置換が難しい場合は、列を分けて段階処理も有効

一つの式でまとめて処理しようとすると、構文が複雑になりがちです。
そんなときは、複数の列に分けて段階的に置換する方法も有効です。

・ステップ形式の例

このように、段階的に変換処理を進めていけば、管理もしやすくなります。

【Excel】文字列を一気に置き換える方法｜複数セル・複数語句を一括置換するテクニック

■ よくあるエラーと対処法

・XLOOKUPで「#N/A」が出る

原因： 元データが置換リストに存在しない場合
対処： 第4引数に元データを設定（例：XLOOKUP(A2, ..., ..., A2)）で回避

・全角・半角の違いで置換できない

対処： CLEAN関数やTRIM関数で前処理。必要であれば ASC・JIS関数で文字コード変換も。

・SUBSTITUTEで意図しない重複変換

対処： 入れ子順序の工夫、またはデータを事前にサポート列で処理し、順序を明確化

■ まとめ：関数＋置換リストの使い分けで効率的な置換作業を

Excelで関数による置換リストを使えば、手間のかかる変換作業を高速かつ正確に自動化できます。