365・LibreOfficeなどに関するメモ2022-12-09(Excel)TEXTSPLIT関数をスピル(複数のテキストに適用)させるExcelExcelのTEXTSPLIT関数は強力ですが、複数のセル(配列)を対象とした場合に自然にスピルした結果が得られません。その現象と対策についてです。TEXTSPLIT関数の第1引数として複数のセルを与えた場合、それぞれのセルの文字列を区切り文字で分割した最初の項目しか返ってきません。対策としては文字列を一旦TEXTJOIN関数で連結する方法があります。簡潔ですが合計文字数が長すぎるとエラーになります。REDUCE/LAMBDA関数を使う方法もありますが若干式が複雑です。画像ではTEXTSPLIT関数を使い、B3:B6の範囲の文字列を区切り文字「☆」で区切った結果を一括して取得しようとしています。結果は期待と異なるものとなります。確かに4つのセルに対する結果は返っているものの、それぞれのセルを「☆」で区切った最初の項目しか得られていません。この場合は式をコピーすれば済む話ですが、アンピボットのような操作が面倒になるのが残念なところです。D3セル=TEXTSPLIT(B3:B6,"☆")そこで以下では他の関数を組み合わせた解決法を試してみます。TEXTJOIN関数を使う方法TEXTJOIN関数を利用して対象となる文字列に行区切り文字を加えて連結し、TEXTSPLIT関数で再度分割する方法です。簡潔ではあるものの連結した文字列が数万字に達するとエラーになるのが欠点です。行区切り文字には文字列中で使われていない文字を選択する必要があります。D3セル=TEXTSPLIT(TEXTJOIN("_",FALSE,B3:B6),"☆","_",,,"")「TEXTJOIN("_",FALSE,B3:B6)」によりB3:B6の文字列を、区切り文字「_」を挿みつつ連結しています。TEXTSPLIT関数により、「☆」を列区切り文字とし、「_」を行区切り文字として分割しています。TEXTJOIN関数の第2引数(空のセルを無視)をFALSEにしているためB3:B6の中に空白セルがあっても行を詰めません。ここは欲しい結果によって判断が必要なところです。列区切り文字のほかに行区切り文字(ここでは「_」)がある場合、連結時の区切り文字をそれに合わせておけば自然にスタックした結果を得ることができます。式の内容は1つ上のものと全く同じです。REDUCE/LAMBDA関数を使う方法LAMBDA関数を使い、各セルごとに分割を実行した結果を得る方法です。ヘルパー関数はMAPかBYROWが適当に見えますがエラーになるので、REDUCE/VSTACK関数を使い出力を積み重ねていきます。無用な初期配列を設定して削除する作業が生じるのが残念な点です。対象となるセルに空白セルや空文字列が含まれていると全体が1つのエラーになります。FILTER関数等で対策は可能ですが不必要に長くなりそうなので省略します。D3セル=DROP(IFNA(REDUCE("",B3:B6,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"☆")))),""),1)REDUCE/LAMBDA関数を使い初期配列となる空文字にa、処理対象となる配列B3:B6にbという名前を付けています。あとはbの1セルごとにTEXTSPLITを実行してVSTACK関数によりaに順次スタックしていきます。各行の列数の違いにより#N/Aエラーが生じるのでIFNA関数で空文字列に変換し、最後にDROP関数で先頭行(空文字列だけ)を削除しています。列区切り文字のほかに行区切り文字(ここでは「_」)がある場合にも対応できます。365はデスクトップ版、LibreOfficeは記事作成時の安定バージョンを用いた結果を示しています。サイトポリシー等についてはこちらをご覧ください。(1)検索スポンサーリンク引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。