2013年

3月

11日

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

では次に件数のカウント方法とクロス集計表の作り方を述べていきます。

 

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


①前に何度も使っているフライパンの売上明細データを、商品名と売上日を行ラベルに入れて売上金額を集計するピボットテーブルを作ります。

 

図6-2 商品名と売上日でデータを集計 (クリックで拡大)
図6-2 商品名と売上日でデータを集計 (クリックで拡大)

 

②ピボットテーブルはそのまま作ると図6-2のように集計ラベルの商品名は明細行の頭に表示されるだけですが、行ラベルを図6-3のようにピボットテーブルツールのデザインタブのレポートレイアウトを押し、「表形式で表示」「アイテムのラベルを全て繰り返す」とすると、商品名-売上日-売上金額、というデータ行で集計できます。この後、小計の「小計を設定しない」を押して、商品名の小計を表示しないで下さい。同じように総計も表示する必要はありません。これはこの集計結果を別シートに「値と元の書式」で貼り付け、再度ピボットテーブルで整理するためで、小計や総計はかえって邪魔になるからです。

 

これで、商品別売上日別の売上金額の集計データが出来上がりました。この売上日をカウントすれば重複を排除してユニークな日のカウントが出来るわけです。既存のデータベース上でdistinct count関数を使うと容易なのですが、Excelなどの表計算ソフトには付いていませんので、このような手間をかけることになります。

図6-3 商品名をすべて繰り返すピボットテーブルと別シートへの貼り付け (クリックで拡大)
図6-3 商品名をすべて繰り返すピボットテーブルと別シートへの貼り付け (クリックで拡大)

 

③データとして貼り付けたシートから再度ピボットテーブル機能を使って図6-4のような集計テーブルを作っていきます。これは行に商品名、数値項目として売上金額は合計ですが売上日はデータの個数で計算されます。この元データは商品と売上日で集計されているデータを使っていますので、日数の個数はユニーク値を集計することになります。このピボットテーブルをコピーして面倒でもまた新しいシートに貼り付けます。

 

続けて、1か月平均の売上日数を算出して頻度の高低の目安とし、図6-1の示したようなクロス集計表(ポジショニングマップ)を作っていきます。新しいシートに貼り付けたら、売上日数の右列に月平均売上日の計算を挿入します。これは数式タブからROUND関数を使うと四捨五入が計算できます。切り捨てはROUNDDOWN、切り上げはROUNDUP。何を使うかは個々の場合で使い分けてください。

 

図6-4 商品別の売上合計と1か月平均売上日数を算出した表 (クリックで拡大)
図6-4 商品別の売上合計と1か月平均売上日数を算出した表 (クリックで拡大)

 

④月平均日数が算出出来たらこの列を列ラベルとして再々度ピボットテーブルを作ります(図6-5)。行は売上金額の大きい順、列は日数の多い順に並べ直して完成です。この表にABC分析でランク付けした商品に赤線を引き、列にも4日、1日に線を入れて、最初に説明したようなAA、AB、・・・・CB、CCと9個に分類し、内容を吟味していきます。

図6-5 行を商品名、列を月売上日数でクロス集計した例 (クリックで拡大)
図6-5 行を商品名、列を月売上日数でクロス集計した例 (クリックで拡大)

 

いかがでしょうか、なんか面倒くさいな、と思われた方も多かったと思います。元データが業務システムから抜き出した明細データなら、今まで説明したような手間をかけて売上日や得意先、仕入先などユニーク項目で集計し直してからデータの個数をカウントしなければならないのですが、業務データ抜き出しの時に、集計機能が付いているものがある場合には、〇〇別〇〇別であらかじめ集計させてからデータを抜き出すことが出来ます(「クエリー」と呼ばれている機能に多く付いています)。実際には本当に明細データを数千件程度ならスムーズに処理できますが、万の桁で処理するとなると手間もかかります。システム担当者と相談してどのような集計データで抜き出すか、あらかじめ決めておくと非常に使い易くなります。   

 

次回から3回に渡って、実際の分析事例を紹介する予定です。この中でも単純に数値項目(金額や個数など)を集計するだけの分析から、何社に売ったとか、何日出荷したか、何か月滞納しているのか、などユニーク項目をカウントして数値化することで「見える化」を実現しているケースが多く見られます。  今回はちょっと解りづらかったかもしれませんが、是非ユニーク項目のカウント、distinct count の概念を覚えて、今後の分析に役立たせてください。


NICO 情報戦略チーム 星野

 

 

  << 前ページ     1     2  

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

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

 (NEWS,BLOGのみ)

連載ブログ 

 大人気3rdシリーズ

 

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

産業創造グループ

情報戦略チーム

TEL 025-246-0069

Mail  kns@nico.or.jp