アンケートの自由記述、問い合わせ履歴、口コミ、会議メモなど、文章として蓄積されたデータをExcelで分析したい場面は少なくありません。
専用の分析ツールを導入しなくても、Excelの関数、フィルター、ピボットテーブル、Power Queryを組み合わせれば、無料で基本的なテキストマイニングを始められます。
この記事では、1行目に見出しがあるサンプルデータを使い、単語の出現回数、関連語、傾向をExcelで確認する流れを解説します。
Excelでのテキストマイニングは、文章を整える、単語を分ける、集計する、結果を読むという順番で進めると分かりやすいです。
大量の自由記述をそのまま読むのではなく、頻出語と回答内容を行き来しながら確認することが、実用的な分析につながります。
まずは、Excelでできる範囲と、実際に作る分析表を確認していきましょう。
Excelで無料テキストマイニングを始める準備
それではまず、Excelだけで行うテキストマイニングの基本的な準備について解説していきます。
| 回答ID | 自由記述 | 分類 |
|---|---|---|
| 1 | 操作が簡単で集計も早い | 肯定的 |
| 2 | 画面が見にくくて操作に迷った | 改善要望 |
| 3 | 説明が分かりやすく便利だった | 肯定的 |
テキストマイニングで読み取れる情報
自由記述データには、数値の評価だけでは把握しにくい不満、期待、利用場面、具体的な要望が含まれています。
テキストマイニングとは、このような文章を単語や表現に分け、出現回数や組み合わせを調べて、全体の傾向を見つける分析方法です。
Excelを使う場合は、高度な自然言語処理を最初から目指すのではなく、頻出する言葉を正確に見つけることが第一歩です。
たとえば「操作」という語が多ければ、操作性に関する評価や課題を重点的に確認できます。
ただし、単語の件数だけで結論を出すのは危険です。
「操作が簡単」と「操作が難しい」はどちらも操作という語を含むため、前後の文脈や分類列もあわせて読まなければなりません。
テキストマイニングの結果は、回答を読む順番を決めるための地図として使うと効果的です。
頻出語を見つけた後に元データへ戻り、どのような文章で使われているかを確認しましょう。
分析用データを表形式に整える作業
分析前には、1行を1件の回答として扱える形にそろえます。
サンプルではA列を回答ID、B列を自由記述、C列を分類とし、1行目には列名を入力します。
空白行、途中の見出し、結合セルが含まれていると、フィルターやピボットテーブルが正しく動作しにくくなります。
回答文が複数のセルに分かれている場合は、できるだけ1つの文章を1セルにまとめておくと後の作業が楽になります。
入力後は表全体を選択して、挿入タブのテーブルを実行すると、行の追加や集計範囲の管理がしやすくなります。
テーブル名は分かりやすく「tbl回答」のように変更しておくと、Power Queryや数式で参照する際にも便利です。
原文を直接書き換えず、整形用の列を右側に追加する運用にすると、分析結果を見直すときに安心です。
目的に合わせた分析単位の決定
同じ文章データでも、何を知りたいかによって見るべき単位が変わります。
サービス全体の評価を知りたい場合は単語の出現回数が役立ち、部署別の意見を比べたい場合は分類列や属性列で絞り込みます。
「改善」と「料金」が同時に書かれている回答を探すなら、キーワード検索とフィルターが有効です。
また、対象期間を表す日付列、顧客区分、商品名、担当部署などを残しておくと、単語の増減を比較できます。
単に「多い語」を数えるだけでなく、誰が、いつ、どの場面で書いた語かを確認できるデータ構造が重要です。
【操作のポイント】自由記述だけを切り離さず、回答ID、日付、カテゴリなどの属性列を残したままテーブル化すると、分析後の深掘りがしやすくなります。
不要な文字を整える関数と置換
続いては、集計前に文章の表記をそろえる方法を確認していきます。
| 元の文章 | 整形後の文章 |
|---|---|
| Ｅｘｃｅｌ は 便利！ | Excel は 便利 |
| 操作、操作。操作 | 操作 操作 操作 |
全角半角と余分な空白の統一
同じ意味の言葉でも、「Excel」「Ｅｘｃｅｌ」「excel」が別々に集計されると、出現回数が分散してしまいます。
まずは表記ゆれを減らすために、CLEAN関数、TRIM関数、ASC関数を必要に応じて使います。
元の文章がB2にある場合、半角化と余分な空白の整理には次の式を使用できます。
=TRIM(ASC(CLEAN(B2)))
CLEAN関数は制御文字を取り除き、ASC関数は全角の英数字や記号を半角へ寄せ、TRIM関数は連続する空白を整理します。
ただしASC関数は日本語の全角文字を半角へ変換するものではありません。
変換後の文章は必ず数件を目視し、意図しない表記になっていないか確認しましょう。
数式を入力したら、セル右下のフィルハンドルを下へドラッグするか、ダブルクリックして最終行までコピーします。
句読点と記号を空白へ置換する方法
単語を空白で区切る分析を行う場合、句読点や括弧をそのまま残すと「便利です」と「便利」が別の語として扱われることがあります。
SUBSTITUTE関数を重ねると、対象の文字を空白へ置き換えられます。
B2の文章から句点、読点、感嘆符を空白へ変換する式です。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,”。”,” “),”、”,” “),”！”,” “)
Excelのバージョンによっては、複数の置換処理をPower Queryで行うほうが見通しよく作業できる場合もあります。
置換する文字は、実際のデータを見ながら追加してください。
ハッシュタグ、絵文字、URL、商品コードなどを削除するか残すかは、分析目的で判断します。
不要な記号を一律に消す前に、その記号が意味を持つデータではないかを確認する姿勢が大切です。
表記ゆれを辞書で管理する考え方
「使いやすい」「使い易い」「使いやすかった」のような表現は、人が読むと近い意味でも、機械的な集計では別の文字列です。
頻出する表記ゆれは、別シートに置換前と置換後を並べた辞書表として管理すると、作業を再利用しやすくなります。
たとえば「エクセル」を「Excel」に寄せる、「ログイン出来ない」を「ログインできない」に寄せるといったルールです。
完全一致で置換すると意味を変える可能性があるため、短すぎる語や固有名詞には特に注意しましょう。
【操作のポイント】元データ列は残し、関数やPower Queryで作成した整形済み列を別に用意すると、置換ルールの修正後も安全にやり直せます。
Power Queryによる単語分割と集計
続いては、文章を単語のような単位へ分割し、集計可能な一覧に変える方法を確認していきます。
| 回答ID | 分割後の単語 |
|---|---|
| 1 | 操作 |
| 1 | 簡単 |
| 1 | 集計 |
Power Queryへテーブルを読み込む手順
Power QueryはExcelに搭載されているデータ取得と変換の機能で、繰り返し行う整形作業に向いています。
元データのテーブル内を選択し、データタブからテーブルまたは範囲からを選びます。
Power Queryエディターが開いたら、自由記述列を複製して、原文を残したまま整形用の列を作ります。
この操作で、更新可能な変換手順としてデータを扱えるようになります。
元の表に回答が追加された後も、更新を実行すれば同じ変換を適用できる点がPower Queryの強みです。
区切り記号で列を分割して行へ展開
日本語を完全に単語へ分けるには形態素解析が必要になることがありますが、あらかじめ空白や改行で区切られたデータならExcelだけでも分割できます。
Power Queryで整形済みの文章列を選択し、列の分割から区切り記号による分割を選びます。
区切り記号に半角スペースを指定し、詳細オプションで行に分割を選択すると、1つの回答が複数行の語に展開されます。
このとき回答IDは各行に引き継がれるため、後からどの回答に含まれていた語かを確認できます。
空文字列の行ができた場合は、フィルターで空白を外します。
文章が「操作 簡単 集計 早い」のように空白で区切られていれば、分割後は操作、簡単、集計、早いを別々の行として数えられます。
日本語の通常文を自動で正確に分かち書きする機能ではない点は理解しておきましょう。
グループ化による出現回数の作成
分割後の単語列を選択してグループ化を実行し、グループ化の基準を単語、操作を行数のカウントに設定します。
これにより、各単語が何回登場したかを一覧にできます。
件数の列を降順で並べ替えれば、頻出語ランキングの完成です。
「する」「です」「これ」など、分析に役立ちにくい語は除外リストとして別管理すると、結果が読みやすくなります。
除外語は正解が一つではなく、分析テーマに応じて見直す対象です。
【操作のポイント】Power Queryの手順名を「句読点を置換」「空白で分割」「単語別に件数集計」のように変更しておくと、後からクエリを修正するときに迷いません。
関数を使ったキーワード出現回数の集計
続いては、Power Queryを使わず、関数で特定キーワードを数える方法を確認していきます。
| キーワード | 出現件数 |
|---|---|
| 操作 | 12 |
| 価格 | 8 |
| 説明 | 6 |
COUNTIF関数で回答件数を調べる方法
あらかじめ調べたい語が決まっている場合は、COUNTIF関数が手軽です。
自由記述がB列、キーワードがE2にあるときは、次の式でその語を含む回答数を数えられます。
=COUNTIF($B$2:$B$100,”*”&E2&”*”)
アスタリスクは任意の文字列を表すワイルドカードで、文章の途中にキーワードが含まれていても判定できます。
この式が数えるのは単語そのものの総数ではなく、キーワードを含む回答の件数です。
1つのセルに「操作」が3回含まれていても、COUNTIFでは1件として数えられます。
回答数を知りたいのか、文章中の総出現数を知りたいのかを、集計前に分けて考えましょう。
LEN関数とSUBSTITUTE関数で総出現数を数える方法
同じ文章内に現れる回数も含めて数えたい場合は、LEN関数とSUBSTITUTE関数を組み合わせます。
自由記述がB2からB100、キーワードがE2にある場合の例です。
=SUMPRODUCT((LEN($B$2:$B$100)-LEN(SUBSTITUTE($B$2:$B$100,E2,””)))/LEN(E2))
最初のLEN関数は元の文章の文字数を求めます。
SUBSTITUTE関数でキーワードを空欄へ置換した後の文字数を引くと、消えた文字数が分かります。
その差をキーワードの文字数で割ることで、1セル内に含まれる回数を計算できます。
最後にSUMPRODUCT関数で範囲全体の件数を合計します。
キーワードが空欄だとエラーや不適切な結果になりやすいため、検索語の入力セルには必ず文字を入れてください。
検索語リストとオートフィルの活用
E列に「操作」「価格」「説明」「サポート」などの検索語を縦に並べ、F列に集計式を入力すると、簡易的なキーワード表を作れます。
F2の数式を下へオートフィルすれば、E列の語に応じて参照先だけが切り替わります。
固定したいデータ範囲にはドル記号を付け、キーワードセルだけは行に合わせて動く参照にすることがポイントです。
数式のコピー後は、検索語を一つ変えて結果が連動するかを確認すると、参照ミスに早く気付けます。
【操作のポイント】短い語は他の語の一部にも一致します。必要に応じて前後の空白や表記ルールを利用し、意図しない一致を減らしてください。
ピボットテーブルとグラフによる傾向の可視化
続いては、集計済みの単語データをピボットテーブルやグラフで見やすくする方法を確認していきます。
| 単語 | 肯定的 | 改善要望 |
|---|---|---|
| 操作 | 10 | 14 |
| 説明 | 12 | 3 |
単語別件数をピボットテーブルで並べる方法
Power Queryで作った単語一覧には、単語、回答ID、分類などの列がある状態が理想です。
この一覧を選択して挿入タブからピボットテーブルを作成します。
行に単語、値に単語の個数、フィルターまたは列に分類を配置すると、カテゴリごとの語の使われ方を比べられます。
件数の多い順に並べ替え、上位の語だけを確認すると、最初に読むべきテーマを絞り込めます。
ピボットテーブルは、単語と属性列の組み合わせを何度も切り替えて見られる点が便利です。
棒グラフで頻出語を比較する方法
頻出語ランキングは、横棒グラフにすると長い単語でも読みやすくなります。
単語と件数の2列を選択し、挿入タブから集合横棒グラフを選びます。
上位10語から20語程度に絞ると、情報量が過剰になりにくく、報告資料にも使いやすい表示になります。
単語が多すぎると文字が重なり、分析の要点が伝わりません。
グラフのタイトルには、対象期間、対象件数、集計の条件を記載すると、後から見ても解釈しやすくなります。
共起語と原文確認を組み合わせる視点
より深く読むには、一つの回答内で一緒に現れた言葉を確認します。
たとえば「操作」という語が多くても、「簡単」と共に使われるのか、「難しい」「迷う」と共に使われるのかで意味は変わります。
Excelでは、操作を含む行をフィルターで抽出し、隣の文章を読んで関連語をメモするだけでも有効です。
関数で複数語を含む回答を探すなら、COUNTIFSやSEARCH関数を組み合わせる方法があります。
「操作」と「難しい」を両方含む回答を確認する場合は、補助列にSEARCH関数で判定を作り、TRUEの行をフィルターする方法が分かりやすいです。
共起の数だけで判断せず、必ず実際の回答文を読み、書き手の意図を確かめましょう。
【操作のポイント】グラフは結論を自動で出すものではありません。件数が目立つ語を起点に元の自由記述へ戻ることで、数字と文章の両方から判断できます。
まとめ 無料でデータを分析 エクセルでテキストマイニングを行う方法
Excelで無料のテキストマイニングを行うには、まず自由記述を1行1回答の表として整え、元データを保管したまま整形用の列を追加します。
表記ゆれ、余分な空白、句読点を整理すると、同じ意味の語が分散しにくくなります。
キーワードがあらかじめ決まっているならCOUNTIF関数で回答件数を数え、総出現数が必要ならLEN関数とSUBSTITUTE関数の組み合わせを使えます。
回答が増え続ける場合や、単語の分割と集計を繰り返したい場合は、Power Queryで手順を保存する方法が便利です。
さらにピボットテーブルで分類別に比較し、棒グラフで上位語を可視化すれば、読むべき回答を効率よく見つけられます。
頻出語は分析の入口であり、最終的な判断には必ず原文の確認が必要です。
まずは小さなアンケートデータから、単語の集計と元回答の確認を繰り返してみましょう。
