アンケートの数量回答をExcelで集計する方法
2017/08/07
カテゴリ:Tips
Excel の関数やピボットテーブルを用いて数量による回答を集計する方法を紹介します。
概要
数量回答形式の質問では、回答者が年齢や人数、割合、時間などの量を数値で回答します。回答データは、サンプルサイズと同じ行数の1列のセル範囲に入力されます。数量回答の集計では、任意の間隔ごとのデータの件数を調べます。これは、度数分布を作成することに他なりません。
集計に用いるデータ
回答データの例として、「高校生の生活調査」を200人を対象に行った結果を用います。なお、データは架空の値です。
![「データ」シート 「データ」シート](../wp-body/wp-content/uploads/2017/08/eq_1_1.gif)
ここでは、上図の「Q2.通学にかかる時間はどれくらいですか?」の回答データをExcel 2010を用いて以下の4種類の方法で集計します。
COUNTIF関数を用いる場合の作成手順
1. 集計表と検索条件の枠を用意します。
![集計表と検索条件の枠 集計表と検索条件の枠](../wp-body/wp-content/uploads/2017/08/eq_3_1.gif)
2. 検索条件を入力します。各階級の下限値を用いて「>=0」「>=10」…と入力します。
![検索条件の入力 検索条件の入力](../wp-body/wp-content/uploads/2017/08/eq_3_2.gif)
3. Q2の回答データの範囲に名前を定義します。「データ」シートのセル範囲「C15:C214」を選択後、Excel のリボンから[数式]→[名前の定義]をクリックします。「新しい名前」ウィンドウで[名前]を「Q2回答」とし、[OK]ボタンをクリックします。定義した名前は数式の引数に指定することができます。
![「新しい名前」ウィンドウ 「新しい名前」ウィンドウ](../wp-body/wp-content/uploads/2017/08/eq_3_3.gif)
4. COUNTIF関数を用いて件数を求めます。COUNTIF関数は、[範囲]内で[検索条件]を満たすセルの個数を求める関数です。
COUNTIF(範囲, 検索条件)
集計表内のセル範囲「D3:D12」を選択後、下図のように数式を入力します。階級「0~9分」の場合、「>=0」を満たすセルの個数から「>=10」を満たすセルの個数を引きます。[範囲]には先ほど定義した「Q2回答」を指定します。
![件数を求めるCOUNTIF関数の入力 件数を求めるCOUNTIF関数の入力](../wp-body/wp-content/uploads/2017/08/eq_3_4.gif)
5. [Ctrl]+[Enter]を押して数式を一括入力します。下図のように件数が求まります。
![件数の算出 件数の算出](../wp-body/wp-content/uploads/2017/08/eq_3_5.gif)
6. SUM関数で件数の合計を求めます。ショートカットキーを利用すると入力が簡単です。D13セルで[Shift]+[Alt]+[=]を押し、[Ctrl]+[Enter]を押します。合計が200となり、サンプルサイズに等しいことを確認します。
![合計を求めるSUM関数の入力 合計を求めるSUM関数の入力](../wp-body/wp-content/uploads/2017/08/eq_3_6.gif)
7. 割合を求めます。集計表内のセル範囲「E3:E13」を選択後、下図のように数式を入力します。合計のセルD13を絶対参照で参照します。数式のD13にカーソルがある状態で[F4]キーを押すことで絶対参照とすることができます。
![割合を求める数式の入力 割合を求める数式の入力](../wp-body/wp-content/uploads/2017/08/eq_3_7.gif)
8. [Ctrl]+[Enter]を押して数式を一括入力します。下図のように割合が求まります。
![割合の算出 割合の算出](../wp-body/wp-content/uploads/2017/08/eq_3_8.gif)
9. 割合の表示形式を整えて、集計表の完成です。
![Q2の集計表 Q2の集計表](../wp-body/wp-content/uploads/2017/08/eq_3_9.gif)
SUMPRODUCT関数を用いる場合の作成手順
1. 集計表と下限値の枠を用意します。
![集計表と下限値の枠 集計表と下限値の枠](../wp-body/wp-content/uploads/2017/08/eq_3_10.gif)
2. 各階級の下限値を入力します。
![下限値の入力 下限値の入力](../wp-body/wp-content/uploads/2017/08/eq_3_11.gif)
3. Q2の回答データの範囲に名前を定義します。「データ」シートのセル範囲「C15:C214」を選択後、Excel のリボンから[数式]→[名前の定義]をクリックします。「新しい名前」ウィンドウで[名前]を「Q2回答」とし、[OK]ボタンをクリックします。定義した名前は数式の引数に指定することができます。
![「新しい名前」ウィンドウ 「新しい名前」ウィンドウ](../wp-body/wp-content/uploads/2017/08/eq_3_3.gif)
4. SUMPRODUCT関数を用いて件数を求めます。SUMPRODUCT関数は、複数の条件を満たすセルの個数を求める場合に有用な関数です。
SUMPRODUCT((範囲>=下限値)*(範囲<次の階級の下限値))
集計表内のセル範囲「D3:D11」を選択後、下図のように数式を入力します。階級「0~9分」の場合、「>=0」と「<10」の両方を満たすセルの個数を求めます。[範囲]には先ほど定義した「Q2回答」を指定します。
![件数を求めるSUMPRODUCT関数の入力 件数を求めるSUMPRODUCT関数の入力](../wp-body/wp-content/uploads/2017/08/eq_3_12.gif)
5. [Ctrl]+[Enter]を押して数式を一括入力します。
![件数の算出 件数の算出](../wp-body/wp-content/uploads/2017/08/eq_3_13.gif)
6. 最後の階級の「90分以上」は条件が1つだけなので、他の階級とは異なる仕様でSUMPRODUCT関数を入力します。
SUMPRODUCT((範囲>=下限値)*1)
![件数を求めるSUMPRODUCT関数の入力 件数を求めるSUMPRODUCT関数の入力](../wp-body/wp-content/uploads/2017/08/eq_3_14.gif)
7. [Ctrl]+[Enter]を押して数式を入力します。
![件数の算出 件数の算出](../wp-body/wp-content/uploads/2017/08/eq_3_15.gif)
8. SUM関数で件数の合計を求めます。ショートカットキーを利用すると入力が簡単です。D13セルで[Shift]+[Alt]+[=]を押し、[Ctrl]+[Enter]を押します。合計が200となり、サンプルサイズに等しいことを確認します。
![合計を求めるSUM関数の入力 合計を求めるSUM関数の入力](../wp-body/wp-content/uploads/2017/08/eq_3_16.gif)
9. 割合を求めます。集計表内のセル範囲「E3:E13」を選択後、下図のように数式を入力します。合計のセルD13を絶対参照で参照します。数式のD13にカーソルがある状態で[F4]キーを押すことで絶対参照とすることができます。
![割合を求める数式の入力 割合を求める数式の入力](../wp-body/wp-content/uploads/2017/08/eq_3_17.gif)
10. [Ctrl]+[Enter]を押して数式を一括入力します。下図のように割合が求まります。
![割合の算出 割合の算出](../wp-body/wp-content/uploads/2017/08/eq_3_18.gif)
11. 割合の表示形式を整えて、集計表の完成です。
![Q2の集計表 Q2の集計表](../wp-body/wp-content/uploads/2017/08/eq_3_19.gif)
FREQUENCY関数を用いる場合の作成手順
1. 集計表と上限値の枠を用意します。
![集計表と上限値の枠 集計表と上限値の枠](../wp-body/wp-content/uploads/2017/08/eq_3_20.gif)
2. 上限値を入力します。次の階級の下限値より少しだけ小さい値を入力します。「90分以上」については上限値を入力しません。
![上限値の入力 上限値の入力](../wp-body/wp-content/uploads/2017/08/eq_3_21.gif)
3. FREQUENCY関数を用いて度数分布を求めます。FREQUENCY関数は、[データ配列]のデータに対し、[区間配列]に指定した各階級の上限値で度数分布を求める関数で、複数のセルに結果を出力する配列数式です。
FREQUENCY(データ配列, 区間配列)
集計表内のセル範囲「D3:D12」を選択後、下図のように数式を入力します。[データ配列]にQ2の回答データ範囲、[区間配列]に上限値のデータ範囲を指定します。
![件数を求めるFREQUENCY関数の入力 件数を求めるFREQUENCY関数の入力](../wp-body/wp-content/uploads/2017/08/eq_3_22.gif)
4. [Ctrl]+[Shift]+[Enter]を押して配列数式として入力します。下図のように度数分布が求まります。
![件数の算出 件数の算出](../wp-body/wp-content/uploads/2017/08/eq_3_23.gif)
5. SUM関数で件数の合計を求めます。ショートカットキーを利用すると入力が簡単です。D13セルで[Shift]+[Alt]+[=]を押し、[Ctrl]+[Enter]を押します。合計が200となり、サンプルサイズに等しいことを確認します。
![合計を求めるSUM関数の入力 合計を求めるSUM関数の入力](../wp-body/wp-content/uploads/2017/08/eq_3_24.gif)
6. 割合を求めます。集計表内のセル範囲「E3:E13」を選択後、下図のように数式を入力します。合計のセルD13を絶対参照で参照します。数式のD13にカーソルがある状態で[F4]キーを押すことで絶対参照とすることができます。
![割合を求める数式の入力 割合を求める数式の入力](../wp-body/wp-content/uploads/2017/08/eq_3_25.gif)
7. [Ctrl]+[Enter]を押して数式を一括入力します。下図のように割合が求まります。
![割合の算出 割合の算出](../wp-body/wp-content/uploads/2017/08/eq_3_26.gif)
8. 割合の表示形式を整えて、集計表の完成です。
![Q2の集計表 Q2の集計表](../wp-body/wp-content/uploads/2017/08/eq_3_27.gif)
ピボットテーブル機能を用いる場合の作成手順
1. 「データ」シートでセル範囲「A14:AK214」を選択後、Excel のリボンから[挿入]→[ピボットテーブル]→[ピボットテーブル]を選択します。
![「ピボットテーブルの作成」ウィンドウ 「ピボットテーブルの作成」ウィンドウ](../wp-body/wp-content/uploads/2017/08/eq_3_28.gif)
このまま[OK]ボタンをクリックします。
![ピボットテーブル用新規ワークシート ピボットテーブル用新規ワークシート](../wp-body/wp-content/uploads/2017/08/eq_3_29.gif)
2. 「ピボットテーブルのフィールドリスト」から「行フィールド」へ「Q2」をドラッグします。
![Q2を行フィールドへドラッグ Q2を行フィールドへドラッグ](../wp-body/wp-content/uploads/2017/08/eq_3_30.gif)
3. A4セルを選択後、Excel のリボンから[オプビョン]→[グループの選択]をクリックします。
![「グループ化」ウィンドウ 「グループ化」ウィンドウ](../wp-body/wp-content/uploads/2017/08/eq_3_31.gif)
[先頭の値]を0、[末尾の値]を100とし、[OK]ボタンをクリックします。グループを設定することにより、一定間隔毎の集計を行うことができるようになります。
![Q2にグループを設定 Q2にグループを設定](../wp-body/wp-content/uploads/2017/08/eq_3_32.gif)
4. 「ピボットテーブルのフィールドリスト」から「値フィールド」へ「Q2」をドラッグします。
![Q2を値フィールドへドラッグ Q2を値フィールドへドラッグ](../wp-body/wp-content/uploads/2017/08/eq_3_33.gif)
5. もう一度、「ピボットテーブルのフィールドリスト」から「値フィールド」へ「Q2」をドラッグします。
![再度Q2を値フィールドへドラッグ 再度Q2を値フィールドへドラッグ](../wp-body/wp-content/uploads/2017/08/eq_3_34.gif)
6. B3セル「データ」をC3セル「集計」にドラッグし、集計項目を縦並びから横並びに変更します。
![集計項目を縦並びから横並びに変更 集計項目を縦並びから横並びに変更](../wp-body/wp-content/uploads/2017/08/eq_3_35.gif)
7. C4セルを選択後、Excel のリボンから[オプション]→[計算の種類]→[総計に対する比率]を選択します。
![計算の種類を総計に対する比率に変更 計算の種類を総計に対する比率に変更](../wp-body/wp-content/uploads/2017/08/eq_3_36.gif)
8. ラベルと割合の表示形式を整えて、集計表の完成です。
![Q2の集計表 Q2の集計表](../wp-body/wp-content/uploads/2017/08/eq_3_37.gif)
補足
今回はExcel 2010を用いましたが、他のバージョンのExcelでも同様の集計を行うことが可能です。
ダウンロード
このページのExcel ファイルのダウンロードはこちらから → enqete_3.xlsx
このファイルは、エクセル統計の体験版に対応しています。
関連リンク
単回答・複数回答・数量回答の単純集計やクロス集計を行う場合、アンケート集計ソフト「秀吉Dplus」がおすすめです。