- 公開日：
- 更新日：
エクセル家計簿の作り方（テンプレート2020年版有り）
家計簿をつけるツールとして、最近ではスマートフォンのアプリを利用する方が増えてきました。
しかしエクセルで家計簿を作ると、自分で思い通りのフォーマットやデザインにカスタマイズすることができます。仕訳する項目を自分で決めたり、見やすいグラフを作ったりすることも可能です。
エクセルでしっかりとした家計簿を一から作るには、様々な関数を利用しなくてはならないのでとても難しいですが、この記事で家計簿の作り方の手順を学ぶことができます。
Excelを使った家計簿のテンプレートを無料でダウンロード！
Excelを使った家計簿のテンプレートを無料でダウンロードできます。Excelで作られているので年数を変更したり項目を増やしたりとご自身でカスタマイズすることも可能です
家計簿を作る前の準備
家計簿を作る前に、取引を仕訳する項目を考えましょう。項目とは、収入であれば「給与」、支出であれば「食費」のようなものです。
家計簿の項目の種類に決まりはありません。家計簿のアプリや本によっても、項目の名称や分類方法は様々です。
項目の決め方については、過去の取引を振り返ってみることが大切です。例えば、投資を行っている方であれば収入で「投資収入」を設けるべきです。
また気をつけるべき点として、項目を細分化しすぎないことです。細分化しすぎると、取引を入力する際に項目を選ぶのが大変になるからです。例えば、電気代/ガス代/水道代を「水道光熱費」にまとめるか否かを、全体含めて考慮してください。
見やすい家計簿を自作で作る
それでは仕訳する項目が決定したら、エクセルを使って家計簿を作ってみましょう。
家計簿の完成図としてはこんな形になります。こちらのシートは「全体」シートなので、各月で入力したデータが自動反映されている表です。
項目編集シートの準備
まずは仕訳する項目を入力するシートから作っていきます。
「項目一覧・説明」シートが選択されています。項目編集シートの完成図としてはこんな形になります。左に項目名を入力するエリア、右に仕訳する際に便利な項目ごとの説明文を用意しました。
項目名を入力するエリアを作る
収入と支出を分けると便利です。また収入を青色、支出を赤色にするとビジュアル的に認識しやすいかもしれません。
そして項目名を入力する数を限定すると良いです。前述したとおり、項目を細分化しすぎることによって、取引を選ぶときに大変になるからです。
「入力して良いセル」を分かりやすいように、「背景を白」にしました。収入より支出の項目が多くなりますので、このくらいの数に限定しております。
このフォーマットでは、画像の項目名を初期値で用意してありますが、ご自身で変更できるようにもしております。
仕訳する際に項目の説明するエリアを作る
項目名だけでは仕訳が分からないときに、分類名を説明しております。例えば交通費であれば「電車・タクシー・バス」が対象となります。車を持っている方であれば、「ガソリン代」を追加しても良いです。
項目名を追加/削除/編集した場合は、こちらの説明文を変えていただいていただき、ご自身で分かりやすい分類を編集しましょう。
各月の取引入力シートの準備
次に各月で取引を入力するシートを作っていきます。
「1月」シートが選択されていますが1月～12月のシートは同じフォーマットになります。取引入力シートの完成図としてはこんな形になります。
左に取引の入力エリア、中央に取引項目の合算、右に合算したそれぞれの横棒グラフを配置しました。
対象月のエリアを作る
左上の対象月のエリアから説明します。
対象年は「全体」シートで年数を手入力するセルがありますので、その箇所を参照しています。
対象月は「1月」シートのシート名を関数で引っ張って来ております。ですので、シート名を変更するとこのセルも値が変わってしまいます。
数式は『=SUBSTITUTE(RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename",A2))),"月","")』となっており、ファイル名を参照しつつ、数値だけ取得したいので「月」の文字列を削除しております。
取引入力のエリアを作る
左下の取引入力のエリアを説明します。ここでは実際の取引を1行ずつ入力していくエリアです。関数やプルダウンを利用して極限まで自動化しました。
1ヶ月あたり、500行まで取引を入力することが可能です。
こちらも「入力して良いセル」を分かりやすいように、「背景を白」にしました。
E5セルに日にちを入力すると、自動的に曜日が表示される仕組みです。B2セルの対象年とD2セルの対象月と手入力したE5セルの日にちを組み合わせて、TEXT関数で曜日を出力しております。IF関数で空白の場合は、表示しないようにしております。
数式は、『=IF(E5="","",(TEXT($B$2&"/"&$D$2&"/"&E5,"aaa")))』となっております。
取引項目が収入なのか支出なのか選ぶ方法は、プルダウンになります。手入力で入力ミスをすると合算ができなくなるのを防ぐためです。
条件付き書式で、収入を選ぶとその行の文字色が青くなり、支出を選ぶと赤くなる設定にしております。
プルダウンのリストは「項目一覧・説明」シートの収入と支出の見出しのセルを絶対参照で指定しています。データの入力規則でリストを指定する際、「=項目一覧・説明!$B$4:$C$4」のように参照します。
項目を選ぶ方法も、プルダウンになります。左のセルで収入を選択したら、収入に関する項目しか現れません。支出なら支出の項目のみ現れます。
プルダウンを連動させるためにデータの入力規則でリストを指定する際、「=OFFSET(項目一覧・説明!$B$4,1,MATCH(F5,項目一覧・説明!$B$4:$C$4,0)-1,IF(MATCH(F5,項目一覧・説明!$B$4:$C$4,0)=1,COUNTA(項目一覧・説明!$B$5:$B$12),COUNTA(項目一覧・説明!$C$5:$C$28)),1)」のように参照しています。
OFFSET関数とMATCH関数を組み合わせて左のセルで選択された項目を取得し、「項目一覧・説明」シートの該当列をプルダウンのリストして設定します。プルダウン内で空白のリストが表示されないようにCOUNTA関数を使って空白でないセルの行数までを範囲としています。
プルダウンの連動については以下の記事でも説明していますのでもっと知りたい方はご覧ください。
収入と支出の合算値とグラフエリアを作る
右上の収入と支出の合算エリアを説明します。ここでは1ヶ月の収支の累計を数値とグラフで見ることができます。
収入の累計を算出するには、SUMIF関数を利用しております。F列で「収入」という文字列が入っているセルをすべて検索し、H列にある取引金額を合算します。
数式は、『=SUMIF(F:F,"収入",H:H)』となっております。
収入と支出の累計を算出したら、横棒グラフで視覚的にわかるようにすると便利です。グラフの色も収入と支出の色を「同じ青と赤」に合わせてあげると理解しやすいかもしれません。
各項目の合算値とグラフエリアを作る
右下の各項目の合算エリアを説明します。ここでは各項目に対する累計を数値とグラフで見ることができます。
今月はどの項目が一番コスト高になっているのか、他の項目と比較してどのくらい使っているのかを視覚的に把握できます。
各項目の累計を表示するには「ピボットテーブル」が一番最適なので利用しました。
ピボットテーブルのデータソース（参照範囲）は、入力した取引エリアを参照することで、収入と支出別に各項目の累計が表示されるようになります。
1点だけ注意点としては、入力したデータがリアルタイムにピボットテーブルに反映しないので、「手動でピボットテーブルの更新ボタン」を押すか、ファイルを再起動すると更新されるようになります。
そしてピボットテーブルの右側にあるグラフは「ピボットグラフ」といいます。ピボットテーブルでフィルタリングされたりしたデータをリアルタイムにグラフ化してくれる機能です。
分類別に表示してくれるので、大変見やすいグラフとなっております。
全体の収支シートの準備
最後に全体の収支シートを作っていきます。
「全体」シートが選択されています。全体の収支シートの完成図としてはこんな形になります。
左に1年を通した収支の月別のデータエリア、右に収入と支出の月別折れ線グラフとなっております。
対象となる年の入力エリアを作る
左上の対象年を入力するエリアです。
西暦で手入力します。このセルの数値を利用して、別シート「1月～12月」の取引曜日を自動で算出しております。
収入と支出の月別データ表示エリアを作る
左側には、1年を通した収入と支出を見ることのできるエリアがあります。
数式は『=IF(SUMIF(INDIRECT(F$6&"!$G$5:$G$500"),$C8,INDIRECT(F$6&"!$H$5:$H$500"))=0,"",SUMIF(INDIRECT(F$6&"!$G$5:$G$500"),$C8,INDIRECT(F$6&"!$H$5:$H$500")))』となっております。
長い数式になっていますが、SUMIF関数を使ってC列の項目を検索条件とし、各月のシートのG列、H列を検索範囲、合計範囲に指定しています。範囲はそれぞれオートフィルで数式をコピーできるようにINDIRECT関数を使って参照できるようになっています。
またIF関数で空白の場合は、0を表示しないようにしております。
下部に月別の合計をSUM関数で算出しております。『=IF(SUM(F$8:F$15)=0,"",SUM(F$8:F$15))』とすることで、0の場合は非表示にするようにしております。
収入と支出の月別推移グラフエリアを作る
右側には、1年を通した収入と支出のグラフ見ることのできるエリアがあります。
折れ線グラフの参照元データは、先程SUM関数で算出した月別の合計値となっております。
これで1年を通して、収入額と支出額の推移を把握することができます。グラフの線は収入を青色、支出を赤色にするとビジュアル的に認識しやすいかもしれません。
全体収支計算をする
最後に1年を通した「収益-支出」を算出して、黒字なのか赤字なのかを把握しましょう。
Excelで用意されているテンプレート
ここまではエクセルで作る家計簿の作り方の「一例」をご紹介しました。
同じエクセルで作る家計簿でも作る人が違えば、入力方法も出力方法もデザインも異なってきます。ここからは様々なテンプレートの取得方法をご紹介をいたします。
Excelを立ち上げると、スタート画面が表示されます。ここで【その他のテンプレート】を押します。
検索ボックスに『家計簿』と入力し、Enterを押します。
家計簿に関するフォートマットの一覧が表示されました。シンプルなフォーマットからかわいいフォーマットまで多様に用意されております。