Excelでパレート図を作る方法(品質管理用 Excel2010~Excel2016共通のやり方)

スポンサーリンク

Excelでパレート図を作る方法

Excelで品質管理用のパレート図を作る方法を解説します.

左下図の不良項目とその発生件数のデータから右下図のパレート図を作る過程です. やり方はいろいろあるのですが, どのexcelのバージョンでも作成可能でかつ簡単な方法を紹介します.


ここでは品質管理でよくあるパターンとして,
不良の項目とその発生件数の例を使っていきます.

①項目と件数のデータを準備する

元となるデータを準備します.
件数はあらかじめフィルタの降順などで件数の多い順に並び変えます(ここ重要).
並び変えたあとに件数の極端に少ないものはその他として1項目にまとめます.

②件数の合計を求める

件数の合計を求めます.

③データがあるセルの上に1行足す

データが入っているセルの上に行を挿入します.

足した行の割合と累積比率の部分に0を入力します.

④件数の割合を求める

それぞれの項目の全体に占める割合を求めます.

下図のよう各項目の件数を件数の合計で割ります.
(合計のセルをクリックしたときにF4キーを押し絶対参照した状態で, オートフィルを使うと早くできます.)

⑤累積比率を求める

④で求めた割合を足し合わせていくことで累積比率を求めます.
最初の1項目はそのまま, 2項目目は1項目と2項目の割合を足したもの, 3項目目は
1項目と2項目の割合を足したものに3項目の割合を足したもの…といったふうに足し合わせます.
その他の項目まで全て足し合わせると累積比率は1になります.

⑥件数と累積比率で棒グラフを作る

図の赤枠部分のように件数と累積比率(を選択します.
件数を選択するときに不良項目のデータが入っている行の1つ上のセル(0を入れたところ)から選択するのがポイントです.
(離れたところを選択するのにはCtrlキーを押しながらドラッグして選択します.)

選択した状態で, [挿入]タブ→[縦棒/横棒グラフの挿入]のアイコンをクリックします. 2D縦棒の集合縦棒(普通の棒グラフ)をクリックします.

ちなみに上の画面はexcel2016を使っていますが,
excel2010だと下のような画面になります.
アイコンの外観などは異なっていますが基本的に挿入タブから棒グラフを選ぶ手順は同じです.

押すと下のような棒グラフが表示されます.

⑦累積比率のグラフの種類を変更する

パレート図は件数を棒グラフで表し, 累積比率を折れ線グラフで表すグラフです.

今のままだと累積比率も棒グラフになっているので, 折れ線グラフに変更します.

累積比率の棒グラフの上で右クリックして, [系列グラフの種類の変更]をクリックします.

下のような画面が出てきます(excel2016版)
1)累積比率の方のデータ系列(ここでは系列2)の▽をクリックします.
2)そうするとグラフの種類が出てくるのでマーカー付き折れ線を選択します.
3)次に系列2の第2軸のところをクリックしてチェックマークを入れます.

⑧項目名のラベルを付ける

グラフの上で右クリックして, データの選択をクリックします.

データソースの選択の画面が出てきます.

系列1を押して, 横(項目)軸ラベルの[編集]ボタンをクリックします.

項目ラベルの範囲をドラッグして選択します.
OKを押すと横軸に不良項目が表示されます.

⑨第2横軸を追加する

この第2横軸を作る作業は品質管理用のパレート図をexcelで作る場合特に重要です.
この作業を忘れると棒グラフの右肩に折れ線グラフとの交点を合わすことが出来ずにいつまでやっても何か思っていたパレート図と違うグラフになってしまいます(経験談). 

グラフをクリックして上の[デザイン]タブをクリックします.
[グラフ要素を追加][軸][第2横軸]とクリックしていきます.

グラフの上部に第2横軸が追加されます.

上はexcel2016の手順ですが, 他のバージョンでもグラフツールからレイアウト⇒軸⇒第2横軸で左から右に軸表示を選択していくと第2横軸を追加することが出来ます.

⑩第2横軸を軸の書式設定で見えなくする

第2横軸が追加されたらその部分(グラフ上部)を右クリックし,
[軸の書式設定]をクリックします.

軸の書式設定の画面が出てきます.
第2横軸の書式設定では,
1) 軸位置:目盛にチェックを入れます.
2) 目盛の種類:なしを選択します.
3) ラベルの位置:なしを選択します.

⑪棒グラフの要素の間隔を0にする

ここまできたらもう一息です.
棒グラフの上で右クリックして[データ系列の書式設定]をクリックします.
要素の間隔0%に変更します.
すると棒グラフ同士の間隔が詰まって隣接するようになります.

⑫第2縦軸の最大値を1にして表示を%にする

第2縦軸を右クリックして軸の書式設定を選びます.

軸の書式設定の軸のオプションで目盛の最大値を1に変更します.

同じく軸の書式設定で, 表示形式パーセンテージにします.

⑬縦軸の最大値を件数の合計にする

次はグラフ左側の件数の縦軸の最大値を変更していきます.

縦軸を右クリックし, 軸の書式設定を選びます.

軸の書式設定で,
第1縦軸の目盛の最小値を0,
最大値を不良件数の合計の値(この例では34件)にします.

入力すると下図のような感じになります.

1項目目の棒グラフの右肩に累積比率の折れ線グラフが来るようになります.
ここまで来ればあとはグラフの体裁を整えるだけなのでパレート図作成の手順としては実質これで完成です.

⑭軸ラベルを付ける・書式設定する

グラフに件数・累積比率・不良項目の軸ラベルを付けます.
棒グラフの塗りつぶし・枠線の追加/色の変更を好みに合わせて行います.

折れ線グラフも線の色・太さの変更,
マーカーの形・色の変更などを行って完成です!

【関連項目】

パレート図~QC7つ道具~

パレートの法則 (80対20の法則)とロングテール