Poniżej przedstawiam konkretny przykład kodu VBA, który automatyzuje konsolidację danych oraz generowanie raportu wariancji między danymi rzeczywistymi a budżetowymi. Załóżmy, że w każdej zakładce (poza raportem) dane zaczynają się od wiersza 2, a kolumna B zawiera wartości rzeczywiste, natomiast kolumna C – dane budżetowe.
Kod VBA:
Sub GenerateVarianceReport()
Dim ws As Worksheet
Dim wsReport As Worksheet
Dim LastRow As Long
Dim i As Long
Dim TotalActual As Double
Dim TotalBudget As Double
Dim variance As Double
Dim ReportRow As Long
’ Usuń arkusz raportu, jeśli już istnieje
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(„VarianceReport”).Delete
Application.DisplayAlerts = True
On Error GoTo 0
’ Utwórz nowy arkusz raportu
Set wsReport = Worksheets.Add
wsReport.Name = „VarianceReport”
’ Ustaw nagłówki
wsReport.Range(„A1”).Value = „Nazwa arkusza”
wsReport.Range(„B1”).Value = „Suma wartości rzeczywistych”
wsReport.Range(„C1”).Value = „Suma budżetu”
wsReport.Range(„D1”).Value = „Wariancja (Rzeczywiste – Budżet)”
ReportRow = 2 ’ Pierwszy wiersz danych w raporcie
’ Przejdź przez wszystkie arkusze, oprócz arkusza raportu
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> „VarianceReport” Then
’ Znajdź ostatni wiersz z danymi w kolumnie A
LastRow = ws.Cells(ws.Rows.Count, „A”).End(xlUp).Row
’ Sumuj dane z kolumny B (Rzeczywiste) oraz kolumny C (Budżet)
TotalActual = Application.WorksheetFunction.Sum(ws.Range(„B2:B” & LastRow))
TotalBudget = Application.WorksheetFunction.Sum(ws.Range(„C2:C” & LastRow))
variance = TotalActual – TotalBudget
’ Zapisz wyniki w arkuszu raportu
wsReport.Cells(ReportRow, 1).Value = ws.Name
wsReport.Cells(ReportRow, 2).Value = TotalActual
wsReport.Cells(ReportRow, 3).Value = TotalBudget
wsReport.Cells(ReportRow, 4).Value = variance
ReportRow = ReportRow + 1
End If
Next ws
’ Dodaj sumy końcowe
wsReport.Cells(ReportRow, 1).Value = „TOTAL”
wsReport.Cells(ReportRow, 2).Value = Application.WorksheetFunction.Sum(wsReport.Range(„B2:B” & ReportRow – 1))
wsReport.Cells(ReportRow, 3).Value = Application.WorksheetFunction.Sum(wsReport.Range(„C2:C” & ReportRow – 1))
wsReport.Cells(ReportRow, 4).Value = Application.WorksheetFunction.Sum(wsReport.Range(„D2:D” & ReportRow – 1))
MsgBox „Raport wariancji został wygenerowany w arkuszu 'VarianceReport’.”
End Sub
Wyjaśnienie:
Kod ten tworzy nowy arkusz o nazwie “VarianceReport”, usuwa go, jeśli już istnieje, a następnie przechodzi przez wszystkie inne arkusze w skoroszycie. Dla każdego arkusza sumuje wartości z kolumny B (wartości rzeczywiste) oraz z kolumny C (wartości budżetowe), oblicza wariancję (różnicę między wartościami rzeczywistymi a budżetowymi) i zapisuje wyniki w nowym arkuszu. Na końcu dodawane są sumy końcowe dla wszystkich arkuszy. Taki raport może być użyteczny do analizy odchyleń między planowanymi a rzeczywistymi wynikami, co jest kluczowe przy monitorowaniu efektywności operacyjnej.
