まずはここから：自由回答データをExcelで扱いやすい形に整える
アンケートの自由回答を集計しようとして、最初につまずきやすいのが「回答データがバラバラで見づらい」という状態です。選択式のアンケートなら数字で集計できますが、自由回答は文章なので、そのままでは件数を数えたり、傾向を見たりするのが難しくなります。
そこで最初にやるべきことは、自由回答をExcelで扱いやすい表形式に整えることです。いきなり集計や分析を始めるのではなく、まずはデータの土台をきれいにしておきましょう。
基本の形は、1行に1人分の回答を入れる形式です。たとえば、以下のような列を用意します。
- 回答ID
- 回答日時
- 年代
- 性別
- 部署・職種
- 自由回答
- カテゴリ
- メモ
ポイントは、自由回答の文章を1つのセルにまとめることです。1人の回答が複数行に分かれていたり、回答の途中でセルが分割されていたりすると、後からフィルターや関数を使うときに扱いづらくなります。
また、元データを直接編集するのは避けたほうが安全です。アンケートツールからCSVで出力したデータは、まず「元データ」シートとして残しておき、別シートにコピーして「作業用データ」として加工しましょう。こうしておけば、ミスをしても元に戻せます。
Excelで整理する際は、表の1行目に必ず見出しを入れます。「回答」「コメント」など曖昧な名前ではなく、「自由回答：サービス改善点」のように、何に対する回答なのかが分かる名前にしておくと便利です。後から複数人で確認するときにも、認識のズレを防げます。
さらに、データ範囲を選択して「テーブル」に変換しておくのもおすすめです。Excelでは、データ範囲を選んでCtrl + Tを押すとテーブル化できます。テーブルにしておくと、フィルターが自動で付き、行を追加しても範囲が自動で広がるため、集計作業がかなり楽になります。
自由回答の整理では、「きれいな文章に直す」ことよりも、まずは集計しやすい構造にすることが重要です。1行1回答、1列1項目、元データは残す。この3つを意識するだけで、後の表記ゆれ修正やカテゴリ分け、ピボットテーブルでの集計がスムーズになります。
最初の下準備に少し時間をかけることで、後半の作業時間は大きく短縮できます。自由回答の集計は、いきなり分析するのではなく、まずExcel上で「扱いやすいデータ」に整えるところから始めましょう。
表記ゆれ・空白・重複をスッキリ整理する基本テクニック
自由回答データを表形式に整えたら、次に行いたいのが表記ゆれ・空白・重複の整理です。ここを放置したまま集計すると、本当は同じ意味の回答なのに別々のデータとして扱われてしまい、正しい傾向が見えにくくなります。
たとえば、以下のような回答があったとします。
- 使いやすい
- 使い やすい
- 使いやすいです
- つかいやすい
- 操作しやすい
人が読めば似た内容だと分かりますが、Excel上ではすべて別の文字列として扱われます。COUNTIFやピボットテーブルで集計する前に、こうした違いをできるだけ整理しておくことが重要です。
まず確認したいのが、余分な空白です。自由回答には、文頭や文末にスペースが入っていたり、単語の間に不要な空白が混ざっていたりすることがあります。見た目では気づきにくいですが、Excelでは「満足」と「満足 」は別データとして認識されます。
空白を取り除くには、TRIM関数が便利です。たとえば自由回答がA2セルに入っている場合、隣のセルに以下のように入力します。
=TRIM(A2)TRIM関数を使うと、文字列の前後にある不要なスペースや、単語間の余分なスペースを整理できます。ただし、日本語入力で使われる全角スペースは残る場合があるため、その場合は置換機能も活用しましょう。
置換は、Ctrl + Hで開けます。「検索する文字列」に全角スペースを入力し、「置換後の文字列」を空欄にして実行すれば、全角スペースをまとめて削除できます。半角スペースも同じように処理できますが、必要なスペースまで消さないように、作業前には必ずデータをコピーしておくと安心です。
次に整理したいのが、表記ゆれです。たとえば「スマホ」「スマートフォン」「携帯」など、同じような意味でも表現が違うケースがあります。また、「良い」「よい」「いい」のように、漢字・ひらがな・口語表現が混ざることもよくあります。
表記ゆれを整理する際は、いきなりすべてを手作業で直すのではなく、まず頻出する言葉を確認しましょう。フィルターで並べ替えたり、重複を一時的に削除して一覧化したりすると、どんな表現が多いか見つけやすくなります。
よく出てくる表現については、統一ルールを決めます。たとえば、以下のような形です。
- 「スマホ」「スマートフォン」→「スマートフォン」に統一
- 「アプリ」「アプリケーション」→「アプリ」に統一
- 「わかりにくい」「分かりづらい」→「分かりにくい」に統一
このとき、元の自由回答を直接書き換えるのではなく、整理後の回答という列を追加して、そこに修正版を作るのがおすすめです。元の文章を残しておけば、「回答者が実際にどう書いたか」を後から確認できます。
最後に確認したいのが、重複回答です。同じ人の回答が二重に取り込まれていたり、テスト送信のデータが混ざっていたりすると、集計結果がズレてしまいます。回答IDやメールアドレス、回答日時などをもとに、重複がないか確認しましょう。
Excelでは、対象範囲を選択して「データ」タブから重複の削除を使うと、同じ内容の行を削除できます。ただし、自由回答の文章だけで重複判定すると、たまたま同じ回答をした別人のデータまで消してしまう可能性があります。重複チェックには、回答IDなどの識別情報を組み合わせるのが安全です。
表記ゆれ・空白・重複の整理は地味な作業ですが、自由回答を正しく集計するための大切な準備です。ここを丁寧に整えておくことで、次のカテゴリ分けや集計作業の精度が大きく上がります。
自由回答をカテゴリ分けして「集計できるデータ」に変換する方法
表記ゆれや空白を整理できたら、次は自由回答をカテゴリ分けしていきます。自由回答はそのままだと文章データですが、カテゴリを付けることで「何についての意見が多いのか」を件数で集計できるようになります。
たとえば、サービス改善に関する自由回答がある場合、以下のようなカテゴリを用意できます。
- 料金に関する意見
- 操作性に関する意見
- 機能追加の要望
- サポート対応への意見
- 不具合・エラー報告
- その他
ポイントは、いきなり細かく分類しすぎないことです。最初から「ログイン画面の使い勝手」「ボタン配置」「検索機能」など細かく分けると、判断に迷いやすくなります。まずは大きめのカテゴリで分けて、必要に応じて後から詳細カテゴリを追加するとスムーズです。
Excel上では、自由回答の右側に「カテゴリ」列を追加します。さらに分析をしやすくしたい場合は、以下のように列を分けるのもおすすめです。
- 大カテゴリ
- 小カテゴリ
- ポジティブ・ネガティブ
- 対応優先度
たとえば「アプリは便利だが、通知が多すぎる」という回答があった場合、大カテゴリは「機能・仕様」、小カテゴリは「通知」、感情分類は「ネガティブ」といった形で整理できます。こうしておくと、後から「ネガティブな意見が多いカテゴリはどこか」「優先的に改善すべき内容は何か」を見つけやすくなります。
カテゴリ分けをするときは、分類ルールを先に決めておくことが大切です。たとえば「価格が高い」「料金プランが分かりづらい」はどちらも料金カテゴリに入れる、「画面が見にくい」「ボタンが探しにくい」は操作性カテゴリに入れる、というように基準を明確にします。
複数人で作業する場合は、特にこのルールが重要です。人によって判断が違うと、同じような回答なのに別カテゴリに分かれてしまいます。簡単なものでよいので、以下のような分類表を作っておきましょう。
| カテゴリ | 該当する回答例 |
|---|---|
| 料金 | 高い、プランが分かりにくい、割引がほしい |
| 操作性 | 使いにくい、画面が見づらい、操作に迷う |
| 機能要望 | 〇〇機能がほしい、連携できるようにしてほしい |
また、1つの自由回答に複数の内容が含まれることもあります。たとえば「料金は満足しているが、サポートの返信が遅い」という回答は、「料金」と「サポート」の両方に関係しています。
この場合は、運用ルールを決めましょう。主な内容だけを1つ選ぶ方法もありますし、複数カテゴリを付ける方法もあります。後で細かく分析したいなら、「カテゴリ1」「カテゴリ2」「カテゴリ3」のように列を分けておくと便利です。
さらに、カテゴリとは別にキーワード列を作るのも効果的です。「通知」「ログイン」「価格」「問い合わせ」など、回答の中で重要な単語を抜き出しておくと、集計や検索がしやすくなります。
自由回答のカテゴリ分けは、文章を数字で扱えるようにするための変換作業です。すべてを完璧に分類しようとすると時間がかかるため、まずは大きな傾向をつかむことを意識しましょう。カテゴリが整えば、次のステップでCOUNTIFやピボットテーブルを使って、回答傾向を一気に見える化できるようになります。
COUNTIF・フィルター・ピボットテーブルで回答傾向を見える化する
カテゴリ分けまでできたら、いよいよ自由回答を集計して見える化していきます。ここからは文章を1件ずつ読む作業ではなく、「どの意見が多いのか」「どの属性に多いのか」をExcelで確認するステップです。
まず手軽に使えるのが、COUNTIF関数です。たとえば「カテゴリ」列がG列にあり、「料金」というカテゴリの件数を数えたい場合は、以下のように入力します。
=COUNTIF(G:G,"料金")これで、カテゴリ列の中に「料金」と入力されている回答数を数えられます。同じように「操作性」「機能要望」「サポート」などのカテゴリごとにCOUNTIFを使えば、どの意見が多いかを一覧で確認できます。
| カテゴリ | 件数 |
|---|---|
| 料金 | 25 |
| 操作性 | 40 |
| 機能要望 | 18 |
キーワードの出現数を調べたい場合も、COUNTIFが使えます。自由回答がF列に入っていて、「ログイン」という言葉を含む回答を数えたい場合は、以下のように入力します。
=COUNTIF(F:F,"*ログイン*")アスタリスク（*）は「前後にどんな文字が入っていてもよい」という意味です。そのため、「ログインできない」「ログイン画面が分かりにくい」といった回答もまとめてカウントできます。
次に便利なのが、フィルター機能です。Excelの表をテーブル化している場合、見出し行にフィルターが表示されます。カテゴリ列で「操作性」だけを選択すれば、操作性に関する回答だけを一覧表示できます。
フィルターは、件数を見るだけでなく、該当する自由回答の中身を確認したいときに役立ちます。たとえば「操作性の不満が多い」と分かったら、フィルターで絞り込み、実際にどんな不満が書かれているのかを読むことで、改善ポイントが具体的になります。
さらに全体の傾向を素早く把握したい場合は、ピボットテーブルを使いましょう。データ範囲を選択し、「挿入」タブから「ピボットテーブル」を選びます。新しいシートに作成すると、元データと分けて集計結果を管理しやすくなります。
カテゴリ別の件数を出すなら、ピボットテーブルの設定で以下のように配置します。
- 行：カテゴリ
- 値：回答ID または 自由回答
- 集計方法：個数
これだけで、カテゴリごとの回答件数が自動で集計されます。さらに「年代」「部署」「性別」などの列がある場合は、それらを列やフィルターに追加すると、属性ごとの傾向も見られます。
たとえば、行に「カテゴリ」、列に「年代」、値に「回答IDの個数」を入れると、20代は操作性への不満が多い、30代は料金への意見が多い、といった違いを確認できます。単に全体件数を見るよりも、施策につながるヒントを得やすくなります。
集計結果は、棒グラフや円グラフにするとさらに分かりやすくなります。特に社内共有用の資料に使う場合は、数字だけの表よりもグラフの方が一目で傾向を伝えられます。
自由回答の集計では、COUNTIFでざっくり件数を確認し、フィルターで中身を読み込み、ピボットテーブルで全体傾向を整理する流れがおすすめです。文章データも、カテゴリやキーワードを使えばExcel上で十分に分析できます。
作業を効率化するコツ：関数・置換・AI活用で集計時間を短縮する
自由回答の整理・集計は、どうしても手作業が多くなりがちです。特に回答数が100件、500件と増えてくると、1つずつ読んでカテゴリを付けるだけでもかなり時間がかかります。そこで意識したいのが、Excelの関数・置換機能・AIを組み合わせて、単純作業を減らすことです。
まず便利なのが、特定のキーワードを含む回答に自動でカテゴリを付ける方法です。たとえば自由回答がF列にある場合、「料金」という言葉が含まれていればカテゴリを「料金」にしたいときは、以下のような式が使えます。
=IF(ISNUMBER(SEARCH("料金",F2)),"料金","")SEARCH関数は、セル内に指定した文字が含まれているかを調べる関数です。完全一致ではなく、文章の一部にキーワードが含まれていれば判定できるため、自由回答の分類に向いています。
複数の条件で分類したい場合は、IFS関数を使うと見やすくなります。
=IFS(
ISNUMBER(SEARCH("料金",F2)),"料金",
ISNUMBER(SEARCH("使いにくい",F2)),"操作性",
ISNUMBER(SEARCH("機能",F2)),"機能要望",
TRUE,"その他"
)このようにしておけば、よく出るキーワードをもとに一次分類ができます。もちろん、すべてを完璧に自動化できるわけではありませんが、最初の振り分けだけでも自動化できれば、確認作業に集中できます。
表記ゆれの修正には、置換機能を積極的に使いましょう。Ctrl + Hで置換画面を開き、「スマホ」を「スマートフォン」に置き換える、「分かりづらい」を「分かりにくい」に統一する、といった作業をまとめて行えます。よく使う置換ルールはメモしておくと、次回以降のアンケート処理にも使い回せます。
さらに効率化したい場合は、置換前・置換後の対応表を別シートに作っておくのもおすすめです。「スマホ→スマートフォン」「アプリケーション→アプリ」のように一覧化しておけば、チーム内でルールを共有しやすくなります。
最近では、AIを活用して自由回答の整理を時短する方法もあります。たとえば、回答一覧をAIに貼り付けて、以下のように依頼します。
以下の自由回答を「料金」「操作性」「機能要望」「サポート」「その他」に分類してください。
分類理由も簡単に付けてください。AIを使うと、大量の文章を短時間でざっくり分類できます。特に「どんなカテゴリを作ればよいか分からない」という初期段階では、候補を出してもらうだけでもかなり役立ちます。
ただし、AIの結果をそのまま確定データにするのは避けましょう。文脈の読み違いや、分類基準のブレが起きることがあります。AIはあくまで下書き・一次分類の補助として使い、最終判断は人が確認するのが安全です。また、個人情報や社外秘の内容を含む回答は、利用するAIサービスの規約を確認し、必要に応じて匿名化してから扱いましょう。
自由回答の集計は、「全部手で読む」から「機械で下処理して、人が確認する」流れに変えるだけで、作業時間を大きく短縮できます。関数でキーワード分類、置換で表記統一、AIで一次整理。この3つをうまく使えば、Excelでのアンケート集計はぐっと効率的になります。
コメント