Windows TIPS

［Office Master］

→ Windows TIPS TOPへ → Windows TIPS全リストへ → 内容別分類一覧へ ピボットテーブルでクロス集計を行う → 解説をスキップして操作方法を読む 山田 祥寛 2003/11/22 　 対象OS Microsoft Excel 2000 Microsoft Excel 2002 Microsoft Excel 2003

■ 大量のデータを軸や視点を変えて分析したいという場合、「ピボットテーブル・レポート」機能を利用すると便利である。 ■ 「ピボットテーブル」は単純な明細データなどを簡単にクロス集計することのできる対話型のテーブルであり、ユーザーの用途に応じたさまざまな角度からのデータ分析を可能にする。

　

解説

　例えばWebサイトのアクセス・ログなどを想定してみよう。

　コンテンツのカテゴリ、Webページのタイトル、ユーザー・エージェント名、アクセス年月日、カウント数など、ログにはさまざまな項目が記録されている。ただ、実際にはそのベタの明細データを参照しただけでは、その背景にあるユーザーのアクセス動向などの意味を読み取ることは難しい。

　カテゴリごとにアクセス件数の月別推移を見るだけではなく、カテゴリをページ単位にまでブレークして調査したり、さらには、ユーザが使用しているOS（ブラウザ）の単位ごとに日別推移を見たいといった要求もあるだろう。

　このように明細データに意味を与え、ユーザーの関心に応じてさまざまな観点からのデータ分析を可能にする方法として、Excelの「ピボットテーブル・レポート」の機能がある。「ピボットテーブル・レポート」は対話（インタラクティブ）型のテーブルであり、必要な項目をドラッグ＆ドロップするだけで、簡単に行と列を入れ替えたり、集計・表示項目を変更したりといったことが可能となる。

　それではさっそく、設定までの流れを見てみることにしよう。

操作方法

手順1―分析の元となるリストを用意する

　リストの内容は特に問わないが、ここではサンプルとして、Webサイトのアクセス・ログを取り上げてみる。リストは次の画面のように「ページタイトル」「対象年月」「アクセス数」から構成されているものとする。

用意したサンプルのアクセス・ログ

あるWebサイトへのアクセス・ログをサンプルとして用意した。アクセスされたWebページのタイトルとアクセス年月、その月のアクセス数のデータが記録されている。これをピボットテーブルの機能を使って、さまざまな視点からのデータ分析を行ってみる。

• サンプル・ファイルのダウンロード
  （注：今回のサンプル・ファイルpivot.xlsをダウンロードするには、上のリンクを右クリックして、pivot.xlsというファイル名で保存してください）

手順2―ピボットテーブル・ウィザードを起動する

　リスト全体（サンプルでは「B2:D53」）を選択状態にした上で、メニュー・バーから［データ］−［ピボットテーブルとピボットグラフ レポート］を選択する。

ピボットテーブル・ウィザードの起動

ピボットテーブル・ウィザードを起動して、データソースと作成するレポートの種類を選択する。

元となるデータソース。単一のワークシートのほか、Accessなどの外部データソースや複数のワークシートを選択可能であるが、ここでは［Excelのリスト/データベース］を選択する。 　 作成するレポートの種類。単純なテーブル（表）を作成するか、テーブルに基づいてグラフも生成するかを選択できるが、ここでは単純な［ピボットテーブル］を選択する。

　次にデータの抽出元となるワークシートのデータ範囲を選択する。ここでは、すでにデフォルトで先ほど選択したテーブルが選択されているはずなので、そのまま［次へ］をクリックすればよい。

データ範囲の指定

ピボットテーブルに使用するデータの範囲を指定する。同一のExcelブックだけでなく、外部のExcelファイルを参照することもできる。

使用するデータの範囲。これはウィザード起動時に選択されていた範囲。 　 あらためてデータ範囲を特定したい場合にはテキスト・ボックス右端のアイコンをクリックする。 　 ほかのExcelブックを指定したい場合には、［参照］をクリックする。

　次はピボットテーブルの作成先を指定する。

ピボットテーブルの作成先の指定

ピボットテーブルを作成する先のシートもしくは配置場所を指定する。

いまあるワークシートとは別に新規のシートを生成する場合には、［新規ワークシート］を選択する。 　 既存のワークシート上に生成したい場合には［既存ワークシート］を選択してから、作成セル位置を選択する。

手順3―ピボットテーブルに項目を割り当てる

　ウィザードが完了すると、空のピボットテーブルと［ピボットテーブル］ツール・バーが表示されたはずだ。

作成されたピボットテーブル

ウィザードを終了すると、このような空のピボットテーブルが作成される。ピボットテーブルを選択状態にすると、周りに青い枠線が表示され、さらに「ここに〜をドラッグします」という文字列が全部で4カ所表示される。これらのいずれかに、以下のピボットテーブル・ツール・バーから項目をドラッグしてドロップすると、さまざまな視点でデータを分析することができる。

この部分のセルを選択すると、ピボットテーブルがアクティブになり、「ここに〜をドラッグします」という文字列が表示される。 　 これらのフィールドのいずれかに、以下のツール・バーからフィールド名をドラッグ＆ドロップする。1度ドロップした項目を削除するには、項目を選択して右クリックし、ポップアップ・メニューから［表示しない］を選ぶか、ピボットテーブルの青枠の外（右側か下側）までドラッグすればよい。

　ピボットテーブルを選択すると、以下のようなピボットテーブルのツール・バーがアクティブになり、表示される。

ピボットテーブルのツール・バー

ピボットテーブルで使用できるフィールド名。ここでは「ページタイトル」「対象年月」「アクセス数」の3つがある。これらのフィールド名は、元のデータ・シート上にあったフィールド名である。

　それぞれ行・列に割り当てるフィールドを、ツール・バーからドラッグ＆ドロップで割り当ててみよう。ここでは次の画面のように、行に「ページタイトル」を、列に「対象年月」を、データ・アイテムに「アクセス数」を配置する。具体的には、ピボットテーブル・ツール・バーから、［ページタイトル］というフィールド名をドラッグして、ピボットテーブル上の［ここに行のフィールドをドラッグします］の上でドロップする。同様に［対象年月］というフィールド名をドラッグして、［ここに列のフィールドをドラッグします］の上でドロップする。そして［アクセス数］というフィールドを［ここにデータ アイテムをドラッグします］までドラッグして、ドロップする。

完成したピボットテーブルの例

これはページタイトルごとの月別アクセス数を集計したもの。行には「ページタイトル」、列には「対象年月」のそれぞれの項目が表示されている。例えば左上に表示されている312という数値は、『「WINGS News」登録・解除』というWebページの「2003年6月」における参照数が「312回」であったという意味。

「データアイテム」として選択した項目。ここでは、タイトルごとに、月別のアクセス数を集計している。ほかの場所（もしくはの場所）へドラッグして、項目を入れ替えることも可能。 　 行として選択した項目。ほかの場所へドラッグして、項目を入れ替えることも可能。 　 列として選択した項目。ほかの場所へドラッグして、項目を入れ替えることも可能。 　 以上のもしくは、などの項目をドラッグして、ピボットテーブルの外側（右側もしくは下側）へドロップすると、その項目を削除することができる（ポップアップ・メニューから「表示しない」を実行してもよい）。

　これでページタイトルごとの月別アクセス数の集計ができたはずだ。

　あとは、項目部分を入れ替えることで、さまざまな分析が可能である。例えば現在は列に割り当てられている「対象年月」を、ヘッダ部分の「ページ（ピボットテーブルの最上部のフィールド）」にドラッグ＆ドロップしてみよう。すると、次のような対象月総計の表ができる（この場合、「列のフィールド」は空の状態になっている）。

月別のアクセス総計表の例

先の画面において、［対象年月］というフィールドを一番上の［ここにページのフィールドをドラッグします］までドラッグして、ドロップする。すると、各月別ではなく、すべての月（3カ月分）の総計が表示される。ただしドロップ・ダウン・リストから月を選択することにより、特定の月のデータだけを取り出すこともできる。

これをクリックすると、のドロップ・ダウン・リストに表示させたい月を選択することができる 　 全期間ではなく、特定の単月のデータだけを表示させたい場合はこれをクリックして選択する。 　 これをクリックすると、「合計」ではなく、平均値や最大値、最小値など、さまざまな統計値を選択することができる。 　 これをクリックすると、表示させたいタイトルを絞り込むことができる。

　「対象年月」がデフォルトでは「（すべて）」となっているが、この部分を「2003/08」とすることで単月のデータを表示することができる（同様に、「ページタイトル」部分をクリックすることで、表示させたい項目を絞り込むことも可能だ）。

　また、表左上の「合計：アクセス数」と書かれた欄をダブルクリックして、集計方法を変更することもできるだろう。デフォルトでは「合計値」が表示されているが、データ個数や平均値、最大値、最小値など、主要な集計オプションを選択することができる。

　そのほか、「ピボット テーブル」ツール・バーからは、関数を用いた集計方法のカスタマイズや集計項目の絞り込みなども可能になっている。今回は扱っていないが、ピボットグラフなども重宝する機能である。また「TIPS―Excelと外部データベースとを連携させる」を併用して、データベース・サーバ上の業務データを直接参照することで、エンド・ユーザに対して簡易なEUC（End User Computing）のしくみを提供することもできる。いずれもシンプルな操作で容易にさまざまなレポートを作成可能であるので、ぜひ自分で動かしてみて、その効果を実感してほしい。

「Windows TIPS」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）