【12枚の画像で解説】自動計算Excel家計簿の作り方

本記事ではこんなお悩みを解決します。

Excel家計簿の全体図について

上記の画像がExcel家計簿の全体図です。家計簿の項目について知りたい方はこちらの記事をごらんください。Excel家計簿の作成手順は下記の3STEPです。

1. 外枠を作る
2. 合計の関数を入力する
3. 自動集計表を作る

「Excel家計簿」というタイトルですが、Googleスプレッドシートでももちろん作成可能です。

Excel家計簿の作り方①外枠を作る

まずは外枠を作っていきます。Excelの「ホーム」タブから枠線を作成します。

A列からE列には収入、収支計算、積立費用の表が入ります。

収入の表はA列とB列、3~6行目です。A列に収入の項目(給与等)、B列に金額を入れます。

収支計算の表はD列とE列、3~7行目です。D列には大項目である「①積立費用②固定費用③貯蓄・投資④変動費」を入れます。E列には次のステップで入力します。

積立費用の表はA列からE列、9~25行目です。A列に「項目」、B列に「今月積立分」、C列に「先月積立分」、D列に「今月利用額」、E列に「今月の合計(残高)」が入ります。行は必要に応じて調整してください。

G列からL列は固定費用、貯蓄・投資、変動費の表が入ります。

固定費用の表はG列とH列、4~11行目です。G列には項目(家賃、水道光熱費など)、H列には金額を入力します。

貯蓄・投資の表はJ列〜k列、4~11行目です。J列には口座、K列には目的、L列には金額を入れています。

変動費の表はG列〜J列、14~23行目です。G列には項目、H列には予算、I列には実際に使った金額、J列には予算と実際に使った金額の差額を入力しています。項目には変動費である食費、外食費、日用品、交通費、服・美容、自己投資、プレゼント、その他を入力します。

N列からQ列は変動費一覧表です。N列にお金を使った日付、Q列に具体的な内容、P列に項目、Q列に金額を入力します。P列の項目はプルダウン形式に作ります。詳しくはSTEP3で紹介します。

Excel家計簿の作り方②合計を入力する

SUM関数を入れる

外枠を作ったら、関数を入れていきます。

上記の画像の紫の四角の部分にSUM関数を入れていきます。具体的には下記の通りです。

• B6=sum(B3:B5)
• B25=sum(B10:B24)
• E10=sum(B10:C10)-D10
• H11=sum(H5:H10)
• I23=sum(I15:I22)
• J23=sum(J15:J22)
• L11=sum(L5:L10)

収支を計算する

収支計算表を入力します。

• ①積立費用「E3」のセルに「=B25」を入力
• ②固定費用「E4」のセルに「=H11」を入力
• ③貯蓄・投資「E5」のセルに「=H23」を入力
• ④変動費用「E6」のセルに「=B6-sum(E3：E5)」を入力
• 変動費用表の「H23」のセルに「=E6」を入力

④変動費＝収入ー(①積立費用＋②固定費用＋③貯蓄投資)とします。「変動費の余りを貯蓄・投資に回す」のではなく、「先に貯蓄・投資の金額を確保」します。

Excel家計簿の作り方③自動集計表を作る

最後に変動費の自動集計表を作ります。

右側の表に「日付」「詳細(何に使ったか)」「費目」「金額」を入力すると、項目ごとにいくら使ったかが自動で計算されます。予算を入力しておけば、予算との差もすぐにわかります。

変動費の予算の合計は「収支を計算する」の項目で入力しました。「H23セル(変動費の予算)の金額=H15~H22のセルの金額の合計」となるようにH15~H22の金額を入力してください。合計の下(H24セル)に「=sum(H15：H22)」を入力しておくと計算が簡単です。

しかし、変動費全体で予算内に収まればいいので項目ごとの予算はざっくりでOKです。例えば「外食が予算より1000円多かった」としても、変動費全体が予算内におさまっていれば内容は特に気にしません。

「今月は友達と飲みに行くから外食費を取っておこう」「今月は母親にプレゼントをあげるからプレゼント費を多めに取ろう」のように大ざっぱにお金を使う予定を把握するのに利用しています。

プルダウンを作る

変動費一覧表(使った内容や金額を記載するN列〜Q列の表)にプルダウンを作っていきます。

変動費一覧表の「P5」のセルを選択したままデータタブの中から、中央右あたりにある「入力規則」を選択します。「入力規則」から「数式」の中の「許可」で「リスト」を選んでください。

「元の値」として、変動費集計表の項目の部分を選択します。具体的には「G15：G22」です。

「P5」のセル(変動費一覧表の2行目)がプルダウンで選択可能になりました。「P6」以下にもコピー(またはオートフィル)すると、一覧表全体でプルダウンが利用可能になります。

自動集計表を作る

次に、自動集計表を作っていきます。使うのはSUMIF関数です。「=sumif(範囲、検索条件、合計範囲)」で検索条件に一致する行の合計を計算してくれます。

変動費集計表の「実際に使った金額」を表示する列(I列)にSUMIF関数を入れます。食費を集計する場合はI15セルです。

• 範囲：変動費一覧表の「項目」の列(P列)を選びます。
• 検索条件：集計したい項目です。変動費集計表のG列の中から選択します。例えば食費を指定したい場合は「G15」です
• 合計範囲：合計したい金額の列(Q列)を選びます

I15のセルに「=sumif($P$5:$P$27,G15,$Q$5:$Q$27)」が入ります。色で示すと、範囲、検索条件、合計範囲です。I15を入力し終えたら、I22までコピー(またはオートフィル)を使い入力します。

自動集計表が完成しました。

まとめ：Excel家計簿について

本記事ではExcel家計簿の作り方を紹介しました。

最後までお読みいただきありがとうございました。