TEXTSPLIT は、区切り文字で文字列を部分に分割し、VSTACK と HSTACK は範囲を 1 つにまとめます。どれも数式として働くため、元のデータが変わると結果も更新されます。これらには Excel 2024 か Microsoft 365 が必要です。
文字列の分割
A2 に oak;pine;birch がある場合、=TEXTSPLIT(A2,";") は oak、pine、birch を、横に並んだ 3 つのセルに入れます。区切り文字には任意の文字列を指定でき、数文字の長さでもかまいません。
3 番目の引数は 行の区切り文字 です。A2 に a,b;c,d がある場合、数式 =TEXTSPLIT(A2,",",";") は 2 行 2 列のブロックを埋めます。1 行目に a と b、2 行目に c と d が入ります。完全な行になっていない部分は、最後の引数を設定しない限り #N/A で埋められます。たとえば、=TEXTSPLIT(A2,",",";",FALSE,0,"-") は、空いている部分をダッシュで埋めます。
知っておくと役立つオプション
- 空の部分: 「a,,b」の場合、既定では中央に空のセルが残ります。4 番目の引数を TRUE に設定すると、空の部分をスキップします:
=TEXTSPLIT(A2,",",,TRUE)。 - 結果は文字列です。数値として扱うには、VALUE で囲みます。A2 に 3,5,8 がある場合、
=SUM(VALUE(TEXTSPLIT(A2,","))) は 16 を返します。 - 5 番目の引数は一致モードです。既定では、区切り文字は大文字と小文字を正確に一致させる必要があります。1 に設定すると大文字と小文字を無視するため、x と書いた区切り文字は X でも分割されます。
「区切り位置」ボタンも似た働きをしますが、こちらは 1 回限りの操作です。TEXTSPLIT は数式なので、新しいデータも自動的に分割されます。
複数の区切り文字と整理
1 つの文字列では、カンマやセミコロンなど、区切り文字が混在していることがあります。区切り文字の引数は、中かっこで囲んだ配列定数として書かれた区切り文字の一覧を受け取り、Excel はそのいずれでも分割します。乱雑な元データでは、各部分の前後に余分なスペースがあることも多いです。それらを取り除くには、結果を TRIM で囲みます: =TRIM(TEXTSPLIT(A2,","))。
分割された部分は文字列として返ってくることを忘れないでください。007 のような値は先頭の 0 が残りますが、文字列になった数値は、変換するまで合計できません。それを行うのが VALUE です。
範囲の積み重ね
=VSTACK(A2:A3,C2:C4) は、2 つ目の範囲を 1 つ目の範囲の下に配置し、5 行になります。=HSTACK(A2:A3,C2:C3) は、それらを横に並べます。どちらもコピーと貼り付けは不要で、どちらもスピルします。
範囲の幅が異なる場合 (HSTACK では高さ)、短い方が #N/A で埋められます。代わりに空欄かダッシュを表示するには、数式を IFERROR で囲みます。
HSTACK は、別々の列から表を作るのにも同じくらい便利です。たとえば、名前をその得点の横に並べる場合などです。役立つパターンは、見出しの行とフィルター済みのデータを組み合わせることです: =VSTACK(A1:B1,FILTER(A2:B20,B2:B20>5)) は、見出しに続けて一致する行だけを返します。
共有する前に
Excel 2021 以前を使う同僚は、これらの関数を使っている箇所で #NAME? と表示されます。多くの人に送るファイルでは、古い文字列関数や区切り位置を使うと、どの環境でも動作します。