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

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

How-to

パレート図 for QC
Excel 2013 or later

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

QC(Quality Control)など工学分野で用いられるパレート図の仕様は、細部については利用される環境によって異なることと思いますが、大枠のきまりごとについてはいくつかの共通する部分も見られるのではないでしょうか。

ここではそうしたいくつかの点を念頭に置きながら、パレート図の作り方を追っていきたいと考えています。以下の手続きをご覧いただける際は、パレート図をお使いの環境で要求されるきまりごとにしたがって、手続きを端折って、変更して、ないしは補いながら追っていただければと思います。

Excel 2013 では UI の変更があったこと、および機能の向上が見られた部分があることから、パレート図の作成(QC ver.) with Excel でふれた手続きをこちらで若干見直したいと思います。また、手続きそのものは Excel 2016 を通して追っていますが、Excel 2013でも大枠は変わりません(変更箇所は都度記載)。以下、Excel2013 または Excel2016 を使ったややストリクトな仕様によるパレート図の作り方です。

元データ

綾子

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 列を(「計」行を除いて)選択します。

挿入タブグラフグループの複合グラフの挿入ボタンユーザー設定の複合グラフを作成するとクリックします。

scrollable

挿入→複合グラフの挿入→ユーザー設定の複合グラフを作成する

綾子

6

"累積比率"系列のグラフの種類にマーカー付き折れ線を指定し、第 2 軸のチェックボックスを ON にします。

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

綾子

7

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

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

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

綾子

8

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

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

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

綾子

9

具体的には、順に…


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

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

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

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

…といったところです。

綾子

10

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

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

綾子

11

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

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

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

scrollable

ホーム→挿入

綾子

12

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

綾子

13

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

綾子

14

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

綾子

15

グラフが選択された状態から、リボンのデザインタブグラフのレイアウトグループのグラフ要素を追加ボタンをクリックします。

下の図のように第 2 横軸と 2 段階のプルダウンメニューを処理していきます。

scrollable

デザイン→グラフ要素を追加→軸→第2横軸

綾子

16

挿入した第 2 横軸をアクティブにして、書式タブ現在の選択範囲グループの選択対象の書式設定ボタンをクリックします。

scrollable

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

綾子

17

軸の書式設定ウインドウが表示されます。

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

ついでに、カテゴリーを「塗りつぶしと線」に切り替えて、ここで軸線を消しておくと後がラクかと思います。

scrollable

「軸の書式設定」ウインドウの設定箇所

綾子

18

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

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

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

綾子

19

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

scrollable

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

綾子

20

データ系列の書式設定ウインドウが表示されます。

要素の間隔のスライダーを0%に設定します。

「データ系列の書式設定」ウインドウの設定箇所

綾子

21

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

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

綾子

22

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

ここからデータラベルの追加データラベルの追加とたどっていきます。

棒へデータラベルを追加

綾子

23

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

綾子

24

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

scrollable

線へデータラベルを追加

綾子

25

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

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

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

綾子

26

グラフの第 1 縦軸をアクティブにして、書式タブ現在の選択範囲グループの選択対象の書式設定ボタンをクリックします。

scrollable

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

綾子

27

軸の書式設定ウインドウが表示されます。

最小値0(ゼロ)を、最大値件数の合計を入力し、あわせて(ver.2013では目盛間隔)の値を適宜設定します。また目盛の種類についても、内向き(場合によってはそれ以外でも)に変更しておきます。

2013

ver.2016 では項目名の一部、具体的には目盛間隔→「単位」に、その子要素である目盛が「主」と変更されました。したがって 2013 の場合には、以下文中に同項目の名称が登場する際、適宜「目盛間隔」「目盛」に読み換えてください。

2013と2016における表記の変更点

また、ついでにカテゴリーを「塗りつぶしと線」に切り替えて、ここで軸線に関する書式の設定を済ませておくと後がラクかと思います。

scrollable

「軸の書式設定」ウインドウの変更箇所

綾子

28

グラフの第 2 縦軸をアクティブにして、書式タブ現在の選択範囲グループの選択対象の書式設定ボタンをクリックします。

scrollable

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

綾子

29

軸の書式設定ウインドウが表示されます。

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

先ほどと同様、ここでもカテゴリーを「塗りつぶしと線」に切り替えて、軸線に関する書式の設定を済ませておくといいかと思います。

scrollable

「軸の書式設定」ウインドウにおける変更箇所

綾子

30

第 1 縦軸の任意の目盛線をクリックしてすべての目盛線を選択します。

この状態から、DELETEキーを押してこれらを削除します。

綾子

31

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

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

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

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

綾子

32

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

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

綾子

33

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

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

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

綾子

34

グラフタイトルをプロットエリアの下部にドラッグして移動させます。

綾子

35

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

パレート図、完成です。

FINISHED

パレート図の完成

Tips

綾子

36

1 つ Tips を加えます。

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

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

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

綾子

37

そんなとき!

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

2段ラベルのパレート図

綾子

38

これは要点のみ示すと,

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

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

晴花

39

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

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

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


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

LastUpdate

2016.7.12

.
このページの先頭へ