エクセルでアンケート分析をする

概要

  • 課題⑦ではエクセルを使ってアンケートの分析をします。以下を読みながら、画面写真と同じように入力し、この通りに操作できることを確認してください。なお、入力する際は、画面写真と同じ場所(同じセル)に入力してください。
  • まずは質問ごとに基本統計(平均や分散など)を求めます。そして、その数字をもとに「この集団にはどんな特徴があるか」を考えます。次に質問を二つずつペアにして相関係数を求め、「Aな人ほどBだ」「Aな人ほどBではない」といった集団の法則を発見します。なお、これらの発見をまとめたレポートを後日提出してもらいます。
  • 課題として提出してもらうのは、「アンケート分析のシート」と「ワードで作るレポート」です。

データのダウンロード

  • 以下のリンクをクリックして、アンケートのファイルをダウンロードしましょう。ダウンロードにはパスワードが必要です。「名前を付けて保存する」際に「データ」のファイル名は「番号 氏名 アンケート」としてください。「質問項目」のファイル名は何でもいいです。

      アンケート・データ(配付終了)
      アンケート・質問項目(配付終了)
  • 下のようなメッセージが表示されたら「編集を有効にする」をクリックします。

基本統計でデータの特徴を確認する

  • アンケート分析では、まずは平均などの基本統計と呼ばれる数字を求め、全体の傾向を見てみます
  • まずは生データの下に、下のように基本統計の項目を用意しましょう。そして、問ごとにこれらの統計量を関数で求めてください。分散の関数には「var.p」を使います。合計は「Yes or No」の問だけ求めればいいです。

  • 基本統計量が計算されたら、以下の点に注意しながら、今回のアンケート結果にどんな特徴があるかを考えましょう。また、まわりの人たちと話をして、発見を共有してください。
種類 意味・解釈
平均 「5点スケール」では中立的な「どちらでもない」が3なので、これを上回っていれば「このクラスでは肯定が強い(多い)」と解釈でき、下回っていれば「このクラスでは否定が強い(多い)」と解釈できます。「Yes on No」では真ん中が0.5なので、これを上回っているか下回っているかで、同様の解釈をします。
分散 相対的なバラツキの強さを表します。この数字が小さければ「回答は平均付近に集中している」ことになり、大きければ「回答は割れている」ことになります。
合計 「Yes or No」の問では「Yes」を「1」としているので、合計が「Yesの人数」を表します。5点スケールには使いません。

相関係数を使って、変数間の関係を考える

  • まずは相関係数の表を作ります。
  • 「データ」タブにある「データ分析」のアイコンをクリックします。下のような画面が表示されるので、「相関」を選び「OK」をクリックします。

  • すると下の画面が出てきます。入力範囲では分析対象のセル範囲を指定します。
  • データがあるのは「B3からAY41」までですが、今回は表の先頭行を項目名として使うため、「B2からAY41」までを指定します。そして、「先頭行をラベルとして使用」にチェックマークを付け、「OK」を押します。

  • すると、下のように全ての質問間の相関係数表が出力されます。
  • 例えば「C4」セルを見ると、「0.659244」と表示されています。これは「Q2とQ3は正の相関があり、強さは0.65」ということです。
  • 同様に「D6」セルを見ると、「-0.68399」と表示されています。これは「Q3とQ5は負の相関があり、強さは0.68」ということです。
  • 相関係数は0から1の値を取り、「その絶対値が大きいほど、二つの相関関係は強い」と解釈されます。「相関係数がどれぐらいあれば関係が強いと言ってよいか」は分野によって異なります。理科の実験のように因果関係が明確な場合は0.8ぐらいがメド、人間の心理のように因果関係があいまいな場合は0.3ぐらいがメドになります。今回は相関係数が0.3以上の場合に「関係が強い」と読み取ることにします

  • なお、表の中には「#DIV/0!」と表示されているセルがありますが、そのセルは無視してください。Q10は全員が1と答えていますが、このように全員が同じ回答の時、相関係数は計算不能でエラーになります。

  • このままでは表全体を見にくいので、小数点以下の表示桁数を減らします。
  • まずは、相関係数が表示されている「B2からAY51」セルまでを範囲選択します。このような大きな表を範囲選択する際は、一番右下のセルから左上のセルへ範囲選択するのが簡単です。
  • そして、「ホーム」タブの「小数点以下の表示桁数を減らす」ボタンをクリックし、小数点以下2ケタまで表示されるようにします。

  • さらに、列の幅を調整するために、「A列からAY列」を選択します。そして、どこの列でもいいので、列の間にある仕切り線をマウスで動かして列の幅を調整します。

  • するとこのようになり、先ほどより見やすくなりました。

  • 相関表の見方は、この表の中から、数字の絶対値が大きいものを探します。絶対値が大きければ、それだけ相関が強いことを意味します。
  • 今回は、正の相関として「0.3以上」、負の相関として「-0.3以下」を探します。正の相関が強ければ「Aな人ほどBだ」と解釈し、負の相関が強ければ「Aな人ほどBでない」と読み取ります。

  • ただし、この膨大な表から目で探すのは大変なので、一定以上の数字に自動で色を付けてもらいましょう。まずは、相関係数が表示されている「B2セルからAY51セル」を範囲選択します。
  • そして「ホーム」タブの「条件付き書式」をクリックします。このアイコンは人によって違うかもしれません。その中の「セルの強調表示ルール」を選び、さらに「指定の範囲内」を選びます。

  • すると下のような設定画面が表示されるので、左の数字欄に「0.3」と「0.99」を入力します。
    そして、装飾を設定する右側で「ユーザー設定の書式」を選びます。

  • 設定画面の「塗りつぶし」タブで色を選べば、セルに色を付けることができます。また、「フォント」タブの「色」欄を設定すれば、文字に色を付けることができます。

  • 下の例は、「強調表示ルール」の「指定の範囲内」で「0.3~0.99」が黄色に塗りつぶされるように設定した例です。数字はこの通りに、装飾は自由に設定してください。

  • さらに、「セルの強調表示ルール」を加えます。今度は「指定の値より小さい」で「-0.3」以下が水色に塗りつぶされるように設定すると、下のようになります。

  • なお、この「強調表示ルール」を修正したければ、「条件付き書式」の「ルールの管理」を選ぶと修正できます。

  • 下の修正画面で赤線部分をダブルクリックすると、その設定を変更できます。

法則の見つけ方

  • 「基本統計」
    平均は真ん中からずれている回答を探し、分散は相対的に大きいか小さい回答を探します。合計は「Yes or No」の時だけ使い、「何人いる」という意味になります。
     平均:Q43は「平均が2.2」と3を大きく下回っている。このクラスは英語が苦手なようだ。
     分散:Q27は「分散が0.4」と最も小さい。このクラスは島根が好きであることに異論はあまり無い。
     合計:Q13は「合計が7人」。このクラスは夏が苦手なようだ。
  • 「相関係数」
    相関係数の絶対値が大きいものを探します。符号がプラスなら「Aな人ほどBだ」と解釈し、符号がマイナスなら「Aな人ほどBではない」と解釈します。そして、そのようになる理由も考えます。
    ・Q6とQ47は「相関係数が0.47」だ。兄弟がいるほど、血を見るのは平気らしい。
     おそらく○○だからだろう。
    ・Q26とQ37は「相関係数が-0.46」だ。高校の部活が運動部だった人ほど、勉強が嫌いらしい。
     おそらく○○だからだろう。

課題提出

  • 今回のファイル名はエクセル・ワードとも、「番号 氏名 アンケート」としてください。例えば「32○○アンケート」のようにします。
      締切:7月22日21時
  •  ①エクセルの基本統計と相関係数表を完成させたファイルを提出してください。
  •  ②アンケート分析の以下の内容を、WORDでレポートにして提出してください。
      ・基本統計と相関係数、各3個以上の「クラスのおもしろい(不思議な)特徴・法則」を発見する。
      ・その際、なぜそのような結果が生じたのか、それぞれの理由も考えて書いてください。
      ・また「Q1に関して」のように、どの質問のことかが分かるように番号を書いてください。
      ・レポートは表紙は不要で、必ず一番上に自分の番号と氏名を書いてください。

情報科学