VBA für gleitende Zwischensummen in Excel
Schritt-für-Schritt-Anleitung
Um eine Zwischensumme in Excel mit VBA zu berechnen und unter jede bestehende Summe einzufügen, kannst du folgendes Makro nutzen:
- Öffne Excel und drücke
ALT + F11, um den VBA-Editor zu öffnen.
- Wähle das gewünschte Arbeitsblatt aus und füge ein neues Modul ein (
Einfügen > Modul).
- Kopiere und füge den folgenden Code in das Modul ein:
Sub Zwischensummen()
Dim wks As Worksheet
Dim i As Long, Zeile_L As Long, Zeile_1 As Long
Dim Spalte_1 As Long, Spalte_L As Long
Dim strA As String
Set wks = ActiveSheet
Zeile_1 = 1 '1 Zeile die in den Formeln summiert werden soll
Spalte_1 = 2 'Spalte in der 1. Formeln eingetragen werden sollen
With wks
'letzte Daten-Zeile
Zeile_L = .Cells(.Rows.Count, 1).End(xlUp).Row
'letzte Daten-Spalte
Spalte_L = .Cells(Zeile_L, .Columns.Count).End(xlToLeft).Column
For i = 1 To 4
'Einfügezeile und Text in Spalte A festlegen
Select Case i
Case 1: Zeile_L = Zeile_L + 1: strA = "ENDSUMME"
Case 2: Zeile_L = Zeile_L - 3: strA = "ZWISCHENSUMME"
Case 3: Zeile_L = Zeile_L - 4: strA = "ZWISCHENSUMME"
Case 4: Zeile_L = Zeile_L - 3: strA = "ZWISCHENSUMME"
End Select
'leerzeile einfügen
.Rows(Zeile_L).Insert Shift:=xlShiftDown
'Eintrag in Spalte A
.Cells(Zeile_L, 1) = strA
'Formeln in Leerzeile einfügen
.Range(.Cells(Zeile_L, Spalte_1), .Cells(Zeile_L, Spalte_L)).FormulaR1C1 = _
"=SUBTOTAL(9,R" & Zeile_1 & "C:R[-1]C)-SUMIF(R" & Zeile_1 & "C1:R[-1]C1,""SUMME"",R" & _
Zeile_1 & "C:R[-1]C)"
Next i
End With
End Sub
- Schließe den VBA-Editor und führe das Makro aus (
ALT + F8 und wähle Zwischensummen aus).
Häufige Fehler und Lösungen
- Fehler: Falsche Spalte für SUMMEWENN
Wenn das Makro die Werte nicht korrekt summiert, kann es daran liegen, dass die Spalte in der SUMMEWENN-Formel nicht korrekt eingestellt ist. Ändere den Code wie folgt, um sicherzustellen, dass die richtige Spalte verwendet wird:
SUMIF(R" & Zeile_1 & "C2:R[-1]C2,""SUMME"",R" & Zeile_1 & "C:R[-1]C)
- Fehler: Keine Leerzeilen eingefügt
Stelle sicher, dass das Makro im richtigen Arbeitsblatt ausgeführt wird und dass die Struktur der Tabelle dem Beispiel entspricht.
Alternative Methoden
Falls du keine VBA-Lösungen verwenden möchtest, kannst du auch die Excel-Funktion TEILERGEBNIS nutzen, um Zwischensummen manuell zu berechnen. Hier ein Beispiel, wie du die Excel Zwischensumme einfügen kannst:
- Füge eine neue Zeile unter der gewünschten Summe ein.
- Nutze die folgende Formel in der neuen Zeile:
=TEILERGEBNIS(9;B$1:B16) - SUMMEWENN($A$1:$A16;"SUMME";B$1:B16)
- Kopiere die Formel nach unten, um die Zwischensummen für andere Datenblöcke zu bilden.
Praktische Beispiele
Angenommen, deine Tabelle hat die folgenden Daten:
A B
1 A 1
2 A 1
3 A 1
4 SUMME 3
5 B 1
6 B 1
7 SUMME 2
Nach Ausführung des VBA-Skripts solltest du die Zwischensummen wie folgt sehen:
A B
1 A 1
2 A 1
3 A 1
4 SUMME 3
5 ZWISCHENSUMME 3
6 B 1
7 B 1
8 SUMME 2
9 ZWISCHENSUMME 5
Tipps für Profis
- Nutze den
SUBTOTAL-Funktionstyp 9, um die Zwischensummen dynamisch zu berechnen, während du Daten in deiner Tabelle änderst.
- Experimentiere mit der
SUMIF-Funktion, um spezifische Bedingungen für deine Summen zu setzen.
- Speichere deine Makros in einer persönlichen Makroarbeitsmappe, um sie in verschiedenen Excel-Dateien verwenden zu können.
FAQ: Häufige Fragen
1. Kann ich die Zwischensumme automatisch aktualisieren?
Ja, wenn du die TEILERGEBNIS-Funktion verwendest, wird die Zwischensumme automatisch aktualisiert, wenn du neue Daten hinzufügst oder bestehende Daten änderst.
2. Funktioniert das Makro in allen Excel-Versionen?
Das bereitgestellte Makro sollte in den meisten modernen Versionen von Excel funktionieren, ab Excel 2010 und höher. Achte darauf, dass du die Makros aktiviert hast.
3. Was, wenn ich mehr als 4 Gruppen von Daten habe?
Du kannst die Schleife im VBA-Code anpassen, um mehr Gruppen zu berücksichtigen, indem du den Wert in For i = 1 To 4 erhöhst.