カンマ区切りの文字列を縦に展開する方法

Excelでカンマ区切りの文字列を縦に展開する方法について説明します。例えば、セルA1に「佐藤,山田,田中,星野,佐々木,定岡」といったカンマ区切りのデータがあり、これをセルB1から下方向に縦に展開したいときに便利な方法です。

ここでは、以下の式を使って展開します。

=TRIM(MID(SUBSTITUTE("," & $A$1, ",", REPT(" ", 999)), 999*(ROW()), 999))

目的の実現だけでよろしければ上記の式を加工・コピーして使ってください。

意味が気になる方、位置などの調整をしたい方は以下の解説をご一読ください。

一つずつ分解して説明します。

1. SUBSTITUTE関数

SUBSTITUTE関数は、ある文字列内の特定の文字や文字列を別の文字や文字列に置き換える関数です。この場合、カンマ区切りの文字列の前にカンマを追加し、カンマを「空白文字列」に置き換えています。

SUBSTITUTE("," & $A$1, ",", REPT(" ", 999))

2. REPT関数

REPT関数は、指定した文字列を指定回数繰り返す関数です。ここでは、空白文字を999回繰り返すことで、999文字の空白文字列を作成しています。

REPT(" ", 999)

3. MID関数

MID関数は、指定した文字列から指定した位置から指定した文字数だけ文字を抽出する関数です。SUBSTITUTE関数で置き換えられた文字列から、特定の位置から999文字の長さの文字列を抽出しています。

MID(SUBSTITUTE(",", & $A$1, ",", REPT(" ", 999)), 999*(ROW()), 999)

4. ROW関数

ROW関数は、指定したセルの行番号を返す関数です。ここではROW関数を引数なしで使用しており、その場合現在のセルの行番号を返します。この行番号に基づいてMID関数で抽出する位置を変更しています。

999*(ROW())

5. TRIM関数

TRIM関数は、文字列の前後の余分な空白を取り除く関数です。MID関数で抽出した文字列に余分な空白が含まれていることがあるため、TRIM関数を使用して、抽出された文字列の前後の空白を取り除いています。

TRIM(MID(SUBSTITUTE("," & $A$1, ",", REPT(" ", 999)), 999*(ROW()), 999))

まとめ

以上の5つの関数を組み合わせることで、カンマ区切りの文字列を縦に展開する式が完成します。これにより、セルA1にあるカンマ区切りの文字列を、セルB1から下方向に縦に展開できます。

各関数の役割を理解すれば、この式がどのように動作するかが分かるでしょう。まず、SUBSTITUTE関数でカンマを999文字の空白に置き換えた文字列を作成し、次にMID関数でその文字列から必要な部分を抽出しています。その後、ROW関数で抽出する位置を調整し、最後にTRIM関数で余分な空白を取り除いています。

これでカンマ区切りの文字列を縦に展開する方法が理解できたはずです。これを実際に使って、データ整形や見やすさの向上に役立ててください。Excelの関数を組み合わせて使うことで、様々なデータ処理が可能になります。