第2弾はExcelでパレート図を画いてみましょう。（グラフを作ります）
食料品を扱う問屋（卸売業者）の受注データから、2013年の1年間に受注した商品（アイテム）と受注金額（値）でパレート図を画いてみます。
元データはこんなデータです。
受注データは1日毎に受注の有った、得意先や商品名、数量等のデータになっています。
なので、同じ日の中に異なる得意先から同じ商品を受注することもあり、商品は重複したデータになっています。
パレート図に使う元データは重複の無い商品（アイテム）と金額（値）のデータが必要になりますから、この受注データからそのようなデータをまず集計する必要があります。
方法は、いろいろあると思いますが、簡単な方法は“ピボットテーブル”による集計です。その方法は第3弾！“ピボットテーブルでパレート図を画いてみた”で説明しますね。
んで、商品と金額のデータはこのようになります。
例えば、家計簿のデータだったら、1年間か、1ヶ月など期間を区切って日々のデータから買物をした品名等の支出の項目（アイテム）と金額のデータを集計しておく必要があります。
ちなみに、商品（アイテム）の数は119種類あります。
これを、金額の大きい順（降順）に並べ替えておきます。
それから、金額の一番下に合計金額を“SUM関数”を使って求めておきます。2013年の受注金額の合計は\10,561,900でした。
まず最初に“累積金額”を計算していきます。
一番最初の“讃岐めん”の累積金額は“金額”（\740,000）をそのまま持ってきて、2番目の“北海道昆布”から下は一つ上のセルの累積金額に金額を足していきます。
“北海道昆布”の累積金額は“\740,000+\528,000=\1,268,000”になります。
この式を一番下までコピーします。
すると、一番下の累積金額は予め計算しておいた合計金額を同じになります。
次に累積構成比を計算します。
これは、合計金額に対する累積金額の割合（構成比率）のことです。
“累積金額”を“合計金額”で割ります。
セルにはこんな式を入力します。
ここで、“合計金額”のセルを“$C$123”と“$”マークを付けているのは、このセルを一番下までコピーする時に“合計金額”のセルを絶対位置にさせておくために付けます。
一番下まで、コピーして表示形式を“%”にしておきます。
ちなみに、一番下の“ローカロリー牛乳”の累積金額は合計金額と同じですから、累積構成比は100%になります。
さ、これでパレート図（グラフ）を画く元データは完了です。
このデータからだけでも、累積構成比が80%の時のアイテム数がいくつあるかが分かりますから、総アイテム数119個の20%になっているかどうか、つまりパレートの法則（80-20の法則）に則っているかどうかを確認することができますね。
では、グラフを画いていきましょう。
ここからは、グラフ作成の説明ですよ。。
まず、グラフにするデータを範囲選択します。
パレート図はアイテムの金額（棒グラフ）と累積構成比（折れ線グラフ）の2つのデータで、“累積金額”はグラフのデータに不要なので、まず最初にセル“B3〜C122”を範囲選択したあと「Ctrl」キーを押して、次にセル“E3〜E122”を範囲選択します。
すると、選択範囲がグレーになります。
次に、「挿入」、「グラフ」、「縦棒」から「集合縦棒」を選択します。
すると、サクッとグラフができました。
累積構成比は“1”以下の値ですから、この縦軸“\0〜\800,00”では下の方にへばりついてほとんど見えませんね。。。
次に、累積構成比を“第2軸”にして%表示とします。
グラフの中でクリックしておいて「グラフツール」、「書式」、「現在の選択範囲」から「系列“累積構成比”」を選択します。
そして「選択対象の書式設定」を選択します。
「データ系列の書式設定」画面がひらきます。
ここで、「使用する軸」を「第2軸」を選択します。
「閉じる」を押すと“累積構成比”が赤い棒グラフで出現しました！
右側に第2軸が現れれて“0%〜120%”になっています。
これを、折れ線グラフにしなくちゃならないので、赤の棒グラフの上をクリックします。
すると所々に○が表示されて選択されたことが分かります。
その上で右くりし、「系列グラフの種類の変更」を選択します。
「グラフの種類の変更」画面が開きます。
「折れ線」を選択します。
「OK」を選択します。
んで、ついでにグラフの右側に表示されていた“凡例”も消しちゃいましょう。
「グラフツール」、「レイアウト」、「ラベル」、「凡例」から「なし」を選択します。
すると、このようにほぼパレート図ができました。
で、正しいパレート図というのは各アイテムの金額の棒グラフの構成比も読み取れるように金額の縦軸の最大値を合計金額“\10,561,900”に固定して、第2軸は最大値を“100%”に固定するのです。
そうすると、累積構成比の折れ線グラフは“讃岐めん”の棒グラフの頭から伸びる形になります。
ですが、この場合ハッキリ言って一番金額が大きい“讃岐めん”でも構成比は“7%”しかないので、そうしてしまうと棒グラフが低くなりすぎてみにくくなってしまうため、敢えて行いません。
ですが、このままの状態では主軸と第2軸の目盛の位置が合っていないので、それを合わせるために修正しておきます。
まず、主軸の上でクリックすると主軸が四角で囲まれます。
そこで、右くりして「軸の書式設定」を選択します。
「軸の書式設定」画面が開きます。
「軸のオプション」から「最大値」を「固定」にして“100000”と入力します。
次に同じように第2軸も「軸の書式設定」を選択し
「最大値」を“1”（100%）に「固定」します。
するとこのように主軸と第2軸の目盛があったパレート図が完成しました。
これで、ABC分析をしてみると
70%までを“ランクA”、そこから90%までを“ランクB”、その後を“ランクC”にしてみると
こんな感じで、“80-20の法則”にはなってなくて“なだらか形”になっているのが分かります。
ABC分析からすると、受注金額に大きな影響を与える商品はなくリスクは小さいと言えますが、売れ筋商品が無く受注金額を伸ばすための注力すべき商品がハッキリしていない。ということが言えます。。。
サンプルが良くなかったですね。。。反省。。
関連ページ
第1弾!“ABC分析（パレート分析）について”
第2弾!“ピボットテーブルでパレート図を画いてみた”
