いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)文字列中の複数種類の文字列を置換・削除する

 Excelで、文字列に含まれる複数種類の部分文字列を別の文字列に一括で置き換える方法についてです。
 従来はSUBSTITUTE関数を重ねるぐらいしか適当な方法がなかったのですが、2022年2月ごろに利用可能となったLAMBDA関数等を利用することで対応できるようになっています。

  • REDUCE/LAMBDA関数を用いることで、文字列に含まれる複数種の文字列を一度に置き換えできます。
  • 同様に置換先を空文字列とすることで複数種の文字列を一度に削除できます。

手順

複数種の文字列を1つの文字列に置き換える

f:id:accs2014:20220315170051p:plain:right:w550

 画像ではC8セルに式を入力して、B8に入力されている文字列「東京-京都-大阪」のうちB3:B5に入力されている3種の文字列「東京」「名古屋」「大阪」をいずれも「☆」に置き換えています。
 B8に「東京」「大阪」は含まれているものの「名古屋」は含まれていないので、結果的に置換は2箇所のみ行われています。

 C8セルの式の内容は次のとおりです。

 C8セル

=REDUCE(B8,B3:B5,LAMBDA(a,b,SUBSTITUTE(a,b,"☆")))

 REDUCE関数とLAMBDA関数、さらにSUBSTITUTE関数を組み合わせることにより、B8をaと名付け、B3:B5をbと名付けたうえで、aに含まれるbの各要素を順次「☆」に置き換えています。
 単純に「=SUBSTITUTE(B8,B3:B5,"☆")」などとすると三種類の置換結果が3つのセルに表示されるだけですが、REDUCE関数とLAMBDA関数の働きにより、一つの置換結果が次の置換前文字列(SUBSTITUTE関数の第1引数)に渡されるので、すべての置換元文字列を置き換えた一つの結果が得られるというわけです。



f:id:accs2014:20220315170055p:plain:right:w600

 さらにREDUCE関数の第1引数を「B8:B12」とすることで、5つある置換前文字列のすべてに対し同様の置換を行っています。
 SUBSTITUTE関数による置換ですので、同じ置換元文字列が複数含まれている場合でもすべて置き換えられます(B9→C9セルのケース)。

 C8セル

=REDUCE(B8:B12,B3:B5,LAMBDA(a,b,SUBSTITUTE(a,b,"☆")))

 上の例では3種の置換元文字列をシート上に配置して参照していましたが、次の画像では式の中で直接指定しています。
 内容的には1つ上の画像と同じです。

f:id:accs2014:20220315170058p:plain:w700

 C3セル

=REDUCE(B3:B7,{"東京","名古屋","大阪"},LAMBDA(a,b,SUBSTITUTE(a,b,"☆")))

 REDUCE関数の第2引数を「{"東京","名古屋","大阪"}」としています。一つ上の画像のB3:B5セルが縦に並んでいることを考えると厳密には「{"東京";"名古屋";"大阪"}」とするところでしょうが、この例では結果に影響しません。

複数種の文字列をそれぞれ対応する文字列に置き換える

 上記の2番目の画像の例を踏まえ、さらに置換元文字列の種類ごとに異なる置換先を設定して置き換えます。
 画像ではB3:C5の範囲に3種の置換パターンを示す表を設置し、それを参照することで置換を行っています。

f:id:accs2014:20220315170102p:plain:w700

 C8セル

=REDUCE(B8:B12,B3:B5,LAMBDA(a,b,SUBSTITUTE(a,b,FILTER(C3:C5,B3:B5=b))))

 SUBSTITUTE関数のところを「SUBSTITUTE(a,b,C3:C5)」などとしてしまうと置換元文字列と置換先文字列の対応付けがうまくいかず、正しい出力が得られません。
 そこで一例として、SUBSTITUTE関数の第3引数にFILTER関数を用いることで、b(置換元文字列)の各要素に対応する置換先文字列を抽出しています。ただFILTERよりXLOOKUPの方が自然だったかもしれません。

複数種の文字列を削除する

f:id:accs2014:20220315170105p:plain:right:w600

 置換先文字列を空文字列にすれば文字列の削除ができます。
 ここでは、上からの2番目の画像の例をベースとして、SUBSTITUTE関数の第3引数を「""」とすることで3種の文字列を一括削除しています。

 C8セル

=REDUCE(B8:B12,B3:B5,LAMBDA(a,b,SUBSTITUTE(a,b,"")))