Excel：ピボットテーブルでパレート分析（１）

パレート分析をExcelのピボットテーブルでやってみた覚書です。

 

 

 

パレート分析とは？

パレート分析というのは、売上や在庫の分析をするのによく用いられます。

例えばある商品の売上が、総売上額に対してどの程度締めているのかなどを分析することができます。

また、「20-80の法則」という法則があり、

• 総売上の 80% は売上に含まれている商品群の 20% で構成される。
• 全顧客の 20% が、全体の売上の 80% を占めている。
• 総仕入の 80% が、20% の仕入先に依存している。
• 売上の8割は、上位2割の営業マンによって構成されている。

などのように、より重点を置く項目と、集中すべきリソースを分析するために頻繁に使用されます。

よく Excel でパレート分析を行ってパレート図をグラフとして出力する方法が紹介されますが、ここではピボットテーブルを使ってパレート分析する方法を紹介します。

 

ピボットテーブルとは？

売上明細といったデータや、仕入、在庫の明細などレコード（行）とフィールド（列）で構成されている表（テーブル）を、様々な方法で集計するためのExcelの機能です。

簡単な例を以下に示します。

上図の向かって左側が、表（テーブル）で、右側がその集計結果、ピボットテーブルとなります。

 

データを用意する

下に示すのは、とある街の売上一覧です。ちょっとリアルな数字に見えますが、実際の数字をちょっと加工して、商品名をどこかのRPG風に変えただけのものです（"店" っていフィールドは、サンプル作成時に変更し忘れているだけです。"ID" と読み替えてください）。

ググって出てくる例では、もっとシンプルなデータを使用すると思いますが、なるべくリアルな方が面白いでしょう？

このデータを、「売上データ」というワークシートに作成します。

今回は、このデータをピボットテーブルの元になる表として使用します。

 

ピボットテーブルを作成する

売上データを作成したら、さっそくピボットテーブルを作成します。

まず、シート上のカーソルを、A1 に合わせて¹、リボンの[挿入]タブの[ピボットテーブル]をクリック。

すると、「ピボットテーブルの作成」というダイアログが現れます。

ここでの注意点は、[テーブルまたは範囲を選択] で表示されている範囲が正しく表全体を含んでいるかどうか確認してください。

上記で提示したテーブルを使用する限りは、正しく範囲を認識してくれているはずです。

しかしながら、実際実務で作業する場合にはテーブルが表示範囲を超えていることのほうが大半だと思うので、範囲を選択した上で、[ピボットテーブル] をクリックするなど、状況によって判断する必要があります。

正しく範囲が選択されているのを確認したら、[OK] ボタンをクリックします。

そうすると、新しくシートが作成されて、ピボットテーブルの作成準備が整い、以下のような画面となります。

 

フィールドの落とし込みと設定

画面の向かって右側に、「ピボットテーブルのフィールド」というサイドパネルが開き、上に先程のもととなったテーブルのフィールド名（列名）が。下に「フィルター」、「行」、「列」、「値」の各ボックスがあります。

このボックスに、各フィールドをドラッグして落とし込んでいくことで、ピボットテーブルを作成します。

まず、「商品名」フィールドを、「行」のボックスにドラッグアンドドロップします。

そうすると、下の画面のようにピボットテーブルの行の見出し部分に、商品名の一覧が表示されると思います。

次に同じようにして、「販売金額計」フィールドを、「値」ボックスにドラッグアンドドロップします。

更に同様に、合わせて合計３回、「販売金額計」フィールドを、「値」ボックスにドラッグアンドドロップします。

ちょうど次の画面のようになります。

次に、値に落とし込んでいるフィールドの見出しや計算方法を設定していきます。

まず、向かって一番左側の「合計／販売金額計」の見出しを右クリックして、[値フィールドの設定] をクリックします。

名前の指定を、「売上金額計」として、[表示形式] ボタンをクリックします。

表示形式は、数値 を選択し、[桁区切り（,）を使用する] にチェックを入れます。負の数の表示形式は、デフォルトのままにし、[OK] ボタンをクリック。

先程、「合計／販売金額計」だった見出しが「売上金額計」に変わり、数字も見やすくなったと思います。

この列の任意の数字を右クリックして、[並べ替え] → [降順] とします。ここが重要な部分になります。

この時点で、ピボットテーブルは次のようになっています。

以降、順に値フィールドの設定をいじっていきます。

まず、「合計／販売金額計2」の見出しを右クリックして、[値フィールドの設定] をクリックします。

名前の設定を構成比として、[計算の種類] タブを選択します。

計算の種類 を、[列集計に対する比率] として、[OK] をクリック。

さらに、「合計／販売金額計3」の見出しを右クリックして、[値フィールドの設定] をクリックします。

名前を、比率の集計として、計算の種類を[比率の累計] として、[OK] をクリック。

これで、以下のようになります。

この状況で、ほぼパレート分析のピボットテーブルに関しては完成したと言ってもよい状態になっています。

 

ピボットグラフでパレート図の作成

先のピボットテーブルの作成で、パレート分析そのものはほぼ出来上がっていますが、ここでグラフを付けたりするだけで見栄えがグッと変わるので、もし上司にいいところ見せたいならば、グラフをおつけすることをお勧めします。

ピボットテーブルを作成したシートが選択されている状態で、リボンのピボットテーブルツール → 分析 → ピボットグラフ をクリック。

グラフの見た目は、たて棒 → 集合縦棒 として、[OK] をクリック。

これで、次のような棒グラフが作成されます。

この棒グラフの、棒の部分をクリックで選択し、右クリックして[系列グラフの種類を変更] をクリックします。

ここで、「比率の累計」の部分を、集合縦棒から、マーカー入りの折れ線グラフに変更します。

同じく、「比率の累計」の、[第 2 軸] の部分にチェックを入れ、[OK] します。

すると、よく教科書とかで見るようなパレート図のグラフができていると思います。

あとは、グラフのサイズを微調整して、少しでも上司受けが良くなるようにかっこよく仕上げてください。

今回はここまで

今回は、

1. 元となるデータを用意し、
2. それを元にピボットテーブルを作成。
3. また、ピボットテーブルを元に、パレート図をグラフで作成

しました。

次は、パレート分析でよく聞かれる、A ～ C までのランク分けと、それに基づいたピボットテーブルの書式設定、それからピボットテーブルの集計結果を、元となるテーブルに反映し、書式設定で見やすくしてみたいと思います。

関連項目

• Excel：ピボットテーブルでパレート分析（２）
• Excel：ピボットテーブルでパレート分析（３）