Größte und kleinste Werte mit LARGE und SMALL

MAX gibt Ihnen den einzelnen größten Wert und MIN den einzelnen kleinsten. Was aber, wenn Sie den zweithöchsten oder den drittniedrigsten Wert benötigen?

Genau dafür sind LARGE und SMALL gedacht. Damit können Sie den n-größten oder n-kleinsten Wert aus einem Bereich abrufen – die Grundlage für Berichte zu den besten N bzw. schlechtesten N.

LARGE(range, k) gibt den k-größten Wert zurück. Das zweite Argument k bestimmt die Position: 1 ist der größte, 2 der zweitgrößte Wert und so weiter.

Damit entspricht LARGE(A2:A20,1) dem Ergebnis von MAX(A2:A20), während LARGE(A2:A20,2) den zweitbesten Wert liefert.

SMALL(range, k) funktioniert wie LARGE, beginnend mit dem kleinsten Wert. SMALL(A2:A20,1) entspricht MIN(A2:A20), und SMALL(A2:A20,3) gibt den drittkleinsten Wert zurück.

Verwenden Sie SMALL, um die schwächsten Leistungen, die günstigsten Optionen oder die frühesten Datumswerte in einem Datensatz hervorzuheben.

Um die drei größten Werte aufzulisten, kombinieren Sie drei LARGE-Formeln mit k = 1, 2, 3. Eine übersichtlichere Vorgehensweise besteht darin, auf eine Hilfszahl zu verweisen, sodass Sie die Formel nach unten ausfüllen können.

Wenn D2 den Wert 1, D3 den Wert 2 und D4 den Wert 3 enthält, erzeugt diese eine Formel, nach unten ausgefüllt, die vollständige Top-3-Liste.

Mit der Funktion ROW können Sie k automatisch erzeugen und benötigen daher keine Hilfsspalte. ROW()-1 ergibt in der ersten Zeile 1, in der nächsten 2 und so weiter.

Nach unten ausgefüllt erstellt die Formel eine Rangliste ohne zusätzliche Einrichtung. Passen Sie den Versatz an die Startposition Ihrer Liste an.

In modernen Versionen von Excel und Google Sheets können Sie die gesamten fünf besten Werte mit einer einzigen Formel ausgeben, indem Sie SEQUENCE als k-Argument verwenden. SEQUENCE(5) erzeugt 1, 2, 3, 4, 5.

LARGE gibt dann fünf Werte auf einmal zurück und füllt sie automatisch nach unten aus. Eine Formel, eine vollständige Rangliste.

Eine häufige Frage im Geschäftsalltag lautet: Welchen Beitrag leisten meine drei wichtigsten Kunden? Umschließen Sie LARGE mit SUM und verwenden Sie SEQUENCE (oder eine Arraykonstante), um die Top-Werte zu summieren.

Die folgende Formel addiert die drei größten Werte im Bereich und liefert den gemeinsamen Beitrag der Top 3 in einer einzigen Zelle.

Wenn k kleiner als 1 oder größer als die Anzahl der Zahlen im Bereich ist, geben LARGE und SMALL den Fehler #NUM! zurück.

Wenn Sie beispielsweise in einer Liste mit nur 19 Zahlen den 30.-größten Wert anfordern, schlägt die Berechnung fehl. Fangen Sie dies in Berichten ab, indem Sie den Aufruf in IFERROR einschließen.

Ein Wert allein ist weniger aussagekräftig, als zu wissen, wer ihn erzielt hat. Kombinieren Sie LARGE mit INDEX und MATCH, um den Namen abzurufen, der dem höchsten Wert zugeordnet ist.

Damit wird die größte Verkaufszahl ermittelt, ihre Zeile gefunden und der zugehörige Name aus Spalte A zurückgegeben, sodass in Ihrem Bericht „Top seller: Maria“ steht.

Wie die anderen statistischen Funktionen berücksichtigen LARGE und SMALL nur Zahlen. Text und leere Zellen im Bereich werden übersprungen und beeinflussen daher nicht, welcher Wert der n-größte ist.

Sie können die Funktionen daher problemlos auf eine Spalte anwenden, die eine Überschriftenzeile enthält. Achten Sie jedoch auf Zahlen, die versehentlich als Text gespeichert wurden, da sie ignoriert werden und dadurch den von LARGE oder SMALL zurückgegebenen Wert verändern können.

Sie haben monatliche Verkaufszahlen in B2:B20. Die Geschäftsleitung möchte die drei besten und drei schlechtesten Monate sehen.

• Beste drei: =LARGE($B$2:$B$20,{1,2,3})
• Schlechteste drei: =SMALL($B$2:$B$20,{1,2,3})

Platzieren Sie jede Formel in einer Zelle, in die sie überlaufen kann, und schon erhalten Sie eine Übersicht über die besten und schlechtesten Werte für Ihr Dashboard.

Sie können nun über MAX und MIN hinausgehen:

• LARGE(range,k) gibt den k-größten Wert zurück; SMALL(range,k) den k-kleinsten.
• k = 1 entspricht MAX bzw. MIN; erhöhen Sie k für die nächstbesten Werte.
• Übergeben Sie SEQUENCE oder ein Array wie {1,2,3}, um eine Top-N-Liste mit einer einzigen Formel zu erstellen oder zu summieren.
• Kombinieren Sie die Funktion mit INDEX-MATCH, um den Wert zu beschriften, und schließen Sie sie in IFERROR ein, um k-Werte außerhalb des gültigen Bereichs abzufangen.