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

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

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

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を選択して、縦棒グラフを挿入します。

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