エクセルでアンケート集計を自動化する方法|関数の基本とマクロで回答ファイルを一括集計する手順

アンケートを回収したものの、集計に何日もかかってしまう。
設問ごとの件数やクロス集計を、毎回手作業で作り直している方も多いはずです。

結論から言うと、回答が数十件ならエクセルの関数とピボットテーブルで十分に集計できます。
一方、1人1ファイルで回収した回答が何十件もあるなら、マクロ(VBA)で取り込みから集計までを自動化するのが近道です。

この記事では、次のことがわかります。

• エクセルでできるアンケート集計の種類
• COUNTIF関数とピボットテーブルを使った集計の手順
• 回答ファイルが多いときに、マクロで一括集計する流れ
• セルネッツが公開しているサンプル(アンケート用紙と回答データ作成ツール)の使い方

これまで弊社では、様々なExcel業務の効率化をお手伝いさせていただきました。

中でも、「アンケート調査」によるデータ集計・分析レポート作成については、多くのご依頼をいただきました。

その背景は特別な理由がある訳でもなく、低料金で手軽に収集データを集計するには、Excelが最も向いているという事なのだと思います。

一方で、Excelマクロの実際の動作サンプルについては、「現物すら見たことがない。」という方も少なくありません。

そこで本コラムでは、集計の基本に加えて、弊社のExcelマクロ(VBA)のサンプルも公開しています。
マクロの動きを、実際に手元で体感してみてください。

エクセルでできるアンケート集計は3種類

アンケートの集計は、単純集計・クロス集計・自由回答の集計の3つに分けられます。
目的に合わせて、使う機能を選びます。

種類	わかること	エクセルで使う機能
単純集計	設問ごとに、どの選択肢が何件・何%選ばれたか	COUNTIF関数、SUM関数
クロス集計	年齢・職業などの属性ごとに、回答がどう違うか	COUNTIFS関数、ピボットテーブル
自由回答の集計	感想・ご意見にどんな内容が多いか	フィルター、COUNTIF関数(キーワードを数える)

どの集計も、最初に「1行=1人の回答、1列=1つの設問」の一覧表(ローデータ)を作るのが前提です。
この一覧表づくりが、手作業では最も時間のかかる部分です。

関数とピボットテーブルで集計する手順

一覧表ができていれば、集計そのものは関数とピボットテーブルで短時間に終わります。

単一回答(1つだけ選ぶ設問)はCOUNTIF関数で数える

「満足」「やや満足」などの選択肢ごとに、=COUNTIF(回答の列,”満足”) のように件数を数えます。
件数を回答者数で割れば、構成比(%)も出せます。

複数回答(いくつでも選べる設問)は「1と0」で入力してSUM関数で合計する

選択肢ごとに列を分け、選んだら1、選ばなければ0を入力します。
各列をSUM関数で合計すると、選択肢ごとの件数になります。
1つのセルに「A,C」のようにまとめて入力すると集計しにくいので、避けてください。

クロス集計はピボットテーブルが早い

一覧表を選んで[挿入]→[ピボットテーブル]を選びます。
行に属性(年齢など)、列に回答、値に回答の個数を置きます。
決まった形の表に数字を入れたいときは、COUNTIFS関数で条件を2つ指定して数えます。

項目ごとに合計を出す方法は、エクセル集計のやり方4選と自動化すべきかの判断基準で詳しく解説しています。

回答ファイルが何十件もあるならマクロで一括集計する

エクセルのアンケート用紙を配って1人1ファイルで回収すると、一覧表への転記が大きな負担になります。
この転記こそ、マクロ(VBA)で自動化しやすい作業です。

マクロで自動化すると、たとえば次の流れをボタン1つで実行できます。

1. 回収した回答ファイルを、1つのフォルダにまとめて置く
2. マクロがフォルダ内のファイルを順に開き、回答を一覧表に1行ずつ書き出す
3. 一覧表から、設問ごとの集計表とグラフを作る
4. 報告用のシートやPDFとして出力する

手作業との違いをまとめると、次のとおりです。

比べること	手作業(関数・ピボット)	マクロ(VBA)で自動化
回答ファイルの取り込み	1件ずつ開いてコピー・貼り付け	フォルダ内のファイルをまとめて取り込む
転記ミス	件数が多いほど起きやすい	同じ手順で処理するので起きにくい
毎回の集計	そのたびに表を作り直す	同じ形式なら繰り返し使える
向いているケース	回答が少ない、1回だけの調査	回答ファイルが多い、定期的に行う調査

VBAで項目ごとに集計する具体的な書き方は、連想配列でグループ集計を効率化で紹介しています。
大量のデータ集計をマクロで効率化した例として、官公庁の統計白書作成における改善事例もご覧ください。

サンプルで試す:アンケート用紙と回答データ作成ツール

マクロの動きは、実際に触ってみるのが一番わかりやすいです。
弊社のサンプルを、プログラム・ソースごと公開しています。

Excelアンケート回答データ生成ツール ※ソース公開版

まず、実際の動作を体感いただく為には、「Excelアンケート用紙」や「Excelアンケート回答データ」が必要ですので、以下のSampleをダウンロードいただき、まずは、実際の処理速度を体感ください。

サンプルをダウンロードする anketo_kaito_make.zip【206KB】

解凍後Fileについて

この度の「Excelマクロ(VBA)ツール」は、以下、3つの目的により、[ソース公開版]といたしました。

1. 「操作性を体感いただきたい」
2. 「処理速度を体感いただきたい」
3. 「有用性を知っていただきたい」

セルネッツ流コーディングを公開します。
プログラム・ソースは、セルネッツExcelVBA標準化ガイドラインに沿った記述となり、「勉強用」カリキュラムなどで、お役立て頂ければ光栄です。

以下の構成がフォルダ内に展開されます。

1. 01_OUTフォルダ:作成した回答データの出力先
2. ★アンケート用紙Sample【第006版】.xlsx:セミナー受講者アンケートの用紙(集計シート付き)
3. 01_アンケート回答データ作成ツール【第011版】.xlsm:架空の顧客96人分のデータをもとに、回答済みのアンケートブックを作るマクロ

ダウンロード後の操作手順

1. zipファイルを展開し、「01_アンケート回答データ作成ツール」を開く
2. MENUシートの[11:アンケート回答Dummyデータ作成]ボタンを押す
3. [入力元]で「★アンケート用紙Sample」を、[出力先]で「01_OUTフォルダ」を選ぶ
4. 作成する件数(最大999件)を入力し、[確定]を押す
5. 01_OUTフォルダに、回答済みのアンケートブックが指定した件数だけ作られる

できあがったファイルは、「1人1ファイルで回収したアンケート」と同じ状態です。
これを手作業で一覧表にまとめる手間を想像すると、自動化の効果がつかみやすくなります。

【マクロが動かない場合】インターネットから取得したファイルのマクロは、既定でブロックされます。
ファイルを右クリックして[プロパティ]を開き、[全般]タブの[許可する](ブロック解除)にチェックを入れてください。
(参考:Microsoft Learn「インターネットからのマクロは、Office で既定でブロックされます」)

アンケート集計を自動化するときの注意点

自動化の成否は、アンケート用紙の作り方で決まります。

• 用紙の形式をそろえる:回答者が行や列を追加すると、マクロが回答の位置を見つけられなくなります。入力欄以外はシートの保護をかけておきます。
• 表記ゆれを防ぐ:「満足」と「まんぞく」のように表記が割れると、正しく数えられません。選択式の設問はプルダウンにします。
• Web版のExcelではマクロが動かない:ブラウザで開くExcel for the webでは、VBAは実行できません。デスクトップ版のExcelで使います。
• 元の回答ファイルを残す:集計のやり直しや確認ができるよう、取り込んだ後もファイルは消さずに保管します。

プルダウンの作り方は、エクセルでプルダウン連動・自動入力する方法で解説しています。
(参考:Microsoft Learn「Office スクリプトと VBA マクロの違い」)

よくある質問

GoogleフォームやMicrosoft Formsと、エクセルのどちらで集めるべきですか?

Webで回答を集められるなら、フォームのほうが転記の手間はかかりません。
一方、紙やエクセルの用紙で回収している場合や、社内の決まった書式で集計したい場合は、エクセルとマクロの組み合わせが向いています。

自由回答(感想・ご意見)はどう集計すればよいですか?

まず回答を一覧にし、内容ごとに「価格」「対応」などの分類を付けます。
分類を付けたあとは、選択式の設問と同じようにCOUNTIF関数で数えられます。

回答が数千件を超える場合も、エクセルで大丈夫ですか?

集計だけなら、エクセルでも数千件は十分に扱えます。
ただし、複数人で同時に入力する場合や過去の調査も蓄積していく場合は、Accessなどのデータベースも検討します。
選び方は、ExcelとAccessの違いを徹底比較をご覧ください。

まとめ|回答ファイルが多いならマクロで自動化

アンケートの集計は、回答を1行=1人の一覧表にまとめることから始まります。
一覧表ができれば、COUNTIF関数とピボットテーブルで単純集計もクロス集計もできます。

1人1ファイルで回収した回答が多い場合や、同じ調査を定期的に行う場合は、マクロで取り込みから集計までを自動化すると効果的です。
まずはサンプルで、マクロの動きを体感してみてください。

関連する記事

• 「開発でエクセルとアクセスのどっちを使う」論争の決着
• 業務システム開発で、AccessかExcelかで迷ったら?!
• Excelシステムって複数人でも使用できるの?
• Excelシステム開発の長所と短所