1つのセルに「赤,青;白,黒」のような区切り文字付きデータが入っている場合、ExcelのTEXTSPLIT関数を使えば、カンマを列、セミコロンを行として表形式へ分割できます。
ただし、実際のデータでは「赤,,青」のような連続した区切り文字によって不要な空欄ができたり、行ごとの項目数が違って末尾に#N/Aが表示されたりすることがあります。
この2つは同じ問題ではありません。連続区切りによる空欄はignore_empty、行・列の不足部分はpad_withで調整します。空欄そのものに意味があるデータでは、むやみにignore_emptyをTRUEにしないことも重要です。
TEXTSPLITで1セルの文字列を行・列に分割する
TEXTSPLIT関数の基本構文は次のとおりです。
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with])主な引数の役割は次のとおりです。
| 引数 | 役割 |
|---|---|
| text | 分割する文字列またはセル |
| col_delimiter | 列方向へ分割する区切り文字 |
| row_delimiter | 行方向へ分割する区切り文字 |
| ignore_empty | 連続する区切り文字による空要素を無視するか |
| match_mode | 区切り文字を大文字・小文字を区別して判定するか |
| pad_with | 行や列の要素数が足りない部分を何で補うか |
たとえばA2セルに次の文字列が入っているとします。
赤,青;白,黒カンマ,を列区切り、セミコロン;を行区切りとして分割するなら、次の数式です。
=TEXTSPLIT(A2,",",";")結果は次のような2行2列の表になります。
| 赤 | 青 |
| 白 | 黒 |
TEXTSPLITでは、列区切りと行区切りを別々に指定できるため、1セルに格納された簡易的な表データを一度に展開できます。
連続する区切り文字で空欄ができる場合はignore_emptyを確認する
TEXTSPLITで特に混乱しやすいのが、連続する区切り文字です。
たとえばA2セルが次の内容だったとします。
赤,,青;白,,黒カンマが2つ連続しています。
通常の数式では、連続する区切りの間も1つの要素として扱われるため、空のセルができます。
=TEXTSPLIT(A2,",",";")不要な空欄を取り除きたい場合は、第4引数のignore_emptyをTRUEにします。
=TEXTSPLIT(A2,",",";",TRUE)これにより、連続した区切り文字による空要素を無視して分割できます。
空欄に意味がある場合はTRUEにしない
ignore_empty=TRUEが常に正解とは限りません。
たとえば次のデータを考えてみます。
山田,,東京都この2番目の項目が「部署名」で、部署名が未入力という意味なら、空欄もデータ構造の一部です。
この場合に空欄を無視すると、
山田
東京都のように後続項目の位置関係が変わってしまいます。
そのため、判断基準は次のようになります。
| データの状態 | ignore_empty |
|---|---|
| 連続区切りが単なる入力上の余分な文字 | TRUE |
| 空欄が「未入力」「該当なし」などを意味する | FALSE |
| 項目の位置そのものに意味がある | FALSE |
ignore_emptyの既定値はFALSEです。空欄を削除する前に、その空欄が不要なのか、それとも欠損値として必要なのかを確認するのが安全です。
行ごとの項目数が違って#N/Aになる場合はpad_withを使う
ignore_emptyとは別に問題になるのが、行ごとの要素数が揃っていないケースです。
たとえばA2セルが次の内容だったとします。
赤,青;白,黒,黄1行目は2項目ですが、2行目は3項目です。
=TEXTSPLIT(A2,",",";")とすると、TEXTSPLITは3列の配列として結果を作る必要があります。そのため、1行目の不足している3列目は既定値の#N/Aで補われます。
イメージすると次の状態です。
| 赤 | 青 | #N/A |
| 白 | 黒 | 黄 |
この#N/Aを空欄に見せたい場合は、第6引数のpad_withに空文字列""を指定します。
=TEXTSPLIT(A2,",",";",FALSE,0,"")結果は次のようになります。
| 赤 | 青 | |
| 白 | 黒 | 黄 |
ここで重要なのは、ignore_emptyとpad_withは役割が異なることです。
ignore_emptyは元データ内の連続した区切り文字をどう扱うかを指定します。一方、pad_withは分割後の配列で行や列の長さが揃わないとき、不足部分を何で埋めるかを指定します。
空欄と#N/Aの違いを切り分ける
「空白や#N/Aを消したい」という場合でも、原因によって使う引数が変わります。
| 症状 | 原因 | 調整する引数 |
|---|---|---|
赤,,青の中央に空欄ができる | 連続区切り | ignore_empty |
| 短い行の末尾に#N/Aが付く | 行ごとの項目数が違う | pad_with |
| 空欄を残したい | 空欄自体がデータ | ignore_emptyをFALSE |
| 不足部分だけ空白表示にしたい | 配列の不足部分 | pad_withを"" |
まず「元文字列の空要素なのか」「表へ展開した結果の不足部分なのか」を見分けると、数式を直しやすくなります。
ignore_emptyとpad_withを同時に指定する
連続区切りを無視しつつ、行ごとの不足部分も空欄にしたい場合は、両方を指定できます。
たとえば次のようなデータです。
赤,,青;白,黒,黄次の数式では、連続区切りを無視し、不足部分を空文字列で補います。
=TEXTSPLIT(A2,",",";",TRUE,0,"")ただし、ignore_empty=TRUEにすると元データの空要素そのものが取り除かれます。
単に「見た目をきれいにしたい」という理由だけでTRUEにせず、元データの項目構造を確認してから指定してください。
複数種類の区切り文字をまとめて指定する
実務データでは、区切り文字が統一されていないこともあります。
たとえば、
赤,青、白のように、半角カンマ,と読点、が混在している場合です。
TEXTSPLITでは、複数の区切り文字を配列定数で指定できます。
=TEXTSPLIT(A2,{",","、"})カンマと読点のどちらでも列方向へ分割できます。
行区切りも併用するなら、たとえば次のように指定できます。
=TEXTSPLIT(A2,{",","、"},";")区切り文字の揺れが少数で、あらかじめ種類を把握できている場合に便利です。
ただし、文字列の形式そのものが大きく乱れている場合は、区切り文字を増やし続けるより、元データを整形してから分割したほうが管理しやすくなります。
TEXTSPLITの結果を置く周辺セルは空けておく
TEXTSPLITの結果は、数式を入力した1セルだけに収まるとは限りません。
たとえば2行3列へ分割される文字列なら、その結果を表示するために周囲の複数セルが使われます。
そのため、TEXTSPLITを入力するときは、分割結果が展開される範囲に既存データがない場所を選ぶのが基本です。
特に、元データのすぐ隣へ数式を入れる場合は、右側や下側に別の値が入っていないか確認しておきましょう。
表の大きさが入力内容によって変わる運用では、あらかじめ十分な空き範囲を確保しておくと扱いやすくなります。
TEXTSPLITを使うときによくある失敗
ignore_emptyをTRUEにすれば#N/Aも消えると思っている
ignore_emptyは連続区切りによる空要素を無視する設定です。
行ごとの項目数が異なることによって生じる末尾の#N/Aは、pad_withで調整します。
=TEXTSPLIT(A2,",",";",FALSE,0,"")のように、第6引数を指定してください。
空欄をすべて不要なデータとして消してしまう
名簿やCSV風データでは、「空欄」という状態自体が意味を持つことがあります。
たとえば、
商品A,,1200が「商品名、型番、価格」という順番なら、中央の空欄を削除すると「1200」が型番の位置へ移ってしまいます。
項目位置を維持する必要があるデータでは、ignore_emptyをFALSEのまま使います。
行区切りと列区切りを逆に指定する
TEXTSPLITでは、第2引数が列区切り、第3引数が行区切りです。
=TEXTSPLIT(A2,",",";")なら、
,で右方向へ分割;で下方向へ分割
という意味です。
結果の向きがおかしい場合は、まず第2引数と第3引数を確認してください。
TEXTSPLITはCSVファイル全体を完全に読み取る機能ではない
TEXTSPLITは、セル内にある文字列を指定した区切り文字で分割する関数です。
単純なカンマ区切りやセミコロン区切りの文字列には便利ですが、一般的なCSVファイルには、引用符で囲まれた値や値の中に含まれるカンマ、改行など、単純な文字分割だけでは正しく処理できないケースがあります。
たとえば、
"東京,本社",営業部,100のように、値そのものへカンマが含まれているデータを単純に,で分割すると、本来1項目である文字列まで分かれてしまいます。
そのため、TEXTSPLITを「CSVファイルを完全に解析する関数」と考えるのは適切ではありません。
セル内の規則的な区切り文字データを分割する用途と考えると分かりやすいでしょう。
TEXTSPLITを使えるExcelのバージョンを確認する
TEXTSPLITは、Microsoftが案内している対応環境ではExcel for Microsoft 365およびExcel 2024などで利用できます。
Excel 2021以前を含むすべてのExcelで使える関数ではないため、他のPCへブックを渡す場合は利用環境にも注意が必要です。
古いExcelとの互換性が必要な業務では、TEXTSPLITを前提に数式を組む前に、利用者側のExcelバージョンを確認しておきましょう。
空欄と#N/Aは別々に原因を確認する
TEXTSPLITで1セルの区切り文字付きデータを表へ変換するときは、まず列区切りと行区切りを指定します。
=TEXTSPLIT(A2,",",";")そのうえで、不要な連続区切りがあるならignore_emptyをTRUEにします。
=TEXTSPLIT(A2,",",";",TRUE)行ごとの項目数が違い、末尾の#N/Aを空欄にしたいならpad_withを指定します。
=TEXTSPLIT(A2,",",";",FALSE,0,"")ポイントは、連続区切りによる空欄と、配列の不足部分に入る#N/Aを同じものとして扱わないことです。
空欄に意味があるデータではignore_emptyをFALSEのままにし、表示上不要な不足値だけをpad_withで調整します。まず元データの区切り方を確認し、「空要素を消したいのか」「不足部分の表示を変えたいのか」を切り分けてから数式を組むと、意図しない列ずれを防げます。
コメント