Wie die Perzentil zu finden, wenn mit den Kriterien in Excel
In diesem Artikel erfahren Sie, wie Sie den Perzentilwert ermitteln, wenn Sie bestimmte Kriterien in Excel
verwenden === Szenario:
Mit einfachen Worten, wenn wir mit einem lange 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 ermitteln oder mehrere Kriterien für Datum, Namen, Abteilung oder sogar Zahlen wie Gehälter unter dem Wert oder Menge über dem Wert haben.
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öse ich das Problem?
Sie müssen sich überlegen, wie dies möglich ist, um mithilfe der IF-Funktion logische Operationen über Tabellenarrays auszuführen. Wenn die Funktion in Excel sehr nützlich ist, werden Sie durch einige schwierige Aufgaben in Excel oder anderen Codierungssprachen geführt. Die IF-Funktion testet Bedingungen für ein Array, die den erforderlichen Werten entsprechen, und gibt das Ergebnis als Array zurück, das den True-Bedingungen als 1 und False als 0 entspricht. Für dieses Problem verwenden wir die folgenden Funktionen:
-
link: / mathematische Funktionen-wie-man-die-Perzentil-Funktion benutzt [PERCENTILE-Funktion] -
link: / tips-if-condition-in-excel [IF-Funktion]
Wir werden diese oben genannten Funktionen und einen grundlegenden Sinn für die Datenoperation benötigen. Logische Bedingungen auf Arrays können mit logischen Operatoren angewendet werden. Diese Logikoperatoren arbeiten sowohl mit Text als auch mit Zahlen. Unten ist die generische Formel. \ {} * geschweifte Klammern sind das magische Werkzeug, um Array-Formeln mit IF-Funktion auszuführen.
Allgemeine Formel:
|
\{* = ( op crit), percentile_array ), k ) }* |
Hinweis: Verwenden Sie für geschweifte Klammern (\ {}) Strg + Umschalt + Eingabetaste *, wenn Sie mit Arrays oder Bereichen in Excel arbeiten. Dadurch werden standardmäßig geschweifte Klammern für die Formel generiert. Versuchen Sie NICHT, geschweifte Klammern hart zu codieren.
Bereich: Array, wobei Bedingung gilt op: Operator, Operation angewendet Kritisch: Kriterien angewendet auf Bereich Perzentil_array: Array, wo Perzentil benötigt wird k: k-tes Perzentil (zum Beispiel 33% → k = 0,33 Wert als Zahl)
Beispiel:
All dies könnte verwirrend zu verstehen sein. Testen wir diese Formel also, indem wir sie im folgenden Beispiel ausführen. Hier haben wir Daten von erhaltenen Produkten aus verschiedenen Regionen zusammen mit dem entsprechenden Datum, der Menge und dem Preis. Hier haben wir die Daten und müssen das 25. Perzentil oder den Wert finden, der 25% entspricht, wenn wir Bestellungen berücksichtigen, die nur aus der Region „Ost“ eingehen. Jetzt können Sie mit der folgenden Formel das gewünschte Ergebnis erzielen.
Verwenden Sie die Formel:
|
\{* = ( = „East“ , D2:D11 ) , 0.25 ) } |
Erläuterung:
-
Region B2: B11 = „Ost“: Überprüft, ob die Werte in der Array-Region mit „Ost“ übereinstimmen.
-
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 der 1s und False entsprechen. Unten ist der Bereich angegeben, der von der IF-Funktion zurückgegeben und von der PERCENTILE-Funktion empfangen wird.
\ {FALSE; 303,63; FALSE; 153,34; FALSE; 95,58; FALSE; FALSE; 177; FALSE}. Die PERCENTILE-Funktion gibt den Perzentilpreis von 25% (k = 0,25) für das zurückgegebene Array zurück.
Hier werden die Arrays unter Verwendung der Zellreferenz angegeben. Nun ist der Preis, der den 25% des Arrays entspricht, unten angegeben.
Wie Sie sehen können, beträgt der Preis 138,9, aus den drei Bestellungen aus „Ost“ mit Preiswerten von 303,63, 153,34 und 95,58. Ermitteln Sie nun den Preiswert mit unterschiedlichem Perzentil, indem Sie den k-ten Wert für 50% auf 0,5, für 75% auf 0,75 und für 100% auf 1% ä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 bei Datumskriterien in Excel:
Datum als Kriterium ist in Excel eine schwierige Sache. Da Excel Datumswerte als Zahl speichert. Wenn also der Datumswert von Excel nicht erkannt wird, wird die Formel zurückgegeben. Hier müssen wir den 25% -Perzentilwert ermitteln, wenn Bestellungen nach dem 15. Januar 2019 eingegangen sind.
Verwenden Sie die Formel:
|
\{* = ( > H3 , D2:D11 ) , 0.25 ) } |
>: Größer als der über das Datumsarray angewendete Operator.
Hier werden die Arrays unter Verwendung der Zellreferenz angegeben. Nun ist der Preis, der den 25% des Arrays entspricht, unten angegeben.
Wie Sie sehen können, ist 90,27 der Wert des 25. Perzentils mit einem Datum nach dem 15. Januar 2019. Ermitteln Sie nun das Perzentil für den anderen k-ten Wert.
Hier sind alle Perzentilwerte als Ergebnisse. Hier sind alle Beobachtungshinweise zur Verwendung der Formel.
Anmerkungen:
-
Das Perzentil-Array in der Formel funktioniert nur mit Zahlen.
-
Locken Sie geschweifte Klammern NICHT mit der Formel fest.
-
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 als gleich (⇐), größer als (>) oder nicht gleich (<> *) können innerhalb einer angewendeten Formel nur mit Zahlen ausgeführt werden.
Hoffe, dass dieser Artikel über das Finden des Perzentils mit Kriterien in Excel erklärend ist. Weitere Artikel zu Summierungsformeln 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 gerne von Ihnen hören, uns mitteilen, wie wir unsere Arbeit verbessern, ergänzen oder innovieren und für Sie verbessern können. Schreiben Sie uns an [email protected]
Verwandte Artikel:
link: / summing-excel-sumproduct-function [Verwendung der SUMPRODUCT-Funktion in Excel]: Gibt die SUMME nach Multiplikation von Werten in mehreren Arrays in Excel zurück.
link: / summing-sum-if-date-is-Between [SUMME wenn Datum zwischen liegt]: Gibt die Summe der Werte zwischen bestimmten Daten oder Zeiträumen in Excel zurück.
link: / Summierungssumme-wenn-Datum-größer-als-gegebenes-Datum ist [Summe, wenn Datum größer als gegebenes Datum ist]: * Gibt die Summe der Werte nach dem gegebenen Datum oder Zeitraum in Excel zurück.
link: / Summieren-2-Wege-zu-Summe-von-Monat-in-Excel [2 Möglichkeiten zur Summe nach Monat in Excel]: * Gibt die Summe der Werte innerhalb eines bestimmten Monats in Excel zurück.
link: / summieren-wie-mehrere-Spalten-mit-Bedingung summieren [Wie mehrere Spalten mit Bedingung summiert werden]: * Gibt die Summe der Werte über mehrere Spalten mit Bedingung in Excel zurück.
Beliebte Artikel:
link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfung zur Steigerung Ihrer Produktivität]: Schneller bei Ihrer Aufgabe. Mit diesen 50 Verknüpfungen können Sie noch schneller in Excel arbeiten.
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Verwendung der VLOOKUP-Funktion in Excel]: Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die zum Nachschlagen von Werten aus verschiedenen Bereichen verwendet wird und Blätter. link: / tips-countif-in-microsoft-excel [Verwendung der COUNTIF-Funktion in Excel]: Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]: Dies ist eine weitere wichtige Dashboard-Funktion. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.