集計に「ピボットテーブル」を使うと、簡単に「クロス集計」や「複合集計」ができる
「特定の年齢層」や「性別ごと」に、集計する必要がある場合はとても面倒な作業が必要です。
このような時、複数の条件をもとに「複合集計」や「クロス集計」をしますが、関数を使って作業をするのは大変です。
「ピボットテーブル」を使えば、簡単に「複合集計」や「クロス集計」をすることができます。
そしてピボットテーブルで集計をする最大のメリットは、「集計方法の変更が簡単にできる」ことです。
ファミレスのアンケートを集計
「性別」、「年齢」、「メニュー名」等のアンケート結果をリスト形式にしています。
この集計表から、特定のフィールド（項目）をもとに集計していきます。
「メニュー」と「年齢」でクロス集計
メニューと年齢でクロス集計した集計表です。
この集計表を関数を使って作成するためには「COUNTIF関数」を使います。
クロス集計にするためには「ネスト（入れ子）」構造にして複数の条件を設定する必要があり数式はさらに複雑になってしまいます。
※ Excel2007からCOUNTIF関数に複数の条件を設定できる、COUNTIFS関数が追加されました。
数式は「 =COUNTIFS($F$3:$F$52,$M58,$D$3:$D$52,N$56) 」のようにとても複雑になってしまいます。
数式を使う方法でも慣れてしまえば比較的簡単ですが、問題は「集計方法」を変更するたびに複雑な「数式」を作り変える必要があることです。
ピボットテーブルは、フィールド（項目）をドラッグするだけで集計方法を変更できます
ピボットテーブルを使えばグラフの作成も簡単です
「年齢」、「性別」の複合条件で集計し、さらに「メニュー」をクロス集計して、積み上げ棒グラフを作成。
「年齢」と「性別」の複合条件で集計し、「メニュー」でクロス集計したグラフ
「スライサー」を加えることで、条件を絞り込むことができます
「メニュー名」のスライサーを追加
スライサーを使って「ステーキ」に絞り込みます
ステーキの「グラム別」の集計になり、情報が分かり易くなりました。
- 「350gのステーキ」は20台の男性のみが注文
- 女性は「150gのステーキ」しか注文していない
- 男性は「150gのステーキ」は注文していない
グラフにすれば、情報が伝わり易くなります
ピボットテーブルの具体的な操作方法
集計表から「ピボットテーブル」のシートを作成します
- 表のどこか１カ所を選択します
- 「挿入」タブを選択
- 「ピボットテーブル」をクリック
- ピボットテーブルの作成ボックスで「OK」をクリック
別シートで「ピボットテーブル」が作成されました
集計方法を指定します
- 「性別」のフィールドを「行」のボックスにドラッグ
- 「年齢」のフィールドを「行」のボックスの「性別」の下にドラッグ
- 「メニュー名」を「列」のボックスにドラッグします
- 「メニュー名」を「∑値」のボックスにドラッグします
※ 「メニュー名」を∑値に配置すると、自動的に「個数」に設定されます。
「年齢」の区切りが細かすぎるので、１０歳区切りの「年齢層」にします
「20代・30代･･･」の様に、集計の区切りを変更したい場合には「グループ化」をすることで、区切りの「単位」を変更することができます。
- 「年齢」のフィールドを選択します
- 「グループ化」をクリックします
- 先頭の値を「20」に設定します
- 末尾の値を「50」に設定します
- 単位を「10」に設定します
- 「OK」ボタンをクリック
「年齢層」が１０歳ごとにグループ化されました
先頭・末尾は自動的に「より大きい”＞”」、「より小さい”＞”」になります
グラフを作成します
- 「ピボットテーブル」のどこか1カ所を選択します
- 「ピボットテーブルツール」を選択します
- 「分析」タブを選択
- 「ピボットグラフ」をクリックします
- 「積み上げ縦棒」を選択します
「年齢」と「性別」の複合条件で「メニュー名」でクロス集計したグラフ
グラフの体裁を整えます
- 「グラフの凡例」部分で「右クリック」します
- 「凡例の書式設定」をクリックします
「凡例」をグラフの「下部分」に移動します
- 凡例の書式設定のサイドバーから「凡例のオプション」を選択します
- 「凡例のオプション」を選択
- 凡例のオプションで「下」にチェックを入れる
「グラフ スタイル」を変更します
- グラフのどこか一部にカーソルを置いて「ピボットテーブルツール」を選択
- 「デザイン」タブを選択
- 「グラフスタイル」で好みのスタイルを選択します
ピボットテーブルを使えば集計方法を簡単に変更できます
「フィールドリスト」が表示されていない場合は、表示させます
- 「ピボットテーブルツール」を選択
- 「フィールドリスト」をクリックします
「スライサー」を作成します
- 「ピボットテーブルツール」を選択
- 「分析」タブを選択
- 「スライサーの挿入」をクリック
- 「メニュー名」にチェックを入れます
「複数」を選択することもできます
- 「複数選択」をすることができます
- 「フィルターのクリア」で選択を全解除できます
メニュー別に「どの年齢層」に人気があるかを集計
「メニュー」別と「年齢」別のクロス集計
- 列のボックスに「年齢」を配置
- 行のボックスに「メニュー名」を配置
- ∑値のボックスに「合計／番号」を配置
※ ∑値の集計方法は、自動で設定されます
※ 変更する場合は、ラジオボタン「▼」をクリック
「年齢別」に「どのメニュー」が好まれているかを集計
「メニュー別」と「年齢別」のクロス集計
- 列のボックスに「メニュー名」を配置
- 行のボックスに「年齢」を配置
- ∑値のボックスに「合計／番号」を配置
「年齢別」と「性別」の、「複合条件」にします
行のボックスの「年齢」の下に「性別」を配置して、複合条件にします。
このように「ピボットテーブル」を使えば、簡単なドラッグ操作で色々な方法で集計することができます。
さらにグラフを加えることが簡単にできるので、視覚的な資料を作成することができます。
今回は「スライサー」を説明しましたが、「タイムライン」を使えば時間や日付の期間設定も簡単にできます。
- 集計に「ピボットテーブル」を使うと、簡単に「クロス集計」や「複合集計」ができる
