B/Sグラフ(貸借対照表グラフ)をExcelでつくる方法。区分別・科目別B/Sグラフのバリエーション。

B/S(貸借対照表・バランスシート)は重要な反面、わかりにくい・説明しにくいものですが、グラフにしてみるとわかりやすくなります。
ExcelでB/Sグラフをつくる方法をまとめてみました。
Excel BSグラフ
※自宅にて iPhone X

B/SをExcelのグラフでつくる

主な決算書として、
・B/S 貸借対照表→現金、預金、売掛金、在庫、借入金、資本金など
・P/L 損益計算書→売上、仕入、経費、利益など
があります。

(今回の記事は法人のB/Sについてです。個人についてはこちらで解説しています)

売上や利益が載っているP/Lは、なんとなくなじみがありますが、B/Sのほうは、なかなか理解しくく、説明しにくいものです。
・現預金(現金、預金)が大事
・利益剰余金は過去の業績の蓄積(原則として)
・資産が多ければいいものではない
・負債が少なければいいものではない
・経理を間違っていると、B/Sに現れる(粉飾も)
といったポイントはありますが、やはり見やすいとはいえません。
EX IT 35

私も好きではなく、こういったグラフにしています。

手書きでさらっと書いてもいいのですが、Excelのグラフでつくると楽ですし、毎年使えるので便利です。
EX IT 26

EX IT 29

EX IT 33


■スポンサードリンク
------※この記事は、投稿日現在の状況、心境、法律に基づいて書いています。---------

■スポンサードリンク


2010年、2011年に出したこれらの書籍でも紹介しました。
(『そのまま使える経理&会計のためのExcel入門』は、新板を今書いており、今年出す予定です)

B/Sグラフの基本形

B/Sグラフの基本形は、ざっくりと大枠をつくるものです。

B/Sには、区分がありますので、それをグラフにしていきます。
(繰延資産があればわけてつくります)
EX IT 37

サンプルもありますが、理屈を知っておいたほうがいいので、つくり方をまとめてみました。
EX IT 28

このグラフは、Excelの積み上げ縦棒グラフを使います。
Excelでグラフをつくるには、元データが必要です。
B/Sグラフの場合は、データをグラフのイメージどおりにつくります。

グラフのイメージ通りのデータは、赤枠のものです。
ただ、このデータからB/Sグラフをつくると、表示が逆になってしまい、修正しなければいけません。
EX IT 39

どっちもどっちではあるのですが、今後の使いやすさから考えると、青枠のようにデータをつくったほうが楽です。
直接入力するのも大変なので、赤枠のデータから数式で連動しています。
(実際は、赤枠のデータも、会計ソフトから貼り付けたデータから連動させるので、入力はしません)

■スポンサードリンク

データを選択して、Alt+F1で棒グラフをつくり、[グラフの種類の変更]で、
EX IT 17

[積み上げ縦棒グラフ]かつ、行と列を逆にしたものを選びましょう。
EX IT 18

グラフをクリックして、[+]→[データラベル]をクリックすれば、数値が表示されます。
EX IT 19

さらに、[その他のオプション]をクリックし、
EX IT 20

[系列名]にチェックを入れると、区分名(画像では「固定資産」)が表示されます。
ただ、この操作を1つずつやらなければいけません。
※インストラクターのネタ帳の伊藤潔人さんが、このマクロを記事にしてくださいました。
データラベルに系列名を表示するExcelマクロ:エクセルマクロ・Excel VBAの使い方-グラフ・Chart

EX IT 21

地道にやった後、データ系列(グラフの棒)を選択して、[系列のオプション]の[要素の間隔]を「0]にしましょう。
こうすると、棒と棒の間隔が0になり、B/Sっぽくなります。
EX IT 23

あとは好みで色を付けて完成です。
EX IT 26

B/Sグラフのバリエーション

B/Sグラフは次のようなものもつくれます。

B/Sグラフ 区分・科目バージョン

区分に加え、科目もいれたバージョン。
EX IT 29

金額が少ない科目は、その他でまとめます。
データは4列、
EX IT 40

グラフ用のデータも同じようにつくりましょう。
EX IT 41

B/Sグラフ 科目・区分バージョン

科目を外側にし、区分を内側にしたバージョン。
EX IT 42

色の付け方が悩みどころです。

B/Sグラフ 科目のみバージョン

気に入っているのはこの科目のみのバージョン。
色は区分別につけます。
1つずつつけなければいけませんが、F4キー(繰り返し)を使うと便利です。
EX IT 31

B/Sグラフ 比較バージョン

科目のみのものを比較したバージョン。
比較してこそわかることも多いです。
むしろ比較しないとわかりません。
EX IT 33

この事例では、前期と比べて
・総資産(青系)が減った
・現預金が増えた
・純資産(黄色)が増えた
ことから、より効率のいい状態になっているということがわかります。
資産=正義、負債=悪というわけではなく、資産が減ったからダメというわけではなく、中身とバランスが大事なわけです。

マクロを使うとよりいいのですが、マクロなしでも十分なので今回は使っていません。
サンプルの茶色の字のセルに入力するとグラフが変わりますのでお好きなバージョンで試してみていただければ。

EX-ITサンプル BSグラフ.xlsx


■スポンサードリンク
【編集後記】

ヘルシオが届き、最初につくってみたのは温泉卵。
つくるとめんどくさい温泉卵をかんたんにおいしくつくれるのは感激です。
・鶏のからあげ
・あぶりサーモン
・牛肉のステーキ
もつくってみました。

楽しめそうです。

【昨日の1日1新】
※詳細は→「1日1新」

ヘルシオ AX-XW400

【昨日の娘日記】

毛布に隠れていると、確実に見つけられます。
もうちょっと小さく隠れないといけないのかもしれませんが、9ヶ月児でそういうことがわかるのはすごいなぁと。




■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』