Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
888to892
888to892
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Alle PivotItems auf Visible setzen - Performance

Forumthread: Alle PivotItems auf Visible setzen - Performance

Alle PivotItems auf Visible setzen - Performance
27.07.2007 11:49:00
Andreas
Hallo Excelianer,
leider habe ich ein Problem, per VBA alle Einträge einer PivotTabelle auf visible zu setzen.
Es gibt zwar beliebig viele Methoden das zu bewerkstelligen, z.B.
  • https://www.herber.de/forum/archiv/880to884/t880272.htm#880527

    • bloß sind die bei grossen Tabellen? ELEND langsam.
    Ich habe z.B. 1300 Items und die For Each Schleife dauert xx Minuten (Ich habe das Ende noch nie abgewartet ). Aber jetzt bitte keine Seitenhiebe auf meine CPU Leistung ;-). 
    
Sub PivotItemsAlleEinblenden(Feld As PivotField)
'Alle Einträge des Feldes einblenden
Dim Pivot_Item As PivotItem
For Each Pivot_Item In Feld.PivotItems
Pivot_Item.Visible = True
Next
End Sub


    Probiert habe ich auch die Performance-Geeks mit

  • Application.ScreenUpdating = False

  • Application.DisplayAlerts = False

  • Tabelle1.PivotTables("PivotTable1").ManualUpdate = True

    • die bringen aber keine wirkliche Verbesserung.
    Das Problem liegt in der Excel internen Verarbeitung der Schleife: Bei jedem Durchlauf wird die Ganze Tabelle geupdated, was sehr viel Zeit kostet.
    Verwunderlich ist allerdings, daß bei der manuellen Eingabe in der Auswahlbox der Pivottabelle mit Klicken
    von "(Alle anzeigen)" die Performance ganz und gar nicht leidet.
    Wer jetzt glaubt Makrorecorder anschmeissen und schauen was passiert, wird enttäuscht.
    Das Macro sieht in etwa so aus:
    
.PivotItems("D000004").Visible = True
.PivotItems("D014520").Visible = True
.PivotItems("D014565").Visible = True
.PivotItems("D014620").Visible = True
.PivotItems("D021063").Visible = True


    Beim Abspielen hat man wieder genau das gleiche Performanceproblem.
    Hat jemand eine Idee ? Danke für Eure Kommentare !

    Anzeige

    5
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    AW: Alle PivotItems auf Visible setzen - Performance
    27.07.2007 13:19:45
    Beate
    Hallo,
    teste mal diesen Code:
    
Sub Pivot_aufraeumen()
Dim f As PivotField, i As PivotItem
Application.ScreenUpdating = False
With ActiveSheet.PivotTables(1)
For Each f In .PageFields
f.CurrentPage = "(Alle)"
Next
For Each f In .RowFields
For Each i In f.PivotItems
If i.RecordCount  0 Then
i.Visible = True
Else
i.Delete 'wenn leerer DS dann löschen
End If
Next
Next
End With
End Sub


    Wenn dies nun auch noch lange dauert, dann schau dir mal diesen Thread an:
    https://www.herber.de/forum/archiv/824to828/t827482.htm#827482
    Da gibt es den Vorschlag von mir, nur mit einer Kopie der Pivottabelle zu arbeiten, diese nach Gebrauch zu löschen. Wenn man sie wieder braucht, einfach wieder die Pivottabelle kopieren. Dann braucht nie was zurückgesetzt werden.
    Gruß,
    Beate

    Anzeige
    AW: Alle PivotItems auf Visible setzen - Performan
    27.07.2007 13:40:12
    Andreas
    Hallo Beate,
    vielen Dank für Deine Antwort. Leider ist der Code nicht wirklich schneller.
    Das Performance-Leck liegt im Excel, da nach jeder Iteration gleich ob
    
For Each i In f.PivotItems
If i.RecordCount  0 Then
i.Visible = True
Else
i.Delete 'wenn leerer DS dann löschen
End If
Next


    oder 

    
For Each Pivot_Item In Feld.PivotItems
Pivot_Item.Visible = True
Next


    die Pivottabelle Excel-intern neu organisiert wird. Bei kleinen Pivot-Tabellen kein Problem, da nicht viel Zeilen der Tabelle verschoben werden müssen. Bei grossen Tabellen dauert "eine" Neuorganisation etwa 1 Sekunde. Bei 1300 Items erwarte ich 1300 Sekunden entspricht etwa 21 Minuten.
    Der Screenaufbau (Application.ScreenUpdating = False) hätte wenn eingeschaltet nicht die Zeit gekostet, eher das immerwährende Neuberechnen der Tabelle.
    MfG,
    Andreas

    Anzeige
    AW: Alle PivotItems auf Visible setzen - Performance
    27.07.2007 15:40:56
    Luschi
    Hallo Andreas,
    schalte auch noch die Ereignissteuerung aus, da jeder Befehl
    .PivotItems("D000004").Visible = True
    folgende Ereignisse auslöst
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    und
    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    
Application.EnableEvents = False
For Each Pivot_Item In Feld.PivotItems
Pivot_Item.Visible = True
Next
Application.EnableEvents = True

    Gruß von Luschi
    aus klein-Paris

    Anzeige
    AW: Alle PivotItems auf Visible setzen - Performan
    30.07.2007 10:07:00
    Andreas
    Hallo Luschi,
    vielen Dank für Deinen Tip mit Application.EnableEvents=False. Ohne zu Testen - Fehlanzeige ...
    Beate Schmitz hat mir am letzten Freitag nämlich die Tipps https://www.herber.de/forum/archiv/824to828/t827482.htm#827482
    ans Herz gelegt.
    Da und im Forum gibts immer wieder die Funktion getmorespeed(...) worin die Events auf gleiche Art ausgeschaltet für mehr speed werben. So konnte ich das schon am Freitag testen.
    DIE beiden genannten Events können IMHO auch nicht das massive Leck mit einer Sekunde Delay für einen Schleifendurchlauf verursachen:
    
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)


    Ich schätze mal, da liegt noch eine andere Leiche im Excel begraben :-(
    Ich bin grade dabei einen Workaround für das Problem zu basteln, indem ich nicht gewünschte Zeilen einfach ausblende.
    Falls Du vielleicht doch noch einen anderen Tip für mich hast, lass es mich wissen. Bin für jede Hilfe dankbar.
    MfG,
    Andreas

    Anzeige
    AW: Alle PivotItems auf Visible setzen - Performan
    30.07.2007 14:17:00
    Andreas
    Habe inzwischen den Workaround implementiert und blende nicht benötigte Zeilen einfach aus.
    * Zeit weniger als 30s gegenüber 21 Minuten
    Das Ergebnis entspricht zwar nicht genau der Pivot-Auswahl (z.B. bei Summenbildung), aber ich kann damit leben.
    ;
    Anzeige
    Anzeige

    Infobox / Tutorial

    Alle PivotItems auf Visible setzen - Performance

    Schritt-für-Schritt-Anleitung

    Um alle PivotItems in einer PivotTabelle auf "Visible" zu setzen, kannst du folgenden VBA-Code verwenden:

    Sub AllePivotItemsSichtbarMachen()
    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1) ' Ersetze 1 durch den Index deiner PivotTabelle

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    For Each pi In pt.PivotFields("DeinFeldName").PivotItems ' Ersetze "DeinFeldName" durch den Namen deines PivotFeldes
        pi.Visible = True
    Next pi

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

    Dieser Code setzt alle PivotItems sichtbar und schaltet die Bildschirmaktualisierung sowie die Ereignissteuerung aus, um die Performance zu verbessern.

    Häufige Fehler und Lösungen

    • Fehler: PivotItems werden nicht sichtbar

      • Lösung: Stelle sicher, dass der Feldname im Code korrekt angegeben ist und das PivotTable-Objekt richtig referenziert wird.

    • Fehler: Lange Laufzeit bei großen Datenmengen

      • Lösung: Nutze Application.ScreenUpdating = False und Application.EnableEvents = False, um die Performance zu steigern.

    Alternative Methoden

    Eine alternative Methode besteht darin, nur die nicht benötigten Zeilen auszublenden, anstatt sie zu löschen oder die Sichtbarkeit zu ändern. Hier ein Beispiel:

    Sub PivotItemsAusblenden()
    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1)

    Application.ScreenUpdating = False

    For Each pi In pt.PivotFields("DeinFeldName").PivotItems
        If pi.RecordCount = 0 Then
            pi.Visible = False
        End If
    Next pi

    Application.ScreenUpdating = True
End Sub

    Diese Methode kann die Performance erheblich verbessern, da sie die Anzahl der sichtbaren Elemente reduziert.

    Praktische Beispiele

    Wenn du mit einer großen PivotTabelle arbeitest, kann die folgende Methode nützlich sein:

    Sub AllePivotItemsEinblenden()
    Dim pf As PivotField
    Set pf = ActiveSheet.PivotTables(1).PivotFields("DeinFeldName")

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    For Each pi In pf.PivotItems
        pi.Visible = True
    Next pi

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

    Ersetze "DeinFeldName" mit dem tatsächlichen Namen deines PivotFeldes. Dieser Code setzt alle PivotItems sichtbar und sorgt gleichzeitig für eine bessere Performance.

    Tipps für Profis

    • Nutze die PivotItems.Visible-Eigenschaft, um gezielt nur die benötigten Items sichtbar zu machen.
    • Überlege, ob du mit Kopien deiner PivotTabellen arbeiten kannst, um die Performance zu steigern.
    • Experimentiere mit der ManualUpdate-Eigenschaft, um die Aktualisierung der PivotTabelle zu steuern.

    FAQ: Häufige Fragen

    1. Wie kann ich alle PivotItems auf einmal ausblenden? Du kannst die Sichtbarkeit aller PivotItems gleichzeitig steuern, indem du die Sichtbarkeit in einer Schleife änderst, wie im oben genannten VBA-Code.

    2. Warum dauert es so lange, alle PivotItems zu ändern? Die lange Laufzeit kann durch die interne Verarbeitung von Excel verursacht werden, da jede Änderung an der Sichtbarkeit die Tabelle neu organisiert. Tipps zur Verbesserung der Performance sind in diesem Tutorial enthalten.

    Beliebteste Forumthreads (12 Monate)

    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
    Anzeige
    Anzeige
    Anzeige