エクセルでアンケート集計に関する情報まとめ

クロス集計とはデータの分析方法の1つで、アンケート調査等で集めたデータを複数の質問項目を掛け合わせて集計したものです。

上の画像は「好きなフルーツのアンケート結果」から「性別」と「フルーツ」の項目を掛け合わせて「性別ごとの回答結果」を表現した例です。

クロス集計をすると「性別」「年代別」等様々な角度からデータを分析することが可能です。

クロス集計の方法や、集計結果をグラフにする方法等については以下の記事をご参照ください。

エクセルのクロス集計のやり方をわかりやすく説明

上の画像のように、カンマで区切られた複数回答を集計する方法をご紹介します。

今回は「好きなフルーツは何ですか？」という複数回答可の架空のアンケート結果を例に挙げて、カンマで区切られた複数回答を集計する方法をご説明します。

まずは、カンマで区切られたデータを1つのセルに1つのデータになるように分けます。

①【集計したいデータ（例：B3セルからB17セル）】、②【データ】タブ、③【区切り位置】の順に選択します。

「区切り位置指定ウィザード」ダイアログボックスが表示されます。

①「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択し、②【完了】ボタンを押します。

これで、カンマで区切られたデータを分けることが出来ました。

次に、COUNTIF関数でそれぞれのデータ数をカウントします。

【任意のセル（例：E3セル）】を選択し、『=COUNTIF(』と入力します。

次に、範囲の引数を入力します。

データが入ったすべてのセルを範囲の引数として選択します。

「=COUNTIF(」に続いて『B3:C17,』と入力します。

次に、検索条件の引数を設定します。

「B3:C17,」に続いて『"みかん")』と入力しEnterを押します。

これで、「みかん」を集計する数式が完成しました。

その他E列の「りんご」「ぶどう」「いちご」「パイン」の回答数のセルにも「範囲」の引数まではE3セルに入力した数式とすべて同じで「検索条件」の引数にそれぞれのフルーツの名前を入れた数式を入力しましょう。

これでカンマで区切られた複数回答を集計することが出来ました。

上の画像は自由回答で「好きな果物は？」というアンケートに回答してもらった架空のデータです。

自由回答は、選択肢がなく回答者がそれぞれ自由に答えるのでC列にあるように回答結果の表記がばらばらになってしまいます。

表記が統一されず集計しづらくなるため、エクセルで集計したい場合は自由回答の質問は作成せず選択肢を設定するのがおすすめです。

今回は、上の画像のような「単語での自由回答」をエクセルで集計する方法をご説明します。

まずは、自由回答の結果を似ている回答ごとにまとめます。

【回答結果（例：C2セルからC22セル）】を選択し、Ctrl+Cを押します。

別の列に貼り付けます。

【任意のセル（例：E2セル）】を選択し、Ctrl+Vを押します。

結果が貼り付けられました。

並べ替えを行います。

①【コピーした回答（例：E3セルからE22セル）】、②【データ】タブ、③【昇順】の順に選択します。

結果を昇順で並べ替えたことで、似ている回答ごとにおおまかにまとめることが出来ました。

集計しやすくするために上の画像のF3セルからF22セルのように、並べ替えた回答の隣のセルにそれぞれの回答ごとに『数字』を入力します。

今回は以下のように数字を割り当てています。

1. みかん、ミカン、甘いみかん
2. ぶどう、ブドウ、種なしぶどう
3. パイナップル、パイン
4. 桃、もも、白桃
5. りんご、リンゴ

コピーしたデータと割り振った数字でピボットテーブルを作成します。

①【コピーしたデータ（例：E2セルからF22セル）】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。

【OK】ボタンを押します。

ピボットテーブルが作成されます。

「割り振った数字のリスト（例：結果）」を「行ボックス」に【ドラッグ】します。

「割り振った数字のリスト（例：結果）」を「値ボックス」にも【ドラッグ】します。

次に、値フィールドの設定を行います。

①【合計 / 結果】、②【値フィールドの設定】の順に選択します。

「値フィールドの設定」ダイアログボックスが表示されます。

①「値フィールドの集計」の【個数】を選択し、②【OK】ボタンを押します。

これで、自由回答に割り振った数字がそれぞれカウントされ、似ている回答ごとにまとめて集計することが出来ました。

1から5の割り振った数字を元のデータ（例：果物）にあてはめて結果を導きだすことが出来ます。

上の画像のような、5段階評価で集めたデータを集計する方法は以下の通りです。

今回はピボットテーブルで集計する方法をご説明します。

①【アンケート結果を入力した表（例：A2セルからC22セル）】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。

範囲選択が正しく出来ていることを確認して、【OK】ボタンを押します。

ピボットテーブルが表示されました。

まずは、「満足度」を行ボックスに【ドラッグ】します。

次に、「NO.」を値ボックスに【ドラッグ】します。

これで、5段階の集計を行うピボットテーブルが出来ました。

次に、「値フィールド」の設定を行います。

①【合計 / NO.】、②【値フィールドの設定】の順に選択します。

「値フィールドの設定」ダイアログボックスが表示されます。

①「値フィールドの集計」で【個数】を選択し、②【OK】ボタンを押します。

これで、5段階評価のアンケート結果を集計することが出来ました。

今回は、上の画像のように「はい/いいえ」で回答を集めたアンケート結果を円グラフで集計します。

①【表（A2セルからB17セル）】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。

範囲選択が正しく出来ていることを確認して、【OK】ボタンを押します。

ピボットテーブルが表示されました。

まずは、「はい/いいえが入力されたリスト（例：問1）」を行ボックスに【ドラッグ】します。

次に、「はい/いいえが入力されたリスト（例：問1）」を値ボックスにも【ドラッグ】します。

これで、「はい/いいえ」の数がそれぞれ集計できました。

次に、この結果でピボットグラフを作成します。

①【ピボットテーブル内の任意のセル（例：A3セル）】、②【ピボットテーブル分析】タブ、③【ピボットグラフ】の順に選択します。

「グラフの挿入」ダイアログボックスが表示されます。

①【円グラフ】、②【円グラフ】の順に選択し、③【OK】ボタンを押します。

これで、アンケート結果を円グラフで集計することが出来ました。

まずは、まとめたい全てのファイルを同じフォルダに格納しておきましょう。

今回は「東京支店」「大阪支店」「福岡支店」のそれぞれのアンケート結果をマクロを組んで1つにまとめます。

各店舗のアンケート結果には上の画像のようなデータが入力されています。

このデータを1つのファイルにまとめていきます。

集計用のマクロを組む任意のファイルを開きます。

①【開発】タブ、②【Visual Basic】の順に選択します。

「Microsoft Visual Basic for Applications」ダイアログボックスが表示されます。

①【挿入】タブ、②【標準モジュール】の順に選択します。

コードを入力します。

以下のコードを貼りつけましょう。

Sub Dataextraction()

    Dim FolderName As String
    Dim FileName As String
    Dim ThisBook As Workbook
    Dim i As Integer
    i = 2
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = True Then
            FolderName = .SelectedItems(1)
        End If
    End With
    
    FileName = Dir(FolderName & "\*.xlsx")
    
    Do While FileName <> ""
    
        Set ThisBook = Workbooks.Open(FolderName & "\" & FileName)
        
        ThisWorkbook.Worksheets("Sheet1").Range("A" & CStr(i)).Value = ThisBook.Worksheets("Sheet1").Range("A1").Value
        ThisWorkbook.Worksheets("Sheet1").Range("B" & CStr(i)).Value = ThisBook.Worksheets("Sheet1").Range("B1").Value
    
        
        FileName = Dir()
        i = i + 1
        
        ThisBook.Close
    Loop
    
End Sub

貼りつけたら保存します。

①【ファイル】タブ、②【集計用マクロ.xlsx の 上書き保存】の順に選択します。

「Microsoft Excel」ダイアログボックスが表示されます。

【いいえ】ボタンを押します。

「名前を付けて保存」ダイアログボックスが表示されます。

①【任意の保存場所（例：デスクトップ）】、②【まとめたいファイルが入っているフォルダ（例：アンケート集計）】の順に選択します。

「名前を付けて保存」ダイアログボックスが表示されます。

①『任意のファイル名（例：集計）』を入力して、②【Excel マクロ有効ブック】を選択し、③【保存】ボタンを押します。

VBAを設定したファイルのホーム画面に戻ります。

①『項目名』を入力したり、【セルの塗りつぶし】を設定したりして集めたデータが分かりやすいように任意の書式に整えます。

②【開発】タブ、③【マクロ】の順に選択します。

「マクロ」ダイアログボックスが表示されます。

①【マクロ名（例：Dataextraction】を選択し、②【実行】ボタンを押します。

「参照」ダイアログボックスが表示されます。

ファイルの保存先を決めます。

①【任意の保存場所（例：デスクトップ）】、②【任意のフォルダ（例：アンケート集計）】の順に選択し、③【OK】ボタンを押します。

これで、同一フォルダ内の複数ファイルに入力されたデータを、1つのファイルにまとめることができました。

アンケート集計表：フリーテンプレートでは、上の画像のような印刷にも適したアンケート集計表のフリーテンプレートを配布しています。

無回答を含めた5段階評価で割合が出せるように、数式も入力してあります。

アンケート集計＆グラフ作成用のエクセルテンプレートでは、アンケート結果を入力するだけでグラフも作成できるようなアンケート集計表のテンプレートが配布されています。

学校や会社など、規模の大きいアンケートを実施したい場合などに便利に使うことが出来ます。

アンケート集計表：円グラフ付きでは、円グラフがあらかじめ挿入されたアンケート集計表を配布しています。

自分で円グラフを作成したい方は上記「円グラフでアンケート集計する」セクションも参考にしてみて下さい。