アンケートの単回答をExcelで集計する方法
2017/08/07
カテゴリ:Tips
Excel の関数やピボットテーブルを用いて単回答を集計する方法を紹介します。なお、ページ下部で解説に用いたExcel ファイルをダウンロードすることができます。
概要
単回答形式の質問では、回答者はもっともあてはまると思う選択肢を1つ回答します。単一回答、単数回答と呼ばれることもあります。英語では Single Answer、略してSAです。回答データは選択肢の番号(カテゴリーコード)で、1から始まる整数になります。各整数の件数を数えることが単回答の集計となります。
集計に用いるデータ
回答データの例として、「高校生の生活調査」を200人を対象に行った結果を用います。なお、データは架空の値です。

ここでは、上図の「Q1.あなたは、どのようにして学校に通っていますか?」の回答データをExcel 2010を用いて以下の3種類の方法で集計します。
COUNTIF関数を用いる場合の作成手順
1. 集計表の枠を用意します。

2. COUNTIF関数を用いて件数を求めます。COUNTIF関数は、[範囲]内で[検索条件]を満たす数値の個数を求める関数です。
COUNTIF(範囲, 検索条件)
集計表内のセル範囲「D3:D6」を選択後、下図のように数式を入力します。[範囲]に「データ」シートのQ1のデータ範囲、[検索条件]に集計表のカテゴリーコードのセルを指定します。また、選択範囲内で同じ式を利用できるように、[範囲]を絶対参照で参照します。絶対参照とするには、数式のテキストにカーソルを置いて[F4]キーを押し、$マークを付けます。

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

4. SUM関数で件数の合計を求めます。ショートカットキーを利用すると入力が簡単です。D7セルで[Shift]+[Alt]+[=]を押し、[Ctrl]+[Enter]を押します。合計が200となり、サンプルサイズに等しいことを確認します。

5. 割合を求めます。セル範囲「E3:E7」を選択後、下図のように数式を入力します。合計セル(D7)を絶対参照で参照します。

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

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

DCOUNT関数とデータ テーブル機能を用いる場合の作成手順
1. 集計表の枠を用意します。カテゴリーラベルは未入力とします。

2. OFFSET関数を用いて「データ」シートのカテゴリーラベルを参照します。OFFSET関数は、[参照]セルから[行数]と[列数]だけ移動した先のセルの値を返します。
C3セルで下図のように数式を入力します。[参照]には「データ」シートの「Q1」ラベルのセル、[行数]には集計表のカテゴリーコードのセル、[列数]には0を指定します。

3. [Ctrl]+[Enter]を押して数式を確定します。

4. DCOUNT関数を用いて件数を求めます。DCOUNT関数は、[データベース]のセル範囲で[条件]を満たす行で、かつ[フィールド](列)に数値が入力されている行の数を返します。[データベース]および[条件]にはラベルを含むセル範囲を指定することがポイントです。
D3セルに下図のように数式を入力します。[データベース]に「データ」シートの質問ラベルを含む回答データ全範囲、[フィールド]にデータ範囲の2列目を表す2、[条件]に質問ラベルとカテゴリーコードの範囲を指定します。

5. [Ctrl]+[Enter]を押して数式を確定します。

6.データ テーブル機能を用いて残りの範囲のカテゴリーラベルと件数を出力します。セル範囲「B3:D6」を選択後、Excel のリボンから[データ]→[What-If 分析]→[データ テーブル]を選択します。

「データ テーブル」ウィンドウで[列の代入セル]にB3を指定し、[OK]ボタンをクリックします。データ テーブルの機能を用いることで、選択範囲の1行目の数式の[列の代入セル](B3)を他のカテゴリーコード(B4:B6)で置き換えた場合の結果が「C4:D6」に出力されます。

7. SUM関数で件数の合計を求めます。ショートカットキーを利用すると入力が簡単です。D7セルで[Shift]+[Alt]+[=]を押し、[Ctrl]+[Enter]を押します。合計が200となり、サンプルサイズに等しいことを確認します。

8. 割合を求めます。セル範囲「E3:E7」を選択後、下図のように数式を入力します。合計セルを絶対参照で参照します。

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

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

ピボットテーブル機能を用いる場合の作成手順
1. 「データ」シートでセル範囲「A14:AK214」を選択後、Excel のリボンから[挿入]→[ピボットテーブル]→[ピボットテーブル]を選択します。

このまま[OK]ボタンをクリックします。

2. 「ピボットテーブルのフィールドリスト」から「行フィールド」へ「Q1」をドラッグします。

3. 「ピボットテーブルのフィールドリスト」から「値フィールド」へ「Q1」をドラッグします。

4. もう一度、「ピボットテーブルのフィールドリスト」から「値フィールド」へ「Q1」をドラッグします。

5. B3セル「データ」をC3セル「集計」にドラッグし、集計項目を縦並びから横並びに変更します。

6. B4セルを選択後、Excel のリボンから[オプション]→[集計方法]→[データの個数]を選択します。

7. C4セルを選択後、同様にExcel のリボンから[オプション]→[集計方法]→[データの個数]を選択します。

8. C4セルを選択したままで、Excel のリボンから[オプション]→[計算の種類]→[総計に対する比率]を選択します。

9. ラベルと割合の表示形式を整えて、集計表の完成です。

補足
今回はExcel 2010を用いましたが、他のバージョンのExcelでも同様の集計を行うことが可能です。
ダウンロード
このページのExcel ファイルのダウンロードはこちらから → enqete_1.xlsx
このファイルは、エクセル統計の体験版に対応しています。
関連リンク
単回答・複数回答・数量回答の単純集計やクロス集計を行う場合、アンケート集計ソフト「秀吉Dplus」がおすすめです。