【Excel】「置換」の常識が一変する凄腕関数★
こんにちは、HARUです！
実務では、セルに入力されたデータの一部を他の値や文字列に変換することがよくあります。
たとえば以下のような住所録があり、１つめの"-"(ハイフン)を"丁目"に一括変更したいとなったとします。
Excelには対象の文字列と置換後の文字列を指示することでデータを置き換える「置換」機能が搭載されていますが、セル内に該当の文字列が複数ある場合などに柔軟な対応ができません。
※下図は2つ目の"-"(ハイフン)まで置き換えられた状態。
そこでこの記事では、データの置き換えをパターンごとに実行できる関数をご紹介します。
複雑な条件でもスムーズな処理を行えるテクニックですので、ぜひご一読ください！
住所の表記を置き換える
サンプルは冒頭の住所録です。
１つめの"-"(ハイフン)を"丁目"に変更し、さらに２つめの"-"(ハイフン)を"番"に変更していきます。
データの置き換えには、SUBSTITUTE関数を使います。
（substitute：代替、置換、代わり）
置換対象を指示する
①SUBSTITUTE関数を挿入する。
②第１引数「文字列」に住所が入力されたセルを参照する。
③第２引数「検索文字列」に"-"(ハイフン)を入力する。
④第３引数「置換文字列」に"丁目"を入力する。
※これにより、"-"→"丁目"に置き換える指示をしている。
第４引数「置換対象」は任意設定なので、試しにこの段階で結果を見てみます。
すると、前述の「置換」機能と同じく、２つ目の"-"まで"丁目"に置き換わっています。
このままではNGなので、第４引数「置換対象」を設定するところに戻りましょう。
そして、今回はここに"1"を入力します。
この状態で決定すると、１つ目の"-"だけが"丁目"に置き換わりました。
数式を下へコピーします。
SUBSTITUTE関数は、対象データの中に第２引数で設定した「検索文字列」が複数ある場合、第４引数「置換対象」に入力した値の数だけ左からカウントし、そのｎ番目に位置する文字列を置換対象にしてくれるのです。
参考までに、今回のサンプルで第４引数「置換対象」を"2"に設定すると、2つ目の"-"が"丁目"に変換されます。
複数の文字列を置換する
ここからは、１つ目の"-"を"丁目"に変え（条件１）、２つ目の"-"を"番"に変える（条件２）という、複数の文字列をそれぞれ異なる文字列に置き換える方法を演習します。
この処理を実現するには、その条件の数だけSUBSTITUTE関数とSUBSTITUTE関数を組み合わせていきます。
①あらかじめSUBSTITUTE関数にSUBSTITUTE関数をネストする。
今回は条件が２つなので、２つのSUBSTITUTE関数を使う。
②条件１を設定する。
今回は１つ目の"-"を"丁目"に変えることが条件１となるので、前述とまったく同じように「文字列」「検索文字列」「置換文字列」「置換対象」を指示する。
次に条件２を設定していきますが、条件１を指示したSUBSTITUTE関数のカッコを閉じ、","(カンマ)を入力すると、条件２のSUBSTITUTE関数の引数ガイダンスが第２引数「検索文字列」に移動していることがわかります。
これは、条件１のSUBSTITUTE関数ですでに参照元をA3セルに設定しているので、条件２のSUBSTITUTE関数では第１引数「文字列」の参照や入力を省略できるためです。
③条件２を設定する。
今回は２つ目の"-"を"番"に変えることが条件２なので、第２引数「検索文字列」が"-"、第３引数「置換文字列」が"番"となります。
さて、条件１で第４引数「置換対象」を"1"に設定したので、条件２の第４引数にはなんとなく"2"を入力したくなります。
試しに"2"を入力して決定します。
ところが、２つ目の"-"は"-"のままです。
これは、条件１のSUBSTITUTE関数で１つ目の"-"が"丁目"に置き換わった上で、残された"-"を条件２で置き換える、という段階的な処理が行われているためです。
２つの"-"のうち、１つは"丁目"に変わっていますので、残る"-"は１つだけです。
そのため、条件２のSUBSTITUTE関数の第４引数「置換対象」に"2"を設定しても、その処理を行う頃には"-"は１つしか存在せず（２つ目の"-"が見つからず）、結果的に条件２の置き換え処理は行われないということです。
今回は、残るすべての"-"を"番"に変えるよう、第４引数「置換対象」には何も入力しない。
または、残る"-"の中で"1"番目にあたる文字列を置換対象とするために"1"を入力する。
いずれかの設定を行うことで、それぞれの文字列を正しく置き換えられます。
数式を下へコピーします。
商品管理番号を変換する
生産年度の切り替えや部品の仕様変更により、商品の管理番号を新しい体系に置き換えることがあります。
サンプルは家具用品の管理番号マスターです。
今回は旧管理番号に以下の変更を加え、新管理番号を生成します。
・"R010"の部分を"R011"とする。
・管理番号の末尾に"Y"をつける。
【条件１】"R010"→"R011"に変更する。
これは各管理番号共通ですが、末尾に"Y"をつけるので、
【条件２】"SS"→"SSY"に変更する。
【条件３】"M"→"MY"に変更する。
【条件４】"W"→"WY"に変更する。
という４つの変更を行うことになります。
SUBSTITUTE関数
①SUBSTITUTE関数を挿入する。
今回は変更点が４つあるため、SUBSTITUTE関数をあらかじめ４つ組み合わせる。
まずは条件１、"R010"→"R011"を設定していきます。
②第１引数「文字列」に旧管理番号を参照する。
③第２引数「検索文字列」に"0"を入力する。
④第３引数「置換文字列」に"1"を入力する。
⑤第４引数「置換対象」に"2"を入力する。
これにより、旧管理番号の左から数えて"2"番目にくる"0"を"1"に置き換える、という指示を出していることになる。
文字列を変換する場合は""(ダブルコーテーションマーク)で囲む必要がありますが、数値の場合は直接入力でOKです。
続けて、条件２～条件４を設定していきます。
⑥条件２：第２引数「検索文字列」に"SS"、第３引数「置換文字列」に"SSY"を入力する。
⑦条件３：第２引数「検索文字列」に"M"、第３引数「置換文字列」に"MY"を入力する。
⑧条件４：第２引数「検索文字列」に"W"、第３引数「置換文字列」に"WY"を入力する。
第１引数「文字列」は条件１でB3セルを参照済なので省略でき、第４引数「置換対象」は参照セル内に同じアルファベットが存在しないため設定する必要がないのです。
この数式を下へコピーすると、それぞれの新管理番号が正しく生成できます。
SUBSTITUTE関数の特長として、検索文字列が見つからない場合はエラーを返さずにスルーする、という性質があります。
たとえばB4セルの旧管理番号"R010-022M"には、条件２で検索した"SS"は含まれていませんが、見つからないことによるエラーは表示せず、次の条件に設定した文字列検索に移ってくれるということです。
REPLACE関数
同じく文字列を置き換える関数として、REPLACE関数があります。
（replace：交換、替える）
SUBSTITUTE関数が検索文字列と置換文字列をズバリ設定するのに対し、REPLACE関数は置換対象を文字数で指示します。
REPLACE関数の基本構成を確認するために、前述の条件のうち、まずは"R010"→"R011"の変更だけをやってみます。
①REPLACE関数を挿入する。
②第１引数「文字列」に旧管理番号を参照する。
③第２引数「開始位置」に、置換対象のデータが左から数えて何番目から始まるかを入力する。
今回は"R010"を"R011"に変更するため、左から"4"番目の0が置換対象となる。
④第３引数「文字数」に、開始位置から何文字分を置換対象とするかを入力する。
今回は左から4番目の0だけを変更するため、"1"文字分が対象となる。
⑤第４引数「置換文字列」に、置換後の文字列を指示する。
今回は左から4番目の0を1に変えるため、"1"を入力する。
この数式を下へコピーすると、それぞれ旧管理番号の"R010"が"R011"に置き換わったデータが返ります。
続けて、管理番号の末尾に"Y"を付加する条件を加えたときに正しく機能するか見ていきましょう。
①REPLACE関数を挿入する。
今回も変更条件の数だけREPLACE関数をあらかじめ組み合わせる。
②条件１：前述と同じ構成で、"R010"を"R011"にする。
③条件２：9文字目から2文字分（"SS"）を"SSY"にする。
④条件３：9文字目から1文字分（"M"）を"MY"にする。
⑤条件４：9文字目から1文字分（"W"）を"WY"にする。
この数式を下へコピーした結果を見てみると、末尾の部分がおかしなことになっています。
複数条件をズバリ指定したSUBSTITUTE関数はその検索文字列が見つからない場合に次の変更条件に移ってくれますが、REPLACE関数は指定の文字数を置換対象にするため、文字列の内容に限らず複数の変更をすべての文字列で実行します。
今回であれば、条件２～条件４は同じ9文字目から変換します。
まず条件２で"SS"が"SSY"に置き換わり、条件３でその"SSY"の先頭の"S"が"MY"に置き換わることで"MYSY"となり、条件４でさらにその"MYSY"の"M"が"WYに置き換わることで結果的に"WYYSY"となってしまったということです。
まとめ
SUBSTITUTE関数の特長
・検索文字列が参照データに複数存在する場合、どこに位置する文字列を置換対象とするか指示できる。
・複数の文字列をそれぞれ異なる文字列に置き換えられる。
・検索文字列が見つからない場合でもエラーを返さない。
REPLACE関数の特長
・文字列の内容に関わらず、指定の文字数を置換対象にできる。
以上の特長から、複数かつ異なる文字列への置き換えを同時に実行する場合はSUBSTITUTE関数が、文字列の内容に関わらず単一条件で強制的に置き換えを実行する場合はREPLACE関数がおすすめです。
シーンに応じて、使い分けていきましょう！
↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。
↓↓Excel操作をとにかく高速化したい方へ↓↓
この記事が気に入ったらサポートをしてみませんか？