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

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

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

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

仮の仕訳入力

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

例

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

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

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

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

（１）シート「仕訳」任意のデータを選択します。

（２）リボン [ 挿入 ] ＞ [ テーブル ] ＞ [ ピボットテーブル ] をクリックします。

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

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

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

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

（４）シート名を「集計」に変更します。

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

• 列：日付
• 行：グループ、科目CD、科目
• 値：金額（合計）

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

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

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

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

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

（９）「月」を選択して、「OK」をクリックします。

（１０）金額を通貨の設定にします。

（１０－１）ピボットテーブルのフィールド「値」の右下三角「▼」をクリックして、「値のフィールド設定」をクリックします。

（１０－２）選択したフィールドのデータが合計になっているか確認します。

（１０－３）[ 表示形式 ]をクリックします。

（１０－４）通貨もしくは会計を選択します。記号表示は好みで設定します。ここでは「なし」に設定。

完成

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

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

簡易版BSとPLの作成

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

合計表の枠を作成する

（１）科目別推移表の左側に7つ列を挿入します。

（２）BSとPLの表を作成します。

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

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

• 1資産（セルC３）：1集計の金額
• ２負債（セルF3）：2集計の金額
• ３元入（セルF4）：3集計の金額
• ４収入（セルF9）：4集計の金額
• ５共通（セルC9）：5集計の金額
• ６仕事（セルC10）：6集計の金額
• a（セルC11）：7集計の金額
• ｂ（セルC12）：8集計の金額

1資産の設定

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

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

（２）式の一部を変更します。

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

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

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

（３）IFERROR関数を設定します。

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

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

この式にする理由

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

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

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

その他の設定

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

2負債（セルF3）

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

=IFERROR(–GETPIVOTDATA(“金額”,$H$3,”日付”,B2,”グループ”,”2″),””)

３元入（セルF4）

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

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

４収入（セルF9）

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

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

５共通（セルC9）

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

６仕事（セルC10）

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

a（セルC11）

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

ｂ（セル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),"")

式の内容を確認する方法

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

（２）数式バーをクリックします。

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

利益または損失の設定

次の設定をします。

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

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),””)

完成

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

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

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

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

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

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

仕訳例

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

仕訳入力の方法

（１）項目「日付」の最終行を選択します。

（２）日付を入力します。

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

D列

E列

H列

（３）科目ＣＤと金額を入力します。

• 科目ＣＤの入力：列とＥ列の式の結果が表示されます。
• 金額の入力：貸借区分が表示されます。

（４）すべての仕訳を入力します。

（５）金額を選択し合計が「０」か確認します。

楽に日付入力する方法

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

Ctrl　+D（Ctrlを押しながら「D」を押す）

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

（２）総計の金額が「０」になっているか確認します。

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

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

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

（１）科目CDの右側にある下三角をクリックします。

（２）昇順を選択します。

→　結果

日常の仕訳例

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

現金と預金の仕訳例

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

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

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

給与の仕訳例

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

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

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

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

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

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

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

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

（１）費用計上したい月内に未払費用を計上する。

（２）振替日に費用計上する。

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

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

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

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

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

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

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

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

（２）科目CDのBS科目のみ選択し、コピーします。科目CD100～300まで。

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

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

（３）シート「仕訳」のC列の最終行の次のセルを選択します。

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

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

→　値のみ貼り付ける。

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

（５）シート「集計」へ戻り、金額をコピーします。

（６）シート「仕訳」の金額欄へ値のみを貼り付けます。

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

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

（９）利益の場合は、金額をマイナスにします。

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

（１０）Ｆ列に貼り付けた金額が「０」の場合は、行削除します。

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

（１２）A列に「K」と入力します。

（１３）Ｂ列に翌月1日の日付を入力します。

（１４）G列は自分でわかる内容を入力します。

（１５）最後にピボットテーブルのグループの小計を表示するに戻します。

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

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

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

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

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

（２）ピボットテーブルのデータ内を選択します。

（３）右クリックより、「更新」を選択します。

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

ＢＳとＰＬの確認

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

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

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

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