Excel -Formel, um die Top 3, 5, 10 Werte in der Spalte oder Zeile zu finden

In diesem Tutorial wird gezeigt, wie die Top -N -Werte in einem Datensatz effizient lokalisiert werden und zugehörige Daten mithilfe von Excel -Formeln abgerufen werden können. Unabhängig davon, ob Sie die höchsten, niedrigsten oder diejenigen, die spezifische Kriterien erfüllen, benötigen dieser Leitfaden Lösungen.

• Finden von oberen/unteren Werten
• Auffinden von Top -Werten in Zeilen
• Abrufen von Übereinstimmungen für die größten/kleinsten Werte
• Umgang mit Duplikationen bei Top -Wert -Suchen
• Einbeziehung von Kriterien für die Top -Value -Auswahl
• Verwendung der Filterfunktion für Top -N -Werte

Excel -Formeln für Top 3, 5, 10 oder Nwere

Um die höchsten N -Werte zu identifizieren, kombinieren Sie die LARGE und ROWS :

=LARGE(values, ROWS(A$2:A2))

Wenn Sie beispielsweise die oberen N -Werte in den Zellen B2: B12 finden, geben Sie diese Formel in die gewünschte Ausgangszelle (z. B. D2) ein, um die erforderliche Anzahl von Zellen abzudecken (3 für Top 3, 5 für Top 5 usw.).

Die LARGE Funktion identifiziert den k-ten größten Wert, während ROWS(A$2:A2) den Wert 'k' dynamisch (1, 2, 3 ...) erzeugen. Die absolute Referenz A$2 hält die Startzeile konstant, während die relative Referenz A2 mit jeder Zellkopie auferlegt.

Excel -Formeln für untere n -Werte

Um die niedrigsten N -Werte zu finden, verwenden Sie die SMALL Funktion anstelle von LARGE :

=SMALL(values, ROWS(A$2:A2))

Finden von Top -N -Werten in einer Zeile

Ersetzen Sie für horizontal angeordnete Daten ROWS durch COLUMNS :

=LARGE(values, COLUMNS(A$2:A2)) (für Top -Werte) =SMALL(values, COLUMNS(A$2:A2)) (für untere Werte)

Abrufen von Daten, die mit Top -N -Werten verbunden sind

Verwenden Sie INDEX und MATCH mit LARGE (oder SMALL ), um Daten abzurufen, die den Top -N -Werten entsprechen:

=INDEX(return_array, MATCH(LARGE(lookup_array, k), lookup_array, 0))

Alternativ können Excel 365 -Benutzer dies mit XLOOKUP vereinfachen:

=XLOOKUP(LARGE(lookup_array, k), lookup_array, return_array)

Umgang mit doppelten Top -Werten

Für Datensätze mit Duplikaten ist eine robustere MATCH erforderlich (Array-Formel, Strg-Verschiebung Eingabetaste in Pre-365-Versionen):

=INDEX(return_array, MATCH(1, (lookup_array=LARGE(lookup_array, k))*(COUNTIF(output_range, return_array)=0), 0))

Excel 365 bietet eine einfachere Lösung mit FILTER .

Top -Werte mit Kriterien finden

Verwenden Sie IF Anweisungen innerhalb der LARGE und MATCH Formeln, um Kriterien einzubeziehen:

=LARGE(IF(criteria, values), k)

Wieder vereinfacht Excel 365 dies mit FILTER .

Verwenden der Filterfunktion (Excel 365)

Die FILTER und SORT von Excel 365 bieten einen optimierten Ansatz:

=SORT(FILTER(data, numbers>=LARGE(numbers, n)), sort_index, -1) (oben n) =SORT(FILTER(data, numbers (unten n)

Dieses Tutorial bietet umfassende Methoden zum Extrahieren von Top -N -Werten in Excel, der Verringerung verschiedener Szenarien und Excel -Versionen. Ein Praxis -Arbeitsbuch steht zum Download zur Verfügung.

Das obige ist der detaillierte Inhalt vonExcel -Formel, um die Top 3, 5, 10 Werte in der Spalte oder Zeile zu finden. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!