AIでクロス集計表を作成する方法：完全ガイド
クロス集計表は、地域別の満足度、チャネル別のプラン層、年齢層別の回答など、ある変数ともう一つの変数を掛け合わせて集計するものです。本ガイドでは、読みやすい表にするために記載すべき内容と、手動で作成する方法について解説します。その後、アンケートや取引データの出力からクロス集計表を作成するための3つのステップを紹介します。
クロス集計表でわかること
単一変数の集計は、全体の結果を示します。「62%が選択肢Aを選んだ」というのは大まかな見出しに過ぎず、本当に必要な情報が隠されてしまいがちです。
クロス集計はその見出しを細分化します。一方の変数を行に、もう一方の変数を列に配置し、各セルに件数または割合を表示します。これにより、選択肢Aが、あるセグメントでは78%に選ばれ、別のセグメントでは40%に選ばれたといったことが一目でわかるようになります。
この言葉にはいくつかの類義語があります。クロス集計、分割表、二元配置表はすべて同じものを指します。スプレッドシートにおいて、これを作成するためのツールがPivotTableです。
Microsoftのサポートページでは、これを「データを計算、集計、分析するための強力なツール」と説明しています。同じ一節には、「データ内の比較、パターン、トレンドを把握できる」とも付け加えられています。これはまさに的確な表現であり、ここで最も重要なキーワードは「比較」です。
読みやすいクロス集計表にするために必要な記載事項
表には3つのラベルを記載する必要がありますが、手動で作成されたクロス集計表の多くは、そのうちの少なくとも1つが欠けています。
分母（ベース）。 すべての割合は何らかの数値を基準にしています。Excelでは、これがデフォルトではなくオプションとして明示されています。「総計に対する比率」など、「フィールドの比率として表示」することができます。入れ子状のレイアウトの場合は、「基準にする親フィールド」を選択することで「親行の合計に対する比率」として表示できます。
方向。 行の割合と列の割合は、同じ件数から出発しても異なる問いに答えることになります。行の割合は各セグメントが何を選んだかを示し、列の割合は各選択肢をどのセグメントが構成しているかを示します。
件数。 セルに80%と表示されていても、n=200の場合とn=5の場合では意味が大きく異なります。Microsoftのドキュメントには、両方を同時に表示する方法が記載されています。「値を値エリアに2回ドラッグ」し、それぞれの「値の集計方法」と「計算の種類」を設定します。
始める前に必要なもの
- 回答者、取引、またはチケットごとに1行で構成されたエクスポートデータ。
- クロス集計したい2つの変数（それぞれ独立した列にあること）。
- 作成を始める前に決めておく、割合の算出方向。
- 割合を表示しない基準となる、セルの最小サンプルサイズ。
- 整理されたヘッダー。Microsoftのガイドラインには、「すべての列にヘッダーがあり、各列に一意の空白でないラベルが1行だけあることを確認してください」と明確に記載されています。
この最後のポイントは、思った以上に重要です。同ページでは「2行のヘッダーや結合されたセルは避けてください」と警告しています。しかし、アンケートツールがエクスポートするデータは、まさにこれ（選択肢の行の上に質問の行がある状態）に該当します。
手動で行う方法
Option 1: PivotTableを作成する
これが標準的な手順です。Microsoftの手順説明はシンプルで、「挿入 > PivotTable を選択します」となっています。
フィールドの配置は自動的に行われますが、デフォルトの動作を知っておくと便利です。数値以外のフィールドは「行エリアに追加」され、日付と時刻のフィールドは「列エリア」に、数値フィールドは「値エリア」に配置されます。
前提条件として、データが整理された形状である必要があります。ガイドラインでは、「データは表形式で整理され、空白の行や列がないようにする必要があります」とされています。
Option 2: 手動で作成したグリッドにCOUNTIFS関数で集計する
縦方向に行ラベルを、横方向に列ラベルを書き出し、すべてのセルにCOUNTIFS関数を入力します。
この方法は透明性が高く、検証が容易であるため、数値の妥当性が疑われたときに大きな強みとなります。難点は、グリッドが固定されてしまうことです。翌月のエクスポートデータに新しい回答の選択肢が追加されても、そのための数式が書かれていないため、表はそれを静かに無視してしまいます。
Option 3: クエリツールで先にグループ化し、その結果を貼り付ける
スプレッドシートの外で集計を行い、整理された3列の結果を貼り付けてから形を整えます。
この方法はデータ量が多い場合に適しています。しかし、集計のロジックが同僚の目に見えない場所に置かれるため、「このグループ分けはどのように定義されたのか」と誰かに尋ねられたときに問題が生じます。
手動作業で行き詰まるポイント
最初の障害は、データ型の罠です。これについてはドキュメントにも記載されています。「デフォルトでは、値エリアに配置されたPivotTableフィールドは合計（SUM）として表示されます。Excelがデータをテキストとして解釈した場合、データは件数（COUNT）として表示されます。」さらに、同ページでは「値フィールドのデータ型を混在させないことが非常に重要である理由はここにあります」と警告しています。
数値列にテキストが1つでも紛れ込んでいると、合計が件数に切り替わってしまいます。それでも表自体は正しく見えてしまいます。
2つ目の障害は、グループ分け（バンディング）です。年齢、勤続年数、支出額などは、クロス集計を行う前にグループ化（バケット化）する必要がありますが、その境界線の引き方は判断に委ねられます。誰もそれを記録に残さないため、翌四半期の表では異なる境界線が使われてしまうことがあります。
3つ目は、データの陳腐化です。PivotTableは自動的には更新されません。ドキュメントには、「データソースに新しいデータを追加した場合、そのデータソースに基づいて作成されたすべてのPivotTableを更新する必要があります」と明記されています。
4つ目は、サンプルサイズの小さいセルの問題です。12の地域と6つの回答選択肢を持つクロス集計表には72のセルがあり、その多くには2〜3件の回答しか含まれません。このようなセルで割合を報告してしまうと、次の調査ウェーブでは再現されないような結果を生み出す原因になります。
Powerdrill Bloomでクロス集計表を作成する方法
Step 1: 回答レベルのエクスポートデータをアップロードする
回答者1人につき1行の生の出力データをPowerdrill Bloomにドラッグ＆ドロップします。FreeプランはExcel、CSV、PDF、およびドキュメントのアップロードに対応しているため、有料プランに加入していなくてもファイルをアップロードできます。
アンケートツールから出力されたデータが2行のヘッダーになっている場合は、そのままアップロードしてください。データを平坦化することは、前提条件ではなく、指示の一部として処理できます。
Step 2: 2つの変数、割合の方向、最小セルサイズを指定する
自然言語で指示を入力します。行の変数と列の変数を指定します。行の割合と列の割合のどちらが必要かを指定します。そして、割合の代わりに生の数値を表示すべき基準となる件数を指定します。
グループ分けの基準もここで指定します。年齢や支出額のバケットの開始位置と終了位置を指定することで、実行するたびに同じ境界線が適用されるようになります。
Step 3: 件数と割合を同時に表示する表を生成する
必要な出力を指示します。各セルに件数と割合が表示され、さらにすべての割合の分母（ベース）が記載されたクロス集計表を求めます。どの差異が注目に値するほど大きいかについての短いメモも追加します。Powerdrill Bloomの料金ページには、チャート、表、エクスポート機能を使用して、根拠に基づいた回答を得る方法が記載されています。
表をレポートに組み込む場合、Proプランを利用すれば、より高度なOfficeドキュメント出力が可能になります。AI report generatorのページで、レポート作成の手順について詳しく説明しています。
これにより削減できる作業
| タスク | 手動の場合 | アップロードされたデータから作成する場合 |
|---|---|---|
| 2行のアンケートヘッダーの平坦化 | 作業を始める前に手動で整形が必要 | 同じ処理の中で自動対応 |
| グループ分けの境界線の適用 | 期間ごとに再決定が必要 | 指示に一度記載するだけ |
| 件数と割合の同時表示 | フィールドを2回ドラッグし、個別に設定が必要 | 指示に含めるだけ |
| サンプルサイズの小さいセルの割合の非表示 | すべてのセルを手動で確認 | ルールを一度指定するだけ |
| 新しい回答が届いた後の更新 | 手動での更新が必要（忘れがち） | 新しいファイルに対して再度指示するだけ |
ベストプラクティス
分母（ベース）は脚注ではなく表内に記載する。 読者がn（サンプルサイズ）を探さなければならない場合、それが十分に大きいものと思い込んでしまいます。
1つの表につき、割合の方向は1つに絞る。 行の割合と列の割合が混在したグリッドは、たとえすべてのセルが正しくても、極めて読みにくくなります。
四捨五入するよりも非表示にする。 回答数が4件のセルに「25%」と表示するよりも、ダッシュ（-）を表示する方が誠実です。
生の件数を残しておく。 集計結果には異論が出ることがあります。件数のグリッドこそが議論を解決するものであり、他の方法で割合を再計算する際にも必要となります。survey data analysis roundupのページでは、この分野の幅広いツールについて紹介しています。
パターンが単純な場合のみチャートを追加する。 それぞれ3つのレベルを持つ2つの変数であれば、チャートとして見やすくなります。しかし、12×6の変数では見にくくなります。messy survey data guideでは、その境界線について解説しています。
結論
クロス集計表は簡単に作成できますが、誤解を招きやすいものでもあります。作成自体はPivotTableで行えますが、難しいのは数値を解釈可能にする3つのラベルと、誰も記録に残さないグループ分けの境界線です。
割合の方向、分母（ベース）、最小セルサイズを一度決めておきましょう。それらを記憶に頼るのではなく、指示書に書き留めておくことで、今四半期の表と前四半期の表の比較可能性を維持できます。
エクスポートデータはあるものの、それを整形する手間をかけたくない場合は、回答レベルのファイルを用意してPowerdrill Bloomをお試しください。ピボットテーブルを一切作成せずに集計を行いたい場合は、summarising Excel data without pivot tables（ピボットテーブルを使わずにExcelデータを集計する方法）を参照してください。
よくある質問
クロス集計表とは何ですか？
ある変数ともう一つの変数を掛け合わせて集計する表のことで、縦方向に行カテゴリ、横方向に列カテゴリが配置されます。各セルには件数、割合、またはその両方が表示されます。クロス集計、分割表、二元配置表はすべて同じものを指します。
クロス集計とPivotTableは同じものですか？
PivotTableは、クロス集計表を作成するためのスプレッドシートの機能です。Microsoftは、これを「比較やトレンドを把握できるようにデータを計算、集計、分析するツール」と説明しています。クロス集計は2つの変数を掛け合わせた結果であり、PivotTableはそれ以外のレイアウトを作成することも可能です。
行の割合と列の割合のどちらを使うべきですか？
答えようとしている問いに合わせて選択してください。行の割合は各セグメントが何を選んだかを示し、列の割合は各選択肢をどのセグメントが構成しているかを示します。1つのグリッドに両方を混在させると、表が極めて読みにくくなります。
割合を報告するための最小セルサイズはどれくらいですか？
万人に共通する数値はありませんが、重要なのは基準を1つ決めて一貫して適用することです。多くのチームでは、セル内の回答数が約30件未満の場合は割合を非表示にし、代わりに生の件数を表示しています。
作成したPivotTableで、合計ではなく件数が表示されるのはなぜですか？
Microsoftのドキュメントで直接説明されています。デフォルトでは、値は合計（SUM）として表示されます。Excelがデータをテキストとして解釈した場合、件数（COUNT）として表示されます。一般的な原因は、本来は数値であるはずの列に、数値以外の入力が1つ紛れ込んでいることです。