ブログblog

貸借対照表(B/S)のグラフって、ちょっと見慣れない形をしていると思いませんか？
大きな長方形を縦に2分して、それぞれの長方形の中に面積の異なる長方形が並んでいます。
我々が日頃業務で使用している会計ソフトを使えば、貸借対照表やそのグラフは簡単に(自動で)作ることができるのですが、今回はあえてExcelを使ってグラフを作ることに挑戦してみました。
Excelで作成する場合は、どのようなデータを作り、どのようにグラフを作ったらいいでしょうか？
実際作ってみたらちょっと手間取ってしまったり、操作方法がわからなかったりしたところがあったので、そのあたりを中心にまとめました。
作業環境はWindows10、Exccel2016です。

できあがりイメージ

最終的にできあがったグラフはこのような感じになりました。
貸借対照表は借方と貸方の左右の金額の合計がイコールになります。

資産 = 負債 + 純資産

このグラフによって資産と負債、純資産のバランスを視覚的にとらえることができるので、数字だけの表でみるよりも会社の財務状況をより把握しやすくなります。

大きな長方形の中に複数の長方形が含まれているように見えるこのグラフは、同じ高さの縦棒グラフが隙間なく2本隣り合っているようすれば作ることができそうです。
ということで、Excelのたくさんあるグラフの中から「100%積み上げ棒線」を使用することにします。
それではグラフの元となるデータ(「データの選択で選択するデータ」)と、グラフ作りのちょっとしたポイントに分けて見ていきます。

グラフの元となるデータ

簡単な貸借対照表グラフの元となるデータの形はこんな感じです。
今回はグラフの作り方がメインなので、ざっくりまとめました。
※数字は適当です。

系列となる先頭列

先頭列には、系列となる項目を「全て」記載します。
資産の部(借方)も負債・純資産の部(貸方)も両方先頭列にまとめてしまうのがポイントです。
あとは資産の部(借方)と負債・純資産の部(貸方)のそれぞれに数字を入力していきます。
空白のセルができますがOKです。

※合計欄は集計してちゃんと資産の部(借方)と負債・純資産の部(貸方)がイコールになっているかどうかを確認するために設けました。グラフでは使用しません。

グラフを作る

データの合計の一つ手前の行まで選択します。
その状態で「挿入タブ」を選択します。
今回は「100％積み上げ縦棒」を利用するので以下の手順で進めます。

これで次のようなグラフができました。

グラフの見た目を変更する

せっかくできたグラフですが、目指す貸借対照表のグラフとはちょっとイメージが違うようです。
二つの棒グラフの間に隙間が空いていますし、値が記載されていないので、元データと見比べながらではないと内容が把握できません。
グラフ化するメリットはパッと見て内容を把握しやすいようにすることなので、これだと貸借対照表のグラフとしてはちょっとイマイチですね。

グラフのデザインや表示などを変更するには、大きく二つのやり方がありそうです。

それから、グラフをダブルクリックすると、細かい設定をするためのパネルがウィンドウの右側からスルッと出てきますので、これも使います。
どちらから変更しても結果は同じですので使いやすい方でいいと思います。
今回は3種類のアイコンのパターンでやっていきます。

グラフをくっつける

まずはグラフをダブルクリックして設定パネルを表示します。
どれでもいいので、棒グラフの要素(色のついた箇所)をクリックします。
そうすると設定パネルの項目が「グラフエリアの書式設定」から「データ系列の書式設定」に変わります。

アイコンが3つ並んでいて、左からバケツアイコン(塗りつぶしと線)、五角形アイコン(効果)、グラフアイコン(系列のオプション)となっています。
グラフアイコン(系列のオプション)を選択します。
一番下に「要素の間隔」という項目があるので、こちらを0にします。
これでグラフの面積が広がり棒グラフ同士がくっつきます。

データラベルを変更する

今度はグラフに値を表示していきます。
今度はプラスアイコン(グラフ要素)からデータラベルのチェックボックスにチェックマークを入れます。
(チェックボックスをクリックします)
これでグラフに「値」が表示されました。

「値」だけだと何の項目かわかりにくいので、合わせて凡例に表示されている項目も加えていきます。
先ほどのプラスアイコン(グラフ要素)からデータラベルにカーソルを合わせると、右側に小さな右向き三角形のアイコンが表示されるのでそれをクリックします。
右側にさらに吹き出しが表示されるので、一番下の「その他のオプション」をクリックして設定パネルに「データラベルの書式設定」を出します。

今度は4つアイコンが並んでいますが、右側のグラフアイコン(ラベル オプション)を選択します。
※同じグラフアイコンなのに先ほどと内容が違うのがちょっとくせ者かもしれません。

「ラベルの内容」を見てみると「値」と「引き出し線を表示する」にチェックがあると思います。
さらに「系列名」にもチェックしてみてください。これで凡例に表示されている項目がグラフに表示されます。
ちなみに分類名にチェックすると横軸の項目名が入ります。

データラベルに系列を追加したので内容が重複してしまう「凡例」を非表示しました。
プラスアイコン(グラフ要素)をクリックし、「凡例」のチェックボックスからチェックを外します。

データラベルを改行する

表示させたデータラベルは、「系列名,値」とコンマ(カンマ)で区切られて横並びになっています。
これだと値の数字が大きくなって桁区切りを表示したとき(例：1,234,567)にちょっとわかりにくいですし、バランス悪く感じます。
そこで改行して2行に表示しましょう。

先ほどと同じ手順でパネルに「データラベルの書式設定」を表示し、今度は「ラベルの内容」の「区切り文字」を変更します。
選べるのは「コンマ・セミコロン・ピリオド・改行・スペース」です。
これも一つずつ変えていきます。
繰延資産だけはグラフ面積が小さいので、改行して2行にするとはみ出してしまったので、「スペース」を選択して1行にしました。
※一括して変える方法が見つかりませんでした。ラベルの位置は一括で変更できるんですけどね。

系列の順序を入れ替える

だいぶそれっぽくなってきましたね。
しかしよく見ると、元のデータの先頭列の項目の並び順が反対になっています。
並び順を入れ替えていきましょう。

漏斗アイコンをクリックし、「データの選択」をクリックします。(グラフを右クリックして「データの選択」でも一緒です)。

クリックすると、このようなポップアップが表示されます。
左側に「凡例項目(系列)」がありますので、この順序を入れ替えます。
入れ替えたい項目をクリックして(背景がグレーになります)、削除ボタンの右側にある「上」「下」ボタンで順序を入れ替えていきます。
やっぱり一括して順序を入れ替える方法が見つけられなかったので、ちょっと手間ですが一つずつ入れ替えていきます。

グラフのカラー(塗り)を変更する

カラーに関しては好き好きあるのでこのままでもいいかもしれませんが、カラーがちょっときついのと、いかにもExcelそのままだったので変更することにしました。
一番簡単なのは、グラフツールタブのデザインから「色の変更」を選ぶとカラーのセットが用意してあるのでお手軽に変更できます。
今回はせっかく貸借対照表なので、「資産の部」「負債の部」「純資産の部」をそれぞれオレンジ・青・緑の系統でグループにしてみました。

一手間かかってしまいますが、この一手間でグラフがぐっと見やすくなった気がします。
コツは、

あたりでしょうか。

その他

• • グラフタイトを変更しました
  • 凡例を非表示にしました
  • 軸(特に縦軸は非表示にしようか迷い中です
  • データラベルのフォントサイズ、ウェイトを変更しました

ちょっと長くなってしまいましたが貸借対照表のグラフをExcelで作ることができました。
100%積み上げ棒線グラフを作るときのコツといった感じになってしまいましたね。
お客様のところをうかがう時、プレゼンや打ち合わせの時、文字と数字だけの資料を用意するよりも、グラフなど視覚的にわかりやすい資料を作っておくと話がスムーズに進みますし、いかにもExcelで作ったものからちょっと工夫を加えると、見てもらう側にも伝えられるものが増えるような気がします。
Excelを使っていると、ちょっとした操作で戸惑ったりします。そんなときにちょっとしたお役に立てればと思います。
グラフに値などと共に割合(％)を表示したい、科目がわかるようなグラフを作りたいなどあればまた次の機会にでも。