In dit artikel zullen we leren Hoe de percentielwaarde te vinden als met bepaalde criteria in Excel
Scenario:
In eenvoudige bewoordingen, wanneer we werken met een lange verspreide dataset, moeten we soms het percentiel van getallen vinden met een aantal criteria eroverheen. Bijvoorbeeld het vinden van het percentiel van salarissen op een bepaalde afdeling of het hebben van meerdere criteria voor datum, namen, afdeling of zelfs cijfers zoals salarissen onder de waarde of hoeveelheid boven de waarde. Hiervoor moet je handmatig vereiste getallen extraheren en vervolgens het percentiel van een array met criteria berekenen. Gebruik van de ALS-functie met matrixformules.
Hoe het probleem op te lossen?
U moet zich afvragen hoe dit mogelijk is, om logische bewerkingen uit te voeren over tabelmatrices met behulp van de IF-functie. ALS de functie in Excel erg handig is, zal het je door een aantal moeilijke taken in Excel of een andere codeertalen loodsen. ALS-functie test voorwaarden op array die overeenkomen met vereiste waarden en retourneert het resultaat als array die overeenkomt met True-voorwaarden als 1 en False als 0
Voor dit probleem zullen we de volgende functies gebruiken:
- PERCENTIEL functie
- ALS-functie
We zullen deze bovenstaande functies en een basisgevoel voor gegevensverwerking nodig hebben. logische voorwaarden op arrays kunnen worden toegepast met behulp van logische operators. Deze logische operatoren werken zowel op tekst als op getallen. Hieronder staat de generieke formule. { } accolades is het magische hulpmiddel om matrixformules uit te voeren met de ALS-functie.
Generieke formule:
| { = PERCENTIEL ( ALS ( (bereik op crit), percentile_array ), k ) } |
Let op: voor accolades ( { } ) Gebruik maken van Ctrl + Shift + Enter bij het werken met arrays of bereiken in Excel. Dit genereert standaard accolades op de formule. Probeer GEEN accolades hard te coderen.
bereik : array, waar voorwaarde van toepassing is
op : operator, bewerking toegepast
crit: criteria toegepast op bereik
percentile_array : array, waar percentiel nodig is
k : kth percentiel (bijvoorbeeld 33% -> k = 0,33 waarde als getal)
Voorbeeld :
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we deze formule dus testen door deze in het onderstaande voorbeeld uit te voeren. Hier hebben we gegevens van ontvangen producten uit verschillende regio's, samen met de bijbehorende datum, hoeveelheid en prijs. Hier hebben we de gegevens en we moeten het 25e percentiel of de waarde vinden die overeenkomt met 25%, rekening houdend met bestellingen die alleen uit de regio "Oost" worden ontvangen. Nu bent u klaar om het gewenste resultaat te krijgen met behulp van de onderstaande formule.
Gebruik de formule:
| { = PERCENTIEL ( ALS ( B2:B11 = "Oost" , D2:D11 ) , 0.25 ) } |
Uitleg :
- Regio B2:B11 ="East" : controleert de waarden in de array-regio om overeen te komen met "East".
- Logische operator retourneert True voor de overeenkomende waarde en False voor de niet-overeenkomende waarde.
- Nu retourneert de ALS-functie alleen de prijswaarden die overeenkomen met de 1s en False zoals het is. Hieronder staat het bereik dat wordt geretourneerd door de ALS-functie en wordt ontvangen door de PERCENTIEL-functie.
{ ONWAAR; 303.63; ONWAAR ; 153,34; ONWAAR ; 95,58; ONWAAR ; ONWAAR ; 177 ; ONWAAR }
- De functie PERCENTIEL retourneert de 25% (k=0,25) percentielprijs voor de geretourneerde array.
Hier worden de arrays gegeven met behulp van de celverwijzing. Nu wordt de prijs die overeenkomt met de 25% van de array hieronder gegeven.
Zoals u kunt zien, komt de prijs op 138,9 , van de drie bestellingen uit "Oost" met prijswaarden 303,63, 153,34 en 95,58. Krijg nu de prijswaarde met een ander percentiel door de kth-waarde te wijzigen in 0,5 voor 50%, 0,75 voor 75% en 1 voor 100% percentielwaarde.
Zoals u kunt zien, hebben we alle percentielwaarden die overeenkomen met bepaalde criteria. Gebruik nu de formule met Datumwaarde als criterium.
percentiel indien met datumcriteria in Excel:
Datum als criterium is een lastig iets in Excel. Omdat Excel datumwaarden opslaat als getal. Dus als de datumwaarde niet wordt herkend door Excel, dan is de formule-retourfout. Hier moeten we de 25% percentielwaarde vinden voor bestellingen die na 15 januari 2019 zijn ontvangen.
Gebruik de formule:
| { = PERCENTIEL (ALS (A2:A11 > H3, D2:D11), 0,25 } |
> : groter dan operator toegepast op de datumreeks.
Hier worden de arrays gegeven met behulp van de celverwijzing. Nu wordt de prijs die overeenkomt met de 25% van de array hieronder gegeven.
Zoals u kunt zien, is 90.27 de 25e percentielwaarde met datum na 15 januari 2019. Krijg nu het percentiel voor de verschillende kth-waarde.
Hier zijn alle percentielwaarden als resultaten
Hier zijn alle observatienotities met betrekking tot het gebruik van de formule.
Opmerkingen:
- De percentiel_array in de formule werkt alleen met getallen.
- GEEN accolades hard coderen met formule.
- Als de formule de fout #WAARDE retourneert, moet u controleren of de accolades aanwezig moeten zijn in de formule, zoals weergegeven in de voorbeelden in het artikel.
- Bewerkingen zoals is gelijk aan ( = ), kleiner dan gelijk aan ( <= ), groter dan ( > ) of niet gelijk aan ( ) kan worden uitgevoerd binnen een toegepaste formule, alleen met getallen.
Ik hoop dat dit artikel over Hoe het percentiel te vinden als met criteria in Excel verklarend is. Vind hier meer artikelen over optelformules. Als je onze blogs leuk vond, deel het dan met je fristarts op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite
Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na vermenigvuldiging van waarden in meerdere arrays in Excel.
SOM als datum tussen ligt : Retourneert de SOM van waarden tussen bepaalde datums of periode in Excel.
Som als datum groter is dan opgegeven datum: Retourneert de SOM van waarden na de opgegeven datum of periode in Excel.
2 manieren om per maand op te tellen in Excel: Retourneert de SOM van waarden binnen een bepaalde specifieke maand in Excel.
Meerdere kolommen optellen met voorwaarde: Retourneert de SOM van waarden over meerdere kolommen met voorwaarde in Excel.
Populaire artikels :
50 Excel-snelkoppeling om uw productiviteit te verhogen : Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe te gebruikende VERT.ZOEKEN-functie in Excel : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.