Makro Summewenn

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

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

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 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

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

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

Beliebteste Forumthreads (12 Monate)

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread