root 
Excel関数は、日々進化しています。
事務職の人が業務に使うExcel関数は、おそらく10か20種類くらいでしょう。
しかし、普段は使わなくても、「こういう新関数があるんだ〜」と知っておくと、意外なところで役立つことがあります。 そんな新関数の代表例として、今回はTEXTSPLIT関数についてご紹介します。
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有料ユーザーは、いますぐ使えます。)
とくに、関数の入れ子(ネスト)を別関数におきかえれば、応用範囲が大きく広がります。
ChatGPTなどの生成AIブームで、「プロンプト力(事前に作成する指示・命令資料の作成能力)」が、成果物のクオリティ良し悪しに大きく影響することがわかってきました。
使用頻度はそれほど高くないExcel関数でも、機能だけはおぼえておけば、資料作成力が格段に上がるかもしれませんよ。
