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

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

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
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
.. , - ...
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
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
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
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
.. , - ...
Anzeige
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
.. , - ...
Tolle Datei (Formeln ) ! Danke und owT-Gruß
03.10.2019 19:33:20
robert

40 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige