Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA - SumIf(s) vereinfachen wegen performence

VBA - SumIf(s) vereinfachen wegen performence
27.03.2018 12:56:13
Andreas
Hallo liebes Forum,
ich habe eine VBA-Herausforderung die ich im Moment nicht lösen kann und die mein Makro ziemlich langsam macht.
Durch den Durchlauf mit Haltpunkten habe ich herausgefunden, dass folgende Codes mein Makro sehr langsamm macht.
...
For i = 1 To d + 1
For z = 5 To b
wrksht.Cells(z, a + i - 1).Value = WorksheetFunction.SumIfs(objVolumenBereich.Offset(0, i - 1), objKriterienBereich.Offset(0, e - 1), wrksht.Cells(z, 1).Value, _
objKriterienBereich.Offset(0, f - 1), wrksht.Cells(z, 2).Value)
Next z
wrksht.Cells(3, a - 1 + i).Value = WorksheetFunction.Sum(Range(Cells(5, a - 1 + i), Cells(b, a - 1 + i)))
Next i
und
For i = 1 To 7
For z = 5 To b
wrksht.Cells(z, 24 + i).Value = wrksht.Cells(z, 3 + i).Value / wrksht.Cells(3, 3 + i).Value
Next z
Next i
Gibt es eine Möglichkeit dies effizienter zu machen? Problem ist wohl auch das die Basistabelle über 40.000 Zeilen hat.
Meine Idee wäre das die "i"-Schleife nicht komplett notwendig ist. Also ich mache die Suche in den Kriterienbereichen nur einmal und summiere dann alle sieben Spalten gemäß Kriterien. Im Moment suche ich für jedes i wieder die Kriterien.
Jemand eine Idee.
Vielen Dank im Voraus.
Andreas

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Pivot als schnellste bedingte Summierung ...
27.03.2018 13:19:20
lupo1
... mit Hilfsspalte, die die Kriterien (1 oder 0) verarbeitet.
Dann braucht VBA nur noch auf die Pivot-Ergebnisse zuzugreifen.
Die PT kannst Du dann wieder löschen (da sie beim Speichern Platz belegt).
AW: Pivot als schnellste bedingte Summierung ...
27.03.2018 13:52:12
Andreas
Danke für deine schnelle Antwort. Da habe ich wohl leider vergessen zu erwähnen, dass die Datentabelle eine Pivot-Tabelle ist. Diese wird dann z.B. über Datenschnitte gefilter und im Anschluss sollen die Werte gemäß den Kriterien neu berechnet werden. Kriterien kann man auch über VBA ändern bzw. auswählen.
Verzicht auf D-Schnitte ggü Daten-Hilfsspalte ...
27.03.2018 14:39:06
lupo1
... erlaubt Dir Vorgehen in einem Rutsch.
Die PT wird dann nur einmal aufgebaut und daraus alles kopiert oder übertragen.
Anzeige
AW: Verzicht auf D-Schnitte ggü Daten-Hilfsspalte ...
27.03.2018 15:03:56
Andreas
Wie aber kann ich dann die Pivotdaten filtern. Im Moment filtere ich in mehreren Tabellenblättern über die Datenschnitte und nicht im Pivotblatt.
Ich kenne Deine Anwendung nicht, sehe aber, ...
27.03.2018 15:10:41
lupo1
... dass Du mehrfach SUMIFS berechnest in einer Schleife.
Daher ging ich - möglicherweise jedoch völlig irrig - davon aus, dass diese SUMIF's sich nicht überschneiden.
Nur in dem Falle gilt das von mir Gesagte.
Sind die Kriterien jedoch komplex und führen zu Ergebnisüberschneidungen bei der Formel, klappt mein Vorschlag nicht.
AW: Ich kenne Deine Anwendung nicht, sehe aber, ...
27.03.2018 17:19:14
Andreas
Hallo Lupo1,
danke dir für den Rat. Ich habe es mit den Tipp von Robert hinbekommen.
AW: VBA - SumIf(s) vereinfachen wegen performence
27.03.2018 14:33:34
Daniel
Hi
wenn du für alle Zellen eine gleichartige Bearbeitung vornimmst, die im Prinzip auch durch eine für alle Zellen gleiche Formel durchgeführt werden könnte, dann ist es besser, alle Zellen in einem Schritt mit dieser Formel zu füllen und hinterher die Formel durch ihren Wert zu ersetzen, als jede Zelle einzeln zu bearbeiten..
sieht im Prinzip so aus:
with wrksht.Cells(5, a).Resize(z - 5 + 1, d)
.FormulaR1C1 = "=SumIfs(...)"
.Formula = value
End with
Gruß Daniel
Anzeige
AW: VBA - SumIf(s) vereinfachen wegen performence
27.03.2018 15:00:57
Andreas
Hallo Daniel,
danke für dein Tipp.
Da bekomme ich doch aber Probleme mit meinen variablen Kriterienbereichen. Diese bestimme ich ja im Moment über die Variablen e und f.
Dann würde die Formel ja auch recht kompliziert (Bereich.Verschieben)und langsam werden, oder?
AW: VBA - SumIf(s) vereinfachen wegen performence
27.03.2018 15:23:50
Daniel
nope, in diesem Fall nicht.
Bereich.Verschieben ist nicht langsam, sondern sorgt durch seine Volatilität zu einer eigentlich unnötigen Anzahl von Neuberechnungen der Formeln.
Bei Formeln, die permanent in der Tabelle stehen, führt das zu einer Verlangsamung.
Hier aber ersetzt du die Formeln sofort durch ihre Werte und dann spielt das keine Rolle.
Außerdem lässt sich Bereich.Verschieben in den meisten Fällen ersetzen.
des weiteren wäre zu prüfen, ob du wirklich eine Berechnung der Spalten benötigst, oder ob es nicht einfach nur ein relativer Zellbezug ist, den du problemlos in der Formel darstellen kannst.
Gruß Daniel
Anzeige
AW: VBA - SumIf(s) vereinfachen wegen performence
27.03.2018 15:11:15
Robert
Hallo Andreas,
schneller wird das ganze, wenn Du die Berechnung nicht für jede Zelle einzeln machst, sondern für den kompletten Bereich auf einen Rutsch. Dazu in die Tabelle in dem Bereich eine Formel einfügen, die Dein gewünschtes Ergebnis liefert und anschließend die Formeln durch die Werte ersetzen.
Ich habe mal versucht, Deinen 2. Codeschnipsel entsprechend zu ändern. Ich kann nicht dafür garantieren, dass ich den richtigen Tabellenbereich und die richtige Formel raus gelesen habe. Nehmen wir mal an, die Variable b hätte den Wert 1000, dann müsste der Codeschnipsel Daten in den Bereich Y5:AE1000 schreiben. Für die Zelle Y5 habe ich hätte ich als Formel =D5/D$3 rausgelesen.
With wrksht.Range(wrksht.Cells(5, 24 + 1), wrksht.Cells(b, 24 + 7))
.Formula = "=" & Cells(5, 4).Address(False, False) & "/" & Cells(3, 4).Address(True, False)
.Value = .Value
End With

Wenn dies richtig sein sollte, ergibt sich bei meiner Kiste hier ein Dauer von 74,25 Sekunden für Deinen Code und eine Dauer von 0,14 Sekunden für meinen. Dasselbe kannst Du dann für Deinen ersten Codeschnipsel mal ausprobieren.
Gruß
Robert
Anzeige
AW: VBA - SumIf(s) vereinfachen wegen performence
27.03.2018 15:29:25
Andreas
hallo Robert,
danke schön.
Das schaut ja schonmal gut aus. Wie müsste ich denn die Formel mit Sumifs eingeben.
AW: VBA - SumIf(s) vereinfachen wegen performence
27.03.2018 16:40:45
Robert
Hallo Andreas,
das kann ich Dir von hier nicht sagen. Ich weiß weder welchen Wert die Variablen a und e haben können, noch welche Zellbereiche die Variablen objVolumenBereich und objKriterienBereich abdecken. Das ist mir zu viel raten und irgendwelche Annahmen zugrunde zu legen.
Versuche einmal für die erste Zelle, die einen Wert erhalten soll (bzw. bei Nichtvorliegen der Bedingungen halt eben keinen Wert), diesen im Tabellenblatt mit SUMMEWENNS zu berechnen. Wenn Du dann die relativen/absoluten Zellbezüge so setzt, dass die Formel auch stimmt, wenn Du sie nach rechts und nach unten ziehst, dann hast Du es fast geschafft.
In VBA dann für den kompletten Tabellenbereich, der die Werte aufnehmen soll die Formel der ersten Zelle links oben in dem Bereich einfügen und durch Werte ersetzen. Da musst Du halt nur noch auf die richtige Schreibweise achten (bei Formula engl. Funktionsnamen, Anführungsstriche verdoppeln, Komma als Parameter-Trenner usw.).
Gruß
Robert
Anzeige
AW: VBA - SumIf(s) vereinfachen wegen performence
27.03.2018 17:18:15
Andreas
hallo Robert,
vielen Dank nochmals für deine erneute Antwort. Ich habe es doch mit deinem Denkanstoß (Beispiel) auch für meine anderen Formeln hinbekommen.
Und was soll ich sagen, um Welten schneller.
Also nochmals vielen Dank du hast mich gerettet.

314 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige