>>運営者イチオシのミールキットはこちら

【家計管理】エクセルで複式簿記の家計簿を作る方法|第五回:損益計算書と貸借対照表

この記事には広告を含む場合があります。記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。

我が家で使っている、エクセルを使った複式簿記の家計簿(エクセル複式家計簿)を作る方法を紹介します。

第五回は損益計算書(PL)と貸借対照表(バランスシート、BS)の作り方です。

あがぺい
あがぺい

家計管理の肝! これらの財務諸表を用いて現状を知ることが最も重要です。

★家計簿の導入記事です。まだ読んでない方はこちらからどうぞ。
>>>【簿記保有者必見】貯まる!複式簿記で家計簿を付けるメリット

エクセル複式家計簿シリーズ

エクセル複式家計簿のまとめ記事

第一回:勘定科目リストをつくる
第二回:仕訳帳をつくる
第三回:集計表をつくる
第四回:締め切りと繰り越し
第五回:損益計算書と貸借対照表をつくる  ←この記事

晩ごはんに悩んだらミールキット!

 ミールキットは共働き世帯が使うべき令和の便利アイテム。食材・調味料・レシピがセットになって自宅に届くから、献立決めや買い物のストレスから一気に開放されます。
 お得に試せるサービスもあるので、気になったらすぐにはじめてみてくださいね!

運営者イチオシのミールキット

損益計算書と貸借対照表のおさらい

エクセル複式家計簿で作る財務諸表
▲エクセル複式家計簿で作る財務諸表

はじめに、損益計算書(PL)と貸借対照表(BS)についてサラッとおさらいします。

★詳しくはこちらの記事で
>>>無駄にしない!簿記3級を取った後の生かし方3選【経理以外の人へ】

損益計算書で分かること

家計の健康状態と損益計算書
▲家計の健康状態と損益計算書

損益計算書は収益・費用・利益から成る表です。

家計簿においては主に月ごとの

  • 黒字or赤字
  • 利益率(=貯蓄率)

を教えてくれます。

グラフ化するとひと目で分かるようになりますよ

損益計算書をきっかけにして、

  • 今月はなぜ赤字だったのか?
  • 収入は一年前より増えているか?
  • 貯蓄率を上げるにはどうすればいいか?

といった、より具体的な分析をすることが重要になります。

貸借対照表で分かること

家計の健康状態と貸借対照表
▲家計の健康状態と貸借対照表

貸借対照表は資産・負債・純資産から成る表です。

家計簿においては、現時点での

  • 保有資産額
  • 家計の健康状態(純資産の正負)

が分かります。

貸借対照表を眺めながら、

  • 過度な借金を負っていないか?
  • 純資産は増えているか?
  • 老後資金を準備するにはどうすればいいか?

といった、理想と現実のギャップを数字で把握できるようになります。

現状把握が優良家計への第一歩です

エクセル複式家計簿 損益計算書の作り方

それでは本題のエクセル複式家計簿で財務諸表を作っていきましょう。
まずは損益計算書からです。

”財務諸表”ワークシートを作り、損益計算書の元となる表を書く

損益計算書と後で紹介する貸借対照表のために、”財務諸表”ワークシートを作成します。

ワークシート一覧

下の画像のように年・日付を入力したあと、損益計算書の元データになる表を書きます。

元データの表

B2、B3セルの年と月から、そのときの損益計算書が表示されるように設定します。

表に集計表のデータを入れる

収入の欄の右側(貸方)に”集計表”ワークシートにあるピボットテーブルから「4_収入 集計」の値を引っ張ってきます。
このとき、絶対値が欲しいのでABS関数で囲っています(マイナスをプラスにする)。

D6セル=ABS(GETPIVOTDATA(“金額”,集計表!$A$1,”年”,2021,”日付”,9,”グループ”,”4_収入”))

収入の入力

費用の欄の左側(借方)も同様に「5_費用 集計」の値を入力します。

C7セル=GETPIVOTDATA(“金額”,集計表!$A$1,”年”,2021,”日付”,9,”グループ”,”5_費用”)

費用の入力
GETPIVOTDATA関数をセル参照にする

B2,B3セルに入力した年と月から損益計算書を表示できるように、GETPIVOTDATA関数を以下のように編集します。

D6セル=ABS(GETPIVOTDATA(“金額”,集計表!$A$1,”年”,B2,”日付”,B3,”グループ”,”4_収入”))
C7セル=GETPIVOTDATA(“金額”,集計表!$A$1,”年”,B2,”日付”,B3,”グループ”,”5_費用”)

★GETPIVOTDATA関数の説明はこちらの記事が詳しいです
>>>【家計管理】エクセルで複式簿記の家計簿を作る方法|第四回:締め切りと繰り越し

利益と損失を計算する

利益と損失はD6,C7セルを参照して計算します。
収入が費用を上回ると利益が、収入が費用を下回ると損失が出ますので、IF関数を使って表現します。

なお、利益は左側(借方)、損失は右側(貸方)になるように入力します。

C8セル=IF(D6>C7,D6-C7,0)
D9セル=IF(D6>C7,0,C7-D6)

利益の入力
損失の入力
枠外で合計値を計算する

借方と貸方が釣り合っていることを確認するため、枠外に合計を出すSUM関数を入れておきました。

C10セル=SUM(C6:C9)
D10セル=SUM(D6:D9)

合計の入力

分かりやすいようにグラフにしましょう!

損益計算書のグラフ化

B6:D9を選択して、[挿入]タブ→グラフ→その他の縦棒グラフをクリックします。

グラフの作成
その他の縦棒グラフを選択

積み上げ縦棒グラフを選びます(縦軸が金額になっているやつ)。

積み上げ縦棒を選択

グラフが挿入されました。

損益計算書の初期グラフ
グラフの体裁を整える

棒を右クリックして、「データ系列の書式設定」を開きます。

データ系列の書式設定を選択

要素の間隔を0%にします。

要素の間隔を0%に

それっぽくなってきました。

間隔がなくなった棒グラフ

グラフタイトル、凡例、横軸なんかは消してしまいましょう

データラベルを付ける

見た目では分かりにくい金額をグラフ内に表示するため、データラベルを追加します。

[グラフのデザイン]→データラベル→中央をクリックします。

データラベルの追加

系列名と値にチェックをつけます。

系列名の追加

棒グラフのなかに金額が表示されるようになりました。

データラベルが追加された損益計算書

文字サイズと色を調整します。

完成した損益計算書
完成した損益計算書

以上で損益計算書のグラフが完成しました。
黒字/赤字や貯蓄率がパッと見で分かるようになりましたね!

ピボットテーブルを更新すると、こちらのグラフも自動で更新されますよ。

赤字のときは?

赤字(収入<費用)になると右側に損失の棒グラフが現れ、下の画像のような見た目になります。

赤字の損益計算書
赤字の損益計算書

エクセル複式家計簿 貸借対照表の作り方

続きまして、エクセル複式家計簿の貸借対照表を作っていきましょう

貸借対照表の元となる表を書く

”財務諸表”ワークシートに貸借対照表の元となる表を書きます。
下の画像のように、損益計算書の右側を使いました。

項目は資産・負債・純資産・損益とし、右2セル分を空けておきます。

貸借対照表の元データの入力
集計表からデータを取ってくる

資産の左側(借方)に”集計表”ワークシートにあるピボットテーブルから「1_資産 集計」の値を引っ張ってきます。
負債の右側(貸方)も同様に値を取ってきます。絶対値が欲しいのでABS関数で囲います。

資産I6セル=GETPIVOTDATA(“金額”,集計表!$A$1,”年”,B2,”日付”,B3,”グループ”,”1_資産”)
負債J7セル=ABS(GETPIVOTDATA(“金額”,集計表!$A$1,”年”,B2,”日付”,B3,”グループ”,”2_負債”))

数式内のB2とB3は年と月を入力したセルです(おさらい)

資産と負債の計算

純資産はプラスにもマイナスにもなるため、借方と貸方にIF関数を使って入力します。

純資産I8セル=IF(I6>J7,0,ABS(GETPIVOTDATA(“金額”,集計表!$A$1,”年”,B2,”日付”,B3,”グループ”,”3_純資産”)))
純資産J8セル=IF(I6>J7,ABS(GETPIVOTDATA(“金額”,集計表!$A$1,”年”,B2,”日付”,B3,”グループ”,”3_純資産”)),0)

純資産の計算

借方に純資産が出てきたら、それは「純負債」です

損益を計算する

借方と貸方の両方に損益を入力します。
※月の締め切りが終わっていないときに貸借対照表を正しく表示させるために必要。締め切ったあとは0が入る。

損益の借方I9セル=IF(SUM(I6:I8)>SUM(J6:J8),0,SUM(J6:J8)-SUM(I6:I8))
損益の貸方J9セル=IF(SUM(I6:I8)>SUM(J6:J8),SUM(I6:I8)-SUM(J6:J8),0)

損益の計算

SUM(I6:I8)は借方の合計、SUM(J6:J8)は貸方の合計です

枠外で合計を計算する

借方と貸方の値が釣り合っているかどうか、枠外で確認します。

I10セル=SUM(I6:I9)
J10セル=SUM(J6:J9)

合計値の計算
グラフを挿入する

H6:J9を選択して、縦棒グラフを挿入します。

貸借対照表の挿入
グラフの体裁を整える

損益計算書のときと同じようにグラフを整えたら貸借対照表の完成です。

完成した貸借対照表
完成した貸借対照表

ついに複式簿記の目標である貸借対照表が完成しました。
家計の健康状態がひと目で分かるようになりましたね。

締め切っていない月の貸借対照表は?

締め切っていない(損益を純資産に振り替えていない)とき、貸借対照表は下の画像のようになります。
貸借対照表の借方もしくは貸方に「損益」がひょっこり出てきます。

締め切っていないときの財務諸表

このとき損益計算書の損益と貸借対照表の損益が一致します。

まとめ 家計改善の一歩目を踏み出そう!

以上、5記事に渡ってエクセルでつくる複式簿記の家計簿を解説してきました。

損益計算書と貸借対照表は家計の通知表みたいなものです。
毎月チェックして、健康的な家計を維持しながら小金持ちを目指していきましょう。

あがぺい
あがぺい

みなさんと一緒に小金持ちを目指します!

ここまでお付き合いくださり、ありがとうございました。

エクセル複式家計簿シリーズ

エクセル複式家計簿のまとめ記事

第一回:勘定科目リストをつくる
第二回:仕訳帳をつくる
第三回:集計表をつくる
第四回:締め切りと繰り越し
第五回:損益計算書と貸借対照表をつくる  ←この記事

エクセル複式家計簿が完成したら読む記事

6 COMMENTS

山口達也

複式家計簿を自分で作れないかと思い、色々探していて「あがぺいブログ」に辿り着きました。

現在、複式家計簿「マスターマネー家計版6」を3年位使用していますが、自分ですべて作ってみたい気持ちが湧いてきました。

「あがぺいブログ」を読んで説明が分かりやすく、取り組んでみようと思います。自分は、78歳の後期高齢者です。格好のテーマが見つかり当分の間楽しめそうです。ゴールまで行けるかどうか分かりませんが挑戦してみます。

これからの「あがぺいブログ」の発展をお祈りします。ありがとうがざいました。

返信する
あがぺい

お力になれて光栄です!
山口さんの挑戦する姿に刺激を受けました。
私もよりよいブログを書き続けられるよう努力していきます。

ご不明点ございましたら、お気軽にご連絡くださいませ!

返信する
井上ひつじ

第1回から第5回まですべて読み、今もあがぺい式複式簿記を実践しています。自分の家計を見つめなおすことができて、大人として成長したなあと実感できました。

細部まで読者のことを考えている投稿ですばらしかったです。
ありがとうございました!

自分も複式簿記を勉強し、学習したことをブログにしてみたので、よかったら遊びに来てください。(記事名「複式簿記のエッセンス」)

返信する
あがぺい

井上ひつじさん
こちらこそコメントありがとうございます!
家計管理のお役に立てたようで、とても嬉しく思います。

ブログの方で当記事を紹介くださり、重ね重ねありがとうございます!!

返信する
mm

はじめまして
先日、簿記3級を取得したmm(仮)と申します。
あがぺいさんの複式簿記家計簿を参考に、家計簿と副業の収支をつけ始め3週間が経ちました。あがぺいさんのおかげで日々の収支が丸分かりで便利だし、とても楽しく記帳させていただいてます。

作り方も画像つきで非常に分かりやすく、少し応用しながら作成しましたが、今回は更なる応用編をご教示いただきたくコメントいたしました。

B/S、P/Lに入れる関数について。
あがぺいさんの作成した財務諸表シートでは、B2とB3に参照値である年と月がありますが、私の財務諸表では、年と月は分けず、「○月○日」(検索値タイトルは締め日/対象セルは集計のE1です)だけで参照しています。
あがぺいさんの財務諸表B2.B3にあたる、私の財務諸表では、B1になります。
P/Lの収入行の関数は下記のように作成しました。

=ABS(GETPIVOTDATA(“金額”,集計!$A$1,集計!$E$1,$B$1,”グループ”,”収入”))

現状はこれで、B1セルの「○月○日」を変えるだけで、締め日ごとの財務諸表が仕上がるので大変満足なのですが、、、
更にB1セルに「○○年度」を追加したいと思っています。

そこで集計のピボットテーブルのD1に「年」を追加しました。
同じく仕訳の方にも「年」列を追加し、「○○年度」と記入していってます。

財務諸表シートへ戻り、B1に「○月○日」を入力していますが、ここを「○○年度」でも参照できるように関数を作り替えたいのです。

「○月○日」の時は、E1から参照。
「○○年度」の時は、D1から参照。
といった具合に。

拙い説明で申し訳ありませんが、ご回答のほど宜しくお願い致します。

返信する
あがぺい

mmさんはじめまして。
簿記3級合格おめでとうございます!
エクセル家計簿も実践していただき嬉しいです。

ご質問内容は、「月単位での財務諸表と、年度単位での財務諸表を切り替えたい」だと解釈しました。

「○月○日」の時は、E1から参照。
「○○年度」の時は、D1から参照。
といった具合に。

上記のようにするのであればIF関数でできそうな気がします。
=IF(COUNTIF(B1,”*年度”)=1,D1から参照するGETPIVOTDATA,E1から参照するGETPIVOTDATA)
とかですかね(動作確認はしてません)。

もしできないようでしたら、年度用で別シートを作るのが早いかと思います。
あまりスマートではありませんが、目的は達成できそうです。

ご検討よろしくお願いします!

返信する

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA