【Excel家計簿(4/6)】現在の口座状況を集計して表示する

前回の記事までで各口座に金額を入力していく機能については完成になりました。

今回と次回の記事で入力された金額を集計して結果表示する機能を作成していきます。

※Excel家計簿を作成した方法についての記事の4つ目になります。その他の記事は以下にまとめています。

【VBA】Excelで家計簿を作成する

この記事で実装する口座ごとの残高一覧

今回作成している家計簿では、口座ごとに金額を管理しています。そこで「各口座にどれだけ残高があるか」を一覧で表示できるようにします。

具体的には下記のような一覧をVBAを使用して作成しました。

SUMIFとかの関数を使用すれば、各シートから集計することも出来るかもしれませんが、今回はVBAを使用して作成を行いました。

集計関数を使用すると重くなるというデメリットもあるし、VBAで作成したほうが機能を拡張しやすいからです。

集計を表示するためのワークシートを作成する

まずは、今回作成する口座の残高一覧を表示するためのワークシートを作成します。「サマリー」という名前で作成を行いました。

次に、このサマリーシートにVBAプログラムで記載を行わない部分のセルに値を入れます。

AB列を結合して「現在」という文言を入れました。（現在の口座残高の一覧なので）

また表の見出しとして「口座名」、「残高」という文字を3行目に入れました。

その他の明細行や表レイアウトはVBAから記載する想定なので、これでワークシートの設定は完了となります。

サマリーシートの定数定義を行う

次に、前回の記事で作成したConstsという定数定義を記載したモジュールにサマリーシートで使用する定数を追加していきます。

追加する定義は以下の通り

'ワークシート名
Public Const SH_NAME_SUMMARY = "サマリー"

'サマリーシートの列定義
Public Enum shSummaryColumns
    KOZA = 1
    ZANDAKA = 2
End Enum

'サマリーシートの行定義
Public Enum shSummaryRows
    START_ROW = 4   '表データの開始行
End Enum

'テーブルとして書式設定のテーブル名
Public Const LAYOUT_SUMMARY = "サマリーテーブル"

'テーブルレイアウト名
Public Const TABLE_STYLE = "TableStyleMedium9"

上3つはワークシートの基本情報です。シート名、列の定義情報、どの行から明細行が始まるかという情報になります。

下2つはテーブルレイアウトを設定するための情報となります。

「テーブルとして書式設定」を行う際に、下記のコードのように「何のテーブルに、どのレイアウトを当てる」かという情報が必要になります。

shSummary.ListObjects(LAYOUT_SUMMARY).TableStyle = TABLE_STYLE

今回は、口座ごとの残高一覧表に「サマリーテーブル」という名前をつけて、「TableStyleMedium9」というレイアウトを適用します。

TableStyleMedium9とは下記の「青,テーブルスタイル(中間)9」のことです。

VBAを高速で動かすための共通関数を作成

表を作成するVBAを記載する前に、全機能が使用する共通関数を作成しておきます。それは「処理を高速化するための関数」になります。

「Util」という標準モジュールを作成しました。共通で使う関数はこちらに作成していくことにします。

作成した関数は以下のとおりです。

'処理開始時の処理（画面描画を止める）
Public Sub appstart()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
End Sub

'処理終了時の処理（画面描画を開始する）
Public Sub append()
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

VBAでメイン処理が始まる前にappstart、メイン処理が終わった後にappendを実行します。

Application.DisplayAlertsは、VBA実行中にアラートを表示するかどうかの設定です。確認ダイアログなどが発生してVBAが中断されたくないのでfalseとします。

Application.ScreenUpdatingは画面の描画をするかどうか、Application.Calculationは関数を再評価するかどうかの設定となります。

上記の2つをflaseとしておくと、VBAの処理速度が格段に上がるのでおすすめです。

サマリーシートの「現在」表を更新する

本題のサマリーシートを更新する処理ですが、以下のような流れになります。

サマリーシートをクリアする
テーブルの書式を解除
ワークシートをループさせて口座ごとの金額を取得
テーブルに書式を設定する

'サマリーシート更新のメイン処理
Public Sub btn_summary_calc_click()
    '共通初期処理
    Call appstart

    'サマリーシートをクリアする
    Dim shSummary As Worksheet
    Set shSummary = ThisWorkbook.Worksheets(SH_NAME_SUMMARY)
    shSummary.Rows(shSummaryRows.START_ROW & ":" & shSummary.Rows.Count).Clear
    
    'テーブルの書式を解除
    For Each ls In shSummary.ListObjects
      ls.Unlist
    Next ls
    
    '現在操作している行
    Dim lngRow As Long
    lngRow = shSummaryRows.START_ROW
    
    '合計金額
    Dim lngGokei As Long
    lngGokei = 0

    Dim i As Integer
    'ワークシートをループさせる
    For i = 1 To ThisWorkbook.Worksheets.Count
        Dim sh As Worksheet
        Set sh = ThisWorkbook.Worksheets(i)
        '口座のシートのみを対象とする
        If sh.CodeName Like "shKoza*" And sh.Name <> SH_NAME_TEMPLATE Then
            '口座名列を入力
            shSummary.Cells(lngRow, shSummaryColumns.KOZA).Value = sh.Name
            '残高列を入力
            Dim lngZandaka As Long
            lngZandaka = getZandaka(sh)
            shSummary.Cells(lngRow, shSummaryColumns.ZANDAKA).Value = lngZandaka
            '合計金額を加算
            lngGokei = lngGokei + lngZandaka
            lngRow = lngRow + 1
        End If
    Next i
    
    '合計金額列を入力
    shSummary.Cells(lngRow, shSummaryColumns.KOZA).Value = "合計"
    shSummary.Cells(lngRow, shSummaryColumns.ZANDAKA).Value = lngGokei

    'テーブルに書式を設定する
    shSummary.ListObjects.Add(xlSrcRange, Range( _
        getColumnAddress(shSummaryColumns.KOZA) & shSummaryRows.START_ROW - 1 & ":" & getColumnAddress(shSummaryColumns.ZANDAKA) & lngRow), , xlYes).Name = LAYOUT_SUMMARY
    shSummary.ListObjects(LAYOUT_SUMMARY).TableStyle = TABLE_STYLE

    '共通終了処理
    Call append
End Sub

'口座シートから口座の残高金額を取得する
Private Function getZandaka(sh As Worksheet) As Long
    Dim lngRow As Long
    
    '返却値を初期化
    getZandaka = 0
    
    'ワークシートを使用最終行までループ
    For lngRow = shKozaRows.START_ROW To sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
        Select Case sh.Cells(lngRow, shKozaColumns.SHUBETU).Value
            '種別列が初期残高、収入、入金の場合
            Case SHUBETU_SHOKI, SHUBETU_SHUNYU, SHUBETU_NYUKIN
                '返却値を加算する
                getZandaka = getZandaka + sh.Cells(lngRow, shKozaColumns.KINGAKU).Value
                
            '種別列が支出、出金の場合
            Case SHUBETU_SHISHUTU, SHUBETU_SHUKIN
                 '返却値を減算する
                getZandaka = getZandaka - sh.Cells(lngRow, shKozaColumns.KINGAKU).Value
            Case Else
        End Select
    Next lngRow
End Function

「サマリーシートをクリアする」の部分で既存の表領域を選択してClearをしています。仮に口座が前回より少なくなった場合に、Clearをしないと結果がおかしくなってしまうからです。

「テーブルの書式を解除」を行っているのは、「テーブルとして書式設定」を行うためには既存の書式設定が解除されている必要があるためです。（解除されていないと重複エラーになる）

今回の家計簿では、口座用のシートはオブジェクト名が「shKoza」で始まるというルールになっているので、口座用のシートであれば金額の合計をして残高表に転記する形にしています。

これで現在の残高は確認できるようになったので、次回の記事では過去の残高からの推移を表示する機能を作成します。

次記事