Excelでアンケートの回答を集計するときは、単に件数を数えるだけではなく、回答を入力しやすい一覧に整え、設問ごとに数式やピボットテーブルで集計し、グラフや割合で見やすく伝える流れが重要です。
回答人数が少ないアンケートでも、最初に表の構造を決めておけば、回答が増えたときの追加作業を大幅に減らせます。
この記事では、満足度調査や社内アンケート、イベント後の意見収集などを想定し、1行目にヘッダーがあるサンプルデータを使って、集計表の作り方を順番に解説します。
アンケート集計表は、回答データ、設問別の集計、報告用の見せ方を分けて作ることが基本です。
回答を直接加工し続けるのではなく、元データを残したまま別の場所で集計すると、修正や追加にも対応しやすくなります。
たとえば、満足度を「とても満足」「満足」「普通」「不満」「とても不満」で集めた場合、COUNTIF関数で各回答数を出し、全体数で割れば割合まで確認できます。
回答割合の基本式
各選択肢の回答数 ÷ 回答総数
Excelでは、たとえばB2に選択肢の回答数、B7に総回答数がある場合、割合は =B2/$B$7 で求められます。
それでは、回答を集計して見やすくまとめるための実践的な方法を確認していきましょう。
エクセルでアンケート集計表を作る手順
| A | B | C | D |
|---|---|---|---|
| 回答者ID | 部署 | 満足度 | 自由記述 |
| 1 | 営業 | 満足 | 説明が分かりやすい |
| 2 | 総務 | 普通 | 時間が短い |
| 3 | 営業 | とても満足 | また参加したい |
それではまず、アンケート集計表の土台となる回答一覧と集計用シートの作り方について解説していきます。
回答データを縦方向に並べる構成
アンケート結果をExcelへ入力するときは、1人分の回答を1行にまとめ、設問を列として配置する形式が扱いやすい構成です。
1行目には必ずヘッダーを置き、A列を回答者ID、B列を部署、C列を年代、D列を満足度のように、各列の意味を明確にします。
この形式なら、途中で回答者が増えても最終行の下へ追加するだけで済み、並べ替え、フィルター、ピボットテーブルにも利用できます。
自由記述欄も同じ表へ残せますが、数値集計の対象となる選択式の回答と、文章を読むための自由記述は用途が異なるため、後で別々に整理する意識が大切です。
空白行をデータの途中に入れると、集計範囲が途中で切れたり、並べ替え時に一部の行だけが動いたりすることがあります。
回答一覧の先頭行は見出し専用にし、2行目から実際の回答を入力します。
同じ設問の回答表記は統一します。
「満足」と「満足です」が混在すると、Excelでは別の回答として数えられます。
【操作のポイント】元データの表は、集計用の数式やグラフを直接置かず、回答を蓄積する場所として保つと安全です。
テーブル機能による集計範囲の固定
回答一覧を作成したら、表内の任意のセルを選択し、挿入タブからテーブルを選択します。
表示された範囲が正しいことを確認し、「先頭行をテーブルの見出しとして使用する」にチェックを入れて作成しましょう。
テーブルに変換すると、行を追加したときに書式や数式の範囲が自動で広がるため、回答件数が増えるアンケートに向いています。
通常のセル範囲よりも、テーブル化したデータのほうがピボットテーブルの更新対象を管理しやすい点もメリットです。
テーブル名は、テーブルデザインタブの左上から変更できます。
たとえば「回答一覧」と付けておくと、数式やピボットテーブルの元データを確認するときに迷いにくくなります。
テーブル化した回答表では、フィルターボタンが各ヘッダーに表示されます。
部署だけを絞り込む、未回答だけを確認する、特定の日付の回答を探すといった作業も短時間でできます。
【操作のポイント】テーブル内で列名を変更する場合は、集計式やピボットテーブルの項目名も確認しましょう。
集計用シートと報告用シートの分離
回答一覧とは別に、「集計」「報告用」などのワークシートを作成します。
集計シートでは設問ごとの回答数、割合、部署別の傾向を計算し、報告用シートでは重要な数値とグラフを見せる構成が分かりやすいでしょう。
元データを直接見せる必要がない場合でも、回答一覧シートを削除せずに残しておけば、数値の根拠を後から確認できます。
集計シートには、たとえばA1に「満足度」、B1に「回答数」、C1に「割合」という見出しを置きます。
その下のA2からA6へ、選択肢を回答時と同じ表記で入力します。
集計用シートの例
A2 とても満足
A3 満足
A4 普通
A5 不満
A6 とても不満
このように集計の設計を先に用意しておくと、集計式を横や下へコピーしやすく、設問が増えた場合にも対応しやすくなります。
【操作のポイント】回答一覧、計算用、提出用の役割を分けると、誤って元データを上書きするリスクを減らせます。
COUNTIF関数による回答数と割合の集計
| A | B | C |
|---|---|---|
| 満足度 | 回答数 | 割合 |
| とても満足 | 12 | 24.0% |
| 満足 | 25 | 50.0% |
| 普通 | 10 | 20.0% |
続いては、選択式アンケートの回答数と構成比をCOUNTIF関数で集計する方法を確認していきます。
COUNTIF関数による選択肢別の件数
回答一覧シートのD列に満足度が入力されている場合、集計シートのB2へ次の数式を入力します。
=COUNTIF(回答一覧!$D$2:$D$51,A2)
この数式は、回答一覧シートのD2からD51の範囲で、集計シートのA2と同じ文字列が入ったセルを数えるものです。
A2が「とても満足」であれば、「とても満足」と回答した人数がB2に表示されます。
範囲の行番号は実際の回答数に合わせて調整しますが、元データをテーブル化している場合は、テーブルの列を参照する方法も便利です。
数式をB6までコピーすれば、各選択肢の人数をまとめて求められます。
回答の選択肢と集計表の選択肢は、表記も全角半角もそろえることが正確な集計につながります。
【操作のポイント】数式中の回答範囲はF4キーで絶対参照にすると、下へコピーしても範囲がずれません。
SUM関数による回答総数
各選択肢の回答数を集計できたら、B7などの合計欄で回答総数を求めます。
=SUM(B2:B6)
SUM関数はB2からB6までの値を合計するため、未回答を含めない選択肢別の合計人数を確認できます。
もしアンケートの回答者数と集計の合計が一致しない場合は、空欄、想定外の表記、複数回答の扱いを確認する必要があります。
たとえば、回答一覧に「満足 」と末尾に空白が入っていると、見た目は同じでもCOUNTIF関数では別の文字列として扱われる可能性があります。
データタブの重複の削除や、TRIM関数による余分な空白の除去も、回答データを整える場面で役立ちます。
未回答を確認したい場合は、次の式を利用できます。
=COUNTBLANK(回答一覧!$D$2:$D$51)
回答漏れの件数を別に出しておくと、集計結果の説明がしやすくなります。
【操作のポイント】回答総数は、グラフの割合や報告書の母数になるため、最初に数値が正しいか確認しましょう。
割合の算出とパーセント表示
割合を出すには、C2に各選択肢の回答数を回答総数で割る数式を入力します。
=B2/$B$7
B2は選択肢ごとの回答数で、$B$7は全選択肢の合計です。
$記号を付けた絶対参照にすることで、C2の数式をC6までコピーしても、分母が常にB7のままになります。
数式を入力しただけでは小数で表示されるため、ホームタブのパーセントスタイルを選択して、必要に応じて小数点以下の桁数を調整します。
割合は人数だけでは見えにくい傾向を把握するための重要な指標です。
回答者数が部署ごとに異なる場合も、件数ではなく割合を比較することで、より公平に傾向を判断できます。
【操作のポイント】割合の合計が表示上100パーセントにならない場合は、丸めによる差がないか確認します。
ピボットテーブルによる設問別と属性別の分析
| 部署 | とても満足 | 満足 | 普通 | 合計 |
|---|---|---|---|---|
| 営業 | 8 | 12 | 3 | 23 |
| 総務 | 4 | 13 | 7 | 24 |
| 総計 | 12 | 25 | 10 | 47 |
続いては、設問ごとの回答を部署や年代などの属性と組み合わせて確認できる、ピボットテーブルの使い方を解説していきます。
ピボットテーブルの挿入操作
回答一覧のテーブル内にある任意のセルを選択し、挿入タブからピボットテーブルを選択します。
テーブルまたは範囲には回答一覧のテーブル名が表示されていることを確認し、新規ワークシートを選んでOKをクリックします。
ピボットテーブルは、元データを複製して加工する機能ではなく、回答一覧を集計して表示するための機能です。
そのため、元データを更新した後は、ピボットテーブルを右クリックして更新を実行する必要があります。
【操作のポイント】回答一覧をテーブル化しておくと、新しい回答行を追加した後もピボットテーブルの対象範囲を広げやすくなります。
行と列へのフィールド配置
作成後の画面右側には、ピボットテーブルのフィールド一覧が表示されます。
部署別に満足度を比較したいときは、「部署」を行、「満足度」を列へドラッグします。
さらに「満足度」を値のエリアへドラッグすると、満足度の件数が部署別に集計されます。
値のエリアで合計と表示された場合は、その項目をクリックして値フィールドの設定を開き、「データの個数」を選択します。
文字列の回答を集計する場合、値の集計方法は通常「個数」を選びます。
部署を行、満足度を列、満足度を値に置く配置なら、どの部署でどの回答が多いかを一つの表で比較できます。
ピボットテーブルの代表的な配置
行 部署、年代、利用回数の区分
列 満足度、認知経路、参加意向
値 回答数、平均点、回答数に対する比率
【操作のポイント】比較したい切り口を行に置き、回答の種類を列に置くと、クロス集計表として読みやすくなります。
構成比表示とフィルターの活用
件数だけでなく構成比を表示したいときは、値エリア内の項目を右クリックし、「値の表示方法」から「行集計に対する比率」または「列集計に対する比率」を選択します。
部署ごとの満足度の内訳を比べる場合は、各部署の中での比率を表す行集計に対する比率が役立ちます。
全回答者の中での割合を知りたい場合は、総計に対する比率を選びます。
フィルターには、実施月、開催回、回答経路などを置くと便利です。
たとえば開催回をフィルターへ配置すると、特定回だけの満足度や、複数回を合わせた結果を切り替えて確認できます。
件数と割合は同じ表で混同せず、何を分母にした割合かを見出しや注記で示すことが重要です。
【操作のポイント】ピボットテーブルの数値を変更したい場合は、表示されているセルを直接書き換えず、元の回答データを修正してから更新します。
グラフによるアンケート結果の見える化
| 回答 | 人数 | 割合 |
|---|---|---|
| とても満足 | 12 | 24% |
| 満足 | 25 | 50% |
| 普通以下 | 13 | 26% |
続いては、集計した数値をグラフにして、アンケート結果をひと目で伝える方法を確認していきます。
回答数の比較に向く棒グラフ
選択肢ごとの件数を比較する場合は、集合縦棒グラフまたは横棒グラフが基本です。
集計表の「満足度」と「回答数」の範囲を選択し、挿入タブから縦棒グラフを選びます。
回答の種類が長い文章になる場合は、横棒グラフにするとラベルが読みやすくなります。
棒グラフは、どの選択肢が最も多いか、差がどれほどあるかを直感的に比較しやすい形式です。
設問の選択肢には意味のある順序があるため、「とても満足」から「とても不満」までの並びを意図どおりに整えます。
件数順に自動で並べると評価の流れが読み取りにくくなることもあるため、アンケートの目的に応じて並び順を決めましょう。
【操作のポイント】グラフのタイトルは「満足度の回答割合」のように、何を集計したグラフか分かる具体的な名称にします。
割合を伝える円グラフと積み上げ棒グラフ
全体に占める構成比を見せたい場合には、円グラフや100パーセント積み上げ縦棒グラフが利用できます。
選択肢が5個程度までで、全体の内訳を端的に示したいときは円グラフが向いています。
ただし、選択肢が多い場合や割合が近い場合は、円グラフでは違いを読み取りにくいことがあります。
部署別など複数のグループの割合を比較するときは、100パーセント積み上げ縦棒グラフが分かりやすいでしょう。
同じ高さの棒の中に、満足度別の割合が色分けされるため、部署ごとの構成の違いを比較できます。
グラフ選びの目安
選択肢別の人数比較には棒グラフ
全体の内訳を簡潔に示す場合には円グラフ
属性ごとの構成比比較には100パーセント積み上げグラフ
【操作のポイント】グラフの色は種類ごとに意味を持たせ、同じ満足度には複数のグラフで同じ色を使うと理解しやすくなります。
データラベルと注記の整備
グラフを作成した後は、グラフ右上のプラス記号からデータラベルを追加し、件数または割合を表示します。
報告資料として使う場合は、グラフだけを見ても内容を誤解しないよう、回答者数や実施期間も近くに記載します。
たとえば「回答数50件」「実施期間4月1日から4月15日」のような補足があると、数値の受け取り方が明確になります。
グラフは装飾を増やすより、数値と比較対象を読み取れる状態に整えることが大切です。
3D表示、影、過剰な色分けは見栄えがよく見えても、棒の長さや割合を正確に比較しにくくすることがあります。
必要な情報だけを残し、凡例、タイトル、データラベルの位置を調整して、見やすい報告用グラフに仕上げましょう。
【操作のポイント】円グラフでは小さな割合のラベルが重なりやすいため、データラベルの位置を「外側」にすると読みやすくなります。
自由記述と複数回答の整理方法
| 自由記述の分類 | 件数 | 代表的な意見 |
|---|---|---|
| 内容への評価 | 18 | 説明が具体的だった |
| 時間への要望 | 7 | 質疑応答を増やしてほしい |
| 資料への要望 | 5 | 後で見返せる資料がほしい |
続いては、数式だけではまとめにくい自由記述と、複数回答形式のアンケートを整理する考え方を解説していきます。
自由記述をテーマ別に分類する作業
自由記述欄は、そのまま一覧で読むだけでは全体の傾向をつかみにくいため、内容が近い意見をテーマごとに分類します。
回答一覧とは別の列に「分類」という見出しを作り、「内容」「時間」「資料」「会場」などのカテゴリを付ける方法が分かりやすいでしょう。
一つの文章に複数の要望が含まれる場合は、主な内容を一つ選ぶか、複数の分類列を使うかを先に決めます。
自由記述の分類基準を途中で変えないことが、集計結果の公平性を保つポイントです。
分類後はCOUNTIF関数やピボットテーブルでカテゴリ別の件数を数え、代表的な意見を数件だけ抜粋します。
【操作のポイント】原文を要約しすぎず、個人が特定される情報を除いたうえで、傾向が伝わる表現を残しましょう。
複数回答を列ごとに集計する方法
「知ったきっかけをすべて選んでください」のような複数回答は、選択肢ごとに列を分け、選択された場合に1を入力する形式が集計しやすい方法です。
たとえばE列を社内メール、F列を上司からの案内、G列を掲示板とし、選択されたセルへ1を入力します。
各列の最終行にSUM関数を入れれば、選択肢ごとの選択人数を求められます。
=SUM(E2:E51)
複数回答では、選択肢ごとの合計をすべて足すと回答者数を超えることがあります。
これは1人が複数項目を選べるためであり、集計ミスではありません。
【操作のポイント】複数回答の割合を出す際は、回答者数を分母にするのか、全選択数を分母にするのかを明記します。
コメントと個人情報の取り扱い
アンケートには、氏名、メールアドレス、所属、具体的な出来事など、個人が推測できる情報が含まれることがあります。
報告用の資料を共有する前に、元データと公開用の集計表を分け、不要な個人情報を掲載していないか確認しましょう。
自由記述を引用する場合は、誤字を整える程度にとどめ、意味を変える編集や発言者を特定しやすい情報の掲載は避けます。
集計表は数字を正しく出すだけでなく、回答者が安心して意見を寄せられるように扱うことも大切です。
回答数が非常に少ない属性は、個別の結果を出すと誰の回答か推測されるおそれがあります。
少人数のカテゴリは統合する、数値を非表示にするなど、共有範囲に応じた配慮を行いましょう。
【操作のポイント】提出用ファイルを作る前に、非表示のシート、コメント、ファイルのプロパティに個人情報が残っていないか確認します。
まとめ エクセルでアンケート集計表を見やすく作る方法
Excelでアンケート集計表を作る際は、まず1行目にヘッダーを置き、1人分の回答を1行に並べる回答一覧を作成します。
回答一覧をテーブル化し、元データ、集計用シート、報告用シートを分けると、回答の追加や修正にも対応しやすくなります。
単一選択の設問はCOUNTIF関数で選択肢別の回答数を数え、SUM関数で総数を求め、回答数を総数で割ることで割合を表示できます。
回答数と割合の両方を確認すると、アンケート結果の規模と傾向をバランスよく把握できます。
部署、年代、参加回数などの属性別に分析したい場合は、ピボットテーブルを使うと、行と列の配置を変えながらクロス集計を行えます。
グラフは、件数比較には棒グラフ、構成比には円グラフや100パーセント積み上げグラフを使い分けると、報告先に伝わりやすい資料になります。
自由記述はテーマ別に分類し、複数回答は選択肢ごとに列を分けて集計すると、数値として整理しやすくなります。
最後に、集計範囲、回答総数、未回答、割合の分母、個人情報の扱いを確認してから共有すれば、正確で信頼できるアンケート集計表に仕上がるでしょう。
