Schnellzugriffe
- So verwenden Sie die RANK-Funktion von Excel
- So verwenden Sie SVERWEIS in Excel zum Erstellen einer Rangliste
Wichtige Erkenntnisse
- Die RANK-Funktion von Excel ist für die Rangfolge von Daten nützlich und kann in verschiedenen Situationen verwendet werden.
- Durch die Kombination der RANK-Funktion mit SVERWEIS können Sie eine Rangliste erstellen.
Die RANK-Funktion von Excel zeigt Ihnen die statistische Rangfolge eines Wertes innerhalb eines Datensatzes an. Sie hat eine Reihe praktischer Anwendungen: Lehrer bewerten ihre Schüler, Sporttrainer bewerten ihre Spieler und es gibt eine ganze Reihe anderer Situationen, in denen Sie Daten bewerten möchten. So verwenden Sie sie.
So verwenden Sie die RANK-Funktion von Excel
Wenn Sie RANK verwenden, wird eine Ziffer zurückgegeben, die Ihnen mitteilt, welchen Rang die Zelle, auf die Sie verweisen, innerhalb der ausgewählten Datenreihe einnimmt. Verwenden Sie dazu die folgende Formel:
=RANK(A,B:C,D)
wobei A der Zellbezug für den Wert ist, den Sie bewerten möchten, B:C der Zellbereich ist, der die Daten enthält, anhand derer Sie den Wert A bewerten, und D entweder „0“ für eine absteigende Bewertung oder „1“ für eine aufsteigende Bewertung ist. Sehen wir uns das genauer an.
In diesem Beispiel haben wir eine Liste von Mitarbeitern und ihre Verkaufsdaten für die ersten sechs Monate des Jahres.
Wir können die RANK-Funktion verwenden, um – Sie ahnen es – die Mitarbeiter anhand ihres Gesamtumsatzes zu ordnen. Dazu müssen wir in unserer vorhandenen Tabelle eine Ranglistenspalte erstellen. Wenn Sie später eine Rangliste für die Rangliste erstellen möchten, ist es wichtig, die Rangliste am Anfang Ihrer Daten zu platzieren, daher muss die Ranglistenspalte in Spalte A stehen. Klicken Sie dazu mit der rechten Maustaste auf die Überschrift der Spalte „A“ (der Teil mit „A“, „B“, „C“ usw.) und klicken Sie auf „Einfügen“.
Auf der linken Seite Ihrer Tabelle wird dann eine neue, leere Spalte angezeigt. Formatieren Sie diese Spalte nach Wunsch und benennen Sie sie mit „Rang“.
Jetzt können wir mit unserer RANK-Formel beginnen.
Klicken Sie auf die erste leere Zelle in Ihrer Rangspalte (in diesem Fall A3) und geben Sie ein:
=RANK(
Klicken Sie nun auf den Zellbezug der Zahl, die Sie ranken möchten, oder geben Sie ihn ein (in unserem Fall I3), und fügen Sie ein Komma hinzu:
=RANK(I3,
Alle Daten, die du in dein Ranking einbeziehen willst, musst du nun mit einem absoluten Bezug und einem Komma referenzieren. In unserem Fall sind das alle Daten aus den Zellen I3 bis I9:
Wenn du hier keinen absoluten Bezug verwendest, sind die Formeln falsch, wenn du die restlichen Ränge für die anderen Mitarbeiter mit AutoFill vervollständigst.
=RANK(I3,$I$3:$I$9,
Geben Sie als Nächstes „0“ ein, wenn Ihre Daten in absteigender Reihenfolge sortiert werden sollen (d. h. der höchste Wert wird zuerst eingestuft), oder „1“, wenn Ihre Daten in aufsteigender Reihenfolge sortiert werden sollen (wobei der niedrigste Wert zuerst eingestuft wird). In unserem Fall soll der höchste Wert zuerst eingestuft werden, also geben wir „0“ ein, schließen die Klammern und drücken die Eingabetaste:
=RANK(I3,$I$3:$I$9,0)
Dies sagt uns nun, dass Kens Gesamtumsatz den dritten Platz belegt. Verwenden Sie abschließend die AutoFill-Funktion von Excel, um die Rangfolge für die verbleibenden Daten in Ihrer Tabelle zu ermitteln.
Ihre Tabelle zeigt Ihnen jetzt klar, welchen Rang jeder Wert innerhalb Ihres Datensatzes einnimmt. Der Übersichtlichkeit halber können Sie Ihr Arbeitsblatt in „Summen“ umbenennen.
Wenn Sie Ihre Rangfolge noch klarer gestalten möchten, damit Sie die Rangfolge auf einen Blick überprüfen können, wenden Sie eine bedingte Formatierung auf Ihre Tabelle an.
Was sind RANK.EQ und RANK.AVG?
RANK.EQ weist Excel an, alle gleichen Werte gemeinsam zu ordnen, während RANK.AVG Excel anweist, die Rangfolge für alle gleichen Werte zu mitteln. Beide folgen genau derselben Syntax und denselben Prozessen wie RANK in Excel.
So verwenden Sie SVERWEIS in Excel zum Erstellen einer Rangliste
Vorausgesetzt, Sie haben die Rangliste bereits mit der oben beschriebenen Methode erstellt, können Sie die Daten jetzt neu anordnen, um eine Rangliste zu erstellen.
Erstellen Sie zunächst ein neues Blatt in Ihrer Excel-Arbeitsmappe, indem Sie auf das Symbol „+“ rechts neben den Registerkartennamen am unteren Rand Ihrer Arbeitsmappe klicken, und benennen Sie es in „LeagueTable“ um.
Erstellen Sie in Ihrem LeagueTable-Blatt die Gliederung für Ihre Rangliste, einschließlich der manuellen Eingabe der Ranglisten in Spalte A. In unserem Beispiel aus dem obigen Abschnitt wissen wir, dass wir sieben Mitarbeiter in unsere Rangliste aufnehmen müssen, also haben wir die Zahlen eins bis sieben eingegeben.
Wir sind nun bereit, die Rangliste zu erstellen. Die ersten Daten, die wir aus unserem Gesamtblatt aufnehmen möchten, sind der Name des Mitarbeiters, der an erster Stelle steht. Dazu müssen wir die SVERWEIS-Funktion von Excel verwenden. Geben Sie in Zelle B3 Folgendes ein:
=SVERWEIS(
Die Funktion SVERWEIS teilt Excel mit, dass Sie Daten aus einer anderen Quelle abrufen. In diesem Fall beziehen wir die Daten aus dem Tabellenblatt „Summen“ unserer Arbeitsmappe.
Als Nächstes möchten wir den Mitarbeiter finden, der insgesamt den ersten Platz belegt. Daher müssen wir auf die Zelle verweisen, die in unserer Rangliste den Rang „1“ enthält (in unserem Fall ist das Zelle A3), gefolgt von einem Komma:
=SVERWEIS(A3,
Wir müssen Excel nun mitteilen, was es sonst noch in seiner SVERWEIS-Berechnung berücksichtigen muss. In diesem Fall möchten wir es anweisen, alle Daten in der bereits erstellten Tabelle „Summen“ zu berücksichtigen. Während Ihr Cursor noch in der Zelle blinkt, in die Sie die Formel eingeben, gehen Sie zurück zu Ihrem Blatt „Summen“ und markieren Sie die gesamte Tabelle. Dadurch wird Ihre Formel wie folgt aktualisiert:
=SVERWEIS(A3,Summen!A3:I9
Jedes Mal, wenn wir diese Formel anwenden, möchten wir, dass auf dieselben Zellen im Blatt „Summen“ verwiesen wird. Machen Sie diesen Verweis also zu einem absoluten Verweis und fügen Sie dann ein Komma hinzu:
=VLOOKUP(A3,Totals!$A$3:$I$9,
Der vorletzte Teil dieser Formel teilt Excel mit, wo es nach den Details suchen soll, die wir in die Zelle einfügen möchten, in die wir gerade tippen. In diesem Beispiel möchten wir den Namen der Person sehen, die an erster Stelle steht.
Unsere Tabelle „Totals“ enthält insgesamt neun Spalten, und die Namen der Mitarbeiter stehen in der zweiten Spalte dieser Tabelle. Daher geben wir die Nummer zwei ein, gefolgt von einem abschließenden Komma:
=SVERWEIS(A3,Gesamt!$A$3:$I$9,2
Und schließlich geben Sie „FALSCH“ ein, um Excel anzuweisen, nach einer genauen Übereinstimmung zu suchen, bevor Sie die Klammern schließen und die Eingabetaste drücken:
=SVERWEIS(A3,Gesamt!$A$3:$I$9,2,FALSCH)
Dies sagt uns nun, dass Regina die Mitarbeiterin mit dem höchsten Rang ist. Verwenden Sie die AutoFill-Funktion von Excel, um die Namen der übrigen Mitarbeiter zu vervollständigen.
Da wir nun die Mitarbeiter in der Reihenfolge basierend auf ihrem Gesamtumsatz aus dem Gesamtblatt sehen können, können wir ihren Gesamtumsatz mit SVERWEIS auf die gleiche Weise addieren.
Dies wäre die Formel, die wir in Zelle C3 einfügen:
=SVERWEIS(A3,Gesamt!$A$3:$I$9,9,FALSCH)
Beachten Sie, dass wir dieses Mal „9“ als vorletzten Teil unserer Formel eingegeben haben, da Excel den Gesamtumsatz aus der neunten Spalte unserer Gesamttabelle erfassen soll.
Vervollständigen Sie die Tabelle, indem Sie die automatische Ausfüllung in Spalte C verwenden.
Anstatt die SVERWEIS-Formel in allen nachfolgenden Spalten manuell einzugeben, nachdem Sie Spalte B vervollständigt haben, können Sie zunächst eine gemischte Referenz verwenden, wobei Spalte A in Ihrer SVERWEIS-Formel gesperrt ist, und dann die automatische Ausfüllung rechts ausführen.
Wenn Sie die Daten in Ihrem Gesamtblatt ändern, wird die Rangliste automatisch aktualisiert, um die Änderungen widerzuspiegeln.
Das war’s! Sie haben jetzt alle Tools, die Sie benötigen, um die RANK-Funktion in Excel zu verwenden und diese mit SVERWEIS zu kombinieren, um eine Rangliste zu erstellen. Wenn Sie sich tatsächlich dafür entscheiden, die Rangliste zu Ihrer Arbeitsmappe hinzuzufügen, können Sie im Anschluss daran Ihr ursprüngliches Gesamtergebnisblatt aufräumen, indem Sie die Spalte mit den Ranglisten ausblenden.