PHP  エクセル10倍活用術              操作編

 ・「ABC分析」グラフを作成する

操作の概要

 ここでは、ABC分析グラフ(パレート図)を作成する操作を詳しく説明します。
 

操作の流れ
 

ここでは下記の流れで操作を説明します。
  1. 下図のような得意先別売上表があります。232件の得意先があります。


     
  2. 売上高より、「売上構成比」を求めます。
  3. 次に「累計構成比」を求めます。
  4. ここではここでは「得意先名」「売上高」「累積構成比」で、2軸のグラフ (パレート図)を作成します。
  5. そのグラフを加工して、ABC分析グラフを作成します。

売上合計を作成します

  1. ここでは、B234に、B列の売上高の合計を作成します。

C列に「売上構成比」を作成します

  • C1に売上構成比の項目名を入力します。
    C行の2行目の売上構成比の作成方法を以下に説明します。
  • そのC2セルを以下のC列にコピーして、全店の売上構成比を求めます。
    その後、表示形式をパーセントにします
  1. C2のセルに「=B2/$B$234」を入力します。
    これは「B2の売上高」÷「B列の売上合計額」の意味です。
    (この位置は変わらないので、絶対番地の意味でB234ではなく、$B$234と入力します)

  2. その計算式をC3〜C233までコピーします (合計の欄のC234は不要です)。
    (C2のセルをオートフィル状態にして、ダブルクリックすれば、最下行まで一瞬にコピー &ペーストが出来ます)

  3. そしてC列の表示形式をパーセントに変更します(詳細説明略)

D列に「売上構成比累計」を作成します−2行目の指定

  1. D1に「売上構成比累計」の項目名を入力します。D列の表示形式もパーセンテージとします。

  2. D2は1件目なので、C2と同じ構成比となりますので、「=C2」と指定します。

D列に「売上構成比累計」を作成します−3行目の指定

  1. D3は2件目で、累積を求めるので「=D2+C3」 と指定します。
    (D列の1つ上の累積構成比+C列の構成比)

D列に「売上構成比累計」を作成します−4行目以降の指定

  1. D4は、D3と同じ考え方と同じ操作です。1つ上の累積構成比とC列の構成比を合計します。
    ですから、これ以降(ここではD233まで)はD3の計算式をコピーして貼り付けます。

    下図は表の最後の方を示します。累積構成比なので最後は100%となっています。
    これでグラフ作成の準備が出来ました。

グラフを作成します−範囲指定

  1. A1〜B233とD1〜D233を範囲指定します。 (合計行の234行目は除きます

    • 離れた範囲を指定する時は、【Ctrl】キーを押しながら指定します。
    • 大きな表の場合には、不要なC列(売上構成比の列)を非表示として、A1〜D233を範囲指定した方が楽です 。

まず縦棒グラフを作成します

  1. 【挿入】タブ→[グラフ]の【縦棒】を選択し、「2−D縦棒」をクリックします。

縦棒グラフが作成されました

  • 縦棒グラフが作成されました。

  • 埋込グラフで表示されましたが、ABC分析グラフは1グラフシート上の表示の方が見やすいので、 表示場所を変更します。

縦棒グラフの表示場所を変更します

  1. 今表示されている【デザイン】タブ→[場所]の【グラフの移動】をクリックします。

  2. 表示された、グラフの移動ダイアログにて、新しいシートを選択し、ここでは「ABC分析グラフ 」と名付けました。
    【OK】をクリックします。

縦棒グラフの表示場所が変更されました

  • 新規シートにグラフが移動されました。

  • 次に、今表示されていない「売上構成比累計」を折れ線グラフで表示させる指定を行います。

「売上構成比累計」のグラフエリアを選択します−1

  • 今グラフ上では見えていない、売上構成比累計のグラフエリアを、リボンバーより選択します。

  1. 【レイアウト】タブをクリックします。
    その中の[現在の選択範囲]の【グラフエリア】より選択します。

「売上構成比累計」のグラフエリアを選択します−2

  1. 「グラフエリア」の下矢印をクリックします。

  2. 【系列”売上構成比累計”】をクリック で選択します。

  3. 【系列”売上構成比累計”】が選択されました。
    (その上で、次に、この系列を折れ線グラフに変更します)

「売上構成比累計」 を折れ線グラフに変更−1

  1. 【デザイン】タブをクリックします。

  2. [種類]で【グラフの種類の変更】をクリックします。

「売上構成比累計」 を折れ線グラフに変更−2

  1. グラフの種類の変更ダイアログにて、「折れ線」を選択して、【OK】とします。

「売上構成比累計」が折れ線グラフに変更されました

  • 折れ線グラフになったと言っても、下図のように今の1軸では、赤い少しの帯状態でしか表示されていません。

  • そこで、次にこのグラフを2軸のグラフに変更します。

「売上構成比累計」を2軸に指定します−1

  1. 売上構成比累計の折れ線グラフ部分(ここでは赤い帯のような部分)をクリック(@)します。

  2. 【レイアウト】タブをクリック(A)します。

  3. [現在の選択範囲]の中の【選択対象の書式設定】をクリック(B)で選択します。

「売上構成比累計」を2軸に指定します−2

  1. 表示されたデータ系列の書式設定で、系列のオプションを「第2軸」に変更します。

  2. 【閉じる】をクリックします。

  3. これで売上構成比累計は第2軸に変更されました。

折れ線グラフを細い線にします

  • この2軸のグラフで折れ線グラフは太すぎますので、細い折れ線グラフに変更します。
    最初から細いグラフで作成して行く方法はもちろんありますが、操作20で説明している、売上構成比の赤い帯をクリックでつかむ事が難しくなります。(操作1 3〜操作16を繰り返してつかむ事は出来ます)
    そこで、ここでは、あえて太い折れ線グラフで一端作成して、出来上がってから、細い折れ線グラフを作成する方法で説明しています。
  1. 売上構成比の赤の折れ線グラフをクリック(@)で選択します。

  2. 【デザイン】タブ→[種類]の【グラフの種類の変更】をクリック(A)します。

  3. 「細い折れ線」を選択します。

  4. 【OK】をクリックします。

ランクを示す為の補助線を追加します−1

  • ここでは、Aランクを80%まで、Bランクを95%まで、Cランクを100%までと考えて、補助線を追加します。
  1. まず、Aランクの直線を引きます。右の軸の80%の所から、折れ線グラフにぶつかるまで、まず直線を引きます(@)。
    【挿入】タブ→[図]の【図形】より、「直線」を選択して線を引きます。

  2. するとリボンバーは【書式】に変更されますので、「図形のスタイル」欄で、直線のスタイルを決めます。

  3. 次に線と折れ線と交差する所から、今度は真下に線を同じ要領で引きます。
    真っ直ぐな直線を描画する時には、【Shift】キーを押しながらマウスで描きます。(A)。

ランクを示す為の補助線を追加します−2

  1. 操作27〜29と同じ要領で、今度は95%の所から、直線を2本引きます。

%とランク名を入れます

  • 一応出来ましたが、 ABC分析グラフとして、下図のように分かりやすく装飾します。
  1. 今直線を引いた95%の表示を追加し 、80%と95%を赤枠で囲んで強調しました。
    85%の枠は、操作27と同じ要領で、□図形を利用して、塗りつぶしなしとして表示しました。
    95%の枠も文字も□図形を利用しました。

  2. 各ランク名も追加しました。
    これは同じく図形の中にある、 (縦書きテキストボックス)を利用しました。