アンケートの複数回答をExcelで集計する方法

2017/08/07

カテゴリ：Tips

Excel の関数を用いて複数回答を集計する方法を紹介します。なお、ページ下部で解説に用いたExcel ファイルをダウンロードすることができます。

概要

複数回答形式の質問では、回答者があてはまると思う選択肢が複数選ばれます。回答データは、選択肢と同じ数の列数とサンプルサイズと同じ数の行数のセル範囲に0または1で入力されます。0は回答者がその選択肢を選ばなかったことを、1は選んだことを表します。各選択肢の該当者数は、各列の数値の合計を求めることで集計できます。

集計に用いるデータ

回答データの例として、「高校生の生活調査」を200人を対象に行った結果を用います。なお、データは架空の値です。

ここでは、上図の「Q3-SQ.朝食を摂らない理由は何ですか？」の回答データをExcel 2010を用いて集計します。この設問は、「Q3.平日は、毎日、朝食を摂っていますか？」で「いいえ」と回答した人のみが回答する設問です。

作成手順

1. 集計表の枠を用意します。

2. COUNTIF関数を用いて全体の件数を求めます。全体の件数は、Q3で「いいえ」と回答した人の数です。COUNTIF関数は、［範囲］内で［検索条件］を満たす数値の個数を求める関数です。

COUNTIF(範囲, 検索条件)

集計表内のセル範囲「D3:D7」を選択後、下図のように数式を入力します。［範囲］に「データ」シートのQ3のデータ範囲、［検索条件］に「いいえ」を表す2を指定します。また、選択範囲内で同じ式を利用できるように、［範囲］を絶対参照で参照します。絶対参照とするには、数式のテキストにカーソルを置いて［F4］キーを押し、$マークを付けます。

3. ［Ctrl］+［Enter］を押して数式を一括入力します。下図のように全体の件数が求まります。

4. SUM関数で各選択肢の件数を求めます。各選択肢のデータ範囲が異なるので、SUM関数の参照範囲をINDEX関数で切り替えて指定します。INDEX関数は、［参照］範囲の［行番号］と［列番号］の交差するセル範囲を返す関数です。ここでは、［行番号］に0を指定することで［列番号］で指定した列全体のセル範囲を返り値とします。

SUM(INDEX(参照, 行番号, 列番号))

集計表内のセル範囲「E3:E7」を選択後、下図のように数式を入力します。［参照］に「データ」シートの「Q3-SQ-1」から「Q3-SQ-5」までのセル範囲、［行番号］に0、［列番号］に集計表のカテゴリーコードを指定します。

5. ［Ctrl］+［Enter］を押して数式を一括入力します。下図のように件数が求まります。

6. 割合を求めます。集計表内のセル範囲「F3:F7」を選択後、下図のように数式を入力します。

7. ［Ctrl］+［Enter］を押して数式を一括入力します。下図のように割合が求まります。

8. 割合の表示形式を整えて、集計表の完成です。

補足

今回はExcel 2010を用いましたが、他のバージョンのExcelでも同様の集計を行うことが可能です。

ダウンロード

このページのExcel ファイルのダウンロードはこちらから　→　enqete_2.xlsx

このファイルは、エクセル統計の体験版に対応しています。

ブログ