Performance: Dropdown & Pivot-Tabellen

Hallo liebes Forum,

in den letzten Tagen habe ich viele Beiträge gelesen und versucht, die Frage für mein Problem optimal zu stellen, um auf die richtigen Forum-Treffer zu kommen...
Leider habe ich mich etwas verloren und hoffe, dass ihr mir Hinweise darüber geben könnt, wie ich meine Datei optimieren kann.

Worum geht es?
Es ist ein Projekt über ein Event, bei dem Helfer organisiert werden sollen.

Das Event dauert 10 Tage.

Jeder Helfer kommt und geht, wann er kann/ möchte und somit habe ich eine variable Verfügbrkeit vom Personal.

Es gibt grob 15 Bereiche (oder Stationen), in denen Unterstützung gebraucht wird. Jede Person kann seine Präferenzen konkret angeben, wo sie sich einbringen möchte, oder die Entscheidung dem Teamleiter überlassen.

Der Tag wird in 4 Schichten á 4 Stunden aufgeteilt. Pro Schicht und Station werden in der Regel 1-2 Personen benötigt, selten 3 oder sogar 4.

Damit entsteht eine Matrix von Personen und deren Verfügbarkeiten für die konkreten Bereiche.

Mit dieser Datei möchte ich den Teamleiter unterstützen, für jeden einzelnen Tag einen Plan zu erstellen und zu jeder Schicht und jedem Bereich nur die konkret verfügbaren Helfer auswählen zu können.

https://www.herber.de/bbs/user/158416.xlsm

Was ist mein Problem?
Performance!

Soweit habe ich es geschafft, für Tag 1 und Tag 2 entsprechend die Tabellen für die Planung vorzubereiten (Tabs "Tag 1" und "Tag 2", Tabellen "PlanungTag1" und "PlanungTag2") und die einzelnen Bereiche mit Datenvalidierung zu versehen (Dropdown-Listen).
Diese verweisen mittels OFFSET() auf die entsprechenden Tabs "pivots_Tag 1"/ "pivots_Tag 2", die Pivot-Tabellen für den konkreten Tag und zu jedem Bereich liefern. Damit erzeuge ich die eindeutigen Listen mit Namen, die sich beim Refresh anpassen und alphabetisch sortiert werden.

Bei jedem Eintrag auf Seiten "Tag 1" - "Tag 10" soll auf dem Tab "Personal" die Gesamtanzahl der gebuchten Schichten pro Person berechnet werden.
Wenn die Kapazitätsgrenze von der Person erreicht ist, darf sie nirgendwo mehr in der Dropdown-Liste erscheinen, da sie nicht mehr verfügbar ist.
Dies bedeutet, dass momentan nach jedem Eintrag die PowerQuery-Transformationen durchlaufen (Tab "MATRIX" also aktualisiert wird) und dann alle Pivot-Tabellen für alle 10 Tage aktualisiert werden (es wären also 150 Pivot-Tabellen, wenn ich die Datei komplett weiter entwickeln würde).

Ich habe einige Ansätze probiert, von:

ThisWorkbook.RefreshAll

bis hin zu

Private Sub Worksheet_Change(ByVal Target As Range)

Dim pvt As PivotTable

ThisWorkbook.Connections(3).Refresh

For Each pvt In Sheets("pivots_Tag 1").PivotTables
'If Not Intersect(Target, Range("rngT1B1")) Is Nothing Then
pvt.RefreshTable
'End If
Next
 
End Sub

und hätte gern als Ziel, dass der Benutzer dieser Datei nicht mehrere Sekunden pro Klick /Eintrag warten muss, bis sich die Dropdown-Listen anpassen.

Habt ihr vielleicht einen Tipp zur Verbesserung?
Soll ich die Daten grundsätzlich anders aufbereiten?

Ich hoffe, dass meine Erklärung verständlich ist und würde mich sehr über einen Hinweis freuen!
Herzlichen Dank im Voraus!

Zusätzliche Erläuterungen:

"Personal":
Hier werden die Stammdaten abgebildet.
Es wird die Anzahl der verfügbaren Schichten (Spalte X) als feste Zahl reingeschrieben, unabhängig vom Datum der An-/Abreise, denn in der Regel wird jede Person nur eine Schicht pro Tag liefern müssen, aber manche VIP-Helfer werden öfters gebraucht.

In Spalte Y werden die insgesamt gebuchten Schichten für diese Person von allen Tabs ("Tag 1" bis "Tag 10") berechnet.
Daraus ergibt sich Spalte Z mit frei verfügbaren Kapazitäten. Sobald die Summe auf 0 sinkt, hat die Person ihre Kapazitätsgrenze erreicht und ihr Name erscheint gar nicht mehr in den Spalten AA bis AJ und damit ist sie nicht mehr für die Dropdown-Listen verfügbar - in PoweQuery werden die Daten über diese Person nicht weiter verarbeitet.
Die Daten werden im Tab "MATRIX", Tabelle "Matrix_Personal_Bereiche" angezeigt.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

#"Gruppierte Zeilen" = Table.Group(#"Zusammengeführte Spalten", {"Zusammengeführt"}, {{"Liste", each Text.Combine([Name], ";"), type text}}), #"Dynamische Liste" = List.Transform( {1 .. List.Max(Table.AddColumn( #"Gruppierte Zeilen", "egal", each List.Count(Text.PositionOfAny([Liste],{";"},Occurrence.All)) )[egal] ) + 1 }, each "Liste." & Text.From(_) ), #"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Gruppierte Zeilen", "Liste", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), #"Dynamische Liste"),

Hi Yal,
Dieses Projekt ist super gut angenommen worden, danke sehr für die Unterstützung soweit!
Jetzt geht es zum nächsten Schritt, natürlich... ;-)

Es wäre einfach bombastisch, wenn ein Button "Let's do magic!" dem Nutzer ermöglichen würde, mindestens die Hälfte der Zellen automatisch und randomly zu befüllen. Danach können noch nachträglich manuelle Anpassungen vorgenommen werden.

Dazu habe ich einige Ansätze ausprobiert und auch den tollen ChatGPT befragt, aber bei jeder zusätzlichen Frage schreibt mir das Programm den Code komplett neu und immer variierend, was mich sehr verwirrt. So gut kann ich VBA leider nicht und letztendlich ist es mir nicht gelungen, alle Komponenten so zusammen zu fügen, dass der komplette Code die Anforderungen erfüllt.

https://www.herber.de/bbs/user/158888.xlsm

Sheet "Plan":
- Beim Befüllen der leeren Zellen ab Spalte D soll erstmal in Spalte C geprüft werden, ob es die Position "Helfer I" ist, denn nur diese soll (vorerst) automatisch befüllt werden. Position II (oder III und IV) sind optional und werden je nach Verfügbarkeit zusätzlich berücksichtigt und manuell befüllt. Dazu sind ja die Drop-down Felder (Datenvalidierung) schon voll funktionsfähig. :)

- Für die Befüllung der konkreten Zellen ab Spalte D (und Helfer I) soll im Code der Validierungsbereich herangezogen werden (Drop-down-Werte), die über INDIRECT() ermittelt werden. Die Quelle dazu ist das Sheet "Verfügbarkeit". Davon kann ruhig eine zufällige Person zum konkreten Tag und Bereich gewählt werden, oder, falls nicht zu aufwändig, die Person mit den meisten Ressourcen.
Oder gibt es einen besseren Ansatz? Im Prinzip soll das Gleiche geschehen, wie wenn ich die Datei manuell befülle und nur die relevanten Personen pro Tag und Bereich sehen kann.

- Nach jedem Eintrag soll die Gesamtzahl der Schichten pro Person gezählt werden und mit den Soll-Vorgaben (Kapazität) auf dem Sheet "Personal" verglichen werden. Bzw. wäre eine Aktualisierung nach jedem Eintrag nötig, so wie es gerade eingestellt ist, wenn ich die Werte vom Drop-down manuell auswähle. Wenn ich das nicht tue, befüllt der Code natürlich im Nu die gesamte Tabelle einfach so und verwendet einen Namen 240 Mal (nicht übertreiben!).
Sobald die Kapazität der Person erreicht ist, sollte sie nicht mehr unter "Verfügbarkeit" erscheinen.

- Falls möglich, macht es Sinn, die Zeilen nach Tagen zu gruppieren, um pro Tag für alle Bereiche und alle Schichten der "Helfer I" Duplikate zu vermeiden. Hintergrund ist die Aussage, dass eine Person pro Tag erstmal nur eine Schicht erbringen soll. Vielleicht kann dies manuell außerhalb dieser Regel angepasst werden.

- Wenn keine Person mehr verfügbar ist, sollte die Zelle einfach leer bleiben und der Code bis zum letzten Feld der Tabelle durchgeführt werden. Also keine Fehlermeldung und Abbruch zwischendurch, sondern eher eine Meldung zum Schluss, dass die verfügbaren Ressourcen verteilt wurden.

Ist das alles machbar? Oder wäre der Code so unperformant, dass es keinen Sinn macht?!? 😯
Ich könnte mir vorstellen, dass ein Nutzer nichts dagegen hätte, eine Minute lang zu beobachten, wie 300 Zeilen befüllt werden. Das würde ihm dennoch deutlich schneller vorkommen, als wenn er diese Arbeit selbst erledigen müsste... 😁

Ich freue mich sehr über jede Form von Feedback und bin dankbar, dass es Euch Experten gibt!
Cheers, Lil

Beliebteste Forumthreads (12 Monate)

Performance: Dropdown & Pivot-Tabellen

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread