実践：家計の状況を分析できるExcel家計簿をつくろう
ここまでの内容を踏まえて、家計の状況を分析できるエクセル家計簿をつくってみましょう。
実際に使用するかは別として、この家計簿をつくればデータ蓄積→データ集計→資料作成の流れをつかめると思いますので、ぜひ一度お試しください。
①家計簿の目的を明確にしよう
まずは家計簿を分析する目的を明確にします。
家計簿をつけるということは、家庭内におけるお金の出入りを管理するということです。わざわざ管理するのですから、目的はひとつ。「家に残るお金を増やすため」。
さらに細かい話をするなら、お金を増やす方法は①収入を増やすか、②支出を減らすかのふたつしかありません。副業などを行っていない方であれば、基本的には支出を減らすことが主な指針になると思います。
なので今回は「支出を減らす」というのを大きな目的に設定します。
②どんなデータが必要か洗い出そう
次に支出を減らすためには何を調べればいいのか考えていきます。
「そもそも毎月きちんとお金が残っているかチェックしたい」
「どこで無駄遣いをしているかが分かれば、支出を減らせるかもしれない」
上記の２点が調べられれば、支出を減らすことに繋がるかな？ とアタリをつけます。
これらを調べるためにどんなデータが必要になるか、以下に書きだしてみました。
◎日付
◎残高
└◎収入金額
└◎支出金額
・収支の理由
└◎カテゴリ
└◎詳細
毎月お金が残っているかをチェックするには、日付と残高が必要です。
そして残高を出すためには収入金額と支出金額が必要になるので、このふたつも記録しないといけません。
さらにどこで無駄遣いをしているかをチェックするためには、なぜお金を使ったのかという支出の理由が必要です。収入の分析も行うなら支出の理由と収入の理由はそれぞれ分けて記録するべきかもしれませんが、今回は支出の管理が中心なので、一緒にしても問題ないだろう……というわけで収支の理由として記録することにします。記録として詳細も残しておきたいけど、分析する時には基本的にカテゴリ単位でチェックしたいから、カテゴリも記録するようにしとこう。
このような流れで、必要なデータの洗い出しを行っていきます。
③データ蓄積用のシートを作ろう
必要なデータの洗い出しを行ったら、エクセルを開いてデータ蓄積シートを作成します。
一番左上のセルに「ID」、その隣に先程洗い出したデータの見出しを入力します。
ID列は、各行に連番を入れていくための列です。データ蓄積を行う際には、とりあえずIDを振るくせをつけておくと、実際に使うかは別にして色々と捗る場面があります。
洗い出したデータの順番はお好みで入れ替えて問題ありません。
私は残高を一番右側に置きたい、金額が入る箇所はひとまとめにしたい、という理由でこの順番です。
もちろん早速データを入力していってもいいのですが、せっかくなのでカテゴリ入力を選択式にしましょう。
シートを追加し、ほしいカテゴリをA列に列挙します。
データ蓄積シートに戻り、カテゴリ見出し下のセルを選択した状態で「データ」メニューの「データの入力規則」をクリック。
設定タブの「入力値の種類」を「リスト」に設定したら、赤枠で囲った矢印のついた部分をクリック。
すると設定ウィンドウが小さくなるので、そのままカテゴリリストに移動し、A列を選択。
自動で「＝カテゴリリスト!$A:$A」と入力されるので、そのまま右端のボタンをクリックします。
設定ウィンドウが元のサイズに戻るので、OKボタンをクリック。
するとデータ蓄積シートで選択していたセルに▼マークが表示されます。
▼マークをクリックするとカテゴリリストの内容からどれを入力するか選べるようになるって寸法よ。
カテゴリの項目を増やしたいときも、カテゴリリストのシートを更新すれば自動で反映されるってわけ。
今回はこの欄に収入のカテゴリも入れるので、給与・開始残高を追加。おまけで支出カテゴリとして書籍代も増やしました。
家計簿は各家庭の状況次第で記載したい支出項目が変わると思うので、無駄遣いをしているかチェックしたい項目を選んでリストにするといいと思います。
ちなみに今回のカテゴリは下記のサイトを参考に設定しました。
選択入力設定をしたセルをコピー→下のセル群を選択→Ctrl＋Vを押せば選択範囲すべてに設定が適用されます。
ここまで準備ができたら、いよいよデータを入力していきます。
残高については一番上の行のみ手入力し、次の行からは計算式を入れていきます。
残高は「今までの残高（ひとつ上の行の残高）+収入金額-支出金額」で算出できるので、「=G2(ひとつ上)+E3(収入金額欄)-F3(支出金額欄)」と入力してエンター。
自動で残高が表示されました。
このセルをコピーして下の行に貼り付ければ、各行の残高が自動で表示されていきます。
この調子でどんどんデータを登録していきましょう。
データ入力のコツは以下の５つ。
①IDをとりあえず振る
②ID・日付・金額など半角英数字で入れられるものは半角英数字で入力。単位も極力つけない
③上の行と入力内容が一緒でも省略しない
④セルを結合しない
⑤原則１セルに１データで入力する
ちなみにこれを守ればひとまずエンジニアに感謝されるデータになるぞ！！ マジで！！！！！
④ピボットグラフを駆使してデータを集計するシートを作ろう
データをある程度蓄積したら、ピボットグラフという機能を使ってデータを自動集計するシートを作りましょう。
IDから残高までの列を選択して、「挿入」メニューの「ピボットグラフ」を選択。
ウィンドウが出たら特になにも触らずにOKボタンをクリック。
するとピボットグラフのフィールドという謎の設定画面がついたシートが新しく作成されます。
こいつが、ハイパー、便利なんですよ！！！！
ピボットグラフというのは何ものかというと、先程選択したデータを、「ピボットグラフのフィールド」で設定した内容に沿って集計し、自動でグラフの形にしてくれるという……なんかすごいやつです！！！！！
ひとまずピボットグラフのフィールド内の４つのボックスについてゆるっと説明します。
①フィルター
グラフに表示する項目を選びたいときに使います。
例えば11月だけのデータを表示したい場合には、ここに日付を追加して、11月中のデータだけを表示するよう設定します。
カテゴリが水道光熱費に設定しているものだけ表示したい場合には、ここにカテゴリを追加して、水道光熱費のデータだけを表示するように設定する、という具合です。
グラフの表示自体には関係ないけど、データの絞り込みに使いたいという項目をここにぶちこむイメージです。
②凡例
各データの内訳を出したい時に使います。
例えば支出の内訳をカテゴリ単位で出したい場合には、ここにカテゴリを追加します。
③軸
グラフ横軸の基準を選びます。
例えば月ごとに表示したい場合は、ここに日付を追加して月単位表示になるよう設定します。（基本的に日付は入れただけで月単位表示になっているはずです。）
④値
集計したい値を選びます。
例えば残高の推移がみたいならば残高を、支出の状況を見たいならば支出を追加します。
③の軸と④の値が必須入力ですかね。
①と②は使わないこともあります。
今回はこのピボットグラフを使って、「月ごとの残高推移」「各月の支出内訳」のふたつを表示したいと思います。
（１）月ごとの残高推移
フィルターに「カテゴリ」、軸に「日付」、値に「残高」を入れると上図のようなグラフが表示されます。
月ごとの残高推移は各月１日に登録している開始残高のみを追いかけたいので、フィルターに「カテゴリ」を追加しました。
グラフの左上にフィルターへ追加した「カテゴリ」の表記があるので、ここをクリック。
『開始残高」をクリックして「OK」ボタンを押すと……
各月の開始残高がグラフ化されました！
棒グラフの上で右クリックしてデータラベルを表示させたり、見出しをいじったりして好みのグラフにしあげます。
（２）各月の支出内訳
月の残高推移グラフを作成したら、シートを複製して各月の支出内訳用シートにします。
フィルターを空にして、凡例に「カテゴリ」を追加。値は「残高」から「支出金額」に変更するとグラフが置き換わるはずです。
グラフの見出しを書き換えれば、各月の支出がカテゴリごとに表示されたグラフのできあがり……ですが、このままだと分かりにくいので、グラフの種類を変更します。
グラフの上で右クリックして、「グラフの種類の変更」をクリック。
内訳を見たい時はだいたい積み上げグラフにすればいいってばっちゃが言ってた。
「積み上げ縦棒」を選択して「OK」をクリック。
内訳がグラフ化されました！
ところで「（空白）」って別に見る必要ないよね。というわけでグラフ上に表示された「カテゴリ」をクリック。
「（空白）」とついでに「開始残高」「給与」のチェックを外してOKを押しましょう。
きれいに支出の内訳だけが表示されました！
あとはデータラベルを表示したり、グラフを縦長にしてより内訳の比率がわかりやすいように設定して……色もデザインメニューからいじって……
よい感じでは！？！？！？！
これで作りたかったグラフ２種を作り終えました。
今後はデータ蓄積シートに入力するだけで、このグラフが更新されていくという寸法です。
便利 あまりにも 便利……。
⑤集計結果を一覧できるシートを作ろう
ひとりで確認するだけだったら、ここまででも十分便利な家計簿になっていると思いますが、せっかくなのでプリントアウトして家族で回覧できるよう、集計結果のグラフを一覧できるシートをつくります。
新しいシートを作成したら、「表示」メニューから「ページレイアウト」をクリック。
印刷した時のレイアウトに近いかたちでエクセルが表示されます。
この１ページ内に先程のグラフが両方表示されるようにしていきましょう。
まずは資料っぽく見出しをつくったり、レイアウトが綺麗に見えるようにセルのサイズを整えたりします。
グラフを見た上での気付きや、アイディアを書くスペースも入れてみました。
というわけで、このシートに先程つくったピボットグラフを貼り付けていきます。
各シートのグラフを右クリックしてコピー。（もちろんCtrl+Cでもいけます）
貼り付けたい場所のセルを右クリックして、「形式を選択して貼り付け」。
「Microsoft Office 描画オブジェクト」を選んで「OK」を押すと……
ピボットグラフがそのまま貼り付けされます。
ここでグラフの表示サイズを変えても勿論大丈夫。
同じ要領で月の支出内訳グラフももってきて、資料っぽく整形して……。
資料ができたぞ！！！！！！
ここまでいったら、今後はデータ蓄積シートを更新していくだけです。
例えば12月の支出を追加すると……
自動でグラフにも追加される！！！！！！
反映されないときはグラフを選択した状態で「ピボットグラフ分析」から「更新」ボタンを押してください。最新データが反映されます。
これで自動でグラフ分析してくれるエクセル家計簿の完成です！
ピボットグラフの使い方に慣れてしまえば、支出について各カテゴリごとの詳細な内訳を出したり、水道光熱費だけに絞って推移グラフを作るみたいなこともできるようになるはずです。
また、本来はもう少しピボットグラフ作成まわりの作業が簡略化できるんですが、今回は分かりやすさを優先して制作しています。慣れてしまえば集計シートと資料シートをまとめたり、色々カスタムできるようになると思います。
