「Googleスプレッドシートから見た！」Excel 14の新関数 -9 TEXTSPLIT 応用編
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 で縦方向に変換。
ここまでは 縦区切りのみのケースと一緒ですね。
この縦方向の配列に対して、横区切り文字の ： 、 の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))
これも TEXTJOINとTEXTSPLITで 処理する方法があります。
列ごとの 空白上詰めよりも、行ごとの空白左詰めの方が簡単なんで、先にそっちから。
=TEXTSPLIT(TEXTJOIN(",",TRUE,EXPAND(A2:Q5,,COLUMNS(A2:Q5)+1,"_")),",","_",TRUE,,"")
少し解説しておきましょう。
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,,""))
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回で終わるかな・・・）