TechTargetジャパン
データ分析
BI／BA／OLAP
事例
オフィスに眠るExcelデータから新発見？　クロス集計を活用する

業務をラクにする！　SMBのExcel活用術【第3回】

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

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

公開 2009年10月16日 08時00分

更新 2012年09月04日 17時44分

[井上猛雄]

印刷する

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

ピボットテーブルレポートで多角的にデータ分析する

　今度は国内営業所ごとの担当者の受注額の確認だ。リポートのデータを部分的に絞り込むためには「レポートフィルタ」を使用する。まず「営業所名」フィールドをレポートフィルタとして、ピボットテーブルレポートに追加。［ピボットテーブルのフィールドリスト］の「営業所名」フィールドをマウス右ボタンでクリックし、［レポートフィルタに追加］を選ぶ。そうすると、リポート上部に新しい「営業所名」レポートフィルタが追加される。「営業所名」フィールドの横の矢印には「（すべて）」と表示され、リポートに国内営業所すべてのデータが表示される。ここでは関西営業所のみを選択しよう。すると関西営業所の担当者の受注総額が表示される（画面5）。これで各営業所の成績優秀者が簡単に分かるだろう。

画面5　作成したリポートから、さらに情報を絞り込む。国内営業所ごとの担当者の受注額を確認するために、「営業所名」にレポートフィルタを適用。リポート上部に新しい「営業所名」レポートフィルタが追加されるので、ここで必要な営業所のデータを選択する。画面では関西営業所のデータを表示《クリックで拡大》

　次に、角度を変えて日次や期間による個人売り上げについて分析しよう。まず［ピボットテーブルのフィールドリスト］の「受注日」フィールド横のチェックボックスにチェックを入れて、フィールドをリポートに追加。これで各営業担当者の日付ごとの受注額がリポートに表示される。しかし、これでは一度に表示されるデータが多過ぎるため管理が面倒だ。そこで、このデータをさらに四半期または年単位などにグルーピングすれば管理しやすくなる。日付をグループ化するにはリポート内の日付をクリックし、次に［オプション］タブ－［グループ］－［グループフィールド］を選択する。［グループ化］ダイアログボックスで「四半期」を選び［OK］ボタンを押すと、担当者の売上高データが四半期ごとにグループ化されて表示されるはずだ（画面6）。

　しかし、画面6のように担当者が多い場合、リポート全体を確認するにはまだ不便だ。ページを下へスクロールしてデータを確認しなければならない。そこでリポートのデータを回転移動させてみよう。フィールドの垂直ビューまたは水平ビューが入れ替わり、行が列エリアに、または列が行エリアに移動して見やすくなる。「四半期」の行のいずれかをマウス右ボタンでクリックし、［移動］－［"受注日"を列に移動］を選択すればよい。営業担当の名前がまとめられ、第1四半期の売り上げデータ上に「列ラベル」というセルが表示され、四半期データはリポートの列として配置される。各列の一番下には四半期ごとの総計も示される（画面7）。

（画面6＝左）「受注日」フィールド横にチェックを入れれば、各営業担当者の日付ごとの受注額がリポートに表示される。ここでは、オプション機能によって、さらに四半期または年単位などにグルーピングする。（画面7＝右）画面6のリポートはデータ量が多く見にくいため、リポートのデータを回転移動させる。画面は行を列エリアに、または列を行エリアに移動したところ。これなら一目でデータを見渡せる《クリックで拡大》

　また、これらの表を視覚的なグラフとして表示させると、さらにデータが見やすくなる。［オプション］－［ピボットグラフ］を選択すると「グラフの挿入」ダイアログが表示されるので、そこで「縦棒」「折れ線」「レーダー」など好きな表示形式を選んでピボットグラフを表示させよう（画面8）。

画面8　ピボットグラフを使ってデータを視覚的に表示にすると、より見やすくなるだろう。グラフのレイアウトやスタイルなども簡単に変更可能だ《クリックで拡大》

簡単に変えられる集計方法

　ここまでは各営業担当の受注額（売上高）について、SUM関数を中心に集計しながら分析してきた。次に、別の関数を使って異なる分析をしてみよう。例えば、リポートの「値の領域」で使用されている集計関数を、SUM関数からCOUNT関数に変更すれば、各担当者の年間売上件数の合計を計算できる。これにより誰が一番受注が多かったのかを確認できるようになるわけだ。

　集計関数を変更するには、リポートの「合計/受注総額」という見出し下の値の領域で、任意の場所をマウス右ボタンでクリック。そして［データの集計方法］をポイントし、［データの個数］を選択すると、数値が値の合計から値の数に切り替わる（画面9）。同時に数値の上のタイトルも「データの個数/受注総額」に変わる。その後、いずれかの営業担当者の小計をマウスの右ボタンでクリックして［並べ替え］をポイントし、［降順］を選んで受注量を並べ替えると、最も受注が多い担当者を簡単に確認できる（画面10）。

（画面9＝左）集計関数をSUM関数からCOUNT関数に変更し、各担当者の受注件数の年間合計を計算。（画面10＝右）データが見にくいので、成績の良い順番に並べ替える。ここでは受注数ベスト3は堀見151件、鈴木125件、井上117件となっていることが分かるだろう《クリックで拡大》

　ただし受注件数が多くても、総受注（売上高）に占める割合が必ずしも多いとは限らない。そこで、念のために総受注額に対する各担当者の受注額の比率も確認しておこう。これを確認するには、ユーザー設定の計算を使用する。値の領域をマウスで右クリックし、［データの集計方法］－［その他のオプション］を選ぶ。［値フィールドの設定］ダイアログボックスが開くので、［計算の種類］タブを選択して［計算の種類］－［全体に対する比率］をクリックすればよい（画面11）。

画面11　ユーザー設定の計算によって、総受注額に対する各担当者の受注額の比率を求める。この結果から総受注額に占める割合は、堀見14.96％、鈴木13.62％、井上11.92％となり、受注件数と受注額が比例する傾向にあることを確認できる《クリックで拡大》

条件付けでさらなるデータの可視化も

　最後に、これらのデータからボーナスの支給対象者と、具体的な支給額について計算してみよう。ここでは、ボーナス支給者に関する条件を「各四半期で350万円より多く受注した担当者に、その四半期の受注額の5％をボーナスとして支給する」としよう。

　集計フィールドを使用して数式を作成することで、どの担当者にどれだけボーナスを支給するかを決定することができる。数式の作成は、ウィンドウの上部にあるリボンメニュー上で、［ピボットテーブルツール］の［オプション］タブを押す。［ツール］－［数式］－［集計フィールド］を選択すると、［集計フィールドの挿入］ダイアログボックスが開くので、［名前］ボックスに「ボーナス」と入力する。

　次に［数式］ボックスにボーナス支給対象者を決定する式として「=受注総額IF(受注総額>3500000,5%)」と入力し、［OK］ボタンを押す。この数式が前述の条件を満たすものだ。もし四半期の売上高が350万円以下の場合は、その四半期のボーナス額はゼロになる（画面12*）。

画面12　受注額に応じてボーナスを決定する。集計フィールドを使用し、「各四半期で350万円より多く受注した担当者に、その四半期の受注額の5％をボーナスとして支給する」という条件を適用した《クリックで拡大》

　なお、この画面では各営業担当者の名前の横にある小計についても、5％のボーナスが表示されている。これは、Excelの機能により、リポートの各行に対して数式が実行されるためだ。この値は、必ずしも四半期の合計と一致するものではない。紛らわしいので小計をオフにしておこう。ウィンドウ上部にあるリボンメニューで、［ピボットテーブルツール］の［デザイン］タブを押す。［レイアウト］－［小計］－［小計を表示しない］を選択すれば、四半期ごとのボーナス額だけが表示される。

　このようにExcelを利用して、さまざまな角度からデータを分析したり、さらに条件によってデータを選別し、再計算まで行えるようになることがお分かりいただけただろう。今回はExcel標準搭載のピボットテーブル機能を利用したデータ分析に絞って実例を紹介したが、次回はさらに踏み込んで、他社製のツールを利用したExcelの分析方法について見ていく。具体的には、Excelを利用してマウス操作だけで多次元データ分析が可能な日立情報制御ソリューションズの「PowerOLAP」、エム・アイ・ティーが提供するBI（Business Intelligence）ツール「Panorama NovaView for Microsoft Office」などを紹介する予定だ。

印刷する