Introduction
Ver. all 2013 2016 2019 2021 365
QC7つ道具としても有名なパレート図をエクセルで作成する方法を紹介します。
ちなみにQC7つ道具とは、品質管理に用いられる代表的なツールを集めたものです。（特性要因図、管理図、層別、ヒストグラム、パレート図、チェックシート、散布図）
最初に、バージョン2016以降での作成方法を紹介します。
簡単に作成出来るのですが、細かい調整が効かないという欠点があります。
そのため、その後に通常のグラフ機能を使用した微調整可能なパレート図を作成する手順を紹介します。
以前のバージョンを使用している方はそちらを参考にしてください。
パレート図はイタリアの学者パレートさんの名前からきているらしいにゃ
パレート図とは
パレート図は、データを発生頻度順に並べ、要素割合の折れ線グラフを付け加えた図です。
対処すべき主要原因を識別するのに役立ちます。
具体的には、項目をデータの降順（大きい順）に並べて縦棒グラフを作成し、
そこに構成比を累積した折れ線グラフを追加します。
優先順位や重要度の決定に役立つグラフです。
簡単な作成方法（2016以降）
パレート図は、エクセルバージョン2016以降は簡単に作成することができます。
今回は、下のデータを使用します。
猫商品の中で、どの商品を重要視すべきなのか確認したかったことにしましょう。
データを選択して、
［挿入］タブ → ［統計グラフの挿入］ →
［パレート図］を選びます。
これで、下のようなパレート図が簡単に作成できます。
具体的には、大きく下の2点を自動で設定してくれます。
- データを降順に並べ替え
- 通常の棒グラフに追加して、構成比の累計を折れ線グラフとして2軸に表示
線や棒の色を変更したりするのは、通常のグラフと同じように設定できるのですが、例えば下のように「折れ線を原点から始めたい」というような細かい調整はできません。
細かい調整が必要になりそうな場合は、次から説明する手順で作成してください。
元データの準備から必要になるので、作成に時間がかかりますが、パレート図そのものへの理解も深まると思いますよ。
データの準備
ここからは、パレート図を通常のグラフ機能を使用して作成していく方法です。
まずデータの準備からです。
今回は、下のデータを使用します。
猫商品の中で、どの商品を重要視すべきなのか確認したかったことにします。
パレート図の縦棒グラフは降順（大きい順）に並んでいる必要があります。
並べ替えをしておきましょう。
データが入っているセルを一か所を選択して、
［ホーム］タブ → ［並べ替えとフィルター］ →
［降順］を選びます。
並べ替え完了。
次に、パレート図には構成比が必要なので、全体の合計を算出しておかなければなりません。
「合計」の欄を作成しておいて、
合計を算出するセルを選択後、［ホーム］タブ → ［オートSUM］ボタンを２回クリック。
下の計算式が入り、
簡単に合計が算出されます。
さらに、右側に「構成比」と「累積構成比」の場所を作成します。
「構成比」を算出します。
【=】を入力して、
「C3」セルを「C13」で割り算する計算式を入力します。
合計の「C13」セルは【F4】キーを押して「$」記号を付け絶対参照にします。
構成比の作成が不安な方は、下のリンク先をご覧ください。
完成した式は
=C3/$C$13
下のような結果になりました。
「%」表示に切り替えましょう。
選択して、［ホーム］タブ → ［パーセントスタイル］ボタンを押します。
後は、オートフィル機能を使用して下にコピー。
一番下の「合計」の場所は「100%」になるはずです。
さらに、「累積構成比」も算出しましょう。
「累積構成比」とは、言葉そのままで構成比を累積していったものです。
構成比を足し算していけばよいのですが、計算式一つで算出するには、下のように式を入れます。
=SUM($D$3:D3)
最初の「D3」に絶対参照を使用してセル番地が移動しないように固定しています。
式の意味がピンと来ない方は、一つの式で作成しようとせずに、
最初のセルには「=D3」と入力して、
隣のセルをそのまま参照して表示。
次のセルに、上のセルに左のセルを足して、
その式を下にコピーしましょう。
これで下準備の完了です。
パレート図の作り方
データの準備ができたら、パレート図の作成です。
使用するセルは下の赤線で囲んだ場所です。
途中の「構成比」は「累積構成比」のために算出したものなので使用しません。同じく下の「合計」も「構成比」のために算出したものなので使用しません。
離れたセルを選択するので【Ctrl】キーを活用しましょう。
まず、最初の部分を選択して、
次に【Ctrl】キーを押しっぱなしで追加したい場所を選択。
これで離れたセルを同時選択できます。
選択できたら、［挿入］タブ → ［おすすめグラフ］をクリックします。
下の画面が表示されますが、この画面は使用しません。
［すべてのグラフ］タブ → 左下の［組み合わせ］を選択 → 右下の［累積構成比］の［第2軸］の場所にチェックを入れます。
［OK］を押すと、下のように縦棒を主軸、折れ線を2軸にした複合グラフが作成されます。
とりあえずタイトルを「猫用品 売上分析」と入力しました。
では、気になる点を修正していきます。
横棒を太くする
横棒を太くして棒が繋がるようにしましょう。
どれでも良いので、縦棒をダブルクリックすると、
画面右側に［データ系列の書式設定］作業ウィンドウが表示されます。
※この作業ウィンドウは後で使用するので消さないようにしましょう。
その［系列のオプション］の場所で、［系列の重なり］［要素の間隔］をそれぞれ「0%」に設定します。
これで、隣との間隔がなくなり縦棒が繋がります。
折れ線を原点に移動する
次に、折れ線の始まりを左下の原点の場所に移動してみましょう。
ちょっと手間がかかります。
まず、折れ線に「第2横軸」を追加します。
折れ線を選択して、
［グラフのデザイン］タブ → ［グラフ要素を追加］ → ［軸］の右側をクリック → ［第 2 横軸］を選びます。
すると、下画像のようにグラフの上に新たな横軸が表示されます。
表示された横軸をクリックして、
右側の［軸の書式設定］作業ウィンドウ → ［軸のオプション］ → ［軸位置］の［目盛］にチェックを入れると、
折れ線の始まりが、左端に移動します。
これで、上に表示されている横軸ラベルは必要なくなりました。
今設定した画面の下側にある、［ラベル］をクリックして隠れていたメニューを表示 → ［ラベルの位置］を［なし］に設定します。
これで横軸ラベルが非表示になりました。
続けて折れ線を下に移動しましょう。
折れ線を選択していると、元の表の折れ線に使用しているセル範囲も選択されているはずです。
この場所を加工していきます。
まず、折れ線で使用しているセル範囲を下に１つだけ広げます。
右下の「■」にマウスを移動するとマウスカーソルが斜め矢印に変更されます。
その状態で下に引っ張ると、範囲が広がります。
続けて、全体の範囲を１つ上に移動します。
マウスを今度は線上に移動するとマウスカーソルが十字矢印に変わります。
その状態で上に引っ張ると、全体の範囲が上に移動します。
項目として入力されている文字部分が「0」の数値として扱われるので、折れ線が「0」からスタートしてくれます。
これで折れ線が原点に移動しました。
目盛りの数値を調整する
左右の目盛りの最大値を変更しましょう。
右側の目盛りは、調整の途中でパーセント表示から数値の表示に変更されてしまっているはずです。
では、左の第1軸からいきましょう。
選択して、
右側の［軸の書式設定］作業ウィンドウの［軸のオプション］ → ［最大値］の場所を変更します。
入力する数値は、表の「合計」の場所です。
ここが第2軸の「100%」と同じ意味合いになるので、左右の軸が一致することになります。
今回の場合は「4724000」と入力。
「最大値」を変更すると「最小値」も自動で変更されることがあります。その際は最小値には「0」と入力しましょう。
縦棒が下画像のように低くなるはずです。
次に、右側の目盛りを修正します。
クリックして、
作業ウィンドウの最大値には100%の意味で「1」と入力します。
また、下にある［表示形式］をクリックして畳まれていたメニューを表示して、
［カテゴリ］の場所から、
［パーセンテージ］を選びましょう。
これで、「0%」～「100%」のパーセント表示になりました。
補助目盛線が必要ない場合は、クリックして【Delete】キーを押すだけで削除できます。
目盛りの単位や色など調整して完成です。
まとめ
バージョン2016以降を使用している場合は他のグラフと同じように簡単に作成できます。
しかし、パレート図仕様の設定項目しか表示されなくなるので微調整が難しくなります。
「え、あのメニューが表示されない・・・」ということが頻繁にあります。
普段グラフ作成に慣れている方は、手間がかかっても後半説明した方法で作成した方が融通が利くと思いますよ。
いちから作成すると結構大変だにゃ