【手順付き】アンケートの自由記述をエクセルでまとめる方法
<この記事でわかること>
- アンケートの自由記述には数値では見えない顧客の意見が詰まっており、内容をカテゴリ分けして整理することで、具体的な課題発見につながる。
- エクセルで自由記述を定量データ化するアフターコーディングは、データのクリーニングや表記統一を事前に行った後、カテゴリの作成、番号の割り振り、複数要素の分割、COUNTIFやピボットテーブルによる集計という手順で進める。
- 作業の効率化には、キーワード抽出のSEARCH関数、条件分岐のIF関数、表記統一のVLOOKUP関数、データの入力規則などを組み合わせることで、手作業によるミスや工数を大幅に減らせる。
- エクセル単体での運用は、回答状況のリアルタイムな把握や既存の顧客属性データとの紐付けが難しく、回答時の入力ハードルも上がるため、回収率や回答精度が低下しやすいというデメリットがある。
- CRMシステム「Synergy!」を活用すれば、Webフォームでのスムーズな回収から顧客データとの自動紐付けまでを一気通貫で行え、エクセル運用の弱点を補いながら効率的な分析・改善サイクルを回すことができる。
アンケートの自由記述には、数値データだけでは把握できない顧客の率直な意見や具体的な不満が含まれています。ただし、そのままではデータとして整理しにくく、目で読んだだけで終わってしまうことも少なくありません。
本記事では、身近なツールであるエクセルを活用し、自由記述を定量データとして整理する方法を解説します。さらに、アフターコーディングの手順と、作業を効率化するための関数や機能についても、実務で使える形で説明します。
<目次>
アンケートの自由記述をまとめる重要性
自由記述には、選択式の設問だけでは把握できない理由や背景が含まれています。回答数が多いと、内容を十分に確認しないまま終わってしまうこともあるでしょう。ここでは、アンケートの自由記述をまとめる意義について解説します。
定量調査では見えない「生の声」と「本音」の抽出ができる
選択式の設問は比較や集計に適していますが、なぜその評価になったのか、どこに不満や満足を感じたのかまでは把握しにくいものです。自由記述では、回答者の言葉で理由や状況が示されるため、より具体的な情報を得ることができます。
たとえば、選択式で「普通」の評価をした回答者であっても、自由記述を読むと具体的な改善点が書かれていることがあります。このような意見を集めることで、同じ評価であっても顧客の感じ方が異なることを理解できます。
改善点の具体化と新しいアイデアの発見
自由記述を確認していると、共通のテーマが繰り返し出現することもあれば、少数の意見の中に重要なヒントが含まれていることもあります。たとえば、単に「料金が高い」という意見だけでは対策を考えにくいですが、「費用の内訳がわかりにくい」といった具体的な内容のものがあれば、説明方法の改善につなげることが可能です。
反対に、肯定的な意見は自社の強みを明確にするために役立ちます。スタッフの対応や返信の早さを評価する声が多ければ、サービスのアピールポイントとして活用できます。全体的な傾向を把握しながら実際の回答も確認することで、業務の改善と新しいアイデアの発見を同時に進めることができるでしょう。
具体的なアクションプランを策定できる
自由記述を整理してカテゴリ化すると、「何が課題か」だけでなく「誰に、どの場面で起きているか」まで追いやすくなります。たとえば年代別に見ると、若年層はUIに不満が多く、年配層は説明不足に不安が多いなど、対策の切り分けが可能です。さらに、分類した意見の件数や割合を算出すると、取り組むべき課題の優先順位が明確になります。
多くの人が感じる小さな不満と、一部の人が深刻に悩んでいる問題とでは、対応の方法が異なります。整理した結果を、具体的な担当者や期限を定めた改善の業務に結びつけることで、自由記述のデータを実際の施策に活用できるでしょう。
エクセルで自由記述をまとめる前に必要な事前準備
エクセルで自由記述を扱うときは、最初の整形で成果が大きく変わります。入力内容が整っていないまま作業を進めると、分類や集計がうまくいかず、後から修正が必要になります。
分析をスムーズに進めるためには、最初に土台を整えておくことが重要です。ここでは、事前に行っておきたい準備について解説します。
データのクリーニング
まずは、集計に適した形へ整えましょう。不要な改行や余分なスペースがあると、同じ内容でも別のデータとして扱われてしまいます。そのため、空白や改行はできる範囲で整理しておきます。回答が複数列にわかれている場合は、質問ごとに列を分け、回答者IDや属性もあわせて並べておくと、後の分析がしやすくなるでしょう。
また、未回答の行をどう扱うかも事前に決めておきます。「未回答」と明示するのか、分析対象外にするのかを統一し、基準をメモしておくと迷いません。さらに、全角・半角の違いや記号の使い方もできるだけ統一しておくと、検索や分類が安定します。
表記ゆれの統一
自由記述では、同じ意味でも表現が異なることがあります。たとえば「サポート」「サポセン」「問い合わせ窓口」などが混在すると、件数が分散し、傾向が見えにくくなります。そこで、代表となる表現を決めて統一しましょう。頻出する言い回しを洗い出し、同じ意味の語をグループ化します。そのうえで置換ルールを作り、表記をそろえていきます。
商品名や店舗名などの固有名詞は、誤字や略称が出やすいため、優先的に整えると作業が進めやすくなります。ただし、必要以上に統一するとニュアンスが失われることもあるため、目的に合わせて、集計に必要な範囲で整えることが大切です。
分析の目的の明確化
自由記述は情報量が多いため、目的が曖昧だと分析の方向性が定まりません。たとえば「満足度を上げたい」という目的だけでは範囲が広すぎます。「不満の原因を上位3つに整理する」「購入の決め手を言語化する」「離脱理由を年代別に把握する」など、具体的な問いに落とし込むことで、見るべきポイントが明確になります。
目的によって、作るべきカテゴリも変わります。改善を重視するなら課題中心に、強みを打ち出すならポジティブな意見を軸に整理します。また、誰に向けて結果を示すのかを明確にしておくと、集計軸やまとめ方もぶれません。
エクセルで自由記述をまとめる手法「アフターコーディング」とは
アフターコーディングとは、アンケートの自由記述を読み解きながら、後からカテゴリーを作って集計する手法です。一つひとつの回答に番号やラベルを割り振ることで、バラバラな文章を数値として扱えるようになり、全体の傾向が見えやすくなります。
自由記述はそのままだと比較が難しいため、まずは「何について書かれているか」という視点をそろえましょう。たとえば、「価格」「接客」「使いやすさ」「品質」といった観点を作り、各回答を振り分けていきます。このとき、最初から完璧な分類を作ろうとせず、読み進めながら柔軟に調整するのがポイントです。
似たような項目をまとめたり、逆に細かく分けたりしながら、全体のバランスを整えていきます。もし複数人で作業する場合は、「こういう回答はこのカテゴリーに入れる」という判断基準をあらかじめ言語化し、サンプルを共有しておくと精度が安定します。最後にカテゴリーごとの件数や属性別の違いをグラフ化すれば、自由記述も立派な分析データとして活用できます。
エクセルでアフターコーディングを行う具体的な手順
アフターコーディングは「読む→分類する→数える→切り口を変えて見る」という流れで進みます。エクセルで行う場合も、手順を分けておくと混乱しません。最初に型を作ってしまえば、次回以降の作業も速くなります。
ここでは具体的な手順について解説します。
ステップ1：回答全体を俯瞰してカテゴリ案を作成する
最初は細かく分けようとせず、全体をざっと確認します。どのような話題が多いか、似た意見がどの程度あるかをつかむのが目的です。
次に、範囲が広すぎる項目については、より具体的な中分類を検討します。たとえば「操作性」であれば「画面の見やすさ」「反応速度」「検索のしやすさ」などに切り分けます。
反対に、該当する回答が極端に少ない項目は、他の項目への統合を検討します。分類の項目は、最終的な意思決定の判断材料となるため、分析の目的に合わせた細かさに設定しておくことが重要です。
ステップ2：各回答に対してカテゴリ番号を割り振る
カテゴリ案が固まったら、各回答に項目の番号や名称を割り当てます。エクセルでは、回答が入力されている列の右隣に新しく列を追加し、そこへ入力していく形がわかりやすいでしょう。入力内容にバラつきがあると正確な集計ができないため、項目の名称は固定し、表記の揺れが起きないよう配慮します。
選択肢から選ぶ形式（ドロップダウンリスト）に設定しておくと、入力内容が安定し、複数人で作業する場合も判断がそろいやすくなります。分類に迷う回答は、一旦「保留」や「要確認」としておき、後でまとめて判断すると作業がスムーズです。
ステップ3：1つの回答に複数の要素がある場合は分割処理する
自由記述では、一文の中に複数のテーマが混ざることがあります。たとえば「価格は高いが、サポートは丁寧だった」という回答を1つの項目だけに絞ってしまうと、大切な情報が欠けてしまいます。このような場合は、回答を分割して扱うのが基本です。
具体的には、回答者の情報を維持したまま行を複製し、一方を「価格」、もう一方を「サポート」としてそれぞれ分類します。分割したことがわかるように連番などを付けておくと、後から元の文章を確認しやすくなります。
ステップ4：COUNTIF関数を使用してカテゴリ別に集計する
分類作業が終わったら、項目ごとの件数を算出します。ここで便利なのが、特定の条件に一致するセルの数を数える関数です。項目ごとの件数だけでなく、全体に対する割合も算出すると、優先順位の判断が容易になります。
たとえば「操作性への不満は件数が多いが内容は軽微である」「解約理由は件数は少ないが影響が大きい」といった整理が可能になります。まずは全体像を数字で把握し、その後に実際の回答内容を確認して背景を探るという流れが、分析として自然です。
ステップ5：ピボットテーブルを用いて属性別にクロス集計する
全体の件数を確認した後は、属性ごとの傾向を分析します。エクセルの集計機能を活用し、縦軸に分類項目、横軸に回答者の属性を配置すると、属性ごとの傾向を比較できます。
たとえば、特定の年代に絞って意見の分布を見たり、利用頻度が低い層に特有の不満を確認したりすることが可能です。数字に顕著な差が見られた場合は、改めて元の文章に戻って文脈を確認することで、より具体的な改善策を導き出すことができます。
エクセルでの自由記述集計を効率化する便利な関数・機能
自由記述の分析は人の手による読み込みが中心となりますが、エクセルの機能を適切に活用することで手作業の負担を大幅に軽減できます。
ここでは、実務に役立つ主な機能とその活用方法について解説します。
SEARCH関数・FIND関数
自由記述の中から特定のキーワードを抽出する際には、検索系の関数が役立ちます。たとえば「遅い」「重い」「わかりにくい」といった言葉が含まれる回答を見つけたい場合、目視で探すよりも迅速に候補を集められます。
SEARCH関数は大文字と小文字を区別せずに検索するため、表記に揺れがあっても抽出漏れを防ぎやすいのが特長です。一方、FIND関数は正確に区別するため、商品名のように表記が決まっている文字列の検出に適しています。
ただし、同じキーワードでも文脈によって意味が異なる場合があるため、最終判断は原文の確認とセットで行うのが安全です。
IF関数・IFS関数
カテゴリ付けの一部を半自動化したい場合は、条件分岐の関数が有効です。たとえば「『高い』を含むなら価格」「『返信』や『電話』を含むならサポート」といったルールを設定し、暫定的なカテゴリを表示させておくと分類作業が容易になります。
IF関数は単純な二択の判定に向いていますが、条件が増える場合はIFS関数を用いることで、複数のルールを順番に評価でき、管理もしやすくなります。最初からすべてを自動化しようとすると誤分類を招く恐れがあるため、まずは候補を出すという運用が現実的です。
VLOOKUP関数
表記ゆれの統一や属性情報の付与には、参照系の関数が便利です。たとえば「サポセン」を「サポート」へ置換するための対応表を作成し、VLOOKUP関数で参照して統一した表現を返す形にすれば、作業の再現性が保てます。また、回答者IDをキーにして別ファイルの顧客情報を紐付けたいときにも活用できるでしょう。
手作業によるコピーはミスを招きやすいため、参照機能を利用して自動でデータを取り込める状態にしておくと作業が安定します。ルール表を資産として残しておけば、次回のアンケートでも同じ基準で集計でき、過去データとの比較も容易になります。
フィルター機能とスライサー
分析の過程では「特定の層だけを確認したい」「特定のカテゴリに関連する原文を読み込みたい」といった場面が頻繁に生じます。そのような際、フィルター機能を活用すると作業の手を止めることなく必要な情報にアクセスできます。
カテゴリ列で絞り込み、該当する原文を連続して読み込むことで、判断基準を一定に保ちやすくなります。また、ピボットテーブルを使用する場合はスライサーを設置しておくことで、属性の切り替えが直感的になります。
データの入力規則
カテゴリ入力をドロップダウン形式にしたいときは、データの入力規則が有効です。カテゴリ名を別シートに一覧化し、それを参照して選択式に設定すれば、表記ゆれをほぼ排除できます。作業者が複数いる場合でも入力内容がそろうため、後の集計工程で支障が出ません。
さらに、入力メッセージを設定して「迷った場合は原文を確認」「複数要素なら行を分割」といった注意書きを表示させれば、分類の品質が安定します。エクセル側でルールに従いやすい仕組みを構築しておくことで、作業の属人化を防ぎ、ミスを未然に防止することができます。
エクセルでアンケートをまとめるデメリット
エクセルは優れたツールである一方、運用面ではいくつか注意すべき点も存在します。集計のしやすさだけで判断してしまうと、回収や共有の段階で予期せぬ課題に直面しかねません。あらかじめどのような負荷が発生するかを把握し、適切な対策を講じておきましょう。
回答状況をリアルタイムで追えない
エクセルは、基本的にすべての回答が集まった段階で分析を開始することになります。そのため、途中の回収率や未回答者の把握、リマインドの配信などを行うには、別の仕組みを用意しなければなりません。
リアルタイムで状況を確認したいのであれば、専用のフォームで回収を行い、回答データが自動的に表へ反映される仕組みを検討しましょう。
顧客情報・属性と回答を紐付けにくい
エクセルでは、顧客情報が別のファイルで管理されているケースが多く、情報の整合性を保つ作業が課題です。入力ミスやデータの欠損があると、情報の紐付けがうまくいかず、正確な集計ができなくなってしまいます。
こうした事態を避けるために、最初から回答者IDや属性列を必須項目に設定したり、回答フォーム側で自動的に属性を取得したりといった工夫をしましょう。紐付けが不十分だと、せっかくの貴重な意見も全体的な傾向を追うだけで終わってしまいます。
回収率と回答精度が落ちやすい
エクセルを用いた回収方法は、スマートフォンからの入力が困難であったり、ファイルを開いて保存した後に返信するという手順を負担に感じたりして、途中で回答を諦めてしまうケースが少なくありません。特に自由記述は、入力環境が不便だと内容が短文化したり、記述を省略されたりしやすいため注意しましょう。
回収段階でのストレスが増えるほど、得られる回答の質も下がってしまいます。回収作業はオンラインフォームなどで簡潔に済ませ、分析はエクセルで丁寧に行うという役割分担を意識しましょう。
これからCRM活用をはじめる人にみてほしいCRM・顧客管理資料3点セット
「CRM導入の失敗あるある7選」や「SFAとCRMの違い」「顧客管理レベルチェックシート」3つの資料がセットでダウンロードできます。
アンケートの作成・回収・分析は「Synergy!」がおすすめ
自由記述の分析に加えて回収や属性管理まで行う場合、エクセル単体ではどうしても工数が増えてしまいます。そこでおすすめなのが、当社の「Synergy!」です。ここでは「Synergy!」の活用について解説します。
「Synergy!」とは
「Synergy!」は、顧客データの管理を中心にメール配信やフォーム作成ができるCRMサービスです。Webフォームで集めた回答をデータベースで一元管理し、必要に応じてエクセル形式で出力できます。
紙やファイルでの回収に比べ、回答者はスマートフォンから手軽に入力でき、管理者側もリアルタイムで状況を把握しやすくなります。アンケートを一時的な調査で終わらせず、改善サイクルに組み込む際に最適な選択肢といえるでしょう。
参考記事：CRM・顧客管理システム「Synergy!（シナジー）」
「Synergy!」の特長・強み
「Synergy!」の強みは、データの収集と管理がスムーズに連携する点です。収集したデータはそのままエクセルへ出力できるため、自由記述のアフターコーディングは従来通りの手順で進められます。
さらに、メール配信機能と組み合わせて回答者へのフォローも行えるため、回収率の向上も期待できるでしょう。分析はエクセル、運用は「Synergy!」と役割を分けることで、手作業を減らしつつ効率的な運用が可能になります。
参考記事：CRM・顧客管理システム「Synergy!（シナジー）」のアンケート機能
「Synergy!」の事例
株式会社エスクリ様では、サービス品質向上のために15年間にわたり「Synergy!」を活用しています。既存システムと連携してメールでフォームを送付し、回答を顧客データに紐付けて蓄積する運用を確立しました。
定期的なアンケートで得た評価はスタッフへ共有され、モチベーション向上やナレッジの共有に役立てられています。その結果、顧客満足度は90％超を維持するに至りました。必要な機能に絞って契約でき、操作も容易なため、現在はアンケート結果を起点としたさらなる施策への活用も検討されています。
アンケートの自由記述のまとめ方に関するよくある質問（FAQ）
自由記述の集計方法は、扱う件数や作業体制によって最適なやり方が変わってきます。現場でよく悩むポイントを実務の観点から整理しました。
数千件ある自由記述を短時間でまとめるには？
データが数千件にのぼる場合、最初から全件を丁寧に分類するのは現実的ではありません。まずは大まかなカテゴリ案を作り、代表的なパターンを把握しましょう。
検索機能で頻出テーマを拾って分類の当たりをつけてからアフターコーディングに入ると、作業スピードが格段に上がります。
自由記述の集計結果はどうレポートにまとめるのが正解？
レポートは「数字、解釈、原文の根拠」という順番で構成すると読みやすくなります。カテゴリ別の件数や割合で全体像を示したのち、属性による違いに触れましょう。そのうえで上位カテゴリごとに原文を数件引用し、要点を短くまとめます。
自由記述は読み手の主観が入りやすいため、実際の意見を添えるだけで結果の納得感が大きく変わってきます。最後は具体的な改善アクションを2から5個程度に絞り、担当者と期限を明記して実務に落とし込みましょう。
ネガティブな意見とポジティブな意見を分ける方法は？
最も確実な方法は、担当者が読み込みながらラベルを付けていくやり方です。件数が多い場合は、不満や遅いといったネガティブ寄りのキーワードと、安心や早いといったポジティブ寄りのキーワードで事前に候補を抽出しておきましょう。
ただし、同じ単語でも文脈によって意味が変わるため、最終的な原文の確認は欠かせません。複数の要素が混ざった回答は分割して集計し、ネガティブな意見は改善へ、ポジティブな意見は強みの訴求へとつなげていきましょう。
まとめ
エクセルの関数やピボットテーブルを活用したアフターコーディングは、こうした生の声を分類・集計し、改善策を見つけ出すために有効な手法です。しかし、すべてエクセルや手作業に頼ってしまうと、データの統合に膨大な時間がかかり、せっかくの声を素早く施策に反映できないという運用面の課題が残ります。
こうした課題を解決できるのが、当社の「Synergy!」です。Webフォームで収集したアンケート結果を顧客データベースに直接蓄積できるため、回答者の属性データと自由記述が自動で紐付いた状態になります。集まったデータはワンクリックでエクセルに出力できるため、使い慣れた手法ですぐに分析作業に入れます。
「Synergy!」を提供するシナジーマーケティングは、システムの提供にとどまらず、回答率を高めるアンケートの設計から、集まったデータを活用したマーケティング施策の立案までを総合的に支援しています。「アンケートの集計やデータ整理に追われている」「顧客の声を素早く次の打ち手に変えたい」とお考えの方は、ぜひお気軽にご相談ください。
CRMシステム「Synergy!」の特長が機能別でわかる資料です！
「Synergy!」の具体的な製品機能については、ぜひ以下のフォームから資料をダウンロードしてご確認ください。
関連情報
※記載されている内容は掲載当時のものであり、一部現状とは内容が異なる場合があります。ご了承ください。