ここで解説する TEXTSPLIT関数 は、ProPlusに追加された関数です。Excel 2016/2019/2021では使用できませんのでご注意ください。また、本稿執筆時点(2022/3/20)では、まだInSider Programに実装されただけですので、製品版で使えるようになるには、まだ少し時間がかかると思います。なお、この関数については、YouTubeの動画でも解説しています。ぜひ、ご覧ください。また、この関数では「スピル」という機能が使われています。スピルに関しては「Excel 2016レビュー[Excelの使い方が激変する「スピル」]」をご覧ください。
Excelの「区切り位置」という機能をご存じですか?ご存じない方は、がんばって勉強してください。この「区切り位置」と似たようなことを関数で実現できるのがTEXTSPLIT関数です。"似たようなこと"っていうのは、機能的に劣るということではありません。逆です。「区切り位置」機能にはない、もっとスゴイことも可能です。もうね、待ちに待っていました!余談ですけど、Excelのスピルという仕組みは、早くからGoogle スプレッドシートには実装されていました。オートフィルタを行うFILTER関数にしても、並べ替えを行うSORT関数にしても、Googleスプレッドシートには存在していたんです。別に、先に実装した方がスゴイってことではありませんけど、Excelのスピルなどは"後追い感"がありました。しかし、後追いなのに、GoogleスプレッドシートのSPLIT関数がありませんでした。どうせなら、FILTER関数などと同時に実装して欲しかったのですけど、なぜかこれだけありません。ガッカリです。って思っていたら、とうとう実装されました!もう一度言います。待ちに待っていました!
できることは、メチャクチャたくさんあるんですけど、最も簡単な使い方を一つご覧に入れると、こんなことができます。
スピってます。このTEXTSPLIT関数も、"スピルありき"の関数です。世間では未だに「スピルがよく分からない」みたいに言う人もいますけど、もうね、そんなこと言ってる時代じゃありません。
さて、さっそく引数を見てみましょう。
TEXTSPLIT(text,col_delimiter,row_delimiter,ignore_empty,pad_with)
ベータ版みたいなものですから、まだ引数の表記が英語のままです。ここでは、解説しやすいよう、私なりに日本語化してみます。
TEXTSPLIT(元の文字列,列の区切り,行の区切り,空欄を無視する,代替文字)
必ず指定しなければいけないのは、最初の引数「元の文字列」と、2つめ「列の区切り」または3つめ「行の区切り」のいずれかです。もちろん、「列の区切り」と「行の区切り」を両方指定することも可能です。では、引数を順番に解説します。最初の「元の文字列」は分かりますね。区切る前の文字列です。さて、"区切り文字"の指定として「列の区切り」と「行の区切り」という2種類が用意されています。そう!このTEXTSPLIT関数は、行(下)方向に分割することができるんです。
今までは、こうした変換って「取得と変換(Power Query)」を使わないとできませんでした。でも、もうPower Queryなんか使わなくても楽勝です。さらに、2つの区切りを両方指定すると、次のようなことも可能です。
もちろん、区切り文字として"セル内改行"も指定できます。
4つめの引数「空欄を無視する」は、区切り文字が連続したとき、そこを空欄として表示するかどうかを決めます。結果を見た方が分かりやすいでしょう。
引数を省略すると、FALSEとみなされます。さて、最後の引数「代替文字」は、ちょっと分かりにくいです。先のように「列の区切り」と「行の区切り」を両方指定して、縦横に分割するケースを想定してください。
上図では、すべての行に2項目ずつの値が存在していますので、どのセルも何らかの値が表示されています。では、行によって項目の数に違いがあったらどうでしょう。
これは、先の引数「空欄を無視する」とは事情が異なります。区切り文字が連続しているのではなく、そもそも項目数が足りないので"区切れない"んです。こんなとき、標準では「#N/A」エラーになります。「#N/A」というのは、値がないという意味のエラーです。こんなとき、「#N/A」ではなく、何かしら別の値を代入する指定が、引数「代替文字」です。
区切りには、複数の文字や記号を指定できます。複数の区切りを指定するときは、それらを配列形式で記述します。
数式内に配列を直接記述するときは、中括弧{}で囲みます。上図では、"-"と"/"と" "の3つを指定しています。あるいは、次のような使い方もできますね。
TEXTSPLIT関数の結果は配列です。
上図でしたら、セルA1の文字列「ABC-100-DEF-200」を"-"で分割した結果の、4要素すべてを返します。では、特定の要素だけを取り出すにはどうしたらいいでしょう。もし、配列内の要素を1つだけ取り出すのでしたら、INDEX関数で一発です。
ただし、INDEX関数で取り出せる要素は1つだけです。このケースで、1つめの要素"ABC"と、3つめの要素"DEF"だけを取り出すのでしたら、これも今回新しく追加されたCHOOSECOLS関数を使うといいでしょう。
取り出す順序を入れ替えることも可能です。
CHOOSECOLS関数については、別のコンテンツで解説します。
なんか楽しくなってきたのでw もう少しレアなケースをやってみましょう。
"いくつめの要素"のように、位置で指定するのではなく、ある条件に一致した要素だけを取り出してみましょう。上図では、"-"で分割した結果が「3文字 2文字 3文字 4文字」となります。この中から、3文字の要素だけを取り出します。パッと思いついたのはFILTER関数です。
いやぁ~、FILTER関数って超絶マジで便利すぎます。最強です。まるでエスカノールのようです。ただ、上図の式では「TEXTSPLIT(A1,"-")」が2回出てきて長くなります。こんなときは、LET関数で「TEXTSPLIT(A1,"-")」の結果を変数に入れておくといいかも。
じゃ、今度は特定の要素を探してみましょう。TEXTSPLIT関数の結果は配列なのですから、その中をMATCH関数で調べられそうです。やってみます。
いけますね~。ってことは、こんなこともできそうです。
どこにあるか分からない"CD"の後ろ(次)を取り出します。MATCH関数で、"CD"が何番目の要素か分かりますので、それに1を加えればいいです。
このケースでしたら、"みんな大好き"VLOOKUP関数でもいけますね。
こっちの方がシンプルかな。
いずれにしましても、TEXTSPLIT関数は、いろいろな場面で活躍しそうです。