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
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:
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!
Funktioniert gut – ABER:
Falls z. B. “Geberit” und “GAM” den identischen Umsatz erzielen, erscheint in der Tabelle aber nur die erst genannte Firma (hier Geberit) – dafür 2x. Wie kann man diese Problematik umgehen?
Gruss
Hi Vince,
Es gibt einen Workaround. Man kann mit einer Hilfsspalte die Zahlen mit ZUFALLSZAHL eine Wert im Bereich von (0.1 bis 0.4) erstellen und addieren. Dann nach der Hilfsspalte sortieren und ausweisen. Allerdings bin ich nicht wirklich ein Fan von dieser Lösung da es beim Summieren für das Total dann wieder Probleme gibt. Eine bessere Lösung kenne ich aber leider auch nicht!
Gruss, Lukas
Moin Lukas, der Tipp mit Hilfsspalte und Zufallszahl ist sehr gut, hatte nämlich gleiches Problem wie Vince.
Lösung: Umsatz+Zufallszahl/100000
Dadurch wird die Zufallszahl so klein, dass diese beim Aufsummieren nicht mehr auffällt.
Salut Lukas
Vielen Dank für die rasche Rückmeldung und für den Workaround-Vorschlag.
So toll wie diese Formel zur Erstellung von Top Listen ist, gibt es keine elegantere Lösung bei identischen Werten trotzdem alle aufzulisten?
Gruss
Christoph
Hi Christoph,
wie oben schon angedeutet, kenne ich leider keine bessere Loesung. Es gibt noch die Moeglichkeit das ganze in einer PivotTabelle abzuhandeln. Dann waeren alle Werte ausgewiesen.
Falls Du noch eine weitere Loesung findest lerne ich die gerne kennen!
Gruss, Lukas
Hallo zusammen,
ich verstehe das mit der Zufallszahl nicht.
Mit diesem Verfahren ist doch nicht gewährleistet, dass bei gleichen Ergebnissen eine andere Zahl addiert wird? (ODer etwa doch???)
Wie der Name schon sagt, handelt es sich um eine „Zufalls“-Zahl, die addiert wird.
Und wie der Zufall manchmal so will, kann das auch mal die gleiche Zahl sein.
So kann das Problem meiner Meinung nach nicht gelöst werden.
Habe aber auch keine bessere Lösung gefunden.
Bitte nicht falsch verstehen, das soll nur ein Hinweis auf die Gefahr bei dieser Variante sein.
Gruß
Björn
Hi Björn, es ist zwar theoretisch Möglich dass die generierte Zufallszahl genaugleich ist, aber ich denke die Chance dass dies eintrifft ist verschwindend klein. In der Praxis dürft die Lösung also tauglich sein.
Es ist meines Erachtens eine Lösung die zu 99.99% funktionieren wird. Eine 100% Variante kenne ich auch nicht. Falls Du eine findest, melde Dich doch bitte wieder, denn man lernt nie aus :- )
Gruss aus der Schweiz, Lukas
Hallo Lukas,
das Risiko ist zwar gering, ich würde es aber so nicht machen, da meine Tools in der Firma von vielen Leuten in unterschiedlichen Werken weltweit eingesetzt werden.
Und da ist mir eigentlich jedes Risiko zu groß ;-)
Ich habe mittlerweile auch eine Lösung gefunden, die zwar eine Hilfsspalte benötigt, aber das stört mich nicht, da ich die ausblenden kann.
Da ich mir das aus verschiedenen Foren zusammenkopiert habe, und ehrlich gesagt die einzelnen Formeln nicht erklären kann, möchte ich darum bitten, meine Erklärungsversuch nicht zu sehr zu kritisieren ;-)
Also, die Zahlen in der richtigen Reihenfolge stehen in Deinem Beispiel ab Zelle F3 nach unten.
In Spalte G (Hilfspalte) lasse ich mir nur die Anzahl anzeigen, zum wievielten Mal ein Wert in F vorkommt.
In G3 steht also eine 1, da der Wert in F3 der 1. Überhaupt ist und somit auch zum ersten Mal vorkommt.
In G4 steht dann diese Formel:
=WENN(F4=F3;G3+1;1)
Wenn sich also der Wert wiederholt, wird der Zähler um 1 erhöht, sonst kommt wieder eine 1.
Das war noch der einfache Teil.
In E3 soll jetzt der passende Firmenname erscheinen, das funktioniert mit folgender Formel:
{=INDEX($A$2:$A$51;KKLEINSTE(WENN($B$2:$B$51=F3;ZEILE($1:$50));G3))}
Die Formel muss als Matrix-Formel eingegeben werden, das heißt die geschweiften Klammern nicht von Hand eingeben.
Die Formel so eingeben:
=INDEX($A$2:$A$51;KKLEINSTE(WENN($B$2:$B$51=F3;ZEILE($1:$50));G3))
Und dann mit Ctrl+Shift+Enter bestätigen. Excel setzt dann die geschweiften Klammern von selbst.
Diese Formel ist etwas tückisch… wie gesagt, verstehen kann ich sie nicht, aber ich kann vielleicht noch ein paar Hinweise geben, worauf man achten muss.
Beschreibung der einzelnen Bereiche:
$A$2:$A$51 Ergebnis-Bereich, in dem die Namen stehen
$B$2:$B$51 Such-Bereich, die Werte (Umsätze)
F3 Suchfeld, nach welchem im Suchbereich gesucht wird
ZEILE($1:$50) WICHTIG! Ergebnis-Bereich und Such-Bereich müssen gleich viele Zeilen haben (im Beispiel von 2-51 50 Zeilen)
Hinter „Zeile“ muss immer aber 1 begonnen werden, also bei 50 Zeilen dann ZEILE($1:$50)
Auch wenn Such/Ergebnisbereich erst bei Zeile 597 anfangen, muss hier trotzdem bei 1 angefangen werden.
G3 Zähler, gibt an, die wievielte Fundstelle verwendet werden soll.
Und noch ein Hinweis zum Schluss.
Diese Formel kann nicht einfach auf einen ganzen Bereich kopiert werden.
Entweder „nach unten ziehen“ oder in jede Zelle einzeln einfügen.
Ich hoffe, das hilft dem ein oder anderen weiter.
Gruß
Björn
Hi Björn! Super dass Du dran geblieben bist… die Antwort ist einleuchtend wenn man Sie einmal gesehen hat. Im Grunde genommen macht Sie das Selbe wie meine Formel, ausser dass es die Werte zählt anstelle einer fiktiven Differenz zu generieren, und somit die doppelten Werte erkennt. Man lernt nie aus! Ich werde, sobald ich etwas Zeit habe, Deine Antwort im Artikel integrieren… natürlich mit Referenz auf Deine Arbeit!
Gruss, Lukas
PS: Was erstellst Du denn so für Tools. Schreib mir doch mal ein Mail!
Hallo Lukas,
ich habe eine Liste mit 600 Verkäufern. In dieser sind vom Jahr 2013 die einzelnen Monatsumsätze. Hier ein Beispiel:
Jahr Monat Verkäufer Nr. Verkäufer Name Umsatz
2013 10 (Oktober) 2 Mustermann, Marco 24,6
2013 12 (Dezember) 2 Mustermann, Marco 42,5
2013 01 (Januar) 4 Beispiel, Martin 61,51
2013 02 (Februar) 4 Beispiel, Martin 58,59
nun möchte ich ein Ranking erstellen in dem die summierten Jahresumsätze der einzelnen Verkäufer die Rankingbasis darstellen.
wie muss ich deine Formel dahingehend verändern? Vielen Dank für Deine Hilfe.
Gruß Kalle
Hallo, ist es möglich nach der Erstellung der Top 10 – zum Beispiel Top Ten Umsatz 2013 eben diese Rangfolge auf eine andere Tabelle zu übernehmen? Ich möchte eine Gegenüberstellung der Jahre 2012 – 2013 unserer Topkunden erzeugen.
Ich wäre für jegliche Tips sehr dankbar!
MfG Philip
Hi Philip, ich denke der SVERWEIS müsste Dir bei dem Problem weiterhelfen! Gruss, Lukas
Hallo,
ich habe das Problem gleicher Werte wie folgt gelöst:
Rohdaten liegen in folgenden Zellen:
Namen: B35:B187
Werte: C35:C187 (verlinkt mit anderem Tabellenblatt + (ZEILE()/1000000)
–> dadurch sind gleiche Werte nicht wirklich gleich
Rang: D35:D387
Außerdem stehen in B4:B14 die Ränge 1-11 (also einfach die Zahlen 1-11)
Zusatzerklärungen: Meine Zahlen sind ganzzahlig. Ansonsten müsste man das unten mittig stehende “(1/2)” durch eine deutlich kleinere Zahl ersetzen. Außerdem will ich nur die TOP10 soweit angezeigt bekommen, wie sie auch eindeutig sind, also nicht ranggleich mit Platz 11 ff.
Sprich: Wenn der 3. – 12. Platz etwa den selben Wert haben, möchte ich die nicht angezeigt bekommen, sondern nur Platz 1 und 2.
Ziel ist es nun, oben im Arbeitsblatt die TOP-10 mit Namen und Wert anzuzeigen. Da dieses jährlich neu für zahlreiche verschiedene Spalten erfolgen soll, wollte ich mit einer automatischen Formel arbeiten, um den jährlichen Sortieraufwand zu sparen.
Im Zellbereich C4:C13 mit den TOP10-Werten:
=WENN((INDEX(C$35:C$187;VERGLEICH($B4;D$35:D$187;0)))-(INDEX(C$35:C$187;VERGLEICH($B$14;D$35:D$187;0)))<(1/2);"ranggleich";(INDEX(C$35:C$187;VERGLEICH($B4;D$35:D$187;0))))
Erklärung, was Excel da machen soll: Überprüfe, ob der Wert im Bereich C$35:C$187, der den in $B4 stehenden Rang (z.B. "3") hat (welcher ja in D$35:D$187 steht) sich um weniger als 1/2 von dem Wert des in $B14 stehenden Ranges unterscheidet (ist bei mir Rang 11, mit dem ja abgeglichen werden soll, ob ein Unterschied zwischen Platz X und und Platz 11 existiert).
Falls ja (deshalb "WENN"): schreibe "ranggleich" (bedeutet also, wenn Platz 3 mit Platz 11 einen geringeren Unterschied als 1/2 hat, möchte ich das ja nicht wissen. Warum 1/2? Durch die Hilfe mit "Wert + ZEILE()/1000000" sind die Werte ja nicht mehr exakt gleich, sondern unterscheiden sich um wenige Millionstel. Man könnte die 1/2 genausogut mit 1/1000 oder 1/500000 angeben, wenn die Originalwerte einige Kommastellen haben.
Zurück zur Formel: die erste WENN-Formel geht dann nach "ranggleich" weiter, indem ich Excel sage, er soll mit im Falle eines größeren Unterschiedes (also: Werte sind nicht ursprünglich gleich) dann doch bitte den Wert, den er am Anfang bereits gesucht hat, anzeigen.
Im Zellbereich D4:D13 mit den zugehörigen Namen:
=WENN((INDEX(C$35:C$187;VERGLEICH($B4;D$35:D$187;0)))-(INDEX(C$35:C$187;VERGLEICH($B$14;D$35:D$187;0)))<(1/2);"x";(INDEX($B$35:$B$187;VERGLEICH($B4;D$35:D$187;0))))
Die Formel unterscheidet sich nur minimal von der ersten: Er soll wieder die Werte, die zu den angegebenen Rängen gehören, vergleichen, nur in diesem Fall soll er mir bei fehlendem Unterschied "x" statt "ranggleich" schreiben und bei existierendem Unterschied natürlich nicht den Wert (INDEX(C$35:C$187) wie oben rechts in der zweiten Zeile) sondern den Namen (INDEX($B$35:$B$187)) sagen.
Ich weiß, es klingt kompliziert, aber wenn man sich mal kurz reindenkt, versteht man es ;-)
Hoffe, ich konnte euch damit vielleicht weiterhelfen
Und wer es einfacher mag und auch gleichrangige bis zu einem bestimmten Punkt angezeigt bekommen möchte:
INDEX([Bereich der Werte+ZEILE()/1000000];VERGLEICH([gesuchte Rang-Nummer];[Bereich der zu den Werten gehörigen Ränge];0)) –> gibt den Wert an
INDEX([Bereich der Namen];VERGLEICH([gesuchte Rang-Nummer];[Bereich der zu den Werten gehörigen Ränge];0)) –> gibt den Namen an
Alles in eckigen Klammern muss durch die entsprechenden Zellbereiche ersetzt werden. Beispiel für die erste Formel: Von $A$1:$A$20 die Werte + ZEILE()/1000000 schreiben/verlinken. Für “gesuchte Rang-Nummer” entweder per Zellbezug oder als Zahl die Ränge von 1 – 20 schreiben. Für die letzte eckige Klammer z.B. $B$1:$B$20 schreiben. In B1:B20 über RANG(A1;$A$1:$A$20;0) die Ränge berechnen.
Dann sieht die erste Formel so aus: INDEX(A$1:A$20;VERGLEICH(1;B$1:B$20;0))
Wie würde das das Basisszenario funktionieren, wenn zusätzlich 1 Kriterium erfüllt sein sollte? Bspw. Kundenstandort (Nord / Süd). Aufgabenstellung also: Top 10 Kunde in der Region Nord nach Umsatzstärke
Der frage schließe ich mich an!
Formel: =INDEX(C:C;VERGLEICH(F2&F3;A:A&B:B;0))
=> Gibt den Wert aus C:C zurück, der die Kriterien aus F2 und F3 in Spalte A:A und B:B erfüllt
Formel: =INDEX($A:$C;VERGLEICH(KGRÖSSTE($C:$C;ZEILE($A1));$C:$C;0);SPALTE(C1))
=> gibt wie oben Beschrieben aus Matrix A:C den größten wert aus C:C zurück
Die Fusion beider:
Formel: =INDEX($A:$C;VERGLEICH(KGRÖSSTE($C:$C;ZEILE($A1))&$F$11;$C:$C&$A:$A;0);SPALTE(C$1))
=> gibt mir komscherweise den Größten Wert aus, für Top2/Top3 kommt #NV.
Ändere ich die Ursprungsmatrix und setzte einen Wert der größer als der bisherige Top1 ist wird Top1 zu #NV und Top1alt steht in Top2 (stimmt ja auch).
Hab natürlich alles als Matrix abgeschlossen (Strg+Umschalt+Enter)
Bin hier echt am verzweifeln….
Hier ein Link zu meiner Test-Excel
https://www.dropbox.com/s/5nim8izaxdbpcmj/Top-Auswertung.xlsx?dl=0
Statt einer Zufallszahl könnt Ihr auch +ZEILE()/10000 addieren
Hallo Zusammen
Erstmals: Vielen Dank für den Artikel hier. Er hat mir gerade sehr viel Zeit gespart.
Ich habe das Problem mit den gleichen Werten wie folgt gelöst.
Anstatt einer Zufallszahl in der Hilfsspalte habe ich einfach in der Hilfsspalte eine sehr kleine (aufsteigende Zahl gewählt) und diese zum Originalwert addiert.
Das heist, in der ersten Zeile der Hilfsspalte habe ich 0.000000001 eingetragen, in der zweiten Zeile 0.000000002 usw.
Somit ist es beinahe unmöglich, dass der unwarscheinliche Fall der “doppelten Zufallszahl” eintritt, da in jeder Zeile eine andere Zahl steht.
Achtung: Diese Variante funktioniert nur, sofern die Originalwerte ohne Dezimalstellen vorhanden sind, ansonsten besteht die kleine Gefahr, dass es trotzdem noch doppelte Werte gibt.
Hoffe ist verständlich beschrieben.
Gruss
Tolle Lösung, aber leider funktioniert sie nur wenn ich keine Leerzeilen in meiner Ausgangstabelle habe. Hat irgendwer eine Idee was man dann macht?
Die Formel oben ist sehr gut. Wie kann ich diese Index-Formel erweitern? Mit der Formel konnte ich die Top 10 Kunden auf Ebene unseres Unternehmens ermitteln. Nun gibt es aber für die Kunden auch 8 Vertriebsmitarbeiter, für die ich die Top 10 eigentlich ermitteln möchte. Der Name des jeweiligen Vertriebsmitarbeiters steht auch in einer eigenen Spalte der Quelltabelle.
Hier die Index-Formel für das Gesamtunternehmen, die funktioniert:
INDEX(‘Daten – detailliert’!$B$4:$B$11487;VERGLEICH(KGRÖSSTE(‘Daten – detailliert’!$L$4:$L$11487;$Q53);’Daten – detailliert’!$L$4:$L$11487;0);SPALTE(‘Daten – detailliert’!A1))
nun ergänzt um den Vertriebsmitarbeiter und dann erhalte ich als Ergebnis “#NV”, obwohl :
=SVERWEIS($A$1;’Daten – detailliert’!$B$4:$C$11487;INDEX(‘Daten – detailliert’!$B$4:$B$11487;VERGLEICH(KGRÖSSTE(‘Daten – detailliert’!$L$4:$L$11487;$Q53);’Daten – detailliert’!$L$4:$L$11487;0);SPALTE(‘Daten – detailliert’!A1));FALSCH)
In der Quelltabelle “Daten – detailliert” steht in Spalte B der Kundenname und in Spalte C der Vertriebsmitarbeiter. Weiß jemand, wo der Bug in meiner Formel ist?