In diesem Artikel erfahren Sie, wie Sie den Perzentilwert mit bestimmten Kriterien in Excel ermitteln
Szenario:
In einfachen Worten, wenn wir mit einem langen verstreuten Datensatz arbeiten, müssen wir manchmal das Perzentil von Zahlen mit einigen Kriterien darüber finden. Wenn Sie beispielsweise das Perzentil der Gehälter in einer bestimmten Abteilung finden oder mehrere Kriterien für Datum, Namen, Abteilung oder sogar Zahlen wie Gehälter unter dem Wert oder die Menge über dem Wert verwenden. Dazu müssen Sie die erforderlichen Zahlen manuell extrahieren und dann das Perzentil eines Arrays mit Kriterien berechnen. Verwendung der IF-Funktion mit Array-Formeln.
Wie löst man das Problem?
Sie müssen sich überlegen, wie dies möglich ist, logische Operationen über Tabellenarrays mit der IF-Funktion auszuführen. Die IF-Funktion in Excel ist sehr nützlich. Sie wird Sie durch einige schwierige Aufgaben in Excel oder anderen Programmiersprachen führen. IF-Funktion testet Bedingungen für Array, die den erforderlichen Werten entsprechen, und gibt das Ergebnis als Array zurück, das den Bedingungen True als 1 und False als 0 entspricht
Für dieses Problem verwenden wir die folgenden Funktionen:
- PERZENTIL-Funktion
- WENN-Funktion
Wir benötigen diese oben genannten Funktionen und ein gewisses Grundverständnis für die Datenverarbeitung. logische Bedingungen auf Arrays können mit logischen Operatoren angewendet werden. Diese logischen Operatoren funktionieren sowohl mit Text als auch mit Zahlen. Unten hier ist die generische Formel. { } geschweifte Klammern ist das magische Werkzeug, um Array-Formeln mit IF-Funktion auszuführen.
Generische Formel:
| { = PROZENTIL ( WENN ( (Bereich op crit), perzentile_array ), k ) } |
Hinweis: Für geschweifte Klammern ( { } ) Verwenden Strg + Umschalt + Eingabetaste beim Arbeiten mit Arrays oder Bereichen in Excel. Dadurch werden standardmäßig geschweifte Klammern für die Formel generiert. Versuchen Sie NICHT, geschweifte Klammern hart zu codieren.
range : Array, in dem die Bedingung gilt
op : Operator, Operation angewendet
crit : Kriterien, die auf die Reichweite angewendet werden
Perzentile_Array : Array, wo Perzentile benötigt werden
k : k-tes Perzentil (zum Beispiel 33% -> k = 0,33 Wert als Zahl)
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 Daten der erhaltenen Produkte aus verschiedenen Regionen zusammen mit dem entsprechenden Datum, der Menge und dem Preis. Hier haben wir die Daten und wir müssen das 25. Perzentil oder den Wert von 25 % finden, wenn Bestellungen berücksichtigt werden, die nur aus der Region "Ost" eingehen. Jetzt sind Sie bereit, das gewünschte Ergebnis mit der folgenden Formel zu erhalten.
Verwenden Sie die Formel:
| { = PROZENTIL ( WENN ( B2:B11 = "Ost", D2:D11 ) , 0,25 ) } |
Erläuterung :
- Region B2:B11 ="East" : überprüft die Werte in der Array-Region auf Übereinstimmung mit "East".
- Der logische Operator gibt True für den übereinstimmenden Wert und False für den nicht übereinstimmenden Wert zurück.
- Jetzt gibt die IF-Funktion nur die Preiswerte zurück, die den 1s und False entsprechen, wie sie sind. Unten ist der Bereich, der von der IF-Funktion zurückgegeben und von der PERCENTILE-Funktion empfangen wird.
{ FALSCH; 303,63 ; FALSCH ; 153,34 ; FALSCH ; 95,58 ; FALSCH ; FALSCH ; 177; FALSCH }
- Die PERCENTILE-Funktion gibt den 25 % (k = 0,25) Perzentilpreis für das zurückgegebene Array zurück.
Hier werden die Arrays über die Zellreferenz angegeben. Jetzt ist der Preis, der den 25% des Arrays entspricht, unten angegeben.
Wie Sie sehen, beträgt der Preis 138,9 , aus den drei Aufträgen von "Ost" mit den Preiswerten 303,63, 153,34 und 95,58. Jetzt erhalten Sie den Preiswert mit unterschiedlichem Perzentil, indem Sie einfach den kth-Wert auf 0,5 für 50%, 0,75 für 75% und 1 für 100% Perzentilwert ändern.
Wie Sie sehen können, haben wir alle Perzentilwerte, die bestimmten Kriterien entsprechen. Verwenden Sie nun die Formel mit dem Datumswert als Kriterium.
Perzentil, wenn mit Datumskriterien in Excel:
Datum als Kriterium ist in Excel eine knifflige Sache. Da Excel Datumswerte als Zahl speichert. Wenn also der Datumswert von Excel nicht erkannt wird, gibt die Formel einen Fehler zurück. Hier müssen wir den 25%-Perzentilwert ermitteln, wenn Bestellungen nach dem 15. Januar 2019 eingegangen sind.
Verwenden Sie die Formel:
| { = PROZENTIL ( WENN ( A2:A11 > H3 , D2:D11 ) , 0,25 ) } |
> : größer als der auf das Datumsarray angewendete Operator.
Hier werden die Arrays über die Zellreferenz angegeben. Jetzt ist der Preis, der den 25% des Arrays entspricht, unten angegeben.
Wie Sie sehen, ist 90,27 der 25. Perzentilwert mit einem Datum nach dem 15. Januar 2019. Jetzt erhalten Sie das Perzentil für den anderen k-Wert.
Hier sind alle Perzentilwerte als Ergebnisse
Hier sind alle Beobachtungshinweise zur Verwendung der Formel.
Anmerkungen:
- Das perzentile_array in der Formel funktioniert nur mit Zahlen.
- KEINE geschweiften Klammern mit Formel hart codieren.
- Wenn die Formel den Fehler #VALUE zurückgibt, überprüfen Sie, ob die geschweiften Klammern in der Formel vorhanden sein müssen, wie in den Beispielen im Artikel gezeigt.
- Operationen wie gleich ( = ), kleiner gleich ( <= ), größer als ( > ) oder ungleich ( ) kann innerhalb einer angewendeten Formel nur mit Zahlen ausgeführt werden.
Hoffentlich ist dieser Artikel über So finden Sie das Perzentil mit Kriterien in Excel erklärend. Weitere Artikel zum Thema Summenformeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie es mit Ihren fristarts 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.
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.