Excel PERCENTILE -funktioner - Beregn kth percentilen

Download eksempel på projektmappe

Download eksemplet på projektmappe

Denne vejledning viser, hvordan du bruger Excel PERCENTILE -funktion i Excel for at beregne percentilen.

PERCENTILE Funktionsoversigt

Funktionen PERCENTIL Beregner kth percentilen.

Hvis du vil bruge funktionen PERCENTILE Excel -regneark, skal du vælge en celle og skrive:

(Bemærk hvordan formelindgangene vises)

PERCENTILE -funktion Syntaks og input:

1	= PERCENTIL (array, k)

array - En række tal.

k - Værdien af ​​percentilen ligger mellem 0 og 1.

Hvad er PERCENTILE -funktionen?

PERCENTILE er en af ​​Excels statistiske funktioner. Det returnerer k-th percentilen for et givet dataområde.

PERCENTILE er en "kompatibilitet" -funktion

Fra Excel 2010 erstattede Microsoft PERCENTILE med to variationer: PERCENTILE.INC og PERCENTILE. EXC.

PERCENTILE fungerer stadig, så ældre regneark, der bruger det, vil fortsat fungere som normalt. Men hvis du ikke har brug for dit regneark til at forblive kompatibelt med ældre versioner af Excel, skal du bruge PERCENTILE.INC eller PERCENTILE.EXC.

Vi diskuterer alle tre, så du forstår ind-og udgange af hver enkelt.

Hvad er procentdelen?

Percentilen fortæller dig, hvordan en værdi kan sammenlignes med de andre værdier i et givet område. Når en værdi er ved k-th percentilen, betyder det, at den er højere end k% af de andre værdier i gruppen.

For eksempel, hvis du er en han, der bor i USA, og du er 180 cm høj, er du i den 71. percentil - du er højere end 71% af andre mænd i USA. Hvis du er kvinde og 180 cm høj, er du på 99^th percentil - du er højere end 99% af andre kvinder.

Når du har at gøre med percentiler, skal du være opmærksom på, at 50^th percentilen er lig med medianen <> af dataene.

Excel PERCENTILE -funktionen fortæller dig, hvad afskæringspunktet for en given percentil er - det vil sige, hvor høj en værdi skal være for at være i k -th percentilen.

Sådan bruges PERCENTILE -funktionen

Brug PERCENTIL sådan:

1	= PERCENTIL (C4: C13, F3)

Her har vi et par venner, der har besluttet at beregne de 80^th percentil af højden på deres gruppe, og vi ser resultatet - 183,4 cm.

Det første argument i funktionen er dataområdet - deres højder, som vi har lagt frem i C4: C13.

Det næste argument er percentilen. I dette eksempel har jeg lagt percentilen i celle F3 og derefter henvist til den celle i funktionen. På denne måde er det let at ændre percentilen og se de nye resultater.

Du kan dog sætte percentilen direkte i formlen, hvis du vil. Du kan definere det som en decimal, sådan her:

1	= PERCENTIL (C4: C13, .8)

Eller som en procentdel, sådan her:

1	= PERCENTIL (C4: C13,80%)

Og disse returnerer alle den samme værdi.

Et par ting at være opmærksom på med PERCENTILE:

• Hvis den percentil, du definerer, ikke er et tal, returnerer funktionen en #VÆRDI! fejl
• Hvis percentilen er mindre end 0 eller større end 1, får du et #NUM! fejl
• Tekst og tomme celler inden for dit dataområde ignoreres

To metoder til beregning af percentiler

Lad os sætte vores data i rækkefølge et sekund:

Du undrer dig måske over, hvorfor 80^th percentilen er ikke 183, da Chandler er den 8^th den højeste person i gruppen.

Nå, der er faktisk mange måder at beregne percentilen matematisk på, og ingen standardiseret eller foretrukken metode til alle situationer. Det er noget statistikere argumenterer over i akademiske artikler.

Excel giver dig to måder at gøre dette på, hvilket bringer os til PERCENTILE.INC og PERCENTILE.EXC.

PERCENTILE.INC

Funktionen Excel PERCENTILE.INC er den samme som PERCENTILE. "INC" -delen er forkortelse for inklusiv, fordi den kan beregne enhver gyldig percentil (det vil sige alt fra 0% til 100%).

Du bruger det sådan her:

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

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

Så tilbage til hvorfor 80^th percentil er ikke ligefrem lig med Chandlers højde. Husk, vi laver en inklusiv beregning her, så vi inkluderer alle k -værdier fra 0 til 1 (eller 0% til 100%).

Det betyder, at vores korteste ven, Janice, er på 0^th percentil, og Richard, den højeste, er 100^th percentil. Alle de andre venner er i lige store trin mellem de to, og det trin er lig med 1/(n-1), hvor n er antallet af datapunkter i intervallet.

I vores tilfælde er det:

1	1 / (10 - 1) = 11.111…

Det betyder, at Chandler faktisk ikke er 80^th percentil, han er på 77.777^th percentil. Hvis vi tilslutter dette nummer til PERCENTILE.INC, bør vi få Chandlers højde …

… og det gør vi.

Lineær interpolation

Når den k-værdi, du angiver, ikke er et multiplum af 1/(n-1), anvender PERCENTILE.INC en proces kaldet lineær interpolation for at beregne resultatet. Dette lyder kompliceret, men det betyder dybest set, at Excel vil nøjes med en værdi mellem de to.

Så hvorfor fik vi 183,4, da vi bad om 80'erne^th percentil tidligere?

Beregningen fungerer som følger:

• Den 80^th percentilen falder mellem Chandler og Ross, Chandler på 77.777^th percentil, og Ross på 88.888.
• Den 80^th percentilen er 2,222% foran Chandlers højde
• Vi ved, at der er 11% afstand mellem Chandler og Ross ’højder
• 222% / 11,111% = 20% efter afrunding. Nu ved vi, at 80^th percentilen er 20% af vejen mellem Ross og Chandlers højder.
• Forskellen mellem Ross ’højde på 185 cm og Chandlers højde på 183 cm er 2 cm
• 20% af 2 cm er 0,4 cm
• Tilføj det til Chandlers højde, og vi får 183,4 cm

PERCENTILE.EXC

Du bruger PERCENTILE.EXC på stort set samme måde:

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

Okay, så vi har de samme data, vi vil have den samme percentil, men vi har forskellige resultater. Hvorfor det?

Det er fordi PERCENTILE.EXC ekskluderer de første og sidste værdier ved beregning af dens percentilintervaller. Se nedenunder:

I stedet for at intervallet er lig med 1/(n-1), med PERCENTILE.EXC er intervallet 1/(n+1), eller i dette tilfælde 9,091%.

Alt andet fungerer på samme måde som med PERCENTILE.INC. Igen 80^th percentilen er mellem Chandler og Ross, så Excel anvender den samme metode til lineær interpolation:

• Den 80^th percentilen falder mellem Chandler og Ross, Chandler på 72.727^th percentil, og Ross på 81.818.
• Den 80^th percentilen er 7,272% foran Chandlers højde
• Vi ved, at der er en 9,091% afstand mellem Chandler og Ross ’højder
• 272% / 9,091% = 80% efter afrunding. Nu ved vi, at 80^th percentilen er 80% af vejen mellem Ross og Chandlers højder.
• Forskellen mellem Ross ’højde på 185 cm og Chandlers højde på 183 cm er 2 cm
• 80% af 2 cm er 1,6 cm
• Tilføj det til Chandlers højde, og vi får 184,6 cm

PERCENTILE i Google Sheets

PERCENTILE -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:

PERCENTILE Eksempler i VBA

Du kan også bruge funktionen PERCENTILE i VBA. Type:
application.worksheetfunction.percentile (array, k)
For funktionsargumenterne (array osv.) Kan du enten indtaste dem direkte i funktionen eller definere variabler, der skal bruges i stedet.

Tilbage til listen over alle funktioner i Excel