アンケートは、ユーザーからの声を一度に大量に集めるのに適したリサーチ手法です。内製・外注どちらでも手軽に始められるのも魅力のひとつです。
しかし、アンケート調査について、以下のような相談を受けることがよくあります。
- アンケート調査を実施したものの、集計作業にとても時間がかかっている…
- 集計はどうにか終えることができたけど、もっと効率的に進められないか？
解決方法はいくつかありますが、大きく分けて以下の3つの方法が考えられます。
- 集計機能が付いたアンケートサービスを利用する
- データの加工、集計に強いツールを使用する
- 作業をアウトソーシングする（組織内の熟達者へ依頼、スキルシェアサービスへの外注など）
この記事では、「データの加工、集計に強いツールを使用する」に焦点を当て、特にExcel関数を用いたアンケート結果集計についてわかりやすく解説します。
アンケート結果の集計に役立つExcel関数
Excelでアンケートを集計する場合のポイントは3つあります。アンケートの結果を入力したデータを加工する視点、アンケート結果を比較するための分類項目はどれかという視点、アンケートのどの項目を集計するのかという視点です。それぞれでExcelの計算方法は変わってきます。
まず、入力結果を加工する関数と、分類しながら集計する関数を紹介します。
なお、ここからはサンプルデータをもとに解説を進めます。
DATEDIF関数
アンケートに入力されているデータに生年月日や入社年月日がある場合は、ある時点での年齢や在籍年数を求めることができます。そのために使うのがDATEDIF関数です。
DATEDIF関数の書式は次の通りです。
=DATEDIF(数え始める日,数え終わる日,年か月か日か)年齢を求める場合、「数え始める日」は、生年月日になります。「数え終わる日」は、いつ時点の年齢を求めかの日付になり、今日であれば「TODAY()」とTODAY関数を指定します。「年か月か日か」には、年ならば「”Y”」、月ならば「“M”」、日なら「“D”」と指定します。年齢を求める場合は年なので「“Y”」と指定します。
セルB2に生年月日が入力されており、その今日時点の年齢を求めるには次の計算式になります。
=DATEDIF(B2,TODAY(),”Y”)セルB2に生年月日が入力されており、「2022年1月1日」時点の年齢を求めるには次の計算式になります。
=DATEDIF(D2,”2022/1/1”,”Y”)このように、決まった日付を””（ダブルクォーテーション）で囲むことによって、指定することもできます。
FLOOR関数
年齢を年代別に分類する場合、年齢を年代に変換する場合は、28を20、41を40、65を60とすればいいので年齢を10で切り捨てれば年代を表す数値になります。その切り捨てるための関数がFLOOR関数で書式は次の通りです。
=FLOOR(数値,切り捨てる数値)年齢がセルG2に入力されている場合に年代の数値を求めるには、次の計算式になります。
=FLOOR(G2,10)COUNTIF関数
アンケートの結果を集計するのに最も多く使うのがCOUNTIF関数です。指定した値の個数を数えます。書式は次の通りです。
=COUNTIF(範囲,指定する値)D列に「はい」「いいえ」の値が入力されていて、セルJ2に「はい」と入力されている場合の、「はい」の回答数を数えるには、次の計算式になります。
=COUNTIF(D:D,J2)こちらはサンプルデータにありませんが、
- 10段階評価の結果がX列に入力されている
- セルY2に入力されている値以上の回答数を求めたい
そんな時には、次の式で計算可能です。
=COUNTIF(X:X,”>=”&Y2)また、こちらもサンプルデータ外のお話ですが
- テキストによる回答がAA列に入力されている
- セルAB2に「よかった」と入力されている
- AA列の中の「よかった」が含まれる個数を求めたい
そんな場合は、何かの文字列を表す「*」（ワイルドカード）を、指定する文字列の前後に入力するのが便利です。
=COUNTIF(AA:AA,”*”&AB2&”*”)今回は「含む」という条件だったので、「*」が指定する値の前後に入りました（*検索したい文字列*）。
たとえば、前方一致の場合は「検索したい文字列*」、後方一致の場合は「*検索したい文字列」のように、ワイルドカードの置き方次第でコントロール可能です。
また、アンケートで複数回答項目があるときに「りんご/ばなな/ぶどう」のように1つのセルにいくつもの値を記入する場合もありますが、この場合も「りんごを含む」という条件を作る時はCOUNTIF関数を使います。
COUNTIFS関数
COUNTIF関数は数える条件が1つしか指定できません。
「性別ごとにはいと答えた人数」を集計する場合は「性別」「はい」が条件になります。「年代別の好きな商品」は「年代」「商品」が条件になります。
このように分類して集計する場合には複数の条件が必要となりますので、COUNTIF関数でなくCOUNTIFS関数を使います。
基本の使い方はCOUNTIF関数と同じく、範囲と指定した値を指定しますが、COUNTIFS関数ではそれをどんどん追加し指定していく形になります。
=COUNTIF(1つ目の範囲,1つ目の指定する値,2つ目の範囲,2つ目の指定する値,3つ目の範囲,3つ目の指定する値,…)H列に年代、E列に「はい」「いいえ」での回答があり、セルK2に40と入力されている場合をイメージしてください。40代の人が「はい」と答えた数をカウントしたいので、以下の計算式になります。
=COUNTIFS(H:H,K2,E:E,”はい”)分類をしない場合はCOUNTIF関数、分類をしなければいけない集計はCOUNTIFS関数と覚えておくとよいでしょう。
Excelの機能を使ったアンケート集計の例
Excelでデータを分類して集計するためには、関数を使った計算式を作る方法の他に、ピボットテーブルというデータ集計に特化した機能があります。分類しながら集計をするということも簡単にできます。
ピボットテーブルでデータを集計する場合は、あらかじめ入力したアンケートデータを整備しておかなければなりません。
A列からC列に回答者の属性となる個人情報、D列からF列までに「はい」「いいえ」からなる設問の回答があったら、G列に生年月日からDATEDIF関数を使って年齢を、H列に年齢からFLOOR関数を使って年代を求めておきます。
挿入タブからピボットテーブルをクリックします。
次のようなウィンドウが表示されるので、そのままOKします。
新しいシートが1枚挿入され、画面の右側に「ピボットテーブルのフィールド」の作業ウィンドウが表示されます。ここには元データの項目名の一覧が表示されています。
今回は、性別ごとに設問1の答えを集計したいと思います。
この場合、「性別」に分類し、また「設問1」の「はい」「いいえ」で分類した、「人数」を数えることになります。「性別」は縦に男女別、「設問」は横にはい、いいえと並ぶようにします。
人数を数えるのですが、項目名は「人数」という項目はありません。ピボットテーブルでは人数のように、そのデータの行数を数える場合、すべての行に確実に記録されている項目を使います。
今回は「氏名」が最も適切でしょう。
その場合は、「氏名」を右クリックして「値に追加」します。同様に「設問1」を「列ラベル」に追加、「性別」を「行ラベルに追加」します。
セル上に、男女別に設問1の回答人数が「はい」「いいえ」の分類で仕分け集計されています。
さらにこの状態から「年代」を行に追加すると、男女別、年代別に設問1の回答人数が「はい」「いいえ」の分類で仕分け集計された表に変更になっています。
このような集計を「クロス集計」と呼びます。
アンケート結果の集計に使える手段・ツール
アンケートの集計をするために、手で計算するのはとても非効率的ですので、PCを使う必要がなります。Excelなどの表計算ソフトで集計する、という人は少なくないと思いますが、Excel以外でも集計したデータの加工や修正を得意とするソフトウェアがあります。
ここでは、アンケート結果の集計に使える手段・ツールについて簡単にご紹介します。
Excel
ここまでの内容を踏まえると。一般的なアンケートデータの集計はExcelで対応できることがお分かりいただけたと思います。
集計のためには、計算式や関数をうまく活用して集計するのがポイントです。Excel関数の他にも「ピボットテーブル機能」も便利です。各ユーザーのニーズに合わせた切り口で集計することができます。
また、集計後はデータを分析に供しますが、作成できるグラフの種類も豊富ですので、多角的に分析できるでしょう。
Power BI
Excelではデータがセルに記入されているのでDeleteキーを押しただけでデータが消えたり並べ替えを誤操作したりすることによりデータが不正確なものになってしまう可能性があります。出来上がりの分析シートにしても、セル上にグラフ、分析文を配置することになり、どうしてもレイアウトに限界があります。また、視覚的にもどうしても単調な表計算ならではのグラフになってしまいがちです。
Power BIを使えば、きちんとデータを保護した上で、綺麗なビジュアルで多種多様なグラフを使った分析シートを作成してくれます。またAIを使った分析もでき、見逃している隠れた傾向を見つけることもできます。
SPSS
自治体や学術的な調査など、大規模なアンケートを行う際は、高度なデータ分析を行う必要があります。その場合にはより高度な機能を持つソフトウェアが必要になります。中でもSPSSは統計分析の専門家と言えるソフトウェアです。
Excelに比べると高価なこと、高度な機能を使うにはスキルが必要になりますが、大規模アンケートを何度も行う場合は、それ以上の恩恵が得られます。
Tableau Prep / Tableau Desktop
Tableau PrepやTableau Desktopには、横持ちデータを縦持ちに変換できる「ピボット」という便利な機能があります。
複数回答ありのアンケートデータなどを取り扱う際に、この機能はとても重宝されます。
プログラミング言語などでピボットをおこなおうとすると少し骨が折れますが、ワンクリックで完了できてしまう点が魅力的です。
参考｜Tableau Prep を使ったアンケートデータの整形
Exploratory
Exploratoryもデータの加工・分析・可視化に優れたツールです。
複数回答ありのアンケートデータに対して、ロング型・ワイド型という2つのデータの持ち方ができます（要するに縦持ちか横持ちかの違いです）。
ロング型とワイド型の切り替えが容易に行えるので、目的に応じた持ち替えが可能です。
参考｜アンケートで一つの質問に対して複数の回答がある場合に、回答ごとに列を作る方法
まとめ
アンケートを集計する方法についてExcelでの手法を中心に紹介しました。Excelでアンケートを集計するには2つの方法があります。一つは関数を使うこと、もうひとつはピボットテーブルを使うことです。どちらもただ集計するだけではなく、アンケート結果の傾向を掴むために必要な、「分類して集計」を行うことができます。
関数での手法ではExcel計算式の相対参照や絶対参照を使うことで、集計一覧表を作成できます。ピボットテーブルでも同じく集計一覧表を作成できます。
どちらで集計すればいいのかは、そのアンケートの結果をどうしたいかで決めることになります。
関数で作っておけば、データが追加されたと同時に集計値が再計算され、最近の結果になります。ピボットテーブルでは自動で再計算をせず「更新」の作業が必要になるので更新し忘れをすると古いアンケート結果になってしまうかもしれません。
ピボットテーブルで作成した場合、後から集計方法をすぐに変更できます。いろいろな角度で分析するのが容易なのです。関数で作る場合は計算式を決まったセルに作ってしまうため他の角度で見たいという時には計算式をもう一度作る必要があります。
アンケートに潜んでいる傾向を様々な角度で見つけたい場合はピボットテーブル、データの分析する分類が決まっていて何度もアンケートを実施して同じ角度で結果を確認したいときは関数というように使い分けると良いでしょう。
