Rankings und Top-N Auswertungen in Excel Dashboards

Excel RankingTop- und Flop-Listen können mit Cubeformeln sehr effizient aus Power Pivot Datenmodellen und Cubes abgefragt werden.

Im "normalen" Excel können als Alternative neben den bekannten MAX-/MIN-Formeln die kaum bekannten KGRÖSSTE-/KKLEINSTE-Formeln ausgezeichnete Dienste zur dynamischen Abfrage von Rankings aus größeren Datenlisten leisten. Mit der INDEX-/VERGLEICH-Kombinationsformel können wiederum die (Produkt-)Bezeichnungen zu den Rankings dynamisch hinzugefügt werden (eine SVERWEIS-Formel würde in den meisten Fällen versagen). Mit der analytischen Formatierung können die abgefragten Rankings leicht visualisiert werden.

Als Quelldaten steht uns eine einfache, unsortierte Umsatzliste eines 17-teiligen Produktportfolios zur Verfügung, aus der wir die Top-3 und die Flop-3 Produkte dynamisch abfragen möchten:

Excel Ranking 01

Größten und kleinsten Wert ermitteln

Weit verbreitet ist der Einsatz der MAX- und MIN-Formeln, diese liefern uns den größten und den kleinsten Wert aus der Umsatzliste:

Excel Ranking 02

Zusätzlich zum Betrag möchten wir auch wissen, welches Produkt den Max- und welches den Min-Wert stellt. Dazu verwenden wir die äußerst leistungsfähige INDEX-/VERGLEICH-Kombinationsformel, da eine SVERWEIS-Formel nicht funktionieren würde (da bei dieser von der Vergleichsspalte - hier die Wertspalte - nur nach rechts, nicht aber nach links zur Produktspalte, abgefragt werden kann):

Excel Ranking 03

N-größten und n-kleinsten Wert ermitteln

Wie aber kann der zweit- und der drittgrößte Wert in der Liste ermittelt werden? Die KGRÖSSTE-/KKLEINSTE-Formeln liefern hier die perfekten Resultate, die Produktbezeichnungen werden wiederum mit einer INDEX-/VERGLEICH-Kombinationsformel abgefragt:

Excel Ranking 04

Ranginformation in Datenliste

Möchten wir jetzt auch in der Quelldatenliste sehen, welches Produkt welches Ranking einnimmt, so geschieht dies sehr effizient mit der RANG-Formel nach folgendem Muster:

Excel Ranking 05

Die RANG-Formel existiert zwar nach wie vor auch noch in Excel 2016, mit Excel 2010 wurden aber die zwei neuen Formeln RANG.GLEICH und RANG.MITTELW eingeführt. RANG.GLEICH ist von der Funktionsweise her ident mit der "alten" RANG-Formel. RANG.MITTELW liefert bei ex aequo Werten das arithmetische Mittel der Rangplätze während RANG.GLEICH für alle ex aequo Werte den gleichen Rang ausweist.

Exkurs ex aequo Werte:

Die RANG- und die RANG.GLEICH-Formeln liefern bei gleichen Werten auch den gleichen Wert im Ranking (beachten Sie bitte, daß die Ränge 12 und 13 ausgelassen werden und es dann mit 14 weiter geht; RANG.MITTELW würde hier übrigens für alle 3 Treffer den Wert "12" liefern):

Excel Ranking 05a

In vielen Anwendungsfällen ist diese Darstellung aber unerwünscht und schwierig weiterzuverarbeiten. Mit einer angehängten ZÄHLENWENN-Formel kann das Ranking recht einfach eindeutig gemacht werden (das Ranking innerhalb der ex aequo Werte leitet sich dann aus der Zeilenreihenfolge ab):

Excel Ranking 05b

Visualisierung von Rankings

Eine erste Visualisierung kann mit der analytischen Formatierung erreicht werden, hier am Beispiel der orangen Balkenvisualisierung für die Flop-Liste ...

Excel Ranking 06

... mit folgendem Ergebnis:

Excel Ranking 07

(Hinweis: der Umsatzwert für die Neoprenhüllen wurde hier demonstrativ vergrößert, um die Balkenvisualisierung deutlicher zu machen).

Die gemeinsame Skalierung beim Einsatz mehrere bedingter Formatierungen (hier: 3) stellt eine kleine Herausforderung dar, möglicherweise dazu mehr ein anderes Mal.

Quellen:

blogs.office.com/2015/04/08/5-easy-and-powerful-excel-features-you-may-not-know-about

officeblogswest.blob.core.windows.net/wp-content/2015/04/Powerful-Excel-features.xlsx

Zur Ex-Auquo-Thematik der RANG-Formel: hier und auch hier

Teilen Sie Ihre / teile Deine Erfahrungen mit Rankings und Top-/Flop-Visualisierungen und hinterlasse einen Kommentar!

Über den Autor

Blog auf Feedly abonnieren

Kategorien

Verwandte Beiträge

Power BI Camp - Präsenztrainings in Wien und Nürnberg!

Dashboarding mit Power BI, DAX & Datenmodellierung und Power Query. Drei Einzelmodule oder als ganze Trainingswoche - für Einsteiger und Fortgeschrittene!

Termine 2022

Wien: (7./8. Februar 2022)
und 25.-28 April 2022
Nürnberg: (14./15. Februar 2022)
und 9.-12. Mai 2022

Jetzt buchen und Rabatt sichern.

Jetzt buchen!

Leave a Replay

2 Gedanken zu „Rankings und Top-N Auswertungen in Excel Dashboards“

  1. Sehr schöne Zusammenfassung der Möglichkeiten. Wie könnte denn bspw. eine Formel aussehen, wenn man Top-n-Werte aus eine weiteren Top-n-Liste filtern möchte (z.B. Top10-Werte aus Top5-Standorten von x-Standorten)? Hierzu habe ich noch keinen praktischen Ansatz ergooglen können.
    Besten Dank und viele Grüße

    Antworten
  2. Super Beitrag wozu ich eine Frage habe.
    Wenn unter den Produkten ein Produkt dabei ist, dass nicht ins Ranking mit aufgenommen werden soll, wie kann ich dieses ausschließen?
    Dieses Problem bekomme ich nicht gelöst.
    Vielen Dank und beste Grüße

    Antworten

Schreibe einen Kommentar

Kostenlos zum Newsletter anmelden

Ihre Anfrage

Schicken Sie uns Ihre Fragen und Anregungen!