【エクセル】パレート図を正確に作成する方法。累積比率を折れ線グラフで表す

どうも、Excelの森のタツリク（@Tatsuriku_ch）です。

Microsoft Excel(エクセル)の『パレート図』というグラフは、各項目が全体でどれくらいの割合を示しているのかが一目で分かるので、分析や改善に役立つ便利なグラフです。

ただ、このグラフをエクセルで作成するのに苦戦する人は多いと思います。

何を隠そう、私自身もその苦戦した中の一人です。

今回は、『パレート図』を簡単に作成する方法を手順に沿いながら解説します。

パレート図とは？

パレート図とは「棒グラフ」と「折れ線グラフ」を組み合わせたグラフのことです。

下のグラフのように、発生した事象の中で大きな割合を占めているものを特定するために用いられます。

品質管理のために用いられることが多く「QC7つ道具」の１つとも呼ばれます。改善活動のために作成されることが多いグラフです。

Excelのパレート図の作り方は２種類

Excelのパレート図の作り方は２種類あります。

１つ目は「棒グラフと折れ線グラフを使用する方法」で、２つは、「ヒストグラムのパレート図を使用する方法」です。

それぞれグラフで表示できる内容が異なり、挿入するグラフの種類も異なるので、下の２つのパレート図を見て、どちらを作成したいか検討してから、記事を読み進めてください。

パレート図その１：棒グラフと折れ線グラフを使用

下のパレート図は棒グラフと折れ線グラフの２つを組み合わせて作成します。

このパレート図は、下の①～④を調整出来る点が特徴です。細かくグラフの見栄えを調整する場合は、こちらの作成方法をお勧めします。

• 折れ線グラフの開始位置をグラフ左下の『0』の位置に調整
• 累積比率の凡例を追加
• 折れ線グラフのマーカーの調整
• 自由に並び順を調整出来る

こちらのパレート図を作成したい方は、下の「パレート図を正確に作成する方法(棒グラフと折れ線グラフ)」という見出しから読み進めてください。

パレート図その２：ヒストグラムを使用

下のパレート図は「ヒストグラム」というグラフカテゴリの中の「パレート図」を挿入して作成します。

作成が簡単というメリットがありますが、以下の４点を調整できないというデメリットがあります。細かく体裁を整えたい方にはあまりおすすめできません。

• 折れ線グラフの開始位置をグラフ左下の『0』の位置に調整出来ない
• 累積比率の凡例を自動で追加出来ない(図形としての追加は可能)
• 折れ線グラフにマーカーを追加出来ない
• 「その他」を一番右の位置に移動出来ない

こちらの簡易的なパレート図を作成したい方は、「パレート図の簡単な作成方法(ヒストグラムを使用)」という見出しから読み始めて下さい。

パレート図を正確に作成する方法(棒グラフと横棒グラフ)

それでは、上の見出しで紹介した『棒グラフと横棒グラフを使ったパレート図』を作成する方法を解説します。

ステップ①からステップ⑥までの手順がありますが、ステップ③の棒グラフの開始位置の調整に少し苦戦するかもしれません。

しかし、落ち着いてステップ①から手順通りに進めれば、誰でも完成することが出来ますので、頑張ってトライしてみてください。

それでは、どうぞ！

ステップ１：パレート図の元データを準備

手順１．パレート図に使うデータを降順で並べる
パレート図に使うデータを入力して降順で並べます。ただし、「その他」の項目はパレート図の一番右に配置したいのであれば、数値が大きくても一番下に並べる必要があります。

手順２．SUM関数を挿入
合計を表示するため、最下行にSUM関数「=SUM(C3:C8)」を挿入します。

手順３．累積比率を算出
表の右に「累積比率」列を作成します。一番上のセルに、「C3」セルを合計の「C9」セルで割り算する数式を入力します。次のセルにも使えるように、合計の「C9」セルは【F4】キーを２回押して「$」を付け絶対参照にします。

=C3/C9　⇒　=C3/C$9

手順４．累積比率を算出
続いて２行目の累積比率を計算します。２行目は、１行目の比率を２行目の比率に足す必要があるので、以下の数式を挿入します。手順３の数式をコピーしてから「+D3」を加えると簡単に作成出来ます。

=C4/C$9+D3

手順５．累積比率の完成
手順４のセルを下のセルにもコピぺして、すべてのセルの累積比率を算出します。最下行の値が「１」になることを確認してください。

手順６．累積比率を「％」で表示
累積比率を「％」で表示するために、「累積比率」列を選択して、右クリックメニューの「セルの書式設定」をクリックします。以降の手順に影響するため、見出しが入力されたD2セルも選択してください。

手順７．「パーセンテージ」を選択
「表示形式」タブの中から「パーセンテージ」を選択し、「OK」で画面を閉じます。

手順８．パレート図の元データが完成
累積比率がパーセントで表示されます。以上でステップ１「元データの作成」は完了です。

ステップ２：パレート図の元グラフを挿入

元データが完成したら、パレート図の元となるグラフを挿入します。

グラフは主軸「棒グラフ」と第２軸「折れ線グラフ」を使って作成していきます。

手順１．「おすすめグラフ」を選択
元データを見出し行も含めて選択して、「挿入」タブ⇒「おすすめグラフ」を選択します。

手順２．挿入するグラフを決定
「すべてのグラフ」タブの「組み合わせ」というグラフを選択します(図③)。次に画面右上の「ユーザー設定の組み合わせ」グラフを選択し(図④)、以下の図⑤～図⑦の設定を行い、「OK」で画面を閉じます。

• 「売上高」系列は、グラフの種類を「集合縦棒」を選択図⑤の上段)
• 「累計比率」系列は、グラフの種類を「折れ線」を選択(図⑤の下段)
• 「累計比率」系列の第２軸にチェックを入れる(図⑥)

手順３．パレート図の元グラフの挿入が完成
下の図のように、パレート図の元となる組み合わせグラフを挿入することが出来れば、ステップ２「パレート図の元グラフを挿入する」は完成です。

ステップ３：パレート図の棒グラフのサイズを調整

組み合わせグラフを挿入したら、次は「棒グラフ」の横幅のサイズを調整します。

手順１．棒グラフをダブルクリック
棒グラフをダブルクリックして、「データ系列の書式設定」画面を表示します。

手順２．「系列の重なり」と「要素の間隔」を調整
「データ系列の書式設定」画面⇒「系列のオプション」から、主軸の「系列の重なり」と「要素の間隔」の数値をともに「0」に設定します。

手順３．棒グラフの枠線を白色に調整
この作業は見栄えを良くするための手順のため、作業は任意です。

「データ系列の書式設定」画面から「塗りつぶしと線」⇒「枠線」⇒「線(単色)」から白色を選択して、それぞれの棒グラフの枠線を白色に変更します。

以上で、ステップ３「パレート図の棒グラフのサイズを調整する」の完成です。

ステップ４：パレート図の折れ線グラフの開始位置を調整

棒グラフの横幅を調整し終えたら、次は「折れ線グラフ」の開始位置をグラフの原点に調整する作業です。

この作業が、「パレート図」が上手に作成出来ない人の一番のお悩みポイントだと思います。

ただ、手順毎に図解を交えて解説しますので、順番どおりに落ち着いて作業すれば大丈夫です。

作業忘れそうなら、お気に入りに登録したり印刷しておいてね。

私

手順１．折れ線グラフを選択
折れ線グラフをクリックして選択します。

手順２．「第２横軸」を設定
「グラフデザイン」タブから「グラフ要素を追加」⇒「軸」⇒「第２横軸」を選択します。

手順３．「第２横軸」をダブルクリック
グラフの上側に「第２横軸」(※)が設定されるので、ダブルクリックします。(※この第２横軸は最終的に非表示にします)

手順４．「軸のオプション」の軸位置を調整
「データ系列の書式設定」画面⇒「軸のオプション」から、「軸位置」の「目盛」にチェックを入れます。そうすると、横軸の開始位置が縦軸の位置に移動します。

手順５．第２横軸の目盛を非表示
手順４で「第２横軸」の役割は終わりましたので、「第２横軸」の目盛を非表示にします。「データ系列の書式設定」画面⇒「軸のオプション」から、「目盛」⇒「目盛の種類」の「なし」を選択します。

手順６．第２横軸のラベルを非表示
「第２横軸」のラベルも非表示にします。「データ系列の書式設定」画面⇒「軸のオプション」から、「ラベル」⇒「ラベルの位置」の「なし」を選択します。

手順１～手順６までの作業で以下のようなパレート図になっていれば作業は順調です。ここからは、棒グラフの原点を真下に移動する作業をおこなっていきます。

手順７．折れ線グラフの参照しているセル範囲を表示
折れ線グラフをクリックして、参照しているセル範囲を表示させます。下の図では、青色の枠線のセル範囲(D3～D8セル)が折れ線グラフに使用しているデータ範囲ということが確認できます。

手順８．ドラッグして選択範囲を１つ上に移動
折れ線のセル範囲の外枠にカーソルを合わせると、ポインタの表示が十字矢印に切り替わります。ポインターが切り替わった状態で１セル分、上にドラッグして、参照範囲を一つ上のセルに移動してください。

参照先：「D3：D8」⇒「D2：D7」

手順９．ドラッグして選択範囲を１セル分広げる
折れ線のセル範囲の外枠の左下にカーソルを合わせると、ポインタの表示が矢印の形に切り替わります。ポインターが切り替わった状態で１セル分、下にドラッグして、参照範囲を一セル分広げてください。

参照先：「D2：D7」⇒「D2：D8」

手順10．折れ線グラフの調整が完了
手順１～手順9の作業により、折れ線グラフの開始位置をグラフの原点に移動することが出来ていれば完了です。

ステップ５：パレート図の目盛を調整

パレート図のおおよその調整は完了しました。続いて、「主軸」と「第２軸」の目盛を調整します。

手順１．第２軸をダブルクリック
第２軸をダブルクリックして、「データ系列の書式設定」画面を表示します。

手順２．第２軸の最大値を「1.0」に調整
「データ系列の書式設定」画面⇒「軸のオプション」から、「境界線」の「最大値」を「1.0」に設定します。

手順３．主軸をダブルクリック
主軸をダブルクリックして、「データ系列の書式設定」画面を表示します。

手順４．主軸の最大値を調整
「データ系列の書式設定」画面⇒「軸のオプション」から、「境界線」の「最大値」を変更します。「最大値」の値は、売上高の「合計値」に一致させてください。

この主軸の最大値を売上高の合計値と一致させる目的は、以下の２点です。

手順５．主軸と第２軸の目盛が完成
下の図のように目盛が調整出来ていれば、目盛の調整が完了です。

ステップ６：パレート図を編集して完成させる

ここまでくれば、パレート図はほぼ完成です。

後は必要に応じて、通常のグラフの作成と同じように以下の項目を調整してください。

残りの調整項目( 作業は任意)

• 表タイトル
• 主軸の軸タイトル
• 主軸の単位
• 折れ線グラフのマーカー
• 折れ線グラフのラベル表示
• 棒グラフのラベル表示

これらの項目の調整方法が分からない方は、下の棒グラフの作成方法を纏めたリンク記事で紹介していますので参考にしてみてください。

パレート図の簡単な作成方法(ヒストグラムを使用)

Excelのパレート図を簡単に作成する方法を解説します。

冒頭で紹介したとおり、こちらの方法は、棒グラフの開始位置を調整するなどの特定のグラフ操作が行えないので、その点を考慮に入れて作成を開始してください。

それでは、どうぞ！

手順１．パレート図に使う元データを準備
パレート図に使うデータを表に纏めます。グラフは自動で大きいものから順に並び替えられますので、昇順や降順での並べ替えは不要です。

手順２．「おすすめグラフ」を選択
元データの見出し行も含めたセル範囲を選択してから、「挿入」タブ⇒「おすすめグラフ」を選択します。

手順３．パレート図を選択
「すべてのグラフ」タブの「ヒストグラム」というグラフを選択します。次に画面右上の「パレート図」を選択し、「OK」で画面を閉じます。

手順４．主軸をダブルクリック
主軸をダブルクリックして、「データ系列の書式設定」画面を表示します。

手順４．主軸の最大値を調整
「データ系列の書式設定」画面⇒「軸のオプション」から、「境界線」の「最大値」を変更します。「最大値」の値は、売上高の「合計値」に一致するように調整してください。

手順５．必要に応じてグラフを調整
後は必要に応じて、通常のグラフの作成と同じように以下の項目を調整しましょう。

残りの調整項目( 作業は任意)

• 表タイトル
• 主軸の軸タイトル
• 主軸の単位
• 棒グラフのラベル表示

これらの項目の作業手順が分からないという方は、下の棒グラフの作成方法を纏めた記事で紹介していますので参考にしてみてください。