Hvordan finne prosentilen hvis du har kriterier i Excel

Anonim


I denne artikkelen lærer vi hvordan du finner prosentilverdien hvis du har gitt kriterier i Excel

Scenario:

I enkle ord, når vi arbeider med et langt spredt datasett, må vi noen ganger finne prosentilen av tall med noen kriterier over det. For eksempel å finne prosentandelen av lønninger i en bestemt avdeling eller ha flere kriterier over dato, navn, avdeling eller kan til og med nummerere data som lønn under verdi eller mengde over verdi. For dette må du manuelt trekke ut nødvendige tall og deretter beregne prosentilen til en matrise med kriterier. Bruk av IF -funksjon med matriseformler.

Hvordan løse problemet?

Du må tenke på hvordan dette er mulig, for å utføre logiske operasjoner over bordarrayer ved hjelp av IF -funksjon. HVIS funksjonen i Excel er veldig nyttig, vil den komme deg gjennom noen vanskelige oppgaver i Excel eller andre kodingsspråk. IF -funksjon tester betingelser på array som tilsvarer nødvendige verdier og returnerer resultatet som array som tilsvarer True conditions som 1 og False som 0

For dette problemet bruker vi følgende funksjoner:

  1. PERSENTIL funksjon
  2. IF -funksjon

Vi vil kreve disse funksjonene ovenfor og en grunnleggende følelse av datadrift. logiske forhold på matriser kan brukes ved hjelp av logiske operatorer. Disse logikkoperatørene jobber både med tekst og tall. Nedenfor er den generiske formelen. { } curly braces er det magiske verktøyet for å utføre matriseformler med IF -funksjon.

Generisk formel:

{ = PERSENTIL (HVIS ((område op crit), percentile_array), k) }

Merk: For krøllete seler ( { } ) Bruk Ctrl + Shift + Enter når du arbeider med matriser eller områder i Excel. Dette vil generere Curly Braces på formelen som standard. IKKE prøv å hardt kode krøllete klammeparenteser.

område: array, der betingelsen gjelder

op: operatør, drift påført

crit: kriterier brukt på rekkevidde

percentile_array: array, der percentilen er nødvendig

k: kth percentile (for eksempel 33% -> k = 0,33 verdi som tall)

Eksempel:

Alt dette kan være forvirrende å forstå. Så la oss teste denne formelen ved å kjøre den på eksemplet nedenfor. Her har vi data om mottatte produkter fra forskjellige regioner sammen med tilsvarende dato, mengde og pris. Her har vi dataene, og vi må finne den 25. prosentilen eller verdien som tilsvarer 25% med tanke på bestillinger som bare mottas fra "øst" -regionen. Nå er du klar til å få ønsket resultat ved å bruke formelen nedenfor.

Bruk formelen:

{ = PERSENTIL (HVIS (B2: B11 = "Øst", D2: D11), 0,25) }

Forklaring:

  1. Region B2: B11 = "Øst": sjekker at verdiene i matriseområdet samsvarer med "øst".
  2. Logisk operatør returnerer True for den matchede verdien og False for den uoverensstemmende verdien.
  3. Nå returnerer IF -funksjonen bare prisverdiene som tilsvarer 1 -tallet og False som det er. Nedenfor er området returnert av IF -funksjonen og mottatt av PERCENTILE -funksjonen.

{ FALSK; 303,63; FALSK ; 153,34; FALSK ; 95,58; FALSK ; FALSK ; 177; FALSK }

  1. PERCENTILE -funksjonen returnerer 25% (k = 0,25) prosentilprisen for den returnerte matrisen.

Her er matrisene gitt ved hjelp av cellereferansen. Nå er prisen som tilsvarer 25% av matrisen gitt nedenfor.

Som du kan se, kommer prisen til 138,9, fra de tre ordrene fra "Øst" som har prisverdier 303,63, 153,34 og 95,58. Få nå prisverdien med en annen prosentil bare ved å endre kth -verdien til 0,5 for 50%, 0,75 for 75% og 1 for 100% prosentilverdi.

Som du kan se, har vi alle prosentilverdiene som tilsvarer gitte kriterier. Bruk nå formelen med datoverdi som kriterier.

prosentil hvis med datakriterier i Excel:

Dato som kriterium er en vanskelig ting i Excel. Som Excel lagrer datoverdier som tall. Så hvis datoverdi ikke gjenkjennes av excel, kommer formelfeilfeil. Her må vi finne 25 prosent prosentverdien som har bestillinger mottatt etter 15. januar 2019.

Bruk formelen:

{ = PERSENTIL (HVIS (A2: A11> H3, D2: D11), 0,25)}

>: større enn operatoren som er brukt over datormatrisen.

Her er matrisene gitt ved hjelp av cellereferansen. Nå er prisen som tilsvarer 25% av matrisen gitt nedenfor.

Som du kan se, er 90.27 den 25. prosentilen som har dato etter 15. januar 2019. Få nå prosentilen for den forskjellige kth -verdien.

Her er alle prosentilverdiene som resultater

Her er alle observasjonsnotatene om bruk av formelen.

Merknader:

  1. Prosentil_arrayen i formelen fungerer bare med tall.
  2. IKKE hardt kode krøllete seler med formel.
  3. Hvis formelen returnerer #VALUE -feil, må du kontrollere om de krøllete bukseselene er tilstede i formelen som vist i eksemplene i artikkelen.
  4. Operasjoner som er lik ( = ), mindre enn lik ( <= ), større enn ( > ) eller ikke er lik () kan utføres innenfor en anvendt formel, bare med tall.

Håper denne artikkelen om How to How to find the percentile if with criteria in Excel er forklarende. Finn flere artikler om Summing -formler her. Hvis du likte bloggene våre, del den med fristarts på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post

Slik bruker du SUMPRODUCT -funksjonen i Excel: Returnerer SUMMEN etter multiplikasjon av verdier i flere matriser i excel.

SUM hvis datoen er mellom : Returnerer SUMMEN av verdier mellom gitte datoer eller periode i excel.

Sum hvis datoen er større enn gitt dato: Returnerer SUMMEN av verdier etter gitt dato eller periode i excel.

2 måter å summere etter måned i Excel: Returnerer SUMMEN av verdier i en gitt spesifikk måned i excel.

Slik summerer du flere kolonner med betingelse: Returnerer SUM av verdier på tvers av flere kolonner som har betingelse i excel.

Populære artikler:

50 Excel -snarvei for å øke produktiviteten : Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.

Slik bruker du tVLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.

Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.

Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.