エクセル練習問題：実績/予算の3年間通算累計をVBAで動的に算出する手法
業務アプリケーション開発において、財務データの集計は避けて通れないタスクです。特に「実績」と「予算」を比較し、さらに「3年間」という期間にわたる累計を算出する処理は、Excel VBAの習熟度を測る非常に良い題材となります。本記事では、単なる数式の埋め込みではなく、データ構造を理解し、保守性の高いコードを実装するための技術的アプローチを解説します。
1. 概要と問題定義
今回の課題は、以下の構造を持つデータセットを想定します。
・列構成：項目名、年度、月、実績値、予算値
・データ期間：3年間（例：2021年度～2023年度）
・出力要件：各項目ごとの「3年間累計実績」および「3年間累計予算」を算出し、別シートに出力する。
この課題のポイントは、データが「行持ち（縦持ち）」である場合、単純なVLOOKUPやSUMIFだけでは処理が煩雑になる点です。VBAを用いることで、Dictionaryオブジェクトを活用した高速な集計と、動的な出力制御が可能となります。これにより、データ量が増加してもパフォーマンスを維持し、コードの再利用性を高めることができます。
2. 技術的詳細解説
この問題を解くためのアルゴリズムは、以下の4つのステップに分解されます。
ステップ1：データソースの読み込み
シート上のデータを配列に取り込みます。セルを一つずつ走査する「Rangeオブジェクトへのアクセス」は非常に低速であるため、必ず一旦Variant型の配列に一括格納します。これにより、メモリ上での高速処理が可能となります。
ステップ2：Dictionaryによる集計
集計のキーとなる「項目名」をキーとし、実績と予算の累計値を保持するクラス、あるいは配列を値としてDictionaryに格納します。Dictionaryは検索コストがO(1)であるため、数万行のデータであっても瞬時に集計が完了します。
ステップ3：データの正規化とフィルタリング
3年間という期間を限定するため、年度列を判定条件に加えます。ここでは、定数または外部設定シートから「開始年度」と「終了年度」を読み込む設計にすると、将来的な期間変更にも柔軟に対応できます。
ステップ4：出力処理
集計されたDictionaryから値を取り出し、出力先シートへ書き出します。この際、シートの最終行を自動判定し、既存のデータが存在する場合はクリアするなどの「初期化処理」を忘れないようにします。
3. サンプルコード
以下に、実務レベルでそのまま使用可能なモジュール構成のコードを提示します。
Option Explicit
' 3年間通算累計を算出するメインプロシージャ
Public Sub GenerateThreeYearSummary()
Dim wsSource As Worksheet, wsOutput As Worksheet
Dim lastRow As Long
Dim dataArray As Variant
Dim dict As Object
Dim i As Long
Dim key As String
Dim results As Variant
' 初期設定
Set wsSource = ThisWorkbook.Sheets("Data")
Set wsOutput = ThisWorkbook.Sheets("Summary")
Set dict = CreateObject("Scripting.Dictionary")
lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
dataArray = wsSource.Range("A2:E" & lastRow).Value
' 集計処理
For i = 1 To UBound(dataArray, 1)
' 列: 1=項目, 2=年度, 3=月, 4=実績, 5=予算
' 3年間(2021-2023)のフィルタリング
If dataArray(i, 2) >= 2021 And dataArray(i, 2) <= 2023 Then
key = dataArray(i, 1)
If Not dict.Exists(key) Then
' 配列で [実績累計, 予算累計] を保持
dict.Add key, Array(0#, 0#)
End If
results = dict(key)
results(0) = results(0) + dataArray(i, 4)
results(1) = results(1) + dataArray(i, 5)
dict(key) = results
End If
Next i
' 出力処理
Call WriteSummaryToSheet(wsOutput, dict)
MsgBox "累計算出が完了しました。", vbInformation
End Sub
' 出力専用プロシージャ
Private Sub WriteSummaryToSheet(ws As Worksheet, dict As Object)
Dim key As Variant
Dim r As Long
ws.Cells.Clear
ws.Range("A1:C1").Value = Array("項目名", "3年間実績累計", "3年間予算累計")
r = 2
For Each key In dict.Keys
ws.Cells(r, 1).Value = key
ws.Cells(r, 2).Value = dict(key)(0)
ws.Cells(r, 3).Value = dict(key)(1)
r = r + 1
Next key
ws.Columns("A:C").AutoFit
End Sub
4. 実務アドバイス
プロフェッショナルとして、コードを書く際に意識すべき点は「堅牢性（Robustness）」です。
1. 型変換の意識：
Excel上の数値が文字列として認識されているケースは非常に多いです。集計時には `Val()` 関数や `CDbl()` を使用して、明示的に数値型へ変換することを推奨します。これにより、予期せぬ型不一致エラーを防げます。
2. エラーハンドリング：
データが空である場合や、シートが存在しない場合など、実行時エラーが発生する箇所には `On Error GoTo` を活用したエラーハンドラを実装してください。特に大規模な業務システムでは、ユーザーが操作ミスをした際に「なぜ止まったか」を明示することが重要です。
3. 定数の外部化：
年度の範囲（2021～2023）をコード内にハードコーディングするのは避けるべきです。設定シートを作成し、そこから定数を読み込む設計にすれば、運用保守のコストを大幅に削減できます。
4. 処理速度の最適化：
`Application.ScreenUpdating = False` をコードの冒頭で宣言し、最後に `True` に戻すことで、画面描画を停止させ、処理速度を向上させることができます。数万行単位のデータを扱う際は、この一行が劇的な差を生みます。
5. まとめ
実績と予算の通算累計算出は、Excel VBAの基礎が凝縮された非常に価値のあるタスクです。Dictionaryオブジェクトを活用した動的集計は、ピボットテーブルでは実現しにくい「複雑な計算ロジック」や「特定のビジネスルール」を適用する際に極めて有効な武器となります。
今回のコードをベースに、さらに「月別の推移を横持ちに展開する」や「予算達成率を自動計算する」といった機能を追加していくことで、自分だけの強力な自動化ツールへと進化させることが可能です。VBAは単なる記録マクロではありません。データ構造を論理的に整理し、効率的なアルゴリズムを構築するためのプログラミング言語であることを忘れないでください。
本記事で解説した手法を習得すれば、どんなに複雑な財務データであっても、確実かつ高速に集計・レポート化することができるようになります。ぜひ、ご自身の業務環境でこのコードをテストし、さらなるカスタマイズに挑戦してみてください。エンジニアとしてのスキルアップは、こうした小さな効率化の積み重ねから始まります。
