【家計管理】エクセルで複式簿記の家計簿を作る方法｜第四回：締め切りと繰り越し

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

第四回は締め切りと繰り越しです。

あがぺい

仕訳帳と集計表の連携が大事になります！

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

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

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

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

あっ！とごはんを見てみる

エクセル複式家計簿の締め切り

締め切りとは？

締め切りとは、ある期間の記入を整理して終わらせることです。

簿記3級では企業の決算を前提としているので、年で区切っていました。
一年間の売上と仕入れなどの支出を整理して年間の営業利益などを計算していたと思います。

でも、家計簿はひと月ごとに区切って黒字か赤字かチェックするのが主流ですよね。
というわけで複式簿記の家計簿もひと月ごとに締め切っていくことにします。

収益と費用は次月に持ち越さないで、ひと月分で一区切りしましょ

簿記3級で習ったやり方とはちょっと異なりますが、締め切りの工程は以下のふたつです。

それではエクセルを使いながら説明していきます！

具体的な締め切り方法

エクセル複式家計簿を使って、2021年9月の締め切りをやっていきます。

9月のすべての取引が下の画像のように終わったとして進めていきますね。

もちろん値はダミーですよ

損益と純資産の仕訳を入力する

まずは締め切りの準備として、仕訳帳の最後尾（月末）に損益と純資産を入力します。

日付は9月30日とします。

損益の金額を入力する

損益の金額列（この例だとF49セル）に9月の利益を入力していきます。
このとき、ピボットテーブルを参照して利益を計算します。

9月の利益は以下の式で算出できます。

（9月の利益）＝ー｛（9月の収入）＋（9月の費用）｝

※簿記だと収入がマイナスで、費用がプラスになっています。符号を逆転させるためにマイナスで囲います。

F49セルに「=」を入力したあと、”集計表”ワークシートのピボットテーブル内にある「4_収入 集計」の9月の金額をクリックします（赤矢印が指すセル）。

そうすると自動でGETPIVOTDATA関数が入力され、9月の収入の合計値-325,000を参照できるようになります。

さらに「+」を押して、「5_費用 集計」の金額をクリック（赤矢印が指すセル）。
これで9月の費用の合計値175,930を参照できます。

最後に式全体をマイナスで囲えば、損益の計算が完了します。
式は、
=-(GETPIVOTDATA(“金額”,集計表!$A$1,”年”,2021,”日付”,9,”グループ”,”4_収入”)+GETPIVOTDATA(“金額”,集計表!$A$1,”年”,2021,”日付”,9,”グループ”,”5_費用”))
となりました。

赤字のときは？

当月が赤字のときは、純資産が借方（プラス）に、損益が貸方（マイナス）になります。

純資産の金額を入力する

純資産に入力する金額は、損益の符号を逆にした値になります。
この例だと「=-F49」と入力します。

締め切り完了

損益を純資産に振り替えることができました。
これで9月の家計簿は締め切りました！

ピボットテーブルを更新する

最後にピボットテーブルを更新しましょう。
利益の欄が追加されていることが分かります。

必ず一番下の総計がゼロになっていることを確認してくださいね。

例だと利益が149,070円のプラスになっています。大幅な黒字ですね♪

エクセル複式家計簿の繰り越し

繰り越しとは？

繰り越しとは、資産・負債・純資産を次期に引き継ぐことです。

収入や費用は月ごとでキッカリ締め切りますが、資産や負債は未来へと続くものです。
今月の資産は来月に、来月の資産は来々月に引き継がれていきます。

資産と負債は何年も継ぎ足してきた秘伝のタレみたいなものです

繰り越しの工程は以下のふたつです。

それではエクセルを使いながら説明していきます！

具体的な繰り越し方法

では、さきほど締め切った9月の資産を10月に繰り越していきます。

9月の仕訳帳は下の画像のように締め切りましたよ。

仕訳帳の10月1日に資産・負債・純資産をすべて記入する

まずは10月の始めに資産・負債・純資産を引き継ぐため、仕訳帳に科目を記入しましょう。

抜け漏れがないよう”勘定科目リスト”ワークシートから科目をコピーするのが便利です。

”仕訳帳”ワークシートに貼り付けます。
日付は10月1日とします（かならず1日にしてくださいね）。

9月の残高を記入する

コピーしたすべての科目について、9月の残高を記入していきます。

現金の金額のところ（F51セル）に「=」を入力し、”集計表”ワークシートにあるピボットテーブルから9月の現金の欄をクリックします。

自動でGETPIVOTDATA関数に変換されるので、エンターを押します。
これで9月の現金の残高 123,370円が参照できるようになりました。

すべての9月の残高を記入する

現金以外の科目についても同様の操作を行います。

「＝」を入力してピボットテーブルをクリックして・・・を繰り返し、下の画像のような仕訳帳が完成しました。

ピボットテーブルを更新する

最後に集計表のピボットテーブルを更新すれば、10月の資産・負債・純資産が表示されるようになります。
これで繰り越し作業は完了です！

繰り越しが済めば、あとは通常の取引を記帳するだけ！
エクセル複式家計簿は以下のループを繰り返して、帳簿していきます。

以上で締め切りと繰り越しの説明は終わりです。ここから先は応用編になります。

応用編）GETPIVOTDATA関数を改良して時短する

こんな作業を毎月やるのはダルい！
もっと賢くやろうよ

この章では応用編としてGETPIVOTDATA関数をいじっていきます。

毎月の締め切りや繰り越し作業で、ピボットテーブルを開いて、クリックして、エンター押して、、、って面倒ですよね。
資産の科目が増えたら時間もかかります。

というわけで、GETPIVOTDATA関数をちょこっと改良して、締め切りと繰り越しをラクする方法をご紹介します。

GETPIVOTDATA関数の中身

ひとまずGETPIVOTDATA関数の中身を見てみましょう。

例えば9月の現金残高を参照したGETPIVOTDATA関数はこのようになっていました。

書き出すと、=GETPIVOTDATA(“金額”,集計表!$A$1,”年”,2021,”日付”,9,”科目CD”,100,”グループ”,”1_資産”,”科目”,”現金”) です。

関数自体は長いものの、実は構成は単純なんです。

この4つを使って、ピボットテーブルのどの数値を取ってくるかを決めているだけの関数です。

データフィールドとピボットテーブル

GETPIVOTDATA関数のデータフィールドとピボットテーブルは「どのピボットテーブルからどんなデータを取ってくるか」を伝える欄です。

上の例では

となっています。これを言語化すれば

”集計表”ワークシートのA1セルにあるピボットテーブル（赤枠）の”金額”欄（青枠）からデータを取ってきてください

というメッセージになります。

フィールドとアイテム

GETPIVOTDATA関数のフィールドとアイテムは「どの見出しの中のどの項目を取ってくるか」を決める欄です。

上の例では

となっていて、これを言語化すれば、

”年”という見出しの中で”2021″に当てはまるもの

ということになります。

いわばフィルターの役割をしています。
各見出しの各項目を絞って、コレダ！という1つのデータを引っ張ってきます。

アイテムをセル参照にすればイイ！

GETPIVOTDATA関数の中身のうち、アイテムをセル参照にして締め切り・繰り越し作業を簡単にします。

データフィールド・ピボットテーブル・フィールドは固定なので、いじる必要はありません。

アイテムは年・月・科目で変わりますからね。そこをセル参照や関数で表現してしまいましょう。

締め切りをカンタンにしよう

締め切りの例で挙げた、9/30に記入した損益の金額セルは以下の数式が入っていました。

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

このうち毎月変化するアイテムは赤字の部分です。

赤字の部分をセル参照にして作ったのが下の式です。

=-(GETPIVOTDATA(“金額”,集計表!$A$1,”年”,A49,”日付”,MONTH(B49),”グループ”,”4_収入”)+GETPIVOTDATA(“金額”,集計表!$A$1,”年”,A49,”日付”,MONTH(B49),”グループ”,”5_費用”))

年フィールド

年フィールドに入力すべきは当月は西暦何年か？ です。
”2021”という情報は一番左のA列にありますので、同じ行のA列のセル（A49）を入力します。

日付フィールド

日付フィールドに入力すべきは当月が何月か？ です。
”9”という値を出すために、B列の日付から月の情報だけを抽出できるMONTH関数を使ってMONTH(B49)とします。

これによって、左の情報（年・日付）からピボットテーブル中の該当するデータを取ってきてくれるようになりました。

毎月末にコピペするだけです！

繰り越しをカンタンにしよう

繰り越しの方もセル参照を取り入れましょう。

10月へ9月の現金残高を繰り越すときに使った数式は以下のようなものでした。

=GETPIVOTDATA(“金額”,集計表!$A$1,”年”,2021,”日付”,9,”科目CD”,100,”グループ”,“1_資産”,”科目”,“現金”)

締め切りと同じように、赤字の部分をいじります。

それぞれのアイテムをセル参照にした式がこちらです。

=GETPIVOTDATA(“金額”,集計表!$A$1,”年”,YEAR(B51-1),”日付”,MONTH(B51-1),”科目CD”,C51,”グループ”,D51,”科目”,E51)

年フィールド

注意すべきは前月の情報を拾ってこなければならないという点。
2021年10月1日（B51セル）のひと月前の西暦を計算するため、YEAR(B51-1)とします。

日付フィールド

こちらも前月の情報が必要です。
B列の日付からひと月前の月を抽出するように、MONTH(B51-1)とします。

科目CD、グループ、科目フィールド

これらは素直に同じ行の情報を入力します。
順に、C51, D51, E51です。

これで、左の情報（年・日付・科目CD・グループ・科目）からピボットテーブル中の該当するデータを取れるようになりました。

”現金”以下のセルにもこの数式をコピーするだけでそれぞれの金額を拾ってきてくれます（セル参照を固定していなければ）。

ピボットテーブルをカチカチとクリックする必要がなくなりました！

以上で、エクセルで複式簿記の家計簿を作る方法の第四回：「締切と繰越」を終わります。

あがぺい

お疲れさまでした！

>>>最終回（第五回）：損益計算書と貸借対照表

[補足資料] 締め日を任意の日にする方法

上記の説明では月末で締め、月初に損益を振り替えていました。しかし、給料日などに合わせて締め日を変更したい方もいらっしゃると思います。

ここでは例として、締め日を毎月25日にする方法を紹介します。

やることは以下の2つです。

ちょいと手間ですが頑張りましょう

① C列に締め日用の日付を追加する

まずは”仕訳帳”ワークシートのC列に1列挿入します。見出しは「締め日」とします。

C2セルに=IF(DAY(B2)<=25,B2,DATE(YEAR(B2),MONTH(B2)+1,1))と入力します。

下にもコピーします。これで26日~月末までの取引が翌月のものとして集計されるようになりました。

② C列を使ってピボットテーブルを集計する

C列を使ってピボットテーブルを作っていきます。詳しくは【家計管理】エクセルで複式簿記の家計簿を作る方法｜第三回：集計表をご参照ください。ここでは変化点だけ説明します。

ピボットテーブルのフィールドで、列を「締め日」にします。

次に、ピボットテーブル本体の締め日あたりを右クリックして「グループ化」をクリック。「年」「月」を選択してOKを押します。

これにて25日締めの集計表が完成です！

お疲れ様でした！