Excel-TippWie Sie den x-größten und x-kleinsten Wert ermitteln
Die Standardfunktionen MIN() und MAX() in Excel sind sehr nützlich, wenn Sie den kleinsten oder größten Wert in einem Bereich oder aus einer Liste ermitteln wollen. Wie aber gehen Sie vor, wenn Sie nicht den größten (kleinsten), sondern den zweit- oder drittgrößten (kleinsten) Wert aus einem Bereich ermitteln wollen? Hierfür gibt es die Funktionen KGRÖSSTE() und KKLEINSTE().
Wie Sie diese Funktionen anwenden und worauf Sie achten müssen, wird am folgenden Beispiel anschaulich.
Wie viele Punkte haben die besten Prüflinge erzielt?
In der folgenden Abbildung sehen Sie die Prüfergebnisse bei einer Ausbildungsprüfung. Gesucht ist zunächst die höchste Punktzahl, die ein Prüfling im Test erzielen konnte. Grundsätzlich würden Sie hier die Funktion MAX() einsetzen. Sie können aber auch die Funktion KGRÖSSTE() wie folgt verwenden:
=KGRÖSSTE(B2:B11;1)
- B2:B11 = Im ersten Argument der Formel erfassen Sie den Bereich, in dem die Prüfergebnisse (erreichte Punktzahl) stehen.
- 1 = Im zweiten Argument erfassen Sie den Rang der Prüfergebnisse, dessen Wert zurückgegeben werden soll; da Sie den größten Wert ermitteln wollen, legen Sie den Wert 1 fest.
Mit beiden Funktionen MAX() und KGRÖSSTE() kommen Sie somit zum Ergebnis 98.
Wie viele Punkte haben die schlechtesten Prüflinge erzielt?
Wollen Sie die niedrigste Punktzahl ermitteln, dann gehen Sie analog mit den Funktionen MIN() oder KKLEINSTE() vor. Sie können das kleinste Prüfergebnis mit KKLEINSTE() wie folgt ermitteln:
=KKLEINSTE(B2:B11;1)
- B2:B11 = Im ersten Argument der Formel erfassen Sie den Bereich, in dem die Prüfergebnisse (erreichte Punktzahl) stehen.
- 1 = Im zweiten Argument erfassen Sie den Rang der Prüfergebnisse, dessen Wert zurückgegeben werden soll; da Sie den kleinsten Wert ermitteln wollen, legen Sie den Wert 1 fest.
Mit den beiden Funktionen MIN() und KKLEINSTE() kommen Sie somit zum Ergebnis 52.
Das x-größte oder x-kleinste Prüfergebnis ermitteln
Die Stärke von KGRÖSSTE() und KKLEINSTE() kommt aber erst zum Vorschein, wenn Sie nicht die höchste oder niedrigste Punktzahl ermitteln wollen, sondern die x-größte oder x-kleinste. Für diesen Fall können Sie nicht mehr die Standardfunktionen MAX() und MIN() einsetzen.
Um den x-größten bzw. x-kleinsten Wert zu ermitteln, müssen Sie lediglich das zweite Argument in den Funktionen KKGRÖSSTE() bzw. KKLEINSTE() von 1 auf den gewünschten Rang (x) anpassen und Sie erhalten das entsprechende Ergebnis. Also:
- =KGRÖSSTE(B2:B11;2) für die zweitbeste Punktzahl
- =KGRÖSSTE(B2:B11;3) für die drittbeste Punktzahl etc.
Entsprechendes gilt auch für die Funktion KKLEINSTE():
- =KKLEINSTE(B2:B11;2) für die zweitniedrigste Punktzahl etc.
In der folgenden Abbildung sehen Sie ein Beispiel für die Anwendung dieser Formeln.
Den jeweiligen Rang (x) können Sie auch variabel auswerten. Tragen Sie beispielsweise in Zelle C3 die Zahl 2 ein und ändern Sie die Formel in E3 zu:
=KGRÖSSTE(B2:B11;C2)
Das Ergebnis in E3 zeigt dann immer den Wert des Rangs, den Sie in C3 eintragen.
Achtung: Wenn Werte in der Liste mehrfach vorkommen!
Die Funktionsweise von KKLEINSTE() und KGRÖSSTE() kann zu unerwarteten Problemen oder unerwünschten Ergebnissen führen, wenn ein Wert mehrfach in einer Liste vorkommt.
Um dieses Problem deutlich zu machen, wurde der Datenbereich (Prüfergebnisse) in der folgenden Abbildung geändert: Die größte und kleinste Punktzahl kommen je zweimal vor. Gesucht wird weiterhin die zweitgrößte und zweitkleinste Punktzahl. Das Ergebnis in der folgenden Abbildung entspricht nicht dem, was viele erwartet hätten.
Nicht 97 und 53 werden als Ergebnis zurückgegeben, sondern die Werte 98 und 52. Dies liegt daran, dass die beiden Funktionen nicht nach Zahlen, sondern nach Positionen suchen.
KKLEINSTE() und KKGRÖSSTE() sortieren die Daten im Datenbereich und geben dann die x-te Position als Ergebnis zurück. Da der größte Wert zweimal vorkommt und der zweite sortierte Wert zurückgegeben werden soll, entspricht das Ergebnis somit nicht dem zweitgrößten Wert, sondern dem Wert, der die zweite Position in den sortierten Daten einnimmt; das ist der Wert 98. Bei KKLEINSTE() trifft dies analog zu.
Wichtig: Diese Funktionsweise sollten Sie immer beachten, wenn Sie KKLEINSTE() und KGRÖSSTE() einsetzen.
KKLEINSTE() und KGRÖSSTE() bei mehrfachen Werten
Wenn Sie aus einer Liste mit Werten, die mehrfach vorkommen, nicht den x-ten Wert der sortierten Reihenfolge suchen, sondern den tatsächlich x-größten oder x-kleinsten Wert, dann benötigen Sie eine spezielle Formel. Sie nutzen diese, wenn Sie also im Beispiel der vorigen Abbildung mit Ihrer Suche nach dem zweitgrößten Wert nicht 98 erhalten wollen (der Wert, der zweimal vorkommt), sondern den Wert 95 – die zweitgrößte Zahl in der Liste.
Die entsprechenden Formeln für die x-größten oder x-kleinsten Werte lauten:
- {=KGRÖSSTE(WENN((HÄUFIGKEIT(B2:B11;B2:B11)>0);B2:B11;"");2)}
- {=KKLEINSTE(WENN((HÄUFIGKEIT(B2:B11;B2:B11)>0);B2:B11;"");2)}
Achtung: Beachten Sie, dass es sich um eine Array-Formel handelt. Sie müssen die Eingabe der Formel in die entsprechende Zelle deshalb mit STRG + UMSCHALTTASTE + ENTER abschließen. Damit entstehen die geschweiften Klammern um die Formel.
Die Formeln prüfen zunächst mit der Funktion HÄUFIGKEIT(), ob ein Wert im Bereich B2:B11 mehrfach vorkommt. Ist das der Fall, wird der Wert nur einmal berücksichtigt. Damit wird für das betrachtete Beispiel der zweitgrößte Wert 95 (statt 98) und der zweitkleinste Wert 53 (statt 52) ermittelt. Das Ergebnis sehen Sie in der folgenden Abbildung.
