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

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 !

    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

    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

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige
    Archiv - Verwandte Themen
    Forumthread
    Beiträge