家計簿アプリを、より使いやすく自分仕様に改造していきましょう！
以前の投稿で、家計簿アプリとして最低限の機能を持たせることができました。
（その６までをご確認いただいていない方は、まずは下記まとめをご確認ください。）
ただ、これは本当に最低限の機能しかなく、勘定科目も『固定費』と『変動費』しかないので、
各家庭に合った勘定科目を自由に追加できるように、
このマクロのデコレーションをしていく必要があります。
今回は、まずは一番簡単な方法として、『ドロップダウンリストを使って勘定科目を追加する』方法について説明していきたいと思います。
【本記事の目標】
ドロップダウンリストを使って
任意の勘定科目を追加する
簡単なイメージは下記記事に記載していますので、イメージづくりや今回以降の流れの参考に。
勘定科目を追加する前準備
入力用Excelシートの配置替え
現状の家計簿アプリの入力シートでは勘定科目を追加しても、リストにその内容を書くスペースが無いので、まずは入力用Excelシートの配置替えをしていきましょう。
勘定科目を追加していくと、その項目ごとの集計欄が必要になってきますので、集計部分を広くとっておく必要があります。
また、「その８」以降の予定ですが、勘定科目をボタン入力していきたいと思っていますので、ボタンが置ける場所もあけておきたいです。
このあたりを考慮して、入力シートを下記のように配置替えしました。
これに関しては好みもあるかと思いますので、これが絶対ではないです。
自分が入力しやすいように、自分専用に改造していっていただくと良いと思います。
（ただ、マクロを書く場合に読み込む場所・貼り付ける場所等がズレますのでご注意ください。）
【Before】
【After】
配置換えに伴うマクロの修正
Excelシートについては配置替えが完了しましたが、このままだとマクロが正しく動きません。
というのも、このマクロはExcelの『セル』を指定して動かしている部分がいくつかあるためです。
（金額や勘定科目を読み込んだり、読み込んだ内容を貼り付けたりです）
各モジュールで修正が必要な場所と、修正後のコードは下記の通りです。
【KamokuInput】モジュール
修正後コード
Option Explicit
Sub KamokuInput_1()
'リストに追加をクリック
Dim Kingaku1 As Double
Kingaku1 = ThisWorkbook.ActiveSheet.Range("C3").Value
Dim Syushi1 As String
Dim Kamoku1 As String
Syushi1 = ThisWorkbook.ActiveSheet.Range("C4").Value
If Syushi1 = "収入" Then
Kamoku1 = ""
Else
Kamoku1 = ThisWorkbook.ActiveSheet.Range("D4").Value
End If
Dim EndRow1 As Integer
With ThisWorkbook.ActiveSheet
EndRow1 = .Cells(.Rows.Count, 2).End(xlUp).Row
EndRow1 = EndRow1 + 1
.Cells(EndRow1, 2).Value = Syushi1
.Cells(EndRow1, 3).Value = Kamoku1
If Syushi1 = "収入" Then
.Cells(EndRow1, 4).Value = Kingaku1
Else
.Cells(EndRow1, 5).Value = Kingaku1
End If
End With
Call Jisaku_SUM
End Sub
【JisakuFunc_SUM】モジュール
修正後コード
Option Explicit
Sub Jisaku_SUM()
'自作の関数を使った集計
Dim SyunyuSyukei3 As Double
Dim ShisyutsuSyukei3 As Double
Dim i As Integer
Dim EndRow2 As Integer
Dim Koteihi As Double
Dim Hendouhi As Double
With ThisWorkbook.ActiveSheet
EndRow2 = .Cells(.Rows.Count, 2).End(xlUp).Row
For i = 8 To EndRow2
If IsError(.Cells(i, 4).Value) Then
Else
SyunyuSyukei3 = SyunyuSyukei3 + .Cells(i, 4).Value
ShisyutsuSyukei3 = ShisyutsuSyukei3 + .Cells(i, 5).Value
If .Cells(i, 3).Value = "固定費" Then
Koteihi = Koteihi + .Cells(i, 5).Value
End If
If .Cells(i, 3).Value = "変動費" Then
Hendouhi = Hendouhi + .Cells(i, 5).Value
End If
End If
Next i
.Range("K3") = SyunyuSyukei3
.Range("K4") = ShisyutsuSyukei3
.Range("K6") = Koteihi
.Range("K7") = Hendouhi
End With
End Sub
【SheetCopyCode】モジュール
修正後コード
Option Explicit
Sub SheetCopy1()
'シートの複製
Dim YearInput As Integer
Dim MonthInput As Integer
With ThisWorkbook.Sheets("入力")
YearInput = .Range("M4").Value
MonthInput = .Range("O4").Value
End With
ThisWorkbook.Sheets("入力").Copy After:=ThisWorkbook.Sheets("入力")
ActiveSheet.Name = YearInput & "年" & MonthInput & "月"
ThisWorkbook.Sheets("入力").Range("B8:F10000").ClearContents
ThisWorkbook.ActiveSheet.Shapes("正方形/長方形 5").Delete
'=============毎月データの集計用コード（参照関数の入力ver） ここから=================
Dim EndRow1 As Integer
With ThisWorkbook
'『月ごとの推移』シートの最終行を確認
EndRow1 = .Sheets("月ごとの推移").Cells(.Sheets("月ごとの推移").Rows.Count, 3).End(xlUp).Row
EndRow1 = EndRow1 + 1
'=============行挿入のコード ここから=================
With .Sheets("月ごとの推移")
.Range(.Cells(EndRow1, 3), .Cells(EndRow1, 9)).Insert Shift:=xlDown
End With
'=============行挿入のコード ここまで=================
'データを貼り付けていく
.Sheets("月ごとの推移").Cells(EndRow1, 3).Value = "='" & YearInput & "年" & MonthInput & "月'!M4" '年
.Sheets("月ごとの推移").Cells(EndRow1, 4).Value = "='" & YearInput & "年" & MonthInput & "月'!O4" '月
.Sheets("月ごとの推移").Cells(EndRow1, 5).Value = YearInput & "年" & MonthInput & "月" '年/月
.Sheets("月ごとの推移").Cells(EndRow1, 6).Value = "='" & YearInput & "年" & MonthInput & "月'!K3" '収入合計
.Sheets("月ごとの推移").Cells(EndRow1, 7).Value = "='" & YearInput & "年" & MonthInput & "月'!K4" '支出合計
.Sheets("月ごとの推移").Cells(EndRow1, 8).Value = "='" & YearInput & "年" & MonthInput & "月'!K6" '固定費
.Sheets("月ごとの推移").Cells(EndRow1, 9).Value = "='" & YearInput & "年" & MonthInput & "月'!K7" '変動費
End With
'=============毎月データの集計用コード（参照関数の入力ver） ここまで=================
End Sub
【SheetFunc_SUM】モジュール
（※現状では使っていませんし、今後も使う予定はないですが、念のため記載しておきます）
修正後コード
Option Explicit
Sub SheetFunction_SUM()
'WorksheetFunctionを使った集計
Dim SyunyuSyukei2 As Double
Dim ShisyutsuSyukei2 As Double
With ThisWorkbook.Sheets("入力")
SyunyuSyukei2 = WorksheetFunction.Sum(.Range("D8:D100000"))
ShisyutsuSyukei2 = WorksheetFunction.Sum(.Range("E8:E100000"))
.Range("K3") = SyunyuSyukei2
.Range("K4") = ShisyutsuSyukei2
End With
End Sub
これで勘定科目を追加できるようにするための前準備が終わりました。
（長くなりました。。。。）
それでは、いよいよ任意の勘定科目を追加していきましょう！
勘定科目を追加する（ドロップダウンリストを追加する）
固定のドロップダウンリストを追加する
まずは、一番単純な方法として固定のドロップダウンリストを追加しましょう。
固定のドロップダウンリストの追加に関しては、『収入』・『支出』の選択や、『固定費』・『変動費』の選択をする部分を作る際に一度記載していますので、
下記の過去の記事もご参考に。
勘定科目は金額を入力する際に記入しますので、
ひとまず『固定費』・『変動費』を選択する欄の隣に作ります。
前回記入した際は、下記のようにデータ入力規制の「元の値(S)」の欄に直接記入していました。
項目数が少なければこれでも問題ないですが、項目数が増えたときや追加・削減をしたいときにやりにくいですよね。
その対策として、この欄は直接記入だけでなく、セルを選んだらそのセルの値でドロップダウンリストを作ることも可能です。
ただし、この方法だと、この欄の項目を修正した場合（例えば「生活費」を「日用品」に変えるなど）には自動的に反映されますが、
項目を追加したとしてもドロップダウンリストは追加されません。
項目を追加したときに、ドロップダウンリストも追加されるようにしようと思うと、Excelに備わっている【テーブル】という機能を使う必要があります。
動的ドロップダウンリスト（自動で項目追加）
ドロップダウンリストを自動で項目追加できるようにするには、【テーブル】を使う必要があります。
【テーブル】の機能は非常に便利ですが、その機能を説明するだけで入門書の１章分、もしくは本1冊分ぐらいのボリュームがあるので、ここで全てを説明するのは不可能なので、
今回必要な部分に特化して説明します。
【テーブル】はExcelシート上にある任意の領域を、「その領域を【テーブル】として指定します」と宣言することで使えるようになります。
ただ１つ注意点があり、その領域の一番上の行は『見出し行』して指定されます。
ですので、その点を考慮して領域を選ぶ必要があります。
今回は、とりあえず下記のように集計部の下の部分に、任意の勘定科目とそれが固定費か変動費か、というのが分かるような表を作って、それを【テーブル】に指定します。
この領域を選んだ状態で、ツールバーの 挿入⇒テーブル を選択します。
この時、「先頭行をテーブルの見出しとして使用する」にチェックを入れておきましょう。
そうすれば、最初の行の部分（「固定/変動」、「科目」、「合計」の部分）がテーブルの見出しになります。
（チェックを入れなかった場合、見出しを入力する欄が、選択した領域の一番上の行に追加されます）
そうすれば、選択した領域が自動的に枠線や背景色が入れられます。
これでこの領域が【テーブル】として指定されました。
この【テーブル】ですが、それぞれの列に名前を付けることができます。
名前を付ける場合、見出しを除く1列を選択した状態で、左上のセルの場所が記入されている部分に付けたい名前を記入することで名前付けが可能です。
（この左上の部分は「名前ボックス」と言います）
今回は『勘定科目』という名前を付けておきます。
（入力してエンターを押すと、元の表示に戻りますが、名前は登録されていますので気にしなくて大丈夫です）
今付けた名前を、ドロップダウンリストの元の値のところに記入すれば、項目が自動的に追加されていくドロップダウンリストの完成です。
なぜこれで自動的に項目が追加されていくのかというと、
テーブルのすぐ外側のセルに記入すると分かります。
例えば、「家賃」と記載したセルの１つ下のセルに「サブスク代」と記入してみましょう。
そうすると、自動的に枠線や色が追加されたと思います。
これが【テーブル】の一番便利な機能で、【テーブル】のすぐ右側およびすぐ下側のセルに何かを記入したとき、自動的に【テーブル】の領域を広げてくれます。
また、領域を自動的に広げてくれるだけでなく、
・名前を付けた範囲も自動的に拡張
・１つの列全てに同じ関数が入っていた場合、広げた領域に関数を自動入力
ということも自動で実施してくれます。
この１つ目の機能のおかげで、「勘定科目」と名前を付けた領域が自動的に広くなり、ドロップダウンリストの項目も自動的に追加された、ということになったのです。
【テーブル】の書式は自動的に指定されますが、好きなスタイルに変更することも可能です。
【テーブル】を選択した状態で、ツールバーの
テーブルデザイン ⇒ テーブルスタイル
でご自身の好きな見やすいものを選んでもらったらいいかと思います。
また、テーブルスタイルのオプションの部分で全体的なスタイルも変更できます。
（縞模様にするのかしないのか等）
これらは見栄えだけで、機能的な部分に影響が出るものではないので、好きな形に選んでもらって良いと思います。
マクロの作成
これでドロップダウンリストができましたが、それで終わりではなく、一覧表の勘定科目にこの科目が表示されるようにマクロを修正する必要があります。
ただその前に、一覧表に新しい勘定科目を入れられるようにしておきましょう。
固定費・変動費の勘定科目を残しておきたいので、下記のように欄を１つ追加し、『詳細科目』という欄にしてあります。
（収入金額・支出金額の欄がズレてますので、該当するコードの修正も必要ですのでご注意ください。）
では「リストに追加」のボタンを押したときに、今回追加した「詳細科目」（ドロップダウンリストの内容）が記入されるようにします。
基本的に、もともと作っていた勘定科目（固定費と変動費）を記入する部分をそのまま踏襲すればOKです。
もともとも勘定科目を入力する部分は、『KamokuInput_1』のプロシージャの下記の部分ですので、
これに倣って、
１．新しく『Kamoku2』という変数を定義
２．「詳細科目」が記入されている『E4』セルの内容を『Kamoku2』に代入
３．一覧表の所定の場所に記入する
ということをしていきます。
この内容を記入したコードを下記に記載します。
修正後コード ↓（KamokuInputモジュール）
Option Explicit
Sub KamokuInput_1()
'リストに追加をクリック
Dim Kingaku1 As Double
Kingaku1 = ThisWorkbook.ActiveSheet.Range("C3").Value
Dim Syushi1 As String
Dim Kamoku1 As String
Dim Kamoku2 As String
Syushi1 = ThisWorkbook.ActiveSheet.Range("C4").Value
If Syushi1 = "収入" Then
Kamoku1 = ""
Kamoku2 = ThisWorkbook.ActiveSheet.Range("E4").Value
Else
Kamoku1 = ThisWorkbook.ActiveSheet.Range("D4").Value
Kamoku2 = ThisWorkbook.ActiveSheet.Range("E4").Value
End If
Dim EndRow1 As Integer
With ThisWorkbook.ActiveSheet
EndRow1 = .Cells(.Rows.Count, 2).End(xlUp).Row
EndRow1 = EndRow1 + 1
.Cells(EndRow1, 2).Value = Syushi1
.Cells(EndRow1, 3).Value = Kamoku1
.Cells(EndRow1, 4).Value = Kamoku2
If Syushi1 = "収入" Then
.Cells(EndRow1, 5).Value = Kingaku1
Else
.Cells(EndRow1, 6).Value = Kingaku1
End If
End With
Call Jisaku_SUM
End Sub
また、一覧表の欄を追加した影響で、『Jisaku_SUM』プロシージャの部分も修正する必要があるので、その修正後のコードもおいておきます。
Option Explicit
Sub Jisaku_SUM()
'自作の関数を使った集計
Dim SyunyuSyukei3 As Double
Dim ShisyutsuSyukei3 As Double
Dim i As Integer
Dim EndRow2 As Integer
Dim Koteihi As Double
Dim Hendouhi As Double
With ThisWorkbook.ActiveSheet
EndRow2 = .Cells(.Rows.Count, 2).End(xlUp).Row
For i = 8 To EndRow2
If IsError(.Cells(i, 4).Value) Then
Else
SyunyuSyukei3 = SyunyuSyukei3 + .Cells(i, 5).Value
ShisyutsuSyukei3 = ShisyutsuSyukei3 + .Cells(i, 6).Value
If .Cells(i, 3).Value = "固定費" Then
Koteihi = Koteihi + .Cells(i, 6).Value
End If
If .Cells(i, 3).Value = "変動費" Then
Hendouhi = Hendouhi + .Cells(i, 6).Value
End If
End If
Next i
.Range("K3") = SyunyuSyukei3
.Range("K4") = ShisyutsuSyukei3
.Range("K6") = Koteihi
.Range("K7") = Hendouhi
End With
End Sub
これで、任意の勘定科目（詳細科目）を好きに追加でき、その内容が一覧表に表示できるようになりました。
ただ、まだ一覧表に表示できるようになっただけで、各勘定科目も合計は計算されていませんよね。
ここについては、次回に詳細を記載していきたいと思います。
次の記事へ
コメント