- 公開日:
- 更新日:
エクセルでアンケート集計に関する情報まとめ
この記事では、アンケート集計に関する様々な情報をご紹介します。
大量のデータを集計して結果を出したい場合は、エクセルで処理を行うのがおすすめです。
以下では、アンケート集計表の作り方や自由回答を集計する方法、5段階評価を集計する方等についてご説明しています。
クロス集計とは?

クロス集計とはデータの分析方法の1つで、アンケート調査等で集めたデータを複数の質問項目を掛け合わせて集計したものです。
上の画像は「好きなフルーツのアンケート結果」から「性別」と「フルーツ」の項目を掛け合わせて「性別ごとの回答結果」を表現した例です。
クロス集計をすると「性別」「年代別」等様々な角度からデータを分析することが可能です。
クロス集計の方法や、集計結果をグラフにする方法等については以下の記事をご参照ください。
アンケート集計表の作り方
COUNTIF関数を使って、アンケートで集めた「はい/いいえ」の回答がそれぞれいくつかあるか数えることが出来る集計表を作成する方法をご説明します。

上の画像のような表を準備します。左の表には15名分の回答が入力されています。まずはCOUNTIF関数を入力します。COUNTIF関数の書式は「=COUNTIF(範囲,検索条件)」です。【任意のセル(例:E3セル)】を選択し、『=COUNTIF(』と入力します。

次に、COUNTIF関数の範囲の引数を設定します。B2セルからB17セルから回答を集計するので「=COUNTIF(」に続いて『B3:B17,』と入力します。

次に、COUNTIF関数の検索条件の引数を設定します。「はい」の数を数えたいので、「B3:B17,」に続いて『,"はい")』と入力し【Enter】キーを押します。

「はい」を集計するCOUNTIF関数が完成しました。次に「いいえ」を集計するCOUNTIF関数を入力します。【任意のセル(例:E4セル)】を選択し、『=COUNTIF(』と入力します。

「いいえ」を集計する範囲を設定します。「=COUNTIF(」に続いて『B3:B17,』と入力します。

「いいえ」を検索条件として引数を設定します。「B3:B17,」に続いて『,"いいえ")』と入力し【Enter】キーを押します。

これで、COUNTIF関数を使って「はい」「いいえ」の回答をそれぞれ集計することが出来ました。
エクセルのアンケート集計方法
エクセルでアンケートを集計する様々な方法についてご紹介します。
カンマで区切られた複数回答を集計する

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

まずは、カンマで区切られたデータを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セルのように、並べ替えた回答の隣のセルにそれぞれの回答ごとに『数字』を入力します。
今回は以下のように数字を割り当てています。
- みかん、ミカン、甘いみかん
- ぶどう、ブドウ、種なしぶどう
- パイナップル、パイン
- 桃、もも、白桃
- りんご、リンゴ

コピーしたデータと割り振った数字でピボットテーブルを作成します。
①【コピーしたデータ(例:E2セルからF22セル)】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

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

ピボットテーブルが作成されます。
「割り振った数字のリスト(例:結果)」を「行ボックス」に【ドラッグ】します。

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

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

「値フィールドの設定」ダイアログボックスが表示されます。
①「値フィールドの集計」の【個数】を選択し、②【OK】ボタンを押します。

これで、自由回答に割り振った数字がそれぞれカウントされ、似ている回答ごとにまとめて集計することが出来ました。
1から5の割り振った数字を元のデータ(例:果物)にあてはめて結果を導きだすことが出来ます。
5段階評価を集計する

上の画像のような、5段階評価で集めたデータを集計する方法は以下の通りです。
今回はピボットテーブルで集計する方法をご説明します。

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

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。
範囲選択が正しく出来ていることを確認して、【OK】ボタンを押します。

ピボットテーブルが表示されました。
まずは、「満足度」を行ボックスに【ドラッグ】します。

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

これで、5段階の集計を行うピボットテーブルが出来ました。
次に、「値フィールド」の設定を行います。
①【合計 / NO.】、②【値フィールドの設定】の順に選択します。

「値フィールドの設定」ダイアログボックスが表示されます。
①「値フィールドの集計」で【個数】を選択し、②【OK】ボタンを押します。

これで、5段階評価のアンケート結果を集計することが出来ました。
円グラフでアンケート集計する
円グラフでアンケート集計する方法は以下の通りです。

今回は、上の画像のように「はい/いいえ」で回答を集めたアンケート結果を円グラフで集計します。
①【表(A2セルからB17セル)】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。
範囲選択が正しく出来ていることを確認して、【OK】ボタンを押します。

ピボットテーブルが表示されました。
まずは、「はい/いいえが入力されたリスト(例:問1)」を行ボックスに【ドラッグ】します。

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

これで、「はい/いいえ」の数がそれぞれ集計できました。
次に、この結果でピボットグラフを作成します。
①【ピボットテーブル内の任意のセル(例:A3セル)】、②【ピボットテーブル分析】タブ、③【ピボットグラフ】の順に選択します。

「グラフの挿入」ダイアログボックスが表示されます。
①【円グラフ】、②【円グラフ】の順に選択し、③【OK】ボタンを押します。

これで、アンケート結果を円グラフで集計することが出来ました。
マクロで複数ファイルのアンケートをまとめる
複数人にアンケートファイルを送付して回答してもらった場合、マクロを使えば複数のファイルを自動的に1つのファイルにまとめることが出来るので集計の作業効率が上がります。
以下では「東京支店」「大阪支店」「福岡支店」のそれぞれの店舗に回答してもらったアンケート結果を1つのファイルにまとめる方法をご説明します。
エクセルでマクロを組むためには、「開発タブ」が必要になります。
開発タブは初期設定では表示されていないので、以下の記事を参考に開発タブの表示を行ってください。
マクロで複数ファイルのアンケートをまとめる方法は、以下の通りです。

まずは、まとめたい全てのファイルを同じフォルダに格納しておきましょう。
今回は「東京支店」「大阪支店」「福岡支店」のそれぞれのアンケート結果をマクロを組んで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つのファイルにまとめることができました。
無料でダウンロードできる集計表
Web上にある無料でダウンロードできる集計表をご紹介します。
利用規約等は、各サイトをご確認ください。

アンケート集計&グラフ作成用のエクセルテンプレートでは、アンケート結果を入力するだけでグラフも作成できるようなアンケート集計表のテンプレートが配布されています。
学校や会社など、規模の大きいアンケートを実施したい場合などに便利に使うことが出来ます。

アンケート集計表:円グラフ付きでは、円グラフがあらかじめ挿入されたアンケート集計表を配布しています。
自分で円グラフを作成したい方は上記「円グラフでアンケート集計する」セクションも参考にしてみて下さい。