ExcelのTEXTSPLIT関数の使い方

ExcelのTEXTSPLIT関数は、テキストを指定した区切り文字に基づいて分割する際に使用します。返り値は右のセルや下のセルへこぼれる処理、いわゆる「スピル」の処理がされます。この関数は、データ整理や解析において非常に便利で、特に大きなデータセットを扱う際に有用です。この記事では、TEXTSPLIT関数の使い方、引数の説明、そして具体的な使用例を提供します。

TEXTSPLIT関数の書式

TEXTSPLIT関数の書式は以下の通りです。

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

引数の詳細

1. text（必須）：分割するテキストを指定します。
2. col_delimiter（必須）：列に分割する際の区切り文字。例: “,”, “|”, “、” など。複数の文字を登録したい場合には配列定数が使用できます。
3. row_delimiter（オプション）：行に分割する際の区切り文字。例: “\n”, “;”, “。” など。
4. ignore_empty（オプション）：このブール型引数にTRUEを指定すると、空のテキストフィールドは無視されます。FALSE(既定値)を指定した場合、空のテキストフィールドも結果に含まれます。
5. match_mode（オプション）：区切り文字の大文字と小文字を区別するかどうかを指定します。”0″（または指定なし）で区別せず、”1″ で区別します。
6. pad_with（オプション）：分割結果の各行の要素数が不均等な場合に、不足分を埋めるための文字列を指定します。例えば、”*” を指定すると、不足分が “*” で埋められます。既定値は「#N/A」です。

TEXTSPLIT関数の使用例(列区切り)

以下のテキスト(引数：text)を例にして、TEXTSPLIT関数の使用方法を説明します。

"Apple,Banana,Orange,Grape,Melon"

例：カンマを使用してテキストを列で分割する場合

=TEXTSPLIT("Apple,Banana,Orange,Grape,Melon",",")

この式の結果は、「Apple」「Banana」「Orange」「Grape」「Melon」となり、それぞれが1つずつ別のセルに表示されます。式を作成したセルを開始点として、列方向に区切られてスピルされます。

上記の計算式をセルB2へ作成した。

また、セルA1に

Apple,Banana,Orange,Grape,Melon

が入力されていて、このセルの値をtextとして列区切りで表示する計算式は下記のようになります。

=TEXTSPLIT(A1,",")

セルA1の値をカンマ区切りで列方向に展開する計算式をセルA3へ作成した

TEXTSPLIT関数の使用例(行区切り)

同様のテキストを行区切りで表示する計算式を見てみましょう。列区切り文字は指定せず、行区切り文字だけを指定します。

例：カンマを使用してテキストを行で分割する場合

=TEXTSPLIT("Apple,Banana,Orange,Grape,Melon",,",")

この式の結果は、「Apple」「Banana」「Orange」「Grape」「Melon」となり、それぞれが1つずつ別のセルに表示されます。式を作成したセルを開始点として、行方向に区切られてスピルされます。

上記の式をセルB2へ作成した

TEXTSPLIT関数の使用例(列区切りと行区切りの併用)

以下のテキスト(引数：text)を例にして、TEXTSPLIT関数の使用方法を説明します。

"Apple, Banana; Orange; Grape, Melon"

例：カンマとセミコロンを使用してテキストを分割する場合

=TEXTSPLIT("Apple, Banana; Orange; Grape, Melon", ", ", "; ")

この式の結果は、「Apple」「Banana」【行替え】、「Orange」「空白」【行替え】、「Grape」「Melon」となり、それぞれが別のセルに表示されます。

上記の計算式をセルB2へ作成した

空のテキストフィールドを含むテキストの分割

TEXTSPLIT関数では、空のテキストフィールド(区切り文字が連続するケース)を無視するかどうかを指定できます。以下の例では、空のテキストフィールドが含まれる場合に、それらを無視して分割します。「,」が連続している部分は無視されます。

"Apple,,Orange,,Grape"

例：カンマを使用してテキストを分割し、空のテキストフィールドを無視する場合

=TEXTSPLIT("Apple,,Orange,,Grape", ",",, TRUE)

この式の結果は、「Apple」「Orange」「Grape」となり、空のテキストフィールドは無視されます。

例3：カンマを使用してテキストを分割し、空のテキストフィールドを無視しない場合

=TEXTSPLIT("Apple,,Orange,,Grape", ",", FALSE)

この式の結果は、「Apple」「空」「Orange」「空」「Grape」となり、空のテキストフィールドも結果に含まれます。

TEXTSPLIT関数の「pad_with」引数の使用例

`pad_with` 引数は、分割結果の各行が異なる数の要素を持つ場合に、右側不足分を特定の文字列で埋めるために使用されます。ここでは、3行×4列のデータを例に、`pad_with` 引数の使い方を説明します。以下のテキストを考えます：

"Apple,Banana,Cherry;Grape,,Melon,;Kiwi,"

このテキストに TEXTSPLIT 関数を適用し、セミコロン ; を行の区切り、カンマ , を列の区切りとして使用します。さらに、pad_with 引数に “*” を設定して、右側不足分を “*” で埋めます。

具体的な式

=TEXTSPLIT("Apple,Banana,Cherry;Grape,,Melon,;Kiwi,",",",";",FALSE,0,"*")

この公式の結果は以下のようになります：

各行は4列のデータを持つべきですが、元のテキストの各行には異なる数の要素が含まれています。pad_with 引数が “*” に設定されているため、不足分の右側セルは “*” で埋められ、全ての行が4列に揃えられます。

このように、pad_with 引数を使用することで、分割後のデータを均一な形式で整理し、データの可視化や分析に適した形に整えることができます。

まとめ

ExcelのTEXTSPLIT関数は、テキストを指定した区切り文字で分割する際に使用する関数です。空のテキストフィールドを無視するかどうかを指定でき、また、分割するテキストにはさまざまな形式を適用することが可能です。これにより、データの整形や解析において幅広い用途があります。テキストの分割は、データの前処理や整形作業において特に役立ちます。