【家計管理】エクセルで複式簿記の家計簿を作る方法|第三回:集計表

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

第三回は集計表を作っていきます。

エクセル複式家計簿では仕訳の集計にピボットテーブルを利用します。
「ピボットテーブル?」という方にも伝わるよう、スクリーンショットをたくさん用意しました。

あがぺい
あがぺい

ピボットテーブルって意外と使う機会がないんですよね。私は家計簿を機に使い方を学びました。

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

エクセル複式家計簿シリーズ

第一回:勘定科目リストをつくる
第二回:仕訳帳をつくる
第三回:集計表をつくる ←この記事
第四回:締め切りと繰り越し
第五回:損益計算書と貸借対照表をつくる

「家計簿を自作するのは大変そう……」

と思ったあなたには最新の家計簿アプリがオススメ!

銀行口座や証券口座と連携できるので、かんたんに家計管理ができますよ。

※基本無料で使えます

\ 人気No.1 家計簿アプリ/

タップするとアプリストアへ移ります

エクセル複式家計簿の集計表の全体像

まずはどんな集計表を作るのかについて、全体像を確認します。

この記事では下の画像のような集計表を作っていきます。
特徴を一言でいうと「ピボットテーブルを利用して月ごとに各科目の合計金額をまとめる」です。

完成した集計表

だいたいの家計簿は月で締めていると思います。
この集計表を使えば、月ごとの損益計算書・貸借対照をつくることができるようになりますよ。

それでは具体的な話に移っていきましょう!

ピボットテーブルとは

ピボットテーブルってなに?

ここで一度、ピボットテーブルについて簡潔にご紹介します。

ピボットテーブルは集計ツール

ピボットテーブルはデータの集計を楽にしてくれる機能です。
膨大なデータを集計して様々な側面からデータを見せてくれます。

家計簿であれば、

  • 月次の支出、年次の支出
  • 項目ごとの支出、グループごとの支出
  • 支出の合計、支出の平均

などなど。どんな情報を得たいかによって集計の切り口を変えることができる便利なツールです。

データをインフォメーションに昇華させるツールですね!

ピボットテーブルの注意点

ピボットテーブルを扱う上で、絶対に守らなければならないルールがあります。
そのルールとは「元になるデータがリスト形式であること」です。

リスト形式といってもよくわからないので、ダメな例をいくつか挙げました
ピボットテーブルの元になる仕訳帳では下記のこと、厳禁ですよ!

  • セルの結合
  • 空白セル
  • 表記ゆれ
  • 誤入力
不都合な元データ
▲ダメな仕訳帳の例

これらが元のデータにあるとうまく集計してくれません。
ピボットテーブルにとって集計しやすいデータリストを作ってあげることが大切です。

第二回で解説した仕訳帳は、誤入力を未然に防ぐ仕組みを取り入れています。

・テーブル化しているのでセルの結合が禁止されている

・入力規則により、表記ゆれの可能性がない

★ピボットテーブルの基本をより詳しく知りたい方はこちらの書籍がオススメ!

エクセル複式家計簿の集計表を作る

ピボットテーブルの概要はご理解いただけましたか?
それでは具体的な集計表の作り方を見ていきましょう。

集計表の作り方

”集計表”のワークシートを開く
タブの選択

集計表にピボットテーブルを作成したいので、”集計表”ワークシートを選びます。

[挿入]タブからピボットテーブルを選ぶ
ピボットテーブルの場所を提示

上のタブからピボットテーブルを選択します。

ピボットテーブルのデータ元と作成場所を入力する
分析するデータの設定画面

分析するデータは”仕訳帳”ワークシートの「shiwake」と名付けたテーブルになります。
別の名前をつけている場合はそれに合わせてくださいね。

ピボットテーブルの作成場所は「集計表!$A$1」でいいと思います。(”集計表”ワークシートのA1セルってことです)

できたらOKを押します。

ピボットテーブルの素ができる
ピボットテーブル作成の画像

OKを押すと、ピボットテーブルの素ができます。
右のフィールドリストを使って行や列の設定をおこないます。

行を設定する
フィールドの設定画面(行)

行(縦軸)には科目類を入れていきます。
ドラッグ&ドロップで動かせます。

このとき、上から[グループ>科目CD>科目]の順に並べるとキレイにまとまります。

列を設定する
フィールドの設定画面(列)

列(横軸)には時系列を入れます。
上から順に[年>日付]の順に並べましょう。

値を設定する
フィールドの設定画面(値)

表の中には合計金額を表示させたいので、金額を入れます。
デフォルトで合計になるはずです。

集計表の第一形態が完成
完成したピボットテーブル

これにて集計表の素が完成しました!

・・・なんか見づらくない?

今のままでは表を眺めても「なんのこっちゃ?」なので、体裁を整えていきましょう。

ピボットテーブルの見た目を整える

ピボットテーブルの設定をいじって、見た目をシンプルにします。

フィールドの小計を消す

小計が多すぎて分かりにくいので、科目CDの小計は消してしまいましょう。
ピボットテーブルのフィールドから「科目CD」のフィールドの設定を開きます。

科目CDの設定画面

小計を「なし」にします。

小計の設定画面

これで科目CDにおける小計が表示されなくなりました。

小計を消したピボットテーブル

同様に「年」の小計もいらないと思うので、消しましょう。

行の総計を消す

行の総計(一番右の列)も特に必要ないので消しておきます。

[ピボットテーブル分析]タブからオプションを開き、[集計とフィルター]の「行の総計を表示する」のチェックを外します。

オプションの場所
総計の設定画面
列の総計は表示する

列の総計(一番下の行)は残しておきましょう。
仕訳が正しく記入できているか確認する目印になります。

日付を月でまとめる

横軸が9月12日・9月13日・・・と一日単位で分かれています。
細かくしすぎても解釈が難しくなるだけなので、月単位でまとめちゃいましょう。

どこか任意の日付のセルをクリックして(アクティブなフィールドを日付にして)、[ピボットテーブル分析]タブから「グループの選択」をクリックします。

グループの選択の場所

単位を「月」にします。

グループ化の設定画面

これで9月がひとまとまりになりました。

月でまとめたピボットテーブル

レポートのレイアウトを表形式にする

行(縦軸)が複数行にわたって表示されると見にくいので、1行にきゅっとまとめます。
[デザイン]タブからレポートのレイアウトを開き、「表形式で表示」を選択します。

デザインの設定画面

これで科目が隙間なく並ぶようになりました。

表形式のピボットテーブル

合計/金額のマイナスを赤字にする

金額のプラスマイナスを認識しやすくするため、マイナスは赤字で表示するようにします。
ピボットテーブルのフィールドから「合計/金額」の右の▼をクリックし、「値フィールドの設定」を開きます。

値フィールドの設定

左下の「表示形式」を開きます。

値フィールドの設定画面

通貨を選択し、記号=なし、負の数の表示形式-1,234を選んで、OKを押します。

セルの書式設定

これでプラスマイナスの見分けがつきやすくなりました。

書式設定後のピボットテーブル

科目CDを昇順に並べる

仕訳が増えてくると、科目CDの順番がごちゃごちゃになることがあります。
数字の小さい順に並ぶように設定しておきましょう。

科目CDが無秩序に並んでいる

ピボットテーブルの「科目CD」の▼をクリックし、「昇順」を選びます。

並べ替え設定

これでバッチリ並ぶようになりました。

昇順化

ピボットテーブルは自由に形を変えることができます。まずは我が家の家計簿を参考にしていただき、その後、自分好みの形にアレンジしてみてくださいね。

ピボットテーブルの使い方

最後にピボットテーブルの使い方をお伝えします。

やることは以下の2つだけです。
体裁を整えてしまえば、ピボットテーブルを操作することはほとんどありません。

  1. 仕訳を入力したら更新する
  2. 列の総計がゼロになっていることを確認する

仕訳を入力したら更新する

ひとつは仕訳を入力したらピボットテーブルを更新すること

日々、”仕訳帳”ワークシートに取引を入力することになりますが、入力後、都度ピボットテーブルを更新しなければなりません。

  • ピボットテーブル上で右クリックする
  • [ピボットテーブル分析]タブから更新する

の2通りのやり方があります。

更新ボタン
更新ボタン

お忘れなく!

列の総計がゼロになっていることを確認する

ピボットテーブルの更新後、必ず一番下の総計をチェックしましょう。
ここがゼロになっていない場合、仕訳帳に入力ミスがあると分かります。

不具合の確認

この例では、仕訳帳で入力する数値に誤りがあったようです(-1500と入力すべきところが-150になっていた)。

仕訳帳の不具合

複式簿記は借方と貸方でプラマイゼロになるのが原則ですので、常にチェックするようにしてくださいね。


以上で、エクセルで複式簿記の家計簿を作る方法の第三回:「集計表の作り方」を終わります。

あがぺい
あがぺい

お疲れさまでした!

>>>第四回:締め切りと繰り越しへ

コメントを残す

メールアドレスが公開されることはありません。

CAPTCHA