エクセル2013でパレート図を作成する方法 | サンプルテンプレートあり

エクセル2013でパレート図を作成する方法を紹介します。

今回は、下記の表のような、〇×株式会社の各支店の売上データを使ってパレート図をつくってみます。

全国展開の小売店の各支店の売上データ表

最終的に、下図のようなパレート図が完成します。

最後には実際のファイルもダウンロード可能なので是非業務に役立ててください。

パレート図の基本的な項目については、以下のページをご覧ください。

エクセル2013でパレート図の作り方

エクセル2013でパレート図を作る方法は、大まかに3つに分けられます。

1. データをつくる
2. 複合グラフをつくる
3. パレート図に仕上げる

ではそれぞれの手順を解説します。

1.データをつくる

最初に項目と項目の数値を表にします。今回はサンプルとしてある会社の店舗ごとの売上データをパレート図にします。

表に必要なのは、項目名と数値、最後に合計です。合計がなくともパレート図を作れますが、あった方が表が見やすくなるのと、累積比率を計算するときに簡単になります。

売り上げの表を降順にしておく

大きい棒グラフから表示したいので表の項目の値は降順(大きい→小さい)で入力しておきます。

次に累積金額を作成します。表の一番上のセルは、そのまま同じ値にし、それ以下の項目は一つ上のセルを順次足していきます。

売上表の累積金額の算出方法

エクセルでの計算は、以下のようにします。

エクセル2013で売り上げのセルを累積のセルにコピーする
(C6は、横浜支店の売上金額)

エクセル2013で売り上げと累積値を加算する
(D6は横浜支店の累積金額、C7は大阪支店の売上金額)

大阪支店の累積金額の隅の■をドラッグして神戸支店までコピーします。

大阪支店のセルの隅をクリック

大阪支店のセルの隅をドラッグして神戸支店までコピー

累積額の合計は最終行と同じ値です。一つ上のセルを足さないように気を付けてください。

最終行の合計値は合計をそのまま加算
(D14は、神戸支店の累積金額)

次は累積比率を計算します。ここで注意ですが、パレート図の累積比率は0%から始めるのでダミーの0%を表の一番上に作る必要があります。

一番上の項目に行を挿入して0%のセルを作成してください。

累積比率を作る:一番上に0%を挿入

累積比率は

累積金額 ÷ 合計金額

で算出できます。セルに計算式を入れてセルの書式を「パーセンテージ」にしてください。

累積比率の計算:累積金額 ÷ 合計金額

横浜支店の累積比率を作成したら、同じようにセルの隅をクリックしてドラッグして計算式を合計までコピーします。

セルの計算式をドラッグしてコピーする

これでデータが完成してパレート図をつくる準備ができました。

2.基本のグラフを作成する

では次にエクセル2013のグラフ作成機能で、ベースとなるグラフを作成します。

まずは、パレート図にしたい表の値を選択します。

今回は、店舗名。売上金額、累積比率だけをつかいます。

Ctrlキーを押しながら、店舗名から売上金額、累積比率を選択してください。合計欄の選択は不要です。

表の値の部分をすべて選択する

上図のように選択した状態で、グラフを選びます。

グラフの作成方法は、excel2013のメニューから、

挿入 → 「おすすめグラフ」ボタンをクリックします。

挿入→おすすめグラフを選択する

「おすすめグラフ」をクリックすると、グラフを選ぶダイアログがあらわれます。「集合縦棒 - 第2軸の折れ線」を選んでください。ここまでを同じ手順で進んでいれば、一番最初に表示されているはずです。

集合縦棒 - 第2軸の折れ線を選ぶ

このようなグラフが表示されます。ここまでで基本のグラフが作成できました。

excel2013の基本のグラフを作成する

これでは、まだパレート図とは言えないので細かい部分を修正していきます。

3.パレート図に仕上げる

ここまででも、棒グラフと折れ線グラフの複合グラフはできましたがパレート図にするには少し工夫が必要です。

グラフの太さを変える

最初に棒グラフの太さをパレート図で使うヒストグラムに変更します。

棒グラフのどれかを右クリックするとメニューができるので[データ系列の書式設定]を選択します。右側に「データ要素の書式設定」ダイアログが表示されるので、グラフのようなアイコンをクリックし、下のリストから[系列のオプション]を選択し、[系列の重なり][要素の間隔]を両方「0」にします。

データ系列の書式設定で値を変更する

するとこのようなグラフになります。

ヒストグラムに変更したグラフ

軸の最大値を変える

次に累積比率の横軸を100%で最大にしたいので修正します。現在は120%が最大になっているはずです。

累積比率の横軸を100%までにする

右横にあるパーセンテージの目盛を右クリックして「軸の書式設定」を表示します。軸のオプションから、最大値を1.0に変更します。

軸の書式設定から最大値を1.0にする

これで累計比率の最大値が100%になりました。

累計比率の最大値を100%にしたグラフ

左の売上金額の最大値も変更しましょう。

同じように売上金額の軸を右クリックして「軸の書式設定」を表示します。最大値を合計金額の「18,740」に、最小値を「0」にします。

最大値と最小値を変更する

棒グラフが縮こまった感じになりますが問題ありません。

売上金額の最大値と最小値を変更したグラフ

折れ線グラフの開始位置を正しくする

次に現状の折れ線グラフの開始点がずれているので、パレート図にするために開始点を0から始めるように修正します。

折れ線グラフを0から始めるようにしたい

どこでもいいのでグラフを選択した状態にすると、グラフの右上に[+]ボタンがでてきます。

グラグを選択すると出るボタン

これをクリックすると、グラフの要素を追加したり、削除したりできるメニューが表示されます。

メニューが表示される

グラフ要素メニューの「軸」にマウスを当てる(クリックはしない)と、右に矢印がでてきます。
※矢印だけをしっかりクリックしないと、今まであった軸が消えてしまいます。その場合は、もう一度「軸」をクリックすれば元に戻ります。

メニューから矢印をクリックする

その矢印をクリックすると、第1横軸、第1縦軸、第2横軸、第2縦軸のチェックボックスが出て、第2横軸のチェックがはずれています。それをチェックしてください。すでにチェックされている場合はそのままで大丈夫です。

第2横軸のチェックを入れる

グラフに第2横軸が追加されましたので、それをクリックして選択状態にします。

第2軸が追加される

そして右クリックから「軸の書式設定」を表示してください。

軸のオプション(グラフのアイコン)を選択し、詳細設定の中にある「軸位置」の「目盛」にチェックを入れます。

軸の書式設定で目盛にチェックを入れる

これで折れ線グラフが、原点から開始するようになりました。

0から始めるようになった複合グラフ

折れ線グラフは移動できましたは、第2横軸がちょっと邪魔です。消しておきましょう。

第2軸を消す

第2軸を消すための手順は、

第2軸を選択
軸の書式設定
ラベル
ラベルの位置→「なし」に変更

で目盛のラベルを消すことができます。

軸の書式設定でラベルを「なし」にする

[Delete]で軸ラベルを消してしまうと、せっかく0から開始にした折れ線グラフが元にもどってしまうので注意して下さい。

棒グラフの位置を正しくする

次に折れ線グラフに続いて棒グラフの開始位置も原点から開始するようにします。

棒グラフがずれている原因は、0%を表示するためにつくった行に空白部分があるためです。

表の空白部分の説明

この空白を選択からはずします。

表の空白部分をはずすためには、まず棒グラフのどこかをクリックしてください。

すると、棒グラフに関係している表の部分が選択状態になります。

グラフを選択すると表の選択が表示される

ここから空白をはずします。

選択範囲の四隅の「■」をドラッグし、選択範囲から空白のセルを除外してください。

表の■を下にドラッグする

空白を選択からはずすと以下のようになります。

空白の選択部分を除外した表

※ 項目の値が項目のタイトルと重なってしまう場合は、「■」の少し下をドラッグするとうまくいきます。

グラフは左にずれて原点から開始するようになりました。

これでパレート図が完成しました。

後は、色を変えたり、枠線をつけたりしてグラフのデザインを修正してください。

完成したパレート図

パレート図のサンプルファイルダウンロード

ここまで作成したパレート図のサンプルファイルをダウンロードできます。1から作成するのが難しい、簡単に作成したいという方はタイトルや項目を変更することでオリジナルのパレート図を作る事が可能です。

テンプレートとして活用してください。

※excel2013で作成していますが、他のバージョンでも使うことができます。

まとめ

エクセル2013でパレート図を作成する方法を紹介しました。

複合グラフをつくるまでは簡単なエクセルの操作ですが、パレート図に仕上げるまでが慣れていないとちょっと面倒です。

しかし2,3回作ってみると、その後は簡単にできるようになるでしょう。

サンプルファイルもダウンロードできるようにしたので、値や項目だけ入れ替えてやってみてもいいでしょう。

Twitterでフォローしよう

おすすめの記事