ExcelVBAで家計簿アプリを作ろう　その2-1

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

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

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

それでは次に、2つ目の動きのプログラミングをしていきます。

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

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

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

Excelマクロで作る家計簿アプリの作り方実践・説明の記事です。本記事では変数の指定・定義方法、セルデータの取得、デバックの方法について説明しています。

yorozuya-tanuki.com

集計の方法について

集計の方法は、手法としては大きく分けると２つあります。

1. Excel関数を使った集計方法
2. マクロを使った集計方法

基本的には【１．Excel関数を使った集計方法】の方が簡単で分かりやすく、かつ修正も容易です。
よほどのことがなければ、こちらで済ましてしまうのが効率的です。
ただ、せっかくなのでマクロを使った集計方法も知っておきましょう。
どちらが正解というのはないです。状況に合わせて使い分けるのが一番ですが、
そのためにも、最低限どんな動きをさせることができるのかを知っておいた方が良いと思います。

Excel関数を使った集計方法

Excel関数を使った集計は非常に簡単です。
集計結果を表示したいセルに『=SUM(集計する領域）』とすれば集計が可能です。
簡単でしょう？
今回は収入金額と支出金額の両方をそれぞれ集計したいので、それぞれの集計結果を表示させます。

ただこのままだと、項目が増えたときに対応できず、都度関数を修正する必要があります。
回避する方法としては、最初から集計する領域をできる限り大きくしておくというのがあります。
（例えば、『=SUM(C4:C100000)』）
ほとんどこれで問題ないと思いますが、ただ「必ず大丈夫」とは言えません。極端に言えば100000個以上データを入力すると集計する領域から外れるので、その時にまた関数を修正しないといけないです。
これが、【Excel関数を使った集計方法】のデメリットです。

マクロを使った集計方法

ではマクロを使った集計方法であれば、そのデメリットが起こらないのか？と言われると、
マクロの作り方による、というのが答えです。
ざっくり分けると下記の２通りがあります。

1. WorksheetFunctionを使う方法
2. 繰り返し（For文）を使う方法

WorksheetFunctionを使う方法

１つ目の【WorksheetFunctionを使う方法】は比較的イメージがしやすいです。
というよりも、シート上で関数を使う（=SUM(C4:C100000)みたいなの）と全く同じです。

実際にコード書くと下記のようなイメージになります。

Dim SyunyuSyukei2 As Double
Dim ShisyutsuSyukei2 As Double

With ThisWorkbook.Sheets("入力")

SyunyuSyukei2 = WorksheetFunction.Sum(.Range("C4:C100000"))
ShisyutsuSyukei2 = WorksheetFunction.Sum(.Range("D4:D100000"))

.Range("G7") = SyunyuSyukei2
.Range("H7") = ShisyutsuSyukei2

End With

【WorksheetFunction.Sum】にすると、シート上で使うSUM関数と同じことができます。
シート上で使うSUM関数も合計する領域を指定するので、この時も合計する領域を指定します。
今回は『このファイルの「入力」シートのC4:C100000を合計する』という内容にするため、
　ThisWorkbook.Sheets(“入力”).Range(“C4:C100000”)
を領域を指定しています。

この方法は、おそらくイメージはしやすいと思いますが、シート上の関数を読みだしているだけなので、【Excel関数を使った集計方法】と同じデメリットがあります。

繰り返し（For文）を使う方法

このデメリットを回避する１つの方法が、集計の関数を自分で作ってしまう、というのがあります。
自分で作ってしまえば使いやすい形にカスタマイズも可能なので、自由度が大幅に増えます。
その反面、考えないといけないことも増えるので、難易度は上がります。
ただ、複雑なマクロを作っていけばいくほど、基本の関数では対応できないことが増えてきて、自作の関数を作る必要が出てくるので、その時のために簡単な関数の段階で慣れておくのが良いと思います。

集計は単純に足し算の繰り返しなので、今回の場合、例えば収入金額の合計は収入金額の欄を順番に足していけばOKです。

繰り返しをさせる場合に使う構文の一つが【For文】です。
まずは実際に書いてみましょう。

Dim SyunyuSyukei3 As Double
Dim ShisyutsuSyukei3 As Double
Dim i As Integer
Dim EndRow2 As Integer

With ThisWorkbook.Sheets("入力")

EndRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row

For i = 4 To EndRow2

SyunyuSyukei3 = SyunyuSyukei3 + .Cells(i, 3).Value
ShisyutsuSyukei3 = ShisyutsuSyukei3 + .Cells(i, 4).Value

Next i

.Range("G8") = SyunyuSyukei3
.Range("H8") = ShisyutsuSyukei3

End With

　EndRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row
ここでEndRow2に表の最後の行を入れています。
（この構文については前回投稿をご確認ください。）

　For i = 4 To EndRow2
　　～
　Next i
これでFor～Nextの間を、最初はiに4を入れて実行、次にiに4+1を入れて実行、次にiに4+2・・・
と繰り返して、最後にiにEndRow2の値をいれて実行したら終了しています。
今回の内容だと、4行目からEndRow2の行数までを繰り返し足し算していく、
という内容になっています。

これで自作の集計の関数を作ることができました。

ちなみに、集計したい領域の中にエラーデータが入っていた場合、シート上の関数やWorksheetFunctionを使った場合は計算ができなくなります。

ただ、自作の関数の場合、For文の中にひと手間加えることでこれを回避することができます。

For i = 4 To EndRow2

If IsError(.Cells(i, 3).Value) Then

Else

SyunyuSyukei3 = SyunyuSyukei3 + .Cells(i, 3).Value
ShisyutsuSyukei3 = ShisyutsuSyukei3 + .Cells(i, 4).Value

End If
Next i

　If IsError(.Cells(i, 3).Value) Then
IsErrorというのはカッコ内がエラーだったときにTrueになる構文です。
この場合セルの値がエラーだった時にThen～Elseの動きを実行して、
エラーじゃなかったときにElse～End Ifの動きを実行します。
これでエラー回避が可能です。
こういったことができるのが、自作の関数のメリットです。

マクロを起動するタイミング

WorksheetFunctionと自作関数の2つのコードが作れましたので、これらをどうやって起動させるかを考えます。今回は3パターンで作ってみます。
ただその前に、WorksheetFunctionと自作関数それぞれを、これまでとは別のプロシージャに書いておきます。プロシージャ自体は今までと同じモジュールに書いても良いですし、別に書いてもOKです。
今回はそれぞれ別々のモジュールに書いておきます。

「集計」ボタンを作成し、クリックして起動させる

まずは前回と同じ、「集計」ボタンを作ってそれをクリックすることで起動させます。

これでクリック時に集計されるようになりました。
ただ、これだと毎回集計のためにクリックする必要があるので、
もう少しリアルタイムに集計されるようにしたいです。

「リストに追加」ボタンをクリック時に都度実行する

もう少しリアルタイム感を出すために、前回のマクロの中からこれらの関数を呼び出して、
勘定科目を追加するたびに都度集計するようにします。
マクロの中から別のマクロを呼び出す場合は【Call】という構文を使います。

Sub KamokuInput_1()

　'～（前回までに記載のマクロ）

Call SheetFunction_SUM

Call Jisaku_Sum

End Sub

これで勘定科目の『リストに追加』をクリックするたびに集計されるようになりました。
ただこれだと、後から表のデータを修正した時にはリアルタイムに集計してくれません。

シートの内容を変更時に実行する

最もリアルタイムに起動しようと思った場合、シートの内容を何か変更するたびにマクロを実行するようにしておくことで、完全リアルタイムに集計ができるようになります。
シートの内容を変更するたびにマクロを実行するには、Sheet1（入力）が持っているコードの中で、「Worksheet_SelectionChange」というプロシージャの中にコードを書けばOKです。

ただ、この方法は何か操作をするたびにマクロが実行されるので、動きの遅いマクロを呼び出すと毎回動きが遅くなってしまいます。
また、何か操作をするたびにマクロを起動して何かを変更するので、Excel の『やり直し』（Ctrl＋Z）ができなくなります。
なので、この方法をとる場合は注意が必要です。

Excel関数とマクロのメリット・デメリット

今回は集計なのでどちらでも問題ないが、複雑になってきたらこれらのメリット・デメリットを理解しつつ使い分けていくと良いと思います。

次の記事へ

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

Excelマクロで作る家計簿アプリの作り方実践・説明の記事です。本記事では、条件付き集計方法について、Excel関数を使った場合（SUMIF）、マクロを使った場合（For文とIf文の組み合わせ）について説明。またExcel関数での文字入力や相対参照・絶対参照についても説明。

yorozuya-tanuki.com