ピボットテーブルの使い方・初心者へ完全ナビ

ピボットテーブル

ピボットテーブルの使い方・初心者へ完全ナビ

ピボットテーブルはデータベースから簡単に集計表を作成できるExcelの機能です。しかも、視点や切り口を変えて異なった集計表にトランスフォームさせることができ、多方向からの分析が可能。プレゼン資料の作成作業を段違いに効率化できる優れもので、ピボットテーブルを使いこなせれば応用範囲が果てしなく広がります。

ピボットテーブルとは?

日々の売上データの表があります。これを元に必要な集計表を作って現状を分析することになりました。ここで活躍するのが、ピボットテーブルです。
売上データの表
ピボットテーブルは、簡単なマウス操作だけで様々なデータ分析用の集計表を立ちどころに作成してくれます。売上データの元表から、日付ごと、商品ごとの集計、曜日と商品のクロス集計といった異なる切り口の集計表を簡単に生み出してくれるのです。
ピボットテーブルで作った集計表

クロス集計表とは?

集計表には、行に項目を置き、対応する数値を次の列に並べるシンプルな構造のものと、
行に項目を置いたシンプルな集計表
行(縦軸)と列(横軸)に項目を置いて、交差する位置に集計値を置くクロス集計表があります。
クロス集計表
ピボットテーブルの得意技、専門分野は「クロス集計」の方です。
手作業で組み立てようとすればひどく手間のかかるクロス集計表を手早く自在に作れるのが、ピボットテーブルの大きく優れた点です。

元データの作り方

ピボットテーブルの操作は拍子抜けするほど簡単です。厄介なのは、むしろ、集計に使う元データの作成と整備です。ピボットテーブルがデータ自体を生成してくれるわけではないので、集計の元となる表の作成だけはきっちりやっておかなければいけません。

元データの表はデータベースの作法に則って作成されている必要があります。データベースの基礎、表の作成方法、データの整備・統一化については以下にまとめてあります。

データベース・表

それともう一つ、元となるデータは表のままでも構わないんですが、できればテーブル化しておくことをお勧めします。データを管理する機能が細やかに装備されているので、追加や修正がスムーズに行えます。

表をテーブル化する手順・テーブル機能で何がどう変わるのか・テーブルの使い方。

テーブル機能の使い方|データ表にはテーブルを使うべき!

ピボットテーブルでクロス集計を作成

実際に機能を使ってみながら手順や構成を覚えるのが習得の早道だと思うので、ピボットテーブルを操作してクロス集計表を作ってみましょう。

ピボットテーブルの開始

表内のセルをどれか一つ選択し、「挿入」タブのリボン左端にある「ピボットテーブル」ボタンをクリックします。
「挿入」→「ピボットテーブル」をクリック
ピボットテーブルの作成」ダイアログが開き、「テーブル/範囲」に元表のテーブル名が入っています。特定の範囲をドラッグで指定し直すこともできます。
ダイアログの下方に「ピボットテーブルレポートを配置する場所を選択してください」とあり、ピボットテーブルの作成場所を聞いています。デフォルトで「新規ワークシート」がオンになっているので、特段の理由がない限り、このままで「OK」します。
ピボットテーブルの作成ダイアログボックス
新しいシート「Sheet1」が追加され、「ピボットテーブルのフィールド」という作業ウィンドウが開きました。
ピボットテーブルのフィールド作業ウィンドウ

「ピボットテーブルのフィールド」作業ウィンドウ

「ピボットテーブルのフィールド」で行うのは、列項目、行項目に置く「フィールド」と集計する値の「フィールド」の指定です。ここで集計表の大枠を作るわけですね。
作業ウィンドウには元表の「フィールド=列の項目」名が並ぶエリアと、「フィルター」「列」「行」「値」の各エリアがあります。
「ピボットテーブルのフィールド」作業ウィンドウの構成
「フィルター」はこの段階では用がありません。クロス集計表の大枠を設計するのは「列」「行」「値」の3つのエリアです。
「列」「行」「値」エリアとピボットテーブルの対応図
「列」と「行」に入るのは項目名。日付、商品名、地名などなど。「値」には数値が入ります。

最初の作業は「列」と「行」に配置するフィールドの選択です。これは、何を分析したいかで自ずと決まってきます。
例えば、新製品のバージョンごとに年代別の売筋を分析したいとか、系列店舗ごとに月別の売上を比較したいとか、目的がはっきりしているなら、縦軸(行)と横軸(列)に入れるフィールドを選択するのに迷うことはありません。

ここでは、ヘアサロンの「日付」ごとに「施術メニュー」別の売上金額を比較する表を作成してみることにしましょう。
元表には7つのフィールド(列項目)があり、作業ウィンドウに並んだフィールドと対応しています。
フィールド(列項目)
この中で、「日付」を「行」フィールドに、「施術メニュー」を「列」フィールドに指定します。
「行」フィールド「列」フィールドに置く項目
作業ウィンドウの「日付」フィールド名をドラッグして、「行」エリアに配置します。配置と同時に「日付」フィールド名にチェックが付きます。
「日付」フィールド名をドラッグして「行」エリアに配置
同様にして、「施術メニュー」フィールドを「列」エリアにドラッグします。
「施術メニュー」フィールド名をドラッグして「列」エリアに配置
最後は売上金額の「合計」フィールドを「値」エリアにドラッグします。
「合計」フィールド名をドラッグして「値」エリアに配置
クロス集計表の出来上がりです。
クロス集計表の完成

元表の集計行に注意

元となる表に集計行があると、ピボットテーブルに「空白」列(行)ができてしまうので注意しましょう。
元表に集計行があるとピボットテーブルに「空白」列(行)ができる
テーブル化した表で、「テーブルデザイン」タブの「集計行」にチェックを入れて表示した場合には「空白」はできません。
「テーブルデザイン」タブの「集計行」にチェック

フィールドの移動と削除

「ピボットテーブルのフィールド」作業ウィンドウでは、設定した各エリアのフィールドを削除したり、別エリアに移動させたりするのも直感的に行えます。

エリア内のフィールド名を作業ウィンドウの外へドラッグするだけで削除できます。マウスポインタの下に✗(バツマーク)が出たら、マウスを放せばOK。
フィールド名を作業ウィンドウの外へドラッグ
同様に、列エリアへドラッグすれば、列見出しに入ります。

フィールド名の右横にある▼を押して、コンテキストメニューから削除や移動を選ぶこともできますし、
フィールド名の右横にある▼から操作を選択
削除だけなら、フィールド名のチェックを外すことでも実行できます。
フィールド名のチェックを外して削除

ピボットテーブルのデザインの変え方、見やすいレイアウトのために空白行を挿入する操作を解説しています。

ピボットテーブルのレイアウトとスタイル

元表のデータを追加したり、修正・削除したら、ピボットテーブルを更新する必要があります。

ピボットテーブルを更新して、元データの変更を反映させる

フィールドの階層とグループ化

ピボットテーブルでは列・行に配置したフィールドに「地区」→「店舗」→「担当者」というような階層構造を持たせたり、「商品」フィールドの項目をグループ化して幾つかのカテゴリーに分類するといったことができます。

フィールドの階層化・グループ化のやり方と、多角的な分析の実行。

ピボットテーブルでクロス集計|フィールドの階層とグループ化

値フィールドの設定

ピボットテーブルでは、「値フィールドの設定」の「集計方法」で、様々な集計列を表示させることができます。
また、煩雑な操作も関数も無しに「計算の種類」リストから選ぶだけで分析に役立つ「構成比」「累計」「前日比」「前月比」を表に加えることもできます。

集計方法

「値フィールドの設定」→「集計方法」で、「合計」の横に、データの「個数」「平均」「最大(小)値」「標準偏差」を並べる操作の解説です。

合計と並べて個数・平均・最大(小)値・標準偏差を表示

計算の種類

「値フィールドの設定」→「計算の種類」で、「構成比」「累計」「前日比」「前月比」を表に組み込む操作の解説です。

構成比・累計・前月比・前日比を表示

「フィールド/アイテム/セット」

「フィールド/アイテム/セット」には、ピボットテーブルにオリジナルな集計を追加できる機能「集計フィールド」と「集計アイテム」があります。

「集計フィールド」と「集計アイテム」の違いを理解しましょう。

集計アイテムと集計フィールドの違いを徹底解説

集計フィールド

「集計フィールドの挿入」とは、数式を指定してピボットテーブルに独自の集計列を表示させる機能です。

「集計フィールド」を挿入して消費税額の集計列を表に加える手順と、集計列同士を足し算させる実例を挙げて基本操作を解説します。

集計フィールドの挿入・編集・削除

集計アイテム

「集計アイテム」は、あるフィールドに、項目単位で集計したオリジナルなアイテムを追加する機能です。

同種の項目の総計に対し、個別の項目ごとの構成比を表示させる例題で、「集計アイテム」挿入の基本操作を解説していきます。

集計アイテムを挿入する基本手順

列と行の2軸に配置したフィールドそれぞれに集計アイテムを追加して、一つのピボットテーブルで2方向からの分析を可能にする提案例です。企画書などで少し小洒落たコンパクトな見せ方をしたい時に参考にして下さい。

クロス集計表の列・行に集計アイテムを追加する

集計アイテムを活用しようとしてエラーの警告画面が出たり、集計アイテムが起動できなかったりという場面に出くわすかもしれません。その主な事例と対処法をまとめました。

集計アイテムがうまくいかない事例と対処法

タイトルとURLをコピーしました