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

　・「ＡＢＣ分析」グラフを作成する

　

操作の概要

　ここでは、ＡＢＣ分析グラフ（パレート図）を作成する操作を詳しく説明します。
　

操作の流れ
　

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


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

売上合計を作成します

  1. ここでは、Ｂ２３４に、Ｂ列の売上高の合計を作成します。

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

  • Ｃ１に売上構成比の項目名を入力します。
    Ｃ行の２行目の売上構成比の作成方法を以下に説明します。
  • そのＣ２セルを以下のＣ列にコピーして、全店の売上構成比を求めます。
    その後、表示形式をパーセントにします。

  1. Ｃ２のセルに「＝Ｂ２／＄Ｂ＄２３４」を入力します。
    これは「Ｂ２の売上高」÷「Ｂ列の売上合計額」の意味です。
    （この位置は変わらないので、絶対番地の意味でＢ２３４ではなく、＄Ｂ＄２３４と入力します）

  2. その計算式をＣ３～Ｃ２３３までコピーします （合計の欄のＣ２３４は不要です）。
    （C２のセルをオートフィル状態にして、ダブルクリックすれば、最下行まで一瞬にコピー ＆ペーストが出来ます）

  3. そしてＣ列の表示形式をパーセントに変更します（詳細説明略）。

Ｄ列に「売上構成比累計」を作成します－２行目の指定

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

  2. Ｄ２は１件目なので、Ｃ２と同じ構成比となりますので、「＝Ｃ２」と指定します。

Ｄ列に「売上構成比累計」を作成します－３行目の指定

  1. Ｄ３は２件目で、累積を求めるので「＝Ｄ２＋Ｃ３」 と指定します。
    （Ｄ列の１つ上の累積構成比＋Ｃ列の構成比）

Ｄ列に「売上構成比累計」を作成します－４行目以降の指定

  1. Ｄ４は、Ｄ３と同じ考え方と同じ操作です。１つ上の累積構成比とＣ列の構成比を合計します。
    ですから、これ以降（ここではＤ２３３まで）はＤ３の計算式をコピーして貼り付けます。

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

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

  1. Ａ１～Ｂ２３３とＤ１～Ｄ２３３を範囲指定します。 （合計行の２３４行目は除きます）

    • 離れた範囲を指定する時は、【Ctrl】キーを押しながら指定します。
    • 大きな表の場合には、不要なＣ列（売上構成比の列）を非表示として、Ａ１～Ｄ２３３を範囲指定した方が楽です 。

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

  1. 【挿入】タブ→［グラフ］の【縦棒】を選択し、「２－Ｄ縦棒」をクリックします。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

「売上構成比累計」 を２軸に指定します－１

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

  2. 【レイアウト】タブをクリック（②）します。

  3. ［現在の選択範囲］の中の【選択対象の書式設定】をクリック（③）で選択します。

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

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

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

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

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

  • この２軸のグラフで折れ線グラフは太すぎますので、細い折れ線グラフに変更します。
    最初から細いグラフで作成して行く方法はもちろんありますが、操作２０で説明している、売上構成比の赤い帯をクリックでつかむ事が難しくなります。（操作１ ３～操作１６を繰り返してつかむ事は出来ます）
    そこで、ここでは、あえて太い折れ線グラフで一端作成して、出来上がってから、細い折れ線グラフを作成する方法で説明しています。

  1. 売上構成比の赤の折れ線グラフをクリック（①）で選択します。

  2. 【デザイン】タブの「種類」の【グラフの種類の変更】をクリック （②）します。

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

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

ランクを示す為の補助線を追加します－１

  • ここでは、Ａランクを８０％まで、Ｂランクを９５％まで、Ｃランクを１００％までと考えて、補助線を追加します。

  1. まず、Ａランクの直線を引きます。右の軸の８０％の所から、折れ線グラフにぶつかるまで、まず直線を引きます（①）。
    【挿入】タブ→［図］の【図形】より、直線を選択して線を引きます。

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

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

ランクを示す為の補助線を追加します－２

  1. 操作２７～２９と同じ要領で、今度は９５％の所から、直線を２本引きます。

％とランク名を入れます

  • 一応出来ましたが、 ＡＢＣ分析グラフとして、下図のように分かりやすく装飾します。

  1. 今直線を引いた９５％の表示を追加し 、８０％と９５％を赤枠で囲んで強調しました。
    ８５％の枠は、操作２７と同じ要領で、四角図形を利用して、塗りつぶしなしとして表示しました。
    ９５％の枠も文字も四角図形を利用しました。

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