【Excel】エクセルでアンケート集計表を作る方法（回答を集計しやすい表）

エクセルでアンケートを集計するときは、回答者ごとの入力表と、結果を見やすく確認する集計表を分けて作ることが大切です。

最初に列の設計を整え、入力規則や関数を活用すれば、回答数が増えても集計ミスを抑えながら年代別、満足度別、選択肢別の結果を確認できます。

この記事では、回答を入力しやすく、あとから集計しやすいアンケート表の作り方を、具体的なセル配置や数式を交えて解説します。

 

エクセルでアンケート集計表を作る手順

それではまず、回答を集計しやすいアンケート表の基本構成について解説していきます。

A列	B列	C列	D列
回答番号	年代	満足度	自由記述
1	30代	満足	説明が分かりやすい
2	40代	普通	待ち時間が長い

 

回答データ用シートの列設計

アンケート集計表を作るときは、最初に回答データを入力するシートを用意します。

シート名は「回答データ」のように、用途がすぐ分かる名前にしておくと管理しやすくなります。

1行目には質問ごとの見出しを置き、2行目以降に回答者別のデータを縦方向へ追加していく構成が基本です。

たとえばA1セルに回答番号、B1セルに回答日、C1セルに年代、D1セルに性別、E1セルに利用頻度、F1セルに満足度、G1セルに自由記述と入力します。

1人分の回答を必ず1行にまとめる設計にすると、並べ替え、抽出、関数での集計、ピボットテーブル作成を行いやすくなります。

質問を横方向の列に配置し、回答者を縦方向の行に配置する考え方です。

回答番号は、後から入力内容を確認したり、重複した回答を探したりするときに役立ちます。

A2セルに1、A3セルに2と入力してオートフィルを使えば、連番を簡単に作成できます。

回答日を記録する場合は、日付として認識される形式で入力しておくと、月別や週別の傾向を分析するときにも便利です。

自由記述のように文章が長くなる項目は、右端の列に置くと画面を横にスクロールする場面を減らせます。

一方で、年代や満足度など数えたい項目は、左側に寄せておくと一覧性が高まります。

列名には「質問1」「質問2」のような曖昧な名前ではなく、「年代」「購入経路」「総合満足度」のように質問内容が分かる名前を付けましょう。

質問票の文章が長い場合でも、表の見出しでは意味を損なわない範囲で短く整理することが重要です。

たとえば「当社サービスを友人に勧めたいと思いますか」という設問なら、「推奨意向」とまとめられます。

ただし、集計担当者以外もファイルを見る可能性があるなら、コメント機能や別シートに質問文の全文を残しておくと誤解を防げます。

列を途中で追加すると集計用の参照範囲がずれることがあるため、質問項目が固まってからデータ入力を始めるのが理想です。

回答の予定件数が少なくても、将来の追加を想定して空白行を含めた表にしておくと安心でしょう。

データ入力を開始する前に、見出し行を選択して太字や塗りつぶしを設定すると、入力位置の見間違いも減らせます。

フィルターを利用する予定がある場合は、見出し行の途中に空白セルを作らないことも大切です。

表の途中に空白行や結合セルがあると、並べ替えやピボットテーブルで正しく範囲を認識できない場合があります。

最初の設計に少し時間をかけることが、集計作業を短縮する近道です。

 

入力しやすいアンケート回答形式

選択式のアンケートでは、回答者や入力担当者によって表記がばらつかない仕組みを作る必要があります。

たとえば満足度を入力するときに、「満足」「とても満足」「良い」「4」といった異なる表記が混在すると、同じ意味の回答でも別々に数えられてしまいます。

集計対象になる選択肢は、エクセルのデータの入力規則を使ったドロップダウンリストにすると、表記ゆれを大幅に防げます。

満足度の列を選択したあと、リボンのデータタブからデータの入力規則をクリックします。

設定タブの入力値の種類でリストを選び、元の値に「大変満足,満足,普通,不満,大変不満」と入力します。

これで対象セルをクリックしたときに、決めた選択肢だけを選べる矢印が表示されます。

年代についても、「20代未満,20代,30代,40代,50代,60代以上」のように、事前に区分を統一しておくとよいでしょう。

複数回答の設問は、1つのセルに「価格,品質」のように複数の回答を入れる方法もあります。

ただし、この形式はCOUNTIF関数で完全一致を数えにくく、後の集計が複雑になりやすい点に注意が必要です。

複数回答を詳しく分析したい場合は、選択肢ごとに列を分け、該当する項目に「1」または「回答あり」と入力する形式が向いています。

たとえば購入理由が価格、品質、配送、口コミの4項目なら、それぞれを別列にして、選ばれた項目に1を入れます。

この方法ならSUM関数で選択数を簡単に数えられます。

自由記述欄は入力規則を設定せず、回答者の言葉をそのまま残せるようにします。

長い文章を読みやすくするには、該当列を選択してホームタブの折り返して全体を表示するを有効にします。

行の高さは内容に応じて自動調整されるため、文章が途中で隠れる問題を避けられます。

必須回答の項目がある場合は、入力規則のエラーメッセージを設定し、空欄や誤った値を入力したときに警告を表示する方法も有効です。

入力漏れを完全に防ぐ機能ではありませんが、集計前の修正作業を減らす助けになります。

質問の選択肢を途中で変更すると、以前の回答と条件がそろわなくなる場合があります。

調査期間中はできるだけ選択肢を固定し、変更が必要な場合は変更日と内容を記録しておきましょう。

 

テーブル機能によるデータ範囲の管理

回答データの入力範囲が決まったら、エクセルのテーブル機能を使って表として登録する方法がおすすめです。

テーブルにすると、新しい回答を行の下に追加したとき、罫線や数式、入力規則が自動的に引き継がれます。

テーブル化したデータは、集計関数やピボットテーブルの参照範囲も広がりやすく、回答追加後の再設定を減らせる点が大きな利点です。

まず回答データの見出しを含む範囲内のセルを1つクリックします。

次に挿入タブからテーブルをクリックするか、CtrlキーとTキーを同時に押します。

テーブルの作成画面が表示されたら、選択範囲に誤りがないか確認します。

先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認してから、OKをクリックします。

テーブルとして登録されると、見出しにフィルターの矢印が表示され、行の色が交互に設定されます。

色はテーブルデザインタブから変更できますが、集計の正確さには影響しません。

分かりやすさを優先し、印刷時にも見やすい配色を選ぶとよいでしょう。

テーブル名も確認しておくと、数式やピボットテーブルで扱いやすくなります。

テーブルデザインタブの左上にあるテーブル名欄で、「回答一覧」のような意味のある名前に変更できます。

スペースを含む名前より、短く分かりやすい名前のほうが数式入力時に扱いやすい傾向があります。

テーブル内では、列名を利用した構造化参照という形式で数式を書くことも可能です。

たとえば年代列の30代を数える場合、通常の範囲指定ではC2:C100のように書きますが、テーブル名を使うと回答一覧[年代]のように指定できます。

この形式は列の追加や行数の増加に強く、数式が何を数えているかも見分けやすくなります。

一方で、すでに作成済みの複雑な集計表が固定範囲を参照している場合は、テーブル化後に参照先を確認しましょう。

入力済みの回答に不要な空白や全角半角の違いがあると、同じ選択肢でも別データとして扱われます。

テーブル化はこうした入力内容を自動修正する機能ではないため、作成前に数件のデータを確認することも必要です。

 

COUNTIF関数による回答数の集計

続いては、選択肢ごとの回答数を数えるCOUNTIF関数の使い方を確認していきます。

H列	I列
満足度	回答数
大変満足	=COUNTIF($F$2:$F$100,H3)
満足	=COUNTIF($F$2:$F$100,H4)

 

単一条件で満足度を数える数式

満足度や年代など、1つの条件に当てはまる回答数を数えるときはCOUNTIF関数を使います。

集計結果を回答データとは別の場所に作るため、同じシートの右側、または「集計」シートを新しく追加する方法が分かりやすいでしょう。

ここでは集計シートのH2セルに満足度、I2セルに回答数と入力します。

H3セルからH7セルには、大変満足、満足、普通、不満、大変不満という選択肢を縦に並べます。

I3セルには、次の数式を入力します。

この数式の最初の部分である回答データ!$F$2:$F$100は、数える対象の範囲です。

回答データはシート名を表し、F列は満足度が入力されている列を表します。

$記号は絶対参照であり、数式を下方向へコピーしてもF2からF100という対象範囲がずれないようにする役割があります。

最後のH3は条件を指定するセルで、ここには大変満足という文字が入っています。

COUNTIF関数は、指定した範囲の中から条件に完全に一致するセルの個数を返す関数です。

I3セルでEnterキーを押すと、大変満足と回答した人数が表示されます。

その後、I3セル右下の小さな四角を下へドラッグするか、コピーしてI4からI7へ貼り付けると、各満足度の人数を数えられます。

このときH3の参照はH4、H5と自動的に変わるため、選択肢ごとに数式を入力し直す必要はありません。

回答件数が100件を超える可能性があるなら、範囲をF2:F1000のように余裕を持って指定します。

ただし、空白セルが増えてもCOUNTIFの結果には影響しないため、あらかじめ広めに指定しても問題ありません。

テーブル化している場合は、=COUNTIF(回答一覧[満足度],H3)のように列名で参照することもできます。

この書き方では、回答が追加されるたびに集計対象が自動的に広がるため、継続的にアンケートを受け付ける場面に便利です。

結果が0になるときは、条件セルの文字と回答データの文字が本当に一致しているか確認しましょう。

見た目では同じでも、末尾の空白や異なる記号が含まれていると一致しないことがあります。

 

複数条件で年代別の回答を数える方法

年代別に満足と回答した人数のように、2つ以上の条件を同時に満たす回答を数えたい場合はCOUNTIFS関数を使用します。

たとえば、回答データのC列に年代、F列に満足度が入力されているとします。

集計シートでは、H2セルに年代、I2セルに満足度、J2セルに回答数という見出しを用意します。

H3セルに30代、I3セルに満足と入力した場合、J3セルには次の数式を入力します。

COUNTIFS関数では、最初に年代を調べる範囲、次に年代の条件を指定します。

続けて満足度を調べる範囲と、満足度の条件を指定する構造です。

すべての条件を満たした行だけが数えられるため、属性別の傾向を細かく確認したいアンケート集計に向いています。

たとえば30代かつ満足の人数、40代かつ満足の人数を並べれば、年代ごとの評価の違いが見えてきます。

年代をH列に縦に並べ、満足度をI列に横に並べる形式にすると、表として読みやすくなります。

この場合、行と列の見出しを固定しながら数式をコピーする必要があります。

年代がH3、満足度がI2に入っているなら、数式内では$H3とI$2のように一部だけ$記号を付けます。

$H3は横方向にコピーしてもH列を固定し、下方向にコピーしたときは行番号だけ変わります。

I$2は下方向にコピーしても2行目を固定し、横方向にコピーしたときは列だけ変わります。

この混合参照を使うと、1つの数式を表全体にコピーしても各交点に合った人数を求められます。

年代別の合計が全体の回答数と大きく異なるときは、年代が未回答の行や、区分外の表記が存在する可能性があります。

フィルター機能を使って空白や想定外の値を確認すると、データの問題を見つけやすくなります。

条件が3つ以上になっても、COUNTIFS関数では範囲と条件を追加していくことができます。

ただし、条件を増やしすぎると該当人数が少なくなり、個人が推測されるおそれもあります。

社内アンケートや顧客アンケートを共有するときは、少人数の属性を細かく公開しすぎない配慮も必要です。

 

ピボットテーブルによるアンケート結果の分析

続いては、多数の回答を一覧表にまとめて分析できるピボットテーブルの活用方法を確認していきます。

年代	大変満足	満足	普通
20代	8	12	4
30代	10	15	6

 

ピボットテーブルの作成手順

回答数が多い場合や、年代別、性別、利用頻度別など複数の切り口で結果を見たい場合は、ピボットテーブルが便利です。

ピボットテーブルは、元の回答データを変更せずに、ドラッグ操作で集計表の形を入れ替えられる機能です。

関数を何本も作らなくても、集計軸を変更しながら回答の傾向を確認できるため、探索的な分析に適しています。

まず回答データのテーブル内にあるセルを1つクリックします。

挿入タブを開き、リボン左側にあるピボットテーブルをクリックします。

テーブルまたは範囲からという選択肢を選ぶと、ピボットテーブルの作成画面が表示されます。

テーブル化済みなら、テーブル名が範囲として表示されるため、回答の追加にも対応しやすくなります。

配置先は新規ワークシートを選ぶと、元データと集計表が混ざらず見やすくなります。

既存のワークシートを選ぶ場合は、回答データと重ならない空白のセルを指定しましょう。

OKをクリックすると、新しいシートに空のピボットテーブルと、右側にピボットテーブルのフィールド一覧が表示されます。

フィールド一覧には、回答データの1行目に入力した列名が並びます。

この列名を行、列、値、フィルターの各エリアへドラッグして、必要な集計表を作ります。

たとえば年代別の満足度を見たい場合は、年代を行エリア、満足度を列エリア、回答番号を値エリアへ配置します。

回答番号を値エリアに置くと、通常は個数として集計されます。

数値が合計になってしまう場合は、値エリアの項目をクリックし、値フィールドの設定からデータの個数を選択します。

 

行と列を入れ替えた見やすい集計表

ピボットテーブルでは、行と列に置く項目を入れ替えるだけで、同じデータを別の見え方で確認できます。

年代を行、満足度を列に置くと、各年代がどの評価を選んだかを横方向に比較できます。

反対に満足度を行、年代を列に置くと、各評価を選んだ人の年代構成を確認しやすくなります。

何を比較したいかによって行と列を決めると、数字の意味を読み取りやすい集計表になります。

報告書で年代ごとの差を伝えたいときは、年代を行に置く構成が一般的です。

評価ごとの人数を強調したいときは、満足度を行に置く構成も適しています。

値エリアには回答番号の個数を置くのが基本ですが、満足度を数値化しているアンケートでは平均値を表示することもできます。

たとえば大変満足を5、満足を4、普通を3、不満を2、大変不満を1として別列に入力しておけば、年代別の平均満足度を計算できます。

この場合は数値化した列を値エリアに置き、値フィールドの設定で平均を選択します。

人数と平均点では意味が異なるため、同じ表に混在させるときは見出しを明確にしましょう。

ピボットテーブルの総計が不要な場合は、デザインタブから総計をオフにできます。

ただし、全体件数との整合性を確認する段階では、総計を表示したままにしておくほうが安心です。

集計表にフィルターを追加すると、特定の回答期間や性別だけに絞り込むこともできます。

たとえば利用頻度をフィルターエリアに置けば、週1回以上利用する回答者だけの満足度を確認できます。

フィルターをかけた状態で報告する場合は、どの条件で絞り込んだ結果なのかを表の近くに書いておくと誤解を防げます。

空白という項目が表示されるときは、元データに未回答セルがある状態です。

未回答も重要な情報になる場合がありますが、入力漏れなら元データを修正したうえで更新するほうがよいでしょう。

 

アンケート集計表のグラフと確認方法

続いては、集計結果をグラフで伝え、入力ミスを確認するための方法を確認していきます。

満足度	回答数	構成比
大変満足	18	30.0%
満足	24	40.0%
普通	12	20.0%

 

回答割合を求める構成比の計算

回答数だけでは、アンケートの規模が違う結果を比較しにくいことがあります。

そのようなときは、各選択肢の人数を全回答数で割り、構成比としてパーセント表示にします。

たとえばI3セルに大変満足の回答数、I7セルに各満足度の合計がある場合、J3セルには次の数式を入力します。

この数式では、I3が大変満足の回答数、$I$8が全体の回答数です。

分母となる全体件数はコピーしても変わらないように、列記号と行番号の両方に$記号を付けた絶対参照にします。

数式を入力したあとにホームタブのパーセントスタイルを選ぶと、0.3のような小数を30.0パーセントとして表示できます。

必要に応じて小数点以下の桁数を増減し、資料の読みやすさに合わせます。

J3セルの数式を下へコピーすると、各選択肢の構成比が計算されます。

構成比の合計は、原則として100パーセントになります。

表示桁数の丸めによって99.9パーセントや100.1パーセントになることはありますが、大きく異なる場合は数式や対象範囲を確認しましょう。

未回答を含めるかどうかも、構成比を出す前に決める必要があります。

設問ごとの回答傾向を見たい場合は、その設問に回答した人数を分母にする方法が一般的です。

一方で、配布人数に対する回答率を知りたい場合は、配布数を分母にして別途計算します。

この2つは目的が異なるため、同じパーセントとして混同しないようにしましょう。

複数回答の質問では、選択数の合計が回答者数を超えるため、各選択肢の割合を足しても100パーセントにはなりません。

この場合は「回答者に占める選択率」なのか、「全選択数に占める割合」なのかを表の見出しで明記することが重要です。

集計表の近くに有効回答数を記載しておくと、結果を見る人が数字の母数を判断しやすくなります。

 

グラフ作成と集計前のデータ確認

集計表が完成したら、結果を直感的に伝えるためにグラフを作成します。

満足度のような選択肢別の人数を比較する場合は、縦棒グラフや横棒グラフが向いています。

選択肢の比率を大まかに示したい場合は円グラフも使えますが、項目数が多いと見分けにくくなります。

5段階評価や年代別比較では、各項目の差を読み取りやすい棒グラフを選ぶと、報告資料として使いやすくなります。

グラフを作るには、満足度と回答数を含む集計範囲を選択します。

挿入タブをクリックし、グラフグループから縦棒または横棒を選びます。

グラフタイトルは自動で表示されますが、「総合満足度の回答分布」のように内容が分かる言葉へ変更しましょう。

凡例が不要な単一系列のグラフでは、凡例を削除すると表示領域を広く使えます。

データラベルを追加すれば、棒の高さだけでなく具体的な人数や割合を直接読めるようになります。

グラフだけを見せる場合でも、回答数や対象期間を近くに書くと解釈しやすくなります。

グラフ作成前には、元データと集計表を確認する作業も欠かせません。

まず、回答番号の件数と、集計表の合計件数が一致しているかを確認します。

次に、フィルターを利用して空白、想定外の文字、重複回答がないかを確認します。

たとえば満足度列で「満足 」のように末尾に空白が入っていると、COUNTIF関数やピボットテーブルでは別項目として扱われることがあります。

不要な空白はTRIM関数で整理できますが、入力規則を設定しておけば、そもそもの発生を抑えられます。

回答番号が同じ行がある場合は、同じ人が二重登録されていないか、意図した再回答なのかを確認します。

個人情報を扱うアンケートでは、氏名やメールアドレスを含む元データと、共有用の集計表を分けることも重要です。

共有する資料には個人を特定できる列を含めず、必要なら自由記述も内容を確認してから掲載しましょう。

 

まとめ エクセルでアンケート集計表を作る方法

エクセルでアンケート集計表を作るときは、1行を1回答、1列を1質問として回答データを整理することが基本です。

1行目にヘッダーを置き、年代や満足度などの選択式項目にはデータの入力規則を設定すると、表記ゆれを防ぎやすくなります。

回答データと集計結果を分け、回答データをテーブル化しておくと、回答が増えた後も管理しやすくなります。

選択肢ごとの回答数はCOUNTIF関数、年代別など複数条件の集計はCOUNTIFS関数で求められます。

数式をコピーする場合は、対象範囲や分母を絶対参照にすることが正確な集計につながります。

多角的に分析したいときは、ピボットテーブルで行と列の項目を入れ替え、年代別や利用頻度別の傾向を確認しましょう。

最後に構成比やグラフを作成し、回答数の一致、未回答、表記ゆれ、個人情報の扱いを確認すれば、共有しやすいアンケート結果に仕上がります。