In diesem Artikel erfahren Sie, wie Sie n-kleinste mit Kriterien und n-größte mit Kriterien aus der angegebenen Tabelle in Excel finden.
Szenario:
In einfachen Worten, bei der Arbeit mit Zahlen in Datenzahlen wird manchmal eine Bedingung gegeben, z. B. wenn wir nach dem fünfthöchsten angegebenen Wert suchen müssen. Sie können die Lösung dieses Problems einfach mit den unten erläuterten Excel-Funktionen durchführen.
N-größte mit Kriterien
Wie löst man das Problem?
Für diesen Artikel müssen wir die LARGE-Funktion verwenden. Jetzt werden wir aus diesen Funktionen eine Formel machen. Hier erhalten wir eine Tabelle und wir müssen den n-größten Wert im Bereich mit gegebenen Kriterien finden.
Generische Formel:
{ = GROSS ( WENN ( c_range = "Wert", Bereich ) , n ) }
c_range : Kriterienbereich
Wert : Kriterien entsprechen Wert
Bereich : Ergebnis-Array
n : n-größter
Notiz : Setzen Sie geschweifte Klammern nicht manuell. Dies ist eine Array-Formel, muss verwendet werden Strg + Umschalt + Eingabetaste anstelle von nur Enter, wo #NUM zurückgegeben wird! Error.
Beispiel:
All dies kann verwirrend sein zu verstehen. Lassen Sie uns diese Formel testen, indem wir sie im unten gezeigten Beispiel ausführen.
Hier haben wir Bestelldetails mit Bestelldatum, Region und Bestellpreis.
Wir müssen den fünfthöchsten Bestellpreis aus der Region Ost herausfinden. Hier wird der Bereich mit dem benannten Bereich Excel-Tool angegeben.
Region (C3:C16)
Preis (D3:D16)
Zuerst müssen wir die fünftgrößten Werte mit der Funktion LARGE finden und dann eine Summenoperation über diese 5 Werte durchführen. Jetzt verwenden wir die folgende Formel, um die Summe zu erhalten
Verwenden Sie die Formel:
{ = GROSS ( WENN ( Region = G5 , Preis ) , F5 )}
Erläuterung:
- Die IF-Funktion überprüft die Kriterien, die allen Werten in der Region mit dem angegebenen Ostwert in der G5-Zelle entsprechen, und gibt die entsprechenden TRUE-Werte aus der Preisspanne zurück.
= GROSS ({58.41; 303.63; FALSCH; 153.34; 82.84; FALSCH; 520.01; FALSCH; 177; FALSCH; FALSCH; 57.97; 97.72; FALSCH}), F5)
- Die Funktion LARGE nimmt das Array mit der gesamten Preisspanne wie oben gezeigt und gibt den fünftgrößten Wert aus dem Array zurück, wie in der folgenden Momentaufnahme gezeigt.
Sie können die Formel im Formelfeld anzeigen, wie oben im Schnappschuss gezeigt. Verwenden Sie Strg + Umschalt + Eingabetaste, um das Ergebnis zu erhalten.
Wie Sie sehen können, gibt die Array-Formel den fünfthöchsten Preis von 97,72 USD mit der Ostregion zurück.
Sie können Array auch als Array, Kriterien in Anführungszeichen und n-Werte direkt an die Funktion füttern, anstatt den Zellbezug oder den benannten Bereich zu verwenden.
Verwenden Sie die Formel:
{ = GROSS ( WENN ( C3:C16 = "West", D3:D16 ) , 3 )}
Verwenden Strg + Umschalt + Eingabetaste
Sie können sehen, dass die Formel gut funktioniert, wenn Sie den n-größten Wert mit Kriterien in der Tabelle finden.
N-niedrigste mit Kriterien
Wie löst man das Problem?
Für diesen Artikel müssen wir die Funktion SMALL verwenden. Jetzt werden wir aus diesen Funktionen eine Formel machen. Hier erhalten wir eine Tabelle und wir müssen den n-kleinsten Wert im Bereich mit gegebenen Kriterien finden.
Generische Formel:
{ = KLEIN ( WENN ( c_range = "Wert", Bereich ) , n ) }
c_range : Kriterienbereich
Wert : Kriterien entsprechen Wert
Bereich : Ergebnis-Array
n : n-größter
Notiz : Setzen Sie geschweifte Klammern nicht manuell. Dies ist eine Array-Formel, muss verwendet werden Strg + Umschalt + Eingabetaste anstelle von nur Enter, wo #NUM zurückgegeben wird! Error.
Beispiel:
All dies kann verwirrend sein zu verstehen. Lassen Sie uns diese Formel testen, indem wir sie im unten gezeigten Beispiel ausführen.
Hier haben wir Bestelldetails mit Bestelldatum, Region und Bestellpreis.
Wir müssen den n-kleinsten Bestellpreis aus der Region Ost herausfinden. Hier wird der Bereich mit dem benannten Bereich Excel-Tool angegeben.
Region (C3:C16)
Preis (D3:D16)
Zuerst müssen wir den fünftkleinsten oder niedrigsten Wert unter Verwendung der SMALL-Funktion finden und dann eine Nachschlageoperation über die Werte durchführen. Jetzt verwenden wir die folgende Formel, um die Summe zu erhalten
Verwenden Sie die Formel:
{ = KLEIN ( WENN ( Region = G5 , Preis ) , F5 )}
Erläuterung:
- Die IF-Funktion überprüft die Kriterien, die allen Werten in der Region mit dem angegebenen Ostwert in der G5-Zelle entsprechen, und gibt die entsprechenden TRUE-Werte aus der Preisspanne zurück.
= GROSS ({58.41; 303.63; FALSCH; 153.34; 82.84; FALSCH; 520.01; FALSCH; 177; FALSCH; FALSCH; 57.97; 97.72; FALSCH}), F5)
- Die SMALL-Funktion nimmt das Array mit der gesamten Preisspanne wie oben gezeigt und gibt den fünftgrößten Wert aus dem Array zurück, wie im folgenden Schnappschuss gezeigt.
Sie können die Formel im Formelfeld anzeigen, wie oben im Schnappschuss gezeigt. Verwenden Strg + Umschalt + Eingabetaste um das Ergebnis zu bekommen.
Wie Sie sehen können, gibt die Array-Formel den fünftkleinsten Preis von 153,34 USD mit der Ostregion zurück.
Sie können Array auch als Array, Kriterien in Anführungszeichen und n-Werte direkt an die Funktion füttern, anstatt den Zellbezug oder den benannten Bereich zu verwenden.
Verwenden Sie die Formel:
{ = KLEIN ( WENN ( C3:C16 = "Westen" , D3:D16 ) , 3 )}
Verwenden Strg + Umschalt + Eingabetaste
Sie können sehen, dass die Formel gut funktioniert, wenn Sie den n-größten Wert mit Kriterien in der Tabelle finden.
Hier sind einige Beobachtungsnotizen, die unten gezeigt werden.
Anmerkungen:
- Die Formel funktioniert nur mit Zahlen.
- Setzen Sie keine geschweiften Klammern manuell. Dies ist eine Array-Formel, muss verwendet werden Strg + Umschalt + Eingabetaste anstelle von nur Enter, wo #NUM zurückgegeben wird! Error.
- Der Wert von darf nicht größer sein als die Anzahl der Kriterienwerte oder er gibt #NUM zurück! Fehler.
- WENN-Kriterien nicht mit der Formel übereinstimmen, wird ein Fehler zurückgegeben.
- Das Argument Array muss die gleiche Länge haben, sonst die Funktion.
Ich hoffe, dieser Artikel zum Finden des n-größten mit Kriterien und des n-kleinsten mit Kriterien in Excel ist erklärend. Weitere Artikel zu Nachschlageformeln in Excel finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site
So verwenden Sie die SUMMENPRODUKT-Funktion in Excel: Gibt die SUMME nach der Multiplikation von Werten in mehreren Arrays in Excel zurück.
SUM, wenn das Datum dazwischen liegt : Gibt die SUMME der Werte zwischen den angegebenen Daten oder dem Zeitraum in Excel zurück.
Summe, wenn das Datum größer als das angegebene Datum ist: Gibt die SUMME der Werte nach dem angegebenen Datum oder Zeitraum in Excel zurück.
2 Möglichkeiten, in Excel nach Monat zu summieren: Gibt die SUMME der Werte innerhalb eines bestimmten Monats in Excel zurück.
So summieren Sie mehrere Spalten mit Bedingung: Gibt die SUMME von Werten über mehrere Spalten mit Bedingung in Excel zurück
So verwenden Sie Platzhalter in Excel : Zählen Sie Zellen, die mit Phrasen übereinstimmen, indem Sie die Platzhalter in Excel verwenden
populäre Artikel
50 Excel-Shortcut zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgabe schneller. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
Wie benutzt manDie SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.