自作のExcel家計簿を大幅改修したので少し紹介

またまた久しぶりの記事です。

家計簿を付け始めて5年が経ちます。

最初はExcelでベタ打ちで付け始めて、データの表記ゆれを減らすために入力用のシートを作って、項目分類ごとに集計するシートを作ったり、クレカの請求額がわかるようにしたり、幾度となくアップデートを続けてきました。

ついさっき家計簿Ver.8になったので、簡単に紹介します。

家計簿始めたきっかけ

新社会人になり、支出をなんとなく把握するためにつけ始めました。

支出のみ付けていたので貯金額はすぐにはわからず、「いくら貯まった」というより「あといくら使える」という感じの使い道になってしまい、あまりお金を貯めることには繋がりませんでした。

使っていくうちに、こういう機能が欲しい、もっと楽にしたい、といった欲が出ていろいろ改造してきました。

バージョンアップしてきた機能

Ver.1

2015年4月1日からスタート

今のメインPCにデータが入っていないのでうろ覚えですが、Excelに各種項目をベタ打ちで管理していたと思います。

年度ごとにシートが追加されるクソ仕様。

Ver.2

公務員の仕事に慣れてきた(悪い意味で)頃に改修したもの。

セルの結合や明朝体がつかわれているぞ！！

機能的にはクレカの請求額(100%一致はしない)を表示できる機能と、月額の合計を表示できる機能を追加。

Ver.3

何が変わったのかよく覚えていない(データ見てもわかりゃん)

Ver.4

2018年11月頃に改修

年度ごとにシートを追加する運用はいい加減キツイと感じて大改修。

データはひたすら積み重ねていくデータベース式に変えて(Excelのままですが)、表記ゆれやミスを減らすために入力用シートを作成したのが大きな変更点。

↓Ver.2と同じデータのVer.4での画像

入力用シートはこんな感じ。

いちいち日付を入力するのが面倒になったのでボタンを作った。

Ver.5

2019年7月頃に改修。転職して固定費が増えたことにより、固定費を簡単に把握できるようにしたくなった。

月ごと・分類ごとに集計して一覧化するシートはあるものの、入力した日付=集計する日付となってしまい、2ヶ月分の固定費が集計される月や、0円の月があり、把握が煩雑だった。

固定費用のコードを作って管理することにした。

また、このバージョンからポイント利用やPayPay等の電子マネー利用の管理を出来るようにした。

この機能と合わせて、ようやく収入を記録できるようになった。

チャージ時に支出して、電子マネーでの支払いは支出無しという扱い。

↓変更したフォーマットと電子マネーの扱い

↓固定費シートはこんな感じ

B列のコード+年度+月を検索キーにして、データのJ列とM列でsumifsしているだけ。

Ver.6

収入記録が無いデータと、収入を付け始めたデータではかなりゴチャゴチャして計算が合わないことが頻発したので、引越し前のデータはVer.5に置いてきて、真っ白なデータで再スタートした。

データの最初には調整用の金額が乱れまくっているけど、そのうち気にならなくなるだろう…

機能的なバージョンアップは無し。

Ver.7

Ver.6で運用していると、ある日家計簿上の残高と実際の残高が一致しないことに気が付いて、残高を管理できるようにした。

使用している口座と、現金まで管理できるぞ！

また、マイナーバージョンアップも重ねて、残高推移を見れる機能や、コーヒーを淹れた記録を付けるシートも追加した(家計簿関係無い)

見せられないよ！

一件ベタ打ちみたいなデザインだけど集計は自動です。

コーヒー記録用のシート(笑)

最近はコメントが「美味しい」とか雑な感じになってきた。

使った金額が異様に高いのはミルのせい。

Ver.8

完成したばかりの最新バージョン。

Ver.7では収入機能を追加したものの、

• 立て替えたお金は収入？支出？返金は？
• 使ってる電子マネー増えてきたけど残額管理してないから記入漏れあってもわからない
• 電子マネー込みの資産総額がわからない
• 電子マネー増えてきて支払方法が複雑化してきた
• ↑例1:LINEPayのチャージ&Payは電子マネー？クレジット？
• ↑例2:GooglePay経由のQUICPay経由のデビットカードは電子マネー？カード？口座？
• 資産管理始めるから対応できるようにしたい

といった細かい部分に手が届かなくなり、改修。

ついでに絶対参照していたVBAのコードを、名前定義に変えてメンテナンス性を改善しました。

例:Range("$A:$B")→Range("定義した範囲")

定義した範囲=$A:$B

フォーマット変更

フォーマットを大きく変えました。

• 分類のほかに収支分類(支出、収入、移動、クレカ引落)を追加(集計が楽に)
• 支出用の金額列と収入用の金額列を分けていたものを統合
• お金の移動の場合は、移動先を指定できるように項目を追加
• 支払方法をマスタ管理化
• 分類をマスタ管理化
• 電子マネーをチャージ時支出無し、利用時支出の考え方に変更

右の灰色の部分は集計に使用するもの。支払方法に紐づいて自動で入力されます。

データから入力用シートにコピーするボタンも追加。

入力補助

入力用シートでは、日付を入れると必須項目に色が付きます。

例:支出の場合

例:移動の場合

必須項目が埋まっていない場合、入力完了を実行できないようフェイルセーフを付けています。

分類のマスタ化

見切れていますが、こんな感じで管理しています。

1行目が収支分類

2行目以降が、それぞれの収支分類に対応した明細分類です。

ちなみに抽出ボタンを押すと、選択中のセルに該当するデータのみをフィルターして表示できます。

入力用シートでは、収支分類に応じて、明細分類のプルダウン内容が変化します。

収支分類が支出のときはマスタのA列が表示され、収入のときはB列が表示されます。

これは、indirect関数と名前の定義を使っています。

支払方法のマスタ化

支払方法の多様化に合わせて、マスタ管理するようにしました。

機能としてはあまり変わりませんが、複雑化が少し解消されました。

入力時は、支払方法だけ選択し、残りのデータはvlookupで自動入力されます。

思いついただけ入れてますが、使わないこともあるかもしれません。

支払方法

入力時に使用する。わかりやすい任意の名前でおｋ(ただし要ユニーク)

S_最終決済

最終的にどこで処理されるかの決定。

デビットカードとかはカードだけど実質口座引落と同義だよねってことです。

S_現金属性

いるかこれ？

S_口座属性

口座振込や口座引落、振込等に参照する金融機関の情報。

S_クレジットカード属性

クレジットカード利用時に参照する金融機関の情報。

支出として集計されるが、残高には影響しない。(引落予定に集計される)

S_電子マネー属性

電子マネー利用時に参照するサービスの情報

S_デビットカード属性

いるかこれ？

資産運用シートの追加

始めたばかりなので資産運用なんて大層なものではない(金額ボカすほどでもない)ですが、使って見ながら改修していく感じです。

経過も記録したいところですが、さすがにそれは面倒かな…。

オレンジのセルに入力すると、隣に入力した時間が自動記録されます。

今後のアップデート予定

いつかやりたいな～程度ですが、

• 完全にDB化したい
• Webアプリ化したい

くらいです。

だいぶ突き詰めてきた感じがします。

終わりに

最後まで読んでくださりありがとうございました！

ここの処理どうやってるの？とかこういうケースの時どういう扱いしているの？とかありましたらコメントかtwitterでご連絡くださいませ。

家計簿ガチ勢の人と話してみたいです。