2023/7/10

イントロダクション

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

READ MORE

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

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

なお,Excel 2013 から大きめのUI変更があったこと,そして機能面での向上が図られた部分があることから,パレート図の作成(QC ver.) with Excelで触れた手続きをこちらで若干見直したいと思います。ここでは一連の手続きを Excel 2016 で追っています。もっとも手続きそのものは「永続ライセンス版」にいうところの Excel 2019, Excel 2013,それから「Office365版」の Excel (本頁更新時点のver.1905)でも基本的には同じです(変更箇所は都度記載)。

綾子

元データ

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

元データ・パレート図

元表を準備する

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

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

件数を合計します。

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

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

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

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

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

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

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

"累積比率"系列のグラフの種類にマーカー付き折れ線(あるいはマーカーのない種でも)を指定し,第2軸のチェックボックスをONにします。

「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(ゼロ)を入力し,「累積比率」列にパーセントスタイルを適用しておきます。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

棒へデータラベルを追加

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

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

線へデータラベルを追加

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

以上で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ツールも用意しています。