Microsoft365で使えるTEXTSPLIT関数があればCSVデータ加工がラクになります!

TEXTSPLIT関数で、CSVデータをExcelの個別セルに格納できます

TEXTSPLIT関数は、2022年3月のExcelバージョンアップで追加された新関数です。

このバージョンアップでは、TEXTSPLITのほかに、TEXTBEFORE、TEXTAFTERという3つのテキスト操作関数が追加されました。

じつは、TEXTSPLITは、これまで「Googleスプレッドシートで使えるのに、Excelでは使えない関数」のひとつでした。GoogleスプレッドシートのSPLIT関数に似た機能をもち、Excel実装が心待ちにされていました。

なぜでしょう。 Excelを業務システムと連携させる時、CSV編集作業がラクになるからです。

たとえば、カンマ(,)つきCSVファイルをExcelへ読みこみ、編集してから、業務システムにインポートすることがあります。

その場合、従来なら［データ］→［区切り位置］から”区切り位置指定ウィザード”を表示して、こまかく指定していく方法が一般的でした。

しかし、TEXTSPLIT関数ならば、ウィザード操作のかわりに、引数指定だけですむのです。

構文 (Microsoftサポートページより転載)
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
text                分割するテキスト(必ず指定)
col_delimiter       列間でテキストをスピル(*)するポイント(colかrowは必ず指定)
row_delimiter      テキストを下の行に書き込むポイント(colかrowは必ず指定)
ignore_empty      空白を無視するかしないか(省略可)
match_mode    　大文字と小文字を区別するかしないか(省略可)
pad_with           結果を埋め込む値　既定値は #N/A(省略可)

*スピル(spill);英単語で「こぼれる」「あふれる」の意味。ここでは、1つの文字列を分割させ、複数のセルにわけて表示させることをさします。なお、Microsoft365などでは「1つの数式を入力することで、そこを基点に、複数セルに数式を自動入力してくれる」ことも、スピル機能とよんでいます。

ここで「Microsoft365では」と指定しているように、TEXTSPLIT関数もスピル機能も、使用できないバージョンがありますのでご注意ください(インストール版Excel2019で確認してみたところ、TEXTSPLIT関数は使用できませんでした)。

以下、Microsoft 365有料ライセンスがなくても、Microsoft ID(無料)で使用できるExcel on the web上で解説します。自分のパソコンでTEXTSPLIT関数が使えるかどうかは、fx(数式パレット)などで、この関数が選択候補に表示されるかどうかで、判定してみてください。

では、実際に”区切り位置指定ウィザード”を使わず、関数でCSVファイルをExcelに取り込んでみましょう。

まず、カンマ(,)つきCSVファイルをExcel on the Webへアップロードし、セルB1に

=TEXTSPLIT(A1,”,”)

とだけ入力してみてください。

入力したのはB1セルだけなのに、スピル機能が働き、A1セルの元データが、B:Qへと自動的に16分割されました。

関数には、ウィザードにないメリットもあります

それでも「”区切り位置指定ウィザード”でできることとおなじなら、従来どおりでいいんじゃない?」という考え方もあります。

しかし、TEXTSPLIT関数には、従来の”区切り位置指定ウィザード”にはない、多くの特長があるのです。

特長1;元レコードを保持したまま、CSVのExcel読み込みができます

”区切り位置指定ウィザード”を使ってExcelを読みこむと、そのExcel上でCSV元レコードを参照することができません。
あやまった分割をしたり、バリエーションを変え資料を複数作りたい場合には、
何回もウィザードを起動する必要があります。
しかし、関数による読みこみであれば、読みこみ元のデータは保持されたままです。

特長2;Excelへタテに読みこむこともできます

資料によっては、CSV元レコードを、行方向に配列する場合もあるでしょう。

ウィザードで想定されていないこのケースにも、TEXTSPLIT関数は対応可能です。

この場合、第2引数(列方向に区切るポイント)は空欄にしておいて、第3引数(行方向に区切るポイント)のみ指定します。

特長3;関数なので、入れ子(ネスト)にできます

TEXTSPLITもExcel関数なので、二重にも三重にも入れ子にできます。

これが、どれほど便利なことか、事例をみてみましょう。

元CSVに、なにやら日付データらしき文字列が…。

TEXTSPLIT関数は、2018-04-02という文字列を返しますが、Excel資料を作るときに必要なデータは、この文字列でしたっけ?

一方、=VALUE(TEXTSPLIT…と関数を入れ子にすると、おなじデータが43192と返ってきます。

そう!日付のシリアル値ですね。

シリアル値になってしまえば、書式をかえる(“2018年4月2日”)ことも、日数の足し算、引き算も自在です。

”区切り位置指定ウィザード”を使っても、データ表示形式をこまかく設定しなおすことはできますが、入れ子関数の簡便さ、自在さとはくらべものになりません。 無味乾燥なCSV元データを、Excelで自由自在に資料化していくためには、TEXTSPLIT関数を使ったほうが便利なことも多いのです。

まとめ

Microsoft 365で使えるTEXTSPLIT関数はCSVのExcel資料化に使える新機能がいっぱい!

2022年以前は、「CSVをSPLIT関数で加工したいために、Googleスプレッドシートを使う」という人もいたくらい、重要な新機能です。

現在はまだ、使用できるExcelバージョンが限られますが、ぜひ無料版Excel on the webを使って、ためしにCSVデータを読み込んでみてはいかがでしょうか?

(Microsoft 365有料ユーザーは、いますぐ使えます。)

とくに、関数の入れ子(ネスト)を別関数におきかえれば、応用範囲が大きく広がります。