【種類別】エクセルを使ってアンケートの集計結果を整理する方法

アンケート調査は、顧客からのニーズを知るためにとても重要です。しかし、回答数の多いアンケート、それも選択肢が複数選べるものだった場合、集計するのさえ一苦労です。

そこで本記事では、選択肢形式のアンケート調査をエクセルで集計する方法を紹介します。選択肢の種類ごとの集計方法に加え、集計結果を特定の属性に沿ってまとめ、傾向を見出す方法も解説します。

※当記事の解説では2019年版のエクセルを使用しています。 メニューの表示箇所や表示形式が若干異なる場合がありますが、バージョンの違いによるものなので、類似のメニューがないか探してみてください。

エクセルで集計できるアンケート調査の種類

単一回答

単一回答とは、回答の選択肢の中から1つだけを選択するタイプのアンケートを指します。

Q3：あなたの1番好きな食べ物は？

1. カレーライス
2. ハンバーグ
3. オムライス

上のように、質問1つにつき回答は1つなので、集計もあまり難しくはありません。

複数回答

複数回答とは、回答の選択肢の中から複数選択できるタイプのアンケートを指します。

Q4：カレーライスの中で好きな具は？（複数回答可）

1. ジャガイモ
2. タマネギ
3. ニンジン
4. ピーマン
5. カボチャ
6. ナス
7. 牛肉
8. 豚肉

上の場合、質問1つへの回答が1つから8つまで可能性があり、集計が複雑になります。

クロス集計

クロス集計は純粋な回答結果の集計ではなく、2つ以上の回答結果を掛け合わせて相関を分析する集計手法です。クロス集計を使った分析には、「カレーが好きだと答えた人はハンバーグが好きな割合が高い」など、単純に回答同士を掛け合わせるものもあれば、「10歳以下の男性がカレーを好きだと答える割合が高い」など、性別や年齢などの属性と回答を掛け合わせて、ある属性を持った人の傾向を見るものもあります。

クロス集計では、ニーズの分析などに有益な相関関係をアンケートから抽出できますが、「ご飯を毎朝食べる人には犯罪者が多い」のような該当範囲が広すぎる分析や、「アイスが売れると熱中症患者が多くなる（猛暑が本当の原因）」のような疑似相関など、誤った分析結果を出してしまわないように注意しましょう。

単一回答を集計する手順

1. データを準備する

まずはアンケートの回答データを準備します。集計のシートとは別に用意するので、ここでは「Data」シートに回答を記録していきます。各選択肢に番号を振り、回答者がどの選択肢を選んだかを番号で記録しましょう。

2. 関数を入力する

次に集計をしていきます。「Count」シートに移り、各質問ごとに集計表を作ります。単一回答はCOUNTIF関数を使って集計します。COUNTIF関数は、指定した範囲内で検索条件を満たす要素の数を計測する関数です。

集計結果を記録したい欄に、「=COUNTIF(範囲,検索条件)」と記入します。今回の場合、範囲は「Data」シートのD12からD21なので、「Data!D12:D21」と記入します。後で下の欄にもコピーするため、範囲がズレないように絶対参照$を付けて、「$D$12:$D$21」とします。

検索条件は、指定した範囲の中で集計したい要素を指定します。カレーライスは1の番号が振ってあったので、同じく1の番号が振ってある「Count」シートのA2を指定する、「Count!A2」を記入します。もちろんここで「1」と記載しても同じ結果は出ますが、下の欄にコピーしたり後で見返したりする際に不都合が生じるため、同じシートの選択肢ナンバーを参照するのがおすすめです。

集計結果が出たら他の選択肢も集計し、最後にSUM関数を使って合計を記入します。

最初の欄を下にコピーしてそれぞれの集計を出した後、「=SUM(C2:C4)」と記入して出た合計が回答者の数10人と一致すれば、集計は正しく行われています。

3. 割合を算出する

次に、それぞれの回答数が全体の何割なのかを計算します。

「割合」の列を隣に作り、1つ目のカレーライスを選んだ割合を出します。割合を出す数式は各数値÷合計×100なので、そのまま「=C2/C5*100」と記入します。後でコピーするときに分母がズレないように、「=C2/$C$5*100」としておきます。これを他の欄にもコピーすればそれぞれの割合が算出できます。

4. グラフを作成する

最後に、この割合を基にグラフを作成すれば、集計結果が一目で確認できるようになります。単一回答の場合、棒グラフか円グラフを用いるのがおすすめです。

複数回答を集計する手順

1. 選択肢ごとにデータを準備する

まずは「Data」シートに回答データを入力していきます。こちらでは各質問ではなく各選択肢ごとに列を作り、その選択肢が選択されたなら1、選択されなかったなら0を記入していきます。

2. 関数を入力する

次に「Count」シートに集計を記録していきます。複数回答では、SUM関数とINDEX関数を組み合わせて各選択肢の合計を集計します。INDEX関数はセル範囲を指定する関数なので、SUM関数と組み合わせれば、指定したセル範囲内の数字を合計することができます。

集計結果を記録したい欄に「=SUM(INDEX(参照, 行番号, 列番号))」と記入します。今回の場合、参照する範囲は「Data」シートのE12からL21（1つ上の表の赤枠の部分）なので、コピーしたときにズレないように絶対参照$も加えて、「Data!$E$12:$L$21」となります。行番号は10人全員の回答を集計するので、選択した範囲内の全ての行を指定する「0」を入れます。列番号はジャガイモの回答を集めているQ4-1が、範囲内で最初の列なので「1」ですが、後でコピーしやすいように、「Count」シートの選択肢番号に合わせて「Count!A8」を指定しておきます。

3. 割合を算出する

次に、それぞれの選択肢が全体のうち何割の人に回答されたのかを計測します。

「割合」の列を隣に作り、1つ目のジャガイモを選んだ割合を出します。割合を出す数式は各数値÷全体の人数×100なので、「=C8/10*100」と記入します。今回は10人にアンケートを取ったので、全体の人数は10と記載しましたが、人数を集計したセルを選択して代用することもできます。

4. グラフを作成する

最後に、この割合を基にグラフを作成すれば、集計結果が一目で確認できるようになります。複数回答の場合、棒グラフか折れ線グラフを用いるのがおすすめです。

クロス集計の手順

1. データを準備する

まず、「Data」シートに回答データを入力していきます。回答形式によって記録方法が異なることに注意してください。単一回答の項目には回答した選択肢の番号を、複数回答の項目にはそれぞれの選択肢に回答したら1、回答しなかったら0を記載します。

2. ピボットテーブルを作る

次に、「Count」シートで集計表を作りたい欄を選び、タブの「挿入→ピボットテーブル」を選択します。

すると範囲の指定を求められるので、「Data」シートの回答データ全体を選択します。

3. 知りたいデータに合わせて、行と列、集計方法を選択する

次に知りたいデータに合わせて、行と列、集計方法を選択します。まずは単一回答のクロス集計として、Q1の性別とQ3の好きな料理の相関関係を調べてみましょう。

挿入されたピボットテーブルを選択すると出てくるワークスペースで、「列」にQ1を、「行」にQ3を、「値」にQ1を入れます。値の集計方法はデフォルトでは合計なので、選択して「値フィールドの設定」から「値フィールドの集計」を「個数」に変更します。

以上のように選択すると、自動で表の形で記載されます。デフォルトでは「列ラベル」「行ラベル」、「Q1」「Q2」などの項目名になっていて分かり辛いため、「好きな料理」「カレーライス」など具体名に書き換えておくと見やすくなります。表では列を性別、行を好きな料理として集計が行われているため、男性でカレーライスを選んだ人数や、女性でオムライスを選んだ人数が記録されています。

さらに、ワークスペースの「値フィールドの設定」から、「計算の種類」を「総計に対する比率」に選択すれば、それぞれの選択肢の割合を出すこともできます。

このクロス集計から、男性の方がカレーライスが好きで、オムライスは女性のみが好きであることが分かりました。

次に複数回答として、Q2の年齢とQ4の好きな食材の相関を調べてみましょう。

「行」にQ2、「列」には何も入れずに、Q4-1からQ4-8すべてを「値」に入れます。複数集計は1と0で記録しているので、集計方法はデフォルトの「合計」のままにしておきます。

作業の結果、上のように各年代ごとの好きな食材が表にできました。今回は母数が少ないために相関らしいものはあまり見られませんが、もっと母数の多いアンケートであれば、統計的に妥当な相関が現れることもあるでしょう。

4. グラフを作成する

最後に、割合を基にグラフを作成すれば、集計結果が一目で確認できるようになります。単一回答の場合は帯グラフを、複数回答の場合は折れ線グラフと棒グラフを組み合わせて用いるのがおすすめです。