会計人のためのExcel活用術（２２）

会計データの編集で押さえておきたいExcelのグラフ機能（PL分析２）

　前回に引き続き、Excelグラフを活用したPL分析を見ていきます。
　前回、「比較」、「傾向」という切り口からの分析例を見ましたので、今回は「分解」（ 部門別、一人当たり等）の切り口による分析例を見ていきます。

（１）「分解」で検証する

　部門別や店舗別に分けて損益を把握することは一般的によくやられている分析です。
　これらはグラフを使って視覚化すると、さらに気づく点がはっきりします。

　たとえば、次のような月別の部門別売上表を作ります。
　何を比較したいかによって使うグラフの種類は変わりますが、各部門の売上高推移をみたい場合は折れ線グラフを使います。
　各部門の月別売上表を選択して、メニューの「挿入」→「折れ線」を選びます。

このケースでは、売上高推移から特に目立った変化は見受けられません。

次に、各部門の売上高構成割合も検証してみます。
各部門の売上構成割合をみるには積み上げ棒グラフを使うとわかりやすいです。
同じように表を選択してメニューの「挿入」→「積み上げ縦棒」を選びます。
今度は部門売上の合計である「全社売上高」の部分まで選択して表にしてみます。

「全社売上高」も含めてグラフ化したので、「全社売上高」も積みあがった状態になっています。そこで「全社売上高」のみを折れ線グラフに変更します。
グラフのどこでもいいので右クリックをして「系列グラフの種類の変更」を選びます。
次の画面で「全社売上高」を「折れ線」に変更します。
そうすると、全社売上高を折れ線にした売上構成割合の推移グラフが完成します。

これで、売上構成の推移グラフが出来上がります。
あとは、特徴的な変化がないかを探します。この例で言うと、構成割合も特に目立った変化はなさそうです。
もちろん、「売上構成に大きな変化なし」、「これまでと同様に推移」ということでもよいのですが、もう少し細かく部門ごとの特徴を検証したい場合には、散布図を使って、マッピングで視覚化する方法もあるので、こちらもご紹介します。

（２）特徴を視覚化して検証する

　複数の軸から、座標上にマッピングして、それぞれの特徴をみるのに散布図を使うと便利です。「分解」したそれぞれの特徴を見たいときなどに使えるグラフです。
　棒グラフや折れ線グラフ同様、Excelの標準的なグラフなので、簡単に作成できます。
　その中の１つ「バブルチャート」という散布図を使った例をご紹介します。
　「バブルチャート」は縦軸、横軸、バブルの大きさという３つの視点から特徴を視覚化できるグラフになっています。

　たとえば、先ほどの部門について、売上高や売上構成割合からは特徴がつかめなかったので、「粗利益率」、「一人当たり粗利益額」、「粗利益額」という3つの軸からマッピングしてみます。

　３つの軸に何を選ぶかに特に決まりはありませんが、バブルの大きさを示す3つ目の軸としては金額（率ではなく）を使うことが多いです。
　感覚的にバブルの大きさがボリュームを表すからだと思います。
　「バブルチャート」を作るには、次のような表を用意します。
　1列目（粗利益率）が横軸、2列目（1人当たり粗利益）が縦軸、3列目（粗利益額）がバブルの大きさを表す値になってきます。

　項目名のところは選択せずに、値が入っている範囲のみを選択して、「挿入」→「散布図」の中の「バブル」を選ぶと、バブルチャートが出来ます。

　このままだと、どれがどの部門かわからないので、部門名をグラフ上に表記します。
　やり方は、バブルを選択して右クリック→「データラベルの追加」をします。
　そうすると、縦軸の値「一人当たり粗利益」の値がデフォルト表示されますので、これをまた右クリック→「データラベルの書式設定」を選びます。

　「データラベルの書式設定」で「Y値」（縦軸の値）になっているので、この表示を変更します。部門名を表示したいので、この中の「セルの値」を選びます。
　すると、どのセル範囲かを指定するポップアップが出てくるので、部門名のセルを指定してOKします。そうすると部門名がグラフ上に表記されます。

あとは、「グラフのデザイン」から縦軸と横軸の軸名を表示させたり、色を変えたりすれば、バブルチャートの完成です。

　このケースでは、A部門は粗利益（や売上高）自体は小さいが、粗利益率、一人当たり粗利益は大きく、効率性の高いことがわかります。
　もちろん、毎月数字を見ていれば、感覚的にそれぞれの部門や店舗の収益性や特徴が、わかってくると思います。
　その感覚的なものを、３つくらいの額や率を用いて座標上にマッピングして視覚化してあげれば、よりわかりやすくなると思います。
　ＰＬ分析の応用編として、バブルチャートも試してみましょう。

　今回まで「儲かっているか？」（ＰＬ分析）の分析例をいくつか見てきました。
　紹介したのは、ほんの一例ではありますが、考え方や切り口（比較、傾向、分解）については共通していますので、そういう視点で分析をチェックしてみましょう。

　次回は、「お金は足りているか？」（ＣＦ分析）について見ていく予定です。