【Excel】面倒な集計を自動化！VBAでアンケート複数回答の「カンマ区切り」を一瞬で分割

2025年10月5日
EXCEL, ITログ
VBA

EXCEL

Webアンケートの結果をExcelにまとめたら、複数回答の項目が全部1つのセルにカンマ区切りで入っていて、集計できずに困った…なんて経験はありませんか？

例えば、Microsoft Forms（アンケート作成ツール）の複数回答の選択肢は、Excelで開くと一つのセルにカンマ区切りで表示されます。

手作業で一つずつセルを分けるのは、時間がかかるしミスも怖いですよね。

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

1 Excelのデータツール（区切り位置）と関数で分割する
2 Excel VBAで分割する
3 おわりに
4 【2025年版・追記】コードを大幅にアップデート！

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などのプログラミング言語でも同様の処理を行うことが出来るかと思います。適切なツールや言語を選択し、目的に応じた方法を採用してください。

【2025年版・追記】コードを大幅にアップデート！

この記事で紹介した「カンマ区切りデータを0/1に変換するVBAコード」を全面的に見直し、さらに実用的で、エラーが起きにくく、使いやすいコードに改良しました！

以前のコードからの主な変更点

以前のコード（UserFormを使うもの）には、以下のような制限がありました。

回答項目が「1,2,3」のような数字である必要があった。
回答項目の総数（要素数）を毎回手入力する必要があった。
処理の基準がActiveCell（アクティブセル）だったため、操作に手間がかかった。

今回の改良版コードでは、これらの制限をすべて撤廃しています。

ポイント①：文字列に対応！ 「ラーメン,カレーライス」のような日本語（文字列）の回答でも完璧に動作します。
ポイント②：項目数を完全自動カウント！ データ全体をスキャンし、回答項目（ラーメン、寿司など）が何種類あるかをVBAが自動で判別します。もう要素数を数える必要はありません。
ポイント③：操作がカンタン！ 実行するとダイアログが2回表示されます。
1. 「入力列」（例: C列）をクリック
2. 「出力列」（例: E列）をクリックこれだけで、指定列の1行目から自動で表を作成します。

【改良版 Ver.4】VBAコード（文字列・自動カウント対応）

以前のUserFormや関連プロシージャはすべて不要です。以下のコードを標準モジュールにコピー＆ペーストするだけで準備完了です。

Option Explicit

'================================================================================
' メインプロシージャ
' 実行すると、入力列と出力列をダイアログで指定できます。
' 出力は必ず指定された列の「1行目」から開始されます。
' A列の最終行を基準に処理範囲を自動特定します。
'================================================================================
Sub Convert_FormsData_To_Boolean_V4()
    
    Dim dict As Object
    Dim inputArea As Range
    Dim outputArea As Range
    Dim outputStartCell As Range
    Dim ws As Worksheet
    Dim celldata As String
    Dim myArray() As String
    Dim item As String
    Dim key As Variant
    
    Dim inputCol As Long
    Dim outputCol As Long
    Dim headerRow As Long
    Dim startRow As Long
    Dim lastRow As Long
    Dim rowOffset As Long
    Dim colOffset As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    
    Set ws = ActiveSheet
    
    ' 実行速度の向上
    Application.ScreenUpdating = False
    
    ' 1. Dictionaryオブジェクトを作成 (項目と列番号を管理するため)
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 2. ユーザーに入力「列」を選択させる
    On Error Resume Next
    Set inputArea = Application.InputBox("カンマ区切りのデータが含まれる「入力列」を指定してください。" & vbCrLf & _
                                            "(例: C列の列ヘッダーをクリック)", "① 入力列の選択", Type:=8)
    If inputArea Is Nothing Then Exit Sub ' キャンセル時
    inputCol = inputArea.Column
    
    ' 3. ユーザーに出力開始「列」を選択させる
    Set outputArea = Application.InputBox("0/1データの表を出力したい「出力列」を指定してください。" & vbCrLf & _
                                            "(例: E列の列ヘッダーをクリック)" & vbCrLf & vbCrLf & _
                                            "※必ず「1行目」から出力されます。", "② 出力列の選択", Type:=8)
    If outputArea Is Nothing Then Exit Sub ' キャンセル時
    outputCol = outputArea.Column
    
    ' ★出力開始セルを「指定された列の1行目」に固定
    Set outputStartCell = ws.Cells(1, outputCol)
    On Error GoTo 0

    '=====================================================
    ' 4. 処理範囲の自動特定
    '=====================================================
    headerRow = outputStartCell.Row ' (常に 1)
    startRow = headerRow + 1        ' (常に 2)
    
    ' 最終行は「A列」を基準に自動取得 (フリーズ対策)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' A列のデータが1行目（ヘッダーのみ）の場合や空の場合
    If lastRow < 2 Then
        MsgBox "A列に処理対象のデータが見つかりません（2行目以降）。", vbExclamation
        Exit Sub
    End If
    
    '=====================================================
    ' フェーズ1: 全項目を自動スキャン (2行目から最終行まで)
    '=====================================================
    colOffset = 0
    For i = startRow To lastRow
        celldata = CStr(ws.Cells(i, inputCol).Value)
        If celldata <> "" Then
            myArray = Split(celldata, ",")
            For j = 0 To UBound(myArray)
                item = Trim(myArray(j)) ' 前後の空白を削除
                If item <> "" Then ' 空の要素を無視
                    If Not dict.Exists(item) Then
                        dict.Add item, colOffset ' 項目と列オフセットを登録
                        colOffset = colOffset + 1
                    End If
                End If
            Next j
        End If
    Next i
    
    If dict.Count = 0 Then
        MsgBox "処理対象のデータが見つかりませんでした。", vbInformation
        Exit Sub
    End If

    '=====================================================
    ' フェーズ2: ヘッダー行（項目名）の書き出し (1行目)
    '=====================================================
    ' 出力先列（1行目からA列最終行まで）をクリア
    ws.Range(outputStartCell, ws.Cells(lastRow, outputCol + dict.Count - 1)).Clear
    
    Dim outputHeaderRange As Range
    Set outputHeaderRange = outputStartCell.Resize(1, dict.Count)
    
    With outputHeaderRange
        .Interior.Color = RGB(255, 240, 245) ' 薄いピンク
        .WrapText = False
    End With
    
    k = 0
    For Each key In dict.Keys
        outputStartCell.Offset(0, k).Value = key
        k = k + 1
    Next key
    
    '=====================================================
    ' フェーズ3: 0/1 データの書き込み (2行目から)
    '=====================================================
    rowOffset = 1 ' (ヘッダーの1行下)
    For i = startRow To lastRow
        Dim outputDataRow As Range
        Set outputDataRow = outputStartCell.Offset(rowOffset, 0).Resize(1, dict.Count)
        outputDataRow.Value = 0
        celldata = CStr(ws.Cells(i, inputCol).Value)
        If celldata <> "" Then
            myArray = Split(celldata, ",")
            For j = 0 To UBound(myArray)
                item = Trim(myArray(j))
                If dict.Exists(item) Then
                    k = dict(item) ' Dictionaryから項目の列番号を取得
                    outputStartCell.Offset(rowOffset, k).Value = 1
                End If
            Next j
        End If
        rowOffset = rowOffset + 1
    Next i
    
    ' データ部分の書式設定
    With outputStartCell.Offset(1, 0).Resize(lastRow - startRow + 1, dict.Count)
        .NumberFormatLocal = "0_ "
    End With
    
    Application.ScreenUpdating = True
    MsgBox "処理が完了しました。" & vbCrLf & _
           dict.Count & " 項目に分類しました。 (" & startRow & "行目から " & lastRow & "行目まで)", vbInformation
           
End Sub

新しいコードの使い方

上記コードを標準モジュールに貼り付けます。
Alt + F8 でマクロ実行ウィンドウを開き、「Convert_FormsData_To_Boolean_V4」を実行します。
「① 入力列の選択」ダイアログが表示されます。 Formsのデータ（例: ラーメン,カレー）が入っている列（例: C列）の列ヘッダー C をクリックし、「OK」を押します。
「② 出力列の選択」ダイアログが表示されます。 0/1の表を出力したい列（例: E列）の列ヘッダー E をクリックし、「OK」を押します。
処理が実行され、E列の1行目（E1）から項目名（ラーメン、カレー…）が、2行目から0/1のデータが自動で入力されます。

（注意点） このマクロは、表の最終行を「A列」に入力されている最終行を基準に判断します。A列にIDや氏名などが正しく入力されていることを確認してから実行してください。

ぜひ、この新しいコードでさらなる業務効率化にお役立てください！

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

リンク

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

リンク

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

リンク