Excelでマスター「ビジネスデータ分析」実践の極意 第5回

Excelグラフは魔物？　それとも分析のミカタ!?

「グラフ分析は異常値分析」操作解説
順位比較で異常値を見つける

順位比較グラフの作成

　異常値分析のひとつに、順位比較グラフを利用する分析方法があります。たとえば、売上高順グラフに粗利額を表示させて比較し、売上高順と異なる傾向を示すモノを、異常値として分析対象にします。

操作事例

　得意先別の売上額と粗利額を比較して、顕著な高粗利額や低粗利額の得意先を、異常値として分析対象に選びます。

　ここでは、ある担当者の得意先の売上額と粗利額を比較し、その後、粗利率を加えて異常値を分析します。

操作1――
得意先別売上額順粗利額表の作成

　サンプルとして「得意先売上粗利データ」のExcelファイルを開きます。ピボットテーブルを起動し、［担当者名］をページ欄へ、［得意先名］を行欄へ、［売上額］と［粗利額］をデータ欄へ、それぞれドラッグします（関連記事）。［担当者名］から「松枝実雄」を条件指定します。

分析元となるデータ

ピボットテーブルで分析の準備を進める

操作2――
データ欄の整理

　B3にある［データ］ボタンを、ここではC3の列欄へドラッグします。

［データ］ボタンをB3からC3の列に移動する

操作3――
売上額順に並べ替えてグラフ作成

　［売上額］（B列）の任意の数値をクリックし、［降順に並べ替え］ボタンをクリックします。

　［グラフウィザード］ボタンをクリックし、積み上げ縦棒グラフが表示されたら、「集合縦棒グラフ」に変更します（（関連記事））。

データを降順に並べ替え、集合縦棒グラフに変更する

解説――異常値を見つける

　売上額順に並んだグラフで、粗利額が前の得意先より多いものがあります。「スーパー夢」です。

売上順に並べたのに、粗利額が前の店より多い、という異常値を発見

　これだけでは、「スーパー夢」が粗利率が高いのか、前の「スーパーレッズ」が低いのか判断できません。

　そこで粗利率を加えたグラフを作成し、異常値分析を行います。

操作4――
粗利率の追加と新規グラフの作成

　ピボットテーブルのシートに表示を切り替えて、［フィールドリスト］から［粗利率］をデータ欄へドラッグします。次に［グラフウィザード］ボタンをクリックします。

粗利率を追加して新規グラフを作成する

操作5――
粗利率表示のため2軸グラフに変更

　グラフが作成されたら、再び［グラフウィザード］ボタンをクリックし、表示された［グラフウィザード 1/4］画面で、［ユーザー設定］タブをクリックします。

　［グラフの種類］から「2軸の折れ線と縦棒」を選択して、［完了］ボタンをクリックします。

グラフウィザードで［2軸の折れ線と縦棒］グラフに変更

解説――
異常値を分析する

　このグラフを見ると、異常値は「スーパー夢」ではなく、「スーパーレッズ」の低粗利率であることがわかります。また、「六甲山」が高粗利率の異常値と判明しました。

　このように、順位比較や率比較によって異常値を発見し、その異常値に絞った分析を続けて行ないます。

異常値の理由が、当該店舗ではなく、その直前の店舗の方にあることが判明。同時に別の異常値にも気づくことができた

■より詳しくなりたい人にオススメBOOK（Amazon.co.jp）

• Excelでマスターする ビジネスデータ分析 実践の極意

■関連サイト

• 「Excelでマスターする ビジネスデータ分析 実践の極意」公式サイト
• 特別ダウンロードサイト（Excel極意のサイト）