どうも、Excelの森のタツリク（@Tatsuriku_ch）です。
『パレート図』というグラフは、各項目が全体でどれくらいの割合を示しているのかが一目で分かるので、分析や改善に役立つ便利なグラフです。
しかし、このグラフをエクセルで作成するのに苦戦する人は多いと思います。
何を隠そう、私自身もその苦戦した中の一人です。
今回は、皆さんが簡単に『パレート図』を作成出来るように手順を纏めましたので解説します。
パレート図の作成方法は２種類
パレート図の作成方法は２種類あります。
それぞれ出来ることが異なりますので、下の２つのパレート図を見て、どちらを作成したいか検討してから、記事を読み進めてください。
パターン① 正統法
上のパレート図は棒グラフと折れ線グラフの２つを組み合わせて作成します。
下の①～④を調整出来る点がメリットですので、私はこちらの作成方法をお勧めします。
- 折れ線グラフの開始位置をグラフ左下の『0』の位置に調整
- 累積比率の凡例を追加
- 折れ線グラフのマーカーの調整
- 自由に並び順を調整出来る
こちらのパレート図を作成したい方は、下の見出しの「ステップ１：パレート図の元データを準備」から「ステップ６：パレート図を完成」までを読み進めて下さい。
パターン➁ 簡易版
上のパレート図は「ヒストグラフ」の中の「パレート図」を挿入して作成します。
作成が簡単な一方、以下の３点を調整できないというデメリットがあります。
- 折れ線グラフの開始位置をグラフ左下の『0』の位置に調整出来ない
- 累積比率の凡例を自動で追加出来ない(図形としての追加は可能)
- 折れ線グラフにマーカーを追加出来ない
- 「その他」を一番右の位置に移動出来ない
こちらの簡易的なパレート図を作成したい方は、「パレート図の簡単な作成方法」という見出しから読み始めて下さい。
ステップ１：パレート図の元データを準備
それでは、上の見出しで紹介した『①正統法のパレート図』を作成する方法を解説します。
ステップ①からステップ⑥までの手順で紹介しますが、ステップ③の棒グラフの開始位置の調整に少し苦戦するかもしれません。
しかし、落ち着いてステップ①から手順通りに進めれば、誰でも完成することが出来ますので、頑張ってトライしてみてください！
それでは、どうぞ！
手順１．パレート図に使うデータを降順で並べる
パレート図に使うデータを降順で並べます。ただし、「その他」はパレート図の一番右に配置したいので、数値が大きくても一番下に並べます。
手順２．SUM関数を挿入
合計を表示するため、最下行にSUM関数「=SUM(C3:C8)」を挿入します。
手順３．累積比率を算出
表の右に「累積比率」列を作成します。累計比率を算出するために、一番上のセルに、「C3」セルを合計の「C9」セルで割り算する計算式を入力します。
次のセルにも使えるように、合計の「C9」セルは【F4】キーを２回押して「$」を付け絶対参照にします。
=C3/C9 ⇒ =C3/C$9
手順４．累積比率を算出
続いて２行目の累積比率を計算します。２行目は、１行目の比率を２行目の比率に足す必要があるので、以下の数式を挿入します。手順３の数式をコピーしてから「+D3」を加えると簡単に作成出来ます。
=C4/C$9+D3
手順５．累積比率の完成
手順４のセルを下のセルにもコピーして、すべてのセルの累積比率を算出します。最下行がぴったり「1」になっていれば成功です。
手順６．累積比率を「％」で表示
累積比率を「％」で表示するために、「累積比率」列を選択して、右クリックメニューの「セルの書式設定」をクリックします。以降の手順に影響するため、見出しが入力されたD2セルも選択してください。
手順７．「パーセンテージ」を選択
「表示形式」タブの中から「パーセンテージ」を選択し、「OK」で画面を閉じます。
手順８．パレート図の元データの完成
累積比率がパーセントで表示されます。以上でステップ１「パレート図の元データの完成」です。
ステップ２：パレート図の元グラフを挿入
元データが完成したら、パレート図の元となるグラフを挿入します。
グラフは「棒グラフ」と「折れ線グラフ」を２軸を使って作成していきます。
手順１．「おすすめグラフ」を選択
元データを見出し行も含めて選択して、「挿入」タブ⇒「おすすめグラフ」を選択します。
手順２．挿入するグラフを決定
「すべてのグラフ」タブの「組み合わせ」というグラフを選択します。次に画面右上の「ユーザー設定の組み合わせ」グラフを選択し、以下の設定を行い、「OK」で画面を閉じます。
「売上高」列は、グラフの種類を「集合縦棒」を選択
「累計比率」列は、グラフの種類を「折れ線」を選択
「累計比率」列の第２軸のチェックボックスに✓を入れます。
手順３．パレート図の元グラフの挿入が完成
下の図のように、パレート図に似たグラフを挿入することが出来れば、ステップ２「パレート図の元グラフを挿入する」の完成です。
ステップ３：パレート図の棒グラフのサイズを調整
グラフを挿入したら、次は「棒グラフ」の横幅のサイズを調整します。
手順１．棒グラフをダブルクリック
棒グラフをダブルクリックして、「データ系列の書式設定」画面を表示します。
手順２．「系列の重なり」と「要素の間隔」を調整
「データ系列の書式設定」画面⇒「系列のオプション」から、主軸の「系列の重なり」と「要素の間隔」の数値をともに「0」に設定します。
手順３．棒グラフの枠線を白色に調整
この作業は見栄えを良くするための手順のため、作業は任意です。
「データ系列の書式設定」画面から「塗りつぶしと線」⇒「枠線」⇒「線(単色)」から白色を選択します。
以上で、ステップ３「パレート図の棒グラフのサイズを調整する」の完成です。
ステップ４：パレート図の折れ線グラフの開始位置を調整
棒グラフの横幅を調整し終えたら、次は「折れ線グラフ」の開始位置をグラフの原点に調整する作業です。
この作業が、「パレート図」が上手に作成出来ない人の一番のお悩みポイントだと思います。
ただ、手順毎に図解を交えて解説しますので、順番どおりに落ち着いて作業すれば大丈夫です。
パレート図って毎月作成するわけじゃないからすぐ作り方忘れちゃうよね。
手順１．折れ線グラフを選択
折れ線グラフをクリックして選択します。
手順２．「第２横軸」を設定
「グラフデザイン」タブから「グラフ要素を追加」⇒「軸」⇒「第２横軸」を選択します。
手順３．「第２横軸」をダブルクリック
グラフの上側に「第２横軸」(※)が設定されるので、ダブルクリックします。(※この第２横軸は最終的に非表示にします)
手順４．「軸のオプション」の軸位置を調整
「データ系列の書式設定」画面⇒「軸のオプション」から、「軸位置」の「目盛」にチェックを入れます。そうすると、横軸の開始位置が縦軸の位置に移動します。
手順５．第２横軸の目盛を非表示
手順４で「第２横軸」の役割は終わりましたので、「第２横軸」の目盛を非表示にします。
「データ系列の書式設定」画面⇒「軸のオプション」から、「目盛」⇒「目盛の種類」の「なし」を選択します。
手順６．第２横軸のラベルを非表示
手順４で「第２横軸」の役割は終わりましたので、「第２横軸」のラベルを非表示にします。
「データ系列の書式設定」画面⇒「軸のオプション」から、「ラベル」⇒「ラベルの位置」の「なし」を選択します。
手順１～手順６までの作業で以下のようなパレート図になっていれば作業は順調です。引き続き、第２軸を原点に手順7以降で真下移動します。
手順７．折れ線グラフの参照しているセル範囲を表示
折れ線グラフをクリックして、参照しているセル範囲を表示させます。下の図では、青色の枠線のセル範囲(D3～D8セル)が折れ線グラフに使用しているデータ範囲です。
手順８．ドラッグして選択範囲を１つ上に移動
折れ線のセル範囲の外枠にカーソルを合わせると、ポインタの表示が十字矢印に切り替わります。ポインターが切り替わった状態で１セル分、上にドラッグして、参照範囲を一つ上のセルに移動してください。
参照先：「D3：D8」⇒「D2：D7」
手順９．ドラッグして選択範囲を１セル分拡張
折れ線のセル範囲の外枠の左下にカーソルを合わせると、ポインタの表示が矢印の形に切り替わります。ポインターが切り替わった状態で１セル分、下にドラッグして、参照範囲を一セル分拡張しててください。
参照先：「D2：D7」⇒「D2：D8」
手順10．折れ線グラフの調整が完了
手順１～手順9の作業により、折れ線グラフの開始位置をグラフの原点に移動することが出来ていれば完了です。
ステップ５：パレート図の目盛の調整
パレート図のおおよその調整は完了しました。続いて、「主軸」と「第２軸」の目盛を調整します。
手順１．第２軸をダブルクリック
第２軸をダブルクリックして、「データ系列の書式設定」画面を表示します。
手順２．第２軸の最大値を「1.0」に調整
「データ系列の書式設定」画面⇒「軸のオプション」から、「境界線」の「最大値」を「1.0」に設定します。
手順３．主軸をダブルクリック
主軸をダブルクリックして、「データ系列の書式設定」画面を表示します。
手順４．主軸の最大値を調整
「データ系列の書式設定」画面⇒「軸のオプション」から、「境界線」の「最大値」を変更します。
「最大値」の値は、売上高の「合計値」に一致するように調整してください。
この主軸の最大値を売上高の合計値と一致させる目的は、以下の２点です。
①主軸と第２軸の最大値の位置が一致します。
➁折れ線グラフの第１プロットの位置と棒グラフの右上の位置がぴったり一致します。
手順５．主軸と第２軸の目盛が完成
下の図のように目盛が調整出来ていれば、目盛の調整が完了です。
ステップ６：パレート図を完成
ここまでくればパレート図は完成しようなものです。
後は必要に応じて、通常のグラフの作成と同じように以下の項目を調整しましょう。
残りの調整項目( 作業は任意)
- 表タイトル
- 主軸の軸タイトル
- 主軸の単位
- 折れ線グラフのマーカー
- 折れ線グラフのラベル表示
- 棒グラフのラベル表示
これらの項目の作業手順が分からないという方は、下の棒グラフの作成方法を纏めた記事で紹介していますので参考にしてみてください。
パレート図の簡単な作成方法
一番上の見出しで紹介した『➁簡易版のパレート図』を作成する方法を解説します。
それでは、どうぞ！
手順１．パレート図に使う元データを準備
簡易版のパレート図に使うデータを表に纏めます。グラフは自動で大きいものから順に並び替えられますので、昇順や降順での並べ替えは不要です。
手順２．「おすすめグラフ」を選択
元データを見出し行も含めて選択して、「挿入」タブ⇒「おすすめグラフ」を選択します。
手順３．パレート図を選択
「すべてのグラフ」タブの「ヒストグラム」というグラフを選択します。次に画面右上の「パレート図」を選択し、「OK」で画面を閉じます。
手順４．主軸をダブルクリック
主軸をダブルクリックして、「データ系列の書式設定」画面を表示します。
手順４．主軸の最大値を調整
「データ系列の書式設定」画面⇒「軸のオプション」から、「境界線」の「最大値」を変更します。
「最大値」の値は、売上高の「合計値」に一致するように調整してください。
手順５．必要に応じてグラフを調整
ここまでくればパレート図は完成しようなものです。
後は必要に応じて、通常のグラフの作成と同じように以下の項目を調整しましょう。
残りの調整項目( 作業は任意)
- 表タイトル
- 主軸の軸タイトル
- 主軸の単位
- 棒グラフのラベル表示
これらの項目の作業手順が分からないという方は、下の棒グラフの作成方法を纏めた記事で紹介していますので参考にしてみてください。