今回は、 パレート図 という統計処理によく使う ヒストグラム と 折れ線グラフ を組み合わせた図の作り方を紹介します。標準で準備されている パレート図 では、本来の パレート図 のメリットが１００％発揮できませんので、使える パレート図 をちょっとした工夫で作成する方法を解説します。
皆さんは パレートの法則 というのをご存じでしょうか。
エクセルのグラフの種類にも標準で装備されていますよね。でも、本来のパレート図と微妙にというかかなり違いますね。
- 折れ線グラフは原点から始まって欲しい。
- 折れ線グラフは棒グラフの右肩を通って欲しい。
- 折れ線グラフの終点が100％でグラフエリアの右肩と一致して欲しい。
など昔先輩にこんこんと教えられた事を実現したいと思いました。
これらの パレート図 の約束事は、それぞれ意味があるので、ここを訪れた方も、同じ思いだと強く信じています。是非、ここでマスターして、完璧な パレート図 をエクセルで実現してください。
それではいってみましょう。
経済において、全体の数値の大部分は、全体を構成するうちの一部の要素が生み出しているとした。80:20の法則、ばらつきの法則とも呼ばれる。
ウィキペディアより引用
全体の大半は一部の要素によって構成されるということで、その分析を行うために作成するグラフに パレート図 というものがあります。
今回は、その パレート図 をExcelで作成する方法です。
集計した表を数値により降順に並べ替え、その項目が全体に占める割合の累積比率の列を表に付加します。そしてその表から棒グラフと折れ線グラフを組み合わせた図を作るのです。
ポイントは次の点です
- データを降順に並べ替える処理を自動で出来るようにする
- 累積比率の列を作成する
- パレート図 の累積比率の折れ線グラフは0を起点に描く
- Y軸の最大値は第1軸は数値の合計値、第2軸は100％
データを入力すると自動で降順に並べ替える
販売金額での順位をB列に作成します。
順位を求める関数は RANK.EQ 関数を使います。
B3の列に以下のように入力して[ENTER] を押すとB3~B7へ順位が表示されます
=RANK.EQ(D3:D7,D3:D7)
順位がスピルで作成されました。（スピルについてはこちらの記事を参照）
次に右側に順位で並べ替えた表を作成します。
まず、順位を1～5と並べて入力した表を作成します
順位を検索キーとして、順位に該当する商品名と販売金額を VLOOKUP 関数で抜き出します。
G3のセルに VLOOKUP 関数を以下のようにスピル書式を使って入力します。
=VLOOKUP(F3#,B3:C7,2,FALSE)
同様に販売金額の列もH3に以下のように入力します
=VLOOKUP(F3#,B3:D7,3,FALSE)
これで元の表を順位で並べ替えた表が作成できました。
販売金額が更新される度に販売金額を入力すれば、自動で降順で並べ替えた表が表示されますので、数値が変わる度に並べ替えの操作をする必要がなくなります。
累積比率の列を追加する
累積比率とは、各項目の全体比率を求め、1位から該当順位までの全体比率を合計します。
別の見方をすると、1位から該当順位までの販売金額の合計を全体の合計で除算して全体比率を求めます。
H3の累積比率はH3を全体合計で除算。
H4の累積比率はH3:H4の合計を全体合計で除算。
H5の累積比率はH3:H5の合計を全体合計で除算。というふうにすれば良いので、
Excelでは合計範囲開始のH3を$H$3として絶対参照で固定し、合計範囲終了のH3を相対参照のままとして記述することで、セルの行が進む毎にH3,H4,H5と自動で計算式が更新していきますから、以下のように入力して下にコピペすると簡単に式の入力ができます。
=SUM($H$3:H3)/SUM($H$3#)
そして、次の パレート図 作成のために開始の0％を追加しておきます。
I3~I7を選択した状態で、マウスで選択枠を掴んで一行下へずらします。そして空になったI3のセルに0を入力しておきます。
パレート図 のグラフを作成する
以上でグラフ描画用のデータが作成できましたので、次はいよいよグラフを作成していきます。
H3:I8の範囲を選択して、挿入メニューのグラフのうち組み合わせグラフを選びます。
3つ出てくる真ん中の折線が2軸を使ったものを選んでください。
次のようなグラフが作成されたでしょうか。
上部のメニューにはグラフのデザインが表示されていますので、一番左のグラフ要素の追加をクリックし「軸(X)」、「第2横軸(Z)」の順で選び第2横軸を表示させてください。
第2横軸を選択して右クリックメニューから「軸の書式設定(F)」を選び書式設定ダイアログを開きます。ダイアログ内の軸の軸位置からメモリ(K)を選択してください。これは、棒グラフを原点Oから始めるための設定です。設定すると棒グラフの始点が原点Oへ移動します。
これで第2横軸の表示は邪魔ですから、同じダイアログから「ラベル」の「ラベルの位置(L)」で「なし」を設定し第2横軸のラベルを消しておきます。
次に、第一縦軸の軸の書式設定で、最大値を販売金額合計の220に、最小値を0に設定します。
同様に第二軸は最小値を0、最大値を1、更に表示書式をパーセンテージに設定します。
次に棒グラフの幅を太くするために、棒グラフを選択して「データ系列の書式設定」から「要素の間隔」を0に設定します。
更に、6は無駄な空の棒グラフですから範囲を狭めてなくしておきます。
ここまで来ればほぼ完成です。
あとは好みで調整してください。
例として以下の様に設定します。設定をまとめて載せておきます。
- 棒グラフの枠を白に設定
- 棒グラフにラベルを設定
- 折れ線グラフの種類をマーカー付きに設定
- 折れ線グラフの二番目のマーカーにラベルを表示
- 系列名の設定と凡例の位置を上に設定
- 第一横軸を商品名に設定
- 第2縦軸の表示を0.2間隔に変更
- グラフの横線（目盛り線）は第2横軸にする
- グラフのタイトルを「パレート図」に修正
以上を設定すると以下の様になります。
標準のものではちょっと足りませんので、これを使うようにしてください。
それでは、今回はここまでです。
有り難うございました。
コメント