2013年

2月

19日

データ分析の勧め ~Excelを使って自社の業務データを整理・分析してみよう!~ その3 (p2)

 

では始めます。

 

サンプルデータをダウンロードできますので、実際に操作しながら確認してみてください。

 

①シートのデータ表示されているところにカーソルを置いて下さい。前回も述べましたが、データの無いところにカーソルを置くと操作できませんので注意して下さい。

 

②次に挿入タブの「ピボットテーブル」ボタンをクリックします。

 

③対象となるデータ範囲と、作成するピボットテーブルのワークシートを指定する画面が出ますので新規または指定のシートを選定してOKボタンをクリックします。

 

④ピボットテーブル作成シートのフィールドセクションから「商品名」を行ラベルに、「売上金額」をΣ値にドラッグ&ドロップします。さらにもう一度「売上金額」をΣ値に、続いて「粗利益」をドラッグ&ドロップします。列ラベルは今回使いません。

図3-5 (クリックで拡大)
図3-5 (クリックで拡大)

⑤ 「合計/売上金額」、「合計/売上金額2」、「合計/粗利益」と表示され、「売上金額」の合計が2列できます。このうちの「合計/売上金額2」の計算方法を合計ではなく、累計の比率と設定して、「累計比」という計算表示に変更すると図3-3のABC集計表の原形の表が出来ます。 

 

では計算方法を変更します。まずピボットテーブルツールのオプションタブを選び、アクティブなフィールドのフィールド設定ボタンを押してフィールド設定ウィザードを表示します。計算の種類タブを選び、「比率の累計」を選択します。ついでに名前も「合計/売上金額2」から「累計比」とでも直しましょう。

図3-6 「値」の計算方法や項目名表示の変更 (クリックで拡大)
図3-6 「値」の計算方法や項目名表示の変更 (クリックで拡大)

⑥ABC分析は金額の大きい方から並べて分析するので、カーソルを「合計/売上金額」の数値項目に置き、ピボットテーブルツールの並べ替えウィザードの降順を選択して売上金額の大きい順に並べ直し表を整えます。

図3-7 集計データを売上金額の大きい順に並べなおす (クリックで拡大)
図3-7 集計データを売上金額の大きい順に並べなおす (クリックで拡大)

いかがですか。ピボットテーブルはクロス集計(行と列)の形になっていないとダメだ、と思われている方も多いようですが、このように膨大な明細データを瞬時に集計する機能も持っています(ちなみにサンプルデータは2442明細行)。

 

このピボットテーブルを使って並べ直したりグラフを作成することもできますが、ちょっと体裁を整えたり、グラフを見やすく作成するときにはピボットテーブルの枠が邪魔をしてもう一つ見やすいものになりません。そこでこの集計データを別のシートに移してもう少し表を整えてみます。ピボットテーブルツールの選択ボタン、ピボットテーブル全体をクリックしてこれをコピーし、新しいシートに貼り付けます。

図3-8 (クリックで拡大)
図3-8 (クリックで拡大)

このとき貼り付けボタンの「値」のみ貼り付ける、と選択すると余計な形式や計算式が付いてこないので整えやすくなります。あとは自由に、例えば図3-3のように商品個々の利益率を計算をしたり、この図では累積比の50%未満をAランク、80%未満をBランク、それ以下をCランクなどと設定していますが、皆様の分析基準でランク分けなどすると見やすくなります。

 

次回は図3-4に示しましたグラフの作り方を解説します。


NICO 情報戦略チーム 星野

 

 

  <<  前ページ     1     2  

サンプルデータ
説明に使用したサンプルデータです。ダウンロードして操作をお試しください。
サンプルデータ.xlsx
Microsoft Excelシート 185.7 KB

「いいね!」ボタンを押していただくと、最新情報をお届けできます。

 フィードの購読もできます。

 (NEWS,BLOGのみ)

連載ブログ 

 大人気3rdシリーズ

 

公益財団法人にいがた産業創造機構

産業創造グループ

情報戦略チーム

TEL 025-246-0069

Mail  kns@nico.or.jp