Excel PERCENTILE Funksjoner - Beregn kth percentilen

Last ned eksempel på arbeidsbok

Last ned eksemplet på arbeidsbok

Denne opplæringen viser hvordan du bruker Excel PERCENTILE -funksjon i Excel for å beregne prosentilen.

PERCENTILE Funksjon Oversikt

PERCENTILE -funksjonen beregner kth -percentilen.

For å bruke funksjonen PERCENTILE Excel -regneark, velg en celle og skriv:

(Legg merke til hvordan formelinngangene vises)

PERCENTILE -funksjon Syntaks og innganger:

1	= PERSENTIL (matrise, k)

matrise - En rekke tall.

k - Prosentilverdien ligger mellom 0 og 1.

Hva er PERCENTILE -funksjonen?

PERCENTILE er en av Excel statistiske funksjoner. Den returnerer k-th-persentilen til et gitt dataområde.

PERCENTILE er en "kompatibilitet" -funksjon

Fra Excel 2010 erstattet Microsoft PERCENTILE med to varianter: PERCENTILE.INC og PERCENTILE. EKSK.

PERCENTILE fungerer fortsatt, så eldre regneark som bruker det, vil fortsette å fungere som normalt. Men hvis du ikke trenger regnearket ditt for å forbli kompatibelt med eldre versjoner av Excel, bør du bruke PERCENTILE.INC eller PERCENTILE.EXC.

Vi vil diskutere alle tre, slik at du forstår inn-og utgangene til hver enkelt.

Hva er prosentdelen?

Prosentilen forteller deg hvordan en verdi kan sammenlignes med de andre verdiene i et gitt område. Når en verdi er ved k-th-percentilen, betyr dette at den er høyere enn k% av de andre verdiene i gruppen.

For eksempel, hvis du er en mann som bor i USA og er 180 cm høy, er du i den 71. prosentilen - du er høyere enn 71% av andre menn i USA. Hvis du er kvinne og 180 cm høy, er du på 99^th prosentil - du er høyere enn 99% av andre kvinner.

Når du har å gjøre med prosentiler, må du merke deg at 50^th prosentilen er lik medianen <> av dataene.

Excel PERCENTILE -funksjonen forteller deg hva skjæringspunktet for en gitt prosentil er - det vil si hvor høy en verdi må være for å være i k -prosentilen.

Slik bruker du PERCENTILE -funksjonen

Bruk PERCENTILE slik:

1	= PERSENTIL (C4: C13, F3)

Her har vi noen venner som har bestemt seg for å beregne 80^th prosentilen av høyden på gruppen, og vi ser resultatet - 183,4 cm.

Det første argumentet i funksjonen er dataområdet - deres høyder, som vi har lagt ut i C4: C13.

Det neste argumentet er prosentilen. I dette eksemplet har jeg satt prosentilen i celle F3, og deretter referert til den cellen i funksjonen. På denne måten er det enkelt å endre prosentilen og se de nye resultatene.

Du kan imidlertid sette prosentilen direkte inn i formelen hvis du vil. Du kan definere det som en desimal, slik:

1	= PERSENTIL (C4: C13, .8)

Eller som en prosentandel, slik:

1	= PERSENTIL (C4: C13,80%)

Og disse gir alle samme verdi.

Noen ting du bør være oppmerksom på med PERCENTILE:

• Hvis prosentilen du definerer ikke er et tall, returnerer funksjonen en #VERDI! feil
• Hvis prosentilen er mindre enn 0 eller større enn 1, får du et #NUM! feil
• Tekst og tomme celler i dataområdet ditt blir ignorert

To metoder for å beregne prosentiler

La oss sette ordningen på dataene våre et sekund:

Du lurer kanskje på hvorfor 80^th prosentilen er ikke 183, siden Chandler er 8^th den høyeste personen i gruppen.

Vel, det er faktisk mange måter å beregne prosentilen matematisk på, og ingen standardisert eller foretrukket metode for alle situasjoner. Dette er noe statistikere krangler om i akademiske artikler.

Excel gir deg to måter å gjøre dette på, noe som bringer oss til PERCENTILE.INC og PERCENTILE.EXC.

PERCENTILE.INC

Excel PERCENTILE.INC -funksjonen er den samme som PERCENTILE. "INC" -delen er kort for inkluderende, fordi den kan beregne enhver gyldig prosentil (det vil si alt fra 0% til 100%).

Du bruker det slik:

1	= PERSENTIL.INC (C4: C13, F3)

Som du kan se, returnerer det det samme resultatet som PERCENTILE gjorde tidligere.

Så, gå tilbake til hvorfor 80^th prosentilen er ikke akkurat lik Chandlers høyde. Husk at vi gjør en inkluderende beregning her, så vi inkluderer alle k -verdier fra 0 til 1 (eller 0% til 100%).

Dette betyr at vår korteste venn, Janice, er på 0^th prosentilen, og Richard, den høyeste, er 100^th prosentil. Alle de andre vennene er i like trinn mellom de to, og den økningen er lik 1/(n-1), hvor n er antall datapunkter i området.

I vårt tilfelle er det:

1	1 / (10 - 1) = 11.111…

Det betyr at Chandler faktisk ikke er på 80^th prosentilen, er han på 77.777^th prosentil. Hvis vi kobler dette nummeret til PERCENTILE.INC, bør vi få Chandlers høyde …

… og det gjør vi.

Lineær interpolering

Når k-verdien du angir ikke er et multiplum av 1/(n-1), bruker PERCENTILE.INC en prosess som kalles lineær interpolasjon for å beregne resultatet. Dette høres komplisert ut, men det betyr i utgangspunktet at Excel vil betale en verdi mellom de to.

Så hvorfor fikk vi 183,4 da vi spurte om 80^th prosentilen tidligere?

Beregningen fungerer som følger:

• Den 80^th prosentilen faller mellom Chandler og Ross, Chandler på 77.777^th prosentil, og Ross på 88.888.
• Den 80^th prosentilen er 2,222% foran Chandlers høyde
• Vi vet at det er 11% avstand mellom Chandler og Ross 'høyder
• 222% / 11,111% = 20% etter avrunding. Nå vet vi at 80^th prosentilen er 20% av veien mellom Ross og Chandlers høyder.
• Forskjellen mellom Ross ’høyde på 185 cm, og Chandlers høyde på 183 cm, er 2 cm
• 20% av 2cm er 0,4cm
• Legg det til Chandlers høyde, så får vi 183,4 cm

PERCENTILE.EXC

Du bruker PERCENTILE.EXC på i hovedsak samme måte:

1	= PERCENTILE.EXC (C4: C13, F3)

Ok, så vi har de samme dataene, vi vil ha samme prosentilen, men vi har forskjellige resultater. Hvorfor det?

Det er fordi PERCENTILE.EXC utelukker de første og siste verdiene ved beregning av prosentilintervallene. Se nedenfor:

Snarere enn at intervallet er lik 1/(n-1), med PERCENTILE.EXC er intervallet 1/(n+1), eller i dette tilfellet 9,091%.

Alt annet fungerer på samme måte som med PERCENTILE.INC. Igjen, 80^th prosentilen er mellom Chandler og Ross, så Excel bruker den samme metoden for lineær interpolasjon:

• Den 80^th prosentilen faller mellom Chandler og Ross, Chandler på 72.727^th prosentil, og Ross på 81.818.
• Den 80^th prosentilen er 7,272% foran Chandlers høyde
• Vi vet at det er en 9,091% avstand mellom Chandler og Ross ’høyder
• 272% / 9,091% = 80% etter avrunding. Nå vet vi at 80^th prosentilen er 80% av veien mellom Ross og Chandlers høyder.
• Forskjellen mellom Ross ’høyde på 185 cm, og Chandlers høyde på 183 cm, er 2 cm
• 80% av 2cm er 1,6cm
• Legg det til Chandlers høyde, så får vi 184,6 cm

PERCENTILE i Google Regneark

PERCENTILE -funksjonen fungerer nøyaktig det samme i Google Sheets som i Excel:

PERSENTILE Eksempler i VBA

Du kan også bruke PERCENTILE -funksjonen i VBA. Type:
application.worksheetfunction.percentile (array, k)
For funksjonsargumentene (array, etc.) kan du enten skrive dem inn direkte i funksjonen, eller definere variabler som skal brukes i stedet.

Gå tilbake til listen over alle funksjoner i Excel