ピボットテーブルの使い方・初心者へ完全ナビ
ピボットテーブルはデータベースから簡単に集計表を作成できるExcelの機能です。しかも、視点や切り口を変えて異なった集計表にトランスフォームさせることができ、多方向からの分析が可能。プレゼン資料の作成作業を段違いに効率化できる優れもので、ピボットテーブルを使いこなせれば応用範囲が果てしなく広がります。
ピボットテーブルとは?
日々の売上データの表があります。これを元に必要な集計表を作って現状を分析することになりました。ここで活躍するのが、ピボットテーブルです。
ピボットテーブルは、簡単なマウス操作だけで様々なデータ分析用の集計表を立ちどころに作成してくれます。売上データの元表から、日付ごと、商品ごとの集計、曜日と商品のクロス集計といった異なる切り口の集計表を簡単に生み出してくれるのです。
クロス集計表とは?
集計表には、行に項目を置き、対応する数値を次の列に並べるシンプルな構造のものと、
行(縦軸)と列(横軸)に項目を置いて、交差する位置に集計値を置くクロス集計表があります。
ピボットテーブルの得意技、専門分野は「クロス集計」の方です。
手作業で組み立てようとすればひどく手間のかかるクロス集計表を手早く自在に作れるのが、ピボットテーブルの大きく優れた点です。
元データの作り方
ピボットテーブルの操作は拍子抜けするほど簡単です。厄介なのは、むしろ、集計に使う元データの作成と整備です。ピボットテーブルがデータ自体を生成してくれるわけではないので、集計の元となる表の作成だけはきっちりやっておかなければいけません。
元データの表はデータベースの作法に則って作成されている必要があります。データベースの基礎、表の作成方法、データの整備・統一化については以下にまとめてあります。
それともう一つ、元となるデータは表のままでも構わないんですが、できればテーブル化しておくことをお勧めします。データを管理する機能が細やかに装備されているので、追加や修正がスムーズに行えます。
表をテーブル化する手順・テーブル機能で何がどう変わるのか・テーブルの使い方。
ピボットテーブルでクロス集計を作成
実際に機能を使ってみながら手順や構成を覚えるのが習得の早道だと思うので、ピボットテーブルを操作してクロス集計表を作ってみましょう。
ピボットテーブルの開始
表内のセルをどれか一つ選択し、「挿入」タブのリボン左端にある「ピボットテーブル」ボタンをクリックします。
「ピボットテーブルの作成」ダイアログが開き、「テーブル/範囲」に元表のテーブル名が入っています。特定の範囲をドラッグで指定し直すこともできます。
ダイアログの下方に「ピボットテーブルレポートを配置する場所を選択してください」とあり、ピボットテーブルの作成場所を聞いています。デフォルトで「新規ワークシート」がオンになっているので、特段の理由がない限り、このままで「OK」します。
新しいシート「Sheet1」が追加され、「ピボットテーブルのフィールド」という作業ウィンドウが開きました。
「ピボットテーブルのフィールド」作業ウィンドウ
「ピボットテーブルのフィールド」で行うのは、列項目、行項目に置く「フィールド」と集計する値の「フィールド」の指定です。ここで集計表の大枠を作るわけですね。
作業ウィンドウには元表の「フィールド=列の項目」名が並ぶエリアと、「フィルター」「列」「行」「値」の各エリアがあります。
「フィルター」はこの段階では用がありません。クロス集計表の大枠を設計するのは「列」「行」「値」の3つのエリアです。
「列」と「行」に入るのは項目名。日付、商品名、地名などなど。「値」には数値が入ります。
最初の作業は「列」と「行」に配置するフィールドの選択です。これは、何を分析したいかで自ずと決まってきます。
例えば、新製品のバージョンごとに年代別の売筋を分析したいとか、系列店舗ごとに月別の売上を比較したいとか、目的がはっきりしているなら、縦軸(行)と横軸(列)に入れるフィールドを選択するのに迷うことはありません。
ここでは、ヘアサロンの「日付」ごとに「施術メニュー」別の売上金額を比較する表を作成してみることにしましょう。
元表には7つのフィールド(列項目)があり、作業ウィンドウに並んだフィールドと対応しています。
この中で、「日付」を「行」フィールドに、「施術メニュー」を「列」フィールドに指定します。
作業ウィンドウの「日付」フィールド名をドラッグして、「行」エリアに配置します。配置と同時に「日付」フィールド名にチェックが付きます。
同様にして、「施術メニュー」フィールドを「列」エリアにドラッグします。
最後は売上金額の「合計」フィールドを「値」エリアにドラッグします。
クロス集計表の出来上がりです。
元表の集計行に注意
元となる表に集計行があると、ピボットテーブルに「空白」列(行)ができてしまうので注意しましょう。
テーブル化した表で、「テーブルデザイン」タブの「集計行」にチェックを入れて表示した場合には「空白」はできません。
フィールドの移動と削除
「ピボットテーブルのフィールド」作業ウィンドウでは、設定した各エリアのフィールドを削除したり、別エリアに移動させたりするのも直感的に行えます。
エリア内のフィールド名を作業ウィンドウの外へドラッグするだけで削除できます。マウスポインタの下に✗(バツマーク)が出たら、マウスを放せばOK。
同様に、列エリアへドラッグすれば、列見出しに入ります。
フィールド名の右横にある▼を押して、コンテキストメニューから削除や移動を選ぶこともできますし、
削除だけなら、フィールド名のチェックを外すことでも実行できます。
ピボットテーブルのデザインの変え方、見やすいレイアウトのために空白行を挿入する操作を解説しています。
元表のデータを追加したり、修正・削除したら、ピボットテーブルを更新する必要があります。
フィールドの階層とグループ化
ピボットテーブルでは列・行に配置したフィールドに「地区」→「店舗」→「担当者」というような階層構造を持たせたり、「商品」フィールドの項目をグループ化して幾つかのカテゴリーに分類するといったことができます。
フィールドの階層化・グループ化のやり方と、多角的な分析の実行。
値フィールドの設定
ピボットテーブルでは、「値フィールドの設定」の「集計方法」で、様々な集計列を表示させることができます。
また、煩雑な操作も関数も無しに「計算の種類」リストから選ぶだけで分析に役立つ「構成比」「累計」「前日比」「前月比」を表に加えることもできます。
集計方法
「値フィールドの設定」→「集計方法」で、「合計」の横に、データの「個数」「平均」「最大(小)値」「標準偏差」を並べる操作の解説です。
計算の種類
「値フィールドの設定」→「計算の種類」で、「構成比」「累計」「前日比」「前月比」を表に組み込む操作の解説です。
「フィールド/アイテム/セット」
「フィールド/アイテム/セット」には、ピボットテーブルにオリジナルな集計を追加できる機能「集計フィールド」と「集計アイテム」があります。
「集計フィールド」と「集計アイテム」の違いを理解しましょう。
集計フィールド
「集計フィールドの挿入」とは、数式を指定してピボットテーブルに独自の集計列を表示させる機能です。
「集計フィールド」を挿入して消費税額の集計列を表に加える手順と、集計列同士を足し算させる実例を挙げて基本操作を解説します。
集計アイテム
「集計アイテム」は、あるフィールドに、項目単位で集計したオリジナルなアイテムを追加する機能です。
同種の項目の総計に対し、個別の項目ごとの構成比を表示させる例題で、「集計アイテム」挿入の基本操作を解説していきます。
列と行の2軸に配置したフィールドそれぞれに集計アイテムを追加して、一つのピボットテーブルで2方向からの分析を可能にする提案例です。企画書などで少し小洒落たコンパクトな見せ方をしたい時に参考にして下さい。
集計アイテムを活用しようとしてエラーの警告画面が出たり、集計アイテムが起動できなかったりという場面に出くわすかもしれません。その主な事例と対処法をまとめました。