自作してみたいけど、どうやって作れば良いか分からない・・・
当ブログでは、現在6種類のエクセル家計簿テンプレートを無料配布しています。
様々なテンプレートを作成することで、みえてきたエクセル家計簿のメリット、デメリットや作成ポイント、エクセル家計簿の作り方について解説していきます。
エクセル家計簿のメリット、デメリット
メリット
- 集計が楽にできる
- ほとんど無料でテンプレートをダウンロードできる。
- 自作することで、自分のライフスタイルにアレンジできる。
- グラフを作成することで、前月比などの比較や分析がしやすい。
デメリット
- エクセルに不慣れだと自分で作成するのが大変
- パソコンを立ち上げてエクセルを開く動作が手間
- テンプレートをダウンロードしたが、仕様に慣れるまでに時間がかかる。
- 自分のライフスタイルに合った家計簿テンプレートがみつからない。
エクセル家計簿を作る際のポイント
目的を明確にする
食費を節約したいのか、全体的な浪費を把握したいのかで作成するフォーマットは変わってきます。
食費を節約したい
→食費の項目を主食、副食、嗜好品と細かく分類する。
浪費を把握したい
→浪費の項目を色分けして視覚的に見やすくする。
などどんな目的で家計簿を作成したいのか考えてみましょう。
固定費と変動費を分ける
固定費：毎月必ず発生する費用（住居費や教育費等）
変動費：毎月支出があるが、金額が変動するもの
固定費と変動費を分類することで、家計の圧迫の原因が固定費なのか変動費なのか、それとも両方なのかを突き止めることができます。
また変動費は、予算を設定することで月の目標を達成しやすくなります。
1回の操作はなるべく少なくする
家計簿は続けることが大切です。
日付を入力、費目を入力、内容を入力、金額を毎回入力するのは大変ですよね？
日付や費目はあらかじめ入力するなど、どうやったら簡単に操作できるか考えましょう。
分からないことは検索する
関数の使い方が分からない、色をつけたいなど様々な疑問がでてくると思います。
そんなときは、「エクセル 色つけたい」などで検索するとほとんど解決できます。
私は検索しまくってます・・・
グラフを活用する
エクセルの利点は、分析ツールとしてグラフが活用できることです。
グラフを活用することで、理解度が深まります。
月の比較、費目の比率などグラフを活用してみましょう。
まとめ
- 目的をはっきりさせる
- 固定費と変動費を分類
- 1回の操作は少なく
- 分からないことは検索する
- グラフを活用する
エクセル家計簿の作り方
前述のポイントを抑えて、月のシート、年間推移表、グラフを作成してみます。
基本的なエクセル操作
エクセル家計簿を作成するにあたって使用する、基本的な操作をまとめてみました。
毎月のシート
完成イメージ
- 収入、貯蓄、固定費、変動費に分類
- 収入ー(貯蓄＋固定費)で、月の予算を把握
- 収入ー(貯蓄＋固定費＋変動費)で、残金を月の終わりに把握
- 変動費は、費目、日にちごとに分類して品名と金額を記入
- 円グラフで貯蓄、固定費、変動費の割合を把握
収入、貯蓄、固定費、変動費の枠を作成
収入、項目名、金額を入力して、合計金額には、数式を入力
同様に貯蓄、固定費、変動費の枠を設定(変動費には、予算の項目を追加）
月の予算と残金を設定
収入ー(貯蓄＋固定費)＝予算
収入ー(貯蓄＋固定費＋変動費)＝残金
結合したセルにそれぞれの合計金額を設定する。
コピーして貼り付けるよりも、＝C11のように合計金額のセルを表示できるようにすると毎回貼り付ける手間が省ける。
予算や残金には、＝B17-C23のように式を入力
変動費の入力
始めの日付と曜日を記入して、ドラッグまたはコピーして貼り付けると最終日まで設定できます。
土曜日は青文字、日曜日と祝日は赤字で表示。
背景の色も変更。
一番下には、合計金額を表示。
この合計金額を上の変動費の表に表示されるように設定します。
同じ費目で、一日に複数回記入する場合は、右クリックで行を挿入してください。
月のシートが完成したら、シートをコピーして新しいシートを作成します。(１２ヶ月分）
日付設定の仕方(上級編)
そこで、少し上級編にはなりますが、数式と条件付き書式を使用してかんたんに変更できる機能を追加してみます。
この機能を使用することで、一番上に表示している２０２２年１月の月部分を変更するだけで、日付、曜日、文字色、背景色まで自動で変更することができます。
- 初日の日付と曜日に数式を入力
- ２日目以降の日付に数式を入力
- WEEKDAYを取得
- 条件付き書式で、土日の文字色を変更
- 祝日を設定して、条件付き書式で文字色を変更
- 土日、祝日の背景色を条件付き書式で変更
日付と曜日設定
初日の日付１日のセルに＝DATE(B1,D1,1)と入力。
B1、D1にはあらかじめ年と月を入力します。(完成イメージの一番上の部分)
給料日などに合わせて始まりの日付を変更したい場合は、最後の１の部分に希望の日付を入力してください。
２日目には＝B27+1と入力。(１日目のセルに１を足した日にち）
曜日は、＝B27と入力。(＝隣の日にちが入力されているセル)
日付は、２日目のセルをコピーして３１日目まで貼り付け。
曜日は、始めのセルをコピーして３１日目まで貼り付ける。
セルの書式設定で、日付のセルを選択して、ユーザー定義ｄと入力。
曜日のセルを選択して、ユーザー定義aaaと入力する。
土日の文字色の変更
１日目の日付の左のセルに＝WEEKDAY(C27)と入力。(＝WEEKDAY(日付)で曜日を１から７で表示させる）
土日のセルを確定するために、WEEKDAYを取得します。
セルをコピーして３１日目まで貼り付ける。
このWEEKDAYの表示は、土日の文字色と背景色を設定後に、非表示にするか文字色を白色にしてみえないようにしてください。
非表示設定方法は、行を選択して、右クリック、「表示しない」をクリック。
土曜日は７、日曜日は１と表示される。
条件付き書式でA列の値が７のときは青色、１のときは赤色に表示するように設定する。
日付と曜日の範囲(B27からC57)を選択して、ホーム画面にある「条件付き書式」をクリック。
「新しいルール」、「数式を使用して、書式設定するセルを決定」をクリック。
数式の欄には、＝$A27=7 と入力。
（WEEKDAYが入力されているA列のA27以降の値が７(土曜日)のとき）
書式をクリックして青色を選択する。
ホーム画面の条件付き書式のルールの管理から入力した内容を確認。
適用先：選択した範囲(青色に表示したい範囲)
数式：＝$A27=7
書式：青色
同様に日曜日も設定する。
数式：=$A27=1
書式：赤色
祝日設定と文字色変更
年間の祝日一覧を検索して、日付を新しいシートに貼り付けます。
日付一覧のみを選択して、数式の名前の定義をクリックして「祝日」と名前付けする。
家計簿の月シートに戻って、日付と曜日の範囲を選択。
条件付き書式の新しいルールから数式を設定して書式を赤色に変更。
＝COUNTIF(祝日,$B27)=1
$B27は、日付の列の１日目以降で日付の中に祝日があるかを判別します。
土日、祝日の背景色を変更
曜日の列の背景色を変えたいので、範囲は、シート全体を選択する。
条件付き書式の新しいルールから数式と背景色を設定。
数式は、文字色の設定で使用した数式と同じ内容を入力。
書式は、塗りつぶしの背景色を好きな色に設定する。
目的別月シートの作成
今回は、食費を節約したい場合と無駄遣いを減らしたい場合２通りの家計簿を設定してみます。
食費を節約
食費を節約する際の一例として、嗜好品(お酒やお菓子など)を削減する場合、
食費を①必須②嗜好品③その他に分類して、１ヶ月にどれくらいの嗜好品を消費しているかを知ることができます。
①変動費の項目欄に行を追加して、食費の項目を３つに分類
②変動費の合計の表に食費の合計欄を追加
無駄遣いを減らす
無駄遣いを減らすには、毎月どのくらいの無駄がひそんでいるのかを洗い出す必要があります。
- 費目の欄に浪費の項目を追加して、無駄遣いをした金額を表示させる。
- 無駄遣いの項目、金額に色付けして、月の終わりに合計金額を表示する。
②の無駄遣いに色付けをする作業は、その都度色付けをする方法だと合計金額を計算するのが大変になります。
今回は、規制リスト、条件付き書式、数式を使用して、浪費と選択すると自動で色付け、自動で合計金額を表示できるようにします。
金額の欄の右隣に列を追加する。
リストを表示させたいセルを選択。
データの「入力規制」をクリックして「リスト」を選択。
元の値の欄に、浪費と入力（投資の項目も追加する場合は、「浪費,投資」と入力。（カンマは必ず半角で）
条件付き書式で、浪費と投資を選択したときに背景色が変わるように設定します。
色を変えたい範囲を選択
ホーム画面、条件付き書式、新しいルール、「数式を使用して、書式設定するセルを決定」をクリック
数式には、＝$J15="浪費”と入力
(J列のJ15以降のセルが「浪費」と表示されたとき
セルの書式設定、背景色、塗りつぶしから好きな色を選択してOKをクリック
同様に、「投資」も条件付き書式で色を設定する。
設定したセルをクリックすると、プルダウンが表示されるので、浪費または投資をクリックすると色が変わることを確認してください。
修正する場合は、条件付き書式のルールの設定から確認、編集できます。
同じシート内に、消費、浪費、投資の金額と比率を表示するための表を作成します。
浪費と投資の金額はSUMIF関数を使用して合計金額を表示。
浪費の金額セルに＝SUMIF(J15:AE45,"浪費",I15:AE45)
投資の金額セルに＝SUMIF(J15:AE45,"投資",I15:AE45)
合計金額には、変動費の合計金額を表示。
消費の金額セルは、＝合計金額ー(浪費金額＋投資金額)と入力。
比率は、消費÷合計金額＝C54/C57と入力して右クリックのセルの書式設定から単位をパーセンテージに変更。
この場合、金額が入力されていない状態だと＃DIV/0!と表示されます。
気になる方は、＝IFERROR(C54/C57,0)と入力すると０と表示、＝IFERROR(C54/C57,"")と入力すると空白になります。
年間推移表
新しいシートに年間推移表を作成します。
縦軸には収入、貯蓄、固定費、変動費、支出合計、収入ー支出
横軸には、１月から１２月までと年間合計を表示
それぞれの値は、都度コピーして貼り付けるか、＝サンプル!C5のように他のシートの値を表示することもできます。
支出合計は、SUM関数で貯蓄合計、固定費合計、変動費合計を設定。
収入ー支出は＝収入合計ー支出合計と入力。
グラフを作成
推移表のグラフを作成
年間推移表のすべてのデータを選択して、挿入、棒グラフを選択。
グラフが表示されるので、グラフの内容を修正するために右クリックで「データの選択」をクリック。
「行/列の切り替え(W)」をクリック、表示させたいデータのチェックを残して、表示させたくないデータのチェックを外す。
横軸ラベルには、１月から１２月までを選択。
縦軸には、収入合計、貯蓄合計、固定費合計、変動費合計を選択。
収入と支出を分類するために、グラフの種類を変更する。
グラフを選択して、右クリック、グラフの種類の変更を選択。
「組み合わせ」をクリック、収入には折れ線グラフを選択、支出項目には、積み上げグラフを選択する。
グラフの書式や色を変更する場合は、右クリック、データ系列の書式設定をクリック。
グラフ右上の＋マークをクリックして、データテーブルを選択すると、グラフの下にデータを表示させることができる。
毎月の円グラフを作成
円グラフの作成する場合、データは推移表のデータを使用して作成します。
推移表のグラフ同様に、データを選択、挿入をクリック、円グラフのマークを選択。
円グラフが表示されるので、グラフを選択して右クリック、データの選択をクリックする。
表示させたいデータのチェックを残して、表示させたくないデータのチェックを外す。
凡例項目は、1月(グラフを作成したい月を選択)
横軸ラベルには、変動費合計、固定費合計、貯蓄合計を選択
印刷して使用する場合
ページレイアウトをクリック。
余白の調整、印刷の向き(縦か横かを選択)、印刷用紙サイズの選択、
印刷する範囲を選択して、プリント範囲の設定をクリック。
行間や列の幅を調整する場合は、行または列を選択して右クリック、「列の幅」「行の高さ」を選択してサイズ調整をしてください。
セルに数式が入っており、¥０と表示されている箇所(合計金額など)は、印刷する前に文字色を白にするなどみえないようにしてください。
サンプルダウンロード
エクセル家計簿の作り方で説明したサンプルシートを無料ダウンロードできます。
サンプル内容は、毎月のシート、目的別シート(食費の節約、浪費の減らす)、年間推移表、グラフです。
ダウンロードボタンをクリックするとエクセルファイルがダウンロードできます。
