Pivotmit nach Datenspalte sortieren?
Hallo zusammen,
ich arbeite in in letzter Zeit öfter mit den dynamischen Pivot Tabellen (PIVOTMIT), da ich die statischen nicht mag, weil man die stetig aktualisieren muss.
Die Syntax dazu ist wie folgt:
Als Beispiel habe ich einfach paar Daten mit Projekten und einer Fahrstrecke ausgewertet.
wie man sieht sortiert durch den Parameter -2 am Ende Excel sehr schön nach der Gesamtsumme, bei -1 wird nach dem Namen der Projekte sortiert. ich habe aber keine Möglichkeit gefunden z.B. Ab- oder Aufsteigend nach dem Jahr 2023 zu sortieren. Was für mich eigentlich ein Basic Feature wäre.
So wie ich das nach einigem probieren rausanalysiert kennt Excel die Gruppierten Spalten gar nicht, diese haben schlich und ergreifend keinen Index. Weder in Zeilen noch in Spaltenrichtung.
Wenn ich nun die Funktion Sortieren außen rum anlege, erhalte ich natürlich absoluten Blödsinn, da nun ebenfalls 2022 und das Gesamtergebnis mit einbezogen wird in den Sortiervorgang siehe Screenshot.
Hat einer eine Idee, wie ich das erreichen kann, die Dokumentation bei Microsoft geht natürlich wie so oft nur auf die absoluten Grundlagen ein. Und auf YouTube bin ich bisher noch nichts über sinnvolles gestolpert, fast alle zeigen hier nur wie toll die Funktion ist mit ihren Basics, ohne mal Komplexere Dinge zu probieren, wie z.B. mehrere Spalten verschieden zu aggregieren.
Habe nur altes Excel und kann es nicht probieren:Versuche mal row_sort_order leer zu lassen und dafür col_sort_order zu setzen...
Danke dann wird nach Zeilenbeschriftung sortiert also entweder 2022 2023 2024 auf/absteigend oder nach den Spaltenergebnissen.
2 Antworten
Versuche es doch mal damit, wenn es das ist was Du suchst.
Start - markiere den Sortierbereich (Jahr/Wertebereich) - Sortieren/Filtern (re) - Auswahl (Auf-/absteigend).
sortiert
Für die Auswahl der Jahre könnte man sich ein Makro anlegen, wenn man flexibel sortieren will.
Danke ja das ist die alte klassische Pivot Tabelle. Nachteil dieser ist, die muss man manuell aktualisieren oder eine Auto Aktualisierung einrichten aller x Minuten. Außerdem lässt diese sich nur sehr unschön in bestehende Daten "unauffällig" integrieren und es gibt tatsächlich ein paar Dinge die die Pivottabelle nicht kann.
Der große Vorteil der klassischen Pivottabelle ist natürlich die Performance aber die ist mir wurscht, denn ich hab das mal an 4 3000 Zeilen Tabellen getestet und hatte keinerlei Beeinträchtigungen und das in der Online Version die generell langsamer ist.
Aber ja in dem Punkt scheint die classic Version überlegen mich wundert es, dass dies nie bei der neuen dynamischen bedacht wurde. Warum hat nicht jede Spalte einen eigenen Index sondern nur die Beschriftungen und das Ergebnis. Nicht aber die Aggegierten Wertepaare? Da darf MS ruhig ein wenig nachbessern.
Das war eine schwere Geburt, also ich hab zwar eine Lösung gefunden aber nicht mit der Basis Formel. Ich musste mittels LET die Formel komplett auseinander nehmen und danach wieder zusammen setzen, falls wer eine bessere Lösung hat freue ich mich über Rückmeldung, denn die gleich folgende Formel hat einen großen Nachteil, für diesen Fall funktioniert sie ganz gut, binde ich aber z.B. 2 Spalten ein, nach denen Gruppiert wird und nutze Zwischenergebnisse habe ich ein Problem. Denn diese kann ich nicht einfach wie beim GesamtErgebnis unten dran setzen sondern müsste die Tabelle wieder auftrennen, das würde Rekursiv, nahezu nicht möglich sein.
Hier nach Spalte 2 Absteigend sortiert
Hier nach Spalte 4 Aufsteigend sortiert
zur Bessere Erkennbarkeit hab ich die Spalte farblich hervorgehoben, was sich wo wieder findet.
Für alle, die dies nützlich finden, hier die Erklärung:
=LET(
pivot;PIVOTMIT(B2:B19;JAHR(A2:A19);C2:C19;LAMBDA(z;SUMME(z));;;-2);
rdata;WEGLASSEN(WEGLASSEN(pivot;1);-1);
data;SORTIEREN(WENN(rdata="";0;rdata);F2;ERSTERWERT(F3;"ASC";1;-1));
fdata;VSTAPELN(ZEILENWAHL(pivot;1);data;ZEILENWAHL(pivot;-1));
fdata
)
Zur Besseren Erkennbarkeit habe ich die Formel mal Variablenweise aufgeteilt.
pivot;PIVOTMIT(B2:B19;JAHR(A2:A19);C2:C19;LAMBDA(z;SUMME(z));;;-2)
erzeugt die ursprüngliche Pivottabelle, kein Unterschied zu der obigen Formel aus der Frage. (theoretisch könnte man den Parameter -2 nun weglassen, da der hier sowieso keine Relevanz mehr hat). im Anschluss wird das Ergebnis in die Variable "pivot" geschrieben.
rdata;WEGLASSEN(WEGLASSEN(pivot;1);-1);
hier kürze ich die Tabelle um die unterste und die oberste Zeile der Tabelle, so dass nur noch die Zeilenbeschriftung, die summierten Werte und die Gesamsumme übrig bleiben. und Schreibe das Ergebnis in "rdata"
data;SORTIEREN(WENN(rdata="";0;rdata);F2;ERSTERWERT(F3;"ASC";1;-1));
hier werden alle Felder die keine Daten enthalten, weil z.B. dieses Projekt im Jahr 2023 gar nicht vorkam. mit 0en gefüllt, denn tue ich dies nicht landen leere Felder beim absteigenden sortieren immer ganz oben.
im Anschluss wird der Datensatz nach den definierten Bedingungen in F2 und G2 sortiert F2 = Spaltenindex, G2 = Auf/-Absteigend und in "data" geschrieben
fdata;VSTAPELN(ZEILENWAHL(pivot;1);data;ZEILENWAHL(pivot;-1));
im Anschluss werden wir die einst entfernten Zeilen angefügt, da keine horizontale Verschiebung stattgefunden hat, kann dies einfach so geschehen im Anschluss wird es in "fdata" geschrieben
in der darauffolgenden Zeile wir nun noch fdata ausgegeben.
Ja ich gebe zu nicht ganz einfach, aber eine andere Möglichkeit habe ich nicht gefunden, aber es ist dynamisch und funktioniert für jede Größe einer Pivottabelle. Aus den o.g. aber wahrlich noch nicht optimal, würde mich über Anmerkungen freuen, die mir einen Denkanstoß geben.
Zu deinem ersten Satz geht es mir ganz genauso, denn wie ich oben schrieb halte ich das sortieren nach beliebigen Spalten für ein Basic Feature.
Leider ändert das nichts daran dass ich die klassische Pivottabelle einfach nicht leiden kann. Ich konnte der noch nie etwas abgewinnen einfach vom "work and feel" ich bin der geborene Formelmensch. Keine nervigen Dialoge kein Drag & Drop, individuelle Gestaltungsmöglichkeiten durch die Nutzung von Lambda, Live Aktualisierung nach jedem Enter drücken.
Aber das Konstrukt von oben ist keine wirkliche Alternative für eine eigentlich simple Lösung. Daher bin ich noch immer offen dafür wenn jemand dafür etwas einfacheres kennt vielleicht gibt es ja mal einen nicht dokumentierten Workflow wie man das erreichen kann.
Moin,
ich habe ehrlich gesagt grosse Schwierigkeit in dieser Formelorgie die Überlegenheit der PivotMit ggü die klassische Pivot zu erkennen...
Du kannst eine -normale- Pivottable jederzeit mit Strg+Alt+F5 aktualisieren, ohne auf die Pivot zu sein.
Wenn Du die Quelle als Tabelle umwandelst (Menü "Einfpgen", "Tabelle"), hast Du eine erhöhte Sicherheit, dass neue Daten in die Pivot berücksichtigt werden.
Wenn die Daten aus einer Datei oder fremde Quelle kommen, kann es sich lohnen Power Query anzuschauen.
VG Yal