「Googleスプレッドシートから見た！」Excel 14の新関数 -9 TEXTSPLIT 応用編

mir

2023年2月11日 21:40

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

このシリーズ2ヵ月以上やってるんですが、その間に時代は変化しました。

大河ドラマは 「どうする家康」に変わり、Googleスプレッドシートには無かったExcel14の新関数として特集組んでましたが・・・、このうち配列操作系 8個が Googleスプレッドシートに追加されちゃったｗ

幸い？ なことに、テキスト操作系の TEXTSPLIT、TEXTBEFORE、TEXTAFTER の3つは今回の Excelからの輸入関数には含まれなかったので、残りの Excel新関数の検証はそのままいきましょう。

このシリーズの次に Googleスプレッドシート追加の 11の新関数を取り上げたいと思います。（ネタが尽きないわー）

前回はテキスト操作系の TEXTSPLIT関数 の基本動作をGoogleスプレッドシートの SPLIT関数と比較しながら検証しました。

思った以上に違う点が多く記事が長くなってしまったので、2回に分けることにしました。今回は TEXTSPLIT関数の続き 応用編となります。

シリーズ前回の記事

TEXTSPLIT検証　応用編

前回は書けなかった

SPLIT関数で TEXTSPLITの縦横区切りをどう実現するか？
縦横区切りを得た TEXTSPLIT その代償で失ったものとは？
TEXTSPLIT の活用例・応用例（分割だけじゃない）

これらの検証をしていきましょう。

Googleスプレッドシート SPLIT関数で縦横区切りできるか？

EXCELのTEXTSPLIT関数と GoogleスプレッドシートのSPLIT関数 、この2つの最も大きな違いが縦方向への区切りじゃないでしょうか？

TEXTSPLIT関数の第3引数である縦方向区切り文字が無い SPLIT関数で縦方向への分割はどうすればよいか？

縦方向のみの分割であれば、単純に分割した後にTRANSPOSE すればよいですね。

=TRANSPOSE(SPLIT(A2,","))

TRANSPOSE は、縦横を入れ替える関数です。
これは Googleスプレッドシート、Excel 両方にある関数です。

それでは、縦横両方に区切るケースはどう対応するか？
これをお題にしてみましょう。

Q1. SPLIT関数で縦横区切りをどうすれば実現できるか？

前回の検証で使った Excelの TEXTSPLITの式

=TEXTSPLIT(A1,{"：","、"},CHAR(10),,,"")

これと同じ画像のような結果を得る処理を Googleスプレッドシートで再現できるでしょうか？

これは有名なテクニックなんで知ってる人も多いでしょうが、知らない人は考えてみましょう。

Aチーム：田中、山田、早瀬
Bチーム：松本、宮田
Cチーム：林、河野、水島、工藤

↑ 使用したデータ。A1セルをダブルクリックしてセル内に貼り付け

↓

ここから回答
↓

A1. SPLIT関数 2段階SPLITで縦横区切りを実現する

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(A1,CHAR(10))),"：、"))

いきなり答えです。2段階スプリットで縦横区切りに対応しています。

複雑な式はカッコの内側から見ていくのがポイントです。処理の流れとしては、

まず「改行」（縦方向の区切り文字）で SPLITを実施
横に展開されます。

これを TRANSPOSE で縦方向に変換。
ここまでは縦区切りのみのケースと一緒ですね。

**③ ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(A1,CHAR(10))),"：、"))**

この縦方向の配列に対して、横区切り文字の：、の2つで再度SPLIT。Googleスプレッドシートなので縦方向のスピル処理には Arrayformulaをつける必要があります。

この2段階スプリット方式で縦横区切りに対応できます。

ちなみに上の手順③の縦配列に対して Arrayformula + SPLIT で一括分割は、Googleスプレッドシートではかなり使える処理なんで覚えておきましょう。

縦横区切りを得た TEXTSPLIT その代償で失ったものとは？

では逆に、Excelの TEXTSPLITで複数セルの一括分割（スピル処理）は出来るのか？

前回触れなかったテーマですが、残念ながらこれは出来ません。

=TEXTSPLIT(A2:A4,"、")

このように第1引数に A2:A4 と範囲を指定すると、画像の通り縦方向へのスピルが優先され、横に配列が展開されません。

一つ目の区切られた文字だけが結果としてかえります。これから取り上げる予定ですが、TEXTBEFORE関数で

=TEXTBEFORE(A2:A4,"、")

こうした時と同じ結果ですね。

縦方向の区切りを実装したからなのか、 Excelのスピルは配列のネスト（入れ子）に対応していないってことなのかわかりませんが、これは困りましたね。

でも、これも工夫(他の関数との組み合わせ）で解決する方法があります。

Q2. TEXTSPLITで縦1列の複数セルを1つの式で一気に横分割（スピらせる）は、どうすれば実現できるか？

Excel関数ですけど、お題いってみましょうｗ

ま、これも知ってる人は既に知ってるネタですが、知ってる人も解法を1つだけ知ってるって感じじゃないでしょうか？

mirの検証では 3つほどアプローチ（本当は4つだけど）があるかなと思っています。

どうでしょう考えてみてください。

↓

ここから回答
↓

A2. TEXTSPLITで縦1列の複数セルを1つの式で一気に横分割する方法

一番式の短い解法からいきましょう。

A2-1. 一度連結してから分割する破壊的創造方式

=TEXTSPLIT(TEXTJOIN("_",FALSE,A2:A4),"、","_",,,"")

一度TEXTJOINで範囲を一つの文字列に結合してから縦横分割するというテクニックです。

TEXTJOINで連結する際の連結文字は、対象範囲で使われていないものを指定します。（今回の場合は _ アンダーバー）

これを最終的にTEXTSPLITの縦区切り文字として指定することで、結果として各セルを横分割したものと同じ結果を得ることができます。

ただしTEXTJOINは文字数の上限が 32,767文字 という縛りがあり、これは計算途中であっても超えたらエラーとなります。

というわけで、セル数が膨大だったり、1セルの文字数が多い場合は注意が必要です。

A2-2. REDUCE+VSTACK 連結繰り返し方式

=IFERROR(DROP(REDUCE("",A2:A4,LAMBDA(pv,cv,VSTACK(pv,TEXTSPLIT(cv,"、")))),1),"")

2番目は REDUCEを使うテクニック。1つずつ TEXTSPLITをして配列をVSTACKで連結していく方法です。

VSTACKにDROPと、これまで検証してきた配列操作系関数との組み合わせです。

これ BYROWで行毎に TEXTSPLIT処理をしたいところなんですが、Googleスプレッドシートでは可能な BYROWによる行毎に横スピルが、Excelだと残念ながら出来ません。無念。。

ちなみに実はこの2つの解法は、この記事のだいぶ前（昨年末）に「いきなり答える備忘録」さんで紹介されています。

多少式は違うものの、こちらは後だしなんでパクリじゃんって言われかねないｗと思って作ったのが3番目の解法です。

A2-3. MAKEARRAYとINDEXで分割要素を取り出す方式

=LET(range,A2:A4,x,"、",MAKEARRAY(ROWS(range),
MAX(LEN(range)-LEN(SUBSTITUTE(range,x,"")))+1,
LAMBDA(r,c,IFERROR(INDEX(TEXTSPLIT(INDEX(range,r,1),x),,c),""))))

MAKEARRAY で高さ（縦方向）を対象範囲の行数、幅（横方向）を行毎の区切り文字の最大値 +1 = 最大分割数とした入れ物を用意して、TEXTSPLITで分割した要素を一つずつ INDEXで取得しています。

無駄に長いし複雑だしで、クールな式とは言えませんね。「いきなり答える」さんと被らない式を無理やり作ってみましたが・・・。お勧めしません！

1番目の方法、「一度TEXTJOINで統合してからの～ TEXTSPLIT 縦横分割」がシンプルでおススメです。

今回触れなかった 4つ目の手法は、TEXTBEFORE / TEXTAFTER の回で紹介します。

TEXTSPLIT の活用例・応用例（分割だけじゃない）

TEXTSPLITは文字列を指定した区切り文字で分割する関数ですが、他の関数と組み合わせることで、ちょっと違う使い方もできます。

特に上記の複数セルの一括分割で使った

TEXTSPLIT と TEXTJOIN

という

まさに 破壊と創造 の組み合わせは結構色々使えます。

幾つか変わった活用例を紹介しましょう。

活用例1. 複数の検索文字を指定して一括置換

変換表にそって多対多で変換するのではなく、変換表に一致するワードをある1つの文字列に置き換える 多対１で変換するケースです。

上の画像のように A1セルの文章（生徒の作文）に含まれる文字で、D1:J1 の生徒名に合致するものを、SNSに投稿するので xxx と伏字にして A3のようにしたい。

つまり、リストの文字列を全て "xxx"(伏せ字）に変換したいってお題なんですが、このようなケースは結構 Excelだと悩ましいですよね。

Excelの文字列置換関数と言えば SUBSTITUTE ですが、これを入れ子するにも限度があります。

もちろんイマドキは、REDUCEと組みあわせて

=REDUCE(A1,D1:J1,LAMBDA(pv,cv,SUBSTITUTE(pv,cv,"xxx")))

こんな風にループ処理的な関数記述で処理出来ますが、REDUCEは慣れが必要なんでちょっとハードル高いんですよね。

Googleスプレドシートだったら正規表現の使える最強置換関数 REGEXREPLACE があるんで、 JOIN関数で | パイプを連結文字としてリスト範囲を連結することで、置換対象を「または」記述 化できるので、一発で処理出来ます。

=REGEXREPLACE(A1,JOIN("|",D1:J1),"xxx")

これを EXCELの TEXTSPLIT関数でどう対応するか？
ここは文字列の「置換」から発想を転換して、

リストの文字列（生徒名）でA1を分割からの～
　↓
置換後の文字 "xxx" で分割した文字を連結し再結合

こんな風に考えてみましょう。

=TEXTJOIN("xxx",FALSE,TEXTSPLIT(A1,D1:J1))

結果として 多対1の一括置換が出来るというテクニックです。

活用例2. 複数行（列）の空白詰め

こちらは VSTACK / HSTACK の検証記事でも取り上げたお題です。

列ごと（縦方向にみて）空白を削除して上に詰める、
もしくは行ごと（横方向にみて）空白を削除して左に詰める。

あるあるな要件なんですが、これもExcel関数でやろうとすると結構難しくて、VSTACK の応用だと以下のような複雑な式になってしまいました。

=LET(array,A2:E12,DROP(IFERROR(
REDUCE("",SEQUENCE(COLUMNS(array)),
LAMBDA(pv,cv,HSTACK(pv,IFERROR(TOCOL(INDEX(array,,cv),3),"")
))),""),,1))

Excel列方向上詰め式

これも TEXTJOINとTEXTSPLITで処理する方法があります。

列ごとの空白上詰めよりも、行ごとの空白左詰めの方が簡単なんで、先にそっちから。

=TEXTSPLIT(TEXTJOIN(",",TRUE,EXPAND(A2:Q5,,COLUMNS(A2:Q5)+1,"_")),",","_",TRUE,,"")

EXCEL横方向空白詰め式

少し解説しておきましょう。

EXPAND(A2:Q5,,COLUMNS(A2:Q5)+1,"_")

式の起点は Excel14の新関数シリーズ第1回に取り上げた EXPANDです。

この式でなにをやっているか？

元の範囲 A2:Q5 に対して、第2引数（行方向）を省略することで行数はそのままに列方向だけ COLUMNS(A2:Q5)+1 で1つ増やして、増やした部分のセルに "_" を入れています。

つまり、以下のような対象範囲の右端に "_"だけの列を追加した配列を生成しています。

この右端の"_" は最後の TEXTSPLITで縦区切り文字として使う用です。
折り返し位置をマーキングしているような意味合い。

次のこの配列を TEXTJOINで ","（カンマ）を区切り文字として連結。
その際に、第2引数を TRUE指定することで、空白除去します。

最後に TEXTSPLIT で "," で横区切り、"_"で縦区切りと指定することで、行毎に空白詰めしたものと同じ結果を得ることができるわけです。

注意点としては、TEXTJOINで連結後の文字数が多すぎるとエラーになるのと、連結時に ,"_", という箇所ができるので TEXTSPLIT側でも第4引数を TRUEとして、,"_", の箇所（行の先頭左端）に空白セルが残らないうように空白除去する必要があります。

最終的に TEXTSPLIT側で空白除去しているので、TEXTJOIN側の空白除去（第2引数の TRUE指定）は無くても同じ結果は得られます。

ただ、TEXTJOINで連結した際の 文字数が増えてしまうので、TRUE指定でこの段階でも空白除去しておいた方がよいかなと。（式を極限まで短くしたいなら削ってもいいですが）

これが列ごと（縦方向）の処理だと少し複雑で式が長くなります。

=TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",TRUE,
TRANSPOSE(EXPAND(A2:E12,ROWS(A2:E12)+1,,"_"))),",","_",TRUE,,""))

EXCEL縦方向空白詰め式

TEXTJOIN の動きは横方向に連結していって、範囲の右端までいったら下の行の左端からまた横方向に連結という、横方向への連結優先なんですが、

TEXTSPLITも横方向に区切っていって、縦区切り文字があったら下の行の左端へ折り返してまた横方向に区切っていくという、横区切り優先 の動きになっています。

その為、どうしても縦方向に処理する場合は、

TEXTJOINの前にTRANSPOEで縦横変換
TEXTSPLITの前にTRANSPOSEで縦横変換

と2回TRANSPOSEが必要になります。
このせいで式が長くなり複雑化しています。

TEXTJOINとTEXTSPLITを組み合わせた応用例でした。

その他の TEXTSPLITの応用例

その他の TEXTSPLITの活用例は、またまた登場の「いきなり答える備忘録」さんが色々取り上げられてます。

この中でもシンプルで使えるケースが多そうな、区切った後の〇番目だけ取得について、少しだけ触れておきましょう。

TEXTSPLIT で横区切り文字を指定した場合、結果配列は横に展開されます。
その配列から INDEX や CHOOSECOLS で、区切った後の〇番目の要素を取得することができます。

■A7セルの文字列を - で分割して、2番目の要素を取得する
=INDEX(TEXTSPLIT(A7,"-"),,2)

■A7セルの文字列を - で分割して、最後の要素を取得する
=CHOOSECOLS(TEXTSPLIT(A7,"-"),-1)

INDEXの方が馴染みが合って関数名が短いんで普段はこっち使うと思いますが、「最後の」とか「最後から〇番目」といった指定が必要な場合は、CHOOSECOLS のマイナス指定が便利ですね。

また分割後の配列の要素数 は、横幅（長さ）を調べればよいので、COLUMNS関数を組み合わせることで取得できます。

■A7セルの文字列を - で分割した要素数（いくつに分割されるか）
=COLUMNS(TEXTSPLIT(A7,"-"))

これを応用することで、文字列の中に 指定した文字が幾つ含まれるか？をカウントすることができます。

古典的手法ですが、 SUBSTITUTEで指定した文字を空に置換して LENで置換前、置換後の文字の長さの差分を取得でも同じ結果が得られます。

■A7セルの文字列に - が幾つ含まれるか？
=COLUMNS(TEXTSPLIT(A7,"-"))-1
↓これでもよい
=LEN(A7)-LEN(SUBSTITUTE(A7,"-",""))

TEXTSPLITを使った手法だと {"-","/"} と配列指定で - または / が合わせて幾つ含まれるか？といった複数指定のカウントが可能です。

ただ TEXTSPLITは範囲（複数セル）に対して使えませんが、LEN + SUBSTITUTE のやり方だと A1:A10 といった範囲に対して一つの式でスピらせることが可能です。

一概にどっちが便利とは言えませんね。

まとめ：TEXTSPLIT は SPLIT関数の上位互換なのか？

まとめです。

EXCELの TEXTSPLIT関数は、GoogleスプレッドシートのSPLIT関数の上位互換なのか？

「否」 と mirは答えます。

上の活用例の「いきなり答える備忘録」さんの記事をよく見るとクロス結合（直積）や、クロス表 → リスト表などの事例では、「ここで TEXTSPLIT が使えれば・・・」という内容になっているのがわかります。

いずれも、TEXTSPLITが複数セル（範囲）に対して使えないってがネックになってますね。

mir としても TEXTSPLITが複数セルの一括分割に対応できないってのは結構大きな問題で、これなら縦区切なくても Arrayformulaと組み合わせて一括分割処理が出来る Googleスプレッドシートの SPLITの方が便利だわ、っていう考えです。

困るのが APIを使った IMPORTDATA関数と組み合わせて欲しいデータだけ取り出す処理です。JSONデータを取得して、そこから欲しいデータだけ取り出す際に、配列に対するSPLITは必要なんですよね。

過去にも祝日データ（日付だけ）を取得する際に Arrayformula + SPLIT を使う方法を紹介しています。

このTEXTSPLITの複数セル一括分割対応ができない問題を （部分的に）カバーするのが、次に紹介する TEXTBEFORE、TEXTAFTER となります。

いよいよ、Excel 14の新関数シリーズもゴールが見えてきました。

次回、テキスト操作系最後の2つ TEXTBEFORE、TEXTAFTER を検証していきましょう。（あと1回で終わるかな・・・）

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