Bei Reports von umfangreichen Tabellen, z. B. Produktlisten benötigt das Management Informationen zu Top-Werten, beispielsweise zu den umsatzstärksten und -schwächsten Produkten. Mit Excel 365 lassen sich mithilfe bestimmter Formeln die notwendigen Rangfolgen erstellen.
Kennen Sie das? Sie nutzen ein umfangreiches Reporting-Dashboard, z.B. für den Monatsbericht, der sich dynamisch steuern lässt, wenn man bestimmte Paramater verändert. Wenn das Management sich z.B. für die Top 5 der umsatzstärksten und -schwächsten Produkte interessiert, sind Sie sind also gefordert, nur eine bestimmte Anzahl von Daten darzustellen.
Komprimierte Darstellung von Top-Ansichten
Excel bietet eine Vielzahl von Formeln an, so auch zwei spezielle Formeln, die Ihnen eine vorab definierte Rangfolge von Werten auswerfen kann. Dies sind die Formeln KGRÖSSTE und KKLEINSTE, die Ihnen für komprimierte Darstellungen von Top-Ansichten nützlich sind. In diesem Beispiel (vgl. Bild oben) sehen Sie eine Tabelle mit Artikeln und deren Absatz- und Umsatzzahlen. In einigen Fällen kann diese Liste hunderte bis tausende Artikel enthalten, doch das Management interessiert sich nur für einen kleinen Teil. Die Formeln unterstützen dabei, dies schnell und einfach darzustellen.
Mit KGRÖSSTE und KKLEINSTE Rangfolgen erstellen
In diesem Beispiel möchten wir die Top 3 - Artikel nach Absatzmenge und nach Umsatz darstellen. Dafür verwenden wir die Formel KGRÖSSTE, welche sich wie folgt definiert:
=KGRÖSSTE(MATRIX;k)
Die MATRIX ist dabei der Zellbereich, von dem der n-te größte Wert ausgegeben werden soll. "k" beschreibt eine ganze Zahl, die die Position vom größten Wert bestimmt, d.h. die n-te Position. Für eine Top 3-Darstellung nach Absatzmenge sieht die Tabelle dann wie auf dem nachfolgenden Bild aus.
Die Formel dazu in Zelle D18 lautet wie folgt:
=KGRÖSSTE($D$6:$D$11;1)
In der Formel wird die MATRIX mit D6 bis D11 (rot markiert) dargestellt und wir möchten in der Zelle D18 den größten Wert ausweisen, weswegen für "k" eine "1" eingetragen wird. Das Ergebnis lautet 12.721. Analog dazu wird für die Zellen D19 als "k" eine "2" und in Zelle D20 als "k" eine "3" hinterlegt. Der Formel-Bestandteil MATRIX bleibt in allen drei Fällen die gleiche.
Man kann für die Top-3-Artikel auch analog zur Formel KGRÖSSTE die Formel KKLEINSTE anwenden. Diese ist nahezu identisch und weist den kleinsten Wert in einen Zellbereich aus. Für die nachfolgende Tabelle sieht die Formel in D26 wie folgt aus:
=KKLEINSTE($D$6:$D$11;1)
Ergänzend zu den ermittelten größten und kleinsten Werten, kann man für eine dynamische Darstellung der Artikel dann z.B. den XVERWEIS nutzen, der den jeweiligen Wert in der Ausgangtabelle sucht und ausgibt.
Schwächen der Formeln
Nicht jede Formel in Excel funktioniert immer komplett einwandfrei. So haben die beiden Formeln auch eine gewisse Schwäche. Excel ordnet den definierten Zellbereich im Hintergrund in einer auf- bzw. absteigenden Reihenfolge. Mit "k" wird nur die Position des Wertes ausgewiesen, welcher sich an der n-ten Stelle der Reihenfolge befindet. Nachfolgend eine Darstellung, wenn sich Werte gleichen. Hier gibt die Formel KGRÖSSTE bei "k" = 2 und 3 den identischen Wert aus.
