Excel VBAを使ったプロジェクトごとの経費使用状況報告書の自動生成

この記事では、Excel VBAを使用して、プロジェクトごとの経費使用状況報告書を自動生成する方法について解説します。具体的なコードとその詳細な解説、さらには応用例を通じて、経費報告の効率化と自動化を実現する手法を学びます。

基本的な処理手順

1. データソースの指定
2. Excelシートにデータを配置
3. データを集計・分析
4. 結果をレポートとして新しいシートに出力

コードの概要

Excel VBAを使用して、以下の手順でプロジェクトごとの経費使用状況報告書を生成します：

Sub GenerateExpenseReport()
    Dim wsData As Worksheet, wsReport As Worksheet
    Dim LastRow As Long

    ' データソースのワークシートとレポートのワークシートを設定
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsReport = ThisWorkbook.Sheets.Add
    wsReport.Name = "Expense Report"

    ' データの最終行を取得
    LastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    ' データをレポートシートにコピー
    wsData.Range("A1:D" & LastRow).Copy wsReport.Range("A1")

    ' データを集計
    wsReport.Range("F1").Value = "Project"
    wsReport.Range("G1").Value = "Total Expense"
    wsReport.Range("F2").Formula = "=UNIQUE(A2:A" & LastRow & ")"
    wsReport.Range("G2").Formula = "=SUMIF(A:A,F2,D:D)"

End Sub

コードの詳細解説

このコードは、データソースとして名前が”Data”のワークシートを使用し、新しい”Expense Report”という名前のワークシートを生成します。

– `Dim wsData As Worksheet, wsReport As Worksheet`：必要なワークシートを定義します。
– `Set wsData = ThisWorkbook.Sheets(“Data”)`：データソースのワークシートを指定します。
– `Set wsReport = ThisWorkbook.Sheets.Add`：新しいレポートワークシートを追加します。
– `LastRow = wsData.Cells(wsData.Rows.Count, “A”).End(xlUp).Row`：データソースの最終行を取得します。
– `wsData.Range(“A1:D” & LastRow).Copy wsReport.Range(“A1”)`：データをレポートワークシートにコピーします。
– `wsReport.Range(“F2”).Formula = “=UNIQUE(A2:A” & LastRow & “)”`：プロジェクト名のユニークなリストを生成します。
– `wsReport.Range(“G2”).Formula = “=SUMIF(A:A,F2,D:D)”`：各プロジェクトの合計経費を計算します。

応用例

応用例1：特定の金額以上のプロジェクトをハイライトする

Sub HighlightHighExpenses()
    Dim cell As Range

    For Each cell In ThisWorkbook.Sheets("Expense Report").Range("G2:G100")
        If cell.Value > 10000 Then
            cell.Interior.Color = vbRed
        End If
    Next cell
End Sub

このコードは、経費が10,000以上のプロジェクトを赤色でハイライトします。

応用例2：経費の平均を表示する

Sub ShowAverageExpense()
    Dim LastRow As Long

    With ThisWorkbook.Sheets("Expense Report")
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
        .Range("G" & LastRow + 2).Value = "Average Expense:"
        .Range("G" & LastRow + 3).Formula = "=AVERAGE(G2:G" & LastRow & ")"
    End With
End Sub

このコードは、すべてのプロジェクトの経費の平均を計算して表示します。

応用例3：経費をプロジェクトごとにグラフ化する

Sub GenerateExpenseChart()
    Dim LastRow As Long
    Dim rngData As Range
    Dim chrt As Chart

    With ThisWorkbook.Sheets("Expense Report")
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
        Set rngData = .Range("F1:G" & LastRow)
        Set chrt = .Shapes.AddChart2(251, xlColumnClustered).Chart
        chrt.SetSourceData Source:=rngData
    End With
End Sub

このコードは、プロジェクトごとの経費を棒グラフとして表示します。

まとめ

Excel VBAを使用して、効率的にプロジェクトごとの経費使用状況報告書を自動生成する方法を学びました。基本的な処理からさまざまな応用例まで、この技術を利用することで、経費管理の業務を大幅に効率化することができます。