Excel使いこなしの基礎と応用

ピボットテーブルでクロス集計|作成から更新までの基本操作

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

目次


ピボットテーブルって何?

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

集計表とクロス集計表の違いは?

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

簡単なクロス集計表を作ってみる

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

スポンサーリンク

元になるデータ表の整備

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

元表はデータベースの作法に則って作成されている必要があります。主なチェックポイントを以下に挙げておきます。

これらの条件が満たされていないと、せっかくの機能を十分に活用できません。送り仮名1文字違っても正確な集計が阻害されるので、念を入れた整備が求められます。

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

ピボットテーブルの開始

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

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

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

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

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

フィールドの移動と削除

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

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

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

レイアウトとスタイルを選ぶ

分析に使う表はできるだけ見やすいレイアウトにしておきたいですね。資料として印刷するなら、書式も整えておく必要があります。
作成された集計表のセルをどれかクリックし、画面の最上部に出現した「ピボットテーブル ツール」ー「デザインをクリック。
ピボットテーブルツールのデザインを開く
レイアウトや書式といった見栄えに関する処理は全てこのリボンで行えます。

レポートのレイアウト

作成直後のレイアウトはセル幅が狭く、最もコンパクトにまとめた表示になっています。罫線も最小限なので、数値が判りにくいかもしれません。
これをもう少し伸び伸びした表示に変えるには、「ピボットテーブル ツール」ー「デザイン」タブの「レポートのレイアウト▼」をクリック。
プルダウンメニューで選べる形式はデフォルトの「コンパクト」と、「アウトライン」「表」の3種。この中で「表形式で表示」が一番見やすいレイアウトだと思います。
レポートのレイアウトで、表形式を選ぶ
下図左がコンパクトな表示、右が表形式の表示です。
コンパクトと表形式の表示の比較

スタイルの変更

もう一段スタイリッシュな見た目に仕上げたい時は、既製のデザインから選びましょう。
「ピボットテーブル スタイル」の右下▼をクリック。
ピボットテーブルスタイルを展開
展開したリストのサムネイル画像をポイントすると、適用後のスタイルを確認できます。
ピボットテーブルのスタイル一覧
選んだスタイルは後からでも変えられますし、カスタマイズも可能です。
ピボットテーブルのスタイルの適用例

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

元表を修正しても、即ピボットテーブルに反映されることはありません。ピボットテーブル側で「更新」しないかぎり、作成時のままです。

スポンサーリンク

集計元に何らかの修正を加えたら、ピボットテーブルのあるシートに切り替え、「ピボットテーブル ツール」ー「分析」の「データ」グループで、「更新▼」という文字の上にある画像ボタンをクリック。
ピボットテーブルツールの分析にある更新ボタンをクリック
元表の変更箇所がピボットテーブルに反映されるはずです。

但し、この操作で正常に更新できるのは「修正」した場合で、データを新規に追加した場合は少し面倒です。

テーブル化していない表にデータ追加したら範囲を変更

もし、元表をテーブルに変換してあれば、レコードの追加や削除も上記の「更新」操作だけでいけます。日常的にデータが追加される表は、テーブルに変換して作業することを強くお勧めします。 ▶データ表にはテーブルを使うべき!その理由と設定方法

テーブル化していない元表に幾つかのレコードを追加してみましょう。
テーブル化していない表のレコードを追加
ピボットテーブル側で「更新」ボタンを押しても、追加データはプラスされません。変更した範囲を指定し直す必要があるのです。
この場合は、「更新」の右隣にある「データソースの変更」ボタンをクリックします。
データソースの変更をクリック
ピボットテーブルの移動」ダイアログボックスが元表のあるシートで開くので、新たに追加した範囲を含めて「テーブル/範囲」を指定し直します。
ピボットテーブルの移動ダイアログで新規の範囲を含めて指定
ピボットテーブルに新規追加された21日分のデータが反映され、総計にも加算されました。
新規の追加データ範囲がピボットテーブルに反映された

ピボットテーブル・その他のエントリー


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。