スプレッドシートには文字列や数値を置換する機能が備わっています。同じ文字列や数式が何度も繰り返して使われている場合に、一つ一つ直していくのは手間です。
そんなときは置換機能を使います。その際、似たような値を使っているセルがあると、変更する必要がないところまで置換することになってしまいます。
ここでは、そんなときに便利な正規表現を使った置換方法について実例でまとめています。
正規表現を使うと便利な例
例えば、以下のような数式において、
=if(Z36="","-",ROUNDUP(Z36*$X$33,-1))ROUNDUP(Z36*$X$33,-1) の 「-1」を「-０」にする場合を考えます。
通常の置換で「-1」や「,-1」を「-0」や「,-0」にすると、ROUNDUP以外の該当箇所も置換されてしまいます。
このときに ROUNDUP(○○,-1)の「-1」の部分だけ変更するといった指定ができるのが正規表現です。
そのままの値を出力する際のポイント｜カッコとドルマーク
正規表現でヒットした値のうち、ある個所は置換して、ほかの箇所はそのままとしたい場合に必須になるのが以下の２つです。
- カッコ「( )」を使って検索する。
- ドルマーク「$」で置換する。
正規表現で検索をかけるときに対象をカッコ（）で囲んだ場合、置換する際に、その値を$1という変数で呼び出すことができます。
カッコ（）が複数ある場合は、記載してある順番に、$1, $2, $3,,,,と数値が１つづつ大きくなります。
文章では難しいので、実際の正規表現による置換を見たほうがわかりやすいです。
正規表現の結果をそのまま抜き出す実例
例えば「111aaa@@@」という文字列があるとします。
これを順番を入れ替えて「@@@111aaa」としたい場合、以下のように書くことができます。
(1{3})(a{3})(@{3})とは何か？
通常、正規表現で「111aaa@@@」にマッチさせようとすると、直前の文字を指定した回数だけ繰り返すことを示す{数値}を使って次のように記述します。
1{3}a{3}@{3}1が3回、aが3回、@が3回繰り返す値という意味です。
これだけでは、ただマッチするだけです。
それぞれの値を個別に呼び出したい場合は、パーツごとにカッコで囲みます。
(1{3})(a{3})(@{3})こうしたことで、それぞれにマッチする部分を$1~$3で呼び出すことができるようになります。
まとめると以下のようになります。
| 正規表現 | マッチする値 | 呼び出すための変数 |
|---|---|---|
| (1{3}) | 111 | $1 |
| (a{3}) | aaa | $2 |
| (@{3}) | @@@ | $3 |
とは何か？
置換後として指定している「$3$1$2」は、上記の表と連動するため、「@@@111aaa」となります。
このように、正規表現でカッコとドルマークを使うことで、値をそのまま抜き出すことができます。
正規表現を使った数式の一括置換実例
正規表現を使用した検索
正規表現を使った一括置換をするには、スプレッドシート上で「ctrl + h」をクリックして、置換のウィンドウを開きます。
中に「正規表現を使用した検索」があるのでチェックマークを入れます。チェックを入れると自動で「大文字と小文字の区別」にもチェックマークが入ります。
これで正規表現が使える状態になります。
数式も置換対象にする
数式の中身も置換対象にしたい場合は、「数式内も検索」にチェックマークを入れます。
スプレッドシート上で数式が入っているセルの中の数式が表示されます。
正規表現の入力
ROUNDUP(Z36*$X$33,-1) の 「-1」を「-０」にする場合は以下のように入力します。
パッと見だと目を逸らしたくなりますが、中身の一つ一つはとても簡単です。検索と置換後の文字列はそれぞれ以下のようになっています。
検索対象
ROUNDUP\((.+),-1\)・ROUNDUP
ROUNDUPの部分はそのまま検索でひっかけるところです。
・\（バックスラッシュ）
バックスラッシュはエスケープシーケンスと呼ばれるもので、後ろに続く記号や文字列は正規表現ではないことを指示するためのものです。
カッコ （）は正規表現で使われる記号です。バックスラッシュをつけることで通常のカッコであることを指示しています。
・カッコ（）
バックスラッシュのないカッコ（）はひとつの塊であることを示す正規表現の記号です。置換するときに、ドルマークと数字を組み合わせて「$1」という表記を使うと、一つ目のカッコの中身をそのまま抜き出すことができます。
正規表現の中に、カッコ（）が複数ある場合は、$1, $2, $3とすることでカッコの中身が順番にそれぞれに入ります。
・「.」ドット
ドットは任意の1文字を表します。
・「+」プラス
プラスは直前の文字の1回以上の繰り返しです。「.+」とすることで、任意の文字が1回以上繰り返すことを表しています。
・,-1\)
末尾の部分の「,-1\)」は「,-1)」という値を示しています。
置換後
ROUNDUP\($1,-0\)こちらは先ほどの検索とほとんど変わりません。置き換わったところは以下の2点のみです。
・(.+) → $1
これは、任意の1文字以上の繰り返しでヒットした箇所をそのままとすることを示しています。正規表現の一つ目のカッコ（）の中身を、$1とすることで呼び出せます。
・-1 → -0
今回置換したい部分です。「-1」を「-0」に置換する指示を出しています。
置換後の数式例
この状態で置換を実行すると、ヒットした箇所が置き換わります。例えば数式が以下のようになります。
=if(Z36="","-",ROUNDUP(Z36*$X$33,-1))↓
=if(Z36="","-",ROUNDUP(Z36*$X$33,-0))
「-1」以外のところはそのままで、「-1」のみ「-0」と置き換わったことがわかります。
注意点
置換後の対象に正規表現を使わないようにすることが大切です。
ROUNDUP\(.+,-1\)上記の検索結果を、以下のように置換すると、
ROUNDUP\(.+,-1\)置換後の結果には「.+」という文字列が表示されてしまいます。
スプシ置換のカッコ()とドルマーク$は使い方を覚えてしまえばとても便利です。ぜひ活用してみてください。
