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

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

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

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

【本記事の目標】

色々な集計方法を知ろう（条件付き集計方法を知ろう）

前回からの続きです。前回投稿と併せてご確認ください。

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

条件がある集計方法について
1. Excel関数を使った条件付き集計の方法
2. マクロを使った条件付き集計方法
次のステップに向けて配置換え

条件がある集計方法について

前回は [収入の欄の合計] だとか、[支出の欄の合計] といったように、決まった領域にある全ての値を合計する方法について学びました。

じゃあ、例えば「支出金額」の欄にある値の中で、『変動費』だけの合計金額が知りたい、という場合はどうしたらいいのでしょうか。

そういう場合に使うのが【条件付き】の集計です。
こちらも前回同様、Excel関数を使った方法と、マクロを使った方法の２通りの方法がありますので、それぞれ説明していきます。

Excel関数を使った条件付き集計の方法

まずはExcel関数を使う方法です。
前回、単純に合計する場合は　SUM　を使いましたが、今回は　SUMIF　という関数を使います。

SUM関数では[合計したい領域]だけを指定しましたが、
SUMIF関数では、

検索したい単語が書かれている領域
検索したい単語
合計したい領域

この３つを指定します。
これを指定すれば、
　　「検索したい単語が書かれている領域」の中で
　　「検索したい単語」と同じ単語が書かれている行または列と同じ行または列の
　　「合計したい領域」の値を集計していく
という動きをします。

今回の場合ですと、固定費と変動費それぞれの合計金額を出したいので、
「検索したい単語が書かれている領域」は【勘定科目】の欄、
「検索したい単語」は【固定費】もしくは【変動費】、
「合計したい領域」は【支出金額】の欄ですので、
実際に書くと下記のようになります。

　　=SUMIF(B4:B20,”固定費”,D4:D20)

Excelの関数も、裏で動いているのはプログラミングです。
プログラミングは基本的に半角の英語・数字・記号しか使えません。
なので、Excel関数も同じように基本的に半角英数・記号しか使えません。
しかし今回のように、日本語の全角文字を使いたい場面は必ずあります。
その場合に使うのが、【”】この記号、ダブルクォーテーションマークです。
【”】この記号を前後につけることで、その間の文字は全角・半角関係なく、
一種の記号のように認識されて、問題なく計算できるようになります。

Excel関数に直接日本語の全角文字を入力したい場合は、”　”　で挟む。
これを覚えておきましょう。

この関数を入力すると、固定費の合計金額（上記だと690）が計算されます。

上記ではExcel関数の中に直接【固定費】と入力していますが、１つのセルを指定することもできます。今回の場合、G13のセルに題目として【固定費】と書いている欄がありますので、その欄を使えば、

　　=SUMIF($B$4:$B$20,G13,$D$4:$D$20)

と書くこともできます。動きとしては全く同じ動きをします。
こちらの式を使うと、例えばG14セルに上記の式を入力して、G14セルをコピーし、H14セルに貼り付けると、自動的に関数が書き換わって、【変動費】の合計金額を出してくれるようになります。

上記関数の中に書いてある　$　の記号は、その直後を【絶対参照】に指定する、という記号です。
【絶対参照】とは何か、ですが、上記のように関数が書かれたセルをコピーして別セルに貼り付けたとき、書かれていた関数が自動的に変化します。
これは、Excelの関数は指定しなければ基本的に「関数が入力されたセルからどのぐらい離れているか」というのを守ろうとするので、関数が入力されたセルの１つ上のセルの値を見ている場合、関数が入力されたセルの場所が変わったら、そのセルの１つ上のセルの値を見るようになります。これを【相対参照】と言います。

これに対し、関数が入力されたセルがどこに動こうとも、常に同じ場所のセルを見ようとする、これが【絶対参照】です。
絶対参照は行だけ、列だけ、という指定も可能です。
行だけの場合「B$4」、列だけの場合「$B4」、行列両方の場合「$B$4」という表現になります。
関数を色々な場所にコピーする際のテクニックの一つですので、覚えておくと便利です。

マクロを使った条件付き集計方法

ではマクロを使った場合はどうなるのか。

WorksheetFunctionを使った場合の書き方は、前回説明した流れと全く同じですので、今回は割愛します。

Excel関数として
　　=SUMIF(B4:B20,”固定費”,D4:D20)
と書いているので、WorksheetFunctionでも同じように
　　WorksheetFunction.SumIf(B4:B20,”固定費”,D4:D20)
と指定すればOKです。

ではそれ以外の方法はどんなものがあるのか。

基本は前回と同じ　For文　を使います。
ただし、For文で繰り返し足し算をしていく前に、毎回その値を足していいのかどうかのチェックをしていきます。そのために使うのが、以前に紹介した　If文　です。

For文で繰り返し作業をしていきながら、その中で毎回If文で判定を行っていきます。
コードで書くと下記のようになります。

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.Sheets("入力")

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

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

If .Cells(i, 2).Value = "固定費" Then

    Koteihi = Koteihi + .Cells(i, 4).Value

End If

If .Cells(i, 2).Value = "変動費" Then

    Hendouhi = Hendouhi + .Cells(i, 4).Value

End If


End If
Next i

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

.Range("G15") = Koteihi
.Range("H15") = Hendouhi

End With

これは、前回のコードに今回のコードを追記していますが、追記した部分だけを抜き出すと、

~~
For i = 4 To EndRow2

~~

If .Cells(i, 2).Value = "固定費" Then

    Koteihi = Koteihi + .Cells(i, 4).Value

End If

If .Cells(i, 2).Value = "変動費" Then

    Hendouhi = Hendouhi + .Cells(i, 4).Value

End If

~~

Next i

~~

.Range("G15") = Koteihi
.Range("H15") = Hendouhi

この部分が追加されています。

For文の間（For～Nextの間）にIf文が２つ入っているのがわかります。
それぞれが、
　　「勘定科目」の欄が『固定費』だった場合、『変動費』だった場合に、
　　同じ行の「支出金額」の欄に記載の値を、
　　それぞれの定義された変数に足し算していく
という動きをさせています。

このように、For文やIf文などの動きを指示する構文は、階層でいくつも組み合わせて使っていくことで、どんどん複雑な動きをさせていくことができるようになっていきます。

個人的には、このFor文とIf文さえ使いこなせれば大抵のマクロの6~7割は作れるようになると思ってます。そのぐらい、この２つを組み合わせて使っていくというのは重要になっていきますので、頑張って慣れていきましょう。

次のステップに向けて配置換え

ここまでで自動的に集計するマクロは完成です。
次はいよいよグラフ化をしていくのですが、
今のままだと色々な集計方法の結果が混ざった状態になっているので、
一旦、For文を使った集計だけ残して書き直します。
またグラフ化しやすいように配置も少し動かします。

変更後のコードとExcelシートは下記の通りです。

■モジュール１

Option Explicit

Sub KamokuInput_1()
'リストに追加をクリック

Dim Kingaku1 As Double

Kingaku1 = ThisWorkbook.Sheets("入力").Range("B1").Value


Dim Syushi1 As String
Dim Kamoku1 As String

Syushi1 = ThisWorkbook.Sheets("入力").Range("D1").Value

If Syushi1 = "収入" Then
    Kamoku1 = ""
Else
    Kamoku1 = ThisWorkbook.Sheets("入力").Range("E1").Value
End If



Dim EndRow1 As Integer

With ThisWorkbook.Sheets("入力")

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


.Cells(EndRow1, 1).Value = Syushi1
.Cells(EndRow1, 2).Value = Kamoku1

If Syushi1 = "収入" Then
    .Cells(EndRow1, 3).Value = Kingaku1
Else
    .Cells(EndRow1, 4).Value = Kingaku1
End If

End With

Call Jisaku_SUM

End Sub

■モジュール２

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.Sheets("入力")

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

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

If .Cells(i, 2).Value = "固定費" Then

Koteihi = Koteihi + .Cells(i, 4).Value

End If

If .Cells(i, 2).Value = "変動費" Then

Hendouhi = Hendouhi + .Cells(i, 4).Value

End If


End If
Next i

.Range("G5") = SyunyuSyukei3
.Range("G6") = ShisyutsuSyukei3

.Range("G8") = Koteihi
.Range("G9") = Hendouhi

End With

End Sub

■VBA画面