自作のエクセル家計簿を使いはじめて今年で8年目になりました。
初代・エクセル家計簿を自作した2012年当時の資産は、150万にも達していませんでしたが、2019年初頭には1,200万円まで増加。
その後クルマ購入のため資産は減少しましたが、2020年2月現在は890万円ほどで推移しています。
この間に結婚・転職・治療など、家計に影響を及ぼすイベントがたくさんありましたが、このエクセル家計簿のおかげもあって、資産はおおむね順調に増加してきました。
グラフにするとこんなかんじです↓
ということで本題です！
我家が8年間使い倒してきたクセル家計簿のテンプレートについて、その自作方法・作成手順をご紹介していこうと思います。
この記事で公開しているテンプレートは簡易バージョンのタイプです。
機能やサポートを充実させた、完全バージョンのテンプレートをご希望の方はこちらのコンテンツをご利用くださいませ↓
完成済みテンプレート付属はもちろん、テンプレートの使い方＆作り方、カスタマイズ方法や便利テクニックなどの全てのノウハウを、テキスト・画像・動画をフル活用して解説しています。
エクセル家計簿歴8年分のノウハウをすべて詰め込んだ自信作です！
エクセル家計簿を徹底的に使い倒したい方はぜひご活用くださいませ(○ˊᵕˋ○)
見出し
はじめに。このエクセル家計簿はピボットテーブルを使います
我家のエクセル家計簿を作るうえで、重要なポイントになるのが「ピボットテーブル機能」をフル活用しているという点です。
ある程度、エクセル操作の経験がある人でも、あまり馴染みのない機能かもしれませんね。
ピボットテーブルというのはエクセルに標準で備わっている機能で、「膨大な数字やテキストのデータを一瞬で自動集計して一覧表を作ってくれる」という素晴らしい機能です。
我家のエクセル家計簿は、このピボットテーブル無しでは成立しません。
ネット上には様々なエクセル家計簿テンプレートが公開されており、それらの多くはマクロや関数を多用していますが、我家はマクロは一切使用しません（関数は一回だけ使います）。そのかわりに、ピボットテーブルをフル活用して機能実現しています。
実際にピボットテーブルを使うとなにができるのか、またそれを利用するメリットをこちらの記事にまとめましたので是非参考にしてみてください↓
エクセル家計簿の構成について
我家のエクセル家計簿は大きく分けて2つのパートから構成されています↓
- 記録シート ⇒ 収入額や支出額など、日々の家計データを入力するシート
- 年間収支表 ⇒ 記録シートのデータを元に、年間の収支状況を見える化した一覧表
これが①記録シートになります。我家の実際の家計データです↓
そしてこれが②年間収支表です（2019年分）↓
上記の家計データを、ワンクリックで簡単に一覧表にできる優れものです。
エクセル家計簿テンプレートの作り方
さて、それではいよいよ録シートと年間収支表の作り方について解説していきましょう！
【STEP1】記録シートを作る
項目を配置する
まずはこんなふうに横並びで、エクセルに各項目を入力していきます。
費目は大・中・小と3つ設けていますが、このうち大カテゴリで使うのは「収入」「支出」「貯蓄」の3つであり、基本的にここは固定です。
ということで、こんなふうにあらかじめいくつか入力しておきます（我家は分かりやすく色分けしています）↓
ちなみに我家の費目分類については、こちらの記事にまとめましたのでよければご参考ください↓
手動フィルタを追加する
手動フィルタ機能を追加しておくと、たとえば特定の費目データを抽出できたりと、使い勝手が格段にあがるのでおすすめです。
こんなふうに追加します↓
貯蓄の計算式を入力する
貯蓄算出の考え方はとってもシンプルです。こういうことです↓
- 貯蓄 ＝ 収入合計 － 支出合計
ということで上記の計算を満たすように数式を入力していきます↓
※このシートの場合は「=SUM(G5:G6)-SUM(G7:G12)」と入力しています。ちなみにすべて半角入力です（全角はNGです）。
記録シートはこれで完成です！お疲れ様でした(○ˊᵕˋ○)♪
【STEP2】年間収支表を作る
次は、エクセル家計簿の要とも言える年間収支表を作っていきます。（冒頭で紹介したピボットテーブル機能を使って作っていきます）
あらかじめいくつかの家計データを記録シートに入力してから作業することをおすすめします（年間収支表は記録シートに何も入力されていない状態よりも、ある程度の家計データが揃っていた方が作りやすいためです）。
そのため以下のように、記録シートに家計データを適当に入力した状態で、これ以降は解説していきます。
年間収支表の配置を決める
- 「記録シートのデータ（年～実績）」を適当に選択する
- 「挿入」 ＞ 「ピボットテーブル」 ＞ 「新規のワークシート」 ＞ 「OK」
- （適宜空白挿入して配置を最適化）
- シートに名前をつける（ここでは「年間収支表」とします）
データソース参照範囲の拡張
年間収支表が、記録シートの100万行までのデータを参照できるように、参照範囲を拡張してあげます。
- ピボットテーブル上でクリック ＞ 「オプション」 ＞ 「データソースの変更」 ＞ 「テーブル／範囲」の末尾の数字を「1,000,000」に変更
フィールドの選択
- ピボットテーブル上で適当にクリック（右側にフィールドリストが表示される）
- 「月」「大カテゴリ」「中カテゴリ」「小カテゴリ」「実績」にチェックを入れる
- 「各フィールドリスト」を以下のように配置する（マウス左クリックを使ってリストを掴んだり離したりできる）
- 「大カテゴリ」「中カテゴリ」「小カテゴリ」 → 「行ラベル」へ配置
- 「月」 → 「列ラベル」へ配置
- 「実績」 → 「値」へ配置
実績表記を「合計」に変更
- ピボットテーブル上で適当にクリック（右側にフィールドリストが表示される）
- 「データの個数／実績」の横にある▼をクリック
- 「合計」を選択 ＞ 「OK」
表全体の表示形式を変更する
- セルを適当にクリックしてマウスカーソルを「十字キー✚」にしておく
- 「表全体」を選択（表左上にマウスを持っていき矢印が出てくるところでクリック）
- 「デザイン」 ＞ 「レポートのレイアウト」 ＞ 「表形式で表示」を選択
不要情報の非表示化
- 「月」の下三角▼ ＞ 「空白」のチェックを外す
- 「大カテゴリ」の下三角▼ ＞ 「空白」のチェックを外す
- （ピボットテーブルの適当な部分をクリックしておいて）「デザイン」 ＞ 「総計」 ＞ 「行のみ集計を行う」
数字の表示形式変更
- セルを適当にクリックしてマウスカーソルを「十字キー✚」にしておく
- 「表全体」を選択（表左上にマウスを持っていき矢印が出てくるところでクリック）
- 「ホーム」 ＞ セル表示形式変更用の「下三角▼」 ＞ 「その他の表示形式」 ＞ 「通貨」を選択 ＞ 「負の数の表示形式」はお好みで選択
費目の並び替え
- 「移動したい費目」の上で右クリック ＞ 「移動」 ＞ 「移動先」を選択
ピボットテーブルスタイルの変更
- セルを適当にクリックしてマウスカーソルを「十字キー✚」にしておく
- 「表全体」を選択（表左上にマウスを持っていき矢印が出てくるところでクリック）
- 「デザイン」 ＞ 「ピボットテーブルスタイル」 ＞ 配色パターン「なし」を選択
年間収支表のデザインを変更する
参考までに我家のデザインはこのようにしています↓
さてこれでエクセル家計簿（簡易版）は完成です。お疲れ様でした(*´꒳`*)♪
エクセル家計簿の使い方（つけ方）
さてエクセル家計簿の使い方についても簡単に解説しておきましょう！
入力スペースの作り方
記録シートの入力スペースが少なくなってきたら、このようにして作ります。
家計データの入力＆反映方法
- 記録シートに家計データを入力
- 年間収支表上で「右クリック」 ＞ 「更新」 ＞ 入力したデータが年間収支表に反映される
同じ費目や同じお店での買い物は、過去のデータをコピペするとラクちんです。
月をまたいだ時の入力＆反映方法
月をまたいだとしても、そのまま下方向にひたすらデータ入力していけばOKです（前月分をまるごとコピペするとラクちんです）。
（年をまたいだ時の年間収支表への反映方法は「やさしいエクセル家計簿のはじめ方」にて解説しています）
また、年間収支表への反映は以下のようにして行います。
