703:パレート図

700:品質管理

Excelでパレート図を作成する方法の紹介

サンプルデータをダウンロードしてください

まず、商品コードと商品名でデータを集計し、新しいシートに売上の多い順にソート(並べ替え)してください。

構成比と構成比累計の列を追加する

C列の合計をC27に計算させて、D列に構成比、F列に構成比率を入れましょう。D3に「構成比」としてF3に「構成比率」と入力しておきます。

構成比の計算では、D4セルに、(イチゴの売上)+C4/$C$27(合計)を入れて、D26行までコピー

構成比累計の計算では、F4セルに、+D4 を入れて、F5セルに (えびせんの構成比)+E4+D5(いちごの構成比)として、F26行までコピーしてください。累計比なので最後の行F26が1になっていることを確認できれば、式が正解ということです。

D列とF列は、比率なので、表示形式を%としておきましょう。

グラフ作成

「品番ごとに集計」シートにあるデータを部分を選択します。具体的にはA3:C26です。

「挿入タブ」のおすすめグラフを押すと、ほぼできてしまします。グラフツールのデザインメニューになっているので、

データの選択アイコンをクリックすると、

このWindowが立ち上がるので、商品コードと商品名は凡例項目(系列)に不要なのでチェックを外して、構成比もチェックを外してください。そして、横(項目)軸ラベル(C)の編集(I)を押すと、以下Windowが立ち上がります。

上記で、B3ではなくて、B4:B27と横軸のラベルをマウスで選択すれば、

横項目に設定されます。これでOKボタンを押すと、以下のようになり、これで、ほぼ十分です。

見栄え設定

さらに、少し手を加えたい人向けに、説明を加えます。右側の率をクリックして、その設定内容が右に出てきます。累計率は100%を超えることがないので、「1.2」を「1」に変更します。

下位の5%程度は、内容があってもあまり意味がありません。この場合には「カムイ」以下を一行にまとめてみましょう。

15行目に一行追加して、B15に「その他」、C15にC16以下の合計、D15にD16以下の合計、F15に100をいれて、全体を値固定し、16行以下を行削除します。横のグラフも小さくなってしまいますが、それは、角をつまんで大きくします。さらに、グラフの縦棒を右クリックしてデータ系列の書式設定で要素の感覚を0に限りなく近づけてみてください。

グラフタイトルをクリックして「商品名別売上パレート図」とでも入れて、グラフの移動で、新規シートにすれば、以下のようになって完成します。

この場合、70%までは、紅茶までなので、紅茶までが、Aランク、ペンからカローラまでがBランク、それ以下はCランクという具合です。

コメント

タイトルとURLをコピーしました