Googleスプレッドシート 検索と置換を使いこなそう！3（正規表現の先読み後読み コピペで応用）

Googleスプレッドシートの 検索と置換シリーズ 3回目です。

単に 指定した文字を検索したり置換するだけではない、検索と置換の応用的な使い方を中心に紹介しています。

今回は正規表現を使った 検索と置換の最終回、他のサイトではあまり見かけない GAS無しで出来る「検索と置換」の超応用例を書きたいと思います。

前回は正規表現を使った検索と置換の事例をたっぷり書きました。

Googleスプレッドシート 計算の不具合について

本編とはまったく関係ない話ですが、先週から Googleスプレッドシートで計算がリアルタイムで動かないという問題が、一部のアカウントで発生しているようです。

X(旧 Twitter)、Yahoo知恵袋 でも何件か見かけており、海外でも Google Docs Editors Community などで幾つか報告があります。

完全に不具合によるものと思われるんですが、私の環境ではいずれのアカウントでも発生していないので検証が出来ません。動きがあれば Xでポストしていきます。

現時点では根本的な解決策はなく、更新をかけて強制定期に再計算させるか、不具合の発生していないアカウントに共有をかけて別アカウントで開き操作するといった方法で皆さん凌いでるみたいです。

では本編いきましょう。

GASなし 検索と置換で 出来ること総まとめ

今回掲載分も含めた 検索と置換で出来ることを総まとめしました。やりたいことを見つけたらリンク先に飛んでみましょう！

今回の目次です。

検索と置換 で改行に置換する 裏技

Excelの検索と置換は 置換後の文字に改行を指定することが出来る、つまり改行への置換が出来ますます。しかし残念ながら、Googleスプレッドシートでは 改行に置換することが出来ません。

これは検索と置換シリーズの第1回で触れたGoogleスプレッドシートの検索と置換の仕様なんですが、その際に補足として

と書きました。まずはこの裏技を紹介していきましょう。

ちなみにGASを使った方が実はシンプルだったりするので、諸般の事情や宗教的理由からどうしてもGASが使えないって人以外は、来週あたりに紹介予定のGASを使う方法をおススメしますｗ

検索と置換で改行に置換をする裏技は「こんな方法もあるんだ」と参考程度に読んでいただけば十分です。

キャプチャグループで1回改行へ置換。無駄な改行を削除

前回紹介しましたが、正規表現を使った検索と置換では、検索でマッチした文字列をカッコで括り キャプチャグループとすることで、置換後の文字に $1（1つ目のグループ）、$2（2つ目のグループ）・・・として利用することが出来ます。

このキャプチャグループを使うことで、例外的に 置換後の文字に改行を指定することが出きるわけです。

たとえば上のように連続する無駄な改行（セル内の空白行）を削除したい！といった場合

とすることで、

このように1回改行だけが残り、不要な改行が削除できます。

これは先頭や末尾の不要改行は考慮していませんが、それらの改行削除も考慮した記事を 「いきなり答える備忘録」さんが書かれています。

しかし、これは セル内に改行がある場合でのみ使える方法ですし、指定した区切り文字を改行に置換で活用するのはちょっと厳しいわけです。

では、どうすればよいか？

関数を使って 改行に置換する方法

そもそも改行への置換は、関数を使えば簡単なんです。

たとえばA列のセル内の「、」を全て改行に置換したい場合

こんな式を入れて下にフィルすれば良いですね。

置換関数はGoogleスプレッドシートだと、正規表現が使える REGEXREPLACEに目がいきがちですが、シンプルな置換の場合は 正規表現のメタ文字を意識せず置換処理が出来る SUBSTITUTE パイセンも優秀です。（〇番目を置換するって機能もあります）

CHAR(10) は改行 を表しています。

このように関数での改行への置換はシンプルに処理できます。

これで解決すれば良いんですが対象のデータが1列とは限りませんし、（理由はわかりませんが）事情により 列の追加や作業用のシートは使えないってケースがあるかもしれません。（個人的には謎ですが、たまにこういう前提条件がある）

じゃあ、この関数を使う方法をそのまま検索と置換にあてはめちゃえば良いのでは！？って方法が 検索と置換で改行に置換する裏技となります。

検索と置換で 改行に置換する裏技（回答）

値の入ったセルの数式化は前回登場した、改行ありのセル内の文字列をまるっと取得する方法

([\s\S]+)

を使います。これを 先ほどの SUBSTITUTEへ入れこめば良いので

指定した文字「、」を改行に置換する には

このように設定すれば良いわけです。

検索と置換では改行の置換をせず、そのままセルの中身をSUBSTITUTE関数に放り込んで、実際は関数の処理で改行に置換してますｗ

$1 としてしまうと、式にそのまま文字列は入らないのでエラーになります。 "$1" として ダブルクォートの中にキャプチャした文字列が入るようにしましょう。

数式化したセルは必要に応じて、コピー値貼付けで値化しておきましょう。

しかし、この方法には実は欠点があります。

このように改行への置換対象の「、」を含まない 値が入った全てのセルを 数式化してしまうんです。

通常の文字列なら影響はあまりないのですが、数値や日付がテキスト化してしまうので、これではちょっと困ります。

「、」を含むセルの中身だけを置換対象とする記述に改良しましょう。

検索と置換の正規表現は「先読み・後読み」が使える！

「、」を含むだけであれば、検索と置換は 「完全に一致するセルを検索」にチェックをつけなければ部分一致なので簡単です。

でも「、」を含むセルの値全体をキャプチャ（取得）したい、といった場合はどうすればよいでしょうか？

思いつく方法が2つあります。

1つはこのように [\s\S]* （0回以上の繰り返し） で、「、」を挟む方法です。これはなんとなくわかりますよね？

REGEXMATCHでテストすると、このように 「、」を含むセルだけがTRUEとなっているのがわかりますね。

もう1つの記述方法が

このような 正規表現の 先読み を使って「含む」を判別する書き方です。

正規表現の中でもちょっと難解だけど、かなり便利な 「先読み・後読み」という手法。今回伝えたかったのはコレです！

※一般的なプログラミングで使う際の先読み・後読みと、シート上の検索と置換で使う際の先読み・後読みでは 若干書き方が違う点もあります。

(?=[\s\S]*、)([\s\S]+)

これが、なんで 「、」を含む 文章全体になるのか？

この先読み後読みの説明をすると、それだけで2回分くらい消費する上に多くの人が頭が混乱するんで、今回は完全に説明を割愛しますｗ （mirが説明に自信ないってのもあります）

他のサイトで勉強いただくか、今回はまるっとコピペで使ってください！！

ちなみに、この正規表現の「先読み・後読み」は、Googleスプレッドシートの正規表現 関数でお馴染みの REGEX3兄弟では使えません。

↑ こちらの 公式の RE2 正規表現の使い方についての説明 のリンク先、githubのページを見ると使えないことが書かれています。

でも、検索と置換では 正規表現の「先読み・後読み」が使えるんです！

※関数だと実は QUERY関数の matches なら 正規表現の先読み・後読みが使えます。（が、それはまた別のお話で）

ちょっと [\s\S]だとわかりにくいんで、 . に戻して検証すると

こんな感じで 「、」を含む 全文にマッチしてるのがわかります。

今回はせっかくなんで、この先読みを使った方法で 改行への置換式を改良してみましょう。

検索と置換で 改行に置換する裏技（改良版 回答）

というわけで「、」を含むセルだけを対象に「、」を改行に置換するには

このようになります。

改良版だと、「、」を含まない文字列だけのセルや数値、日付のセルは数式化はされず、影響がないのがわかりますね。

実際は「、」を含むセル内の文字列を『、を改行に置換する式』に置換している。ので、これを改行に置換と言えるかは微妙ですが、こんな裏技があるよってことで紹介しました。

正規表現の「先読み・後読み」を使った検索と置換の他の応用例を見ていきましょう。

検索と置換で 〇〇を含まないセルを探す

正規表現において「含む」は割と簡単なんですが、「含まない」はちょっと難しいんです。

単文字の含まないは割と簡単

1文字であれば「以外」は上のようにあらわせます。

Googleスプレッドシートの検索と置換で このような ある特定の1文字が含まれないセルを検索するには

このように 「以外」の文字の繰り返しに完全一致するセルを検索対象とすればよいです。

当然ですが「完全に一致」としないと、部分的には 「アイドル」や「ももいろクローバー」も大文字アルファベットを含まない文字列として 検索でマッチしてしまうので、正しく「含まない」セルが検索できません。

また、こちらも当たり前ですが「含まない」セルを検索すると、非常に多くのセルにヒットします。

検索範囲を指定せず「すべてのシート」だと、置換処理の場合は思わぬシートに影響が出ることもあります。検索範囲の指定を忘れずに！

ここまでは大丈夫ですね。

文字列を含まないセルを検索する

では、1文字ではなく複数文字の文字列（たとえば「りんご」）を含まないセルを検索したい場合はどうすれば良いか？

ここで先ほどの「先読み・後読み」が再び必要になります。

含まないを検索する場合は 否定先読みを使った記述となります。
※否定先読みに対して、通常の先読みを「肯定先読み」ということもあります。

このように「りんご」を含まないセル 2件が検索にヒットしました。

複数の文字列を含まないセルを検索する

複数ワードの場合を考えてみましょう。
「りんご」と「めろん」の両方を含まない セルは検索できるでしょうか？

両方という言い方をすると and のように感じますが、意味合いを考慮すると否定の場合は

つまり or に言い換えても同じ意味合いとなります。（※厳密にいえば、ANDの否定は 「りんご」と「めろん」の片方は含んでいてもOKで、両方とも含んだ場合のみ除外となります）

or の正規表現なら、検索置換の第1回に登場しましたね。 | パイプが使えそうです。

「りんご」「めろん」を含まない 唯一のセル A8 にのみヒットしました。

「含まない」の検索 も使えるシーンは結構あります。難しいと感じるかもしれませんが、否定先読みの理解は一旦置いといて、パターンとして覚えておいても損はないです。

検索と置換で AND検索を使う

続いては 正規表現のAND検索、たとえば

「りんご」「みかん」「めろん」の全てを含むセルを検索したい。

こんな時はどうすれば良いでしょうか？

関数での対応も結構難しい 含むのAND検索

実は関数で処理する場合も、セルが複数ワードを全て含むかを判定するのは結構面倒です。

一例ですが、このように3つのワードを使ってREGEXMATCHとARRAYFORMULAで含むの判定を各々のワードでチェックしたうえで、ANDで 全てTRUEか？を判定、といった書き方になります。

FILTER関数で 3つのワードを含むセルだけを抽出するなら

煩雑に感じますが、3件くらいなら 1つずつREGEXMATCHを記述した方が簡単だったりします。

肯定先読みの連続記述で AND検索ができる

正規表現において複数ワードのAND検索をする場合は、またまた先読みの出番です。

含むを表す 　(?=.*文字列)(.+)　の先読み部分　(?=.*文字列)　を繋げて記述するだけでアンド検索となります。

通常の正規表現の記述だと 複数ワードの順番を気にする必要があるのですが、先読み・後読みは 単なる検証で 1つの検証が終わると、また先頭に戻るようなイメージです。

だから、順番を気にせず羅列するだけで AND検索が出来るんです！

「なんで？」と思うかもしれませんが、何度も言うようにこれを理解しようとして オーバーヒートするよりは、とりあえずコピペで使ってみて、さらに掘り下げたいと 興味が沸いてきたら仕組みを学ぶで良いかと思います。

まずは使ってみる！です。

検索と置換で 数式内のセル参照を 相対参照 → 絶対参照 一括変換

先読み・後読みが 検索において、「含まない」や「すべて含む」で使えることがわかりました。今度は置換でどのように活用できるかを見てみましょう。

先読みを使った検索と置換の応用例として、指定範囲内の数式のセル参照を一括で 相対参照 → 絶対参照 に切りかえが思いつきます。

検索と置換で 数式内のセル参照を 絶対参照 → 相対参照 変換

ちなみに逆の 絶対参照 → 相対参照 は割と簡単です。

前提条件として数式内で絶対参照以外に文字列としての $は登場しないものとしましょう。（複雑化しすぎるので）

この場合、

数式であるセル内の $を検索して 空白に置換する

を実行すれば良いです。

数式であるセル は、先頭が = で始まる を条件とすれば良いですが、= は置換対象に巻き込みたくないので、先読み・後読みが必要となります。

ここでは 後読み (?<=文字列) を利用します。

また 正規表現をONにした際に、$は文末を表すメタ文字として扱われてしまうので、エスケープ \ を付けて\$ とすることで、 文字列として$を扱う必要があります。

これを空白に置換すれば良いですね。

数式以外の 途中に =を含む文字列や、通常の文字列の $は消えていませんね。式内の$だけを削除し、絶対参照→相対参照の一括処理ができました。

数式内のセル範囲をどう検索で判定するか

本題の 相対参照 → 絶対参照 は、かなり難易度が高いです。

まずはセル範囲をどう定義するか？

セル範囲のパターンを見てみましょう。なお、セル範囲のアルファベット（列）、数字（行）のそれぞれに $を付ける必要があるので、それぞれの視点で定義をします。

結構複雑ですね。これを整理すると

このように4つのパターンに分けることが出来ます。

これを正規表現 の 先読み・後読みを使って記述すると

これを合体させて完成形にしましょう！

相対参照を絶対参照に切り替える 検索と置換の設定

検索に入れる正規表現は

このようになります。かなり複雑ですねｗ

(?<=^=.*)

これは先ほども登場した 先頭に = がある、つまり数式であるかを判別する為の後読みなので いいですね。

\$*

つづくこれは、セル範囲と判定した大文字アルファベットや 数値の前に既に絶対参照の $ が付いていた場合は 二重で $がついてエラーになることを避けるために 一旦$を削除という処理をする為のものです。

( パターン1 | パターン2 | パターン3 | パターン4 )

そしてこれがメインの処理です。パイプを使って先ほど作成した4つのパターンのいずれかにマッチする、セル範囲と判定した（後で頭に$を付ける）大文字アルファベット、数値を取り出します。

この4つのパターン 全体を カッコで括ることで、すべて 1つ目のキャプチャグループ $1 として再利用できるようにしています。

置換後も少し複雑です。

$が 正規表現では置換後でもキャプチャグループで利用するメタ文字である為、単純に $$1 では 頭に $が付きません。

$$$1

このように$1に$をつけるには、 $$ をつける必要があります。

検索と置換の設定をまとめると、

このようになります。うげーって感じですね。

実際に動きを確認してみましょう！

数値や文字列、途中に =を含む文字列 には影響せず、様々なパターンの式で絶対参照化が出来てますね。既に置換前から一部が絶対参照になっている式においても、二重に$がついてエラーになることが回避できています。

もちろんまだ検証漏れのパターンがある可能性もありますが、一応 相対参照を一撃で絶対参照にする検索と置換 の完成です！

といっても、さすがに相対参照→絶対参照の一括置換したいからって、こんな正規表現をイチから考えるくらいなら 手作業でやるわって感じもしますがｗ

mirのnoteに記載があったことをアタマの片隅に入れといて、必要な時にここにコピペしにくるで良いと思います。

検索と置換を使って 伏字入りのテスト問題を作ろう

高度な正規表現の先読み・後読みを使った 検索と置換を使って、ちょっとくだらないことをして遊んでみましょうｗ

こんな感じの文字数をヒントにした穴埋め問題を作りたい！って要望があったとします。

これを検索と置換でやってみよう！ってネタです。

Q. 文字数を合わせた穴埋め問題を数式で生成したい

南斗五車星 風のヒューイ
南斗五車星 炎のシュレン
南斗五車星 山のフドウ
南斗五車星 雲のジュウザ
南斗五車星 海のリハク
葬送のフリーレン
薬屋のひとりごと
五等分の花嫁
魔法使いの嫁
暴食のベルセルク
盾の勇者の成り上がり
七つの大罪 黙示録の四騎士

↑ これが元データです。これを 後ろの「の」から最後までの文字を文字数を変えずに 〇に置き換えたいってお題です。（〇は漢数字ですが、こっちの方がマルっぽいんですよね）

気分展開で、まずはシート関数を使って式で解決してみましょうか。こちらのお題いってみましょう！

B3:B14 に入った 上の文字列の 最後の「の」の後ろの文字を 〇 に置き換得る式を作れるか？

いけそうな方はチャレンジしてみましょう！

↓↓
ここから回答です。

↓↓

A. 文字数を合わせた穴埋め問題を数式で生成する

回答（一例）です。

今回は関数回じゃないんで細かい解説は割愛しますが、後ろの「の」にヒットさせる為に 正規表現の 貪欲（最長）マッチ "^.+の" を使ってます。そうするとその後ろの残った部分が最後の「の」の後の文字列、つまり伏字対象となる箇所なんで、これを (.+)$ としてキャプチャで取得。これをxと置く。

次にxをLENとREPTで文字数分〇を繰り返した文字列を生成、 yとして用意しておく。

最後に 普通にSUBTITUTEで xをyに置換。こんな流れです。

検索と置換で 対象の文字と一文字ずつ伏字にする

それでは、この処理を検索と置換でどう処理するか？

ちなみに、条件がなければ意外と簡単です。

これだけ。超簡単ですね。対象範囲の指定だけ注意です。

これに今回の条件が加わると、正規表現の先読み・後読みを使う処理となります。↓ 答えはこちら。

本当にこれだけで出来るのか？確認してみましょう。

おー。イメージ通りに 置換できました。

なぜこのような置換処理となるのか・・・。これは mirもうまく説明できませんｗ 

ちなみに

(?<=.*の.*).

だけだと、 一番目の 「の」の後ろを全て置換します。

これに (?!.*の.*)　を追加することで、後ろに「の」がこない文字列が対象となり、Q11 やQ12 は最後の「の」の後ろが 〇に置換される（という動きなんだろう）ってことです。

ちょっと面白いネタでした。

検索と置換、次は GASの世界へ

GASなしで出来る検索と置換の応用例として、正規表現の先読み・後読みを使ったディープなものまで紹介しました。

シート上で検索と置換で出来ることは、限界近くまでは引っ張り出せたんじゃないでしょうか。

でも Excelでは標準機能として備わっている

• 検索にヒットしたセル件数を取得する

• 検索にヒットしたセルを全てアクティブにする

• 検索にヒットしたセルの情報を一覧にする

• 簡単に改行に置換する

この辺りは、シート上でどう工夫しても出来ません。（セル件数くらいは工夫で出来ますが）

というわけで、次回は いよいよ 検索と置換をGASで扱う Class TextFinder の世界へと突入していきます！