グラフ(Excel)

Excelでパレート図を作る(Excel2016以降対応)

スポンサーリンク

こんにちは。システム担当の父親、KATSUです。

エクセル歴20年、アクセス歴10年ですが、ブログは40記事投稿した程度です。

これまでは、アナリティクスで1日当り10PV以下でしたが、少しずつPV数も増えてきました。SEOで表示される記事も増えてきました。

ブログの書き方も少しずつ慣れてきましたので、これからは品質も意識していきます。


今回は、パレート図の作り方について紹介します。

目次の「4.複合グラフからパレート図を作る」では、正確なパレート図をつくることを重視しています

手順もかなり細かい内容になっています。

パレート図を作るのに時間をかけたくない方は、目次4を飛ばしてもらっても構いません。

パレート図を作る目的

パレートは、棒グラフと折れ線を組み合わせたグラフです。

各項目と全体に対する累積比率が視覚的に分かります。

パレート図の説明

題解決の手法であるQC7つ道具の一つであることが知られています。

品質管理的な考え方で、「重点志向」というものがあります。

何か問題を解決する場合、比率の高い項目にターゲットを絞って取り組むべきだ、という考え方です。

多くの人が、簡単に解決しそうな、取り組みやすい項目を取り組みたがります。

問題の大きさから取り組むべきことが一目で分かるのがパレート図です。

「ABC分析」という言葉でも表現されます。

元データを作る

パレート図を作るための元データを作ります。

ここでは、都道府県別のズワイガニ漁獲高のデータを抜粋しています。

データの参照元のリンクを貼り付けておきます。

漁業算出額|政府統計の総合窓口(E-Stat)

パレート図の元データ作成

分析グラフからパレート図を作る場合はこれでデータの作成は完了です。

分析グラフからパレート図を作る

まずは、時間をかけずにパレート図を作る方法について紹介します。

データ範囲を選択し、統計グラフをクリックします。

統計グラフをクリック

統計グラフメニュー内の「パレート図」を選択すると、グラフが作成されます。

パレート図を選択

これが統計グラフで作成されたパレート図です。

統計グラフで作られたパレート図

仮に、北海道の数値を5000にして、一位の兵庫県より多い数値に変更してみます。

パレート図の表示順が変わり、北海道が左端に表示されました。

北海道のデータを修正

このように、統計グラフから作成した場合は、大きい数値から自動で並び変えられます。

最後に「その他」を設けても、右端に表示されない場合がありますので、注意が必要です。

複合グラフからパレート図を作る

ここからは、正確なパレート図を作る方法について紹介していきます。

正確なパレート図とは、縦軸の最大値がデータの合計値でないといけません。

各データ(棒)を折れ線グラフの始点までずらすと、「棒の長さ」が「折れ線の終点から終点までの長さ」と等しくなります。

正確なパレート図のイメージ

上のグラフでの緑の棒グラフは、ウオーターフォールチャートから作成できます。

ウオーターフォールチャート

元データに累積比率を追加する

作成した元データの右隣に項目名「累積比率」を追加します。

項目名、ゼロの順に入力します。ゼロはパレート図を作る時に必要になってきます。

累積比率の追加

2行目以降の数式を、絶対参照を使って作成します。

絶対参照ので式を作る

数式の絶対参照については、過去に記事を掲載していますので、よかったら参考にしてください。

最終行の一つ下のセルまで数式を貼り付けます。

オートフィルでも貼り付けることが出来ます。

「最終行の一つ下」というのが大事なポイントです。貼り付ける範囲が正しくないと、正確なパレート図を作ることができません。

式を貼り付ける

合計値を表示させたらデータの完成です。合計値は軸の範囲調整を行う時に活用します。

合計を表示

ここまで完了したら、次はいよいよ正確なパレート図の作成になります。

複合グラフを作る

データ範囲を選択し、おすすめグラフを選択します。

おすすめグラフをクリック

一番上の棒グラフと折れ線の複合グラフを選択し、OKをクリックします。

複合グラフを選択する

グラフが作成されました。

作成されたグラフ

ここから、正確なパレート図を作成するための加工を進めていきます。

第二軸を作って折れ線の視点と終点を調整する

まずは、凡例と目盛線を削除します。

グラフを選択すると、「+」マークが表示されるので、クリックします。要素名が書かれたチェックボックスが表示されます。

グラフ要素を表示

「目盛線」と「凡例」のチェックを外し、削除します。

目盛線と凡例のチェックを外す

「グラフ要素を追加」をクリックし、「第2横軸」を追加します。

第2横軸の追加

第2横軸(上側)にラベルが追加されました。

次に、折れ線の両端を調整します。

折れ線の両端を調整したい

折れ線を選択し、軸の書式設定を選択します。

軸位置を「目盛」にします。

軸位置を目盛にする

折れ線グラフの両端が調整されました。

折れ線グラフの両端が調整される

折れ線グラフの両端が調整されたので、邪魔な第2横軸ラベルを非表示にします。

軸ラベルを消したい

ここで、「DELETE」キーを押してはいけません。軸が完全に消去されてしまいます。

第2横軸の書式設定で、目盛の種類を「なし」にします。

目盛をなしにする

ラベルの位置も、目盛の種類と同じように「なし」にします

ラベルをなしにする

これで、第2横軸のラベルを非表示にすることができます。

棒グラフの項目と幅を調整する

福島県の隣が空白になっていますので調整します。

空白を調整したい

右クリックメニューを表示させ、「データの選択」をクリックします。

データの選択をクリック

「横軸ラベル」の「編集」をクリックします。

編集をクリック

空白セルも含んだ範囲が選択されているので、範囲を再設定します。

空白セルを除外

次に、「金額(百万円)」を選び「編集」をクリックすると、同様に空白セルが選択されていますので、同様に範囲を設定し直します。

編集をクリック

空白の項目を除外することが出来ましたが、棒が細いので太さを調整します。

棒の太さを調整する前

データ系列の書式設定から、要素の間隔をゼロにします。

要素の間隔をゼロに

次に、枠線の色を白に設定します

枠線を白に

棒グラフの幅が調整されました。

棒グラフの調整後

もう少しで正確なパレート図が完成します。

目盛を調整する

第一縦軸(金額)の目盛の調整を行います。

最大値を金額の合計値に設定します。

第一縦軸の最大値を合計値にする

元データの合計値から引用します。

金額の合計値

第2縦軸(累積比率)の最大値を調整します。必ず1(100%)に設定するようにしてください。

単位は好みですが、ここでは0.2(20%)に設定しています。

パーセントの最大値と単位を調整

正確なパレート図としての形に仕上がりました。

完成したパレート図

タイトルや目盛線などの細かい調整を行い、パレート図が仕上がりました。

ここでは、4県で全体の8割程度を占めていることが分かります。

修正されたパレート図

横棒グラフ式のパレート図

パレート図は、不良発生原因の内訳を表現するのによく使われます。

原因を項目名にするとどうしても長い文字数になってしまいます。

文字数が長くなると、言葉が途切れてしまいます。

項目が途切れる

縦棒グラフを横棒グラフにすると、項目を全て表示することが出来ます。

横棒のパレート図にしてみた

このデータの参照元は「国民生活基礎調査」の中からダウンロードできます。

出生動向基本調査(結婚と出産に関する全国調査)

まとめ

パレート図を使うことで、問題を解決する時に何から手をつけるべきか正しく判断することが出来ます。

急ぎで傾向を確認したい時は「統計グラフ」から、報告用の正確なパレート図を作りたい場合は「複合グラフ」からつくるとよいです。

正しいパレート図を作る手順は若干面倒くさいように感じられますが、グラフの仕上がり具合で相手への伝わり方が全く違ってきます。

是非マスターしておきましょう。


ここまで記事を読んでくれたあなたは、多分「QC7つ道具」について関心があるのではないでしょうか?

このブログでは、パレート図だけではなく、ヒストグラムや散布図についても記事を書いています。

発表会やプレゼンの資料作りに役に立てると思いますので、よかったらごらんください。

また最後に「QC手法」の学習にとても有益な本の紹介をさせてください。

日科技連の「コマッタくんの問題解決とQC手法」という本です。

漫画形式のレトロな画風で分かり易くQC7つ道具と問題に対する考え方について解説してありますので、是非参考にして頂ければと思います。

created by Rinker
¥2,200 (2021/09/16 23:43:58時点 Amazon調べ-詳細)

次回は、棒グラフと折れ線の複合グラフについて紹介します。

今回のパレート図も、複合グラフの一つですが、もっと汎用的な内容に触れていきます。

本日も最後までありがとうございました。

スポンサーリンク

-グラフ(Excel)