この記事には広告を含む場合があります。記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
我が家で使っている、エクセルを使った複式簿記の家計簿(エクセル複式家計簿)を作る方法を紹介します。
第五回は損益計算書(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_費用”)

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%にします。

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


グラフタイトル、凡例、横軸なんかは消してしまいましょう
見た目では分かりにくい金額をグラフ内に表示するため、データラベルを追加します。
[グラフのデザイン]→データラベル→中央をクリックします。
系列名と値にチェックをつけます。

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

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

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

ピボットテーブルを更新すると、こちらのグラフも自動で更新されますよ。
スポンサーリンク
エクセル複式家計簿 貸借対照表の作り方
続きまして、エクセル複式家計簿の貸借対照表を作っていきましょう
”財務諸表”ワークシートに貸借対照表の元となる表を書きます。
下の画像のように、損益計算書の右側を使いました。
項目は資産・負債・純資産・損益とし、右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」を3年位使用していますが、自分ですべて作ってみたい気持ちが湧いてきました。
「あがぺいブログ」を読んで説明が分かりやすく、取り組んでみようと思います。自分は、78歳の後期高齢者です。格好のテーマが見つかり当分の間楽しめそうです。ゴールまで行けるかどうか分かりませんが挑戦してみます。
これからの「あがぺいブログ」の発展をお祈りします。ありがとうがざいました。
お力になれて光栄です!
山口さんの挑戦する姿に刺激を受けました。
私もよりよいブログを書き続けられるよう努力していきます。
ご不明点ございましたら、お気軽にご連絡くださいませ!
第1回から第5回まですべて読み、今もあがぺい式複式簿記を実践しています。自分の家計を見つめなおすことができて、大人として成長したなあと実感できました。
細部まで読者のことを考えている投稿ですばらしかったです。
ありがとうございました!
自分も複式簿記を勉強し、学習したことをブログにしてみたので、よかったら遊びに来てください。(記事名「複式簿記のエッセンス」)
井上ひつじさん
こちらこそコメントありがとうございます!
家計管理のお役に立てたようで、とても嬉しく思います。
ブログの方で当記事を紹介くださり、重ね重ねありがとうございます!!