Top Ten Liste mit einer Formel erstellen

image

Top Ten Listen sind nicht nur auf dem Internet beliebt sondern auch in Management Berichten. Die zehn größten Kunden, die besten Verkäufer, die Teuersten Projekte, etc.

Damit man die Liste nicht immer wieder von neuem sortieren und einfügen muss kann man diese auch mit einer einzigen Formel automatisch erstellen! In unserem Beispiel habe ich eine Liste mit Schweizer Firmen und deren fiktive Umsätze.

Wir möchten nun mit einer einzigen Formel aus dieser Liste automatisch die Top Ten der Firmen mit den höchsten Umsätzen erstellen! Wer sich das Beispiel anschauen möchte in Excel kann die Top Ten Liste herunter laden:

Formel Syntax

image

Wir schauen uns die Formel in Zelle E3 an da diese einfach in den Bereich E3:F12 kopiert wird.

=INDEX(Kunde;VERGLEICH(KGRÖSSTE(Umsatz;ZEILE(A1));Umsatz;0);SPALTE(A1))

Ich habe die Liste mit zwei definierten Namen versehen:

SNAGHTML2a8d2f5

1. Den Umsatz gemäß Rang zurückgeben

Der Kern dieser Formel liegt in der Mitte bei:

KGRÖSSTE(Umsatz;ZEILE(A1))

Mit KGRÖSSTE können wir definieren welcher Wert zurückgegeben werden soll. Da die Formel automatisch alle Werte im Bereich Umsatz misst und sortiert, können wir angeben dass wir jeweils den 1.-10. Wert haben wollen.

An erster Stelle wollen wir natürlich den größten Wert anzeigen. Aber unsere Formel soll keine festen Werte enthalten da wir es sonst immer wieder anpassen müssten beim kopieren. Deshalb benutzen wir ZEILE(A1) welches beim kopieren automatisch einen Wert zurück gibt anhand der Zeile auf die verwiesen wird. A1 = 1, A2, =2, etc.

2. Die Stelle des gesuchten Umsatzes ausfindig machen im Bereich

VERGLEICH(KGRÖSSTE(Umsatz;ZEILE(A1));Umsatz;0)

Da wir den Namen der Firma wollen und nicht einfach den Wert, benutzen wir VERGLEICH. KGRÖSSTE liefert uns den richtigen Wert gemäß Rang in der Liste. Mit VERGLEICH suchen wir die Stelle in der Liste in der dieser Wert auftaucht. In unserem Beispiel liefert Verweis den Wert 8, also die 8. Stelle in der Liste Umsatz.

3. Den Namen der Firma zurückgeben

=INDEX(Kunde;VERGLEICH(KGRÖSSTE(Umsatz;ZEILE(A1));Umsatz;0);SPALTE(A1))

Die richtige Stelle haben wir dank VERGLEICH herausgefunden. Diesen Wert können wir nun in INDEX einsetzen und in dem Bereich Kunde, welcher den Firmen Namen enthält, ansteuern und zurückgeben.

Das letzte Argument von INDEX gibt die Spalte an in der der gesucht wird. Weil wir in unserem Beispiel sowohl den Namen wie auch den Umsatz angeben wollen, haben wir hier SPALTE(A1) angegeben. So verschiebt sich die Spalte in der gesucht wird wiederum automatisch ohne dass wir feste Werte einsetzen müssen wenn wir die Formel nach rechts kopieren.

Das Resultat für die Zelle E3 ist Schindler!

Formeln Benutzt:

INDEX, VERGLEICH, KGRÖSSTE, ZEILE, SPALTE

Fragen zum Thema sind willkommen! Einfach Kommentar schreiben!