Optimieren Sie für jede Schleife in der Pivot-Tabelle

547
SeattleITguy

Es fällt mir schwer, eine VBA- For EachSchleife zu optimieren, die 10-15.000 Zeilen in einer PivotTable analysiert, die auf einer Excel-Tabelle basiert, und zwar in derselben Arbeitsmappe. Derzeit dauert dies etwa 2 Minuten, was ich verbessern möchte, da ich dies in Besprechungen verwende.

Ich habe einige interessante Vorschläge gesucht und gefunden, wie das " dim" "Screen Updating" " ," " Manual Calculations" " und" ", um meine Variablen als andere Datentypen zu" " deaktivieren, aber ich habe keinen Unterschied in der Geschwindigkeit. Ich gehe davon aus, dass die For EachSchleife nicht für diesen Zweck gedacht ist.

Beim Lesen dieses Super User-Beitrags sieht es so aus, als könnte ich die relevanten Werte in ein "Wörterbuch" eingeben und wäre sehr schnell. Die Tatsache, dass ich zwei Elemente zum Filtern habe, macht es für mich etwas komplizierter.

Ich lerne durch das Tun und behaupte nicht, ein VBA-Codierer zu sein, also wird wirklich jede Hilfe sehr geschätzt! 

Dim pvtTable As PivotTable Dim pvtField1, pvtField2 As PivotField Dim pvtItem1, pvtItem2 As PivotItem  Set ws = ActiveSheet Set pvtTable = ws.PivotTables("PTReport") Set pvtField1 = pvtTable.PivotFields("callNummer") Set pvtField2 = pvtTable.PivotFields("Destination")  Application.ScreenUpdating = False  For Each pvtItem1 In pvtField1.PivotItems If InStr(UCase(pvtItem1), "STORE") > 0 Then pvtItem1.Visible = True Else pvtItem1.Visible = False End If Next  For Each pvtItem2 In pvtField2.PivotItems If InStr(UCase(pvtItem2), "221") > 0 Then pvtItem2.Visible = True Else pvtItem2.Visible = False End If Next  Application.ScreenUpdating = True

Wenn ich etwas klarstellen oder angeben kann, lass es mich wissen.

1
Bei der Verarbeitung vieler Daten in Excel wird empfohlen, die Berechnungen am Anfang des Makros zu deaktivieren und am Ende zu aktivieren. Es kann ein Makro 10x oder mehr beschleunigen. LPChip vor 7 Jahren 0
Vielen Dank für deine Antwort! Ich habe am Anfang des Makros den Code `Application.ScreenUpdating = False` (bearbeitete es in das OP - sorry für die Verwirrung), aber das scheint mir die Geschwindigkeit nicht zu bringen. SeattleITguy vor 7 Jahren 0

2 Antworten auf die Frage

1
Scott

Ich vermute, dass die 20-30K-Anrufe InStr(UCase())das Problem sind. Ich schlage vor, dass Sie ein paar Helfer-Spalten definieren. Zum Beispiel, wenn Ihre "CallNummer" - und "Destination" -Daten in Spalten  Aund  B, sind gesetzt

  • Y1 → =IFERROR(SEARCH("store", A1), 0)
  • Z1 → =IFERROR(SEARCH("221", B1), 0)

und ändern Sie dann Ihren VBA-Code, um zu prüfen, ob und welche . Auf diese Weise wird die String-in-String-Überprüfung immer dann durchgeführt, wenn die Daten "callNummer" und "Destination" geändert werden und die VBA-Routine weniger Arbeit erfordert. Natürlich können Sie die Helper-Spalten ausblenden, wenn Sie alles zum Laufen gebracht haben.YnZn> 0

Ich weiß nicht genau, wie Sie Ihre Aussagen in dieses Framework umsetzen sollen.pvtItemN.Visible = …

Aber auch wenn dies nicht funktioniert, können Sie den InStr(UCase(pvtItem2), "221")Test in  ändern InStr(pvtItem2, "221"). Wenn Sie nur nach einer Zahl suchen, gibt es keinen Grund, den alphabetischen Inhalt der Zelle in Großbuchstaben umzuwandeln.

Danke, Scott, das hat den Trick gemacht! Jetzt läuft der Filter in Sekunden, danke nochmals, dass du meine Augen dort geöffnet hast. Ich habe das Blatt versteckt, in dem sich die Daten befinden, und das hat perfekt funktioniert! SeattleITguy vor 7 Jahren 0
1
Rich Holton

Fügen Sie kurz vor Ihrer ersten for ... next Schleife folgenden Code ein:

Dim saveCalc as xlCalculation  With Application .ScreenUpdating = False saveCalc = .Calculation .Calculation = xlCalculationManual End With

Fügen Sie dann nach Ihrer zweiten for ... next Schleife folgenden Code ein:

With Application .ScreenUpdating = True .Calculation = saveCalc End With
Vielen Dank, Rich! Hat geholfen, es zu glätten und etwas Zeit auszuziehen SeattleITguy vor 7 Jahren 0

Verwandte Probleme