BellCurve 統計WEB

ブログ

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

2017/08/07

カテゴリ:

Excel の関数やピボットテーブルを用いて単回答を集計する方法を紹介します。なお、ページ下部で解説に用いたExcel ファイルをダウンロードすることができます。

概要

単回答形式の質問では、回答者はもっともあてはまると思う選択肢を1つ回答します。単一回答、単数回答と呼ばれることもあります。英語では Single Answer、略してSAです。回答データは選択肢の番号(カテゴリーコード)で、1から始まる整数になります。各整数の件数を数えることが単回答の集計となります。

集計に用いるデータ

回答データの例として、「高校生の生活調査」を200人を対象に行った結果を用います。なお、データは架空の値です。

「データ」シート

ここでは、上図の「Q1.あなたは、どのようにして学校に通っていますか?」の回答データをExcel 2010を用いて以下の3種類の方法で集計します。

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

1. 集計表の枠を用意します。

集計表の枠

2. COUNTIF関数を用いて件数を求めます。COUNTIF関数は、[範囲]内で[検索条件]を満たす数値の個数を求める関数です。

COUNTIF(範囲, 検索条件)

集計表内のセル範囲「D3:D6」を選択後、下図のように数式を入力します。[範囲]に「データ」シートのQ1のデータ範囲、[検索条件]に集計表のカテゴリーコードのセルを指定します。また、選択範囲内で同じ式を利用できるように、[範囲]を絶対参照で参照します。絶対参照とするには、数式のテキストにカーソルを置いて[F4]キーを押し、$マークを付けます。

COUNTIF関数の入力

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

件数の算出

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

計の算出

5. 割合を求めます。セル範囲「E3:E7」を選択後、下図のように数式を入力します。合計セル(D7)を絶対参照で参照します。

割合を求める数式の入力

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

割合の算出

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

Q1の集計表

DCOUNT関数とデータ テーブル機能を用いる場合の作成手順

1. 集計表の枠を用意します。カテゴリーラベルは未入力とします。

集計表の枠

2. OFFSET関数を用いて「データ」シートのカテゴリーラベルを参照します。OFFSET関数は、[参照]セルから[行数]と[列数]だけ移動した先のセルの値を返します。

=OFFSET(参照, 行数, 列数)

C3セルで下図のように数式を入力します。[参照]には「データ」シートの「Q1」ラベルのセル、[行数]には集計表のカテゴリーコードのセル、[列数]には0を指定します。

OFFSET関数の入力

3. [Ctrl]+[Enter]を押して数式を確定します。

OFFSET関数の確定

4. DCOUNT関数を用いて件数を求めます。DCOUNT関数は、[データベース]のセル範囲で[条件]を満たす行で、かつ[フィールド](列)に数値が入力されている行の数を返します。[データベース]および[条件]にはラベルを含むセル範囲を指定することがポイントです。

=DCOUNT(データベース, フィールド, 条件)

D3セルに下図のように数式を入力します。[データベース]に「データ」シートの質問ラベルを含む回答データ全範囲、[フィールド]にデータ範囲の2列目を表す2、[条件]に質問ラベルとカテゴリーコードの範囲を指定します。

DCOUNT関数の入力

5. [Ctrl]+[Enter]を押して数式を確定します。

DCOUNT関数の確定

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. 割合の表示形式を整えて、集計表の完成です。

Q1の集計表

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

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

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

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

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

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

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

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

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

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

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

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

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

6. B4セルを選択後、Excel のリボンから[オプション]→[集計方法]→[データの個数]を選択します。

集計方法をデータの個数に変更

7. C4セルを選択後、同様にExcel のリボンから[オプション]→[集計方法]→[データの個数]を選択します。

集計方法をデータの個数に変更

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

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

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

Q1の集計表

補足

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

ダウンロード

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

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

関連リンク

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

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

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

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