PC関連

家計簿の集計表作成・仕訳入力方法・月次繰越処理

2020-11-03

エクセルで自作する家計簿の集計表作成方法です。

「仕訳」データをもとに科目別推移表(集計表)をピボットテーブルで作成します。

一度設定すれば簡単に更新できます。

仮の仕訳入力

集計表を作成するために、仮の仕訳を入力します。グループそれぞれの適当な仕訳を入力します。※集計設定が終わったら消します。

エクセル家計簿・複式簿記・仕訳例

仕訳例

仕訳入力は項目「日付」や「科目CD」を入力すると、項目「グループ」と「科目」の式は自動で設定されます。

またテーブルの範囲は自動で拡張されます。

仕訳データの集計一覧・科目別推移表

仕訳データから科目別推移表(集計表)を作成します。

(1)シート「仕訳」任意のデータを選択します。

(2)リボン [ 挿入 ] > [ テーブル ] > [ ピボットテーブル ] をクリックします。

エクセル家計簿・複式簿記・ピボットテーブル

ピボットテーブル

→ 「ピボットテーブルの作成」が表示されます。

(3)テーブル範囲が正しいか確認し、「OK」をクリックします。

テーブル設定で「shiwake」に名前変更したので、「shiwake」が選択されています。

エクセル家計簿・複式簿記・ピボットテーブル

→ 新しいシートにピボットテーブルの作成が設定されます。

エクセル家計簿・複式簿記・ピボットテーブル

(4)シート名を「集計」に変更します。

エクセル家計簿・複式簿記・ピボットテーブル

(5)ピボットテーブルのフィールドで「列」と「行」と「値」を設定します。金額は合計にします。

エクセル家計簿・ピボットテーブル

ピボットテーブルのフィールド

  • 列:日付
  • 行:グループ、科目CD、科目
  • 値:金額(合計)

(6)レイアウトを設定します。ピボットテーブルのデータ内を選択し、リボン [ デザイン ] > [ レイアウト ] で設定します。

  • 小計:小計を表示しない
  • 総計:列のみ集計を行う
  • レポートのアウトライン:表形式で表示

エクセル家計簿・複式簿記・ピボットテーブル

スタイル

(7)ピボットテーブルの「グループ」を選択し、「グループの小計」をクリックします。

エクセル家計簿・複式簿記・ピボットテーブル

グループ

(8)日付を月でグループ化します。ピボットテーブルのデータ内を選択し、リボン [ ピボットテーブル分析 ] > [ グループの選択 ] をクリックします。

エクセル家計簿・複式簿記・ピボットテーブル

グループの選択

「グループ化」が表示されます。

(9)「月」を選択して、「OK」をクリックします。

エクセル家計簿・複式簿記・ピボットテーブル

グループ化

(10)金額を通貨の設定にします。

(10-1)ピボットテーブルのフィールド「値」の右下三角「▼」をクリックして、「値のフィールド設定」をクリックします。

エクセル家計簿・複式簿記・ピボットテーブル

値のフィールド設定

(10-2)選択したフィールドのデータが合計になっているか確認します。

(10-3)[ 表示形式 ]をクリックします。

エクセル家計簿・複式簿記・ピボットテーブル

値のフィールド設定

(10-4)通貨もしくは会計を選択します。記号表示は好みで設定します。ここでは「なし」に設定。

エクセル家計簿・複式簿記・ピボットテーブル

セルの書式設定

完成

ピボットテーブルのスタイルは好みで変更してください。

集計

最終行の「総計」は「0」になっているのが正解です。

エクセル家計簿・複式簿記・BS

総計

簡易版BSとPLの作成

グループそれぞれの合計金額だけ表示するBSとPLの設定をします。

合計表の枠を作成する

(1)科目別推移表の左側に7つ列を挿入します。

エクセル家計簿・複式簿記・ピボットテーブル

科目別推移表

(2)BSとPLの表を作成します。

エクセル家計簿・複式簿記

BSとPL

グループの合計金額を表示する

BSとPLのグループのセルに科目別推移表の数字を表示させます。

  • 1資産(セルC3):1集計の金額
  • 2負債(セルF3):2集計の金額
  • 3元入(セルF4):3集計の金額
  • 4収入(セルF9):4集計の金額
  • 5共通(セルC9):5集計の金額
  • 6仕事(セルC10):6集計の金額
  • a(セルC11):7集計の金額
  • b(セルC12):8集計の金額

1資産の設定

(1)セルC3に「=」と入力して、1集計の金額(セルK12)をクリックし、Enterキーを押します。

→ GETPIVOTDATA関数が設定されます。

エクセル家計簿・複式簿記

式設定

(2)式の一部を変更します。

=GETPIVOTDATA("金額",$H$3,"日付",4,"グループ","1")

日付の後の数字「4」を「B2」に変更します。

=GETPIVOTDATA("金額",$H$3,"日付",B2,"グループ","1")

(3)IFERROR関数を設定します。

=IFERROR(GETPIVOTDATA("金額",$H$3,"日付",B2,"グループ","1"),"")

ここで説明している表と全く同じに作成している場合は、上記の式をコピーしてセルC3に貼り付けてもOKです。

この式にする理由

科目の金額が0円の場合は、エラー表示になるため。

以下はIFERROR関数を設定なしで800番台の仕訳がない場合のエラー例

エクセル家計簿・複式簿記

エラー表示

費用・グループ「8」と損益セル:数式が有効でないセルを参照しているエラー「#REF」が表示される。

その他の設定

1資産以外の式を設定します。

2負債(セルF3)

負債は「GETPIVOTDATA」の前に「-」を入れます。

=IFERROR(+-GETPIVOTDATA("金額",$H$3,"日付",B2,"グループ","2"),"")

3元入(セルF4)

負債は「GETPIVOTDATA」の前に「-」を入れます。

=IFERROR(+-GETPIVOTDATA("金額",$H$3,"日付",B2,"グループ","3"),"")

4収入(セルF9)

  • 収入は「GETPIVOTDATA」の前に「-」を入れます。
  • PL科目の場合は、日付の数字を「B8」に変更します。

=IFERROR(+-GETPIVOTDATA("金額",$H$3,"日付",B8,"グループ","4"),"")

5共通(セルC9)

=IFERROR(GETPIVOTDATA("金額",$H$3,"日付",B8,"グループ","5"),"")

6仕事(セルC10)

=IFERROR(GETPIVOTDATA("金額",$H$3,"日付",B8,"グループ","6"),"")

a(セルC11)

=IFERROR(GETPIVOTDATA("金額",$H$3,"日付",B8,"グループ","7"),"")

b(セルC12)

=IFERROR(GETPIVOTDATA("金額",$H$3,"日付",B8,"グループ","8"),"")

利益または損失の設定・BS資産の設定

BS資産に次の設定をします。

  • 利益または損失の表示
  • 利益または損失の金額表示

セルB5

=IF(C5="","","損失")

セルC5

=IF(SUM(C3:C4)-SUM(F3:F4)<0,SUM(F3:F4)-SUM(C3:C4),"")

式の内容を確認する方法

(1)上記の式をコピーして、BS表のセルC5に貼り付けます。

(2)数式バーをクリックします。

エクセル家計簿・複式簿記・式の確認

計算式

式の参照先を確認できます。

利益または損失の設定

次の設定をします。

  • 利益または損失の表示
  • 利益または損失の金額表示

BS負債の設定

セルE5

=IF(F5="","","利益")

セルF5

=IF(SUM(C3:C4)-SUM(F3:F4)>0,SUM(C3:C4)-SUM(F3:F4),"")

PL費用の設定

セルB13

=IF(C13="","","利益")

セルC13

=IF(SUM(C9:C12)-SUM(F9:F12)<0,SUM(F9:F12)-SUM(C9:C12),"")

PL収入の設定

セルE13

=IF(F13="","","損失")

セルF13

=IF(SUM(C9:C12)-SUM(F9:F12)>0,SUM(C9:C12)-SUM(F9:F12),"")

完成

エクセル家計簿・複式簿記

完成

最後に仮で入力した仕訳を消します。

設定は以上で終わりです。

この他、仕訳データをもとに、残高試算表等作成することも可能です。

仕訳の入力方法と振替仕訳

シート「仕訳」に仕訳を入力する方法です。普段からエクセルを使っている人には説明不要な内容です。

テーブル機能の設定をしてあるので、入力は簡単です。

仕訳例

同一預金内の振替仕訳例です。

エクセル複式簿記家計簿・仕訳

仕訳例

同一口座内でなぜ分けるのか

上記の例は、クレジットカード「VISA]と「JCB」の支払い分を分けています。実際の預金口座はひとつです。このように分けておくとクレジットカードの支払い分をあらかじめ用意できます。詳しくはクレジットカードの管理記事で載せる予定。

仕訳入力の方法

(1)項目「日付」の最終行を選択します。

エクセル家計簿・仕訳入力

仕訳例

(2)日付を入力します。

→ テーブル範囲が自動で拡張されます。また、計算式が設定されている列は自動で設定されます。

D列

エクセル家計簿・仕訳入力

グループ

E列

エクセル家計簿・仕訳入力

科目

H列

貸借

(3)科目CDと金額を入力します。

  • 科目CDの入力:列とE列の式の結果が表示されます。
  • 金額の入力:貸借区分が表示されます。

エクセル家計簿・仕訳入力

入力

(4)すべての仕訳を入力します。

エクセル家計簿・仕訳入力

入力

(5)金額を選択し合計が「0」か確認します。

エクセル家計簿・仕訳入力

金額確認

楽に日付入力する方法

最後のセルと同じ日付の場合

Ctrl +D(Ctrlを押しながら「D」を押す)

最後のセルの翌日日付の場合

最終行に入力されている日付セルの右下にマウスをポイントします。四角い印が表示されたら、下にドラッグします。

エクセル・連速データの入力

日付

→ 翌日の日付が設定されます。

日付

同じ日付を複数行に設定する場合は、次の方法が楽。

日付を入力し、入力した日付をコピーします。

エクセル・コピー

日付

同じ日付を設定したいセルを選択し、Enterキーを押します。

日付

ステータスバーに合計が表示されない場合

ステータスバーを右クリックし、「合計」をクリックします。選択されているものはチェックマークがはいっています。

エクセル家計簿・複式簿記・振替仕訳

合計

合計が「0」でない場合の集計結果

例:未払金・VISAの金額が誤っている

エクセル家計簿・仕訳入力

集計

  • シート「集計」の月別科目別推移表の総計が「0」になっていない。

集計

  • 簡易版BS・PLの損失額(または利益)に差がある

集計

シート「集計」のデータ更新

シート「集計」を選択します。

ピボットテーブルのデータ更新

(1)ピボットテーブルのデータ範囲内を選択し、右クリックより「更新」をクリックします。

エクセル家計簿・複式簿記・振替仕訳

更新

または、リボン[ ピボットテーブル分析 ] タブ >[ 更新 ] をクリックします。

エクセル家計簿・複式簿記・振替仕訳

更新

(2)総計の金額が「0」になっているか確認します。

エクセル家計簿・複式簿記・BS

更新

(3)簡易版BS・PLの損失額、または利益額が一致しているか確認します。

科目が番号順になっていない場合

科目CDが番号順に並んでいない場合は、データを並べ替えします。

エクセル家計簿・複式簿記・振替仕訳

科目

(1)科目CDの右側にある下三角をクリックします。

エクセル家計簿・複式簿記・振替仕訳

科目CD

(2)昇順を選択します。

エクセル家計簿・複式簿記・振替仕訳

科目CD

→ 結果

エクセル家計簿・複式簿記・振替仕訳

科目CD

日常の仕訳例

日常でよくある仕訳の例です。

現金と預金の仕訳例

現金で食品等を購入した。

エクセル家計簿・複式簿記・日常仕訳

仕訳例

預金口座から現金を引き出した。

エクセル複式簿記家計簿・仕訳

仕訳例

クレジットカードの引き落としがあった。「引落」項目に引落日付を入力します。

エクセル複式簿記家計簿・仕訳

仕訳例

給与の仕訳例

給料で引かれる年金等は費用計上します。

エクセル複式簿記家計簿・仕訳

仕訳例

金額はサンプル用で実際の数字ではありません。

口座振替・月末振替だが振替日が翌月になる月の場合

毎月の振替は月末だけど、月末が休日で翌月振替になる場合の仕訳は2通り。

振替日に費用計上する方法

この方法は、費用計上がない月と2か月分の費用計上がある月になります。

家計簿なのでそこまで厳密でなくてもよいならこの方法の方が楽。

未払費用で計上し、振替日に費用計上する方法

(1)費用計上したい月内に未払費用を計上する。

エクセル複式簿記家計簿・仕訳

仕訳例

(2)振替日に費用計上する。

エクセル複式簿記家計簿・仕訳

仕訳例

2回計上があるため、面倒くさいです。

月次処理・翌月にBS科目の残高を繰り越す

月末までの入力が終わったら、BS科目の残高を繰越します。

この処理をしないと、翌月のBS科目の残高が0から開始になります。処理はとても簡単です。

処理内容

  • BS科目の科目CDと金額をコピーして、シート「仕訳」に貼り付けます。
  • 損益の金額を金額をコピーして、シート「仕訳」に貼り付けます。

ここでの例は4月の月次処理です。コピー&ペーストの方法はいくつかあるので、自分のやりやすい方法で構いません。ここでの方法は参考にしてください。

(1)シート「集計」を選択します。

(2)ピボットテーブルの「グループ」を右クリックして、「グループの小計」をクリックします。

小計の金額は不要のため、この処理を飛ばしデータを貼り付けてから小計の金額のある行削除でも構いません。

エクセル家計簿・月次処理

繰越

(2)科目CDのBS科目のみ選択し、コピーします。科目CD100~300まで。

コピーのショートカット:Ctrl を押しながら「C」を押す。

エクセル家計簿・月次処理

繰越

上の例では、小計を表示したままでコピーしています。

(3)シート「仕訳」のC列の最終行の次のセルを選択します。

最終行へ素早く移動:セルC1を選択し、Ctrキーを押しながら「↓」を押す。

(4)右クリックして、貼り付けオプションの「123」をクリックします。

→ 値のみ貼り付ける。

エクセル家計簿・月次処理

繰越

→ 値のみ貼りつき、データの範囲が拡張されます。関数は自動設定されます。

エクセル家計簿・月次処理

繰越

(5)シート「集計」へ戻り、金額をコピーします。

(6)シート「仕訳」の金額欄へ値のみを貼り付けます。

エクセル複式簿記家計簿・仕訳

繰越

(7)シート「集計」へ戻り、損益に表示されている金額をコピーします。ここでの例は利益の金額です。

エクセル家計簿・月次処理

繰越

(8)シート「仕訳」のF列の最終行の次のセルに値のみ貼り付けます。

エクセル家計簿・月次処理

繰越

(9)利益の場合は、金額をマイナスにします。

BSとPLの貸方金額を表示をプラスにしているため。

エクセル家計簿・月次処理

繰越

(10)F列に貼り付けた金額が「0」の場合は、行削除します。

(11)貼り付けた金額を選択し、合計が「0」になるか確認します。

エクセル家計簿・月次処理

繰越

(12)A列に「K」と入力します。

(13)B列に翌月1日の日付を入力します。

(14)G列は自分でわかる内容を入力します。

(15)最後にピボットテーブルのグループの小計を表示するに戻します。

シート「集計」:ピボットテーブルの「グループ」を右クリックして、「グループの小計」をクリックします。

繰越処理は以上で終わりです。

シート「集計」の値の確認

まずはピボットテーブルのデータ更新を行います。

(1)シート「集計」を選択します。

(2)ピボットテーブルのデータ内を選択します。

(3)右クリックより、「更新」を選択します。

BS科目の金額が翌月に繰り越していることを確認します。

エクセル家計簿・月次処理

確認

BSとPLの確認

セルB2に翌月の数字を入力します。ここでは「5」を入力します。

BSの金額が更新され、PLの金額は0になっています。

エクセル家計簿・月次処理

確認

月次処理は以上で終わりです。

続き→家計簿のクレジットカード払い管理はエクセル家計簿でできる

-PC関連

© 2021 オフィスのQ&A