手書きやアプリを使って家計簿をつけるより、エクセルで家計簿をつけるとグラフ化できたり、カスタマイズできたりして便利です。
ですが家計簿ソフトって数千円する高いものが多く、買ってみて使いにくいとショックですよね。
自分でも簡単に作れる方法を解説します。
プロローグ
家計簿用のエクセルを作って管理したら、圧倒的に楽になったよ。
家計簿をつけるのが面倒じゃなくなって、気付いたら節約できたんだ。
えっ、自分で作ったの？
エクセルって聞くと難しそうだけど・・・
簡単な数式しか使っていないから難しくないよ。
作り方を説明するね。
この記事の内容を読むとこんなことが分かります。
- 支出管理用のエクセルの概要や構成を説明します
- 支出管理用のエクセルを使うメリットを説明します
- 支出管理用のエクセルの作り方を６つのステップで紹介します
- マネーフォワードと同時に使うと、非常に便利になります
著者について
- 支出管理用のエクセルを自作して、5年以上継続しています
- マネーフォワードのデータを張り付けて、毎月５分で終わる家計簿を紹介しています
- 節約・副業・投資が趣味で４０歳で資産が４千万円をオーバーしました
・この記事を書いている私のプロフィールはこちらです。
- 節約と投資と副業が趣味の40代サラリーマン
- 仕事は原価計算などお金に関する仕事一筋
- 趣味が興じて簿記2級を独学で取得
- 4人家族で生活費は月27万円。資産は4千万
- 35歳でうつ病を経験し、会社に依存しない生き方に挑戦中
年間収支管理が簡単なエクセル家計簿テンプレートの作り方
年間収支管理用のエクセルとは
私は日常的に仕事でエクセルを使用していて、実験結果のまとめや予算管理に欠かせません。
データを簡単に整理したり、グラフ化で見やすく管理するのに、エクセルは仕事やプライベートに関係なく、本当に便利です。
そしてプライベートで一番扱っているデータって何だろうって考えると、まず思い浮かぶのがお金の管理じゃないでしょうか？
実際に我が家で家計簿をエクセルでつけるようにしたら、圧倒的に楽になりました。
年間収支管理用のエクセルのイメージ
エクセルの特徴
今回紹介するエクセルの特徴は、難しい機能を一切使っていない初心者向けという点です。
多くのエクセル式家計簿で紹介されているものは、「ピポットテーブル」や「マクロ」などを利用しています。
高度な機能を使用している分、何か本格的・難しいことが出来るように感じますが、一般的な家計簿で必要な機能には必要ありません。
かえって自分で仕組みが理解できないと、エラーに対処できないし、カスタマイズして使えません。
エクセルの全体図
１年間の収支を管理するシートと、毎月の支出を入力するシートからできています。
こちらが一年の収支を管理するシートです。
こちらが毎月の支出を入力するシートです。
それぞれの内容について次に紹介します。
年間収支管理用のエクセルの構成
毎月の支出入力シートの使い方
こちらが毎月の支出の入力するシートの記入方法です。
1か月分を1つのシートに入力します。
支出入力表に支払った日付・内容・金額・金融機関・項目などを入力します。
そうすると下記のように、エクセルの機能で自動的に項目ごとに集計します。
年間の支出管理シートの使い方
これをさらに年間の支出を管理する表に自動で転記させると、下記の表が完成します。
あとは上記の支出の累計データを、エクセルで自動的に見やすくグラフにします。
赤線が年間の予算で、青い棒グラフが毎月の実績です。
また上記とは別に出費の項目ごとに分析するために、さらに自動で別のグラフを作って見やすくします。
下記のグラフは左が「水道光熱費」、右が「食費」の毎月の支出の推移です。
赤い破線が予算なので、使い過ぎが一瞬で分かります。
年間収支管理用のエクセルを使う必要はある？
支出管理用のエクセルのイメージは伝わったでしょうか？
家計の状態が見やすくなるように、思われるかもしれません。
でもこんなふうにも感じるかもしれません。
- ここまでやらなくても・・
- 別に家計の状態が分からなくても、うちは浪費していないから
- 普通の手書きの家計簿でも十分だよ
これらの疑問に、回答します。
年間収支管理用のエクセルを使うメリット
私は支出管理用のエクセルを使うようになってから、私の家計に起きた変化です。
- 浪費してるつもりは無かったのに、無駄使いがたくさん見つかった
- 無意識に節約を続けられて、毎月の支出が約12万円減った
- 毎月5分で家計簿の作成が終わるようになった
- 旅行や趣味などに使えるお金が増えた
年間収支管理用のエクセルを使うデメリット
デメリットは特にありませんが、初歩的なエクセルの操作や仕組みを理解することが必要です。
- 簡単な足し算などの関数を理解する
- データをグラフ化する
- 家計簿アプリからデータをコピーしてエクセルに張り付ける
といった基本的な内容は知る必要があります。
少し手間に感じるかもしれませんが、エクセルの使い方を覚えるとその他にも重宝しますので無駄にはなりません。
では次の章で具体的に、支出管理用のエクセルの作り方について詳しく説明します。
年間収支管理用エクセルテンプレートをパソコンで作る方法
支出管理用のエクセルをつける手順は全部で6つです。
- STEP1 月ごとの支出を入力するシートを用意する
- STEP2 支出を管理する項目を決める
- STEP3 支出を自動仕分けする方法
- STEP4 年間支出管理シートに自動転記させる
- STEP5 支出累計と貯蓄累計を求める
- STEP6 グラフ化で支出を見やすく管理する
STEP1 月ごとの支出を入力するシート
1行目に「日付」「内容」「金額」などの必要な情報を作成します。
1行目に何を用意するかは、自分がどのように分類わけをしたいかを考えて決めていきます。
【補足】
私の場合、上記のようなフォーマットになっているのには理由があります。
それは「マネーフォワード」という、自動家計簿ツールのデータを張り付けるだけで完成するようにするためです。
「マネーフォワード」については別記事で紹介しています。
STEP2 支出を管理する項目を決める
さきほどの入力シートの「大項目」や「中項目」に入れる内容を決めます。
これは自分が管理しやすいように、決めればOKです。
私の場合は、下記のように分けていて、その理由は「マネーフォワード」と統一するためです。
細かい内訳
- 収入：給料、副業収入、配当金、利息など収入全て
- 日用品：アマゾン、ホームセンター、コンビニ、イオンや小売店などで購入した物
- 衣服・美容：美容院代、衣料品店で使ったお金
- 通信費：スマホ契約会社
- 水道・光熱費：水道局、電気・ガス契約会社に払ったお金
- 食費：スーパーとレストラン、カフェなど外食すべて
- 住宅：住宅ローンと管理費
- 趣味・娯楽：旅費、遊びや趣味で使ったお金
- 自動車：ガソリン代、保険代、車両代、メンテナンスなど
- 交通費：公共交通機関
- 現金・カード：カードのATMでの引き落とし
- 教養・教育：学校の費用、習い事
- 健康・医療：通院代、薬代など
- 税・社会保障：固定資産税、自動車税など
- 保険：生命保険、火災保険、地震保険など
- 特別な支出：上記以外
私の場合は、支払った内容では無く、支払ったお店やサービスで項目を選んでいます。
理由は「マネーフォワード」の設定をわざわざ変える必要が無いからです。
STEP3 支出を自動仕分けする方法
次にSTEP1で入力したデータを、STEP2で決めた項目ごとに集計します。
イメージは下記の通りです。
例えば、「日用品」に支出した件数を集計する場合は、このようになります。
上の図で赤枠で囲ったマスに下記の数式を入力します。
「=COUNTIF($G$3:$G$250,M3)」
これで、「G3~G250」までの間に、何回「M3」の言葉（日用品）が現れたか計算します。
次に日用品の金額を集計する方法です。
今度は件数の横、赤枠内に日用品に支出した合計額を出します。
上の図で赤枠で囲ったマスに下記の数式を入力します。
「=-1*SUMIF($G$3:$G$250,M3,$E$3:$E$250)」
最初に-1をかけているのは支出をマイナス値で入力しているのを、プラスに直すためです。
この数式の意味は、「G3~G250」までの間に、「M3」と同じ単語があれば、そのときのE列の数字を合計しなさいということです。
STEP4 年間支出管理シートに自動転記させる
次は先ほど集計した項目別のデータを、年間の支出管理表に転記します。
例えば2021/1の収入を、下記の左の年間管理表に転記する方法を説明します。
年間管理表の「2021年1月」の収入の欄に以下の数式を入力します。
「＝シート名！O2」
シート名の部分は自分で1月の支出シートに付けた名前を入れます。
あとは同じように12月までの支出シートと、年間管理表をリンクさせます。
STEP5 支出累計と貯蓄累計を入れる
グラフ化で見やすく管理するために、支出累計と貯蓄累計を求めます。
支出累計
1月なら1月の支出の合計、2月なら1月の支出の累計+2月の支出、3月なら・・（続く）
というように前月までの累計支出に、当月の支出額を足していけば求まります。
貯蓄累計
1月なら1月の「収入-支出」、2月なら「1月の貯蓄」+「2月の収入」-「2月の支出」、3月なら・・（続く）
というように前月までの貯蓄に、当月の収入と支出の差額を足して求めます。
STEP6 グラフ化で支出を見やすく管理する
年間管理表のままだと見づらいので、グラフ化で分かりやすくします。
累計支出を下記のように、1月から12月まで選択して、縦棒グラフを作ります。
そうすると、下記のようなグラフが完成します。
5月以降のデータが無いので、おかしなグラフになっていますが、データを入れれば年間の支出グラフが完成します。
あとは年間の予算をあらかじめ作っておけば、下記のようなグラフが作れます。
同様に貯蓄累計の年間管理表を、グラフにするとこうなります。
支出管理エクセルとマネーフォワード
前章で何回か話が出たマネーフォワードについて、少しここで触れます。
マネーフォワードは家計簿作成用のサービスです。
銀行口座やクレジットカードなどと連携して、自動的に家計簿をつけてくれます。
マネーフォワードとは
- 2,000を超える金融サービスと提携している家計簿アプリ
- 銀行口座、クレジットカード、証券会社も一括で管理ができる
- 使ったお金の項目（食費・光熱費など）を自動で振り分け
- 無料版の機能でも家計簿の作成が便利になる
なぜマネーフォワードがお勧めかというと、エクセル家計簿に張り付けるだけで、毎月の家計簿が完了するからです。
詳細はこちらの記事で紹介しています。
【無料ダウンロードあり】支出管理エクセルの入手方法
支出管理エクセルの作成方法は以上になります。
あとは自分の使いやすいようにカスタマイズして、さらにマネーフォワードと同時に使用すれば、一生使い続けられる家計簿になると思います。
ただエクセルが苦手な人もいると思います。
ぜひ多くの人にエクセル家計簿の便利さを体験していただきたいので、無料のお試し版をダウンロードできるようにしました。
この無料版は1か月分のシートしかありませんが、シートを複製して1年以上継続して家計簿をつけ続けるだけでも、マネーフォワードプレミアム会員級に入るのと同等の機能があります。
さらに年間で家計管理ができる有料版のシートにも興味を持たれましたら、こちらの記事で紹介しています。
最後に
私は過去に有料の家計簿アプリやサービスを幾つか試したことがあって、もっと高機能だったり、見た目が美しいものを体験してきました。
しかしいずれも使い方が難しく覚えるのが大変だったり、毎月何時間も作業時間がかかったりして続きませんでした。
そこで仕事の経験を活かして、自作することを思いつきました。
５年間少しずつ改良を加えながらエクセルは完成しました。
初心者には難しいマクロや、ピポットテーブルを一切使わずに、極力シンプルで簡単なものに仕上げています。
一人でも多くの人の参考になれば幸いです。
さらに詳しい家計簿に関する知見をこちらの記事でまとめています。
このブログでは節約・副業・投資に関する資産形成に役立つ情報を紹介しています。
一人でも多くの読者の方が、今日から豊かな人生設計を始めるきっかけになればと思っています。
良かったら気になる記事があれば、こちらから是非お読みください。
最後までお読みいただきありがとうございました。
※関連記事です
←この記事がお役に立ちましたらクリックしていただけると励みになります
人気ブログランキング
←この記事がお役に立ちましたらクリックしていただけると励みになります
にほんブログ村
コメント