Googleスプレッドシート SPLIT関数/TEXTJOIN関数 超応用例2
Googleスプレッドシートの SPLIT関数、TEXTJOIN関数をセットで取り上げた関数超応用例シリーズ 第２回です。
前回は基本動作や類似する機能や関数、Excelの同等関数である TEXTSPLIT関数、TEXTJOIN関数との違いなどを取り上げました。
SPLIT関数、TEXTJOIN関数がよくわかってない！という方は、当noteを読む前に先に 前回の noteをお読みください。
今回は、SPLIT関数のちょい応用例の続き、TEXTJOIN関数の応用例、さらに両方を使った超応用例など お題たっぷり で学んでいきましょう。
SPLIT関数 チョイ応用例 つづき
早速、前回紹介しきれなかったSPLIT関数のチョイ応用例の続きにチャレンジしてみましょう。
GAS（JavaScript）の split()
split(separator)
split(separator, limit)
その前に少しだけ余談を。
GAS（JavaScript）では、GoogleスプレッドシートのSPLIT関数と同じように区切り文字で文字列を分割して配列化する splitというメソッドが使えます。
区切り文字に正規表現が使えたり、limitを指定できたりと シート関数のSPLITよりも高度な設定が出来るんですが、mir的には
このように 空文字を指定して 1文字ずつ分割できるのがいいなーと思います。
残念ながら、GoogleスプレッドシートのSPLIT関数やExcelのTEXTSPLIT関数は空文字を指定して1文字ずつ区切る という処理が出来ません。
Q1. SPLIT関数で1文字ずつ区切りたい
というわけで、一つ目のお題はこれをいってみましょう。
上のようなA2のテキストを SPLIT関数を使って1文字ずつ区切って複数セルに出力したい場合は、どのような式を組めばよいでしょうか？
SPLITを使わない方法がメジャーですが、SPLITを使う方法も考えてみましょう！
↓↓
ここから回答です。
↓↓
A1. SPLIT関数で1文字ずつ区切る
回答です。
=SPLIT(REGEXREPLACE(A2,"(.)","_$1"),"_")
SPLIT関数を使えるように、先に1文字ずつの前（または後ろ）に任意の区切り文字を挿入しています。
この区切り文字を挿入する処理が出来るのが、正規表現を扱える置換関数 REGEXREPLACE関数です。
今回は REGEX系関数の回ではないんで正規表現部分の説明は簡単に済ませますが、先に A2のテキストから . （任意の1文字）を取得して、それを (.) として キャプチャグループ化。置換後を _$1 と指定することで
_お_も_て_な_し
を生成しています。文頭が _お となってしまいますが、SPLIT関数は分割した際に指定ナシなら 空白箇所は無視されて左に詰まるので、これを _ で分割ｓれば、求めていた1文字ずつセルに分割された
"お" "も" "て" "な" "し"
となります。
質問の方に記載しましたが、この1文字ずつ区切る方法としては MID関数 とSEQUENCE関数を使う方法がメジャーなんですが
=ARRAYFORMULA(MID(A2,SEQUENCE(1,LEN(A2)),1))
ただ、スピル対応の Excelと違ってGoogleスプレッドシートの場合は、MID関数の第2引数を配列処理する為に ARRAYFORMULA が必要となります。
なので、ちょっと式が長めになっちゃいます。
また、複数セルに対して縦方向もスピル処理をする場合は、
単純に範囲指定でARRAYFORMULAをつければ動く SPLIT+REGEXREPLACEでの処理に対して、MID + SEQUENCEによる処理は MAX関数を使った一工夫が必要です。
一方、REGEXREPLACE関数は 数値に対しては使えない為、こちらは&"" などで文字列化する工夫が必要ですが、MIDだと数値でも1文字を取り出せるという利点があります。
どっちも使えるといい感じですね！
Q2. 分割後の数字を文字列として 頭の0を消さないようにしたい
ExcelのTEXSPLITは分割後が 数字であった場合も文字列として扱われますが、Googleスプレッドシートの SPLITは 自動で数値化されます。
分割後にSUM関数などを組み合わせる場合は便利なんですが困るケースもあって、代表的なのは 電話番号や型番のような0始まりの数値データを 分割するケース。
上のように電話番号を - で分割した場合、分割後の頭の0が失われてしまいます。
=ARRAYFORMULA(SPLIT(A2:A5,"-"))
では、この式をアレンジして
このように分割後の数字を 文字列として保持し、頭の0を消さないようにするにはどんな式にすれば良いでしょうか？
A2セルに貼り付けるデータは
03-0405-0099
0120-00-0111
042-0123-0506
090-0987-0004こちらをコピペで利用ください。考えてみましょう！
↓↓
ここから回答です。
↓↓
A2. 分割後の数字を文字列として 頭の0を消さないようにする式
回答です。
=ARRAYFORMULA(SPLIT(REGEXREPLACE(A2:A5,"(^|-)","$1'"),"-"))
先ほどと同じく先にREGEXREPLACE関数を使って、テキストの先頭と 区切り文字の後ろに '（シングルクォート） を入れる処理をしています。
"(^|-)" ・・・ 先頭 または - を検索して
"$1'" ・・・ 先頭または - の後ろに 'を付けた文字列に置換
一旦この処理で
このように 分割された後に 数字の先頭に必ず ’ が付く状態にすることで、分割後に文字列になるようにしています。
ちなみに SPLIT関数の分割後の数値化は、セルに出力されたタイミングではなく出力前の 分割された段階で数値化されてしまっているようで、
SPLIT関数の後で文字列化しようとしても意味がありません。
分割前に 手を打つ必要があるってことです。
Q3. SPLITで空白を生成して指定したセルに文字を表示させたい
前回、SPLIT関数を使って空白配列を作る方法があると書きましたが、それを使ったお題です。
上のような A2 セルに入れた数値に合わせて a の文字を 横方向に移動させて表示するには、どんな式を組んだらよいでしょうか？
A2セルが空の場合は 何も表示させず、式は B2セルにのみ入れるとします。
考えてみましょう！
↓↓
ここから回答です。
↓↓
A3. SPLITで空白を生成して指定したセルに文字を表示させる
回答です。
=IFERROR(SPLIT(REPT(",",A2-1)&"a",",",,FALSE))
横1列の空白配列を REPT関数とSPLIT関数（第4引数 FALSE指定）で生成しています。
REPT関数は指定した文字列を第2引数の回数繰り返してくれる関数です。
REPT(繰り返すテキスト, 繰り返し回数)
こんな感じで使えます。
上のREPT広末式と同じように、今回は REPT(",",A2-1) で適当な区切り文字 , を A2セルの数値-1 回繰り返えしてから 最後に表示させたい文字 "a"を&で連結しています。
これを SPLIT関数で , で分割して、第4引数を FALSE 指定で空白を残すとすることで
このように 指定した数の 横位置に aを表示させています。
A2セルが空だった場合は、REPTの第2引数が -1となり エラーを返すので、
これをIFERRO関数で空白を返すように設定すれば完成です。
ちなみに SPLIT関数は 第1引数が 区切り文字のみで構成されたテキストだった場合、つまり分割した後になにも残らない場合は、第4引数をFALSE指定しないと 分割されず、そのままの値が返ります。
上のお題は空白セルを使ってますが、REPT関数 + SPLIT関数、これに 条件付き書式を組み合わせると
=ARRAYFORMULA(SPLIT(REPT("■,",B3:B5),","))
こんな式をC3に入れるだけで、あとは 「空白ではない」というシンプルな条件付き書式で 横棒グラフ風にすることが出来ます。
Q4. SPLIT関数で ExcelのTEXTSPLITみたいに縦・横の両方に分割したい。
SPLIT関数応用例の最後です。このお題は非常によく使う式のパターンなので、しっかり理解しましょう。
上のA2セルに入ったデータを この構成のまま 分割してセルに展開したい。つまり、,(カンマ)は横に区切って、改行は縦に区切りたい。
C2 にはどんな式をいれれば良いでしょうか？
これは ExcelのTEXTSPLIT関数だと第3引数の縦の区切り文字を指定すれば、一発で出来ます。
しかし、GoogleスプレッドシートのSPLIT関数には縦区切りがありません。では、どうするか？というお題です。
A2セルの中に入れるデータ
"チームA,田中,鈴木,山田
チームB,相沢,古賀
チームC,松野,工藤,日野,川端"考えてみましょう！
↓↓
ここから回答です。
↓↓
A4. SPLIT関数で ExcelのTEXTSPLITみたいに縦・横の両方に分割する。
回答です。
=ARRAYFORMULA(SPLIT(TOCOL(SPLIT(A2,CHAR(10))),","))
SPLITを縦、横と順番に行う ダブルスプリット（筋トレ用語ではありません）です！
流れとしては 前回の noteでも登場したCHAR(10)を使った改行での区切り、TOCOL関数による区切った後の縦への変換、そしてARRAYFORMULAによる 複数行の一括分割の組み合わせです。
1.縦区切り（改行）でSPLITして横に展開
2.分割したデータをTOCOLで縦並びに変更
3.最後にARRAYFORMULAで縦並びの配列の要素を カンマでSPLIT
以前 TEXTJOINのnoteでも使ったお題ですが、よく使う定番処理なので再度掲載しました。
TEXTJOIN関数 チョイ応用例
続いて TEXTJOIN関数のチョイ応用例を見ていきましょう。残念ながらSPLIT関数ほど例は多くありません。
Q5. TEXTJOINでセル範囲を横はカンマ、縦は改行で連結して1セルに入れたい
先ほどの SPLIT関数の応用例の Q4の逆処理です。
セル範囲のCSVデータ化というか、上のようにB2:F4のセル範囲を横はカンマで連結、縦は改行で連結して H2の1セルに格納したい場合は、どのような式を組めばよいでしょうか？
B2:F4のサンプルデータです ↓
チームA 田中 鈴木 山田
チームB 相沢 古賀
チームC 松野 工藤 日野 川端考えてみましょう！
↓↓
ここから回答です。
↓↓
A5. TEXTJOINでセル範囲を横はカンマ、縦は改行で連結して1セルに入れる式
回答です。
=TEXTJOIN(CHAR(10),TRUE,BYROW(B2:F4,LAMBDA(r,TEXTJOIN(",",TRUE,r))))
前回も書きましたが、SPLITはARRAYFORMULAと組み合わせて活用できる関数ですが、TEXTJOINはARRAYFORMULAが効かない関数です。
行毎にTEXTJOINしたい！列毎にTEXTJOINしたい！
以前はこれらに対応するのは結構難しかったんですが、LAMBDAヘルパー関数の BYROWやBYCOLの登場で、かなり簡単に処理できるようになりました。
考え方はシンプルで まずは行毎に カンマ区切りで連結
=BYROW(B2:F4,LAMBDA(r,TEXTJOIN(",",TRUE,r)))
これをさらに 改行 CHAR(10) を区切り文字としてTEXTJOINすればOK。
=TEXTJOIN(CHAR(10),TRUE,BYROW(B2:F4,LAMBDA(r,TEXTJOIN(",",TRUE,r))))
トンカツの二度揚げならぬ、TEXTJOINの二度がけをしているわけです。
BYROW、BYCOLの使い方については過去noteを参考に。
Q6. 検索結果が複数ヒットした時は カンマ区切りで1セルに表示したい
難易度的にはたいしたことないんですが、TEXTJOINを実務で活用するケースのお題をいってみましょう。
範囲 A:Dに 氏名（A列）、住所（D）列が入った名簿データがあったとします。
F2:F5 の都道府県名でD列を検索して、D列の住所がその都道府県を含む同じ行のA列の氏名を取得したい。もし複数人がヒットする場合は、1セルにカンマで繋いで入れたい。
これをG2セルに1つだけ式を入れて実現するにはどうしたらいいでしょうか？
先ほどと同じ TEXTJOIN + 新関数 で解決できます。
データはこちら。
氏名 性別 血液型 住所 都道府県 住んでる人
中濱 賢 男 A 東京都府中市南町3-1-3 東京都
工藤 真那 女 B 大阪府大阪市住吉区長居東1-5-18 北海道
早川 隆則 男 A 福岡県北九州市小倉北区片野3-3-3 大阪府
佐々木 達也 男 AB 大阪府大阪市平野区加美南1-3-4 埼玉県
高杉 恭子 女 B 東京都豊島区長崎3-2-5ゾフィー316
安宅 佐知子 女 B 大阪府大阪市生野区田島2-3-3
佐藤 彩子 女 A 東京都渋谷区恵比寿3-3-8
松井 裕美 女 AB 東京都調布市深大寺北町2-4-19
岡田 敦 男 B 愛知県名古屋市東区東大曽根町中2-2-9
佐藤 裕子 女 A 神奈川県川崎市多摩区宿河原4-2-4
大内 卓也 男 A 大阪府大阪市浪速区日本橋1-5-15パインフィールド306
菅原 強 男 AB 埼玉県さいたま市浦和区岸町4-3-7
澤 孝行 男 A 埼玉県川口市安行出羽2-3-7グランステイツ204
安藤 直之 男 A 福岡県筑紫野市美しが丘南2-1-4ニチモグリーンタウン109
山本 吉史 男 A 宮城県仙台市青葉区一番町1-3-108
加瀬 香織 女 AB 沖縄県名護市宮里3-4-6ペルル515
奥村 智洋 男 B 東京都世田谷区代沢3-2-10
大城 はる 女 A 東京都小平市小川東町3-5-15
梅野 健治 男 O 埼玉県さいたま市南区文蔵3-2-7 考えてみましょう！
↓↓
ここから回答です。
↓↓
A6. 検索結果が複数ヒットした時は カンマ区切りで1セルに表示する式
回答です。
=MAP(F2:F5,LAMBDA(_v,TEXTJOIN(",",TRUE,FILTER(A:A,REGEXMATCH(D:D,_v)))))
このお題は 条件に一致するものを全て取得したいので、XLOOKUPなどのLOOKUP系ではなくFILTER関数を使うべきケースです。
このFILTER関数の結果を1セルに収める為にTEXTJOINを活用します。
=TEXTJOIN(",",TRUE,FILTER(A:A,REGEXMATCH(D:D,F2)))
含むを条件とするFILTERの条件式は 今回は
REGEXMATCH(D:D,F2)
としていますが、
FIND(F2,D:D)
このように FINDを使ってもよいです。
「含む」を条件としていますが、COUNTIFのワイルドカードを使う場面ではありません。
どの関数を組み合わせるべきか？がイメージできるようになると、数式組むのが圧倒的に楽にスピーディーになります。
で、最後は 1セルずつ（セル毎に） FILTERしてTEXTJOINしてを繰り返したいので、MAP関数を組み合わせればOK。
これは LAMBDAヘルパー関数の登場で、やっと一つの式で処理出来るようになりました。
LAMBDAヘルパー関数は慣れていない人も多いですが、素直に記述すれば動くんで、慣れれば割と簡単です。
Q7. QUERY関数の 長いSELECT句を TEXTJOINで生成したい
他の関数の中でTEXTJOINを使うケースを紹介しましょう。
上のような別のスプレッドシートのデータをIMPORTRANGEで取得してQUERY関数で絞り込んでいるデータがあったとします。
現在は
=QUERY(IMPORTRANGE(N1,"名簿!A:L"),"where Col1 is not null")
こんなQUERY式を使ってますが、これを12列目の血液型が O型の人のみに絞り込んで、表示させるのは12列目を除いた 1～11列目としたい。
こんな時どうしたら良いでしょうか？
QUERY関数は 1～11列目といった指定や 12列目を除くといった書き方が出来ないので、愚直に SELECT句で出力する列を
=QUERY(IMPORTRANGE(N1,"名簿!A:L"),"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11 where Col12 ='O'")
このように ひたすら11個 Col1,Col2,… と指定する必要があります。
この面倒な個所を TEXTJOINを使って生成するとしたら、どんな式を組めばよいでしょうか？
つまり Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11
という文字列を生成する式を作ろう！ってお題です。
考えてみましょう！
↓↓
ここから回答です。
↓↓
A7. QUERY関数の 長いSELECT句を TEXTJOINで生成する
回答です。2パターン用意しました。
"Col"&TEXTJOIN(",Col",TRUE,SEQUENCE(11))
ARRAYFORMULA(TEXTJOIN(",",TRUE,TEXT(SEQUENCE(11),"Col0")))
上の方が記述は短いですが、他の部分を配列処理する為に全体にARRAYFORMULAが効かせている式内なら 下も TEXTJOIN(",",TRUE,TEXT(SEQUENCE(11),"Col0") だけで動くんで アリですね。
QUERY関数と組み合わせて
=QUERY(IMPORTRANGE(N1,"名簿!A:L"),"select Col"&TEXTJOIN(",Col",TRUE,SEQUENCE(11))&" where Col12 ='O'")
こんな感じで長いSELECT句の文字列を生成できるってことです。
Q8. 指定したセル範囲の 値を含むセルだけに絞りこみたい
もう1つ他の関数の中でTEXTJOINをよく使うケースを紹介しましょう。
A列のテキストデータで、E2:Fの検索キーのいずれかを含むものだけを絞り込んで出力したい場合、C1セルにどんな式を入れれば良いでしょうか？
FILTER関数を使うお題ですが、その条件式の部分をどう組み立てるか？がポイントです。
データはこちら
今日は田中と松本と会った 検索キー
明日は高見沢と食事だ 田中 前田
月1くらいで馬場と遊んでる 佐藤 川上
鈴木は予選落ちで～す。 鈴木 馬場
実は川田は女だったんだ。
それは前田と出会う前のお話 考えてみましょう！
↓↓
ここから回答です。
↓↓
A8. 指定したセル範囲の 値を含むセルだけに絞りこむ式
回答です。
=FILTER(A:A,REGEXMATCH(A:A,TEXTJOIN("|",TRUE,E2:F)))
AまたはBまたはCまたは… を含むという記述は、正規表現の | パイプを使って表現します。パイプで区切ると正規表現で「または」と出来ることは、検索と置換の回のnoteで触れましたね。
この
田中|前田|佐藤|川上|鈴木|馬場
・・・田中 または 前田 または 佐藤 または 川上 または 鈴木 または 馬場 を含む
という記述はをTEXTJOIN関数を使って生成することができます。
=TEXTJOIN("|",TRUE,E2:F)
TEXTJOINは空白を無視してくれるのと、複数行複数列の範囲でも対応できるのが良いですね。
これを REGEXMATCH関数で 評価することで、A列がE2:Fの検索キーワードを含む場合 TRUEを返し FILTER関数で絞り込むことが出来るわけです。
以上、TEXTJOINのチョイ応用例3選でした。
SPLIT + TEXTJOIN を組み合わせた超応用例
最後は SPLIT関数とTEXTJOINを組み合わせた超応用例に挑戦してみましょう。
対になる関数といえるこの2つは、一つの式の中で両方を使うケースがあります。
Q9. セル内の改行入りテキストの無駄な改行を削除したい
まずは簡単だけど、こんなことにも使えるんだってお題を。
B1セルに改行入りのテキストが入ってますが、文頭や途中、文末に無駄な改行（改行だけの空の行）が入っています。
これを無駄な改行（文頭や文末、連続の改行）を無くしたB5セルの状態としたい場合、どんな式をいれればよいでしょうか？
B1セルのデータはこちら
テキスト - 分割するテキストです。
区切り文字 - テキストを分割するために使用する文字です。
デフォルトでは、区切り文字の各文字が個々に検証されます。
考えてみましょう！
↓↓
ここから回答です。
↓↓
A9.セル内の改行入りテキストの無駄な改行を削除する
回答です。
無駄な改行削除は REGEXREPLACE関数で 正規表現を使う方法もあるんですが、実はSPLITとTEXTJOINを組み合わせる方法でも処理可能です。
=TEXTJOIN(CHAR(10),TRUE,SPLIT(B1,CHAR(10)))
しかも、シンプルに改行で一旦SPLITしてからTEXTJOINで改行で再結合するだけ。
SPLIT関数で改行で分割した時点で、無駄な改行で発生する空白は無視されるので、
あとは改行を区切り文字として再度1つにまとめればOKですね。
Q10. 複数行複数列のセル範囲を区切り文字で区切って展開したい
B2:D4 の各セルに カンマで区切られた 1つ以上のフルーツ名が入っています。この複数列のデータを ,（カンマ）で分割して 横に展開したいというお題です。
F2セルにどんな式をいれたらよいでしょうか？
考えてみましょう！
↓↓
ここから回答です。
↓↓
A10.複数行複数列のセル範囲を区切り文字で区切って展開する
回答です。
=BYROW(B2:D4,LAMBDA(r,SPLIT(TEXTJOIN(",",TRUE,r),",")))
まず、このままSPLITでの分割は出来ません。
SPLIT関数が横方向に展開する関数である為、複数列の配列を対象とした場合、1列目（今回の場合はB列）のみ分割処理がされ、それより右の列は無視されてしまいます。
というわけで発想を変える必要があります。
最終的には分割するんですが、先に一度連結をすると考えましょう。行単位で連結をしておくと後が楽なんで、先ほども登場したBYROW関数の出番ですね。
これで1列のデータとなりました。この結果に対してSPLITをしてもいいんですが、それだとARRAYFORMULAが必要になります。
今回は SPLITも BYROW内で処理してしまった方がARRAYFOMRULAを付ける必要がなく シンプルでしょう。
=BYROW(B2:D4,LAMBDA(r,SPLIT(TEXTJOIN(",",TRUE,r),",")))
行単位で一度連結してからSPLITで分割、これを繰り返すという処理です。
ちなみに今やあまり必要はありませんが、BYROWが登場する以前は
=ARRAYFORMULA(SPLIT(TOCOL(SPLIT(TEXTJOIN(",",TRUE,{B2:D4,IF(SEQUENCE(ROWS(B2:D4)),"_")}),"_")),","))
こんな式で対応していました。
IF(SEQUENCE(ROWS(B2:D4)),"_")
この式で 対象範囲と同じ行数の 縦区切り文字 _ の1列の配列を生成し、中カッコで 対象範囲と横結合します。
この _ で改行ポイントをマーキングしてると思ってください。
あとは Q4 でやった
_で 縦分割
,で 横分割
というダブルスプリットですね。
途中段階では余計な カンマが残ってますが、これは最後のSPLITの空白無視でやっつけることが出来ます。これが便利なんですよね。
BYROW式に比べるとちょっと複雑ですが、昔はこの処理を使うことが結構ありました。
Q11. 1列の チーム名と点数が入ったデータから チームごとの合計を集計したい
最後のお題です。難易度が少し上がる超応用例にチャレンジしてみましょう。
A列に チーム名とそのチームが取得した点数が1列に並んでいるという扱いにくいデータがあります。
これをC2セルに数式を入れて、チームごとの合計点の集計表にしたい。これはどう対応すればよいでしょうか？（C1:D1のタイトル行は入力されているとする）
A列はデータの途中に空白行はなく、チームは必ず「チーム」という文字から始まる、またチームは重複で登場しない。チームや点数が増えることを考慮して範囲は A:Aで取得する。こんな条件とします。
A列のデータ ↓
チームA
100
80
50
100
チームB
50
40
100
チームC
30
20
50
チームD
100
200
チームE
300
100
500チャレンジしてみましょう！
↓↓
ここから回答です。
↓↓
A11.1列の チーム名と点数が入ったデータから チームごとの合計を集計する
回答です。
=ARRAYFORMULA(BYROW(SPLIT(TOCOL(SPLIT(TEXTJOIN(",",TRUE,SUBSTITUTE(A:A,"チーム","_チーム")),"_")),","), LAMBDA(r,{INDEX(r,,1),SUM(r)})))
他のやり方もあるんですが、今回のテーマである SPLITとTEXTJOINを使っても割と短く書けるかなと思います。
解説していきましょう。
まず、SPLITとTEXTJOINの縦横区切りにもっていく為に縦区切りを仕込んでいきます。
Q10 の別解で出てきた改行ポイントのマーキングですね。
ただし今回は縦区切り位置は変動するので、IF関数でチーム名（セルにチームという値が含まれていれば）の前に 縦区切り文字 "_" を付けるとします。
それが
SUBSTITUTE(A:A,"チーム","_チーム")
この式です。
IFで条件分岐したくなるところですが、ここはSUBSTITUTE関数で置換してしまうのがシンプルかなと思います。
REGEXEXPLACE関数は強力なんですが、対象が数値だとエラーを返すのでこのケースはSUBSTITUTEを使うのが良さそうです。
「チーム特攻野郎Aチーム」みたいな チーム名に「チーム」という文字が再度登場する懸念がある場合は
SUBSTITUTE(A:A,"チーム","_チーム",1)
と第4引数で出現回数を1に制限するとより丁寧かもしれません。
これを一旦TEXTJOINで カンマ区切りで連結。この時点でA:Aのデータが入ってる行以降の空白が全て消えます。
ここからSPLITで縦、横に区切れば
=ARRAYFORMULA(SPLIT(TOCOL(SPLIT(TEXTJOIN(",",TRUE,SUBSTITUTE(A:A,"チーム","_チーム")),"_")),","))
チームごとに横並びに集計した表を生成できます。
これ作るのも他の関数でやろうとすると結構大変なんですよね。
折り返しの数が決まってれば WRAPROWS関数で対応できますが、これは所謂 折り返す数が行によって変化するWRAPROWS関数って感じですね。
最後に行毎に集計なんで BYROWを使って
{INDEX(r,,1),SUM(r)}
これで 行毎に 1つ目のチーム名をINDEXで取得して、SUMで合計を算出して 横に連結して返す、という処理を繰り返します。
SUM(r) は チーム名 も範囲に含んでしまっていますが、SUM関数は文字列が含まれていても無視するので気にしなくてOKです。
=ARRAYFORMULA(BYROW(SPLIT(TOCOL(SPLIT(TEXTJOIN(",",TRUE,SUBSTITUTE(A:A,"チーム","_チーム")),"_")),","), LAMBDA(r,{INDEX(r,,1),SUM(r)})))
結構複雑な処理ですが、LET関数を挟まずに式が書ける（無駄に同じ処理が登場しない）ってのが凄いですね。
この手の集計表生成でも SPLIT + TEXTJOINが使えるケースがある！ってのを覚えておくとよいでしょう。
次回は Googleフォームでの活用例を
今回は久々のお題ラッシュでした。
11の SPLIT関数、TEXTJOIN関数の応用例をみてきましたが、いかがだったでしょうか？使えそうな活用例があれば幸いです。
次回、この流れで Googleフォームで使える SPLIT関数（TEXTJOINも？）について紹介したいと思います。
たまに QAサイトで見かける Googleフォームの「アップロード」質問で入れた画像をスプレッドシートに自動表示させる方法も！
この記事が気に入ったらサポートをしてみませんか？