Excel パレート図

デザイン五輪書

Excel2013でパレート図を作成する方法

読了までの目安時間:約 14分

 

にほんブログ村 イラストブログ Photoshopへコンピュータグラフィックス ブログランキングへFC2ブログランキングブログランキングならblogram i2iブログランキング

Excel2013でパレート図を作成する方法

Excel2013でパレート図を作成

パレート図とは

パレート図とは、降順に並べられた棒グラフと、その累積構成比率が折れ線グラフで並べられた、複合的なグラフの事です。
棒グラフは右肩下がり、折れ線グラフは右肩上がりになります。
そして、折れ線グラフは0%から始まり、最終的に100%になります。

パレート図の見本

パレート図は商品の品質管理などに使われ、改善点の優先度をあぶり出す目的で使用されます。

▲もくじへ戻る

Excel2013を活用しよう!

このパレート図を、Excel2013を使って作成してみましょう。

グラフが二種類混ざっているので、一見すると複雑そうに思えるかもしれませんが、Excel2013を使えば、半自動的に複合グラフを作成してくれます。
私達が行う事は、グラフの元となる表を作る事と、Excelによって作られた複合グラフの修正を行う事だけです。

ポイントを押さえながら順を追って作業を行えば、大して難しい事もありません。
ですから、ぜひ、挑戦してみて下さい。

▲もくじへ戻る

パレート図の元となる表を作る

必要な情報を降順に並べる

まずは、必要な情報を集めます。
クレームの種類と、クレーム件数をまとめて表にします。

クレームを集める

2行目が空欄なのは、後ほど必要になるからです。
1行分空けておいて下さい。

最終的には降順に並べた棒グラフを表示しますので、「件数」の値は降順に並べておきましょう。
「件数」の列の中にあるセルを一つ選択して下さい。

件数の列内にあるセルを選択

「リボン」の「ホーム」タブをクリックします。

ホームタブ

「編集」グループの中にある「並べ替えとフィルター」をクリックします。
「並べ替えとフィルター」の詳細設定画面が表示されますので、「降順」をクリックします。

降順

「問題点」が、「件数」の多い順に並び替えられました。

件数が降順に並べ替えられた

▲もくじへ戻る

累積数を表示する

続いて、「累積数」の列を右側に書き加えます。

累積数の列を追加

累積数とは、行が移動する毎に、値が追加されて行く方式の数え方です。
「累積数」列の最終行は、全体の合計数になります。

では、「累積数」列の1行目に「0」と記述して下さい。

累積数の1行目は0

「累積数」列の二行目に「=」と記述し、先ほど記述した「0」をクリックします。

累積数の1行目をクリック

そして、「+」を書き加え、「件数」列の二行目をクリックします。

件数列の2行目をクリック

そして、キーボードの「Enter」を入力します。
累積数列の二行目が計算されます。

累積数列の2行目が記述された

続けて、「累積数」列の二行目を選択し直し、フィルハンドルをダブルクリックします。

フィルハンドルをダブルクリック

「累積数」列の二行目で行った計算が、最終行にまで適用されます。

累積数列が埋まった

▲もくじへ戻る

累積比率を表示する

続いて、「累積比率」の列を右側に書き加えます。

累積比率

「累積比率」とは、その行の「累積数」の全体に対する比率を示した値です。
「累積比率」列の最終行は「100%」になります。

では、「累積比率」列の1行目に「0」と記述して下さい。

累積比率の1行目は0

「累積比率」列の2行目に「=」と記述し、「累積数」列の2行目のセルをクリックします。

累積数の2行目をクリック

続けて、「/」と書き加え、「累積数」列の最終行のセルをクリックします。

累積数列の最終行をクリック

そのまま、キーボードの「F4」をクリックし、「累積数」列の最終行のセルを絶対参照に設定します。

累積数列の最終行を絶対参照

「累積数」列の最終行のセル部分の列番号と行番号の部分に「$」マークが表示されます。
絶対参照の設定をしておく事で、「累積比率」列は全て、「累積数」列の最終行のセルを参照します。

キーボードの「Enter」を入力して下さい。
「累積比率」列の二行目が計算されました。

累積比率列の2行目が計算された

「累積比率」列の結果は「%」で表示します。
ですから、「表示形式」を「パーセンテージ」に変更しておきましょう。

「累積比率」列の一行目と二行目をドラッグして選択します。

累積比率列の1行目と2行目を選択

「数値」グループの中にある「%」をクリックします。

%をクリック

「累積比率」列の一行目と二行目が「パーセンテージ」で表示されるようになりました。

累積比率列がパーセンテージに

続いて、「累積比率」列の二行目のみを選択します。
そして、フィルハンドルをダブルクリックして下さい。

フィルハンドルをダブルクリック

「累積比率」列の二行目で行った計算が、最終行にまで適用されます。

累積比率列が埋まった

これで、パレート図を作成する為に必要な表は完成しました。

▲もくじへ戻る

パレート図を作成する

複合グラフを作成する

作成した表を基に、パレート図を作成します。
まずは、パレート図の元となる複合グラフを作成しましょう。

「問題点」列と「件数」列をドラッグ選択します。

問題点列と件数列をドラッグ選択

更に、キーボードの「Ctrl」を入力しながら、「累積比率」列をドラッグ選択します。

累積比率列をドラッグ選択

続けて、「リボン」の中の「挿入」タブをクリックします。

挿入タブ

「グラフ」グループの中にある「おすすめグラフ」をクリックします。

おすすめグラフ

「グラフの挿入」ダイアログが表示されます。
集合縦棒-第2軸の折れ線」が選択されているのを確認し、「OK」をクリックします。

グラフの挿入

複合グラフが挿入されました。

複合グラフが挿入された

▲もくじへ戻る

グラフタイトルを書き換える

挿入された複合グラフは、既にパレート図らしく見えます。
ですが、まだ、細かい修正部分がいくつかありますので、一つ一つ変更して行きましょう。

まずは、「グラフタイトル」と書かれている部分を修正します。
グラフの中の「グラフタイトル」の文字をクリックします。

文字の周りに枠が表示されます。

グラフタイトルをクリック

この状態になったら文字を入力し、グラフの外をクリックします。
すると、「グラフタイトル」が書き換わります。

グラフタイトルが切り替わる

▲もくじへ戻る

折れ線グラフの最大値を100%に変更する

現段階では、折れ線グラフの最大値の表記が「120%」になっています。

折れ線グラフの最大値

ここを「100%」に変更しましょう。
折れ線グラフの値が表記されている部分をクリックします。

折れ線グラフの値をクリック

「リボン」の中に「グラフツール」の「デザイン」タブと「書式」タブが追加されます。
「グラフツール」の「書式」タブをクリックして下さい。

グラフツールの書式タブ

「現在の選択範囲」グループの中にある「選択対象の書式設定」をクリックします。

選択対象の書式設定

シート右側に、「軸の書式設定」メニューが表示されます。
「軸のオプション」の中にある「境界値」の「最大値」が「1.2」になっていますので、ここを「1」に書き換えます。

境界線の最大値

折れ線グラフの最大値が「100%」に切り替わりました。

折れ線グラフの最大値が100%に

▲もくじへ戻る

棒グラフのデザインを変更する

初期状態の棒グラフは細いですから、棒グラフの太さを変更しましょう。
棒グラフをクリックして下さい。

棒グラフをクリック

シート右側に表示されている「データ系列の書式設定」の中から、「系列のオプション」をクリックします。

系列のオプション

「要素の間隔」の数値を「0」に変更します。

要素の間隔

棒グラフの太さが変更になりました。

棒グラフの太さが変更に

棒グラフを太くする事はできましたが、グラフ同士の隙間が埋まった結果、棒グラフに見えにくくなってしまいました。
ですから、棒グラフに枠線を付け、それぞれの境界線が分かるようにしましょう。

「データ系列の書式設定」の中から、「塗りつぶしと線」をクリックします。

塗りつぶしと線

「枠線」をクリックして、枠線の詳細メニューを展開させます。
そして、「線(単色)」をクリックします。

枠線に単色の線を

初期状態の枠線の色は、棒グラフの色と同じになっています。
これでは枠線が見えませんので、枠線の色を変更しましょう。

「輪郭の色」をクリックし、表示された詳細設定の中から、任意の色を選択して下さい。

輪郭の色を選択

棒グラフに枠線が設定され、棒グラフらしくなりました。

棒グラフに枠線が付いた

▲もくじへ戻る

折れ線グラフの表示位置をずらす

現段階では、折れ線グラフの原点が、複合グラフの原点よりも右側にずれている状態です。
この複合グラフをパレート図にする為には、折れ線グラフの原点が複合グラフの原点と同じ位置から始まっている必要があります。

折れ線グラフの原点

つまり、折れ線グラフの表示位置を左側に移動する必要がある訳です。

「グラフエリア」をクリックし、複合グラフ全体を選択します。

グラフエリアをクリック

「リボン」の中に「グラフツール」の「デザイン」タブと「書式」タブが追加されます。
「グラフツール」の「書式」タブをクリックして下さい。

グラフツールの書式タブ

「現在の選択範囲」の中にある「グラフ要素」をクリックします。

グラフ要素

すると、「グラフ要素」が一覧表示されます。
この中から、折れ線グラフの横軸の設定を行う事のできる要素を選びます。

グラフ要素一覧

一覧を確認してみると、縦軸は「縦(値)軸」と「第2軸 縦(値)軸」がある一方で、
横軸は棒グラフのものと折れ線グラフのものが一まとめにされ、「横(項目)軸」と表記されています。
つまり、折れ線グラフの位置を変更しようとすると、棒グラフまで一緒に移動してしまうと言う事になります。

そこで、縦軸のように、横軸にも第2軸を設定する必要があります。
この発想が、Excelでパレート図を作成する上で、最も重要なポイントとなります。

「リボン」の中にある「グラフツール」の「デザイン」タブを選択します。

グラフツールのデザインタブ

「グラフのレイアウト」グループの中にある「グラフ要素を追加▼」をクリックします。
詳細メニューが展開されますので、「軸」にマウスポインタを乗せます。
すると、更に詳細メニューが展開されますので、「第2横軸」を選択します。

第2横軸

折れ線グラフ用の第2横軸が表示されました。

第2横軸が表示された

表示された「第2横軸」をクリックします。

第2横軸をクリック

「軸の書式設定」の中にある「軸のオプション」をクリックします。

軸のオプション

「軸のオプション」をクリックし、展開された詳細設定の中にある「軸位置」の「目盛」にチェックを入れます。

軸位置を目盛に

折れ線グラフの原点が複合グラフの原点に移動しました。

折れ線グラフが原点から始まる

▲もくじへ戻る

第2横軸のラベルと目盛を非表示に

折れ線グラフの表示位置をずらす事ができましたが、第2横軸を表示した為、余計なラベルや目盛まで表示されてしまいました。

第2横軸のラベル

ですので、第2横軸のラベルと目盛を非表示に設定しましょう。

「目盛」をクリックすると詳細設定が展開されますので、「目盛の種類」をクリックし、「なし」を選択します。

目盛の種類をなしに

引き続き、「ラベル」をクリックして詳細設定を展開します。
「ラベルの位置」をクリックし、その中の「なし」を選択します。

ラベルの位置をなしに

第2横軸のラベルと目盛が非表示になりました。

第2横軸のラベルと目盛が非表示に

▲もくじへ戻る

棒グラフの表示位置をずらす

最後に、棒グラフの表示位置を複合グラフの原点に合わせます。

棒グラフを確認すると、1項目分、棒グラフが右にずれています。

棒グラフの位置がずれている

これは、複合グラフを作成する際、表の空欄部分まで選択した為です。

空欄部分も選択

この空欄は、「おすすめグラフ」機能を使って一括で複合グラフを作成する為に必要でした。
ですが、現段階において、棒グラフを右にずらしてしまっている原因になっていますので、棒グラフの選択範囲から、この空白のセルを省いてしまいましょう。

棒グラフをクリックして下さい。

棒グラフを選択

すると、複合グラフを作成する元となっている表の中に、棒グラフを構成する選択範囲が表示されます。

棒グラフの選択範囲

選択範囲の四隅の「■」をドラッグし、棒グラフを構成する選択範囲の中から、空白のセルを除外します。

棒グラフの選択範囲から空白を取り除く

棒グラフの表示位置が修正され、パレート図が完成しました。

パレート図

▲もくじへ戻る

まとめ

Excel2013で作業が楽に!

今回は、Excel2013でパレート図を作成する方法についてご紹介しました。
内容は盛り沢山でしたが、ポイントさえきちんと踏まえていれば、難易度はそれほどまで高くなかったと思います。

従来のExcelだと、一度、「集合縦棒グラフ」を作成し、その中の一つを「折れ線グラフ」に変更する必要がありました。
Excel2013の場合、「おすすめグラフ」機能を使って一括で複合グラフを作成する事ができるので、とても楽です。
ぜひ、あなたも活用しましょう。

今回の記事が、あなたの作業効率化に繋がれば幸いです。

にほんブログ村 イラストブログ Photoshopへコンピュータグラフィックス ブログランキングへFC2ブログランキングブログランキングならblogram i2iブログランキング

タグ :

ソフトウェア Microsoft Office   コメント:0

この記事に関連する記事一覧

TOPへ戻る