見出し画像

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

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回では終わらないので 解説はサクっといきます。

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

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

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


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

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

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


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

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

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

 ↕ 

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

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

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

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

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


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

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

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

↑ こんな感じ

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



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

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

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

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



別解:SPLITで処理する場合

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

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


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

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

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

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

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

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

これで、対象の文字列の 1つ目の ・ だけ、文字列に登場しない _ に置換してからSPLITで _ で区切ることで、文字列が 2つに区切られることになります。あとは 後ろ(2つ目)を 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スプレッドシート

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

つまり

"^(.*?)[・・]"

から ? をとって

"^(.*)[・・]"

とするだけ。

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

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

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

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

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

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

なんか 絵文字(アスキーアート)が並んでるみたいw



別解: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まで使ってますw

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

そこで

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

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



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

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

正規表現は複雑ですが、REGEXREPLACESPLIT の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スプレッドシート にも新関数が追加(輸入)されちゃいましたw

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

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

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


この記事が気に入ったらサポートをしてみませんか?