見出し画像

「Googleスプレッドシートから見た！」Excel 14の新関数 -11 TEXTBEFORE / TEXTAFTER つづき

mir

Excelに追加された 14の新関数を Googleスプレッドシートからの視点で検証する記事も 11回目 。

Excel 14の新関数シリーズの 最終回です。

14の新関数 の最後の 2つ。 テキスト操作系の TEXTBEFORE、TEXTAFTER を前回に続いて検証していきます。

  • 関数の特徴

  • Excelでの メリット、デメリット、活用　← 前回ここまで

  • Googleスプレッドシートの機能、関数との違い

  • Googleスプレッドシートでは無い機能を どう補うか

この流れで検証きました。

シリーズ前回の記事


EXCEL 14の新関数 TEXTBEFORE / TEXTAFTER

  • 区切り文字の前を取り出すのが　TEXTBEFORE関数

  • 区切り文字の後を取り出すのが　TEXTAFTER関数

こちらの2つのExcel 新関数、Excel上での動きの基本や使い方、活用例は 前回たっぷり書いたので、今回は Googleスプレッドシート視点で 検証してきます。


Googleスプレッドシートの機能、関数との違い

TEXTBEFORE / TEXTAFTER に該当する 関数は 残念ながら Googleスプレッドシートにはありません。

しかし、Googleスプレッドシートには Excelにはない 文字列を取り出す最強の関数があります。

それが REGEXEXTRACT関数 です。


文字列操作系最強関数 REGEX3兄弟

ExcelとGoogleスプレッドシート、互いに関数の輸入・逆輸入を繰り返し、当初あった Googleスプレッドシートの関数における優位性はだいぶ薄れてきました。

Googleスプレッドシートの 最強関数四天王、FILTER、SORT（SORTBY）、UNIQUE、そして今回 SPLIT（TEXTSPLIT）が EXCELでも使えるようになり、EXCEL は 一行数式で出来ることが格段に広がりました。

でも、いまだにExcelには無い Googleスプレッドシート オンリーの 最強関数が 幾つかあります。

代表となるのは QUERY関数で、どちらかというとこの最強集計関数に目がいきがちですが、もう一つ（3つ） REGEX3兄弟という文字列操作系最強関数が存在します。

REGEX3兄弟とは、文字通り REGEXから始まる3つの関数で 正規表現を使って 超柔軟な文字列処理が実現できる Googleスプレッドシートにしかない関数です。

■REGEX3兄弟　いずれも正規表現が使える
※ REGEX3兄弟は mirが勝手にそう呼んでるだけです

REGEXMATCH ・・・ 三男
最強の文字列一致検索関数

REGEXEXTRACT　・・・　次男
最強の文字列抽出関数

REGEXREPLACE　・・・　長男
最強の文字列置換関数
 

今回は TEXTBEFORE / TEXTAFTER の代替なので、文字列を抽出する REGEXEXTRACT の出番となります。

難点は 正規表現 が、ひじょーに初心者殺しで 可読性が低いという点。


Googleスプレッドシートでは無い機能を どう補うか

TEXTBEFORE / TEXTAFTER の代表的な特徴

  • 指定した区切り文字の 前（後ろ）を取得できる

  • 区切り文字は複数指定できる

  • スピル対応（複数セルを処理できる）

  • マイナスで 後ろから〇番目の区切り文字といった指定ができる

  • 〇番目の区切り文字といった、自由な指定ができる

これらを全て満たすことを考えると、とんでもなく複雑で長い式になりそうなので、「やりたいこと」に合わせて 上で紹介した REGEXEXTRACT関数をベースに 式を作って対応するのが良さそうです。

ケースごとに検証していきましょう。


ケース1. 区切り文字（1つ）の前、もしくは 後ろを取得したい

いちばんシンプルな基本形ですね。

このような ・ 区切りの前（後ろ）を取り出したい場合、Googleスプレッドシートで対応するにはどうすればよいでしょうか？

幾つか方法がありますが、まずは 複数の関数を組み合わせず 一つの関数で対応できる REGEXEXTRACT関数を使ってみましょう。

区切り文字 ・ の前を取り出す
=REGEXEXTRACT(A2,"^(.*?)・")

区切り文字 ・ の後ろを取り出す
=REGEXEXTRACT(A2,"・(.*)$")

Googleスプレッドシート

正規表現の説明に突入すると、とても1回では終わらないので 解説はサクっといきます。

^(.*?)・
　^ 文頭から
　^.*?・ 文頭からなにかしらの文字の０文字以上の繰り返しで、
　　　最後が ・ となる最小のマッチ
　^(.*?)・　上記の ・を含まない部分　→　つまり ・の前の部分

・(.?)$
　$ 文末
　・.*$ 文末前の ・から始まる なにかしらの文字の
　　０文字以上の繰り返しの（最大）マッチ
　・(.*)$　上記の・を含まない部分　→　つまり ・の後ろの部分

Googleスプレッドシートの関数における 正規表現は、残念ながら 正規表現の 先読みや後読み が使えない(NOT SUPPORTED) ってことで、カッコをつけて キャプチャグループをつくることで 必要な部分だけを取り出しています。


正規表現を使ったことない人にとっては、もうなんだかわからんですよね？　

専門用語はあまり気にしないでください。

一応、Googleスプレッドシートにおける 正規表現と 正規表現が使える関数は、まいどお馴染み 「いきなり答える備忘録」さんがまとめてくれています。

正規表現の 貪欲（最大マッチ）、怠惰（最小マッチ） をざっくり理解する

上の関数解説の「最小のマッチ」という部分は、

貪欲マッチ（最大マッチ）　大食いマッチともいう　
　→　条件にマッチするなるべく長い文字列

　↕ 

非貪欲マッチ（最小マッチ）　怠惰なマッチともいう
　→　条件にマッチするなるべく短い文字列

という意味なんですが、貪欲とか大食いとか怠惰って・・・。
七つの大罪か！って思いますよね。

簡単にいうと 今回のようなケースで 区切り文字 ・が、複数あった場合は、

=REGEXEXTRACT(A2,"^(.*?)・")　・・・最小マッチ指定（？をつける）

とすると、一つ目の ・ の手前を返して、


=REGEXEXTRACT(A2,"^(.*)・")　・・・貪欲マッチ指定 （?をつけない）

の場合は 最後の ・ の手前を返すってことです。

本当はここに ・ は入らないですが

↑ こんな感じ

今回は TEXTBEFORE の挙動と揃える為、1つ目の区切り文字にヒットする 最小マッチの記述にしています。


注意：区切り文字がメタ文字の場合はエスケープを

REGEXEXTRACT で処理する際の注意点として、区切り文字が 正規表現における メタ文字（例えば . や * 半角カッコなど）の時は、エスケープ処理が必要ってのがあります。

\ をつけることで メタ文字を 通常の文字として扱えます。

.ドットで区切りたいときは \. とする


別解：SPLITで処理する場合

どうしても正規表現は 苦手・・・。っていう人は、SPLITを使う方法でもいいいでしょう。

1つ目の区切り文字 ・ の前を取り出す
=INDEX(SPLIT(A2,"・"),,1)

１つ目の区切り文字 ・ の後ろを取り出す
=INDEX(SPLIT(SUBSTITUTE(A2,"・","_",1),"_"),,2)

前 は、SPLITして１つ目をINDEXでとるだけなんで簡単ですね。

一方、後ろは 区切り文字が複数登場するケースを考慮すると面倒です。ここで役立つのが レガシーな 置換関数 SUBSTITUTE です。

Googleスプレッドシートでの関数による置換処理は、正規表現の使える REGEX３兄弟の長兄（ラオウ的ポジション）、 REGEXREPLACE が圧倒的に便利すぎるんで、ついそっちを使っちゃいますが、まだまだEXCELでは SUBSTITUTE の出番も多いです。

SUBSTITUTE関数のメリットは、置換対象の文字 に対して 〇回目に 登場した ものだけ置換する、といった指定が出来る点。

SUBSTITUTE(A2,"・","_",1)

これで、対象の文字列の １つ目の ・ だけ、文字列に登場しない _ に置換してからSPLITで _ で区切ることで、文字列が ２つに区切られることになります。あとは 後ろ（２つ目）を INDEXで取り出せばOK。

複数の関数を組み合わせる必要がありますが、こちらの方がわかりやすいかもですね。


ケース2. 区切り文字の複数指定、スピル対応

ケース1で使用した REGEXEXTRACT関数 を使った指定区切り文字の前（後ろ）を取り出す方法は、区切り文字の複数指定や、複数セルの一括処理（スピル）にも使えます。

REGEX3兄弟は Arryaformulaと組み合わせて スピル対応できる ってのも最強の理由の一つです。

A2:A6のセルから、一つ目の ・または ･ で区切った前を取り出す
=Arrayformula(REGEXEXTRACT(A2:A6,"^(.*?)[・･]"))

A2:A6のセルから、一つ目の ・または ･ で区切った後ろを取り出す
=ARRAYFORMULA(REGEXEXTRACT(A2:A6,"[・･](.*)$"))

Googleスプレッドシート


まず区切り文字の複数指定ですが、A列に適当に並べた ゲームタイトルのうち、よーく見ると

信長の野望･新生

だけ、・が全角ではなく半角の ･ というのがわかります。。まぎらわしい！

この・の全角、半角両方を区切り文字として指定する為に

[・･]

という書き方で指定しています。これは正規表現における 1文字の複数指定（または）を意味します。

1文字ではない 複数指定（または）の記述は | を使うのですが、これは TEXTSPLITの回で少し触れています。

画像の6行目のように空白セルを対象としたり、区切り文字が見つからない場合は、 #N/A エラーになるので、これは IFERRORあたりで エラー時の処理を追記した方がよいでしょう。


別解：スピル対応は出来るが区切り文字複数パターン対応が厳しい

ケース1と同じく、ケース2も 正規表現を使わない別解を書いておきましょう。

A2:A6の 1つ目の区切り文字 ・ または ･ の前を取り出す
=INDEX(SPLIT(A2:A6,"・･"),,1)

TEXTSPLITの回で触れましたが、GoogleスプレッドシートのSPLITは 区切り文字を連続で記載するだけで 複数区切り文字指定となります。（ただし 単文字に限る）

なので、前を取り出す場合は比較的簡単です。

ちなみにスピル対応の方ですが、INDEX関数自体が 配列を返せる関数である為、Arrayformulaを使う必要がありません。INDEXだけでスピらせることができるので 簡潔に記載が可能です。

ただし、これはあくまでも 1番目の区切り文字の「前」を取り出すときだけ使える式です。

複数区切り文字指定で後ろを取り出したり、〇番目の区切り文字の前を取り出したい場合は通用しません。

複数区切り文字に対しては

SUBSTITUTE(SUBSTITUTE(A2,"・","_",1),"･","_",1)

このように SUBSTITUTEを2重（入れ子）にして処理できそうにも思えますが、区切り文字の並びによっては 2つ _ に置換され その後のSPLITで _ で分割した際に  3つに分割されてしまう恐れがあります。

これだと ただしく　1つ目の ・または ･ で区切った 後ろ を取り出すことができませんし、 2番目の ・または ･ で区切った前を取り出す際も期待する結果とならないケースが出てきます。

複数区切り文字指定 のケースは 正規表現の使えるREGEXEXTRACTを使った方が無難と言えます。 


ケース3. 最後に登場する区切り文字の前（後ろ）を取得したい

EXCELの TEXTBEFORE / TEXTAFTER なら、第3引数に -1を 指定するケースです。

A2:A6のセルから、最後の ・または ･ で区切った前を取り出す
=TEXTBEFORE(A2:A6,{"・","･"},-1)

A2:A6のセルから、最後の ・または ･ で区切った後ろを取り出す
=TEXTAFTER(A2:A6,{"・","･"},-1)

EXCEL

非常に完結に書けますし、もちろんスピル対応可能。

Googleスプレッドシートで代替する場合、これも REGEXEXTRACT でいけます。

A2:A6のセルから、最後の ・または ･ で区切った前を取り出す
=Arrayformula(REGEXEXTRACT(A2:A6,"^(.*)[・･]"))

A2:A6のセルから、最後の ・または ･ で区切った後ろを取り出す
=ARRAYFORMULA(REGEXEXTRACT(A2:A6,".*[・･](.*?)$"))

Googleスプレッドシート

あまり需要なさそうですが、最後の区切りの前を取り出す場合は、正規表現部分の最小マッチを最大マッチ（貪欲マッチ）に変えれば良いだけです。

つまり

"^(.*？)[・･]"

から ？ をとって

"^(.*)[・･]"

とするだけ。

一方需要がありそうな 最後の区切りの後ろを取り出す場合は、

".*[・･](.*?)$"

このように 前に .* の貪欲マッチを追記することで [・･](.*?)$ は 最小マッチとすることができます。

動きとしては文頭からサーチしていくため先に記述した .*の大食いが強く作用するので、(.*?)$"の ?を省略して

 ".*[・･](.*)$"

としても、結果としては同じになります。

なんか 絵文字（アスキーアート）が並んでるみたいｗ


別解：SPLIT + INDEX では 最後の区切り文字の後ろ取得ができない → LAMBDヘルパー関数で対応

最後の区切り文字の前 を取得は使うケースがあまりないので、後ろを取得だけ別解を入れておきます。

なんとなく SPLIT+ INDEX で分割した 後の最後（一番左）を取得すれば良さそうですが

Googleスプレッドシートには、 -1で後ろからという指定ができる関数が（現時点では）ないのと、SPLIT + INDEX だと 区切り文字の数がバラバラの セル範囲を対象とする場合、一括で 一番後ろの区切り文字の後ろを取得が出来ません。（一つの大きな長方形範囲とみなしてしまう為）

※本来はこの記事の公開時点 2023/02/25 では、Googleスプレッドシートの最新輸入関数 CHOOSECOLSが使える想定でしたが、一度使えたのに不具合があった為 停止されてしまいました。

仕方がないので、1セルずつ 分割 → 分割した要素数を取得 → 最後の要素を取得 を繰り返す LAMBDAヘルパー関数を使用します。

今回の場合は BYROW、 MAPでもどちらでもOK

=MAP(A2:A6,LAMBDA(v,LET(arr,SPLIT(v,"・･"),INDEX(arr,,COLUMNS(arr)))))

Googleスプレッドシート

ヘルパー関数内で 最新関数 LETを使って、SPLIT で分割した結果を arrと置いています。COLUMNS(arr)が分割後の配列の横の長さ、つまり 最大値なのでINDEXでそれを指定することで一番後ろが取得できます。


ケース4. 〇番目の区切り文字の 前（後ろ）を取得したい

実はこれが一番面倒（複雑）なので、最後にもってきました。

たとえば 3番目の ・･ の前（後ろ）を一括で取得したい場合

ゲームじゃないけど ・がいっぱいつくサンプルを追加

A2:A6のセルから、3番目の ・または ･ で区切った前を取り出す
=Arrayformula(REGEXEXTRACT(A2:A6,"^((?:.*?[・･]){2}.*?)[・･]"))

A2:A6のセルから、3番目の ・または ･ で区切った後ろを取り出す
=Arrayformula(REGEXEXTRACT(A2:A6,"^(?:.*?[・･]){3}(.*?)$"))

Googleスプレッドシート

複雑さが増してきましたね。

後を取り出す式の方がわかりやすいので、そっちから解説しましょう。

今回 3番目の後ろを取り出したいので、文頭から 

何らかの文字列（または空白）・（または･）何らかの文字列（または空白）・（または･）何らかの文字列（または空白）・（または･）

このような 繰り返しの後ろを取り出したいってことになります。
これを正規表現で表すと

"^(.*?[・･]){3}"

となります。

※{3}は直前の 文字もしくはグループを 3回繰り返すという意味

ただ、これだとカッコ内が キャプチャグループ扱いで取り出す対象となってしまいます。

これをキャプチャしない グループ化する為の記述が

(?: ここに正規表現)

になります。

後を取り出すは、こんなイメージ

これを踏まえると 前を取り出す方は

こうなるのがわかりますね。前を取得する場合は、対象となる3番目の区切り文字を含めないようにする為、2回繰り返しとその後ろという 正規表現にしています。


〇番目の区切り文字の 前（後ろ） を取得する式を 汎用性あるものにする

この〇番目の区切り文字の 前（後ろ）を取得する式を 汎用性あるものにすれば、かなり TEXTBEFORE / TEXTAFTER の代替式といえるものに近づきますね。

■TEXTBEFORE 代替式
セル範囲から 指定した区切り文字の〇番目の 前を取り出す

=LAMBDA(text,delimiter,instance_num,Arrayformula(REGEXEXTRACT(text,"^((?:.*?["&delimiter&"]){"&instance_num-1&"}.*?)["&delimiter&"]")))(A2:A6,"・･",3)

■TEXTAFTER 代替式
セル範囲から 指定した区切り文字の〇番目の 後を取り出す

=LAMBDA(text,delimiter,instance_num,Arrayformula(REGEXEXTRACT(text,"^(?:.*?["&delimiter&"]){"&instance_num&"}(.*?)$")))(A2:A6,"・･",3)

LAMBDA の引数を TEXTBEFORE / TEXTAFTER の引数に揃えたんで 長くなってますが、汎用性のある式になったんじゃないでしょうか？

もちろんこのまま 名前付き関数 にもできます。

ただ、この式は以下に対応していません。

  • 対象のテキストが 改行を含んでいるケース

  • 区切り文字が 2文字以上の文字列のケース

  • instance_num をマイナス指定で 後ろから〇番目としたいケース

これらも REGEXEXTRACT関数で処理できますが、ケースごとに個別に正規表現を作っていくしかないです。

もしかすると、これ以外のケースでもエラーや誤抽出になるかもしれません。

正規表現は、バグ（エラーが発生するケース）を見つけにくいってのも難点です。

※このケースが考慮できていない、この使い方だとエラーが出るといったものが見つかりましたらお知らせください。


Q. カッコ内の文字を取り出してみよう

最後に TEXTBEFORE / TEXTAFTERの応用問題を Googleスプレッドシートでチャレンジしてみましょう。

なお、この問題は エクセルの神髄さん Twitterお題を参考にさせていただきました。

こんなイメージ

Office製品には（エクセル）（ワード）そして（パワポ）があります。

A1に入った画像のような文章から カッコの中の文字だけ 横に展開するように1つの式で抽出したい。

※括弧は全角カッコとする。（←ここだけ少し簡単にしました）
※括弧が正しく対になっているものとする

Excelでの回答は  エクセルの神髄さんを参考に。

これをGoogleスプレッドシートで処理できそうでしょうか？

まずは考えてみてください。







↓↓↓
回答は以下
↓↓↓



A. カッコ内の文字を取り出してみよう

いくつか方法がありますが、まずは REGEXEXTRACT関数を使う方法からいってみましょう。


A1. REGEXEXTRACT関数で抽出する

=LET(x,SPLIT(A1,"（"),FILTER(REGEXEXTRACT(x,"^(.*?)）"),REGEXMATCH(x,"）")))

SPLIT、FILTER、REGEX3兄弟の三男 REGEXMATCH、さらに最新輸入関数のLETまで使ってますｗ

文字列抽出の最強関数 REGEXEXTRACT ですが、正規表現にマッチした 最初の 文字列のみ を抽出する（仕様となっており、マッチする複数の文字列を全て抽出したい場合は 使いにくい、という弱点があります。

そこで

先に SPLITで （　で文字列を 4つに分割。
それを LETで xと置いて
REGEXMATCH で ） を含む を条件に合致するセルから
REGEXEXTRACT で文頭から ） の手前までの文字列だけを取り出したものに
FILTERで絞り込み。

こんな処理手順で カッコ内の文字を抽出しています。


A2. REGEXREPLACE関数で カッコ内の文字だけを残す

=SPLIT(REGEXREPLACE(A1,"(?:^|）).*?（(.*?)）.*?(?:$|（)","_$1_"),"_")

正規表現は複雑ですが、REGEXREPLACE と SPLIT の2つの関数で処理する方法です。

REGEXEXTRACT関数では難しい複数のマッチする文字列を抽出したい場合、最初に分割する方法 回答1で使った方法以外に、REGEXREPLACE関数を使う方法があります。

要は取り出したい文字列以外の邪魔な部分を、区切り文字に置き換えちゃうという方法です。

Office製品には（エクセル）（ワード）そして（パワポ）があります。

↓　REGEXREPLACE(A1,"(?:^|）).*?（(.*?)）.*?(?:$|（)","_$1_")

_エクセル_ワード_パワポ_

キャプチャグループで取り出したものは $1 という記述で利用することができます。これを活用しています。（キャプチャグループが複数ある場合は $2、$3と していきます）

最後に SPLITで分割すれば OK。


A3. 正規表現なし SPLIT + Query で取り出す

=TRANSPOSE(Query(ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(A1,"）")),"（",,false)),"select Col2 where Col2 is not null"))

正規表現を使わない方法も紹介しておきましょう。

これも手順が多いです

SPLITで ）で分割
TRANSPOSEで縦並びに変換
Arrayformula + SPLIT(空白除去なし）で、 （ で分割
抽出したいワードは 全て 2列目 にはいる
Queryで 2列目 で空白ではないものだけに絞り込み
TRAPSPOSEで 横並びに変換

こんな処理をしています。参考までに。

Excelの応用問題を、TEXTBEFORE / TEXTAFTER のない Googleスプレッドシートでチャレンジしてみました。

TEXTBEFORE / TEXTAFTER なくてもいいけど、あればやっぱり便利だなと思いますね。


EXCEL 14の新関数 シリーズ終了です

全11回、3か月くらいこのシリーズやってきましたが、ようやく今回で 14の新関数シリーズも終了です。長かったー。

久しぶりに Excel（オンラインの方）を結構触ったんで、色々発見もあって自分も楽しめました。

Excel14の新関数シリーズは、Googleスプレッドシートには無い Excelの新関数を Googleスプレドシート視点で検証する ってテーマだったんですが、2月月初旬に、なんと14の新関数の 配列操作系関数を中心に Googleスプレッドシート にも新関数が追加（輸入）されちゃいましたｗ

LAMBDAのタイミングで輸入されなくて、もう輸入ないかと思われた LETも登場。

じゃあ、次はこれを取り上げよう！ って思ってたんですが・・・。
先週くらいから 配列操作系の関数が停止中なんですよね。。

というわけで、来週の休日更新 noteは 状況次第で内容考えます。


いいなと思ったら応援しよう！

mir
チップ大歓迎です。やる気がアップしますｗ