2019/7/2

イントロダクション

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

READ MORE

QC(Quality Control)など工学分野で用いられるパレート図は,一般に普遍的ないくつかのきまりごとをもっています。

ここでは,それら諸点を確保したうえで“作り方”を追っていきます。もっとも,そこには場所限定的なきまりごとを伴うことが常だとは思いますので,以下の内容に関しては,ときに端折り,ときに変更を加え,あるいは不足する部分を補いながら読みすすめてください。

以下,Excel 2010 を使ったストリクトな仕様からのパレート図の作り方です。

※ Excel 2013 以降ないしは「Office365版」のExcelは,パレート図: 2013, 2016, 2019, Subscription版専用のページがあります。

綾子

元データ

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

元データ・パレート図

元表を準備する

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

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

件数を合計します。

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

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

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

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

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

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

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

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

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

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

折れ線グループからマーカー付き折れ線(あるいはマーカーのない種でも)を選択します。

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

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

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

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

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

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

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

目的のものではないですが,これまでの作業によって,いうなれば簡易型のパレート図が出来上がりました。ここからは,これにさらに作業を重ねていって,下の図のようなストリクトな仕様にフィットさせたいと思います(イメージ)。

とはいえ結局,細部に至っては環境によりけりなんて感覚もありますし,“ストリクト”を密に定義することは困難です。ゆえにここでは,私が「ここはまずまずのコンセンサスがあるかも」なんて思う注意点を優先して押さえていくといった方法をとりたいと思います。その意味で,以下は主観に寛容な内容です。

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

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

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

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

具体的には,順に


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

  1. 【プロットエリア】ほぼ正方に
  2. 【グラフタイトル】下に配置

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

  1. 【第1軸の最大値】件数の合計
  2. 【軸の目盛線の向き】内向き
  3. 【第2軸の最大値】100%
  4. 【第2軸の目盛間隔】20%, または10%
  5. 【要素間の間隔】空けない
  6. 【累積比率線】必ず原点から引く
  7. 【累積比率線の要素(マーカー)の位置】各棒の上辺の右端
  8. 【累積比率ラベル】50%(仮)を超える最初の要素に付置
  9. 【件数ラベル】直上Hと対応する棒要素まですべて付置

といったところです。

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

F.【累積比率線】必ず原点から引く G.【累積比率線の要素(マーカー)の位置】各棒の上辺の右端

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

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

プルダウンメニューのの方で処理を進めた場合,セルの挿入を選択し,セルの挿入ダイアログが現れたら,下方向にシフトを選択してOKを返しておきます。

ホーム→挿入

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

E.【要素間の間隔】空けない H.【累積比率ラベル】50%(仮)を超える最初の要素に付置 I.【件数ラベル】直上Hと対応する棒要素まですべて付置

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

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

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

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

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

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

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

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

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

棒へデータラベルを追加

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

今度は 線 にもラベルを打ちたいと思います。ただし,条件Hのとおり先ほどラベルを打った項目(=棒)のなかでも,最も右にあるものの右肩部分と位置的にかぶるマーカーにだけ,データラベルを追加します。

線へデータラベルを追加

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

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

A.【第1軸の最大値】件数の合計 B.【軸の目盛線の向き】内向き C.【第2軸の最大値】100% D.【第2軸の目盛間隔】20%, または10%

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.【プロットエリア】ほぼ正方に 2.【グラフタイトル】下に配置

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

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

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

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

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

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

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

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

パレート図,完成です。

パレート図の完成

Tips

1つTipsを加えます。

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

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

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

そんなとき!

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

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

これは要点のみ示すと,

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

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

晴花

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

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

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

また,こんなWebツールも用意しています。