Excelの使い方｜ピポットテーブルの概要と操作方法

ピポットテーブルの概要と操作方法

Excelのピポットテーブルを使った集計について説明します。
例えば以下のようなA同じデータでも
B(支店を軸に)支店ごと,商品別に月単位の売上をまとめたり、
C(商品を軸に変え)商品ごと,支店別に月単位の売上をまとめる等、
複数の項目を簡単に表化する機能です。

Excelでピポットテーブルを使ってクロス集計する手順

1ピポットテーブルにしたい表の一部を選択
2[挿入]タブ
3[テーブル]項目
4[ピポットテーブル]

[ピポットテーブルの作成]ダイアログが開きます。

5値が入力された範囲もしくは命名されたテーブル名が自動で入力されます。
範囲を修正したい場合は手動で行って下さい。

6ピポットテーブルを新しいシートで挿入/作成するか、既存のワークシート内に作成するかの選択

悩んだら失敗した時にシートごと削除できるので[新規のワークシート]にしておけば良いと思います。

[ピポットテーブルのフィールド]を決める

ピポットテーブルレポートで何を基準に比較する資料を作りたいかによって1番最初に選ぶフィールド(項目)が変わってきます。

※以下は同じ資料を「異なる優先項目」でピポットテーブルを作成した例です。

A地域(支社など)を優先(グループ化)し、その中で売れ筋の商品と売上を比較するピポットテーブル

B調査報告の軸が[商品]で、各地域(支社など)の売上の内訳を分かりやすくするピポットテーブル

C日々の売上推移を主軸とし、売上の内訳の商品を報告する場合のピポットテーブル

どんな内容を分析/解析したいかによって一番最初に選ぶ項目を適宜変更してみて下さい。
元の表(テーブル)があればピポットテーブルは簡単に修正や新しいシートに別のレイアウトパターンが作成できるので、最初のうちはいくつか作成してみてからプレゼン効果が高いものを選択してみても良いかと思います。

ピポットテーブルのフィールド設定

実際の手順を Aの地域ごとの場合の作成例で説明していきます。

1地域にチェック
集計したい項目は 2[金額] の [合計]

※[合計]以外にも 3[値フィールドの設定]で

データの個数,平均,最大値,最小値などの集計を行うよう指定もできます。

更に 4をチェックし[商品]別にも分かるように修正します。

これで地域ごとの商品別売上を示すピポットテーブルが完成になります。

ピポットテーブルでは更に細かく資料を作成する事も可能です。
例えば地域ごとの商品別/日別売上表に修正してみます。

5[日付]にチェックを入れると以下のようなピポットテーブルに修正されます。

(感性やセンスもあるので一概には言えませんが)あまり見やすい表ではないと思います。

そこで日付をプルダウンで 6[列ラベルに移動]します。

地域ごとの商品別/日別売上表のピポットテーブルが以下のイメージのようになります。

ピポットテーブルのクロス集計の考え方を「ピポットテーブルフィールドの設定」に沿って説明をすると

赤枠の部分は以下の表を簡略表示したものです。
※ 4は指定されていませんが・・

実際に良く使われそうな項目を当てはめると

行ラベル(横軸の項目)は
1支店を優先項目
※つまり支店ごとにデータをグループ化
2支店の集計を商品別に行う

列ラベル(縦軸の項目)は
3年でまとめる
4月でまとめる
それぞれの縦軸、横軸に当てはまる値を自動集計させるのですが、その集計する項目は 5[売上]で集計方法は[合計]を指定
※5[売上]は[合計]ですが、仕入れ資料の場合は[個数]、テストの難易度を調べるためには[平均]など作成したい資料によって集計方法は変更してみて下さい。

※1と 5は必須ですが 2～ 4は必要に応じて指定して下さい。

つまり[縦軸]、[横軸]の項目を決め、それぞれに対応する[どの値]を[どのように集計]するかを決めればピポットテーブルは完成です。