税理士法人 あおば会計は地域でオンリーワンのサービスを提供できる会計事務所です。
会計・税務のお悩みはお任せください。

365日 しんようの  おける じむしょ 0120-365-406

ブログblog

2018.08.03 【貸借対照表グラフをエクセル100%積み上げ棒線で作る】新潟の税理士がお送りするブログ

貸借対照表グラフをエクセル100%積み上げ棒線で作る

 

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

できあがりイメージ

貸借対照表グラフできあがりイメージ

 

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


資産 = 負債 + 純資産

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

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

 

グラフの元となるデータ

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

 

  資産の部 負債・純資産の部
流動資産 60  
固定資産 30  
繰延資産 10  
流動負債   40
固定負債   35
純資産   25
合計 100 100

 

系列となる先頭列

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

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

 

グラフを作る

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

【グラフ】縦棒/横棒グラフの挿入>その他の縦棒グラフ>縦棒>100%積み上げ縦棒>右のグラフをクリック>OK

 

 

100%積み上げ縦棒_グラフの挿入

 

 

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

100%積み上げ棒線グラフ初期イメージ

 

 

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

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

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

    • グラフをクリックすると上部メニューのタブ群に「グラフ>(デザイン・書式)」が追加されるのでそちらから変更する
    • グラフをクリックするとグラフの右側に表示されるプラスアイコン(グラフ要素)・筆アイコン(グラフ スタイル)・漏斗アイコン(グラフ フィルター)から変更する

 

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

 

グラフをくっつける

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

 

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

 

データラベルを変更する

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

 

100%積み上げ縦棒_グラフ要素の変更

 

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

 

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

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

 

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

 

データラベルを改行する

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

 

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

 

系列の順序を入れ替える

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

 

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

 

100%積み上げ縦棒_並び順変更

 

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

 

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

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

 

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

    • カテゴリでグループ化
    • たくさん色を使わない(同系色の濃淡で対応)
    • 背景色が濃いときは文字色を白にする

あたりでしょうか。

 

 

その他

 

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

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

※当ブログの記事は執筆時の法律に従って書かれています。
法改正等により記載内容との相違がある場合がございます。
あらかじめご了承ください。

会計・税務のお悩みはお任せください。
まずはお気軽にご相談ください!

お問い合わせ
一覧へ戻る
TOP