Excelで家計簿を作りたい！
けど、マクロとかプログラミングとか分からない！！
そんな方向けに、マクロを使わなくてもここまでの家計簿が作れるんだ！
と思えるような家計簿を作ってみました。
それをただ配るだけだともったいないので、
どんなことをしているのか分かるように、中身の解説もしていきたいと思います。
【本記事の目標】
マクロ無しExcel家計簿の中身を理解しよう
まずは各シートの全体像
まずは各シートの内容について説明していきます。
このExcelファイルでは５つのシートでできています。
ただし、基本的に入力していくのは２つのシートだけで、
それ以外は情報を確認するためのシートです。
”入力”シート
”科目情報”シート
”ピボットテーブル”シート
”月別グラフ”シート
”年別グラフ”シート
各シートごとの中身を解説
それでは、各シートごとにどういった目的・動き・使い方をするのか説明していきます。
”入力”シートについて詳細
”入力”シートは、その名の通り日々の収支を入力するためのシートです。
このシートは大きく３つの部分で構成されており、それぞれ入力の仕方等が異なります。
① 黄色エリア：必須入力エリア
黄色のエリアは必須入力エリアです。
このエリアの項目を入力しないとこの家計簿はまともに動かなくなるので、
必ず入力する必要があります。
- 日付：収支が発生した日付です。
『年/月/日』の形式で入力しますが、『月/日』で入力しても、
自動的に『年/月/日』になります。 - 勘定科目：その収支の勘定科目です。
ドロップダウンリストから選択できます。
勘定科目自体は、”科目情報”シートに入力することで変更できます。 - 金額：その収支の金額です。
書式設定済みのため、数値を入力すれば『\000,000』の形式になります。
② 緑色エリア：補足情報エリア
緑色のエリアは補足情報を入力するためのエリアです。
このエリアの項目は入力してもしなくても問題ないです。
金額が大きな買い物をしたときに、その内容を後々分かるようにメモ書きをしたいときに使います。
③ 青色エリア：自動計算エリア
青色のエリアは自動計算エリアです。
このエリアには計算式が自動入力されていますので、入力は不要です。
むしろ計算式が消える懸念があるので、触らない方が良いです。
自動計算エリアの『収入/支出』と『固定費/変動費』については非常に複雑な計算式が入っています。
複雑ではありますが、少しずつ見ていけば理解できると思います。
下記に計算式とその動きを記載しておきます。
■『収入/支出』
=IFERROR(IF(OFFSET(科目情報[[#見出し],[収入/固定費/変動費]],MATCH([@勘定科目],科目情報[勘定科目],0),0)=”収入”,”収入”,”支出”),””)
『勘定科目』に記載の内容と、”科目情報”シートに記載の内容を照合して、
この『勘定科目』が収入なのか支出なのかを判定しています。
MATCH（～ の部分で、『勘定科目』が”科目情報”シートの何行目にあるか確認し、
OFFSET（～ の部分で、その行数の1列目に記載の内容（収入/固定費/変動費）を表示させています。
IF（～ の部分で、表示された内容（収入/固定費/変動費）が「収入」なのかそれ以外なのか確認しており、「収入」であればそのまま、それ以外なら「支出」を表示させます。
IFERROR（～ の部分で、もし上記計算式にエラーが出たときには無記入になるようにしています。
■『固定費/変動費』
=IFERROR(IF(OFFSET(科目情報[[#見出し],[収入/固定費/変動費]],MATCH([@勘定科目],科目情報[勘定科目],0),0)=”収入”,””,OFFSET(科目情報[[#見出し],[収入/固定費/変動費]],MATCH([@勘定科目],科目情報[勘定科目],0),0)),””)
『勘定科目』に記載の内容と、”科目情報”シートに記載の内容を照合して、
この『勘定科目』が固定費なのか変動費なのかを判定しています。
前半部分は『収入/支出』の計算式と同じ内容です。
IF（～ の部分で、「収入」であれば無記入にし、それ以外の時は改めて『勘定科目』が固定費なのか変動費なのかを判定して表示しています。
※シートに入力時の注意点
このシートに記載の表は【テーブル】になっています。
そのため、新しく入力していく場合は表の一番下で、表の外側に文字を入力すれば
自動的に表が拡張され、青色エリアの計算式やその他書式設定全てが自動的に入力されます。
行を挿入して記入すると、青色エリアの計算式が入力されなかったり、テーブルが正しく拡張されず、計算がまともに動かなくなりますのでご注意ください。
また、入力内容を削除する場合は行ごと削除する必要があります。
初めて使う際は行を削除して使用していただく必要がありますが、
2行目だけは入力例として残しておく必要があります。
(※2行目は行削除をしなければOKなので、上書きしてもらうのは問題ないです。）
”科目情報”シートについて詳細
”科目情報”シートは、入力シートで選択する勘定科目の内容を編集・追加するためのシートです。
このシートの『勘定科目』に記載されている項目が、”入力”シートの『勘定科目』の欄で選択できるようになります。
この表も【テーブル】になっていますので、
新しく入力していく場合は表の一番下で、表の外側に文字を入力すれば自動的に表が拡張され、
入力内容を削除する場合は行ごと削除する必要があります。
このシートで、各勘定科目が
収入/支出（固定費）/支出（変動費）
どれに相当するのかを分類してあれば、その分類に沿って以降のシートで分類してくれます。
”ピボットテーブル”シートの詳細
”ピボットテーブル”シートでは、”入力”シートに記載されているすべての内容を集計し、
ピボットテーブルとして表示させています。
① 集計結果を確認したい期間を選択します。
年・月を選択すれば、その月の結果を集計します。
複数の年・月を選択してあれば、選択した全ての期間の集計結果を表示します。
② 集計した結果の表を表示してくれます。
選択した期間での集計結果を表示します。
収入なのか支出なのか
支出は固定費なのか変動費なのか
を選別して集計します。
③ 集計結果をグラフ化
選択した期間での集計結果をグラフ化します。
内容は②と同じですが、表示方法をより視覚的に分かりやすくするためグラフ化します。
”月別グラフ”シートの詳細
”月別グラフ”シートでは、集計できる期間が各月ごとに限定している代わりに、
”ピボットテーブル”シートよりもより詳しく、また視覚的に分かりやすいグラフを複数表示しています。
① 集計結果を表示したい月を入力
ここで入力した月の集計結果が表示されます。
このシートで入力する部分はここだけです。
② 収支と固定費/変動費の集計結果
収支の集計結果と、固定費/変動費の集計結果を表示します。
③ 収支の比較をする棒グラフ
④ 固定費と変動費の比較をする棒グラフ
⑤ 支出の中での固定費/変動費の比率を確認する円グラフ
⑥ 各勘定科目の集計結果一覧
各勘定科目の集計結果一覧を表示します。
”科目情報”シートで勘定科目の修正・追加をした場合は、
この一覧の左側の『勘定科目』を同じように修正する必要があります。
⑦ 各勘定科目を比較する棒グラフ
各勘定科目の集計結果を棒グラフで表示します。
収入はプラス側、支出はマイナス側の棒グラフで表示されます。
⑧ 各勘定科目の金額の大きさを比較する円グラフ
各勘定科目の金額で円グラフを作ります。
収入・支出の区別なく、単純に金額の大きさだけを比較するための円グラフです。
⑨ 各勘定科目の支出によって収入がどのように減っているのかを
視覚的に分かりやすくしたウォーターフォールチャート
”年別グラフ”シートの詳細
”年別グラフ”シートは１年間の集計結果表示し、各年ごとの比較ができるようにしてあります。
”科目情報”シートで勘定科目の修正・追加をした場合は、
①の部分を同じように修正する必要があります。
ダウンロード
今回紹介したExcelファイルは、下記リンクからダウンロードが可能です。
できる限り全てを紹介したつもりですが、細かい部分については
実際にダウンロードしてご確認いただけたらと思います。
コメント