Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Pivot nach "% Differenz von" sortieren

Pivot nach "% Differenz von" sortieren
02.10.2019 16:46:07
Dome
Hallo Leute,
Ich lasse mir in einer Pivot-Auswertung (siehe Beispieldatei) die Differenz zweier Jahreswerte in % anzeigen.
Nun würde ich gerne nach diesen %-Werten sortieren, nur leider stosse ich hier an meine Grenzen.
https://www.herber.de/bbs/user/132334.xls
Kennt jemand hierzu eine Lösung?
Besten Dank für Eure Inputs.
LG
Dome
Anzeige

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Pivot nach "% Differenz von" sortieren
02.10.2019 18:43:15
Luschi
Hallo Dome,
Excel 2007 war der Versuchsballon, neue Möglichkeiten einzuführen:
- Ribbon
- XML-Tabellen usw.
Der eigentliche Fortschritt trat erst mit 'Excel 2010' ein und es wurden neue Add-Ins entwickelt:
- PowerQuery & PowerPivot
- Datenvergleiche zwischen Datumsperioden sind erst mit PowerPivot richtig möglich!
Dazu braucht man aber mindestens Excel 2010 ff.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: eine Möglichkeit wäre ...
02.10.2019 19:45:06
neopa
Hallo Domo,
... man wertet das Ergebnis der Pivottabelle z.B. formeltechnisch in einer weiteren Liste aus.
So könntest Du z.B. mit folgender Matrixfunktion(alität)sformel die keines spez. Formelabschluss wie eine klassische Matrixformel benötigt, die nach absteigender "Delta VJ%" sortierte Firmennamensliste ermitteln:
=WENNFEHLER(INDEX(C:C;MAX(INDEX((I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1)=
KGRÖSSTE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1);ZEILE(C1)))*
ZEILE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1));)));"")

Formel nach unten kopieren. Mittels SVERWEIS() kannst Du dieser dann die entsprechenden Daten aus der Pivotauswertung zuweisen.
Gruß Werner
.. , - ...
Anzeige
AW: und um die 0,0% auszuschließen ...
02.10.2019 20:03:53
neopa
Hallo,
... hab ich die Formel erweitert zu:
=WENNFEHLER(INDEX(C:C;MAX(INDEX((I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1)=
KGRÖSSTE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1);ZEILE(C1
/(RUNDEN(KGRÖSSTE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1);ZEILE(C1));3)&gt0))
*ZEILE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1));)));"")
und wenn identische Deltadifferenzen auftreten können, würde die Formel noch etwas anders.
Aber schau erst einmal, ob Du es überhaupt derart angehen willst.
Gruß Werner
.. , - ...
Anzeige
AW: und um die 0,0% auszuschließen ...
02.10.2019 20:04:33
neopa
Hallo,
... hab ich die Formel erweitert zu:
=WENNFEHLER(INDEX(C:C;MAX(INDEX((I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1)=
KGRÖSSTE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1);ZEILE(C1
/(RUNDEN(KGRÖSSTE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1);ZEILE(C1));3)&gt0))
*ZEILE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1));)));"")
und wenn identische Deltadifferenzen auftreten können, würde die Formel noch etwas anders.
Aber schau erst einmal, ob Du es überhaupt derart angehen willst.
Gruß Werner
.. , - ...
Anzeige
AW: und um die 0,0% auszuschließen ...
03.10.2019 07:32:47
Dome
Hi Werner,
Vielen Dank auch für Deine Rückmeldung.
Das funktioniert super, nur habe ich (wie Du bereits erwähnst) mehrere identische Deltas, insbesondere Nullen, die ich da sauber trennen müsste.
Meinst Du, Du könntest die Formel entsprechend anpassen bitte? ;)
Vielen lieben Dank.
LG
Dome
Anzeige
AW: ja, das meinte ich ...
03.10.2019 09:10:21
neopa
Hallo Dome,
... folgende erweiterte Formel:
=WENNFEHLER(INDEX(C:C;MAX(INDEX((I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1)+
ZEILE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1))%%=
KGRÖSSTE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1)+
ZEILE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1))%%;ZEILE(C1)))*
ZEILE(I$4:INDEX(I:I;VERGLEICH("*"&"Ergebnis";B:B;0)-1));)));"")

Gruß Werner
.. , - ...
Anzeige
AW: ja, das meinte ich ...
07.10.2019 08:38:35
Dome
Hallo Werner,
Bitte entschuldige das verspätete Dankeschön. Herzlichen Dank für Deine Unterstützung.
Mit dieser Formel kann ich wunderbar leben. Den Tabellenansatz schaue ich mir noch an, sieht smart aus.
LG
Dome
AW: bitteschön; aber ...
07.10.2019 08:58:25
neopa
Hallo Dome,
... sind denn Deine Daten zu "FINART" und "PRODART" wirklich immer die gleichen? Wahrscheinlich doch eher nicht. Dann hilft Dir weder meine bisherige (Tabellen-)Formellösung noch die bisheriger PQ-Lösung. Beide lassen sich jedoch erweitern. Dafür wäre es aber Deinerseits notwendig, entsprechende Beispieldaten hier einzustellen und aufzuzeigen wie Du diese dann im Ergebnis gelistet haben möchtest. Würde mich interessieren und mir am Abend anschauen.
Gruß Werner
.. , - ...
Anzeige
Resultat
07.10.2019 13:35:34
Dome
Hallo Werner,
Es spielt für mich insofern keine Rolle, als dass ich der Einfachheit und Übersichtlichkeit halber jeweils pro PRODART und FINART eine eigene Mappe erstelle und entsprechend filtere.
Anbei findest Du was ich mit Deiner Hilfe gemacht habe. (...könnte man noch etwas dynamischer gestalten, i know..)
https://www.herber.de/bbs/user/132376.xlsx
LG
Dome
Anzeige
AW: nun, in dem Fall ...
07.10.2019 14:08:20
neopa
Hallo Dome,
... hättest Du auch meinen Lösungsvorschlag als Grundlage nutzen können, den ich im Beitrag vom 03.10.2019 18:46:03 an Robert aufgezeigt hatte und der ganz ohne eine Pivotauswertung auskommt.
Aber entscheidend ist, dass Du nun Deine Ergebnisauswertung vornehmen konntest.
Gruß Werner
.. , - ...
Anzeige
AW: es gäbe auch eine Formellösung ohne Pivot ...
03.10.2019 11:07:37
neopa
Hallo Dome,
... die ganz ohne (D)eine Pivotauswertung auskommt, dafür lediglich zwei Hilfsspalten benötigt. Die Hilfsspalten und die Formeln dafür definiert. Wenn die beiden Hilfsspalten und die Quelldaten jeweils "Als Tabelle formatiert" werden und die Formeln dafür definiert werden, können die zwei Datenbereiche Datenbereiche sowie die Ergebnisliste innerhalb des Tabellenblattes oder auch der Datei beliebig verschoben werden und das Ergebnis passt sich auch automatisch an, wenn Datenänderungen und oder Erweiterungen vorgenommen werden.
(M)eine Lösung dafür hab ich momentan lediglich für die Auswertung ohne Berücksichtigung von "Finart" und "Prodart" und für lediglich zwei (beliebige) Jahre, die dann für alle Firmen gleich gelten) vorgenommen. Diese könnten jedoch auch berücksichtigt werden, wenn bekannt ist, wie dafür dann die Ergebnisliste aussehen soll.
Gruß Werner
.. , - ...
Anzeige
AW: eine reine PQ-Lösung für XL 2016 ...
03.10.2019 11:08:11
neopa
Hallo an die PQ-Profis,
... wie würde eine PQ-Lösung (in XL 2016!) für das Beispiel aussehen? Dies würde mich nun auch interessieren. Meine Versuche dazu sind gescheitert.
Gruß Werner
.. , - ...
AW: eine reine PQ-Lösung für XL 2016 ...
03.10.2019 16:30:30
Luschi
Hallo Werner,
'PQ' für Pivotauswertungen zu nutzen, wäre wohl 'mit der Wurscht nach dem Schinken werfen'. Außerdem bezweifle ich, daß dies auch automatisch bei Filtern der Pivottabelle klappt.
Dafür gibt es doch 'PP' (PowerPivot) und von diesem AddIn wird in den Excel-Foren fast nie gesprochen.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: dann zeig mal Deinen Lösungsvorschlag auf ...
03.10.2019 16:41:30
neopa
Hallo Luschi,
... wenn mit PP dann eben mit PP und Excel2016.
Für einen PQ-Profi sollte es mE aber auch eine Lösung nur mit PQ möglich sein. Aber scheinbar feiern die heute alle ;-)
Gruß Werner
.. , - ...
AW: das Ergebnis hatte ich mir so vorgestellt ...
03.10.2019 17:35:19
neopa
Hallo Robert,
... vielen Dank für Deinen Lösungsvorschlag.
An der Auswertung der beiden ersten Abfragen, die ich prinzipiell auch so hatte, war und bin ich auch eben zunächst wieder gescheitert. Grund: ich war dummerweise bisher immer davon ausgegangen, dass es ausreichend ist, das es ausreichend die beiden Tabellen zuzuordnen :-( Das auch deren Datenspalten zusätzlich aktiviert werden müssen, hab ich erst jetzt realisiert.
Zumindest war aber wenigstens meine Vermutung richtig, dass eine Lösung nur mit PQ möglich ist.
Jetzt wäre noch die Frage zu klären, wie könnte man die Spaltenauswertung unterhalb der letzten bisherigen Ergebniszeile ermitteln. Dafür hab ich noch nicht einmal eine Vorstellung eines Lösungsansatzes. Wüstes Du da auch einen?
Gruß Werner
.. , - ...
Anzeige
AW: im Prinzip so ... und ...
03.10.2019 18:41:13
neopa
Hallo Robert,
... aber das dazu lediglich die Funktionalität der intelligenten Tabelle genutzt werden muss/kann, hat sich mir erst jetzt durch Deine Lösung erschlossen.
Danke!
Einen kleinen Nachteil hat eine PQ-Lösung jedoch noch, also zumindest in meiner XL-Version.
Eine Änderung des/der Quelltabellennamen/s im Namensmanager bedarf dann einer auch nachträglichen Änderung im PQ-Editor. Bei einer Formellösung passt dies Excel automatisch an.
Hinzu kommt noch ein weiterer Nachteil für mich persönlich. Eine Formellösung macht mir mehr Spaß ;-)
Gruß Werner
.. , - ...
Anzeige
Spaß ? hab ich noch gar nicht bemerkt :-)))) Gruß
03.10.2019 18:46:03
robert
AW: nach dem Motto: der Weg ist mein Ziel ...
03.10.2019 19:27:03
neopa
Hallo Robert,
... dazu hier in https://www.herber.de/bbs/user/132340.xlsx der Vollständigkeit halber meine alternative Formellösung, die im Gegensatz zu einer "normalen PQ-Lösung" auf jegliche Datenänderung/-erweiterung (hier momentan natürlich noch ohne FINART und ohne PRODART) sofort reagiert.
Formeln J5:N5 und Q7:R7 weit genug nach unten kopiert. Die Tabellen "_Dat" und "_Hilf" und auch die Ergebnisliste J3:N10 können nachträglich beliebig in freie Bereich auch anderer Tabellenblätter (wie auch bei einer PQ-Lösung) verschoben werden und auch namentlich in zulässige andere Namen problemlos geändert werden. Nachteile: nicht geeignet für Massendatenauswertung, Formeln müssen auf "Vorrat" weit genug nach unten kopiert werden und sind aufwendiger zu erstellen als eine PQ-Lösung und wohl auch schwieriger zu verstehen. Aber warum gibt es z.B. Marathonläufer, wenn man eine solche Strecke viel einfacher und schneller zurücklegen kann.
Gruß Werner
.. , - ...
Anzeige
Tolle Datei (Formeln ) ! Danke und owT-Gruß
03.10.2019 19:33:20
robert
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Pivot Tabelle nach "% Differenz von" sortieren


Schritt-für-Schritt-Anleitung

  1. Erstelle eine Pivot Tabelle: Wähle deine Daten aus und gehe zu Einfügen > PivotTable. Wähle die Datenquelle und den Zielort für die Pivot Tabelle.

  2. Füge die gewünschten Felder hinzu: Ziehe die Felder, die du für die Analyse benötigst, in die Bereiche Zeilen, Werte und Filter.

  3. Berechne die Differenz zwischen zwei Spalten:

    • Füge eine neue berechnete Feld hinzu. Klicke dazu mit der rechten Maustaste auf die Pivot Tabelle, wähle PivotTable-Optionen und dann Berechnetes Feld.
    • Verwende eine Formel wie =WerteJahr1 - WerteJahr2, um die Differenz zu berechnen.
  4. Füge die Prozentdifferenz hinzu: Um die Differenz in Prozent zu berechnen, kannst du das berechnete Feld wie folgt anpassen:

    = (WerteJahr2 - WerteJahr1) / WerteJahr1
  5. Sortiere die Pivot Tabelle nach den %-Werten: Klicke auf das Dropdown-Menü des entsprechenden Wertes in der Pivot Tabelle und wähle Sortieren nach. Hier kannst du die Sortierung nach den Werten oder nach der Differenz in Pivot festlegen.


Häufige Fehler und Lösungen

  • Pivot Tabelle sortieren nach Wert funktioniert nicht:

    • Stelle sicher, dass du die Werte korrekt in die Werte-Sektion der Pivot Tabelle eingefügt hast. Wenn die Werte nicht korrekt angezeigt werden, kann die Sortierung fehlschlagen.
  • Die Differenz zwischen zwei Spalten wird nicht korrekt berechnet:

    • Überprüfe die Formel für das berechnete Feld. Achte darauf, dass die Felder richtig referenziert sind.
  • Pivot Filter sortieren funktioniert nicht:

    • Wenn der Pivot Filter nicht korrekt sortiert, kann das daran liegen, dass die Daten nicht konsistent sind. Stelle sicher, dass alle Daten in den betreffenden Spalten im gleichen Format vorliegen.

Alternative Methoden

  • Formeln außerhalb der Pivot Tabelle verwenden: Du kannst die Differenz zwischen zwei Spalten auch außerhalb der Pivot Tabelle mit Formeln berechnen.

    • Beispiel: In einer Hilfsspalte kannst du die Formel =B2 - C2 verwenden, um die Differenz zu berechnen, und dann die Hilfsspalte in die Pivot Tabelle einfügen.
  • PowerPivot nutzen: Ab Excel 2010 kannst du PowerPivot verwenden, um komplexere Berechnungen anzustellen und die Daten effizienter zu verwalten.


Praktische Beispiele

  • Beispiel für eine Pivot Tabelle mit Differenz: Angenommen, du hast Verkaufsdaten für die Jahre 2021 und 2022. Du kannst in der Pivot Tabelle die Differenz zwischen den Jahren in Prozent darstellen und nach diesen Werten sortieren.
    • Erstelle ein berechnetes Feld, das den Unterschied zwischen den Verkaufszahlen anzeigt und sortiere die Tabelle nach diesen Werten.

Tipps für Profis

  • Dynamische Daten verwenden: Wenn du häufig Änderungen in deinen Daten hast, formatiere deine Daten als Tabelle. So passt sich die Pivot Tabelle automatisch an neue Daten an.

  • Hilfsspalten verwenden: Nutze Hilfsspalten für komplexe Berechnungen, bevor du die Daten in die Pivot Tabelle einfügst. So behältst du die Übersichtlichkeit und flexiblere Anpassungsmöglichkeiten.


FAQ: Häufige Fragen

1. Wie kann ich die Differenz in Pivot berechnen? Du kannst ein berechnetes Feld verwenden, um die Differenz zwischen zwei Spalten zu berechnen.

2. Warum kann ich meine Pivot Tabelle nicht nach Werten sortieren? Das kann daran liegen, dass die Daten nicht korrekt formatiert sind oder die Werte nicht in der richtigen Spalte platziert wurden. Überprüfe die Platzierung und Formatierung der Daten.

3. Welche Excel-Version benötige ich für PowerPivot? PowerPivot ist ab Excel 2010 verfügbar. Stelle sicher, dass du mindestens diese Version verwendest, um die Funktionen nutzen zu können.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige