アンケートの単回答をExcelで集計する方法
2017/08/07
カテゴリ:Tips
Excel の関数やピボットテーブルを用いて単回答を集計する方法を紹介します。なお、ページ下部で解説に用いたExcel ファイルをダウンロードすることができます。
概要
単回答形式の質問では、回答者はもっともあてはまると思う選択肢を1つ回答します。単一回答、単数回答と呼ばれることもあります。英語では Single Answer、略してSAです。回答データは選択肢の番号(カテゴリーコード)で、1から始まる整数になります。各整数の件数を数えることが単回答の集計となります。
集計に用いるデータ
回答データの例として、「高校生の生活調査」を200人を対象に行った結果を用います。なお、データは架空の値です。
![「データ」シート 「データ」シート](../wp-content/uploads/2017/08/eq_1_1.gif)
ここでは、上図の「Q1.あなたは、どのようにして学校に通っていますか?」の回答データをExcel 2010を用いて以下の3種類の方法で集計します。
COUNTIF関数を用いる場合の作成手順
1. 集計表の枠を用意します。
![集計表の枠 集計表の枠](../wp-content/uploads/2017/08/eq_1_2.gif)
2. COUNTIF関数を用いて件数を求めます。COUNTIF関数は、[範囲]内で[検索条件]を満たす数値の個数を求める関数です。
COUNTIF(範囲, 検索条件)
集計表内のセル範囲「D3:D6」を選択後、下図のように数式を入力します。[範囲]に「データ」シートのQ1のデータ範囲、[検索条件]に集計表のカテゴリーコードのセルを指定します。また、選択範囲内で同じ式を利用できるように、[範囲]を絶対参照で参照します。絶対参照とするには、数式のテキストにカーソルを置いて[F4]キーを押し、$マークを付けます。
![COUNTIF関数の入力 COUNTIF関数の入力](../wp-content/uploads/2017/08/eq_1_3.gif)
3. [Ctrl]+[Enter]を押して数式を一括入力します。下図のように件数が求まります。
![件数の算出 件数の算出](../wp-content/uploads/2017/08/eq_1_4.gif)
4. SUM関数で件数の合計を求めます。ショートカットキーを利用すると入力が簡単です。D7セルで[Shift]+[Alt]+[=]を押し、[Ctrl]+[Enter]を押します。合計が200となり、サンプルサイズに等しいことを確認します。
![合計の算出 計の算出](../wp-content/uploads/2017/08/eq_1_5.gif)
5. 割合を求めます。セル範囲「E3:E7」を選択後、下図のように数式を入力します。合計セル(D7)を絶対参照で参照します。
![割合を求める数式の入力 割合を求める数式の入力](../wp-content/uploads/2017/08/eq_1_6.gif)
6. [Ctrl]+[Enter]を押して数式を一括入力します。下図のように割合が求まります。
![割合の算出 割合の算出](../wp-content/uploads/2017/08/eq_1_7.gif)
7. 割合の表示形式を整えて、集計表の完成です。
![Q1の集計表 Q1の集計表](../wp-content/uploads/2017/08/eq_1_8.gif)
DCOUNT関数とデータ テーブル機能を用いる場合の作成手順
1. 集計表の枠を用意します。カテゴリーラベルは未入力とします。
![集計表の枠 集計表の枠](../wp-content/uploads/2017/08/eq_1_9.gif)
2. OFFSET関数を用いて「データ」シートのカテゴリーラベルを参照します。OFFSET関数は、[参照]セルから[行数]と[列数]だけ移動した先のセルの値を返します。
C3セルで下図のように数式を入力します。[参照]には「データ」シートの「Q1」ラベルのセル、[行数]には集計表のカテゴリーコードのセル、[列数]には0を指定します。
![OFFSET関数の入力 OFFSET関数の入力](../wp-content/uploads/2017/08/eq_1_10.gif)
3. [Ctrl]+[Enter]を押して数式を確定します。
![OFFSET関数の確定 OFFSET関数の確定](../wp-content/uploads/2017/08/eq_1_11.gif)
4. DCOUNT関数を用いて件数を求めます。DCOUNT関数は、[データベース]のセル範囲で[条件]を満たす行で、かつ[フィールド](列)に数値が入力されている行の数を返します。[データベース]および[条件]にはラベルを含むセル範囲を指定することがポイントです。
D3セルに下図のように数式を入力します。[データベース]に「データ」シートの質問ラベルを含む回答データ全範囲、[フィールド]にデータ範囲の2列目を表す2、[条件]に質問ラベルとカテゴリーコードの範囲を指定します。
![DCOUNT関数の入力 DCOUNT関数の入力](../wp-content/uploads/2017/08/eq_1_12.gif)
5. [Ctrl]+[Enter]を押して数式を確定します。
![DCOUNT関数の確定 DCOUNT関数の確定](../wp-content/uploads/2017/08/eq_1_13.gif)
6.データ テーブル機能を用いて残りの範囲のカテゴリーラベルと件数を出力します。セル範囲「B3:D6」を選択後、Excel のリボンから[データ]→[What-If 分析]→[データ テーブル]を選択します。
![「データ テーブル」ウィンドウ 「データ テーブル」ウィンドウ](../wp-content/uploads/2017/08/eq_1_14.gif)
「データ テーブル」ウィンドウで[列の代入セル]にB3を指定し、[OK]ボタンをクリックします。データ テーブルの機能を用いることで、選択範囲の1行目の数式の[列の代入セル](B3)を他のカテゴリーコード(B4:B6)で置き換えた場合の結果が「C4:D6」に出力されます。
![カテゴリーラベルと件数の出力 カテゴリーラベルと件数の出力](../wp-content/uploads/2017/08/eq_1_15.gif)
7. SUM関数で件数の合計を求めます。ショートカットキーを利用すると入力が簡単です。D7セルで[Shift]+[Alt]+[=]を押し、[Ctrl]+[Enter]を押します。合計が200となり、サンプルサイズに等しいことを確認します。
![合計の算出 計の算出](../wp-content/uploads/2017/08/eq_1_5.gif)
8. 割合を求めます。セル範囲「E3:E7」を選択後、下図のように数式を入力します。合計セルを絶対参照で参照します。
![割合を求める数式の入力 割合を求める数式の入力](../wp-content/uploads/2017/08/eq_1_6.gif)
9. [Ctrl]+[Enter]を押して数式を一括入力します。下図のように割合が求まります。
![割合の算出 割合の算出](../wp-content/uploads/2017/08/eq_1_7.gif)
10. 割合の表示形式を整えて、集計表の完成です。
![Q1の集計表 Q1の集計表](../wp-content/uploads/2017/08/eq_1_8.gif)
ピボットテーブル機能を用いる場合の作成手順
1. 「データ」シートでセル範囲「A14:AK214」を選択後、Excel のリボンから[挿入]→[ピボットテーブル]→[ピボットテーブル]を選択します。
![「ピボットテーブルの作成」ウィンドウ 「ピボットテーブルの作成」ウィンドウ](../wp-content/uploads/2017/08/eq_1_16.gif)
このまま[OK]ボタンをクリックします。
![ピボットテーブル用新規ワークシート ピボットテーブル用新規ワークシート](../wp-content/uploads/2017/08/eq_1_17.gif)
2. 「ピボットテーブルのフィールドリスト」から「行フィールド」へ「Q1」をドラッグします。
![Q1を行フィールドへドラッグ Q1を行フィールドへドラッグ](../wp-content/uploads/2017/08/eq_1_18.gif)
3. 「ピボットテーブルのフィールドリスト」から「値フィールド」へ「Q1」をドラッグします。
![Q1を値フィールドへドラッグ Q1を値フィールドへドラッグ](../wp-content/uploads/2017/08/eq_1_19.gif)
4. もう一度、「ピボットテーブルのフィールドリスト」から「値フィールド」へ「Q1」をドラッグします。
![再度Q1を値フィールドへドラッグ 再度Q1を値フィールドへドラッグ](../wp-content/uploads/2017/08/eq_1_20.gif)
5. B3セル「データ」をC3セル「集計」にドラッグし、集計項目を縦並びから横並びに変更します。
![集計項目を縦並びから横並びに変更 集計項目を縦並びから横並びに変更](../wp-content/uploads/2017/08/eq_1_21.gif)
6. B4セルを選択後、Excel のリボンから[オプション]→[集計方法]→[データの個数]を選択します。
![集計方法をデータの個数に変更 集計方法をデータの個数に変更](../wp-content/uploads/2017/08/eq_1_22.gif)
7. C4セルを選択後、同様にExcel のリボンから[オプション]→[集計方法]→[データの個数]を選択します。
![集計方法をデータの個数に変更 集計方法をデータの個数に変更](../wp-content/uploads/2017/08/eq_1_23.gif)
8. C4セルを選択したままで、Excel のリボンから[オプション]→[計算の種類]→[総計に対する比率]を選択します。
![計算の種類を総計に対する比率に変更 計算の種類を総計に対する比率に変更](../wp-content/uploads/2017/08/eq_1_24.gif)
9. ラベルと割合の表示形式を整えて、集計表の完成です。
![Q1の集計表 Q1の集計表](../wp-content/uploads/2017/08/eq_1_25.gif)
補足
今回はExcel 2010を用いましたが、他のバージョンのExcelでも同様の集計を行うことが可能です。
ダウンロード
このページのExcel ファイルのダウンロードはこちらから → enqete_1.xlsx
このファイルは、エクセル統計の体験版に対応しています。
関連リンク
単回答・複数回答・数量回答の単純集計やクロス集計を行う場合、アンケート集計ソフト「秀吉Dplus」がおすすめです。