Thema: Makro Summewenn

Option Explicit

Public Sub SummeWenn()
Dim lZeile  As Long
lZeile = 10
Dim rBereich_1 As Range
Dim rBereich_2 As Range
Set rBereich_1 = Tabelle2.Range("B1:B20")
Set rBereich_2 = Range("A1:A20")
Range("C3:F6") = Application.WorksheetFunction.SumIf(rBereich_1, _
Range("C" & lZeile).Value, rBereich_2)
End Sub

Hallo, ich habe dieses Makro erstellt. Mir fehlen aber leider noch ein paar Variablen bzw. eine Schleife. Das Makro berücksichtigt nur das Suchkriterium in der Zelle C10, dies Zeile soll aber "mitwachsen" so als würde man die Formel in Excel "herunterziehen". kann mir jemand helfen?

Hallo Theo,
in deiner Fragestellung gibt es noch ein paar Lücken:

1.
Sind alle Tabellenbereich auf dem Blatt Tabelle2 ?
Oder ist nur rBereich_1 auf Tabelle2, die anderen Bereiche aber woanders? Wenn ja - wo.

2.
In Spalte C ändert sich der Bereich mit den Kriterien von C10 bis Cxx.
Das Makro soll also entsprechend der letzten Zeile mit Daten eine Schleife entsprechend oft durchlaufen und dabei das Kriterium in der Berechnung ändern.
In welche Zellen soll das Makro die Ergebnisse schreiben? Range("C3:F6") macht irgendwie keinen Sinn.

Nachfolgend mal ein angepasstest Grundgerüst.
Gruß
Franz

Public Sub SummeWenn()
Dim lZeile  As Long, Zeile_L As Long
Dim wks As Worksheet
Dim rBereich_1 As Range
Dim rBereich_2 As Range
Set wks = ActiveSheet 'oder Blatt fest vorgeben
With wks
Set rBereich_1 = Tabelle2.Range("B1:B20")
Set rBereich_2 = .Range("A1:A20")
'Letzte Zeile in Spalte C
Zeile_L = .Cells(.Rows.Count, 3).End(xlUp).Row
'Werte in Spalte C abarbeiten
For lZeile = 10 To Zeile_L
?.Range("C3:F6")? = Application.WorksheetFunction.SumIf(rBereich_1, _
.Cells(lZeile, 3).Value, rBereich_2)
Next lZeile
End With
End Sub

Hallo Franz, danke für deine Antwort.
Bereich1 ist in Tabelle2 und Bereich1 ist auf dem activesheet.
Stimmt, Ergebnisbereich c3:f6 macht kein Sinn. Das sind halt die Ergebniszellen. Aber dann muss ich warscheinlich für jede Spalte (c3:c6, d3:d6, e3:e6,...) eine eigene Formel schreiben oder?

Hallo Theo,
es ist und bleibt verwirrend.
Der einfachste und bei Standard-Formln oft der schnellst ist der, die Berechnungsformeln per VBA-Makro einzufügen und dann durch ihre Ergebnisse zu ersetzen. In den Formeltext muss man dann den Variablen Teil einbaue.
Damit es klarer wird solltest du hier eine Beispieldatei hochladen mit den gewünschten SUMMEWENN-Formeln.
Gruß
Franz

https://www.herber.de/bbs/user/101530.xlsx
Hier einmal die Datei. Die Formel sumif im Makro muss ich dann durch sumifs ersetzten...
Die Beispieldatei erhält nur 2 Monate und ein paar Zeilen. Die echte Datei geht über 12 Monate und eine Übersicht mit jeweils 30.000 Zeilen und 30 Spalten. Daher dauert die Berechnung über Formeln extrem lange, weshalb ich die Werte gerne per Makro in die Zeilen schreiben möchte

Hallo Theo,
bei ca. 900000 Zellen, die auf diese Weise berechnet werden sollen, wirst du mit einem Makro, das die SUMMEWENNS-Funktion verwendet, auch nicht wirklich glücklich werden. Unter VBA sind die vergleichbaren Tabellenfunktionen nämlich langsamer als in einem Tabellenblatt. Wenn jede Zelle nur 1/100 Sekunden erfordert, dann benötigt das Makro noch 9000 sekunden als rund 2,5 stunden.
Im Tabellenblatt führt die Menge an Formeln zum Problem, weil bei diesen Matrixformeln extreme Datenmengen temporär ausgelagert werden müssen - mit Glück in den Arbeitsspeicher, mit Pech auf die Festplatte. Aber Excel bekommt dabei extreme Verdauungsschwierigkeiten.
Wieviele Datenzeilen sind denn in den Monatsblättern?
Wenn du in der Übersicht 30000 Zeilen hast, dann müssen dort ja auch jeweils mehrere Tausend oder Zehntausend Zeilen vorhanden sein.
Die Monatsdaten müssen ja aus einer Datenquelle generiert worden sein.
Gibt es dort keine Möglichkeit das ein Datenexport generiert wird, der die Daten über das Gesamtjahr nach Verkäufer und Produkt aufsummiert?
Bei den Daten in deiner Tabelle sehe ich jetzt 2 grundsätzliche Wege weiterzukommen:
A: Daten-Arrays
Hier werden alle Daten in Datenarrays verarbeitet, wobei 30000 Zeilen mal 32 Spalten schon eine Herausforderung sind - allerdings muss man nicht unbedingt alle 30000 Zeilen in einem Array verarbeiten, sondern kann in Blöcken arbeiten. In verschachtelten For-Next-Schleifen werden dabei die 12 Monate abgearbeitet, die Verkäufer und Produkte verglichen, bei Übereinstimmung die Werte aus der Zeile im Monat zu der entsprechenden Zeile in der Übersicht addiert.
Zum Schluß werden dann die Ergebnisse aus dem Daten-Array in die Übersicht übertragen.
B: Auslagerung der Monatsdaten in eine externe Datei und Aufbereitung via Pivot-Tabellenbericht
In der externen Datei ist schon eine Pivotauswertung vorbereitet, die die Daten eines Monats nach Verkäufer und Produkt aufsummiert, so dass pro Kombination von Verkäufer und Produkt nur noch eine Datenzeile existiert. Die Ergebnis-Daten jedes Monats werden jeweils in eine Jahres-Tabelle kopiert.
Nachdem alle Monate in die Jahres-Tabelle übertragen sind wird die Pivot-Auswertung auf diese Jahres-Werte angewendet. So hat man in der Liste für jede Kombination von Verkäufer und Produkt nur noch eine Datenzeile, was dann schon deinem gewünschten Ergebnis entspricht. Jetzt kann man die Daten falls erforderlich noch in die gewünschte Reihenfolge bringen.
Auch diese Lösung kann man per Makro weitestgehend automatisieren, ist aber auch manuell schnell umgesetzt, wenn man die Basis erst mal angelegt hat. Pivot-Berichte haben den Vorteil, dass man hier auf die schnellen Daten-Bankfunktionen zurückgreifen kann, die auf die in einem separaten Datenspeicherbereich abgelegten Quelldaten zugreifen.
Basisdatei für Auswertung via Pivotbericht:
https://www.herber.de/bbs/user/101536.xlsm
Gruß
Franz

Hallo Franz,
vielen Dank für deine ausführliche Antwort.
Die Monatsblätter haben jeweils ca. 30.000 Zeilen und 30 Spalten, die mit Werten gefüllt sind. In der Gesamtübersicht werden dann die jeweiligen Monate per =summewenns zusammenaddiert, ebenfalls ca. 900.000 Zellen. Diese Berechnung in der Gesamtübersicht dauert so ca. 20 Stunden.
Die Monatsdaten werden aus einem Programm generiert. Dies für das Gesamtjahr zu erstellen, würde noch länger als 20 Stunden dauern.
Die Lösung über Pivot hatte ich auch schon im Hinterkopf, funktioniert leider nicht, da ich dann ziemlich lange damit beschäftigt wäre, die gewünschte Formatierung einzuhalten.
Eine VBA-Lösung die 2,5 Stunden rechnet wäre ein echter Fortschritt...

Public Sub SummeWenn()
Dim lZeile  As Long, Zeile_L As Long
Dim wks As Worksheet
Dim rBereich_1 As Range
Dim rBereich_2 As Range
Dim rBereich_3 As Range
Set wks = ActiveSheet 'oder Blatt fest vorgeben
With wks
Set rBereich_1 = Tabelle2.Range("B:B")
Set rBereich_2 = Tabelle2.Range("C:C")
Set rBereich_3 = Tabelle2.Range("A:A")
'Letzte Zeile in Spalte C
Zeile_L = Tabelle2.Cells(.Rows.Count, 3).End(xlUp).Row
'Werte in Spalte C abarbeiten
For lZeile = 10 To Zeile_L
.Cells(lZeile, 3) = Application.WorksheetFunction.IF(Range("B:B") = """", """", _
SumIfs(rBereich_2,
rBereich_3, .Cells(lZeile, 1).Value, rBereich_1, .Cells(lZeile, 2)))
Next lZeile
End With
End Sub

Das Makro wird nicht ausgeführt, der Debugger zeigt einen Fehler bei SumIfs. Kann mir jemand ein Tipp geben?

Hallo Theo,
ich hab jetzt mal etliches probiert.
Die Kombination aus Vorauswertung per Pivot-Tabellenberichten und die Aufbereitung/Übertragung der Ergebnisse via Datenarray scheint der schnellste Weg zu.
Hier reden wir dann nur noch von Minuten, nicht Stunden für eine Auswertung über jeweils ca. 30000 Zeilen in Blatt "Gesamt" und in den Monatsblättern.
Die Formel-Lösungen kannst du direkt knicken, da muss Excel einfach zu viele Daten jonglieren.
Ich hab in der ZIP-Datei mal alles zusammengestellt, was ich so probiert hab. Unter anderem in der Datei "...Versuche.xlsm" auch die von dir angedachte Lösung mit VBA-internen SumIFs.
https://www.herber.de/bbs/user/101628.zip
Die meiste Zeit geht dabei für die Übertrag der Ergebnisse aus der Pivot-Auswertung in das Blatt "Gesamt" drauf.
Wenn du mit einer von der Pivot-Auswertung im Blatt "PivotJahr" erzugten Sortierung leben könntest, dann könnte man das Ganze nochmals enorm beschleunigen.
Gruß
Franz

Option Explicit

Public Sub SummeWenn()
Dim lZeile  As Long
lZeile = 10
Dim rBereich_1 As Range
Dim rBereich_2 As Range
Set rBereich_1 = Tabelle2.Range("B1:B20")
Set rBereich_2 = Range("A1:A20")
Range("C3:F6") = Application.WorksheetFunction.SumIf(rBereich_1, _
Range("C" & lZeile).Value, rBereich_2)
End Sub

Hallo, ich habe dieses Makro erstellt. Mir fehlen aber leider noch ein paar Variablen bzw. eine Schleife. Das Makro berücksichtigt nur das Suchkriterium in der Zelle C10, dies Zeile soll aber "mitwachsen" so als würde man die Formel in Excel "herunterziehen". kann mir jemand helfen?

Hallo Theo,
in deiner Fragestellung gibt es noch ein paar Lücken:

1.
Sind alle Tabellenbereich auf dem Blatt Tabelle2 ?
Oder ist nur rBereich_1 auf Tabelle2, die anderen Bereiche aber woanders? Wenn ja - wo.

2.
In Spalte C ändert sich der Bereich mit den Kriterien von C10 bis Cxx.
Das Makro soll also entsprechend der letzten Zeile mit Daten eine Schleife entsprechend oft durchlaufen und dabei das Kriterium in der Berechnung ändern.
In welche Zellen soll das Makro die Ergebnisse schreiben? Range("C3:F6") macht irgendwie keinen Sinn.

Nachfolgend mal ein angepasstest Grundgerüst.
Gruß
Franz

Public Sub SummeWenn()
Dim lZeile  As Long, Zeile_L As Long
Dim wks As Worksheet
Dim rBereich_1 As Range
Dim rBereich_2 As Range
Set wks = ActiveSheet 'oder Blatt fest vorgeben
With wks
Set rBereich_1 = Tabelle2.Range("B1:B20")
Set rBereich_2 = .Range("A1:A20")
'Letzte Zeile in Spalte C
Zeile_L = .Cells(.Rows.Count, 3).End(xlUp).Row
'Werte in Spalte C abarbeiten
For lZeile = 10 To Zeile_L
?.Range("C3:F6")? = Application.WorksheetFunction.SumIf(rBereich_1, _
.Cells(lZeile, 3).Value, rBereich_2)
Next lZeile
End With
End Sub

Hallo Franz, danke für deine Antwort.
Bereich1 ist in Tabelle2 und Bereich1 ist auf dem activesheet.
Stimmt, Ergebnisbereich c3:f6 macht kein Sinn. Das sind halt die Ergebniszellen. Aber dann muss ich warscheinlich für jede Spalte (c3:c6, d3:d6, e3:e6,...) eine eigene Formel schreiben oder?

Hallo Theo,
es ist und bleibt verwirrend.
Der einfachste und bei Standard-Formln oft der schnellst ist der, die Berechnungsformeln per VBA-Makro einzufügen und dann durch ihre Ergebnisse zu ersetzen. In den Formeltext muss man dann den Variablen Teil einbaue.
Damit es klarer wird solltest du hier eine Beispieldatei hochladen mit den gewünschten SUMMEWENN-Formeln.
Gruß
Franz

https://www.herber.de/bbs/user/101530.xlsx
Hier einmal die Datei. Die Formel sumif im Makro muss ich dann durch sumifs ersetzten...
Die Beispieldatei erhält nur 2 Monate und ein paar Zeilen. Die echte Datei geht über 12 Monate und eine Übersicht mit jeweils 30.000 Zeilen und 30 Spalten. Daher dauert die Berechnung über Formeln extrem lange, weshalb ich die Werte gerne per Makro in die Zeilen schreiben möchte

Hallo Theo,
bei ca. 900000 Zellen, die auf diese Weise berechnet werden sollen, wirst du mit einem Makro, das die SUMMEWENNS-Funktion verwendet, auch nicht wirklich glücklich werden. Unter VBA sind die vergleichbaren Tabellenfunktionen nämlich langsamer als in einem Tabellenblatt. Wenn jede Zelle nur 1/100 Sekunden erfordert, dann benötigt das Makro noch 9000 sekunden als rund 2,5 stunden.
Im Tabellenblatt führt die Menge an Formeln zum Problem, weil bei diesen Matrixformeln extreme Datenmengen temporär ausgelagert werden müssen - mit Glück in den Arbeitsspeicher, mit Pech auf die Festplatte. Aber Excel bekommt dabei extreme Verdauungsschwierigkeiten.
Wieviele Datenzeilen sind denn in den Monatsblättern?
Wenn du in der Übersicht 30000 Zeilen hast, dann müssen dort ja auch jeweils mehrere Tausend oder Zehntausend Zeilen vorhanden sein.
Die Monatsdaten müssen ja aus einer Datenquelle generiert worden sein.
Gibt es dort keine Möglichkeit das ein Datenexport generiert wird, der die Daten über das Gesamtjahr nach Verkäufer und Produkt aufsummiert?
Bei den Daten in deiner Tabelle sehe ich jetzt 2 grundsätzliche Wege weiterzukommen:
A: Daten-Arrays
Hier werden alle Daten in Datenarrays verarbeitet, wobei 30000 Zeilen mal 32 Spalten schon eine Herausforderung sind - allerdings muss man nicht unbedingt alle 30000 Zeilen in einem Array verarbeiten, sondern kann in Blöcken arbeiten. In verschachtelten For-Next-Schleifen werden dabei die 12 Monate abgearbeitet, die Verkäufer und Produkte verglichen, bei Übereinstimmung die Werte aus der Zeile im Monat zu der entsprechenden Zeile in der Übersicht addiert.
Zum Schluß werden dann die Ergebnisse aus dem Daten-Array in die Übersicht übertragen.
B: Auslagerung der Monatsdaten in eine externe Datei und Aufbereitung via Pivot-Tabellenbericht
In der externen Datei ist schon eine Pivotauswertung vorbereitet, die die Daten eines Monats nach Verkäufer und Produkt aufsummiert, so dass pro Kombination von Verkäufer und Produkt nur noch eine Datenzeile existiert. Die Ergebnis-Daten jedes Monats werden jeweils in eine Jahres-Tabelle kopiert.
Nachdem alle Monate in die Jahres-Tabelle übertragen sind wird die Pivot-Auswertung auf diese Jahres-Werte angewendet. So hat man in der Liste für jede Kombination von Verkäufer und Produkt nur noch eine Datenzeile, was dann schon deinem gewünschten Ergebnis entspricht. Jetzt kann man die Daten falls erforderlich noch in die gewünschte Reihenfolge bringen.
Auch diese Lösung kann man per Makro weitestgehend automatisieren, ist aber auch manuell schnell umgesetzt, wenn man die Basis erst mal angelegt hat. Pivot-Berichte haben den Vorteil, dass man hier auf die schnellen Daten-Bankfunktionen zurückgreifen kann, die auf die in einem separaten Datenspeicherbereich abgelegten Quelldaten zugreifen.
Basisdatei für Auswertung via Pivotbericht:
https://www.herber.de/bbs/user/101536.xlsm
Gruß
Franz

Hallo Franz,
vielen Dank für deine ausführliche Antwort.
Die Monatsblätter haben jeweils ca. 30.000 Zeilen und 30 Spalten, die mit Werten gefüllt sind. In der Gesamtübersicht werden dann die jeweiligen Monate per =summewenns zusammenaddiert, ebenfalls ca. 900.000 Zellen. Diese Berechnung in der Gesamtübersicht dauert so ca. 20 Stunden.
Die Monatsdaten werden aus einem Programm generiert. Dies für das Gesamtjahr zu erstellen, würde noch länger als 20 Stunden dauern.
Die Lösung über Pivot hatte ich auch schon im Hinterkopf, funktioniert leider nicht, da ich dann ziemlich lange damit beschäftigt wäre, die gewünschte Formatierung einzuhalten.
Eine VBA-Lösung die 2,5 Stunden rechnet wäre ein echter Fortschritt...

Public Sub SummeWenn()
Dim lZeile  As Long, Zeile_L As Long
Dim wks As Worksheet
Dim rBereich_1 As Range
Dim rBereich_2 As Range
Dim rBereich_3 As Range
Set wks = ActiveSheet 'oder Blatt fest vorgeben
With wks
Set rBereich_1 = Tabelle2.Range("B:B")
Set rBereich_2 = Tabelle2.Range("C:C")
Set rBereich_3 = Tabelle2.Range("A:A")
'Letzte Zeile in Spalte C
Zeile_L = Tabelle2.Cells(.Rows.Count, 3).End(xlUp).Row
'Werte in Spalte C abarbeiten
For lZeile = 10 To Zeile_L
.Cells(lZeile, 3) = Application.WorksheetFunction.IF(Range("B:B") = """", """", _
SumIfs(rBereich_2,
rBereich_3, .Cells(lZeile, 1).Value, rBereich_1, .Cells(lZeile, 2)))
Next lZeile
End With
End Sub

Das Makro wird nicht ausgeführt, der Debugger zeigt einen Fehler bei SumIfs. Kann mir jemand ein Tipp geben?

Hallo Theo,
ich hab jetzt mal etliches probiert.
Die Kombination aus Vorauswertung per Pivot-Tabellenberichten und die Aufbereitung/Übertragung der Ergebnisse via Datenarray scheint der schnellste Weg zu.
Hier reden wir dann nur noch von Minuten, nicht Stunden für eine Auswertung über jeweils ca. 30000 Zeilen in Blatt "Gesamt" und in den Monatsblättern.
Die Formel-Lösungen kannst du direkt knicken, da muss Excel einfach zu viele Daten jonglieren.
Ich hab in der ZIP-Datei mal alles zusammengestellt, was ich so probiert hab. Unter anderem in der Datei "...Versuche.xlsm" auch die von dir angedachte Lösung mit VBA-internen SumIFs.
https://www.herber.de/bbs/user/101628.zip
Die meiste Zeit geht dabei für die Übertrag der Ergebnisse aus der Pivot-Auswertung in das Blatt "Gesamt" drauf.
Wenn du mit einer von der Pivot-Auswertung im Blatt "PivotJahr" erzugten Sortierung leben könntest, dann könnte man das Ganze nochmals enorm beschleunigen.
Gruß
Franz