[小西ホームページ]   [目次・索引]   [前の授業]   [次の授業]

情報処理技法(リテラシ)II 第4回

目次
索引

Excel(3)

アンケートの例

レポートや論文では、統計データを利用すると、説得力が高まります。 しかし、卒業論文を書こうとしているのに、統計データがまったく見つからないこともあるでしょう。 その場合は、アンケート調査が必要になります。 ここでは、アンケート調査を行ったとして、Excelでアンケートの結果を集計する方法について説明します。

学科、専攻によっては、SPSSやRなどの統計ソフトを利用して、アンケートの結果を分析します。 しかし、基本的な分析であれば、Excelで十分可能です。

仮に、小学生の好きな科目について、以下のように予測したとします。

そして、これらの予測を確かめるために、小学5年生10人に対して、以下のアンケートを行ったとします。 (10人というのは説明のためで、本当はもっと人数が必要です。)

小学5年生の好きな科目アンケート

問1 [性別] あなたは男女のどちらですか。

1. 男子      2. 女子

問2 [科目] あなたは、国語、算数、理科、社会の中で、どれが一番好きですか。

1. 国語    2. 算数    3. 理科    4. 社会

問3 [体育] あなたは体育が好きですか。

1. 好き      2. 嫌い

そして、以下のようなアンケート結果が得られたとします。

アンケートの結果(架空の統計データ)
児童 性別 科目 体育
1 2 1 2
2 2 1 1
3 2 2 1
4 2 3 1
5 1 2 1
6 1 3 1
7 2 2 2
8 1 2 2
9 1 4 1
10 2 1 1

この表の児童の番号は、アンケート用紙を回収したときに振った通し番号です。

度数分布表

アンケートの結果を集計する方法には、単純集計とクロス集計があります。

単純集計は、このアンケートなら、好きな科目について、国語が何人で何パーセント、算数が何人で何パーセント、とまとめることです。 以下のような表を、 度数分布表 frequency distribution table )とよびます。

度数分布表の例
科目 人数 割合
算数 4 40%
国語 3 30%
理科 2 20%
社会 1 10%
合計 10 100%

度数分布表の人数の部分は、データによって個数や回数になります。 それらを一般的に 度数 frequency )とよびます。 割合の部分を 相対度数 relative frequency )とよびます。

度数分布表は、普通は度数の多い順に並べます。 ただし、順番に意味がある場合は、その順番にします。

クロス集計表

一方、クロス集計は、このアンケートなら、

とまとめることです。

クロス集計表 cross tabulation table )とは、一方のデータ(今回は性別)の項目(男子、女子)を縦に並べ、もう一方のデータ(体育)の項目(好き、嫌い)を横に並べ、項目の交わる部分に対応するデータの度数を書き込んだ表です。

クロス集計表の例
性別 体育 合計
好き 嫌い
男子 3 1 4
女子 4 2 6
合計 7 3 10

クロス集計表は、 分割表 contingency table )や クロス表 cross table )ともよばれます。

上記のクロス集計表から、何が読み取れるでしょうか。 体育の好きな男子が3人、女子が4人だから、女子のほうが男子より体育が好きだと言っていいでしょうか。 そもそも、アンケート調査をした人数が男女で異なっているので、人数を比較するのではなく、割合を比較すべきです。 この割合、すなわち度数÷合計を、 相対度数 relative frequency )と呼びます。 クロス集計表に相対度数を追加すると、次のようになります。

相対度数(横比)を追加したクロス集計表
性別 体育 合計
好き 嫌い
男子 3
75%
1
25%
4
100%
女子 4
67%
2
33%
6
100%
合計 7
70%
3
30%
10
100%

これで、体育の好きな男子が75%、女子が67%だから、男子のほうが女子より体育が好きだと言えます。 (もちろん、このような結論を出すには、アンケートの人数が少なすぎますが。)

なお、今回の相対度数は、度数を横方向の合計で割りました。 これを横比と言います。 度数を縦方向の合計で割った、縦比の相対度数も考えられます。

相対度数(縦比)を追加したクロス集計表
性別 体育 合計
好き 嫌い
男子 3
43%
1
33%
4
40%
女子 4
57%
2
67%
6
60%
合計 7
100%
3
100%
10
100%

さらに、度数を全度数で割った相対度数もあり得ます。

相対度数(全体比)を追加したクロス集計表
性別 体育 合計
好き 嫌い
男子 3
30%
1
10%
4
40%
女子 4
40%
2
20%
6
60%
合計 7
70%
3
30%
10
100%

横比、縦比、全体比のどの相対度数を使うべきかはデータによりますが、多くの場合、横比が使われます。

Excelによる度数分布表

それでは、Excelで度数分布表を作成しましょう。 次のExcelファイルをダウンロードしてください。

lit2_04_data.xlsx

まず、好きな科目の度数分布表を作成します。 「科目」の列の「1」、「2」、「3」、「4」それぞれの個数を数えればいいですが、これはExcelのCOUNTIF関数を使えばできます。 この関数は、

COUNTIF(範囲, 検索条件)

という形式で、範囲の中から検索条件に一致するデータの個数を数えます。

Excelシートの余白(例えばセルF1からG5まで)に、「科目」、「人数」、そして科目の番号(1, 2, 3, 4)を入力してください。

度数分布表の作成(1)
度数分布表の作成(1)

そして、セルG2に=COUNTIF($C$2:$C$11,F2)と入力します。 このとき、F4キーを使えば、C2:C11が$C$2:$C$11に変換されます。 セルG2をクリックし、フィルハンドルをセルG5までドラッグすると、数式がコピー・アンド・ペーストされます。

度数分布表の作成(2)
度数分布表の作成(2)

度数分布表としてはこれでもよいですが、仕上げとして、人数の多い順に並び替え、科目の番号を名前にし、相対度数を計算します。

セルF1からG5までを、I1にコピー・アンド・ペーストします。 ただし、普通のペーストではなく、「形式を選択して貼り付け」をクリックし、「値」をクリックします。

度数分布表の作成(3)
度数分布表の作成(3)

値をペーストすることによって、数式の再計算を避けることができます。

セルI1からJ5までをドラッグし、リボンの「データ」をクリックし、「並べ替え」をクリックします。 「先頭行をデータの見出しとして使用する」チェックボックスをオンにし、「最優先されるキー」を「人数」にして、「順序」を「大きい順」にします。 すると、人数の多い順に並び変わります。

度数分布表の作成(4)
度数分布表の作成(4)

科目の番号を名前に置き換え、セルI6に「合計」と入力し、セルK1に「割合」と入力します。 セルJ6に=SUM(J2:J5)と入力し、合計を計算します。

度数分布表の作成(5)
度数分布表の作成(5)

そして、セルK2に=J2/$J$6と入力します。 このとき、F4キーを使えば、J6が$J$6に変換されます。 セルK2をクリックし、フィルハンドルをセルK6までドラッグすると、数式がコピー・アンド・ペーストされます。

度数分布表の作成(6)
度数分布表の作成(6)

セルの表示形式をパーセンテージにするには、セルをクリックするかセルの範囲をドラッグし、右クリックして「セルの書式設定」をクリックし、「表示形式」タブをクリックして、「パーセンテージ」をクリックします。

度数分布表の作成(7)
度数分布表の作成(7)

リボンの「ホーム」をクリックし、「罫線」ボタンで罫線をつければ、完成です。

度数分布表(1)
度数分布表(1)

Excelによるクロス集計表

次に、性別と、体育の好き嫌いのクロス集計表を作成しましょう。

Excelでクロス集計表を作成するには、Excelの関数(COUNTIFSなど)を利用しても可能ですが、ピボットテーブルの作成機能を利用したほうが簡単です。

まず、リボンの「挿入」をクリックし、「ピボットテーブル」をクリックします。 すると、「ピボットテーブルの作成」ウィンドウが開くので、「テーブルまたは範囲を選択」ラジオ・ボタンをオンにし、「テーブル/範囲」入力欄の右のボタンをクリックして、データの範囲(セルB1からD11まで)をドラッグします。 「既存のワークシート」ラジオ・ボタンをオンにし、「場所」入力欄の右のボタンをクリックして、余白(セルM1)をクリックします。

クロス集計表の作成(1)
クロス集計表の作成(1)

すると、「ピボットテーブルのフィールド」設定領域が開くので、「性別」を「行」入力欄にドラッグ・アンド・ドロップし、「体育」を「列」入力欄にドラッグ・アンド・ドロップし、「性別」を「値」入力欄にドラッグ・アンド・ドロップします。

クロス集計表の作成(2)
クロス集計表の作成(2)

すると、ピボットテーブルが作成されます。

クロス集計表の作成(3)
クロス集計表の作成(3)

ただし、この表はデータの個数を数えているのではなく、データの合計をしています。 これを切り替えるために、表の左上(セルM1)を右クリックして「値フィールドの設定」をクリックします。

「集計方法」の「個数」をクリックして、「OK」ボタンをクリックします。

クロス集計表の作成(4)
クロス集計表の作成(4)

すると、データの個数を数えた表に切り替わります。

クロス集計表の作成(5)
クロス集計表の作成(5)

次に、このピボットテーブルをコピー・アンド・ペーストして、クロス集計表らしく直しましょう。 値のみペーストしたいので、ペースト先(例えばセルR1)を右クリックし、「形式を選択して貼り付け」をクリックし、「値」をクリックします。

クロス集計表の作成(6)
クロス集計表の作成(6)

不要な文字を消し、「1」を「男子」、「2」を「女子」などと書き換えます。

クロス集計表の作成(7)
クロス集計表の作成(7)

相対度数は横比で計算します。

表を1行おきにするために、空けたい範囲(セルR4からU4まで)をドラッグし、右クリックして「挿入」をクリックします。 「セルの挿入」ウィンドウで「下方向にシフト」をクリックして、「OK」ボタンをクリックします。

クロス集計表の作成(8)
クロス集計表の作成(8)

これを繰り返して、表を1行おきにします。

クロス集計表の作成(9)
クロス集計表の作成(9)

セルS4に数式=S3/$U3を入力し、セルの表示形式をパーセンテージにします。 このセルを1行おきにコピー・アンド・ペーストすると、すべての相対度数が計算されます。

クロス集計表の作成(10)
クロス集計表の作成(10)

罫線を引いて読みやすくすれば、完成です。

クロス集計表(1)
クロス集計表(1)

演習4

[アンケート集計]仮に、小学生の放課後について、以下のように予測したとします。

そして、これらの予測を確かめるために、小学5, 6年生100人に対して、以下のアンケートを行ったとします。

小学生の放課後アンケート

問1 [学年] あなたの学年を教えてください。

(    )年生

問2 [運動部] あなたは、学校の運動部や地域のスポーツクラブに入っていますか。

1. 運動部のみ    2. スポーツクラブのみ    3. 両方入っている    4. 入っていない

問3 [学習塾] あなたは学習塾に通っていますか。

1. 通っている      2. 通っていない

このアンケートの結果をまとめたExcelファイルをダウンロードしてください。

lit2_04_report.xlsx

アンケートの結果(架空の統計データ)
児童 学年 運動部 学習塾
1 6 1 2
2 5 4 2
3 6 2 2
... ... ... ...
100 5 2 2

(1)Excelで、運動部やスポーツクラブの人数の度数分布表を作成してください。

度数分布表(2)
度数分布表(2)

(2)Excelで、学年と学習塾のクロス集計表を作成してください。 相対度数は横比で計算してください。

クロス集計表(2)
クロス集計表(2)

レポート課題

今日の演習4の答案(Excelファイル)をメールで提出してください。 差出人は学内のメール・アドレス(学生番号@cis.twcu.ac.jp)とし、宛先はkonishi@cis.twcu.ac.jpとします。 メールの本文には、学生番号、氏名、科目名、授業日(10月16日)を明記してください。


参考文献


[小西ホームページ]   [目次・索引]   [前の授業]   [次の授業]

2019年10月16日更新
小西 善二郎 <konishi@cis.twcu.ac.jp>
Copyright (C) 2019 Zenjiro Konishi. All rights reserved.