オフィスに眠るExcelデータから新発見？ クロス集計を活用する：業務をラクにする！ SMBのExcel活用術【第3回】

今回は「Excelデータの分析」をテーマに、社内に蓄積されたExcelデータの使いこなしを中心に紹介したい。ピボットテーブルの活用がカギだ。Excelデータから何か新しい発見ができるかもしれない。

[井上猛雄]

Excelのピボットテーブルで手軽にクロス集計・分析を

　大量のOffice Excel（以下、Excel）データが企業内で蓄積されたまま眠っていないだろうか？　これらのデータをそのままにしておくのは、データ活用の観点から大変もったいないことだといえる。というのも、Excelデータをさまざまな角度から分析すれば、これまで見えなかった新しい傾向などを把握できる可能性があるからだ。

　本来Excelには、データ分析として用意された多数の機能やツールがある。例えば「集計」「検索」「統計」などの関数を組み合わせることで、異なる角度からいろいろな分析が可能だ。例えば複数の質問項目をクロスして表を作成することで相互関係を明らかにする「クロス集計」と呼ばれる手法があるが、実際に関数を組み合わせてクロス集計を行うとかなり手間が掛かる。しかし、Excelには「ピボットテーブル」というクロス集計に特化した強力な機能がある。「ピボットテーブルレポート」を作成すれば、誰でも簡単に分析が行えるのだ。

　ピボットテーブルレポートは、Excelデータの行／列を回転させて、異なる側面からデータを見ることができる動的なテーブルだ。このリポートを作成し、データの傾向を棒グラフで視覚的に表示することも可能だ。例えば画面1のような1000行にわたる受注（売り上げ）データがあるとしよう。このデータには国内の営業担当に関するデータと各担当の日次の受注総額が記録されている。一見バラバラに見えるデータだが、Excelのピボットテーブル機能によってマウスを数回クリックするだけで、受注最多の営業所や受注の多い時期、売れ筋の人気商品、売り上げ成績の良い担当者など、知りたい情報を即座に読み取れるというわけだ。

画面1　国内の営業担当の受注データ。各営業担当者の日々の受注総額が合計1000データほどワークシートに記載されている。このデータを基にピボットテーブルレポートを作成する《クリックで拡大》

連載Index

【第1回】「脱・Excel」から脱するためのExcel活用

【第2回】ExcelデータのWeb化で情報共有をスムーズに

【第3回】オフィスに眠るExcelデータから新発見？ クロス集計を活用する

【第4回】Excel＋OLAPでスタートする「身近なBI」

ピボットテーブルレポートを作成しよう

　ここからは最新のExcel 2007を利用し、ピボットテーブルでクロス集計を行う方法について具体的に説明しよう。例として、前述のような受注（売り上げ）データからピボットテーブルレポートを作成し、各営業担当の年間受注合計を四半期ごとに表示させてみる。さらに、このリポートで会社の総受注に対する営業担当者の貢献度を確認し、その貢献度に応じて各担当者に対するボーナス支給額を決定する手順も紹介したい。

　最初にピボットテーブルレポートを作成する準備を行う。リポートで使用するデータを選択し、［挿入］タブの［テーブル］メニューで［ピボットテーブル］を選択。プルダウンメニューでもう一度［ピボットテーブル］を選択すると［ピボットテーブルの作成］ダイアログボックスが開く（画面2）。［テーブル/範囲］ボックスには選択したデータ範囲が表示されるので、［OK］ボタンを押す。すると新しいワークシートが表示され、そのシートの左側にピボットテーブルレポートの配置先となる「レイアウトエリア」が、右側に「ピボットテーブルのフィールドリスト」が表示される（画面3）。

（画面2＝左）［テーブル/範囲］ボックスに、選択したデータ範囲が表示される。リポートの配置先としては［新規ワークシート］が選択される。もし新しいワークシートにリポートを配置したくない場合は、［既存のワークシート］を選んでワークシートを指定すればよい。（画面3＝右）［ピボットテーブルのフィールドリスト］には、基となるExcelデータの列のタイトルが使用される。ここでは「営業所名」「営業担当者」「受注総額」「受注日」「受注ID」がフィールド名に変換される《クリックで拡大》

　そこでピボットテーブルレポートを作成するために、専用レイアウトエリアに任意のフィールドを移動する。具体的には「フィールドリストのフィールド名の左横にあるチェックボックスをオンにする」あるいは「フィールド名をマウスの右ボタンでクリックし、そのフィールド移動先の場所を選択」すればよい。ここでは、まず各営業担当の受注（売り上げ）のピボットテーブルレポートを作成しよう。必要なフィールドとして「営業担当者」と「受注総額」のフィールド名のチェックボックスにチェックを入れると、画面4のように既定のレイアウトエリアに営業担当ごとの受注総額が表示される。受注総額はSUM関数を使用して合計したものだ。

画面4　各営業担当の受注（売り上げ）のピボットテーブルリポートを作成。フィールド名にチェックを入れると、各担当の受注総計が表示される《クリックで拡大》

関連ホワイトペーパー

Excel | Office | BI（ビジネスインテリジェンス）