ExcelVBAで家計簿アプリを作ろう　その5

●今までプログラミングなんかに触れたことが無くて
　　「今更そんなの使いこなせるわけない」と思っている方
●小学校でプログラミングが必修科目になり、
　　子どもに「ここ分からないから教えて～」と言われても
　「無理！！」と言うしかなくて困ったお父さん・お母さん

そんなプログラミング全くの初心者に向けて、
　「こうやったら自動で動くソフト（Excelマクロ）が作れるんだ！」
というのを理解していただくように、
ゼロからソフトを作っていく過程を紹介していきたいと思います。

Windowsのパソコンがあれば大抵標準装備されているExcelを使って紹介しますので、
この投稿のシリーズを最後まで読んでいただければ、
また、投稿を読みながら実際に一緒にソフトを作っていっていただければ、
　「自分で実際にソフトを作ることができて、プログラミングに少し自信がついた！」
と思ってもらえるかと思いますので、是非とも一緒に手を動かしてみてください。

【本記事の目標】

複数のシートからのデータ抽出方法を知ろう

それでは、5つ目の動きのコードを書いていきます。いよいよ大詰めです。

　　１．金額を入力して勘定科目を選択したら、リストに自動で記入する。
　　２．記入したリストを自動的に集計する。
　　３．集計した結果から、今月使った割合が多い項目が何かわかるようにグラフ化する。
　　４．翌月の内容を記入する際は、自動で新しい白紙のリストを作成して記入していく。
　　５．毎月の集計結果を集めて、毎月の家計の変化がわかるグラフを作る。
　　６．毎月の集計結果をさらに集計し、その年使った割合が多い項目がわかるようにグラフ化。

1つ目～4つ目の動きを確認されていない方は、過去の投稿もご確認ください。

毎月の集計結果を集める
1. 集計用のシートを作っておく
データを集約するコードを書いていく
1. シート複製時に、集計結果のデータを『月ごとの推移』にそのまま直接貼り付ける
  1. コードを書いていく
  2. この方法の特徴（メリット・デメリット）
2. シート複製時に付けたシート名を使って、『月ごとの推移』の所定の場所に、そのシート名のセルを参照するExcel関数を入力する。
集約した結果をグラフ化する
1. グラフを作る
2. グラフのデータ領域が自動で変わるようにする

毎月の集計結果を集める

これまでの作業で毎月の結果を入力できるようになり、毎月の結果が各シートとして残っていくような状態を作ることができました。

これで毎月の状況を確認できるようになりましたが、家計の管理をしていこうと思うと、
「この月は使いすぎたな～」とか、
「この月は節約頑張れた！」とか、
いろんな月の結果を見て比較ができるようにしておきたいですよね。

そこで今回は、複数のシートに記録されている毎月の集計結果を、
１つのシートに集約していきたいと思います。

集計用のシートを作っておく

複数のシートにある毎月の集計結果をかき集めていきますが、
その前に、集めたデータを置いておくためのシートを準備しておきましょう。

これについてはお好みでOKかと思います。

ただ、毎月の集計結果として、

収入合計
支出合計
固定費合計
変動費合計

この４つを集計してありますので、この４つを置ける場所は準備しておきましょう。

ひとまずこんな感じで作ってみました。

シートの名前は『月ごとの推移』にしています。

このシートに、月ごとのシートに記載されているデータを貼り付けていきます。

データを集約するコードを書いていく

月ごとのシートに記載されているデータを『月ごとの推移』のシートに貼り付ける方法がいくつかあるのですが、今回はその中の２つの方法を紹介していきます。

シート複製時に、集計結果のデータを『月ごとの推移』にそのまま直接貼り付ける
シート複製時に付けたシート名を使って、『月ごとの推移』の所定の場所に、
そのシート名のセルを参照するExcel関数を入力する。

シート複製時に、集計結果のデータを『月ごとの推移』にそのまま直接貼り付ける

1つ目の方法は、所定のセルに入っているデータを、別のセルに貼り付ける、という作業なので、
じつは【ExcelVBAで家計簿アプリを作ろう　その１】の内容を使えば作ることができます。

一度自分で作れないか試してみたい、という方は、ここでいったん読むのを止め、
【その１】を復習しながら試してみていただくと、より勉強になるかと思います。

コードを書いていく

それではこの方法のコードを作っていきます。

まずは集計したいデータの場所ですが、『入力』シートの、
　年：I1
　月：K1
　収入合計：G5
　支出合計：G6
　固定費：G8
　変動費：G9
のセルにデータが入っており、このデータを『月ごとの推移』シートの、
　年：C列
　月：D列
　収入合計：E列
　支出合計：F列
　固定費：H列
　変動費：I列
の最後の行に貼り付けていきたいです。

なので、『月ごとの推移』シートの最終行を確認してしまえば、
あとはそれぞれのセルにあるデータを、狙いの場所に貼り付ければOKです。

この貼り付け作業は、シートを複製するタイミングで実施したいので、
【SheetCopy1】のプロシージャに追記する形で作っていきます。

シートを複製した後だと、シート名が変わってしまってコードが少し複雑になる（というほど複雑ではないですが、、、）ので、シートを複製する直前にこの作業を入れていきます。

いきなり答えを書くと、こんな感じになります。

Option Explicit

Sub SheetCopy1()
'シートの複製

Dim YearInput As Integer
Dim MonthInput As Integer

With ThisWorkbook.Sheets("入力")

YearInput = .Range("I1").Value
MonthInput = .Range("K1").Value

End With


'=============毎月データの集計用コード（直接貼り付けver）　ここから=================

Dim EndRow1 As Integer

With ThisWorkbook

'『月ごとの推移』シートの最終行を確認
EndRow1 = .Sheets("月ごとの推移").Cells(.Sheets("月ごとの推移").Rows.Count, 3).End(xlUp).Row
EndRow1 = EndRow1 + 1

'データを貼り付けていく
.Sheets("月ごとの推移").Cells(EndRow1, 3).Value = .Sheets("入力").Range("I1").Value    '年
.Sheets("月ごとの推移").Cells(EndRow1, 4).Value = .Sheets("入力").Range("K1").Value    '月
.Sheets("月ごとの推移").Cells(EndRow1, 5).Value = .Sheets("入力").Range("I1").Value & "年" & .Sheets("入力").Range("K1").Value & "月"    '年/月
.Sheets("月ごとの推移").Cells(EndRow1, 6).Value = .Sheets("入力").Range("G5").Value    '収入合計
.Sheets("月ごとの推移").Cells(EndRow1, 7).Value = .Sheets("入力").Range("G6").Value    '支出合計
.Sheets("月ごとの推移").Cells(EndRow1, 8).Value = .Sheets("入力").Range("G8").Value    '固定費
.Sheets("月ごとの推移").Cells(EndRow1, 9).Value = .Sheets("入力").Range("G9").Value    '変動費

End With

'=============毎月データの集計用コード（直接貼り付けver）　ここまで=================


ThisWorkbook.Sheets("入力").Copy After:=ThisWorkbook.Sheets("入力")

ActiveSheet.Name = YearInput & "年" & MonthInput & "月"


ThisWorkbook.Sheets("入力").Range("A4:D10000").ClearContents


ThisWorkbook.ActiveSheet.Shapes("正方形/長方形 5").Delete



End Sub

【SheetCopy1】のプロシージャをこのような形に修正したら、
シートをコピーするたびに『月ごとの推移』シートにデータが貼り付けられているところを確認できると思います。

データの貼り付けについて、今回は下記のような書き方のコードにしました。

.Sheets("月ごとの推移").Cells(EndRow1, 3).Value = .Sheets("入力").Range("I1").Value

ただ、データの貼り付けのコードはこれだけではなく、他にも方法があります。

例えば【その１】で使った方法だと、任意の変数を１つ定義しておいて、
一旦そこに貼り付けたいデータを入れておいて、貼り付け先にデータを入力する、
といった方法があります。

Dim tempData1 as integer

tempData1 = .Sheets("入力").Range("I1").Value
.Sheets("月ごとの推移").Cells(EndRow1, 3).Value = tempData1

この場合は任意の変数のデータ形式によっては、エラーが出たり、思ったデータと違うデータが貼られる場合があるので、注意が必要です。
（※例えば、Integer(整数)で定義すると、文字列データを入れるとエラーで動かなくなり、小数点データだと、小数点以下を切り捨てたデータにされてしまいます）

それ以外にも、シート上でコピー⇒貼り付けを行うのと同じ動きをさせることもできます。

.Sheets("入力").Range("I1").Copy
.Sheets("月ごとの推移").Cells(EndRow1, 3).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

ここで、.PasteSpecial　というのは「形式を選択して貼り付け」と同じ動きをするコマンドで、この後ろの　Paste:=　の部分で貼り付ける形式を指定しています。
今回の場合、値で貼り付ける必要があるので、xlPasteValues　と指定しています。
（他の形式をどうやって指定するかについては、いつか別記事で書こうと思います。）

その後ろの　Application.CutCopyMode = False　というのはコピー状態（下記図のようにコピーしたときに点線が表示される状態）を解除するためのコマンドです。
無くても大丈夫ですが、間違って変なところに貼り付けないように、貼り付けが終わった後は必ずこのコマンドを入れておいた方が良いです。

この方法の特徴（メリット・デメリット）

この方法の一番のメリットは　わかりやすくて書きやすい　という点です。
すでに【その１】で説明した方法で書くことができるので、非常にシンプルな方法です。

ただ欠点があり、値を貼り付けているので、シートを複製後に各シートのデータを修正しても、貼り付けた『月ごとの推移』シートのデータは修正されません。

せっかく前回の記事で、複製された各シートに『リストに追加』のボタンを残して、シート複製後もデータを修正できるようにしたのに、このままだとそれが使えないことになってしまいます。

なので、リアルタイムに修正結果が反映されるように、次の方法でコードを書いた方が良いです。

シート複製時に付けたシート名を使って、『月ごとの推移』の所定の場所に、そのシート名のセルを参照するExcel関数を入力する。

2つ目の方法は、『月ごとの推移』シートに各月のシートを参照するExcel関数を、マクロを使って記入していく方法です。

シートを参照するExcel関数

そもそも、『各月のシートを参照するExcel関数』とは何？
についてですが、
Excelは下記のように『=(任意セルの場所)』という関数をセルに入力すると、任意セルに記載の値を参照して表示することができます。
下記の場合だと「A1」セルに入力されている「1000」という値が、
「C1」セルに『=A1』と入力することで、「C1」セルにも「1000」という値が表示されます。

この状態で「A1」セルの値を「1000」から「2000」に変えると、何もしなくても「C1」セルの表示も「2000」に変わります。

このような他のセルの値を参照して表示するという方法は、何も同じシート内でないとできない、ということはなく、他のシートにあるほかのセルの値を参照して表示することも可能です。

ただし、他のシートのセルを参照する場合は、「A1」だけでなく「シート名」も記入する必要があります。
具体的には、下記のように
　　＝‘(シート名)’!(セルの場所)
という書き方をする必要があります。

このような書き方をすれば、他のシートに表示されている値を好きなところに表示させることが可能になります。

この関数を使って『月ごとの推移』のシートから毎月のシートの集計結果を参照すれば、１つのシートから全てのシートの集計結果を見ることができるようになるのです。

コードを書いていく

じゃあこの参照の関数を、シートが複製されるたびに『月ごとの推移』のシートに記入されるコードを書いていきましょう。

シートを複製するタイミングで記入していきたいので、SheetCopy1のプロシージャに追記していきます。
先に答えを書いてしまうと、下記のようになります。

Option Explicit

Sub SheetCopy1()
'シートの複製

Dim YearInput As Integer
Dim MonthInput As Integer

With ThisWorkbook.Sheets("入力")

YearInput = .Range("I1").Value
MonthInput = .Range("K1").Value

End With


ThisWorkbook.Sheets("入力").Copy After:=ThisWorkbook.Sheets("入力")

ActiveSheet.Name = YearInput & "年" & MonthInput & "月"


ThisWorkbook.Sheets("入力").Range("A4:D10000").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

'データを貼り付けていく
.Sheets("月ごとの推移").Cells(EndRow1, 3).Value = "='" & YearInput & "年" & MonthInput & "月'!I1"    '年
.Sheets("月ごとの推移").Cells(EndRow1, 4).Value = "='" & YearInput & "年" & MonthInput & "月'!K1"    '月
.Sheets("月ごとの推移").Cells(EndRow1, 5).Value = YearInput & "年" & MonthInput & "月"    '年/月
.Sheets("月ごとの推移").Cells(EndRow1, 6).Value = "='" & YearInput & "年" & MonthInput & "月'!G5"    '収入合計
.Sheets("月ごとの推移").Cells(EndRow1, 7).Value = "='" & YearInput & "年" & MonthInput & "月'!G6"    '支出合計
.Sheets("月ごとの推移").Cells(EndRow1, 8).Value = "='" & YearInput & "年" & MonthInput & "月'!G8"    '固定費
.Sheets("月ごとの推移").Cells(EndRow1, 9).Value = "='" & YearInput & "年" & MonthInput & "月'!G9"    '変動費

End With

'=============毎月データの集計用コード（参照関数の入力ver）　ここまで=================


End Sub

直接入力の場合と違って、今回は複製されたシートの名前を参照する関数を入力していくので、シート複製の後にコードを記入しても問題ないです。

ここで、所定のセルに
　“='” & YearInput & “年” & MonthInput & “月’!I1”
という文字を入力するようにしています。
ポイントとしては３つ。

文字と文字をつなげる場合は　『＆』　という記号を使う
『＆』を使えば文字と変数（数値等）もつなげることができる
セルに関数を入力する場合、最初に　”＝”　を入力すればOK

上記の場合だと「YearInput」と「MonthInput」というのが変数で、それぞれシートを複製したときのシート名の「年」と「月」の数値が入っています。
例えば下記のような状態で『シートの複製・初期化』をクリックした場合、
「YearInput」には『2022』という値が入っており、「MonthInput」には『1』という値が入っています。

なので、
　“='” & YearInput & “年” & MonthInput & “月’!I1”
この部分は
　“=’2022年1月’!I1”
という形になり、これが『月ごとの推移』のシートに入力されていきます。

実際に動かしてもらったら、『月ごとの推移』のシートにどんどん関数が入っていくはずです。
この関数で各シートの値を参照していますので、各シートの値を変えると『月ごとの推移』のシートも動くことを確認できると思います。

この方法のメリット・デメリット

この方法のメリットは、シート複製後に値を変更しても自動更新されるという点です。

家計簿をつけていく場合、翌月になってから前月の費用を記入することは頻繁にあると思いますので、そのようなときにはこの方法のように、自動更新されるシートを作っておくのが必須になります。

ただ、シートの参照や関数の直接入力といったテクニックを使う必要があるので、少しわかりにくい部分があるのがデメリットです。

とはいえ、慣れてくればそれほど苦ではなくなると思いますので、頑張って慣れていきましょう！

集約した結果をグラフ化する

グラフを作る

『月ごとの推移』のシートに、毎月の集計結果がまとまっていきますので、これをグラフ化してわかりやすくしておきましょう。

グラフについては自身がわかりやすい形で作ってもらったらいいかと思います。
このシートは毎月の集計結果の推移が確認できますので、横軸は『年/月』にして、縦軸に各集計結果を入れておいたらいい感じになるかと思います。

参考までに、こんな感じで作ってみました。

グラフのデータ領域が自動で変わるようにする

これで毎月の集計結果を集めたグラフが完成！

でもいいのですが、
このままだとシートを複製するたびに『月ごとの推移』のシートにデータが増えていきますが、
上記のグラフを作るデータの領域は大きくならないので、毎回グラフのデータ領域を変えていく手間が発生してしまいます。

これを回避する方法の１つをお伝えします。

それは、
　あらかじめグラフの領域を少し大きくしておき、
　毎月のシート複製の際に行を挿入してから参照関数を入力する。
これだけです。

グラフのデータの領域は、セルにデータを入力しただけでは変化しませんが、
行を挿入した場合はデータの領域が自動的に大きくなるようになっています。
この機能を利用するのです。

毎月のシートを複製し、毎月の集計結果の参照関数を『月ごとの推移』のシートに記載する直前に、行を挿入するコードを入れておく必要があるので、下記のような形になります。

Option Explicit

Sub SheetCopy1()
'シートの複製

Dim YearInput As Integer
Dim MonthInput As Integer

With ThisWorkbook.Sheets("入力")

YearInput = .Range("I1").Value
MonthInput = .Range("K1").Value

End With


ThisWorkbook.Sheets("入力").Copy After:=ThisWorkbook.Sheets("入力")

ActiveSheet.Name = YearInput & "年" & MonthInput & "月"


ThisWorkbook.Sheets("入力").Range("A4:D10000").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 & "月'!I1"    '年
.Sheets("月ごとの推移").Cells(EndRow1, 4).Value = "='" & YearInput & "年" & MonthInput & "月'!K1"    '月
.Sheets("月ごとの推移").Cells(EndRow1, 5).Value = YearInput & "年" & MonthInput & "月"    '年/月
.Sheets("月ごとの推移").Cells(EndRow1, 6).Value = "='" & YearInput & "年" & MonthInput & "月'!G5"    '収入合計
.Sheets("月ごとの推移").Cells(EndRow1, 7).Value = "='" & YearInput & "年" & MonthInput & "月'!G6"    '支出合計
.Sheets("月ごとの推移").Cells(EndRow1, 8).Value = "='" & YearInput & "年" & MonthInput & "月'!G8"    '固定費
.Sheets("月ごとの推移").Cells(EndRow1, 9).Value = "='" & YearInput & "年" & MonthInput & "月'!G9"    '変動費


End With

'=============毎月データの集計用コード（参照関数の入力ver）　ここまで=================


End Sub

ここで、
　　With .Sheets(“月ごとの推移”)
　　　　.Range(.Cells(EndRow1, 3), .Cells(EndRow1, 9)).Insert Shift:=xlDown
　　End With
この部分は、
　【『月ごとの推移』のシートの表の最後の行のC列～I列を選択して挿入し、下にシフト】
という動きをするコードになっています。