BellCurve 統計WEB

【BellCurve監修】統計検定2級対策に最適な模擬問題集

Kindleストアで配信中Kindle電子書籍にて

「統計検定2級 模擬問題集1」&「統計検定2級 模擬問題集2」&「統計検定2級 模擬問題集3」を配信中です。

ブログ

アンケートの数量回答をExcelで集計する方法

2017/08/07

カテゴリ:

Excel の関数やピボットテーブルを用いて数量による回答を集計する方法を紹介します。なお、ページ下部で解説に用いた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回答」を指定します。

件数を求めるCOUNTIF関数の入力

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

件数の算出

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

合計を求めるSUM関数の入力

7. 割合を求めます。集計表内のセル範囲「E3:E13」を選択後、下図のように数式を入力します。合計のセルD13を絶対参照で参照します。数式のD13にカーソルがある状態で[F4]キーを押すことで絶対参照とすることができます。

割合を求める数式の入力

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

割合の算出

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

Q2の集計表

SUMPRODUCT関数を用いる場合の作成手順

1. 集計表と下限値の枠を用意します。

集計表と下限値の枠

2. 各階級の下限値を入力します。

下限値の入力

3. Q2の回答データの範囲に名前を定義します。「データ」シートのセル範囲「C15:C214」を選択後、Excel のリボンから[数式]→[名前の定義]をクリックします。「新しい名前」ウィンドウで[名前]を「Q2回答」とし、[OK]ボタンをクリックします。定義した名前は数式の引数に指定することができます。

「新しい名前」ウィンドウ

4. SUMPRODUCT関数を用いて件数を求めます。SUMPRODUCT関数は、複数の条件を満たすセルの個数を求める場合に有用な関数です。

SUMPRODUCT((範囲>=下限値)*(範囲<次の階級の下限値))

集計表内のセル範囲「D3:D11」を選択後、下図のように数式を入力します。階級「0~9分」の場合、「>=0」と「<10」の両方を満たすセルの個数を求めます。[範囲]には先ほど定義した「Q2回答」を指定します。

件数を求めるSUMPRODUCT関数の入力

5. [Ctrl]+[Enter]を押して数式を一括入力します。

件数の算出

6. 最後の階級の「90分以上」は条件が1つだけなので、他の階級とは異なる仕様でSUMPRODUCT関数を入力します。

SUMPRODUCT((範囲>=下限値)*1)

件数を求めるSUMPRODUCT関数の入力

7. [Ctrl]+[Enter]を押して数式を入力します。

件数の算出

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

合計を求めるSUM関数の入力

9. 割合を求めます。集計表内のセル範囲「E3:E13」を選択後、下図のように数式を入力します。合計のセルD13を絶対参照で参照します。数式のD13にカーソルがある状態で[F4]キーを押すことで絶対参照とすることができます。

割合を求める数式の入力

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

割合の算出

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

Q2の集計表

FREQUENCY関数を用いる場合の作成手順

1. 集計表と上限値の枠を用意します。

集計表と上限値の枠

2. 上限値を入力します。次の階級の下限値より少しだけ小さい値を入力します。「90分以上」については上限値を入力しません。

上限値の入力

3. FREQUENCY関数を用いて度数分布を求めます。FREQUENCY関数は、[データ配列]のデータに対し、[区間配列]に指定した各階級の上限値で度数分布を求める関数で、複数のセルに結果を出力する配列数式です。

FREQUENCY(データ配列, 区間配列)

集計表内のセル範囲「D3:D12」を選択後、下図のように数式を入力します。[データ配列]にQ2の回答データ範囲、[区間配列]に上限値のデータ範囲を指定します。

件数を求めるFREQUENCY関数の入力

4. [Ctrl]+[Shift]+[Enter]を押して配列数式として入力します。下図のように度数分布が求まります。

件数の算出

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

合計を求めるSUM関数の入力

6. 割合を求めます。集計表内のセル範囲「E3:E13」を選択後、下図のように数式を入力します。合計のセルD13を絶対参照で参照します。数式のD13にカーソルがある状態で[F4]キーを押すことで絶対参照とすることができます。

割合を求める数式の入力

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

割合の算出

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

Q2の集計表

ピボットテーブル機能を用いる場合の作成手順

1. 「データ」シートでセル範囲「A14:AK214」を選択後、Excel のリボンから[挿入]→[ピボットテーブル]→[ピボットテーブル]を選択します。

「ピボットテーブルの作成」ウィンドウ

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

ピボットテーブル用新規ワークシート

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

Q2を行フィールドへドラッグ

3. A4セルを選択後、Excel のリボンから[オプビョン]→[グループの選択]をクリックします。

「グループ化」ウィンドウ

[先頭の値]を0、[末尾の値]を100とし、[OK]ボタンをクリックします。グループを設定することにより、一定間隔毎の集計を行うことができるようになります。

Q2にグループを設定

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

Q2を値フィールドへドラッグ

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

再度Q2を値フィールドへドラッグ

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

集計項目を縦並びから横並びに変更

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

計算の種類を総計に対する比率に変更

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

Q2の集計表

補足

今回はExcel 2010を用いましたが、他のバージョンのExcelでも同様の集計を行うことが可能です。

ダウンロード

このページのExcel ファイルのダウンロードはこちらから → enqete_3.xlsx

このファイルは、エクセル統計の体験版に対応しています。

関連リンク

単回答・複数回答・数量回答の単純集計やクロス集計を行う場合、アンケート集計ソフト「秀吉Dplus」がおすすめです。

統計学やデータ分析を学ぶなら、大人のための統計教室 和(なごみ) [業務提携]