エクセルで対応表（変換前・変換後の一覧）を使ってまとめて置換するには、Microsoft 365 なら =REDUCE(B3,$F$3:$F$5,LAMBDA(t,k,SUBSTITUTE(t,k,XLOOKUP(k,$F$3:$F$5,$G$3:$G$5)))) と入れます。対応表の「変換前」を1つずつ取り出して、SUBSTITUTE で順に置き換えていく式です。
置き換えたい言葉が増えても、式は変えずに対応表に行を足すだけで済みます。セルの中身がまるごと対応表の言葉と同じなら、XLOOKUP だけで置き換えられます。この記事では、2つの方法を実際の Excel の画面で説明します。
練習用Excelファイル（無料ダウンロード）
この記事と同じ文と対応表です。まとめて置換する練習に使えます。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
目次
対応表で置換する方法の一覧
| 置き換えたいもの | 式 | 結果（実機） |
|---|---|---|
| 文の中の言葉（いくつでも） | =REDUCE(B3,変換前,LAMBDA(t,k,SUBSTITUTE(t,k,XLOOKUP(k,変換前,変換後)))) | 「東京と大阪の支店」→「品川と梅田の支店」 |
| セルの中身まるごと | =XLOOKUP(B3,変換前,変換後,B3) | 「大阪」→「梅田」、表に無い言葉はそのまま |
| 言葉が2〜3個だけ | SUBSTITUTE を重ねる | 複数の文字を一度に置換を参照 |
REDUCE で対応表の言葉をすべて置き換える
置換後のセル（C3）をダブルクリックし、=REDUCE(B3,$F$3:$F$5,LAMBDA(t,k,SUBSTITUTE(t,k,XLOOKUP(k,$F$3:$F$5,$G$3:$G$5)))) と入れます❶。F3:G5 が対応表です❷。
Enter を押して下へコピーすると、「東京」は「品川」、「大阪」は「梅田」、「名古屋」は「栄」に、1つの文の中で何か所でも置き換わりました❸。対応表に無い「福岡」はそのままです。
式の読み方
| 部分 | 意味 |
|---|---|
REDUCE(B3, $F$3:$F$5, …) | B3 の文から始めて、変換前の言葉を1つずつ順に使う |
LAMBDA(t,k, …) | t はここまで置き換えた文、k はいま使う変換前の言葉 |
SUBSTITUTE(t,k,XLOOKUP(k,…)) | t の中の k を、対応表で見つけた変換後に置き換える |
セルの中身まるごとなら XLOOKUP だけ
「大阪」→「梅田」のように、セルの中身が対応表の言葉とまったく同じなら、=XLOOKUP(B3,$F$3:$F$5,$G$3:$G$5,B3) で置き換えられます。4つ目の B3 は「見つからないときは元のまま」という意味で、実機では表に無い「福岡」がそのまま返りました（XLOOKUP関数）。
うまくいかないときの確認表
| 症状 | 原因 | 対処 |
|---|---|---|
| #NAME? になる | REDUCE・LAMBDA が使えない版（Excel 2019 以前など） | SUBSTITUTE を重ねる方法を使う |
| 一部だけ置き換わらない | 全角・半角やスペースがちがう | 対応表とデータの文字をそろえる |
| 「大阪府」の「大阪」まで変わった | 言葉の一部も置き換わる | 長い言葉を対応表の上に書く・言葉を区切りごと登録する |
| コピーすると対応表がずれる | $ が無い | $F$3:$F$5 のように $ を付ける |
よくある質問
対応表に行を足したら、式も直す必要がありますか？
範囲を広めに書くか、対応表をテーブルにして テーブル1[変換前] のように書くと、行を足しても式を直さずに済みます。
元のセルを書き換えたいです。
結果のセルをコピーして、元の列に「値の貼り付け」をします。
練習用Excelファイル（無料ダウンロード）
文と対応表が入っています。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
コメント