Microsoft Excel の新しい TEXTSPLIT() 関数を使う

Microsoft Excel の新しい TEXTSPLIT() 関数を使う | TechRepublic

コンピュータ画面上の Microsoft Excel スプレッドシート。 — 画像: wachiwit/Adobe Stock

Microsoft Excelでは、文字列の解析、連結、あるいは文字列の途中から文字を返すなど、文字列の操作は日常茶飯事です。同じセルに複数の名前を持つデータが含まれることもあります。関数を使ってそれらを独立したエンティティに解析するには、これまでは複雑な式が必要でした。しかし、ExcelにはPower Query、テキストの区切り、さらにはフラッシュフィルといった使いやすい機能が新たに搭載されました。そして今、その機能にTEXTSPLIT()が加わりました。

参照: Google Workspace vs. Microsoft 365: チェックリスト付き比較分析 (TechRepublic Premium)

このチュートリアルでは、Excelの新しい関数TEXTSPLIT()を解説します。この関数は、単に文字を解析するだけでなく、それ以上の機能を備えていることがわかります。

TEXTSPLIT() は、Web 版 Excel を含む Microsoft 365 サブスクライバーでご利用いただけます。以前のバージョンをご利用の場合は、[データ] タブの [区切り位置] をご利用ください。TEXTSPLIT() のすべての機能をサポートしているわけではありませんが、まずは試してみる価値はあります。Microsoft 365 をご利用で、この機能が利用できない場合は、管理者にお問い合わせください。

このチュートリアルの Microsoft Excel デモファイルをダウンロードできます。

ExcelでTEXTSPLIT()を使用する方法

Microsoftは昨年、Microsoft Excelに新しい文字列関数TEXTAFTER()、TEXTBEFORE()、TEXTJOIN()、TEXTSPLIT()を追加することに尽力しました。これらはすべて文字列管理において共通しています。TEXTSPLIT()は、列と行の区切り文字を使って文字列を解析（「分割」）します。これは、Microsoftの「Text To Columns」機能が列に対して行うのとほぼ同じですが、関数内で行われます。

TEXTSPLIT() と Text To Columns にはいくつかの違いがあります。

Text To Columns は、元の値と同じセルに最初に解析された値を返すことで、元のデータを上書きします。
Text To Columns は、列区切り文字のみをサポートしています。行区切り文字では全く機能しません。

次に、次の構文を使用するこの新しいテキスト関数について学習します。

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

これらの議論の説明は次のとおりです。

text: この必須引数は、分割するテキストです。
col_delimiter: この必須引数は、列間の分割を開始するポイントを示すテキストです。
row_delimiter: このテキストは、行の分割を開始するポイントを示します。
ignore_empty: TRUE（デフォルト）の場合、この引数は空のセルを無視します。FALSEの場合、連続する2つの区切り文字がある場合に空のセルを作成します。
match_mode: デフォルト値の0は、区切り文字の照合時に大文字と小文字を区別します。大文字と小文字を区別しない場合は1を使用します。
pad_with: 結果を埋める値。デフォルトは #N/A です。

注目すべきことに、この関数は配列として表現された複数の区切り文字を扱うことができます。例えば、関数 TEXTSPLIT(A1,{“,” , “.”}) は、A1 内の文字列をカンマまたはピリオドで分割します。2 つの区切り文字の間にあるスペース文字は必須ではありませんが、引数を読みやすくするために必要です。

それでは、いくつかの簡単な例に移りましょう。

Excel の不規則なデータセットで TEXTSPLIT() を使用する方法

従来の関数では、特にデータに一貫性がない場合、文字列の解析は困難でした。TEXTSPLIT() のおかげで、この作業ははるかに簡単になりました。図Aに示すような単純なデータセットを考えてみましょう。C3の関数は

=TEXTSPLIT(B3,"/")

残りのセルにコピーすると、ほとんどの値は期待どおりに、つまり / 文字で簡単に解析されます。この関数は動的配列関数です。つまり、必要な数の列にまたがって展開されますが、関数は1つしかなく、それはC3にあります。

図A

TEXTSPLIT() 関数を使用します。 — TEXTSPLIT() 関数を使用する。

この関数は区切り文字の前後の文字数を気にしないことに注意してください。しかし、B8の値には区切り文字がなく、C9の値には / 文字の代わりにスペースが含まれています。どちらの場合も、問題はデータの不整合であり、注意が必要ですが、この関数はその情報を返しません。代わりに、元の値を返します。

まず考慮すべきは区切り文字です。値に両方の文字が使われている可能性があります。その場合、関数は

=TEXTSPLIT(B3,{"/"," "})

E3に格納され、残りのセルにコピーされた文字列は、図 Bに示すように両方の区切り文字を処理します。つまり、B8の値だけを処理する必要があります。私の知る限り、TEXTSPLIT()には区切り文字のない値を処理する引数はありません。ただし、空のセルは良い手がかりになるでしょう。

図B

col_delimiter 引数の配列動作を使用すると、複数の区切り文字に対応できます。

ExcelでTEXTSPLIT()を使用して日付を解析する方法

もう一つの一般的な解析タスクは日付です。日付は通常、日付全体として入力しますが、その構成要素も必要です。より複雑な日付関数を使用する代わりに、図Cに示すようにTEXTSPLIT()を使用します。ローカルシステムは米国の地域設定を使用しているため、米国以外の地域にお住まいの場合は、TEXT()関数で書式文字列を指定する際に、形式の違いを考慮する必要があります。

図C

C3の関数を残りのセルにコピーします

=TEXTSPLIT(TEXT(B3,"m/d/yyyy"), "/")

TEXT() 関数を使用して、日付を月、日、年で分割します。他にも以下のような例があります。

"mmm/dd/yyyy"2022年10月13日に戻る
"mmm/dd/yyyy"2022年10月の返品
"yyyy"2022年のリターン
"ddd"木曜日を返す

これらはほんの一部です。可能性を制限する唯一の要因は、これらの日付形式に関する知識です。

この使い方はシンプルですが、TEXT() 関数を追加する必要があるため、直感的ではありません。他に思いつかないかもしれない使い方を見てみましょう。

ExcelでTEXTSPLIT()を使用するときに文字を無視する方法

一貫性のないデータを受け取った場合、別の解析問題が発生します。例えば、図Dに示されている名前の中には、タイトルが付いているものと付いていないものがあります。この問題については異論はありませんが、少し工夫すれば解決できます。タイトルを区切り文字の一部として指定する方法もありますが、複数のタイトルを無視する必要がある場合は、この方法も少し複雑になります。タイトルは配列として入力する必要があります。

図D

C3の関数を残りのセルにコピーします

=TEXTSPLIT(B3,{" ","Ms.","Mrs."},,TRUE)

完璧ではありませんが、ほぼ完璧です。2番目のレコードの敬称「Dr.」とミドルネームの頭文字は特別な問題であり、何らかの方法で対処する必要があります。3番目のレコードにはミドルネームが含まれています。手がかりとなるのは、列EとFに予期しない値があることです。解析前にReplaceタスクを実行して敬称を削除することを検討してください。TEXTSPLIT()だけでは解決できない状況に遭遇することもあります。しかし、結果は通常、元の値に問題があることを示しています。