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