エクセル家計簿の作り方（テンプレート2020年版有り）

家計簿の完成図としてはこんな形になります。こちらのシートは「全体」シートなので、各月で入力したデータが自動反映されている表です。

「項目一覧・説明」シートが選択されています。項目編集シートの完成図としてはこんな形になります。左に項目名を入力するエリア、右に仕訳する際に便利な項目ごとの説明文を用意しました。

収入と支出を分けると便利です。また収入を青色、支出を赤色にするとビジュアル的に認識しやすいかもしれません。

そして項目名を入力する数を限定すると良いです。前述したとおり、項目を細分化しすぎることによって、取引を選ぶときに大変になるからです。

「入力して良いセル」を分かりやすいように、「背景を白」にしました。収入より支出の項目が多くなりますので、このくらいの数に限定しております。

このフォーマットでは、画像の項目名を初期値で用意してありますが、ご自身で変更できるようにもしております。

項目名だけでは仕訳が分からないときに、分類名を説明しております。例えば交通費であれば「電車・タクシー・バス」が対象となります。車を持っている方であれば、「ガソリン代」を追加しても良いです。

項目名を追加/削除/編集した場合は、こちらの説明文を変えていただいていただき、ご自身で分かりやすい分類を編集しましょう。

「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を立ち上げると、スタート画面が表示されます。ここで【その他のテンプレート】を押します。

検索ボックスに『家計簿』と入力し、Enterを押します。

家計簿に関するフォートマットの一覧が表示されました。シンプルなフォーマットからかわいいフォーマットまで多様に用意されております。