ピボットテーブルのはじめの一歩

ピボットテーブルは、テーブル (形式の表) のデータをデータソースとし、別の場所にピボットテーブルという場所を用意して、集計した結果を表示し、データ分析などに活用できる機能です。

基本的な作り方について最近は書いてなかったので、あらためて書きます。
テーブルに記録されているデータをもとに、エリアごとや担当者ごと、日付 × 担当者などの集計表を作成でき、データソースに変更があったら、ピボットテーブルを更新して最新の集計結果を表示します。

テーブルに変換する

データソースは、テーブル形式 (1 行目にだけ見出しのあるセル結合や小計行のない一覧) である必要があります。形が整っていればテーブルに変換してなくてもよいけれど、変換しておいたほうが更新などをスムーズに行えます。
ここでは商品の出荷数量をまとめたセル範囲をテーブルに変換してからピボットテーブルを作ります。

セル範囲内にアクティブセルをおいて、リボンの [挿入] タブの [テーブル] グループの [テーブル] をクリックします。
[テーブルの作成] ダイアログボックスのセル範囲を確認し、必要なら調整して [OK] をクリックします。
テーブルに変換されます。
テーブルの中にアクティブセルをおいて、リボンの [テーブルデザイン] タブの [プロパティ] グループの [テーブル名] にテーブルの名前が表示されます。
クリックして編集状態にし、わかりやすい名前にしておくとよいでしょう。

ピボットテーブルを準備する

テーブルを指定してピボットテーブルを準備します。

テーブルの中にアクティブセルをおいて、リボンの [テーブルデザイン] タブの [ツール] グループの [ピボットテーブルで集計] をクリックします。
[テーブルまたは範囲からのピボットテーブル] ダイアログボックスで、データソースのテーブル名が確認できます。
ピボットテーブルの作成場所を [新規ワークシート] または [既存のワークシート] から選択して指定します。
ここでは、テーブルの近くに作成したいので、[既存のワークシート] を選択し、場所としてセル I3 をクリックして指定して [OK] をクリックしています。
指定した場所にピボットテーブル (枠、場所) が準備され、[ピボットテーブルのフィールド] ウィンドウが表示されます。
[ピボットテーブルのフィールド] ウィンドウで、データソースのどの列をどんな目的で使うのかを決定できます。

合計したい列のデータを指定する

まずは [値] 領域を使って数値の合計または文字列の個数を算出することを考えます。
ここでは、数値データのみが格納されている [出荷数量] 列の数値の合計を求めます。

[ピボットテーブルのフィールド] ウィンドウの [出荷数量] (計算したい値の入っているフィールド) を [値] 領域にドラッグします。
[値] 領域にピボットテーブルのフィールドが作成され、[出荷数量] の合計値が表示されます。
ここでは、[出荷数量] 列に数値データのみが格納されているので、自動的に合計値が算出されます。
(文字列のセルが混ざっていると個数が算出されます)

何ごとの集計とするかを指定する

算出した合計値をエリアごとなどにわけて集計するには、[行] 領域と [列] 領域を使います。
どの列をクロス集計表の行の項目として、どの列を列の項目として使うのかを決めます。

ここでは、エリアごとの出荷数量とするため、[行] 領域にピボットテーブルの [エリア] フィールドを作ります。

[ピボットテーブルのフィールド] ウィンドウの [エリア] を [行] 領域にドラッグします。
[行] 領域に [エリア] フィールドが作成され、[出荷数量] の合計値がエリアごとに表示されます。
エリアごとの出荷数量の合計値、いうことです。
分析の切り口を変えたいときなど、指定したフィールドを除外したい場合はフィールドのチェックボックスをオフにします。
日付データだけが格納されているフィールドを [行] や [列] にドラッグしてピボットテーブルのフィールドを作成すると、ピボットテーブルの [日付] フィールドが作成されます。
既定で、日付が上位の単位でグループ化され、上位の単位の集計値も確認できるようになります。
グループ化そのものを解除することもできますが、[ピボットテーブルのフィールド] でチェックボックスをオフにして下位 (日付) 単位の集計値だけを表示することもできます。
[行] 領域に [日付] フィールドが作成され、[出荷数量] の合計値が日付ごとに表示されます。
日付ごとの出荷数量の合計値、いうことです。
クロス集計表にするには、[列] 領域も使います。
ここでは、[ピボットテーブルのフィールド] ウィンドウの [担当ID] を [列] 領域にドラッグしています。
[列] 領域に [担当ID] フィールドが作成され、[出荷数量] の合計値が日付ごとの担当ごとに表示されます。
日付ごと × 担当ごとの出荷数量の合計値を算出したクロス集計表、いうことです。