Rangfunktion in Excel mit Formelbeispielen

Das Tutorial erklärt die Besonderheiten des Funktion Rang in Excel und zeigt, wie Sie in Excel nach verschiedenen Kriterien sortieren, Daten nach Gruppen sortieren, den Perzentilrang berechnen und vieles mehr. Wenn Sie es brauchen Bestimmen Sie die relative Position einer Zahl in einer ZahlenlisteAm einfachsten ist es, die Zahlen in aufsteigender oder absteigender Reihenfolge anzuordnen. Wenn eine Sortierung aus irgendeinem Grund nicht möglich ist, ist eine Bereichsformel in Excel das perfekte Werkzeug, um die Aufgabe zu erledigen.

Excel RANGE-Funktion

La RANK-Funktion in Excel gibt die Reihenfolge (oder den Rang) eines numerischen Werts im Vergleich zu anderen Werten in derselben Liste zurück. Mit anderen Worten: Hier erfahren Sie, welcher Wert unter anderem der höchste, der zweithöchste ist.

Vielleicht können Sie interessiert sein: Excel CHOOSE-Funktion. Formeln und Beispiele

In einer geordneten Liste wäre der Rang einer bestimmten Zahl die Position. Die Range-Funktion in Excel kann den Bereich beginnend mit dem größten Wert (als ob er in absteigender Reihenfolge sortiert wäre) oder dem kleinsten Wert (als ob er in aufsteigender Reihenfolge sortiert wäre) bestimmen. Der RANK-Funktionssyntax von Excel ist folgende:

RANK (Anzahl, Referenz, [Bestellung])

Dónde:

• Anzahl (erforderlich): der Wert, dessen Rangfolge Sie ermitteln möchten.
• Ref (erforderlich): eine Liste numerischer Werte, nach denen sortiert werden soll. Es kann als Zahlenarray oder als Verweis auf die Zahlenliste bereitgestellt werden.
• Bestellung (optional): eine Zahl, die angibt, wie die Werte sortiert werden sollen:
  • Wenn 0 oder weggelassen wird, werden die Werte in absteigender Reihenfolge sortiert, also vom höchsten zum niedrigsten.
  • Wenn es 1 oder ein anderer Wert ungleich Null ist, werden die Werte in aufsteigender Reihenfolge sortiert, also vom niedrigsten zum höchsten.

RANK.EQ-Funktion in Excel

RANK.EQ Es handelt sich um eine verbesserte Version der RANK-Funktion, eingeführt in Excel 2010. Es hat die gleiche Syntax wie RANK und funktioniert mit der gleichen Logik: Wenn mehrere Werte den gleichen Rang haben, wird allen diesen Werten der höchste Rang zugewiesen. (EQ bedeutet „gleich“).

RANK.EQ (Nummer, Referenz, [Reihenfolge])

In Excel 2007 und niedriger sollten Sie immer die RANK-Funktion verwenden. In Excel 2010, Excel 2013 und Excel 2016 können Sie RANK oder RANK.EQ wählen. Es wäre jedoch ratsam, RANK.EQ zu verwenden, da RANK jederzeit ausgesetzt werden kann.

Excel RANGE.AVG-Funktion

RANK.AVG ist eine weitere Funktion für Finden Sie den Bereich in Excel Dies ist nur in Excel 2010, Excel 2013, Excel 2016 und späteren Versionen verfügbar. Es hat die gleiche Syntax wie die anderen beiden Funktionen:

RANK.AVG (Anzahl, Referenz, [Bestellung])

Der Unterschied besteht darin, dass, wenn mehr als eine Zahl den gleichen Rang hat, der durchschnittliche Rang zurückgegeben wird (AVG bedeutet „Durchschnitt“).

4 Dinge, die Sie über RANK in Excel wissen sollten

Hier sind einige Dinge, die Sie bei der Rangfunktion in Excel beachten sollten:

• Jede Bereichsformel in Excel funktioniert nur für numerische Werte: positive und negative Zahlen, Nullen, Datums- und Uhrzeitwerte. Nicht numerische Werte im ref-Argument werden ignoriert.
• Alle RANGE-Funktionen geben für doppelte Werte denselben Bereich zurück und überspringen eine weitere Sortierung, wie im folgenden Beispiel gezeigt.
• In Excel 2010 und höher wurde die RANK-Funktion durch RANK.EQ und RANK.AVG ersetzt. Aus Gründen der Abwärtskompatibilität funktioniert RANK weiterhin in allen Excel-Versionen, ist jedoch möglicherweise in Zukunft nicht mehr verfügbar.
• Wenn die Zahl nicht in der Referenz gefunden wird, würde jede Excel-Bereichsfunktion den Fehler #N/A zurückgeben.

Grundlegende Excel-Bereichsformel (vom höchsten zum niedrigsten Wert)

Um mehr über das Ranking mit der Rank-Funktion in Excel zu erfahren, schauen Sie sich diesen Screenshot an:

Alle drei Formeln sortieren die Zahlen in Spalte B in absteigender Reihenfolge (das Bestellargument entfällt):

In allen Versionen von Excel 2003 – 2016:

=RANG($B2,$B$2:$B$7)

In Excel 2010 – 2016:

=RANK.EQ($B2,$B$2:$B$7)

=RANK.AVG($B2,$B$2:$B$7)

Der Unterschied besteht darin, wie diese Formeln doppelte Werte verarbeiten. Wie Sie sehen, erscheint derselbe Wert zweimal, in den Zellen B5 und B6, was sich auf die nachfolgende Sortierung auswirkt:

• Die Formeln RANK und RANK.EQ geben beiden doppelten Bewertungen den Rang 2. Die nächsthöhere Punktzahl (Daniela) liegt auf dem vierten Platz. Niemand erhält den Rang 3.
• Die RANK.AVG-Formel weist jedem Duplikat hinter den Kulissen einen anderen Rang zu (in diesem Beispiel 2 und 3) und gibt den Durchschnitt dieser Ränge (2.5) zurück. Auch hier ist der dritte Rang niemandem zugeteilt.

So verwenden Sie RANK in Excel – Formelbeispiele

Sie sagen, der Weg zur Exzellenz sei mit Übung gepflastert. Um also besser zu lernen, wie Sie die RANK-Funktion in Excel allein oder in Kombination mit anderen Funktionen verwenden, schauen wir uns Lösungen für einige reale Aufgaben an.

So sortieren Sie in Excel vom Kleinsten zum Größten

Wie im obigen Beispiel gezeigt, verwenden Sie zum Sortieren von Zahlen vom größten zum kleinsten eine der Excel-Bereichsformeln, wobei das Argument order auf 0 gesetzt ist oder weggelassen wird (Standard).

Um eine Zahl nach anderen Zahlen in aufsteigender Reihenfolge zu sortieren, geben Sie 1 oder einen anderen Wert ungleich Null in das optionale dritte Argument ein. Um beispielsweise die 100-Meter-Sprintzeiten von Schülern zu bewerten, können Sie eine der folgenden Formeln verwenden:

= RANG (B2, $ B $ 2: $ B $ 7,1)

=RANK.EQ(B2,$B$2:$B$7,1)

Achten Sie darauf, dass wir den Bereich im Argument sperren ref durch die Verwendung absoluter Zellbezüge, damit sich diese nicht ändert, wenn wir die Formel in die Spalte kopieren.

Dadurch erhält der niedrigste Wert (schnellste Zeit) den ersten Platz und der größte Wert (langsamste Zeit) den niedrigsten Rang von 6. Gleiche Zeiten (B2 und B7) erhalten den gleichen Rang.

So klassifizieren Sie Daten in Excel eindeutig

Wie oben erwähnt, geben alle Excel-Bereichsfunktionen denselben Bereich für Elemente mit gleichem Wert zurück. Wenn Sie das nicht möchten, verwenden Sie eine der folgenden Formeln, um Tie-Break-Situationen zu lösen und jeder Zahl einen eindeutigen Rang zuzuweisen.

Einzigartiges Ranking vom höchsten zum niedrigsten

Um die Mathematikergebnisse der Schüler nur in absteigender Reihenfolge zu sortieren, verwenden Sie diese Formel:

=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1

Einzigartiges Ranking vom niedrigsten zum höchsten

Verwenden Sie diese Formel, um Ihre 100-Meter-Laufergebnisse in aufsteigender Reihenfolge ohne Duplikate zu sortieren:

=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1

Wie diese Formeln funktionieren

Wie Sie bemerkt haben, Der einzige Unterschied zwischen den beiden Formeln ist das order-Argument der RANK.EQ-Funktion: weggelassen für die Sortierung absteigender Werte, 1 für die Sortierung aufsteigend. In beiden Formeln ist es die ZÄHLENWENN-Funktion mit der geschickten Verwendung relativer und absoluter Zellbezüge, die den Zweck erfüllt.

Kurz gesagt, verwenden COUNTIF um herauszufinden, wie oft die zu sortierende Zahl in den vorherigen Zellen, einschließlich der Zahlenzelle, vorkommt. In der oberen Zeile, in der Sie die Formel eingeben, besteht der Bereich aus einer einzelnen Zelle ($B$2:B2). Da jedoch nur die erste Referenz ($B$2) gesperrt wird, ändert sich die letzte relative Referenz (B2) abhängig von der Zeile, in die die Formel kopiert wird.

Daher wird für Zeile 7 der Bereich auf $B$2:B7 erweitert und der Wert in B7 mit jeder der vorherigen Zellen verglichen. Folglich gibt COUNTIF bei allen ersten Vorkommen 1 zurück; und subtrahiere 1 vom Ende der Formel, um den ursprünglichen Bereich wiederherzustellen.

Beim zweiten Vorkommen gibt COUNTIF 2 zurück. Durch Subtrahieren von 1 erhöhen Sie den Bereich um 1 Punkt und vermeiden so Duplikate. Wenn es 3 Vorkommen desselben Werts gibt, würde COUNTIF() – 1 2 zu Ihrer Sortierung hinzufügen und so weiter.

Problemumgehung zum Unterbrechen von Excel RANK-Verbindungen

Eine andere Form von Zahlen in Excel sortieren Die einzige Möglichkeit besteht darin, zwei ZÄHLENWENN-Funktionen hinzuzufügen:

• Die erste Funktion ermittelt, wie viele Werte größer oder kleiner als die zu sortierende Zahl sind, je nachdem, ob absteigend oder aufsteigend sortiert wird.
• Mit der zweiten Funktion (mit dem „erweiternden Bereich“ $B $2:B2 wie im vorherigen Beispiel) erhalten Sie die Anzahl der Werte gleich der Zahl.

Um beispielsweise Zahlen nur von der höchsten zur niedrigsten zu sortieren, würden Sie diese Formel verwenden:

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIF($B$2:B2,B2)

Wie im Screenshot unten gezeigt, wurde der Tiebreaker erfolgreich gelöst und jedem Schüler wird ein eindeutiger Rang zugewiesen:

Klassifizierung in Excel anhand mehrerer Kriterien

Das vorherige Beispiel hat zwei funktionierende Lösungen für eine Situation demonstriert Excel RANK-Tiebreaker. Allerdings mag es unfair erscheinen, dass gleiche Zahlen allein aufgrund ihrer Position auf der Liste unterschiedlich eingestuft werden.

Um Ihr Ranking zu verbessern, möchten Sie möglicherweise ein weiteres Kriterium hinzufügen, das im Falle eines Gleichstands berücksichtigt wird. Fügen wir in unserem Beispieldatensatz die Gesamtpunktzahl in Spalte C hinzu und berechnen wir den Bereich wie folgt:

• Klassifizieren Sie zunächst anhand der mathematischen Punktzahl (Hauptkriterium).
• Bei Gleichstand wird dieser mit der Gesamtpunktzahl gebrochen (sekundäres Kriterium)

Dazu verwenden wir eine reguläre RANK/RANK.EQ-Formel, um den Rang zu ermitteln, und die COUNTIF-Funktion, um ein Unentschieden zu lösen:

=RANK.EQ($B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)

Im Vergleich zum vorherigen Beispiel ist diese Rangformel objektiver: Timothy liegt auf Platz 2, weil seine Gesamtpunktzahl höher ist als die von Julia:

Wie diese Formel funktioniert

Der RANK-Teil der Formel ist offensichtlich und die ZÄHLENWENN-Funktion führt Folgendes aus:

• Das erste Paar Kriterien_Bereich/Kriterium ($B$2:$B$7,$B2) zählt die Vorkommen des Werts, den Sie bewerten. Beachten Sie, dass wir den Bereich mit absoluten Bezügen festlegen, die Kriterienzeile ($B2) jedoch nicht sperren, sodass die Formel den Wert in jeder Zeile einzeln prüft.
• Das zweite Kriterium_Bereich/Kriterium-Paar ($C$2:$C$7, „>“ und $C2) ermittelt, wie viele Gesamtpunktzahlen größer sind als die Gesamtpunktzahl des bewerteten Werts.

Da COUNTIFS mit UND-Logik arbeitet, also nur Zellen zählt, die alle angegebenen Bedingungen erfüllen, gibt es für Timothy 0 zurück, da kein anderer Schüler mit der gleichen Mathematiknote eine höhere Gesamtpunktzahl hat. Daher ändert sich der von RANK.EQ zurückgegebene Rang von Timothy nicht.

Für Julia die Funktion COUNTIF gibt 1 zurück, weil ein Schüler mit der gleichen Mathe-Punktzahl eine höhere Gesamtpunktzahl hat, sodass seine Rangnummer um 1 erhöht wird. Wenn ein weiterer Schüler die gleiche Mathe-Punktzahl und eine niedrigere Gesamtpunktzahl als Timothy und Julia hatte, erhöht sich Ihr Rang um 2 , und so weiter.

Alternative Lösungen zum Sortieren von Zahlen nach mehreren Kriterien

Anstelle der Funktion RANK oder RANK.EQ können Sie COUNTIF verwenden, um die Hauptkriterien zu überprüfen, und COUNTIFS oder SUMPRODUCT verwenden, um einen Tiebreaker aufzulösen:

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)+1

=COUNTIF($B$2:$B$7,»>»&B2)+SUMPRODUCT(–($C$2:$C$7=C2),–($B$2:$B$7>B2))+1

Das Ergebnis dieser Formeln ist genau das gleiche wie oben gezeigt.

So berechnen Sie den Perzentilrang in Excel

In der Statistik ist ein Perzentil der Wert, unter den ein bestimmter Prozentsatz der Werte in einem bestimmten Datensatz fällt. Wenn beispielsweise 70 % der Schüler Ihr Testergebnis erreichen oder darunter liegen, beträgt Ihr Prozentrang 70.

Um den Perzentilrang in Excel zu erhalten, verwenden Sie die Funktion RANK oder RANK.EQ mit einem Argument ungleich Null, um die Zahlen vom niedrigsten zum höchsten zu ordnen und dann den Rang durch die Zahlenanzahl zu dividieren. Die generische Formel für den Perzentilrang in Excel lautet also wie folgt:

RANK.EQ(oberste_Zelle, Rang, 1) / COUNT(Rang)

Um den Prozentrang der Studierenden zu berechnen, sieht die Formel wie folgt aus:

=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)

Damit die Ergebnisse korrekt angezeigt werden, stellen Sie sicher, dass Sie das Prozentformat in den Formelzellen festlegen:

Vielleicht möchten Sie wissen: So fügen Sie in Excel eine Kategorie hinzu

So sortieren Sie Zahlen in nicht benachbarten Zellen

In Situationen, in denen Sie nicht zusammenhängende Zellen sortieren müssen, geben Sie diese Zellen direkt im Argument an ref Ihrer Excel-Bereichsformel in Form einer Referenzvereinigung und blockiert Referenzen mit dem $-Zeichen. Zum Beispiel:

=RANK(B2,($B$2,$B$4,$B$6))

Passen Sie an, um Fehler in unsortierten Zellen zu vermeiden RANK in Funktion IFERROR, Damit:

=IFERROR(RANK(B2,($B$2,$B$4,$B$6)),»»)

Beachten Sie, dass einer doppelten Nummer auch ein Bereich zugewiesen wird, obwohl Zelle B5 nicht in der Formel enthalten ist:

Wenn Sie mehrere nicht zusammenhängende Zellen sortieren müssen, ist die obige Formel möglicherweise zu lang. In diesem Fall wäre es eine elegantere Lösung, einen benannten Bereich zu definieren und in der Formel auf diesen Namen zu verweisen:

=IFERROR(RANK(B2,Bereich),»»)

So sortieren Sie in Excel nach Gruppen

Wenn Sie mit Einträgen arbeiten, die in einer Datenstruktur organisiert sind, gehören die Daten möglicherweise zu mehreren Gruppen und Sie möchten möglicherweise die Zahlen innerhalb jeder Gruppe einzeln sortieren. Die RANK-Funktion von Excel kann diese Herausforderung nicht lösen, also verwenden wir eine komplexere SUMMENPRODUKT-Formel:

Nach Gruppe in absteigender Reihenfolge sortieren:

=SUMPRODUCT((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

Nach Gruppe aufsteigend sortieren:

=SUMPRODUCT((A2=$A$2:$A$7)*(C2>$C$2:$C$7))+1

Dónde:

• A2: A7 sind Gruppen, denen Nummern zugeordnet sind.
• C2: C7 sind Zahlen zur Klassifizierung.

In diesem Beispiel verwenden wir die erste Formel, um die Zahlen in jeder Gruppe vom größten zum kleinsten zu sortieren:

Wie diese Formel funktioniert

Grundsätzlich wertet die Formel zwei Bedingungen aus:

• Überprüfen Sie zunächst die Gruppe (A2=$A$2:$A$7). Dieser Teil gibt ein Array von TRUE und FALSE zurück, basierend darauf, ob ein Bereichselement zur gleichen Gruppe wie A2 gehört.
• Zweitens überprüfen Sie die Punktzahl. Um die Werte vom höchsten zum niedrigsten Wert (absteigende Reihenfolge) zu sortieren, verwenden Sie die Bedingung (C2 < $C$2:$C$11), die für Zellen, die größer oder gleich C2 sind, TRUE zurückgibt, andernfalls FALSE.

Da in Microsoft Excel WAHR = 1 und FALSCH = 0 ist, ergibt die Multiplikation der beiden Matrizen eine Matrix aus 1 und 0, wobei 1 nur für Zeilen zurückgegeben wird, in denen beide Bedingungen erfüllt sind. Dann, SUMMENPRODUKT Es addiert die Array-Elemente 1 und 0, sodass für die größte Zahl in jeder Gruppe 0 zurückgegeben wird. Und addieren Sie 1 zum Ergebnis, um mit der Sortierung bei 1 zu beginnen.

Die Formel, die Zahlen innerhalb von Gruppen vom kleinsten zum größten (aufsteigende Reihenfolge) klassifiziert, funktioniert nach derselben Logik. Der Unterschied besteht darin SUMMENPRODUKT gibt 0 für die kleinste Zahl in einer bestimmten Gruppe zurück, da keine Zahl in dieser Gruppe die 2. Bedingung erfüllt (C2 > $C$2:$C$7). Ersetzen Sie erneut den Nullrang durch den ersten Rang, indem Sie 1 zum Formelergebnis hinzufügen.

Statt SUMMENPRODUKTkönnen Sie Funktion verwenden SUM um die Elemente der Matrix hinzuzufügen. Dies würde jedoch die Verwendung einer Array-Formel erfordern, die durch vervollständigt wird Strg + Umschalt + Eingabetaste. Zum Beispiel:

=SUM((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

So klassifizieren Sie positive und negative Zahlen getrennt

Wenn Ihre Zahlenliste sowohl positive als auch negative Werte enthält, ordnet die RANK-Funktion in Excel sie alle im Handumdrehen ein. Aber was wäre, wenn Sie positive und negative Zahlen getrennt klassifizieren möchten? Verwenden Sie bei Zahlen in den Zellen A2 bis A10 eine der folgenden Formeln, um eine individuelle Rangfolge für die positiven und negativen Werte zu erhalten:

Positive Zahlen absteigend sortieren:

=IF($A2>0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

Sortieren Sie die positiven Zahlen in aufsteigender Reihenfolge:

=IF($A2>0,COUNTIF($A$2:$A$10,»>0″)-COUNTIF($A$2:$A$10,»>»&$A2),»»)

Absteigende negative Zahlen sortieren:

=IF($A2<0,COUNTIF($A$2:$A$10,»<0″)-COUNTIF($A$2:$A$10,»<«&$A2),»»)

Sortieren Sie die negativen Zahlen in aufsteigender Reihenfolge:

=IF($A2<0,COUNTIF($A$2:$A$10,»<«&$A2)+1,»»)

Die Ergebnisse werden in etwa so aussehen:

Wie diese Formeln funktionieren

Schauen wir uns zunächst die Formel an, die positive Zahlen in absteigender Reihenfolge sortiert:

• Im logischen Test der Funktion IF, prüft, ob die Zahl größer als Null ist.
• Wenn die Zahl größer als 0 ist, gibt die ZÄHLENWENN-Funktion die Anzahl der Werte zurück, die höher sind als die sortierte Zahl.

In diesem Beispiel enthält A2 die zweithöchste positive Zahl, für die COUNTIF 1 zurückgibt, was bedeutet, dass es nur eine Zahl größer als diese gibt. Um unsere Sortierung mit 1 und nicht mit 0 zu beginnen, addieren wir 1 zum Formelergebnis, sodass eine Art 2 für A2 zurückgegeben wird.

Wenn die Zahl größer als 0 ist, gibt die Formel eine leere Zeichenfolge ("") zurück. Die Formel, die positive Zahlen in aufsteigender Reihenfolge sortiert, funktioniert etwas anders: Wenn die Zahl größer als 0 ist, wird die erste COUNTIF Ruft die Gesamtzahl der positiven Zahlen im Datensatz ab und die zweite COUNTIF Finden Sie heraus, wie viele Werte höher als diese Zahl sind.

Dann subtrahieren Sie den letzten vom ersten und erhalten den gewünschten Bereich. In diesem Beispiel gibt es 5 positive Werte, von denen einer größer als A1 ist. Subtrahieren Sie dann 2 von 1 und erhalten Sie so einen Rang von 5 für A4. Formeln zur Klassifizierung negativer Zahlen basieren auf einer ähnlichen Logik.

Hinweis: Alle oben genannten Formeln ignorieren Nullwerte, da 0 weder zur Menge der positiven noch zur Menge der negativen Zahlen gehört. Um Nullen in Ihre Sortierung einzubeziehen, ersetzen Sie >0 und <0 durch >=0 bzw. <=0, sofern die Formellogik dies erfordert. Um beispielsweise positive Zahlen und Nullen vom größten zum kleinsten zu sortieren, verwenden Sie diese Formel:

=IF($A2>=0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

So sortieren Sie Daten in Excel und ignorieren dabei Nullwerte

Wie Sie bereits wissen, a RANGE-Formel ist Excel Behandelt alle Zahlen: positive, negative und Nullen. In einigen Fällen möchten wir jedoch nur Zellen mit Daten bewerten, die 0-Werte ignorieren. Für diese Aufgabe gibt es einige mögliche Lösungen, aber die Excel RANK IF-Formel ist die universellste:

Absteigende Bereichszahlen, die Null ignorieren:

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10), RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0)))

Aufsteigende Bereichszahlen, die Null ignorieren:

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10,1) – COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1)))

Wo B2:B10 ist der zu sortierende Zahlenbereich. Das Beste an dieser Formel ist, dass sie sowohl für positive als auch für negative Zahlen wunderbar funktioniert und Nullwerte aus der Klassifizierung herauslässt:

Wie diese Formel funktioniert

Auf den ersten Blick mag die Formel etwas kompliziert erscheinen. Bei genauerem Hinsehen ist die Logik sehr einfach. So ordnet die RANK IF-Formel von Excel Zahlen von der höchsten zur niedrigsten und ignoriert dabei Nullen:

• Das erste IF prüft, ob die Zahl 0 ist und gibt, wenn ja, einen leeren String zurück:

WENN ($B2 = 0, «»,…)

• Wenn die Zahl nicht Null ist, prüft das zweite IF, ob sie größer als 0 ist, und wenn ja, berechnet eine normale RANK/RANK.EQ-Funktion den Rang:

WENN ($B2 > 0, RANG ($B2, $B$2: $B$10),…)

• Wenn die Zahl kleiner als 0 ist, passen Sie die Sortierung nach der Anzahl Null an. In diesem Beispiel gibt es 4 positive Zahlen und 2 Nullen. Für die größte negative Zahl in B10 würde eine Range-Funktion in Excel also 7 zurückgeben. Wir haben jedoch Nullen weggelassen und müssen daher den Bereich um 2 Punkte anpassen. Dazu subtrahieren wir die Anzahl der Nullen vom Bereich:

RANGE($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0))

Ja, so einfach ist das! Die Formel zum Sortieren von Zahlen von der kleinsten zur größten Zahl, wobei Nullen ignoriert werden, funktioniert auf ähnliche Weise und könnte eine gute mentale Übung sein, um ihre Logik abzuleiten.

So berechnen Sie den Bereich in Excel anhand des Absolutwerts

Bei der Arbeit mit einer Liste positiver und negativer Werte kann es erforderlich sein, die Zahlen nach ihren absoluten Werten zu sortieren und dabei das Vorzeichen zu ignorieren. Die Aufgabe kann mit einer der folgenden Formeln gelöst werden, deren Herzstück die ABS-Funktion ist, die das zurückgibt absoluter Wert einer Zahl:

ABS-Bereich nach unten:

=SUMPRODUCT((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7 )) * (A$2:A$7<>»»))+1

ABS-Bereich nach oben:

=SUMPRODUCT((ABS(A2)>=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7 )) * (A$2:A$7<>»»))+1

Daher werden negative Zahlen so klassifiziert, als wären sie positive Zahlen:

So erhalten Sie N größere oder kleinere Werte

Wenn Sie anstelle ihrer Rangfolge eine reelle N-Zahl der größten oder kleinsten Werte erhalten möchten, verwenden Sie die Funktion LARGE bzw. SMALL. Mit dieser Formel können wir beispielsweise die besten drei Schülernoten ermitteln:

=GROß($B$2:$B$7, $D3)

Wo B2: B7 ist die Punkteliste und D3 ist der gewünschte Bereich. Darüber hinaus können Sie mithilfe der Formel Schülernamen abrufen INDEX-MATCH (sofern es keine doppelten Bewertungen in den Top 3 gibt):

=INDEX($A$2:$A$7,MATCH(E3,$B$2:$B$7,0))

Ebenso können Sie die Funktion SMALL verwenden, um die unteren drei Werte zu extrahieren:

=KLEIN($B$2:$B$7, $D3)

Schauen Sie sich an: So kombinieren Sie Excel mit Word: Importieren Sie Daten aus Excel in Word

Pensamientos finales

So erstellen Sie eine Rangfolge mit der Rangfunktion in Excel. Vielen Dank fürs Lesen und wir hoffen, Sie nächste Woche auf unserem Blog zu sehen! Zur Klärung von Zweifeln im Bereich Informatik stehen zahlreiche Tutorials zur Verfügung. Zurück zum Thema: Es gibt viele alternative Möglichkeiten, den Bereich in Excel zu berechnen, jede mit ihren eigenen Besonderheiten. Sie entscheiden, wie Sie arbeiten möchten.