Excelでカンマ区切りのセル情報をスピル形式で縦に展開する方法

Excelでデータを整理する際、カンマ区切りのセル情報を縦に展開することが必要になることがあります。Excelのデフォルトの機能だけでは直接的には実現できませんが、以下のような関数を使って簡単に実現できます。

=FILTERXML("<t><s>" & SUBSTITUTE(A1, ",", "</s><s>") & "</s></t>", "//s")

この記事では、上記の式を使用してカンマ区切りのセル情報をスピル形式で縦に展開させる方法を紹介します。ただし「スピル」形式での展開はOffice 2021以降(もしくはMicrosoft365)でのみ利用できます。

スピル機能とは

Excelのスピル機能は、関数の結果を自動的に隣接するセルに展開する機能です。これにより、複数のセルに結果を表示することができ、データの整理や分析が容易になります。

以下の手順でカンマ区切りのセル情報を縦に展開します。

=FILTERXML("<t><s>" & SUBSTITUTE(A1, ",", "</s><s>") & "</s></t>", "//s")

これで、選択したセルのカンマ区切りのデータがスピル形式で縦に展開されます。

上記の式では、FILTERXML関数とSUBSTITUTE関数を使用してカンマ区切りのデータを縦に展開しています。以下に、各関数の役割を説明します。

SUBSTITUTE関数は、文字列内の特定の文字を別の文字に置き換える関数です。この例では、A1セルのカンマを”</s><s>”に置き換えています。
FILTERXML関数は、XMLデータから特定の要素を抽出する関数です。この例では、</s><s>で区切られたデータをXML形式に変換し、それを元に縦に展開しています。

上記の式を分解して説明すると、以下のようになります。

SUBSTITUTE(A1, ",", "</s><s>")で、A1セルのカンマを”</s><s>”に置き換えたデータを生成します。
生成されたデータの前後に”<t><s>”と”</s></t>”を追加して、XML形式のデータに変換します。
FILTERXML("<t><s>" & SUBSTITUTE(A1, ",", "</s><s>") & "</s></t>", "//s")で、変換されたXMLデータから<s>要素を抽出し、縦に展開します。