アンケートの単回答を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」がおすすめです。
