【支出管理】これさえ見れば作れる!Excel自作家計簿の作り方【画像付き】

こんにちは!なおひです!

僕は現在つみたてNISAから投資を始めています。

そのためにまず家計を見直してちゃんと貯金するところから始めました。

なおひ

元々浪費が凄かったので貯金5万くらいしか無かったです。

家計を見直すために最初にしたことは家計簿をつける事でした。

家計簿をつけるメリットについてはこちらの記事で紹介しているので併せて見て頂けると嬉しいです↓

その中でも僕はExcelで家計簿を作ることをオススメしています。

Excelで家計簿を作るメリットは以下の通り。

  • 数値の計算、グラフ化が簡単。
  • カスタマイズが簡単
  • クラウドに置くことででいつでも入力できる。

今回は具体的に僕が使っている家計簿シートとその作り方を紹介したいと思います。

なおひ

友達何人かに紹介しましたがかなり好評です。

目次

Excel家計簿の完成イメージ

まず最初に完成イメージはこちらです。

月ごとのシート

集計シート

入力するのはここ

数値を入力すると支出が勝手に合計されてグラフになるようにしています。

月別の支出管理シートの作り方

まずはお買い物ごとに入力するシートを作っていきます。
入力シートは月ごとに分けます。

収入入力欄をつくる

最初に収入の入力欄を作っていきます。
まずはA2~A7,B2セルに画像のように各項目を入力します。

次に、金額を入力する欄は通貨で表示をしたいので

B3~B7を選択 ⇒ 右クリック ⇒ セルの書式設定で通貨として表示するように設定変更します。

収入の合計は自動で計算して欲しいので、合計する関数であるSUMを入力します。

入力する関数 : =SUM(B3:B6)

後は見栄えを良くしたいので罫線を引きます。

収入入力欄のA2~B7を選んだ状態で画像の赤丸部分をクリックします。

そのあと「格子」を選びます。

するとこんな感じで罫線を引くことが出来ます。

もう少し見栄えをよくするために色を付けます。

まずは金額を入れる部分以外をA2~A7を選択し、赤で囲んだ部分を選択します。

収入欄は青ベースで作るので画像の赤で囲んだ部分を選択します。
(色は好みにお任せします)

こんな感じで色が付きます。

同じ要領で残りの部分も色を付けていきます。
数字が入る欄は色を薄めにしておきます。

収入欄の完成です。

支出集計欄をつくる

次に、支出を集計する欄を作ります。

まずは画像のようにC2~F2まで項目名を入力します。

先ほどの要領で格子状に罫線を引き、

色を付けます。貯金だけ、支出と別な気がするので僕は色を分けています。
(この辺はお好みなのでお任せします)

支出入力欄をつくる

次は支出を入力する欄を作ります。

支出項目家賃以外の項目を下の図のように入れていきます。

ポイントは二つ

  • 使った金額、消費or浪費or投資の入力欄、使った用途 の3つ入力する為、3列ずつスペースを取ること。
  • 消費or浪費or投資の集計をする欄を後で追加する為、4列離して欄を作ること。

消費or浪費or投資の入力欄を作る理由はこちらの記事を参照お願いします↓

余裕ある生活を目指すブログ
404: ページが見つかりませんでした | 余裕ある生活を目指すブログ

次に罫線を引いていきます。
今回は格子状に縦横すべて引くと見づらくなるので外枠だけ引いていきます。

画像のように支出項目の行を選択し、赤丸をクリックします。

「外枠」を選びます。

こんな感じで罫線が引かれます。

ここと

ここも同じように罫線を引きます。

支出入力欄の外枠も同じように罫線を引きます。

行数は大体100行くらい取っておけば僕の経験上足りると思います。

次に各支出項目ごとに3行ずつ外枠罫線を引きます。

全部引くとこんな感じ。

後はさっきの要領で色を付けます。

費用入力欄は通貨で表示してほしいので収入欄と同じように
右クリック ⇒ セルの書式設定で通貨として表示するように設定変更します。

次に、消費、浪費、投資の区分分け入力欄を作ります。

入力する欄には「消」、「浪」、「投」以外の入力をしたくないのでドロップダウンリストを作ります。

まずは画像の通りG2~I2にそれぞれ「消」、「浪」、「投」と入力してください。

次に、3列ある支出項目の真ん中の列を選択します。
例えば食費の欄であれば画像のような感じ。

その後、データ⇒データの入力規制を選択し、

入力値の種類でリストを選びます。

赤丸をクリックするとシート上でどの値のリストから選ぶか選択できます。

先ほど入力した「消」、「浪」、「投」を選択します。

OKを選択したら完了です。

試しにセルを選んでみて画像のようにリストから選ぶ形になっていれば成功です。

通信費~その他まで同じ要領で設定したら完了です。

支出を集計する関数を入れる

次に支出の集計をする関数を追加します。
「食費」の欄を例に説明します。

まずは先ほど作った入力欄で、費用を合計する為
SUM関数を表の「計」の行の横に入力します。

入力する関数: =SUM(L7:L100)

試しに入力してみてちゃんと合計されてればOKです。

他の費用欄も同じようにSUM関数を入れます。

次に、オレンジ色で作った支出集計欄にも同じ数字が出てくるように設定します。

入力する関数: =L4

他の費用も同じように入力します。

次に支出の合計を計算します。

D15セルに支出の合計を計算するSUM関数を入れます。

貯金額を計算する

貯金額は(収入の合計)ー(支出の合計)とします。

以下の画像のように引き算する関数を入力します。

入力する関数 =B7-D15

ちゃんと引き算されていればOKです

使用した割合を計算する

収入に対して各支出が何%占めているかを計算します。

割合の欄に収入に対する支出割合の計算をする関数を入れます。

まずは%で表示したいため、割合の欄を
右クリック ⇒ セルの書式設定でパーセント表示するように設定変更します。

次に(支出額)/(収入合計)を計算する関数を入れます。

入力する関数 =D3/$B$7

Bと7の前に$を入れるのがポイントです

こうすることでこの後オートフィルしたときに収入合計の参照セルだけ動かさずに関数を入れることが出来ます。

こんな感じで計算できていればOKです。

残りの欄も同じように入力します。

入力する際、画像赤丸の位置をつかんだまま下に引っ張ることで
セルをずらしながら同じ関数をコピーすることが出来ます。

これをオートフィルと言います。

ただし、$を置いた直後の数字、文字はオートフィルの際ずれることは無いです。

全部入ったらOKです。

合計欄は割合の合計をするためSUM関数を入れます。

入力する関数 =SUM(E3:E14)

合計100%になっていればOKです。

予算設定をつくる

まずオレンジの表の予算割合は手入力します。

ここは月の最初に予算を決める欄です。

そこから予算金額を算出するように作っていきます。

まず入力した予算割合の合計を確認する為、合計欄にSUM関数を入れます。

入力する関数 =SUM(F3:F14)

合計100%になればOKです。

次に各支出項目に予算額を出すようにします。

予算は(収入合計)×(予算割合)で算出します。

食費を例に説明します。

画像のように関数を入力します。

入力する関数 =B7*F4

しっかり掛け算が行われていればOKです。

見やすさの為に予算の右に割合も表示します。

入力する関数 =F4

更に後いくら使えるかを(予算)-(支出合計)で計算します。

入力する関数 =L3-L4

しっかり引き算されていればOKです。

残りの通信費~その他まで同様に入力します。

これで予算の設定は完了です。

消費、浪費、投資の集計をする

次に、それぞれの費用について消費、浪費、投資がいくらだったか集計します。

集計にはSUMIF関数を使います。

SUMIF関数とはある条件を満たす数だけ合計する関数です。

今回は右隣にある文字が「消」の時、「浪」の時、「投」の時それぞれに分けて集計します。

画像のようにSUMIF関数を入力します。

入力する関数 =SUMIF($M$6:$M$101,”消”,$L$6:$L$101)

「浪」、「投」も同じように関数を入力します

「浪」の時

入力する関数 =SUMIF($M$6:$M$101,”浪”,$L$6:$L$101)

「投」の時

入力する関数 =SUMIF($M$6:$M$101,”投”,$L$6:$L$101)

そうすると画像のようにそれぞれ分けて合計されます。

後は各費用について同じように関数を入力してください。

また、家賃は消費としてカウントしているのでG3に以下の関数を入れてください

入力する関数 =D3

次に、全体の消費、浪費、投資を集計します。

まず以下のように項目名を記入します。

次にそれぞれSUM関数で合計を算出します。

消費の場合

入力する関数 =SUM(G3:G13)

浪費の場合

入力する関数 =SUM(H3:H13)

投資の場合

入力する関数=SUM(I3:I13)

合計支出額に対するそれぞれの割合を計算します

消費の場合

入力する関数 =D18/$D$15

浪費、投資はオートフィルで入力できます。

また、理想の割合はこのくらいだそうなので手入力します。

消費・投資・浪費の割合についてはこちらの本を参考にしています。

良ければ併せて読んでみてください↓


最後に個人的にこの集計欄は常に見えている必要はないので非表示にします。

収支のグラフを作る

次に収支の状態を円グラフで表示します。

まずは上のメニューの挿入⇒円グラフのマーク⇒2-D円を選択してください。

そうすると真っ白なグラフエリアが出てくるので右クリックして
「データの選択」を選んでください。
(もし既に円グラフが出てくるようなら、グラフエリアの中の円グラフを選んで消しちゃってください)

「追加」をクリックします。

系列の値を入れたいので赤丸部分をクリックしてください。

集計した各支出と貯金額を選択します。

「OK」を押します。

次に各支出のラベルを設定します。
赤枠の「編集」をクリックしてください。

赤丸部分をクリックします。

赤枠の各支出項目名を選択します。

そうするとこんな円グラフが出てきます。
グラフタイトルと凡例は邪魔なのでクリックしてdeleteを押して消しちゃってください。

円グラフをクリックして
データラベルの追加を選びます。

すると金額だけ追加されます。

ただ、どれがどの費用かしりたいので表示された金額をクリックしてください。

更に右クリックして「データラベルの書式設定」を選択してください。

こんな表示が出てくるので分類名、値、引き出し線を表示するを選択してください。

良い感じになったので邪魔にならないところに移動して完了です。

コピーして12枚にする

これで1か月ごとの集計シートが出来たのでコピーして12枚にしてください。

1年のまとめシートの作り方

次に12枚の月別シートをまとめて集計するシートを作ります。

集計シートの作成

新しいシートでまた以下の画像のように項目名を入力してください。

これまでの要領で罫線を引き、

色を付けます。

集計する関数を入れる

シートの大枠が出来たら集計する関数を入れていきます。

まず収入合計の値を各シートから参照します。

例として1月の収入を参照する場合、以下の関数を入れます。

入力する関数 =’1月’!B7

構造的には =’シート名’!セル名となっています。
なので1月の部分を2月、3月と変えていけば各月の収入合計が入力できます。

支出、月ごとの貯金額も同じように入れていきます。

支出合計

入力する関数 =’1月’!$D$15

月ごとの貯金額

入力する関数 =’1月’!$D$14

前年繰り越しは手入力で入れます。

貯金総額は以下の通り計算しています。

(前の月の貯金総額)+(今月の貯金額)

なので
1月は(繰り越し)+(1月貯金額)
2月は(1月までの貯金額)+(2月の貯金額)
3月は(2月までの貯金額)+(3月の貯金額)

という風になっています。

1月貯金総額

入力する関数 =D2+F2

2月貯金総額

入力する関数 =E2+D3

3月貯金総額

入力する関数 =E3+D4

グラフで可視化

すべて入力が終わったら最後にグラフで可視化していきます。

挿入⇒散布図のマーク⇒折れ線のグラフを選んでください。

真っ白なグラフエリアが出てくるので右クリックして「データの選択」を選びます。

「追加」をクリックします。

まず横軸の値を選びます。
赤丸の系列Xの値を選んでください。

横軸を月にするので赤枠部分を選んでください。

次に縦軸の値を選びます。
赤丸部分をクリックします。

収入合計の値を入れるので赤枠部分を選んでください。

次にこのデータ系列に名前を付けます。
赤枠をクリックしてください。

今回のグラフは収入合計のグラフなので赤枠部分を選んでください。

グラフが出来ました。

凡例を追加したいので
グラフ要素を追加⇒凡例⇒右を選択します。

最後に横軸の表示がイマイチなので、
横軸をクリックして選択後、右クリック⇒軸の書式設定を選んでください。

画像のように調整します。
これで無駄な余白が無くなります。

後は同じ要領で支出合計、月ごとの貯金額、貯金総額のグラフを作ったら完成です!

お疲れ様でした!

まとめ

今回は僕が使っている自作家計簿の作り方を一から紹介しました。

この家計簿は3年以上使っており、家計簿のお陰でだいぶ貯金出来る体質になりました。

なおひ

同時に以下に余分な出費をしていたかも実感しました。

皆さんもこの家計簿を作り、自分流に改造して家計管理をし、無駄なお金を使わないようにしていきましょう!

そして余剰なお金を投資に回し、資産を形成していきましょう!

僕が投資に挑戦した記事もあるので併せて読んで頂けると嬉しいです↓

あわせて読みたい
【2021年4月分】積立NISAの運用結果のご報告 この記事の内容は以下の通りです。 僕が積立NISAで買ったファンドと積立額 現時点での評価損益 こんにちは!なおひです。 人生を楽に生きるためには資産を持つことは大...
あわせて読みたい
【2021年三菱UFJ】確定拠出年金の掛け金おすすめ配分 確定拠出年金の配分はどうしたらいい?この記事では三菱UFJで企業型DCをしている人向けにオススメの配分を紹介します。実際に僕が購入している銘柄と掛金の配分も記載しています。確定拠出年金を始める方、配分を見直したい方向けの記事です。

本日の内容は以上です。
ここまで読んでくださりありがとうございました。

にほんブログ村に参加しています!良ければ応援ポチ頂けると嬉しいです↓

ブログランキング・にほんブログ村へ PVアクセスランキング にほんブログ村

スポンサーリンク↓

よかったらシェアしてね!

コメント

コメントする

目次
閉じる