ブロガーの実験室スポンサー広告
> オフィスに眠るExcelデータから新発見? クロス集計を活用する> ブロガーの実験室パソコンな日々
> オフィスに眠るExcelデータから新発見? クロス集計を活用する
上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。


--.--.--|スポンサー広告||TOP↑

refer http://techtarget.itmedia.co.jp/tt/news/0910/16/news01.html

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



2009.10.19|パソコンな日々コメント(0)TOP↑
名前:
コメントタイトル:
メールアドレス:
URL:
コメント:

パスワード:
管理人だけに表示:
管理者にだけ表示を許可
カテゴリー
最近の記事
最近のコメント
最近のトラックバック
ブログ検索
管理人のブログ一覧
管理人へメール

名前:
メール:
件名:
本文:

月別アーカイブ
プロフィール

やまもも実験室

Author:やまもも実験室
FC2ブログへようこそ!

RSSフィード
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。