Excelで月次集計表を作成していると、締め日が月初や月末と一致しないケースがあります。例えば「4月分」が3月21日から4月20日までの場合、1日から20日までは現在の入力表、21日以降は翌月の入力表からデータを取得するような処理が必要になります。
このような場合、VBAを利用してセルに設定した参照月から対象ファイル名を自動生成し、別ファイルを開いてINDEX・MATCHと同じような検索処理を行うことで効率的に集計できます。この記事では、翌月ファイルを自動的に読み込む基本的な考え方と実装例を解説します。
月をまたぐ集計表で発生する問題
通常のExcel関数だけで集計する場合、同じブック内のデータであればINDEX関数やMATCH関数を使って簡単に検索できます。しかし、翌月分のデータが別ファイルに保存されている場合、参照先ブックを動的に変更する必要があります。
例えば4月分の集計では、4月1日から4月20日までは「4月入力表.xlsx」、4月21日から4月30日までは「5月入力表.xlsx」を参照するというような処理になります。
この場合、毎月手作業でファイルを指定すると入力ミスが発生しやすいため、基準となる月を入力するセルから自動的に翌月ファイルを判断する仕組みを作ると便利です。
A2セルから翌月ファイル名を作成する方法
例えばA2セルに集計対象月が入力されている場合、VBAではDateAdd関数を使って翌月の日付を取得できます。
例としてA2セルに2024年4月の日付が入力されている場合、以下のように翌月を取得できます。
Dim targetMonth As Date
Dim nextMonth As Date
targetMonth = Range("A2").Value
nextMonth = DateAdd("m", 1, targetMonth)
このnextMonthを利用して、「202405入力表.xlsx」のようなファイル名を自動生成できます。
セルA18のファイル名を使って別ファイルを開くVBA例
A18セルに参照するファイル名がセットされている場合、その値を使って対象ファイルを開くことができます。
Sub OpenNextMonthFile()
Dim fileName As String
Dim folderPath As String
Dim wb As Workbook
folderPath = ThisWorkbook.Path & "\"
fileName = Range("A18").Value
Set wb = Workbooks.Open(folderPath & fileName)
End Sub
この処理では、現在開いている集計ブックと同じフォルダ内にあるファイルを開きます。保存場所が固定されている場合は、folderPath部分を指定のフォルダパスに変更します。
例えばA18セルに「202405入力表.xlsx」と入力されていれば、そのファイルを自動的に開いて参照できます。
VBAでINDEX・MATCHと同じ検索処理を行う方法
別ファイルを開いた後は、WorksheetFunctionのMatch関数とIndex関数を利用すると、Excel関数と同じ検索処理をVBA内で実行できます。
Dim result As Variant
Dim ws As Worksheet
Set ws = wb.Worksheets("入力表")
result = WorksheetFunction.Index(ws.Range("B:B"), _
WorksheetFunction.Match(Range("A1").Value, ws.Range("A:A"), 0))
この例では、A列から検索値を探し、該当する行のB列データを取得しています。実際には日付や社員番号など、集計表で利用しているキーに合わせて範囲を変更します。
また、MATCHで見つからない場合はエラーになるため、実際の運用ではOn Error処理を追加すると安定します。
21日以降だけ翌月ファイルを参照する条件分岐
締め日が20日の場合、日付によって参照先を切り替える必要があります。例えば日付が21日以上なら翌月ファイル、それ以前なら現在ファイルを参照するという条件にできます。
If Day(targetDate) >= 21 Then
Set sourceBook = Workbooks.Open(nextMonthFile)
Else
Set sourceBook = ThisWorkbook
End If
このような条件分岐を作ることで、利用者が意識せずに正しい入力表からデータを取得できます。
実際の業務では月末日が28日、30日、31日と変化するため、固定した日付だけではなくDate関数や月末判定を組み合わせると、より柔軟な集計処理になります。
外部ファイル参照マクロを作るときの注意点
別ファイルを自動的に開く処理では、ファイル名や保存場所が変更されるとエラーになります。そのため、ファイルの存在確認を入れておくことが重要です。
If Dir(folderPath & fileName) = "" Then
MsgBox "参照ファイルがありません"
Exit Sub
End If
また、処理終了後には不要になった参照ファイルを閉じることで、メモリ使用量を抑えることができます。
月次処理のように毎回同じ作業を行う場合、ファイル名規則や保存場所を統一すると、VBAによる自動化の効果が高まります。
まとめ
月をまたぐ集計表では、現在月の入力表と翌月の入力表を切り替えて参照する仕組みが必要になる場合があります。
VBAでは、A2セルなどに入力した対象月から翌月を計算し、A18セルなどに設定したファイル名を利用して別ブックを開くことができます。さらにINDEX・MATCH相当の検索処理を組み合わせれば、自動的な月次集計が可能です。
ポイントは、ファイル名の管理、存在確認、エラー処理を含めた設計にすることです。適切に作成すれば、毎月発生する集計作業を大幅に効率化できます。
コメント