レポートや論文では、統計データを利用すると、説得力が高まります。 しかし、卒業論文を書こうとしているのに、統計データがまったく見つからないこともあるでしょう。 その場合は、アンケート調査が必要になります。 ここでは、アンケート調査を行ったとして、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ファイルをダウンロードしてください。
まず、好きな科目の度数分布表を作成します。 「科目」の列の「1」、「2」、「3」、「4」それぞれの個数を数えればいいですが、これはExcelのCOUNTIF関数を使えばできます。 この関数は、
COUNTIF(範囲, 検索条件)
という形式で、範囲の中から検索条件に一致するデータの個数を数えます。
Excelシートの余白(例えばセルF1からG5まで)に、「科目」、「人数」、そして科目の番号(1, 2, 3, 4)を入力してください。
そして、セルG2に=COUNTIF($C$2:$C$11,F2)と入力します。 このとき、F4キーを使えば、C2:C11が$C$2:$C$11に変換されます。 セルG2をクリックし、フィルハンドルをセルG5までドラッグすると、数式がコピー・アンド・ペーストされます。
度数分布表としてはこれでもよいですが、仕上げとして、人数の多い順に並び替え、科目の番号を名前にし、相対度数を計算します。
セルF1からG5までを、I1にコピー・アンド・ペーストします。 ただし、普通のペーストではなく、「形式を選択して貼り付け」をクリックし、「値」をクリックします。
値をペーストすることによって、数式の再計算を避けることができます。
セルI1からJ5までをドラッグし、リボンの「データ」をクリックし、「並べ替え」をクリックします。 「先頭行をデータの見出しとして使用する」チェックボックスをオンにし、「最優先されるキー」を「人数」にして、「順序」を「大きい順」にします。 すると、人数の多い順に並び変わります。
科目の番号を名前に置き換え、セルI6に「合計」と入力し、セルK1に「割合」と入力します。 セルJ6に=SUM(J2:J5)と入力し、合計を計算します。
そして、セルK2に=J2/$J$6と入力します。 このとき、F4キーを使えば、J6が$J$6に変換されます。 セルK2をクリックし、フィルハンドルをセルK6までドラッグすると、数式がコピー・アンド・ペーストされます。
セルの表示形式をパーセンテージにするには、セルをクリックするかセルの範囲をドラッグし、右クリックして「セルの書式設定」をクリックし、「表示形式」タブをクリックして、「パーセンテージ」をクリックします。
リボンの「ホーム」をクリックし、「罫線」ボタンで罫線をつければ、完成です。
次に、性別と、体育の好き嫌いのクロス集計表を作成しましょう。
Excelでクロス集計表を作成するには、Excelの関数(COUNTIFSなど)を利用しても可能ですが、ピボットテーブルの作成機能を利用したほうが簡単です。
まず、リボンの「挿入」をクリックし、「ピボットテーブル」をクリックします。 すると、「ピボットテーブルの作成」ウィンドウが開くので、「テーブルまたは範囲を選択」ラジオ・ボタンをオンにし、「テーブル/範囲」入力欄の右のボタンをクリックして、データの範囲(セルB1からD11まで)をドラッグします。 「既存のワークシート」ラジオ・ボタンをオンにし、「場所」入力欄の右のボタンをクリックして、余白(セルM1)をクリックします。
すると、「ピボットテーブルのフィールド」設定領域が開くので、「性別」を「行」入力欄にドラッグ・アンド・ドロップし、「体育」を「列」入力欄にドラッグ・アンド・ドロップし、「性別」を「値」入力欄にドラッグ・アンド・ドロップします。
すると、ピボットテーブルが作成されます。
ただし、この表はデータの個数を数えているのではなく、データの合計をしています。 これを切り替えるために、表の左上(セルM1)を右クリックして「値フィールドの設定」をクリックします。
「集計方法」の「個数」をクリックして、「OK」ボタンをクリックします。
すると、データの個数を数えた表に切り替わります。
次に、このピボットテーブルをコピー・アンド・ペーストして、クロス集計表らしく直しましょう。 値のみペーストしたいので、ペースト先(例えばセルR1)を右クリックし、「形式を選択して貼り付け」をクリックし、「値」をクリックします。
不要な文字を消し、「1」を「男子」、「2」を「女子」などと書き換えます。
相対度数は横比で計算します。
表を1行おきにするために、空けたい範囲(セルR4からU4まで)をドラッグし、右クリックして「挿入」をクリックします。 「セルの挿入」ウィンドウで「下方向にシフト」をクリックして、「OK」ボタンをクリックします。
これを繰り返して、表を1行おきにします。
セルS4に数式=S3/$U3を入力し、セルの表示形式をパーセンテージにします。 このセルを1行おきにコピー・アンド・ペーストすると、すべての相対度数が計算されます。
罫線を引いて読みやすくすれば、完成です。
[アンケート集計]仮に、小学生の放課後について、以下のように予測したとします。
そして、これらの予測を確かめるために、小学5, 6年生100人に対して、以下のアンケートを行ったとします。
小学生の放課後アンケート
問1 [学年] あなたの学年を教えてください。
( )年生
問2 [運動部] あなたは、学校の運動部や地域のスポーツクラブに入っていますか。
1. 運動部のみ 2. スポーツクラブのみ 3. 両方入っている 4. 入っていない
問3 [学習塾] あなたは学習塾に通っていますか。
1. 通っている 2. 通っていない
このアンケートの結果をまとめたExcelファイルをダウンロードしてください。
児童 | 学年 | 運動部 | 学習塾 |
---|---|---|---|
1 | 6 | 1 | 2 |
2 | 5 | 4 | 2 |
3 | 6 | 2 | 2 |
... | ... | ... | ... |
100 | 5 | 2 | 2 |
(1)Excelで、運動部やスポーツクラブの人数の度数分布表を作成してください。
(2)Excelで、学年と学習塾のクロス集計表を作成してください。 相対度数は横比で計算してください。
今日の演習4の答案(Excelファイル)をメールで提出してください。 差出人は大学発行のメール・アドレス(学生番号@cis.twcu.ac.jp)とし、宛先はkonishi@cis.twcu.ac.jpとします。 メールの本文には、学生番号、氏名、科目名、授業日(10月13日)を明記してください。