Microsoft Excel(エクセル)を使って、【はい・いいえ】や【とても不満・不満・どちらでもない・満足・とても満足】などのアンケート調査をした人は多いでしょう。
ただ、アンケートを作成したけれど、そのアンケートを集計する方法が分からず困っていないでしょうか？
アンケートの対象人数が多いと、目視で集計・分析するのは大変です。
この記事では、エクセル初心者向けにアンケート結果を簡単に集計する方法を紹介します。
エクセルのアンケートの種類
エクセルで作成するアンケート(questionnaire)は、主に２種類あります。
１つ目は、単一回答式のアンケートで選択肢の中から１つだけを選択するもの、２つ目は複数回答式アンケートで選択肢の中から複数の回答をＯＫとするものです。
まず、この２種類のアンケートを事例を使って紹介します。アンケートの種類によって結果を集計する方法も異なってくるので、どんなアンケートを作成するか悩んでいる方は参考にしてください。
単一回答アンケート
単一回答式アンケートは、【はい・どちらでもない・いいえ】や【とても不満・不満・どちらでもない・満足・とても満足】といった選択肢の中から、１つだけを選択して回答するアンケートです。
各設問ごとに１つだけを選択する仕様のため、下のようにプルダウン(ドロップダウン)を用いてアンケートを作成することも可能です。
エクセルのプルダウンリストを使ってアンケートを作成したい方は、以下の記事でプルダウンの設定方法を解説しているので参考にしてください。
複数回答アンケート
２つ目の複数回答式アンケートは、設問ごとに複数の回答が可能なアンケートです。
例えば、【以下の中から好きなジャンルを選択してください。(複数回答可)】といったアンケートであれば、設問によっては回答が複数個発生します。
複数回答を認める場合、「３つまで複数回答可」と用紙に記載して回答数を制約するとアンケートを集計する際、加工が簡単になります。
残念ながら複数の回答が可能なため、プルダウン機能との相性は良くありません。
また、集計するとアンケートをとった人数と回答数が一致しないため、アンケート結果を集計する際は、少し注意が必要です。
【準備】アンケート結果を１つのシートにまとめる
アンケート結果を集計するために、すべてのアンケートを１つのエクセルシートにまとめる必要があります。
単一回答式アンケートか複数回答式アンケートかによってまとめ方が異なるので、それぞれの方法を紹介します。
単一回答式アンケートの結果を１シートにまとめる方法
単一回答式アンケートをまとめる方法を紹介します。
まずは、アンケートの対象者に問１～問7の設問に〇を入力してもらい、その対象データもしくはアンケート用紙を回収します。
次に、下の図のようなアンケートまとめ表を作成します。
それぞれの設問に対して回答は１つだけなので、列項目(C列～I列)は各設問の番号を入力し、行項目(３行目～17行目)はアンケートの人数分の行数を準備します。
まとめ表のフォーマットが完成したら、アンケート結果を１つずつ確認しながら、上の行から選択されている番号を入力します。
複数回答式アンケートの結果を１シートにまとめる方法
複数回答式アンケートをまとめる方法を紹介します。
まずは、アンケートの対象者に問１～問6の設問に〇を入力してもらい、その対象データもしくはアンケート用紙を回収します。
次に、下の図のようなアンケートまとめ表を作成します。
今回のアンケートの例では、設問３、４，６は最大３つまでの複数回答をOKとしているので、入力欄をそれぞれ３列設定します。
まとめ表のフォーマットが完成したら、アンケート結果を１つずつ確認しながら、上の行から選択されている番号を入力します。
単一回答アンケートをCOUNTIF関数で集計する方法
アンケート結果を１つの結果にまとめたら、各設問の結果を集計します。
集計には、COUNTIF関数(カウント・イフ関数)という、1つの検索条件に一致するセルの数をカウントする関数を利用します。
まずは、単一回答式アンケートの結果をまとめた表の横の空いたスペースに、下の赤枠のような集計表を作成します。
また、設問ごとにアンケートした人数が合っているか確認できるようSUM関数を挿入します。
次に、C列から「１」が入力されたセルの数をカウントするために、集計表のK3セルに以下のCOUNTIF関数を挿入します。COUNTIF関数の２つ目の引数は、ダブルクォーテーション(”)を使って数値「”1”」と入力します。
=COUNTIF(C3:C17,"1")
COUNTIF関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
COUNTIF関数を確定すると、C列(問１)で「1」と回答した人の人数が「8」人ということが分かります。
同様に、集計表の残りのセルにもCOUNTIF関数を挿入すれば、集計表の完成です。あとは、横棒グラフや円グラフを用いると、集計結果を視覚的に分かりやすくまとめることができます。
複数回答アンケートをCOUNTIF関数で集計する方法
複数回答式アンケートの結果をまとめたら、各設問の結果を集計します。
単一回答式アンケートと同様に、まとめ表の横に集計表を作成し、COUNTIF関数を利用して集計します。
複数回答式アンケートの場合も同じ方法で集計できますが、カウントするセル範囲の設定だけ注意が必要です。
E列、F列、G列から「１」が入力されたセルの数をカウントする場合は、COUNTIF関数の第１引数「範囲」は、「$E$3:$G$17」と３列を指定します。
=COUNTIF($E$3:$G$17,"1")
同様に、集計表の残りのセルにもCOUNTIF関数を挿入すれば、複数回答式アンケートの集計表の完成です。
COUNTIF関数を複数作成するのは少し面倒ですが、数式を絶対参照「$」で固定してから、他のセルに数式をコピペし、置換機能を使って参照セルをまとめて変更するとCOUNTIF関数の作成を時短できます。
ピボットテーブルを使ったアンケートの集計方法
COUNTIF関数を使わなくても、ピボットテーブルを使ってアンケート結果を集計することも可能です。
ピボットテーブルは、膨大なデータを簡単に分析や集計するときに大いに役立つ便利機能です。
まず、アンケート結果をまとめた表の任意のセルをクリックしてから(図①)、「挿入」タブの「ピボットテーブル」をクリック(図➁)します。
そうすると、「テーブルまたは範囲からのピボットテーブル」画面が表示されますので、元データのデータ範囲(図③)が正しければ、「OK」をクリック(図④)します。この操作でピボットテーブルのための新しいワークシートが自動で作成されます。
画面右端に作業フィールドが自動で表示されます。
問１を集計したい場合は、「問１」にチェックを入れます(図⑤)。そうすると、右下の「値」フィールドに「問１」が移動します。さらに、「行」フィールドに「問１」をドラッグ＆ドロップします(図⑥)。
問１の集計結果が表示されますが、データの個数ではなく合計で集計されます。
集計したいのは”１”と入力されたデータの数なので、ピボットテーブルを右クリックし(図⑦)、右クリックメニューの「値の集計方法」から「データの個数」を選択します(図⑧)。
そうすると、集計方法が合計からデータの個数に変更され、下の図のように、問１は、「１」と回答した人数が８人、「２」と回答した人数が７人であることが分かりました。
上記の図⑤～図⑧の作業を問２から問７まで行うと、各設問のアンケートを集計することができます。
このようにアンケートのクロス集計にはピボットテーブルが役立ちます。さらに使い方を詳しく知りたい方は、以下のリンク記事を参考にしてください。
アンケート結果を積み上げ横棒グラフで分析する方法
アンケートのすべての設問が【不満・やや不満・どちらでもない・やや満足・満足】といった５段階評価の場合は、アンケートの集計結果を積み上げ横棒グラフで表すと、内容を分析しやすくなります。
まずは、下の表のように集計結果を１つの表に纏め、行見出しと列見出しに分かりやすい項目名をつけます。
グラフにしたい元データの範囲を選択します。合計列は選択しないよう注意してください。次に、「挿入」タブ⇒「おすすめグラフ」をクリックします。
「グラフの挿入」画面から、「すべてのグラフ」タブ⇒「横棒」⇒「100％積み上げ横棒」を選択します。軸の設定が異なる２つのグラフのうち、横軸が設問のグラフを選択(図③)し、「ＯＫ」で画面を閉じます。
そうすると、下の図のような100％積み上げ横棒グラフが挿入されます。
後は、横軸の並びを上下で逆転させたり、グラフタイトルやデータラベル、凡例、色などを調整すればグラフの完成です。
積み上げ横棒グラフの作り方を詳しく知りたい方は、以下のリンク記事を参考にしてください。
アンケート結果を円グラフで分析する方法
アンケート結果を円グラフで表すと、視覚的に回答結果が分かりやすくなります。
グラフにしたい元データの範囲を選択します。合計列は選択しないよう注意してください。次に、「挿入」タブ⇒「おすすめグラフ」をクリックします。
「グラフの挿入」画面から、「すべてのグラフ」タブ⇒「円」⇒「円」を選択します。
そうすると円グラフが挿入されます。見栄えなどの体裁を調整すればアンケート結果のグラフ化の完成です。
円グラフの作り方や色のデザインの調整方法を詳しく知りたい方は、以下のリンク記事を参考にしてください。
