【EXCEL VBA】一つのセルにカンマ区切りで登録されているデータの分割

2023年6月15日
2024年8月17日
EXCEL, ITログ
VBA

EXCEL

一つのセルにカンマ区切りで登録されているデータをよく見かけます。例えば、Microsoft Forms（アンケート作成ツール）の複数回答の選択肢は、Excelで開くと一つのセルにカンマ区切りで表示されます。

こうしたデータは、集計やグラフ化をする際には非常に扱いづらいデータです。そこで複数回答の選択肢のデータを集計やグラフ化に適した形式に変換する方法を紹介します。

1 Excelのデータツール（区切り位置）と関数で分割する
2 Excel VBAで分割する
3 おわりに

Excelのデータツール（区切り位置）と関数で分割する

一つのセルにカンマ区切りで登録されているデータを複数列に分割するには、Excelのデータツール（区切り位置）機能を使うと簡単です。

Excelの該当セルにあるデータ列を選択します。
「データ」タブをクリックし、「区切り位置」ツールを選択します。
「元のデータの形式」のオプションの「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択して「次へ」をクリックします。
「区切り文字」オプションの「コンマ」を選択して「次へ」をクリックします。
必要に応じて「列のデータ形式」を設定します（テキスト、数値など）。
複数列に分割したデータを表示する「表示先」を指定して「完了」をクリックします。
分割されたデータが複数の列に入ります。

これらの操作により一つのセルにカンマ区切りで登録されているデータを複数列に分割することができますが、集計やグラフ作成に適したデータ形式とは言えません。なぜなら、分割されたデータは複数の列に分散しているため、集計やグラフ化を行う際にはデータを統合する手間が生じるからです。また、回答の数が異なる場合には、データの整理がより複雑になります。

したがって、より効率的なデータ形式にするためには、分割されたデータをさらに集計やグラフ化に適した形式に変換する必要があります。具体的な方法としては、分割されたデータを各回答ごとに行に配置し、COUNTIF関数を用いて回答が選択されている場合には「1」、選択されていない場合には「0」などの値をセルに入力する方法があります。このようなデータ形式にすることで、集計やグラフ化が容易になります。

Excel VBAで分割する

前述の方法でも目的を達成することは可能ですが、若干の手間がかかります。そこでExcel VBAを使用して、一つのセルにカンマ区切りで登録されているデータを自動的に分割し、集計やグラフ化に適した形式に変換する方法を紹介します。これにより、手動での作業を省略し、効率的にデータの整理と分析を行うことができます。

サンプルコード（全体）

以下にExcel VBAのサンプルコードを示します。このコードを使用すると、ユーザーフォームを通じて要素数を指定し、Excel VBAが自動的にセルの分割処理を行います。セル内のデータが選択肢ごとに分割され、データの整理と集計が容易になります。

'要素数入力フォームプロシージャ

Sub openform()
    UserForm1.Show
End Sub

'メインプロシージャ
Private Sub CommandButton1_Click()
    SELLSPLITBOOL (Me.TextBox1.Value)
    Unload Me
End Sub

'セル分割プロシージャ
Sub SELLSPLITBOOL(ByVal element As Integer)
    Dim myws As Worksheet
    Dim mycol As Integer
    Dim myelmt As Integer
    Dim myArray() As String
    Dim celldata As String

    Set myws = ActiveSheet
    myelmt = element
    mycol = ActiveCell.Column

    '要素数分の列を挿入
    myws.Range(Columns(mycol + 1), Columns(mycol + myelmt)).Insert
    With myws.Range(Columns(mycol + 1), Columns(mycol + myelmt))
        .WrapText = False
        .NumberFormatLocal = "0_ "
    End With

    'タイトル行
    For i = 1 To myelmt
        myws.Cells(1, mycol + i) = myws.Cells(1, mycol) & "-" & i
    Next i
    myws.Range(Cells(1, mycol + 1), Cells(1, mycol + myelmt)).Interior.Color = RGB(255, 240, 245)

    '最終行までループ
    For i = 2 To myws.Cells(Rows.Count, "A").End(xlUp).Row
        myws.Range(Cells(i, mycol + 1), Cells(i, mycol + myelmt)) = 0   '行を「０」で埋める
        celldata = myws.Cells(i, mycol)
        If celldata <> "" Then
            myArray = Split(celldata, ",")              '回答値をカンマで分割し配列に格納

            '配列数をループ
            For j = 0 To UBound(myArray)
                myws.Cells(i, mycol + myArray(j)) = 1   '該当する列に「１」を入力
            Next j
        End If
    Next i
End Sub

サンプルコードの構成

このコードでは、要素数を入力するフォームを開くためのプロシージャ「openform()」と、メインプロシージャ「CommandButton1_Click()」、そしてセル分割プロシージャ「SELLSPLITBOOL()」から構成されています。

「openform()」で要素数を入力するフォームを開き、フォームに配置されたボタンをクリックすることで「CommandButton1_Click()」が呼び出され、フォームで入力された要素数を引数にして、「SELLSPLITBOOL()」を呼び出します。「SELLSPLITBOOL()」は、指定された列に対して、指定された要素数分の列を挿入し、タイトル行を作成し、最終行までループして各行のセルデータを処理します。

サンプルコードの説明

Sub openform()
    UserForm1.Show
End Sub

「openform()」は、ユーザーフォームを表示するためのプロシージャです。UserForm1.Show を使用して、作成した要素数を入力するフォームを表示します。

Private Sub CommandButton1_Click()
    SELLSPLITBOOL (Me.TextBox1.Value)
    Unload Me
End Sub

このプロシージャは、フォーム上の実行ボタンがクリックされたときに実行されます。
Me.TextBox1.Value を使用して、フォーム上のテキストボックスに入力された要素数を取得し、引数としてSELLSPLITBOOL() プロシージャに渡します。SELLSPLITBOOL() プロシージャ実行後、Unload Me を使用して、フォームを閉じます。

Sub SELLSPLITBOOL(ByVal element As Integer)
    Dim myws As Worksheet
    Dim mycol As Integer
    Dim myelmt As Integer
    Dim myArray() As String
    Dim celldata As String

    Set myws = ActiveSheet
    myelmt = element
    mycol = ActiveCell.Column

SELLSPLITBOOL() プロシージャは、指定されたセルのデータを分割し、集計やグラフ化に適した形式に変換します。要素数を element 変数で受け取っています。
・Dim ステートメントの各行でプロシージャ内で使用する変数を宣言しています。
・現在のアクティブなシートを取得して myws 変数に格納します。
・引数で渡された要素数を myelmt 変数に格納します。
・アクティブセルの列番号を mycol 変数に格納します。

    myws.Range(Columns(mycol + 1), Columns(mycol + myelmt)).Insert
    With myws.Range(Columns(mycol + 1), Columns(mycol + myelmt))
        .WrapText = False
        .NumberFormatLocal = "0_ "
    End With

要素数分の列を挿入しているブロックです。myelmt 分だけ新たな列を挿入して書式設定を行います。
・myws.Range(Columns(mycol+1), Columns(mycol+myelmt)).Insert を使用して列を挿入します。
・WrapText プロパティを「 False 」に設定してテキストの折り返しを無効にします。
・NumberFormatLocal プロパティに「 "0_ " 」を設定して表示形式を数値にします。

    For i = 1 To myelmt
        myws.Cells(1, mycol + i) = myws.Cells(1, mycol) & "-" & i
    Next i
    myws.Range(Cells(1, mycol + 1), Cells(1, mycol + myelmt)).Interior.Color = RGB(255, 240, 245)

タイトル行を設定しているブロックです。タイトル行には、元のセルの値に連番を付けた値を設定します。これにより、各列がどの選択肢を表しているかが明示されます。
・For i = 1 To myelmt のループ内で、myws.Cells(1, mycol + i) = myws.Cells(1, mycol) & "-" & i を使用してタイトル行に連番を付けた値を設定します。
・ループを抜けた後、myws.Range(Cells(1, mycol + 1), Cells(1, mycol + myelmt)).Interior.Color = RGB(255, 240, 245) でタイトル行の背景色を設定します。

    For i = 2 To myws.Cells(Rows.Count, "A").End(xlUp).Row
        myws.Range(Cells(i, mycol + 1), Cells(i, mycol + myelmt)) = 0   '行を「０」で埋める
        celldata = myws.Cells(i, mycol)
        If celldata <> "" Then
            myArray = Split(celldata, ",")              '回答値をカンマで分割し配列に格納

            '配列数をループ
            For j = 0 To UBound(myArray)
                myws.Cells(i, mycol + myArray(j)) = 1   '該当する列に「１」を入力
            Next j
        End If
    Next i

このプロシージャは、指定されたセルのデータを分割し、集計やグラフ化に適した形式に変換します。
・For i = 2 To myws.Cells(Rows.Count, "A").End(xlUp).Row ので最終行までループします。
・myws.Range(Cells(i, mycol + 1), Cells(i, mycol + myelmt)) = 0 で処理する行のすべての列を0で埋めます。
・celldata = myws.Cells(i, mycol) を使用して、セルの値を celldata に格納します。
・If celldata <> "" Thenでセルのデータが空でない場合、以下の処理を実行します。
・myArray = Split(celldata, ",") を使用して、セルのデータをカンマで分割し、myArray に格納します。
・For j = 0 To UBound(myArray) のループ内で、分割されたデータを処理します
・myws.Cells(i, mycol + myArray(j)) = 1 を使用して、該当する列に 1 を入力します。これにより、各回答が選択された場合に該当する列に 1 が表示されます。

サンプルコードの実行方法

このコードの実行方法は次の通りです。

Excelシート上で、マクロを実行したい場所（上の例では「回答1.1」のセル）を選択します。
「開発」タブをクリック（又は[Alt]キーと[F8]キーを同時に押して）してマクロのダイアログを開きます。
「openform」という名前のマクロを選択し、実行ボタンをクリックします。
フォームが表示されるので、テキストボックスに要素数を入力して「実行」をクリックします。
指定した要素数に基づいてセルが分割され、該当セルの右に要素数分のデータ（回答が選択されている場合には「1」、選択されていない場合には「0」)が挿入されます。

なお、マクロのダイアログの「オプション」からショートカットキーが設定されていれば、Excelシート上から直接「openform」を呼び出すこともできます。

おわりに

この記事では、Excelで一つのセルにカンマ区切りで登録されているデータの分割方法について詳しく説明しました。これらの手法を使用することで、集計やグラフ化に適した形式にデータを変換できます。

一つのセルに複数の回答がカンマ区切りで登録されている場合、手動での分割作業は手間がかかりますし、ミスが発生するリスクも高まります。そこで、Excelの機能やVBAを活用することで、自動化された分割プロセスを実現しました。これによりデータの整理と分析が迅速かつ正確に行えます。

この他もExcelの関数のみで実現する方法やPythonなどのプログラミング言語でも同様の処理を行うことが出来るかと思います。適切なツールや言語を選択し、目的に応じた方法を採用してください。

Excelの基本から便利な関数やグラフ

リンク

ピボットテーブルの基本から応用

リンク

マクロ/VBAの基本&業務効率化

リンク