Excelのピボットテーブルでクロス集計・複数回答の集計を効率化しよう

アンケート結果の集計を効率化したい場合に便利なのが、Excelのピボットテーブル機能です。簡単な操作で、様々な切り口の集計を自動化することができます。今回は、ピボットテーブルでクロス集計や複数回答の集計を行う基本手順を解説します。

Excelのピボットテーブルとは

まずは、ピボットテーブルとは何かを見ていきましょう。

関数を使わずに集計ができる

ピボットテーブルは、Excelに搭載されている集計機能です。ドラッグ&ドロップなどの簡単なマウス操作だけで、大量のデータの集計や分析を行うことができます。複雑な計算式や関数を使う必要がなく、データ項目を配置するだけで自動集計されるため、手間のかかるアンケート集計の効率化につながります。

ピボットテーブルを起動させる手順は以下です。

1.メニュータブの「挿入」から「ピボットテーブル」を選択
2.ダイアログボックス「ピボットテーブルの作成」で集計するデータ範囲を選択
3.ピボットテーブルを作成する場所(ワークシート)を選択

そうすると、ピボットテーブルを作成するエリアと、集計項目の設定を行う作業フィールドが表示されます。

関数を使わずに集計ができる

作業フィールド上部の項目を選択し、下部の「列」「行」「値」にドラッグ&ドロップすると、左側に集計結果が表示されます。特定の項目でデータを絞りたい場合は、「フィルター」に項目を追加します。

関数を使わずに集計ができる

ピボットテーブルを使用する前の準備

ピボットテーブルで集計を行うには、Excelの元データの形式を整えておく必要があります。元データに次のような点があると、エラーになるので注意しましょう。

●1行目(列の見出し)に項目名が記載されていない
●空白セルがある
●結合セルがある
●項目・値の表記が統一されていない

ピボットテーブルを使用する前の準備

ピボットテーブルでクロス集計を行う方法

ピボットテーブルを用いれば、アンケート結果のクロス集計も簡単に行うことができます。

基本手順

<属性クロス集計>
性別・年齢・居住地域など、属性ごとにクロス集計を行う場合の基本手順は以下です。
ここでは、20代~50代の男女に「旅行で行きたい地域」と「旅行で重視すること」を聞いたアンケートの結果について、男女別に集計します。

まず、集計したいデータ範囲を指定し、ピボットテーブルを起動させます(上記参照)。

ピボットテーブルが表示されたら、作業フィールドの項目を以下のように設定します。
●「性別」を「行」のボックスへドラッグ
●「旅行で行きたい地域」を「列」のボックスへドラッグ
●「性別」を「値」のボックスへドラッグ

ピボットテーブルでクロス集計を行う方法

設定に従って、男女別のクロス集計表が自動作成されます。

ピボットテーブルでクロス集計を行う方法

<設問間クロス集計>
複数の設問間を掛け合わせる「設問間クロス集計」も、基本手順は属性クロス集計と同じです。ただし、あらかじめ各設問の回答を表にまとめておく必要があります。

ピボットテーブルでクロス集計を行う方法

作業フィールドで「設問1」を「行」に、「設問2」を「列」に、「回答者」を「値」にドラッグすると、クロス集計結果が表示されます。

ピボットテーブルでクロス集計を行う方法

集計方法の変更・追加

集計方法や計算の種類を変更したい場合は、作業フィールドの「値」→「値フィールドの設定」で設定します。平均値や最大値もワンクリックで算出されます。

集計方法の変更・追加

また、集計項目を追加することもできます。例えば、データの個数(数量)に加えて総数に対する比率も求めたい場合は、「値」に項目を追加し、「値フィールドの設定」→「計算の種類」→「総計に対する比率」を選ぶと集計表に反映されます。

集計方法の変更・追加

集計方法の変更・追加

表示形式の変更

パーセント表示の桁数の調整など、データの表示形式を変更したい場合は、「値フィールドの設定」→「表示形式」で一括変更できます。

表示形式の変更

フィルターで絞り込む

フィルターを活用すれば、全体の集計結果を別の切り口で分析することができます。

例えば、年代別の集計結果を性別で絞り込みたい場合、「性別」の項目を「フィルター」に追加します。そうすると、ピボットテーブル上部にフィルターボタンが表示されます。

表示形式の変更

フィルターボタンをクリックしてメニューから「女性」を選択すると、女性のみの年代別の集計結果が表示されます。

表示形式の変更

表示形式の変更

ピボットグラフ

クロス集計の結果は「ピボットグラフ」でグラフ化することができます。
集計後のピボットテーブルを選択し、メニューバーの「ピボットテーブル分析」→「ピボットグラフ」で作成できます。

ピボットグラフ

ピボットグラフ

ピボットグラフ

ピボットテーブルで複数回答を集計する方法

複数回答の結果も、ピボットテーブルで簡単に集計することが可能です。

基本手順

複数回答の結果は、以下の表のように一つのセルにまとめられている場合がありますが、このままではピボットテーブルで集計することができません。

ピボットテーブルで複数回答を集計する方法

そのため、以下のように回答項目ごとに列を分け、選択した項目に「1」を、選択しなかった項目に「0」を入力します。これでピボットテーブルでの集計が可能になります。

ピボットテーブルで複数回答を集計する方法

男女別のクロス集計がしたい場合は、作業フィールドのすべての回答項目を「値」へ、「性別」を「行」へドラッグします。

ピボットテーブルで複数回答を集計する方法

「行」の項目を「出身地」に入れ替えれば、出身地別のクロス集計表を作成できます。

ピボットテーブルで複数回答を集計する方法

回答を複数セルに分割する方法

回答を複数セルに分割する方法

なお、一つのセルにまとめられたマルチアンサーの回答結果は、複数のセルに分割することでデータ整理や集計がしやすくなります。

1.データ範囲を選択
2.「データ」→「区切り位置」を選択
3.「区切り位置指定ウィザード」で「カンマやタブなどの区切り位置によってフィールドごとに区切られたデータ」を選択
4.該当する「区切り文字」を選択(選択肢にない場合は「その他」に入力)
5.列のデータ形式を選択

回答を複数セルに分割する方法

アンケート集計を効率化してスピーディな意思決定を

アンケート結果を受けてスピーディに意思決定・アクションを起こすためには、アンケートの集計・分析を速やかに行うことが重要です。Excelで大量のデータを集計するには関数などを駆使する必要がありマンパワーを要しますが、ピボットテーブルであればクロス集計や複数回答の集計を自動化できます。

ここで紹介した手順を参考に、手間のかかるアンケート集計をピボットテーブルで効率化しましょう。

Twitter:ツイート Facebook:シェア はてなブックマーク:ブックマーク pocket:あとで読む
アンケートツールの最新活用事例をご紹介!資料ダウンロードはこちら

イベント・セミナー情報