Excel VBAを使って部署別の経費集計レポートを作成する方法

この記事では、Excel VBAを使用して部署別の経費集計レポートを自動化する方法について詳しく解説します。具体的なコード例、その詳細解説、そして実践的な応用例を含めて説明します。初心者でもステップバイステップで実践できるようにガイドします。

基本の経費集計レポート作成

部署別の経費集計は、多くの企業で必要とされる業務の一つです。手動での作業は煩雑で時間がかかるため、VBAを使用して自動化することで効率的に作業を進めることが可能です。

Sub DepartmentalExpenseReport()
    ' データの最終行を取得
    Dim LastRow As Long
    LastRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row

    ' 経費を部署別に合計
    ThisWorkbook.Sheets("Sheet1").Range("C1").Value = "経費合計"
    ThisWorkbook.Sheets("Sheet1").Range("C2:C" & LastRow).FormulaR1C1 = "=SUMIF(R2C1:R" & LastRow & "C1, RC1, R2C2:R" & LastRow & "C2)"
End Sub

コード解説

上記のコードでは、まずSheet1のA列の最終行を取得しています。これにより、動的なデータの範囲を確認できます。次に、部署別の経費をC列に合計して表示しています。SUMIF関数を使用して、A列の部署ごとにB列の経費を合計しています。

応用例

1. 部署別の平均経費を計算

多くの場面で、部署別の経費の合計だけでなく、平均も知りたい場合があります。

Sub AverageDepartmentalExpense()
    ' データの最終行を取得
    Dim LastRow As Long
    LastRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row

    ' 経費を部署別に平均
    ThisWorkbook.Sheets("Sheet1").Range("D1").Value = "経費平均"
    ThisWorkbook.Sheets("Sheet1").Range("D2:D" & LastRow).FormulaR1C1 = "=AVERAGEIF(R2C1:R" & LastRow & "C1, RC1, R2C2:R" & LastRow & "C2)"
End Sub

2. 部署別の経費でグラフを作成

視覚的に部署別の経費を比較するために、グラフを作成します。

Sub CreateGraphForDepartmentalExpense()
    Dim LastRow As Long
    LastRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row

    ' グラフのデータ範囲を設定
    Dim GraphRange As Range
    Set GraphRange = ThisWorkbook.Sheets("Sheet1").Range("A1:C" & LastRow)

    ' グラフを作成
    ThisWorkbook.Sheets("Sheet1").Shapes.AddChart2(251, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=GraphRange
End Sub

3. 部署別の経費上位3つをハイライト

経費が多い部署を特定するために、上位3つの部署の経費をハイライトします。

Sub HighlightTop3Expenses()
    Dim LastRow As Long
    LastRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row

    ' 経費の列をソート
    ThisWorkbook.Sheets("Sheet1").Range("A1:C" & LastRow).Sort Key1:=Range("C1"), Order1:=xlDescending, Header:=xlYes

    ' 上位3つの経費をハイライト
    ThisWorkbook.Sheets("Sheet1").Range("C2:C4").Interior.Color = RGB(255, 0, 0)
End Sub

まとめ

Excel VBAを利用することで、部署別の経費集計レポートを効率的に作成することができます。基本の集計方法から応用例まで、さまざまなニーズに対応する方法を学ぶことができました。これを機に、VBAの技術をさらに磨いて、業務の自動化を進めてみてはいかがでしょうか。