ひとりマーケティングのためのデータ分析

手順解説 | Excel(エクセル)でおこなうビジネスデータの分析

How-to

パレート図 for QC

このページは パレート図の作成 with Excel の補足ページです。

QC(Quality Control)など工学分野で用いられるパレート図の仕様に関しては,大枠の部分に限ればいくつかの共通するきまりごとが存在しています。

ここではそれらの諸点を確保したうえでの“作り方”を追っていきます。もっとも,そこにはユーザーの周辺にて要求される細部にわたる固有のきまりごとが付随するのが常だとは思いますので,以下の内容に関しては,ときに端折り,ときに変更を加え,あるいは不足する部分を補いながら読みすすめてください。

以下,Excel 2010 を使ったややストリクトな仕様によるパレート図の作り方です(Excel 2007 でも手続きは同様です)。

なお,以下作業量の多さに萎えを感じてしまった場合には,こういう選択肢もあります。

元データ

綾子

1

元のデータです。リサーチサービス社の企業リサーチに関するここ 1 ヵ月のクレーム入電件数を項目別に集計し,降順で並べ替えたものです。


元データ・パレート図

元表を準備する

綾子

2

ここでは,相対的に過小な件数となったもの…たとえば,No. 6 ~No. 12 の 6 つの項目…は「その他」としてひとつにまとめることにします。

項目を集約(ここでは7件→1件に)

綾子

3

件数を合計します。

綾子

4

あたらしく「累積比率」列をつくります。

「累積比率」見出しの直下に,下の式を 2 行にわたって入力し,最後の計算式のみ,項目「計」行の直前の行までコピーします。

見出し「累積比率」の作成とその列の計算式:[セルD2]=C2/$C$8 [セルD3]=D2+C3/$C$8

「簡易型」のパレート図を描く

綾子

5

B/C/D の 3 列を(「計」行を除いて)選択します。

挿入タブグラフグループの縦棒ボタン集合縦棒ボタンとクリックします。

綾子

6

グラフの"累積比率"系列を選択します。

デザインタブ種類グループのグラフの種類の変更ボタンをクリックします。

scrollable

デザイン→グラフの種類の変更

綾子

7

グラフの種類の変更ダイアログが開きます。

折れ線グループからマーカー付き折れ線を選択します。ただし,のちの工程で折れ線にデータラベルを付けない場合は,マーカーなしの折れ線を選択してもいいかと思います。

綾子

8

グラフの"累積比率"系列を選択し…

書式タブ現在の選択範囲グループの選択対象の書式設定ボタンをクリックします。

scrollable

書式→選択対象の書式設定

綾子

9

データ系列の書式設定ダイアログが開きます。

使用する軸第 2 軸を割りあてます。

ここまでの過程を経て出来上がった簡易版のパレート図

「QC型」のパレート図に描きかえる

綾子

10

目的のものではないですが,これまでの作業によって,いうなれば簡易版のパレート図ができあがりました。ここからは,これにさらに作業を重ねていって,下の図のようなややストリクトな仕様のパレート図を成形していきたいと思います(イメージ)。

とはいえ,ここでは “ややストリクトな” 仕様が何を指すかを密に定義することは避けたいと思います。アウトラインはともかく,細部に至っては結局のところ環境によりけりじゃん?…なんて感覚も私にはいくらかあります。したがってこちらでは,私が「ここはまずまずのコンセンサスがあるかも」…なんて思う注意点を優先して押さえていく…といった方法をとりたいと思います。その意味で,以下は主観に寛容な内容です。

これから作成するパレート図のイメージ

綾子

11

…と,いうことで,それらの諸点を下のグラフ上に散らしてみます。

グラフの基本的な部分に関することを「緑」,同じく細かな部分に関することを「オレンジ」の吹き出しであらわしてみました。

パレート図作成における注意点(11点)

綾子

12

具体的には,順に…


<グラフの基本的な部分に関すること>

  1. プロットエリアを正方形に近い形に整える
  2. グラフタイトルを下に配置

<グラフの細かな部分に関すること>

  1. 件数の合計値がはいるまで目盛をとる
  2. 目盛線を内向きに作成
  3. 累積比率の最大目盛を 100% でとる
  4. 累積比率の目盛間隔は 20%(または 10%)刻みで作成
  5. 要素間の間隔はつくらない
  6. 累積比率をあらわす線を原点から引く
  7. 線の要素は各棒の上辺を基準としたとき,その右端で交わる垂直線上へ打つ
  8. 累積比率の 50%(仮)を占める棒の要素に件数を示すラベルを付加
  9. 累計比率 50%(仮)をはじめて超えた線の要素に累積比率を示すラベルを付加

…といったところです。

綾子

13

では以下,上の諸点を適宜処理していきます。とりあえずは,F・G 点なんかがとっかかりとしては良さそうなので,まずはこれらを片づけたいと思います。

(F)累積比率をあらわす線を原点から引く (G)線の要素は各棒の上辺を基準としたとき,その右端で交わる垂直線上へ打つ

綾子

14

ということで,上の F の点を満たすため「累積比率」に 0(ゼロ)のデータを追加します。

表の 2 行目のセル範囲をアクティブにして,リボンのホームタブセルグループにある挿入ボタンをクリックします。

(上の操作でプルダウンメニューのをクリックした場合は,セルの挿入を選択し…セルの挿入ダイアログが現れたら,下方向にシフトを選択してOKを返しておきます)

scrollable

ホーム→挿入

綾子

15

挿入した行の「累積比率」列に 0(ゼロ)を入力し,「累積比率」列にパーセントスタイルを適用しておきます。

見出し「累積比率」直下に0を入力

綾子

16

グラフの"累積比率"系列を選択してから,ワークシートの累積比率に関するデータ範囲を,ハンドルを 1 セル分だけ上にドラッグして拡げます(あらたに "0%" が含まれるようにする)。

綾子

17

念のため,上の作業を経て,この時点でのシートとグラフは下のようになっています。

綾子

18

リボンのレイアウトタブグループのボタンをクリックします。

下の図のように第 2 横軸左から右方向で軸を表示と 2 段階のプルダウンメニューを処理していきます。

scrollable

レイアウト→軸→第2横軸→左から右方向で軸を表示

綾子

19

そのまま(リボンのレイアウトタブ のまま),グループのボタンをクリックします。

先ほどと同じようにして,第 2 横軸から,今度はその他の第 2 横軸オプション…と,たどっていきます。

scrollable

レイアウト→軸→第2横軸→その他の第2横軸オプション

綾子

20

軸の書式設定ダイアログが表示されます。

下の図のように目盛の種類補助目盛の種類軸ラベルをすべてなしに,そして軸位置目盛に変更します。


それから,[閉じる]ボタンはまだ押さずに…

「軸の書式設定」ダイアログに関する設定箇所

綾子

21

そのまま線の色線なしに変更してしまいます。

これを終えたら閉じるボタンをクリックします。

綾子

22

以上で F・G 点の処理を完了しました。グラフにも当該項目が反映されていることと思います。

ということで,ここからは次の工程として,E・H・I 点を処理したいと思います。

(E)要素間の間隔はつくらない (H)累積比率の50%(仮)を占める棒の要素に件数を示すラベルを付加 (I)累計比率50%(仮)をはじめて超えた線の要素に累積比率を示すラベルを付加

綾子

23

グラフの"件数"系列(=棒)を選択した上で,リボンの書式タブ現在の選択範囲グループの選択対象の書式設定ボタンをクリックします。

scrollable

書式→選択対象の書式設定

綾子

24

データ系列の書式設定ダイアログが表示されます。

「系列のオプション」カテゴリ要素の間隔のスライダーをなしに設定し…

閉じるボタンをクリックします。

綾子

25

累積比率の 50 %(この作例での数字です。要求される規定・ルールなどに照らしてください)を占める棒の各要素に対し,件数を表示するためのラベルを加えます。この例では,2 つの項目が該当します。

下の図のように,対象となる要素だけを選択してから,その要素の上で右クリックします。

綾子

26

ショートカットメニューが表示されます

ここからデータラベルの追加をクリックします。

棒へデータラベルを追加

綾子

27

2 手前(Step 25)からの作業を必要な項目の数だけ繰り返します。このとき,上の作業をおこなった直後であれば,別の項目については 1 度クリックするだけで選択状態にすることができます。

綾子

28

今度は 線 にもラベルを打ちたいと思います。ただし,先ほどラベルを打った項目(=棒)のうち,最も右にあるものの右肩と(横軸において)同じ位置にある(線の)マーカーにだけ,データラベルを追加するものとします。

線へデータラベルを追加

綾子

29

以上で E・H・I 点の処理を終えました。

ここからは A・B・C・D 点を処理したいと思います。

(A)件数の合計値がはいるまで目盛をとる (B)目盛線を内向きに作成 (C)累積比率の最大目盛を100%でとる (D)累積比率の目盛間隔は20%(または10%)刻みで作成

綾子

30

リボンのレイアウトタブグループのボタンをクリックします。

つづいて主縦軸その他の主縦軸オプションをクリックします。

scrollable

レイアウト→軸→主縦軸→その他の主縦軸オプション

綾子

31

軸の書式設定ダイアログが表示されます。

「軸のオプション」から,最小値0(ゼロ)を,最大値件数の合計を入力し,あわせて目盛間隔の値を適宜設定します。また目盛の種類についても,内向き(場合によってはそれ以外でも)に変更しておきます。


設定が完了したら閉じるボタンをクリックします。

「軸の書式設定」ダイアログ:最大値=件数合計

綾子

32

またまた…リボンのレイアウトタブグループのボタンをクリックします。

つづけて第 2 縦軸その他の第 2 縦軸オプションをクリックします。

scrollable

レイアウト→軸→第2縦軸→その他の第2縦軸オプション

綾子

33

軸の書式設定ダイアログが表示されます。

「軸のオプション」について,最小値0(ゼロ)を,最大値1 を入力し,あわせて目盛間隔の値を0.1ないしは0.2に設定します。また目盛の種類についても,内向き(場合によってはそれ以外でも)に変更しておきます。

「軸の書式設定」ダイアログ:最大値=1

綾子

34

リボンのレイアウトタブグループの目盛線ボタンをクリックし,プルダウンメニューを主横軸目盛線なしとたどります。

scrollable

レイアウト→目盛線→主横軸目盛線→なし

綾子

35

以上で A~D の項目についての処理を終えました。

いささか唐突かもしれませんが,ここらへんで <グラフの細かな部分に関すること> では掲げていない,以下のような細かな点を必要に応じて修正しておいた方が better かなと思うところがあります。これについての具体的な操作の手順は…ボリューム過多で割愛します。ゴメンナサイ…。

…とまれ,ここではいくつかの手を加えて,下段のグラフをみちびきました。

その他任意の修正項目…ラベル追加/凡例削除/第1横軸文字列の方向/第1横軸目盛なし

綾子

36

最後に,残る 1・2 の項目を処理したいと思います。

(1)プロットエリアを正方形に近い形に整える (2)グラフタイトルを下に配置

綾子

37

グラフの横幅を任意に決めて調整を終えたら,グラフエリアをその横幅よりも多少長めに縦方向に[引き伸ばし or おそらく少ないでしょうが縮め]ます。イメージとしては縦長の長方形を作るようなカンジです。

つづいてグラフエリアの上方に,プロットエリアを正方に調整したうえで据えておきます。

プロットエリア:正方形, グラフエリア:縦長の長方形

綾子

38

リボンのレイアウトタブラベルグループのグラフタイトルボタンをクリックします。

プルダウンメニューからグラフの上をクリックします(「グラフの下!」がありません…)。

グラフタイトルが挿入されたら,それをプロットエリアの下部にドラッグして移動させます。

scrollable

レイアウト→グラフタイトル→グラフの上

グラフタイトルの移動

綾子

39

グラフタイトルに直接任意の内容をタイプして…

パレート図,完成です。

FINISHED

パレート図の完成

Tips

綾子

40

1 つ Tips を加えます。

横軸の項目名に関してですが,上の作例のように「左へ 90° 回転」じゃなくて,ときには 横向き! で表示させたい場合もあるんじゃないかなと思います。

この場合でも,文字数が少なければ問題はあまりないとは思うんですが,ある程度の長さがあれば話は別で,干渉を避けるため下のように文字列に折り返しをかけるか(左),フォントサイズを小さくするか(右)…といった対処法をとらなければならなくなります。

しかし…。これを経るとグラフがなんだかどこかしら野暮ったさをまとってしまうような…そんな気がしないでもありません(個人の感想でry)。

綾子

41

そんなとき!

このページの作例において,いうなれば “捨て軸” にした第 2 横軸を再利用して,下図のように 2 段ラベルにしてしまうと “イイカンジ” になる場合があります。

横軸2段ラベルのパレート図

綾子

42

これは要点のみ示すと,

  1. 「項目」列を 2 列に増やし,交互に名前を振る
  2. 図のピンクの領域を件数系列に,緑の領域を累積比率系列に割りあてる
  3. 第 2 横軸を図の下端に可視化させる
  4. 第 2 横軸のラベルの角度を 1° に設定
  5. 第 1 横軸の軸線をなくす

…といった手続きによって表現が可能です。

晴花

43

…いきなり横からごめんなさい。

以上のように,QC で利用される形式のパレート図をエクセルでつくるのは,結構な手間がかかります。そんな機会が希であればそれでもいいのでしょうが,そうでない場合…とってもメンドーでヘコんじゃいます。後者の場合,いっそのこと自動化しちゃうのもオススメです。

参考までに,別のページで VBA での自動化の一例を提示しています。シロウトコードですが,必要に応じて下のリンクから参照ください。

  • 本頁で使用したデータはすべて架空のものです。また特定の会社等に実在する人物名,および同場所で実際に観測されたデータ群などを根拠にしたものでもありません。
.

LastUpdate

2017.7.28

.
このページの先頭へ