Office Hack

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

  • Release

この記事では、アンケート集計に関する様々な情報をご紹介します。

大量のデータを集計して結果を出したい場合は、エクセルで処理を行うのがおすすめです。

以下では、アンケート集計表の作り方や自由回答を集計する方法、5段階評価を集計する方等についてご説明しています。

クロス集計とは?

クロス集計の例

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

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

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

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

アンケート集計表の作り方

COUNTIF関数を使って、アンケートで集めた「はい/いいえ」の回答がそれぞれいくつかあるか数えることが出来る集計表を作成する方法をご説明します。

作業時間:7分

COUNTIF関数を入力する

COUNTIF関数を入力する

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

範囲の引数を設定する

範囲を設定する

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

検索条件の引数を設定する

検索条件を設定する

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

2つ目のCOUNTIF関数を入力する

COUNTIF関数を入力

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

範囲の引数を設定する

範囲の設定する

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

検索条件の引数を設定する

検索条件を設定する

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

集計結果が出る

結果が表示される

これで、COUNTIF関数を使って「はい」「いいえ」の回答をそれぞれ集計することが出来ました。

エクセルのアンケート集計方法

エクセルでアンケートを集計する様々な方法についてご紹介します。

カンマで区切られた複数回答を集計する

カンマで区切られた回答データ

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

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

区切り位置を選択する

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

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

完了ボタンを押す

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

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

COUNTIF関数を入力

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

次に、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】ボタンを押します。

行ボックスにドラッグする

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

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

値ボックスに表示する

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

値フィールドの設定を行う

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

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

個数を選択してOKボタンを押す

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

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

結果ごとに個数が集計される

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

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

5段階評価を集計する

5段階評価のアンケート結果例

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

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

ピボットテーブルを作成する

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

OKボタンを押す

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

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

満足度をドラッグする

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

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

回答番号をドラッグする

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

値フィールドの設定を行う

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

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

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

個数に変更してOKボタンを押す

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

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

ピボットテーブルでの集計が完了する

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

円グラフでアンケート集計する

円グラフでアンケート集計する方法は以下の通りです。

表を選択してピボットテーブルを作成する

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

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

OKボタンを押す

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

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

行フィールドにドラッグする

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

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

値フィールドにドラッグする

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

ピボットグラフを作成する

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

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

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

円グラフを選択する

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

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

アンケート結果の円グラフが完成する

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

マクロで複数ファイルのアンケートをまとめる

複数人にアンケートファイルを送付して回答してもらった場合、マクロを使えば複数のファイルを自動的に1つのファイルにまとめることが出来るので集計の作業効率が上がります。

以下では「東京支店」「大阪支店」「福岡支店」のそれぞれの店舗に回答してもらったアンケート結果を1つのファイルにまとめる方法をご説明します。

エクセルでマクロを組むためには、「開発タブ」が必要になります。

開発タブは初期設定では表示されていないので、以下の記事を参考に開発タブの表示を行ってください。

マクロで複数ファイルのアンケートをまとめる方法は、以下の通りです。

1つのフォルダにまとめる

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

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

ファイルの内容

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

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

VBAを選択する

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

①【開発】タブ、②【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上にある無料でダウンロードできる集計表をご紹介します。

利用規約等は、各サイトをご確認ください。

集計表テンプレート

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

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

アンケート集計テンプレート

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

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

集計テンプレート

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

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

[Excel 合計]の関連記事

よろしければ参考にならなかった点をお聞かせください

CAPTCHA


Page Top