エクセル家計簿にマクロを設定してみた【使い方と作り方を徹底解析】

 

エクセルで家計簿をつけたい。計算とかグラフの設定ってどうやってやるの?

 

こんな要望、疑問にお答えします。

 

この記事では、「マクロを設定したエクセル家計簿」について詳しくご紹介しています。

 

Point

 

  • 無料で家計簿をつくりたい
  • やるからには長く使える家計簿にしたい
  • 自分だけの使いやすい家計簿を作りたい

 

このような考えを持っている方にとっておすすめの内容になっています。

 

紹介している「マクロを設定したエクセル家計簿」は

我が家で実際に使っているもので、いつも妻が操作してくれています。

 

マクロめっちゃ使いやすくて便利」と高評価。

 

アプリや専用ツールではなく

エクセルで家計簿を付けたい!と考えているは是非チェックしてみてください。

 

解説はいいから「マクロを設定したエクセル家計簿」を使って帳簿を付けたい!

という方は下記のサイトを参考にしてください。


エクセル家計簿をダウンロードしてすぐにお使い頂けます。

 

広告

マクロが設定されたエクセル家計簿とは

 

 

動画でエクセル家計簿の使い方や設定されている機能の紹介をしています。

 

エクセル家計簿には

「記入する」「確認する」作業を簡単にするために、

4つのマクロが設定されています。

 

 

  • 金額を集計するマクロ
  • 金額をグラフ化するマクロ
  • ドロップダウンリストを自動作成するマクロ
  • 年数と日付から曜日を自動入力するマクロ

 

設定したマクロのおかげで実際に操作することと言えば、

  1. レシートの日付を入力
  2. 項目をリストから選択入力
  3. レシートの金額を入力
  4. マクロ実行ボタンをクリック

たったこれだけ。

 

Left Caption

ひらめく人

家計簿への入力作業がめちゃくちゃ簡単

設定されたマクロについて詳しく解説していきます。

 

金額集計マクロ

金額集計マクロは、

入力した数値の合計を自動的に計算してくれる

マクロです。

 

どのような処理をするかというとこんな感じ。

下の画像は内容を入力する前の状態です。

 

内容を入力して「集計実行ボタン」を押すと、このように画面が変化します。

 

「支出合計」「収入合計」「日付別項目合計」「日付別細目合計」が自動で計算され、

さらに

「全体集計」シートに各月ごとの集計結果が自動反映される仕組みになっています。

 

 

集計作業は、

レシートの内容を打ち込んで「集計実行」ボタンをクリック

たったこれだけで完了です。

 

設定されているマクロがこちら

Sub 月別シート計算()
     Dim Ash As Worksheet
     Set Ash = ThisWorkbook.Worksheets("項目・設定")
     Dim syu As Long
     retua = Ash.Cells(1, Columns.Count).End(xlToLeft).Column
     gyoa = Ash.Cells(Rows.Count, 1).End(xlUp).Row
     retu = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
     gyo = ActiveSheet.Cells(Rows.Count, 6).End(xlUp).Row
     If (ActiveSheet.Cells(4, 9) = "") And (ActiveSheet.Cells(4, 13) = "") Then
     Exit Sub
     End If
     '合計支出計算
     ActiveSheet.Range(ActiveSheet.Cells(4, 40), ActiveSheet.Cells(4, 42)).ClearContents
     For i = 4 To gyo
     If ActiveSheet.Cells(i, 6) = "支出" Then
     ActiveSheet.Cells(4, 40) = ActiveSheet.Cells(4, 40).Value + ActiveSheet.Cells(i, 17).Value
     Else
     End If
     Next
     '合計収入計算
     ActiveSheet.Range(ActiveSheet.Cells(4, 43), ActiveSheet.Cells(4, 45)).ClearContents
     For i = 4 To gyo
     If ActiveSheet.Cells(i, 6) = "収入" Then
     ActiveSheet.Cells(4, 43) = ActiveSheet.Cells(4, 43).Value + ActiveSheet.Cells(i, 17).Value
     Else
     End If
     Next
     '項目設定
     retu3 = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column + 1
     ActiveSheet.Range(ActiveSheet.Cells(3, 48), ActiveSheet.Cells(35, retu3)).ClearContents
     ActiveSheet.Range(ActiveSheet.Cells(3, 48), ActiveSheet.Cells(35, retu3)).ClearFormats
     For i = 4 To gyo
     retu4 = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
     For j = 47 To retu4 + 1
     If (ActiveSheet.Cells(i, 9) = ActiveSheet.Cells(3, j)) Then
     GoTo L2
     ElseIf (ActiveSheet.Cells(3, j) = "") And ActiveSheet.Cells(i, 6) = "支出" Then
     ActiveSheet.Cells(3, j) = ActiveSheet.Cells(i, 9)
     GoTo L2
     ElseIf ActiveSheet.Cells(i, 9) <> ActiveSheet.Cells(3, j) Then
     Else
     End If
     Next
L2:
     Next
     For i = 4 To gyo
     For j = 4 To 34
     For k = 48 To retu4 + 1
     If (ActiveSheet.Cells(i, 4) = ActiveSheet.Cells(j, 47)) And (ActiveSheet.Cells(i, 6) = "支出") And (ActiveSheet.Cells(i, 9) = ActiveSheet.Cells(3, k)) And (ActiveSheet.Cells(j, k) = "") Then
     ActiveSheet.Cells(j, k) = ActiveSheet.Cells(i, 17)
     ElseIf (ActiveSheet.Cells(i, 4) = ActiveSheet.Cells(j, 47)) And (ActiveSheet.Cells(i, 6) = "支出") And (ActiveSheet.Cells(i, 9) = ActiveSheet.Cells(3, k)) And (ActiveSheet.Cells(j, k) <> "") Then
     ActiveSheet.Cells(j, k) = ActiveSheet.Cells(j, k).Value + ActiveSheet.Cells(i, 17).Value
     Else
     End If
     Next
     Next
     Next
     For k = 48 To retu4 + 1
     syu = Application.WorksheetFunction.Sum(Range(Cells(4, k), Cells(34, k)))
     ActiveSheet.Cells(35, k) = syu
     Next
     ActiveSheet.Range(Cells(3, 47), Cells(3, retu4 + 1)).Columns.AutoFit
     ActiveSheet.Range(Cells(3, 47), Cells(35, retu4 + 1)).Borders.LineStyle = xlContinuous
     ActiveSheet.Range(Cells(3, 47), Cells(35, retu4 + 1)).Borders.Weight = xlThick
     ActiveSheet.Range(Cells(3, 47), Cells(35, retu4 + 1)).Borders(xlInsideVertical).LineStyle = xlContinuous
     ActiveSheet.Range(Cells(3, 47), Cells(35, retu4 + 1)).Borders(xlInsideVertical).Weight = xlThin
     ActiveSheet.Range(Cells(3, 47), Cells(35, retu4 + 1)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
     ActiveSheet.Range(Cells(3, 47), Cells(35, retu4 + 1)).Borders(xlInsideHorizontal).Weight = xlThin
     '細目設定
     retu5 = ActiveSheet.Cells(37, Columns.Count).End(xlToLeft).Column + 1
     ActiveSheet.Range(ActiveSheet.Cells(37, 48), ActiveSheet.Cells(69, retu5)).ClearContents
     ActiveSheet.Range(ActiveSheet.Cells(37, 48), ActiveSheet.Cells(69, retu5)).ClearFormats
     ''''項目作成''''
     For i = 4 To gyo
     retu6 = ActiveSheet.Cells(37, Columns.Count).End(xlToLeft).Column + 1
     For j = 47 To retu6 + 1
     If (ActiveSheet.Cells(i, 13) = ActiveSheet.Cells(37, j)) Then
     GoTo L3
     ElseIf ActiveSheet.Cells(37, j) = "" And ActiveSheet.Cells(i, 6) = "支出" Then
     ActiveSheet.Cells(37, j) = ActiveSheet.Cells(i, 13)
     GoTo L3
     ElseIf ActiveSheet.Cells(i, 13) <> ActiveSheet.Cells(37, j) Then
     Else
     End If
     Next
L3:
     Next
     ''''項目集計作成''''
     For i = 4 To gyo
     For j = 38 To 68
     For k = 48 To retu6
     If (ActiveSheet.Cells(i, 4) = ActiveSheet.Cells(j, 47)) And (ActiveSheet.Cells(i, 6) = "支出") And (ActiveSheet.Cells(i, 13) = ActiveSheet.Cells(37, k)) And (ActiveSheet.Cells(j, k) = "") Then
     ActiveSheet.Cells(j, k) = ActiveSheet.Cells(i, 17)
     ElseIf (ActiveSheet.Cells(i, 4) = ActiveSheet.Cells(j, 47)) And (ActiveSheet.Cells(i, 6) = "支出") And (ActiveSheet.Cells(i, 13) = ActiveSheet.Cells(37, k)) And (ActiveSheet.Cells(j, k) <> "") Then
     ActiveSheet.Cells(j, k) = ActiveSheet.Cells(j, k).Value + ActiveSheet.Cells(i, 17).Value
     Else
     End If
     Next
     Next
     Next
     For k = 48 To retu6
     syu = Application.WorksheetFunction.Sum(Range(Cells(38, k), Cells(68, k)))
     ActiveSheet.Cells(69, k) = syu
     Next
     ActiveSheet.Range(Cells(37, 47), Cells(37, retu6)).Columns.AutoFit
     ActiveSheet.Range(Cells(37, 47), Cells(69, retu6)).Borders.LineStyle = xlContinuous
     ActiveSheet.Range(Cells(37, 47), Cells(69, retu6)).Borders.Weight = xlThick
     ActiveSheet.Range(Cells(37, 47), Cells(69, retu6)).Borders(xlInsideVertical).LineStyle = xlContinuous
     ActiveSheet.Range(Cells(37, 47), Cells(69, retu6)).Borders(xlInsideVertical).Weight = xlThin
     ActiveSheet.Range(Cells(37, 47), Cells(69, retu6)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
     ActiveSheet.Range(Cells(37, 47), Cells(69, retu6)).Borders(xlInsideHorizontal).Weight = xlThin
     retu = ActiveSheet.Cells(37, Columns.Count).End(xlToLeft).Column + 5
     gyo = ActiveSheet.Cells(Rows.Count, 47).End(xlUp).Row + 5
     ActiveSheet.Range(Cells(1, 47), Cells(gyo, retu)).Interior.Color = RGB(255, 242, 209)
End Sub

 

めちゃくちゃ長いプログラムになっていますが、

難しい関数やVBAはほとんど使われていません

 

「集計項目が多い」、「罫線表示マクロが設定されている」という理由から長文になっているだけで、

一つ一つ分解して注目してみると簡単なマクロの組み合わせだとわかってもらえるかと思います。

 

グラフを自動作成するマクロ

グラフ自動作成するマクロは、

集計した合計結果からいろんな種類のフラフを自動作成する

マクロです。

 

 

  • 項目別支出
  • 細目別支出
  • 合計収入・支出
  • 月別集計
  • 年別集計

 

グラフを見ることで、

「収入と支出のバランスは適切か」「無駄使いしている項目はあるか」

などを一目で確認することができます。

 

どのようなグラフが作成されるかというと、こんな感じ

 

設定されているマクロがこちら

Sub 月別シートグラフ()
     Dim Po As Range
     Set Po = ActiveSheet.Cells(3, 22)
     Dim Po2 As Range
     Set Po2 = ActiveSheet.Cells(15, 22)
     Dim Ta As Range
     Dim Ta2 As Range
     Dim Po3 As Range
     Set Po3 = ActiveSheet.Cells(27, 22)
    gyo = ActiveSheet.Cells(Rows.Count, 47).End(xlUp).Row
    retu = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
    retu2 = ActiveSheet.Cells(37, Columns.Count).End(xlToLeft).Column
    For Each sa In ActiveSheet.Shapes
    On Error Resume Next
    If sa.TopLeftCell.Address >= ActiveSheet.Cells(1, 1).Address Then
    sa.Delete
    End If
    If Err <> 0 Then
    Err.Clear
    End If
    Next
        '収入・支出グラフ作成
    With ActiveSheet.Shapes.AddChart.Chart
    .ChartType = xlColumnClustered
    .SetSourceData Source:=ActiveSheet.Range("AN3:AN4,AQ3:AQ4")
        'タイトル追加
    .HasTitle = True
    .ChartTitle.Text = "収入・支出"
    .Parent.Top = Po.Top
    .Parent.Left = Po.Left
    .HasLegend = False
    .Axes(xlCategory).HasTitle = False
    .FullSeriesCollection(1).Points(1).Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
    .FullSeriesCollection(1).Points(2).Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent5
    End With
     '項目別グラフ作成(円項目)
    With ActiveSheet.Shapes.AddChart.Chart
    .ChartType = xlPie
     Set Ta = Range(Cells(3, 48), Cells(3, retu))
     Set Ta2 = Range(Cells(35, 48), Cells(35, retu))
     .SetSourceData Source:=Union(Ta, Ta2)
    'タイトル追加
    .HasTitle = True
    .ChartTitle.Text = "項目別支出"
    .Parent.Top = Po2.Top
    .Parent.Left = Po2.Left
    .SeriesCollection(1).ApplyDataLabels ShowPercentage:=True, ShowValue:=False
    End With
  '項目別グラフ作成(円細目)
    With ActiveSheet.Shapes.AddChart.Chart
    .ChartType = xlPie
     Set Ta = Range(Cells(37, 48), Cells(37, retu2))
     Set Ta2 = Range(Cells(69, 48), Cells(69, retu2))
     .SetSourceData Source:=Union(Ta, Ta2)
        'タイトル追加
    .HasTitle = True
    .ChartTitle.Text = "細目別支出"
    .Parent.Top = Po3.Top
    .Parent.Left = Po3.Left
    .SeriesCollection(1).ApplyDataLabels ShowPercentage:=True, ShowValue:=False
    End With
End Sub

 

グラフを作成するマクロは

ネットや書籍でもあまり情報が出回っていない気がします。

是非上記のサンプルマクロを参考にしてみてください。

 

ドロップダウンリストを自動作成するマクロ

ドロップダウンリストを自動作成するマクロは、

別シートに作成された一覧表の情報を元にドロップダウンリストを表示する

マクロです。

 

言葉で説明するのは結構難しいので、図を使って解説していきます。

 

背景色が白いセルに家計に合わせた項目を入力するだけで作業完了です。

 

あとは月別シートに移動し項目欄をクリックすると

「項目・設定」シートで設定した内容が自動的にドロップダウンリストに表示されます。

しかも

「条件によってリスト内容が変化するマクロ」が設定されているので

常に最小限のリスト内容が表示される仕組みになっています。

 

設定されているマクロの解説はちょっと複雑になってしまうので、今回が省略させてもらいます。

 

「条件によってリスト内容が変化するマクロ」については

下記の記事で詳しく解説しています。

 

 

マクロの設定方法や作り方が掲載されているので、

「仕事に応用したい!」と言う方は是非参考にしてみてください。

 

年数と日付から曜日を自動入力するマクロ

エクセル家計簿に絶対に必要となる機能が

年数と日付から曜日を自動入力する

マクロです。

 

レシートの内容を家計簿に入力するとき、

いちいち曜日を手入力するのはめんどう

と感じたことのある方がほとんどではないでしょうか。

 

 

年月日の情報さえあれば、

曜日の入力を完全自動化することが可能です。

 

設定されているマクロがこちら

Sub 月別シート曜日()
     Dim Ash As Worksheet
     Set Ash = ThisWorkbook.Worksheets("項目・設定")
     Dim youb As Date
     ActiveSheet.Cells(1, 1) = Ash.Cells(1, 1)
     gyo = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
     For i = 4 To gyo
     youbi = ActiveSheet.Cells(1, 1).Value & "/" & ActiveSheet.Cells(1, 4) & "/" & ActiveSheet.Cells(i, 4)
     ActiveSheet.Cells(i, 2) = WeekdayName(Weekday(youbi), True)
     Next
End Sub

 

 

下記の記事では、曜日自動入力するマクロについて解説されています。

 

 

エクセル家計簿に限らず、

日付を編集する作業に「曜日を自動入力するマクロ」はめちゃくちゃ利用されます。

是非参考にしてみてください。

 

マクロが設定されたエクセル家計簿の使い方

 

マクロが設定されたエクセル家計簿の使い方についてご紹介していきます。

 

感覚的に使えるエクセル家計簿を目指して作成したので、

誰でも簡単に使える仕様になっているはず。。

 

Left Caption

ひらめく人

使いやすいかどうかチェックしてみてください。

 

リストの作成

上記でも紹介していますがこのエクセル家計簿には、

ドロップダウンリストを自動作成するマクロが設定されています。

 

この「ドロップダウンリストに表示されるリスト」を最初に設定してしまえば、

家計簿への入力がかなり簡単になるので一番先に設定しておきましょう

 

リストの設定方法がこちら

 

「項目・設定」シートの一覧表に好きなように項目を追加する

たったこれだけです。

 

記入内容としては、

我が家の仕訳項目をサンプルとして掲載しているので参考にしてください。

 

各月別シートで入力

実際のお金の流れを各月別シートに入力していきます。

 

一番上の行から順番に

「日付」「収入・支出」「項目」「細目」「金額」を入力。

 

「日付」と「金額」以外は先ほど設定したドロップダウンリストからの選択入力になります。

 

もし家計簿への入力を忘れてしまい日付が飛んでしまっても、

行を挿入したりコピペして順番を並び替えをする必要は一切ありません

 

日付順で並び替えをするマクロが設定されているので、

集計実行ボタンでマクロ実行の都度並び替えが可能です。

 

 

入力場所の目印としては

手入力が必要な箇所のセルは「塗り潰しなし」

自動で値が反映される編集不要セル「黄色の塗りつぶし」

という設定になっています。

 

全体集計シートで集計

各月別シートでの集計作業を終えたのち、

「全体集計」シートで「集計実行」ボタンを押してください。

 

各月別シートで集計された値が「全体集計」シートに集約されます。

 

一年を通して「収入と支出のバランス」や「どの項目にお金がかかっているのか」などを確認することができる

さらに、年ごとの収入と支出を入力する欄を設けることで、年ごとの比較が可能になっています。

 

 

 

マクロが設定されたエクセル家計簿の使い方はこれで終了です。

 

Left Caption

ガッツポーズの人

直観的に扱って頂ける仕様になっているかと思います。

 

 

アプリよりもエクセルで家計簿管理したいという方へ

スマホアプリよりもエクセルで家計簿を管理したいという方にとって、

「マクロを設定したエクセル家計簿」は、妻が使いやすいと絶賛の仕様になっています。

 

アプリではなくエクセルで家計簿を管理する最大のメリットとして

編集の際自由度が高い

ということです。

 

「ちょっとしたコメントを残したいとき」「グラフを変えてみたい」など

エクセル家計簿ならば追加編集がめちゃくちゃ簡単です。

 

Left Caption

正解の人

私と妻はアプリよりもエクセル派です。

 

この記事を見て、エクセル家計簿使ってみたい!と感じてくれた方は下記のサイトを参考にしてください。

サイトでは「マクロを設定したエクセル家計簿」の使い方とダウンロード方法が掲載されています。