Größten Wert, N-ten größten Wert und andere davor finden? (unter Ignorieren von Duplikaten)

Question

Ich habe also beispielsweise eine Spalte A, die folgendermaßen aussieht:

I
15
0
3
15
M
8
0
8
21
Q
0
5
0
0

Den größten Wert erreiche ich mit=MAX(A1:A100) Den kleinsten Wert finde ich mit=KLEIN(A1:A100,1)

Wie finde ich den vor dem größten und den davor? Ich kann „Large“ verwenden, um den N-ten Wert aufzurufen, aber woher weiß ich dann, wie viele N-Werte die Funktion zählt, damit ich weiß, welche Positionen sie in Bezug auf die letzten 3 (den größten und die 2 davor) verwendet?

Duplikate ignorieren – im Beispiel ist der größte 21, der davor 15 und davor 8. Wenn ich =LARGE(A1:A100,3) eingebe, wird 15 zurückgegeben, da es zwei davon in der Spalte gibt. Und ich suche nach der 8 als drittgrößtem Wert.

Nachfolgend finden Sie eine Liste der hilfsbereiten Leute im Internet, die eine PivotTable und eine Array-Formel-Lösung bereitgestellt haben. Ich konnte schließlich auch eine einfache Formellösung erstellen.

Sie können VBA für den Job bereitstellen, falls Sie darauf bestehen, dass wir alles haben, ansonsten ist schon genug vorhanden.

Answer 1

Verwenden Sie eine PivotTable, um schnell eine deduplizierte Version Ihrer Liste zu erhalten (fügen Sie einfach Ihre Spalte hinzu zuZeilenbeschriftungen), und verwenden Sie dann LARGEund SMALLFunktionen auf der deduplizierten Liste:

=LARGE(D4:D9,3)

Wenn Sie möchten, können Sie die PivotTable aufräumen, indem Sie dieGesamtsummeund UmschaltenFeldüberschriftenaus, sodass nur Ihre Deduplizierungsliste angezeigt wird.

Answer 2

Mit Daten in der SpalteAwie:

InB1eingeben:

=MAX(A:A)

InB2Geben Sie die Array-Formel ein:

=MAX(IF(A$1:A$10<B1,A$1:A$10))

Dann kopierenB2so weit nach unten, wie Sie möchten.

Array-FormelnCtrlmuss mit + Shift+ Enterund nicht nur mit der Taste eingegeben werden Enter.

Answer 3

Bei all der tollen Hilfe, die ich bekommen habe, habe ich darüber nachgedacht, ob ich das machen kann, was die Pivot-Tabelle macht, und bin auf Folgendes gestoßen: (sogar alleine, LOL)

=IF(COUNTIF($A$1:A1,A1)<=1,A1,"")

Das Erstellen einer parallelen Spalte zu den Daten von Interesse löst das Problem. (z. B. in C1 einfügen und nach unten kopieren)

Es stellt die anfängliche Liste mit Duplikaten bereit, auf der Sie große und kleine Werte aufbauen können. Als einfache Lösung sollte es nicht mit flüchtigen Vorgängen in Ihren Dokumenten in Konflikt geraten. Und ich für meinen Teil kann ohne VBA leben. Alle unerwarteten Wendungen sollten mit IFERROR eingedämmt werden können, falls gemischte Datentypen vorhanden sind.

Answer 4

@helena4 Versuchen Sie diese Array-Formel

=LARGE(IF(A1:A15 < LARGE(A1:A15,1),A1:A15),3)

Dann drückenCtrl+Shift+Enter

die Duplikate werden ignoriert.