エクセルで0からスタートするパレート図の作り方を紹介します。
今回は、下記の表のような、〇×株式会社の各支店の売上データを使ってパレート図をつくってみます。
最終的に、下図のようなパレート図が完成します。
最後には実際のファイルをダウンロード可能なので是非業務に役立ててください。
パレート図や関連する他のＱＣ７つ道具については、以下のページをご覧ください。
エクセル2013パレート図の作り方
エクセル2013で0からスタートするパレート図を作る方法は、大まかに３つに分けられます。
2. 複合グラフをつくる
3. パレート図に仕上げる
ではそれぞれの手順を解説します。
1.データをつくる
最初に項目と項目の数値を表にします。今回はサンプルとしてある会社の店舗ごとの売上データをパレート図にします。
表に必要なのは、項目名と数値、最後に合計です。合計がなくともパレート図を作れますが、あった方が表が見やすくなるのと、累積比率を計算するときに簡単になります。
大きい棒グラフから表示したいので表の項目の値は降順(大きい→小さい)で入力しておきます。
次に累積金額を作成します。表の一番上のセルは、そのまま同じ値にし、それ以下の項目は一つ上のセルを順次足していきます。
エクセルでの計算は、以下のようにします。
(C6は、横浜支店の売上金額)
(D6は横浜支店の累積金額、C7は大阪支店の売上金額)
大阪支店の累積金額の隅の■をドラッグして神戸支店までコピーします。
累積額の合計は最終行と同じ値です。一つ上のセルを足さないように気を付けてください。
(D14は、神戸支店の累積金額)
次は累積比率を計算します。ここで注意ですが、パレート図の累積比率は0%から始めるのでダミーの0%を表の一番上に作る必要があります。
一番上の項目に行を挿入して0%のセルを作成してください。
累積比率は
で算出できます。セルに計算式を入れてセルの書式を「パーセンテージ」にしてください。
横浜支店の累積比率を作成したら、同じようにセルの隅をクリックしてドラッグして計算式を合計までコピーします。
これでデータが完成してパレート図をつくる準備ができました。
2.基本のグラフを作成する
では次にエクセル2013のグラフ作成機能で、ベースとなるグラフを作成します。
まずは、パレート図にしたい表の値を選択します。
今回は、店舗名。売上金額、累積比率だけをつかいます。
Ctrlキーを押しながら、店舗名から売上金額、累積比率を選択してください。合計欄の選択は不要です。
上図のように選択した状態で、グラフを選びます。
グラフの作成方法は、excel2013のメニューから、
挿入 → 「おすすめグラフ」ボタンをクリックします。
「おすすめグラフ」をクリックすると、グラフを選ぶダイアログがあらわれます。「集合縦棒 - 第２軸の折れ線」を選んでください。ここまでを同じ手順で進んでいれば、一番最初に表示されているはずです。
このようなグラフが表示されます。ここまでで基本のグラフが作成できました。
これでは、まだパレート図とは言えないので細かい部分を修正していきます。
3.パレート図に仕上げる
ここまででも、棒グラフと折れ線グラフの複合グラフはできましたがパレート図にするには少し工夫が必要です。
グラフの太さを変える
最初に棒グラフの太さをパレート図で使うヒストグラムに変更します。
棒グラフのどれかを右クリックするとメニューができるので[データ系列の書式設定］を選択します。右側に「データ要素の書式設定」ダイアログが表示されるので、グラフのようなアイコンをクリックし、下のリストから［系列のオプション］を選択し、［系列の重なり］［要素の間隔］を両方「0」にします。
するとこのようなグラフになります。
軸の最大値を変える
次に累積比率の横軸を100%で最大にしたいので修正します。現在は120%が最大になっているはずです。
右横にあるパーセンテージの目盛を右クリックして「軸の書式設定」を表示します。軸のオプションから、最大値を1.0に変更します。
これで累計比率の最大値が100%になりました。
左の売上金額の最大値も変更しましょう。
同じように売上金額の軸を右クリックして「軸の書式設定」を表示します。最大値を合計金額の「18,740」に、最小値を「0」にします。
棒グラフが縮こまった感じになりますが問題ありません。
折れ線グラフの開始位置を正しくする
次に現状の折れ線グラフの開始点がずれているので、パレート図にするために開始点を0から始めるように修正します。
どこでもいいのでグラフを選択した状態にすると、グラフの右上に[+]ボタンがでてきます。
これをクリックすると、グラフの要素を追加したり、削除したりできるメニューが表示されます。
グラフ要素メニューの「軸」にマウスを当てる(クリックはしない)と、右に矢印がでてきます。
※矢印だけをしっかりクリックしないと、今まであった軸が消えてしまいます。その場合は、もう一度「軸」をクリックすれば元に戻ります。
その矢印をクリックすると、第１横軸、第1縦軸、第２横軸、第２縦軸のチェックボックスが出て、第２横軸のチェックがはずれています。それをチェックしてください。すでにチェックされている場合はそのままで大丈夫です。
グラフに第２横軸が追加されましたので、それをクリックして選択状態にします。
そして右クリックから「軸の書式設定」を表示してください。
軸のオプション(グラフのアイコン)を選択し、詳細設定の中にある「軸位置」の「目盛」にチェックを入れます。
これで折れ線グラフが、原点から開始するようになりました。
折れ線グラフは移動できましたは、第２横軸がちょっと邪魔です。消しておきましょう。
第2軸を消すための手順は、
軸の書式設定
ラベル
ラベルの位置→「なし」に変更
で目盛のラベルを消すことができます。
棒グラフの位置を正しくする
次に折れ線グラフに続いて棒グラフの開始位置も原点から開始するようにします。
棒グラフがずれている原因は、0%を表示するためにつくった行に空白部分があるためです。
この空白を選択からはずします。
表の空白部分をはずすためには、まず棒グラフのどこかをクリックしてください。
すると、棒グラフに関係している表の部分が選択状態になります。
ここから空白をはずします。
選択範囲の四隅の「■」をドラッグし、選択範囲から空白のセルを除外してください。
空白を選択からはずすと以下のようになります。
※ 項目の値が項目のタイトルと重なってしまう場合は、「■」の少し下をドラッグするとうまくいきます。
グラフは左にずれて原点から開始するようになりました。
これでパレート図が完成しました。
後は、色を変えたり、枠線をつけたりしてグラフのデザインを修正してください。
パレート図サンプルダウンロード
ここまで作成したパレート図のサンプルファイルをダウンロードできます。１から作成するのが難しい、簡単に作成したいという方はタイトルや項目を変更することでオリジナルのパレート図を作る事が可能です。
テンプレートとして活用してください。
※excel2013で作成していますが、他のバージョンでも使うことができます。
まとめ
エクセル2013で0からスタートするパレート図を作成する方法を紹介しました。
複合グラフをつくるまでは簡単なエクセルの操作ですが、パレート図に仕上げるまでが慣れていないとちょっと面倒です。
しかし２，３回作ってみると、その後は簡単にできるようになるでしょう。
サンプルファイルもダウンロードできるようにしたので、値や項目だけ入れ替えてやってみてもいいでしょう。
