Excel VBAで週次集計を自動化！ワンクリックで定例業務を劇的に効率化するマクロの作り方

Excel・VBA

はじめに：毎週の集計作業、もううんざりしていませんか？

毎週月曜日、または週末。決まってやってくるのが「週次集計」のタスクです。売上データ、勤怠データ、プロジェクト進捗、アクセス解析…集計内容は様々ですが、共通しているのは「定期的かつ手間がかかる」という点でしょう。

「先週のデータをコピーして、別のシートに貼り付けて、ピボットテーブルを更新して、グラフを作り直して…」手作業でやっていると、ちょっとしたミスでやり直しになったり、貴重な時間を奪われたりすることもしばしばです。

この記事では、そんな毎週の定例集計作業を、Excel VBAを使って「ワンクリック」で完了させる方法を解説します。初級者の方でも理解できるよう、具体的なコードと手順を丁寧に説明しますので、ぜひ最後までお読みください。

この記事で得られること：ワンクリックで週次集計を完了！

この記事を通じて、あなたは以下のことを習得し、業務に活かせるようになります。

  • 週次集計を自動化するためのExcelシートの構成方法
  • 特定の期間（例：前週）のデータを自動で抽出・集計するVBAコードの書き方
  • 作成したマクロをボタン一つで実行できるようにする方法
  • 日々のルーティンワークから解放され、より創造的な業務に時間を使えるようになる

【準備】週次集計を自動化するためのデータ構成

VBAで効率的に集計を行うには、元となるデータと集計結果を出すシートの構成が重要です。今回は、以下のようなシンプルな構成を例に進めます。

1. 集計元データの準備 (「DailyData」シート)

日々のデータが蓄積されていくシートを準備します。日付ごとにデータが並んでいることが前提です。例えば、以下のような売上データシートを想定します。

シート名: DailyData

日付 商品名 売上数 売上金額
2023/10/01 A商品 10 10000
2023/10/02 B商品 5 7500
… … … …
2023/10/07 A商品 12 12000

2. 集計結果シートの準備 (「WeeklyReport」シート)

週次集計の結果を出力するシートです。ここに前週のデータが自動でコピーされ、必要であれば集計関数などを設定します。

シート名: WeeklyReport

このシートは、最初は空で構いません。VBAが自動でデータを書き込みます。

【実践】Excel VBAで週次集計マクロを作成する

それでは、実際にVBAコードを書いていきましょう。

1. VBAエディタを開く

Excelを開き、以下の手順でVBAエディタを開きます。

  1. 開発タブの表示: Excelのリボンに「開発」タブが表示されていない場合、「ファイル」→「オプション」→「リボンのユーザー設定」から「開発」にチェックを入れてOKをクリックします。

  2. VBAエディタの起動: 「開発」タブをクリックし、「Visual Basic」をクリックします（または Alt + F11 キーを押します）。

  3. 標準モジュールの挿入: VBAエディタの左側のプロジェクトエクスプローラーで、対象のブック（例: VBAProject(Book1)）を右クリックし、「挿入」→「標準モジュール」を選択します。

2. マクロの基本構造と変数定義

挿入したモジュールに以下のコードを記述していきます。まずは、マクロの開始と終了、そして使用する変数を定義しましょう。

Option Explicit

Sub CreateWeeklyReport()
    
    ' 変数の宣言
    Dim wsDailyData As Worksheet
    Dim wsWeeklyReport As Worksheet
    Dim lastRowDaily As Long
    Dim startOfWeek As Date
    Dim endOfWeek As Date
    Dim dataRange As Range
    
    ' 各シートを設定
    Set wsDailyData = ThisWorkbook.Sheets("DailyData")
    Set wsWeeklyReport = ThisWorkbook.Sheets("WeeklyReport")
    
    ' 処理開始メッセージ
    MsgBox "週次集計を開始します。", vbInformation
    
    ' ここに具体的な処理を記述していきます
    
    ' 処理完了メッセージ
    MsgBox "週次集計が完了しました！", vbInformation
    
End Sub

Option Explicit は、変数の宣言忘れを防ぐためのおまじないです。必ず記述するようにしましょう。

3. 前週データの特定とコピー

次に、集計対象となる「前週」の日付範囲を計算し、その範囲のデータを「DailyData」シートからコピーする処理を追加します。

    ' 今日の日付を基準に前週の日曜日と土曜日を計算
    ' Weekday関数は、vbSunday=1, vbMonday=2, ..., vbSaturday=7 を返す
    endOfWeek = Date - Weekday(Date, vbSunday) - 1 ' 今日の前の週の土曜日
    startOfWeek = endOfWeek - 6 ' その週の日曜日
    
    ' DailyDataシートの最終行を取得
    lastRowDaily = wsDailyData.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 前週のデータを抽出してコピー
    ' ヘッダー行を除く2行目から最終行までをループ
    wsDailyData.Activate ' オートフィルターを使うためにシートをアクティブ化
    With wsDailyData.Range("A1:D" & lastRowDaily)
        .AutoFilter Field:=1, Criteria1:=">=" & startOfWeek, Operator:=xlAnd, Criteria2:="<=" & endOfWeek
        Set dataRange = .SpecialCells(xlCellTypeVisible)
    End With
    
    ' フィルターがかかったデータ（ヘッダー含む）をコピー
    If Not dataRange Is Nothing Then
        dataRange.Copy
    Else
        MsgBox "前週のデータが見つかりませんでした。", vbExclamation
        GoTo CleanUp
    End If

このコードでは、AutoFilter を使って日付でフィルタリングし、表示されているセル（xlCellTypeVisible）をコピーしています。これにより、必要なデータだけを効率的に抽出できます。

4. 集計シートへの貼り付けと整形

コピーしたデータを「WeeklyReport」シートに貼り付け、必要に応じて整形します。

    ' WeeklyReportシートをクリアして貼り付け
    wsWeeklyReport.Cells.ClearContents ' 既存の内容をクリア
    wsWeeklyReport.Range("A1").PasteSpecial xlPasteValues ' 値のみ貼り付け
    Application.CutCopyMode = False ' コピーモードを解除
    
    ' ヘッダー行に日付範囲を追記（オプション）
    wsWeeklyReport.Range("E1").Value = "対象期間: " & Format(startOfWeek, "yyyy/mm/dd") & " - " & Format(endOfWeek, "yyyy/mm/dd")
    
    ' 列幅の自動調整（オプション）
    wsWeeklyReport.Cells.Columns.AutoFit
    
    ' オートフィルターを解除
    wsDailyData.AutoFilterMode = False

CleanUp:
    ' オブジェクトの解放
    Set wsDailyData = Nothing
    Set wsWeeklyReport = Nothing
    Set dataRange = Nothing

PasteSpecial xlPasteValues を使うことで、書式はコピーせず値だけを貼り付けることができます。また、Application.CutCopyMode = False は、コピー状態を解除するために重要です。

5. 完成版VBAコード

これまでのコードを結合し、エラーハンドリングなども加えた完成版です。

Option Explicit

Sub CreateWeeklyReport()
    
    ' 変数の宣言
    Dim wsDailyData As Worksheet
    Dim wsWeeklyReport As Worksheet
    Dim lastRowDaily As Long
    Dim startOfWeek As Date
    Dim endOfWeek As Date
    Dim dataRange As Range
    Dim filterRange As Range
    
    ' エラーハンドリング
    On Error GoTo ErrorHandler
    
    ' 各シートを設定
    Set wsDailyData = ThisWorkbook.Sheets("DailyData")
    Set wsWeeklyReport = ThisWorkbook.Sheets("WeeklyReport")
    
    ' 処理開始メッセージ
    Application.ScreenUpdating = False ' 画面更新を停止
    MsgBox "週次集計を開始します。", vbInformation, "週次集計マクロ"
    
    ' 今日の日付を基準に前週の日曜日と土曜日を計算
    ' Weekday関数は、vbSunday=1, vbMonday=2, ..., vbSaturday=7 を返す
    endOfWeek = Date - Weekday(Date, vbSunday) - 1 ' 今日の前の週の土曜日
    startOfWeek = endOfWeek - 6 ' その週の日曜日
    
    ' DailyDataシートの最終行を取得
    lastRowDaily = wsDailyData.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' データがヘッダー行しかない場合は処理を中断
    If lastRowDaily < 2 Then
        MsgBox "DailyDataシートにデータがありません。", vbExclamation, "週次集計マクロ"
        GoTo CleanUp
    End If
    
    ' フィルター範囲を設定 (ヘッダー含む)
    Set filterRange = wsDailyData.Range("A1:D" & lastRowDaily)
    
    ' オートフィルターを適用して前週のデータを抽出
    filterRange.AutoFilter Field:=1, Criteria1:=">=" & startOfWeek, Operator:=xlAnd, Criteria2:="<=" & endOfWeek
    
    ' フィルターがかかったデータ（ヘッダー含む）をコピー
    ' visibleCells を直接コピーすると、ヘッダー行のみの場合にエラーになるため、事前にチェック
    Set dataRange = filterRange.SpecialCells(xlCellTypeVisible)
    
    If Not dataRange Is Nothing Then
        ' ヘッダー行のみの場合はコピーしない (A1がヘッダー)
        If dataRange.Rows.Count = 1 And dataRange.Cells(1, 1).Value = wsDailyData.Cells(1, 1).Value Then
            MsgBox "前週のデータが見つかりませんでした。", vbExclamation, "週次集計マクロ"
            GoTo CleanUp
        Else
            dataRange.Copy
            
            ' WeeklyReportシートをクリアして貼り付け
            wsWeeklyReport.Cells.ClearContents ' 既存の内容をクリア
            wsWeeklyReport.Range("A1").PasteSpecial xlPasteValues ' 値のみ貼り付け
            Application.CutCopyMode = False ' コピーモードを解除
            
            ' ヘッダー行に日付範囲を追記（オプション）
            wsWeeklyReport.Range("E1").Value = "対象期間: " & Format(startOfWeek, "yyyy/mm/dd") & " - " & Format(endOfWeek, "yyyy/mm/dd")
            
            ' 列幅の自動調整（オプション）
            wsWeeklyReport.Cells.Columns.AutoFit
        End If
    Else
        MsgBox "前週のデータが見つかりませんでした。", vbExclamation, "週次集計マクロ"
    End If
    
CleanUp:
    ' オートフィルターを解除
    If wsDailyData.AutoFilterMode Then
        wsDailyData.AutoFilterMode = False
    End If
    
    ' オブジェクトの解放
    Set wsDailyData = Nothing
    Set wsWeeklyReport = Nothing
    Set dataRange = Nothing
    Set filterRange = Nothing
    
    Application.ScreenUpdating = True ' 画面更新を再開
    MsgBox "週次集計が完了しました！", vbInformation, "週次集計マクロ"
    Exit Sub

ErrorHandler:
    Application.ScreenUpdating = True ' エラー時も画面更新を再開
    MsgBox "エラーが発生しました: " & Err.Description, vbCritical, "週次集計マクロ"
    Resume CleanUp
    
End Sub

6. マクロの実行と動作確認

VBAエディタでF5キーを押すか、Excelに戻って「開発」タブ→「マクロ」からCreateWeeklyReportを選択して実行します。正しく動作すれば、「WeeklyReport」シートに前週のデータがコピーされているはずです。

【応用】ワンクリックボタンの設置

毎回VBAエディタを開くのは面倒ですよね。Excelシート上にボタンを設置し、クリック一つでマクロを実行できるようにしましょう。

  1. ボタンの挿入: 「開発」タブの「挿入」から「フォームコントロール」の「ボタン」を選択します。

  2. ボタンの描画: シート上の任意の場所にドラッグしてボタンを描画します。

  3. マクロの割り当て: ボタンを描画すると「マクロの登録」ダイアログが表示されます。作成したCreateWeeklyReportを選択し、「OK」をクリックします。

  4. ボタン名の変更: ボタンを右クリックし、「テキストの編集」で「週次集計実行」など分かりやすい名前に変更します。

これで、このボタンをクリックするだけで、週次集計マクロが実行されるようになります。

さらに高度な自動化を目指すには

今回の基本的な週次集計マクロをベースに、さらに業務効率を高めるためのアイデアをいくつかご紹介します。

  • 複数ファイルの処理: 複数のExcelファイルからデータを集めて集計する。
  • 集計結果のグラフ化: 集計データから自動でグラフを作成・更新する。
  • レポートのPDF出力: 完成したレポートを自動でPDFとして保存する。
  • メールでの自動送信: 集計結果やPDFレポートを関係者に自動でメール送信する。
  • ユーザーフォームの活用: 集計期間や出力形式をユーザーが選択できるようにする。

これらは少し難易度が上がりますが、VBAを学ぶことで実現可能です。

まとめ：VBAで業務効率を劇的に向上させよう

この記事では、Excel VBAを使って毎週の定例集計作業をワンクリックで自動化する方法を解説しました。手作業によるミスや時間の浪費を減らし、より生産的な業務に集中できるようになるはずです。

VBAは、一度覚えてしまえば日々の業務を劇的に効率化できる強力なツールです。ぜひ今回のコードを参考に、あなたの業務に合わせたカスタマイズを試してみてください。自動化の喜びを体験し、あなたの仕事をもっとスマートにしていきましょう！

コメント

タイトルとURLをコピーしました