Excel PERCENTILE -funktioner - Beräkna kth percentilen

Ladda ner exempel på arbetsbok

Ladda ner exempelarbetsboken

Denna handledning visar hur du använder Excel PERCENTILE -funktion i Excel för att beräkna percentilen.

PERCENTIL Funktionsöversikt

Funktionen PERCENTIL Beräknar kth percentilen.

Om du vill använda funktionen PERCENTILE Excel -kalkylblad väljer du en cell och skriver:

(Lägg märke till hur formelinmatningarna ser ut)

PERCENTILE -funktion Syntax och ingångar:

1	= PERCENTIL (array, k)

array - En rad siffror.

k - Procentilvärdet mellan 0 och 1.

Vad är PERCENTILE -funktionen?

PERCENTILE är en av Excel: s statistiska funktioner. Den returnerar k-th-percentilen för ett givet dataintervall.

PERCENTILE är en "kompatibilitet" -funktion

Från och med Excel 2010 ersatte Microsoft PERCENTILE med två varianter: PERCENTILE.INC och PERCENTILE. EXC.

PERCENTILE fungerar fortfarande, så äldre kalkylblad som använder det fortsätter att fungera som vanligt. Men om du inte behöver ditt kalkylblad för att vara kompatibelt med äldre versioner av Excel, bör du använda PERCENTILE.INC eller PERCENTILE.EXC.

Vi kommer att diskutera alla tre så att du förstår insatserna i var och en.

Vad är percentilen?

Procentilen berättar hur ett värde kan jämföras med de andra värdena inom ett givet intervall. När ett värde är vid k-percentilen betyder det att det är högre än k% av de andra värdena i gruppen.

Till exempel, om du är en hane som bor i USA och du är 180 cm lång, ligger du i 71: e percentilen - du är högre än 71% av andra män i USA. Om du är kvinna och 180 cm lång är du i 99: an^th percentil - du är högre än 99% av andra kvinnor.

Observera att 50 -talet när du har att göra med percentiler^th percentilen är lika med medianen <> av data.

Excel PERCENTILE -funktionen berättar vad avstängningspunkten för en given percentil är - det vill säga hur högt ett värde måste vara för att vara i k -percentilen.

Hur man använder funktionen PERCENTIL

Använd PERCENTIL så här:

1	= PERCENTIL (C4: C13, F3)

Här har vi några vänner som har bestämt sig för att beräkna 80: an^th percentilen av höjden på deras grupp, och vi ser resultatet - 183,4 cm.

Det första argumentet i funktionen är dataområdet - deras höjder, som vi har lagt fram i C4: C13.

Nästa argument är percentilen. I det här exemplet har jag lagt percentilen i cell F3 och sedan refererat till den cellen i funktionen. På så sätt är det enkelt att ändra percentilen och se de nya resultaten.

Du kan dock lägga procentilen direkt i formeln om du vill. Du kan definiera det som en decimal, så här:

1	= PERCENTIL (C4: C13, .8)

Eller i procent, så här:

1	= PERCENTIL (C4: C13,80%)

Och dessa ger alla samma värde.

Några saker att vara medveten om med PERCENTILE:

• Om percentilen du definierar inte är ett tal returnerar funktionen ett #VÄRDE! fel
• Om percentilen är mindre än 0 eller större än 1 får du ett #NUM! fel
• Text och tomma celler inom ditt dataintervall ignoreras

Två metoder för att beräkna procentsatser

Låt oss ordna våra data för en sekund:

Du kanske undrar varför 80 -talet^th percentilen är inte 183, eftersom Chandler är åtta^th högsta personen i gruppen.

Tja, det finns faktiskt många sätt att beräkna percentilen matematiskt, och ingen standardiserad eller föredragen metod för alla situationer. Detta är något statistiker argumenterar över i akademiska uppsatser.

Excel ger dig två sätt att göra detta, vilket tar oss till PERCENTILE.INC och PERCENTILE.EXC.

PERCENTIL.INC

Funktionen Excel PERCENTILE.INC är densamma som PERCENTILE. "INC" -delen är en förkortning för inkluderande, eftersom den kan beräkna valfri percentil (det vill säga allt från 0% till 100%).

Du använder det så här:

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

Som du kan se returnerar det samma resultat som PERCENTILE gjorde tidigare.

Så återgå till varför 80 -talet^th percentilen är inte exakt lika med Chandlers höjd. Kom ihåg att vi gör en inkluderande beräkning här, så vi inkluderar alla k -värden från 0 till 1 (eller 0% till 100%).

Det betyder att vår kortaste vän, Janice, ligger på 0^th percentilen, och Richard, den högsta, är 100^th procentil. Alla andra vänner har lika stora steg mellan de två, och den ökningen är lika med 1/(n-1), där n är antalet datapunkter i intervallet.

I vårt fall är det:

1	1 / (10 - 1) = 11.111…

Det betyder att Chandler faktiskt inte är på 80 -talet^th procent, ligger han på 77.777^th procentil. Om vi ​​ansluter det numret till PERCENTILE.INC bör vi få Chandlers höjd …

… och det gör vi.

Linjär interpolation

När k-värdet du anger inte är en multipel av 1/(n-1) kommer PERCENTILE.INC att tillämpa en process som kallas linjär interpolation för att beräkna resultatet. Det här låter komplicerat, men det betyder i grunden att Excel kommer att nöja sig med ett värde mellan de två.

Så varför fick vi 183,4 när vi bad om 80: an^th percentilen tidigare?

Beräkningen fungerar enligt följande:

• 80 -talet^th percentilen faller mellan Chandler och Ross, Chandler på 77.777^th percentilen och Ross vid 88.888: e.
• 80 -talet^th percentilen ligger 2,222% före Chandlers höjd
• Vi vet att det är 11% avstånd mellan Chandler och Ross höjder
• 222% / 11,111% = 20% efter avrundning. Nu vet vi att 80 -talet^th percentilen är 20% av vägen mellan Ross och Chandlers höjder.
• Skillnaden mellan Ross ’höjd på 185 cm och Chandlers höjd på 183 cm är 2 cm
• 20% av 2cm är 0,4cm
• Lägg till det på Chandlers höjd, så får vi 183,4 cm

PERCENTILE.EXC

Du använder PERCENTILE.EXC på i stort sett samma sätt:

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

Okej, så vi har samma data, vi vill ha samma percentil, men vi har olika resultat. Varför är det så?

Det beror på att PERCENTILE.EXC utesluter de första och sista värdena vid beräkning av dess percentilintervall. Se nedan:

Snarare än att intervallet är lika med 1/(n-1), med PERCENTILE.EXC är intervallet 1/(n+1), eller i detta fall 9,091%.

Allt annat fungerar på samma sätt som med PERCENTILE.INC. Återigen 80^th percentilen är mellan Chandler och Ross, så Excel tillämpar samma metod för linjär interpolering:

• 80 -talet^th percentilen faller mellan Chandler och Ross, Chandler vid 72.727^th percentilen och Ross på 81.818: e.
• 80 -talet^th percentilen ligger 7.272% före Chandlers höjd
• Vi vet att det finns ett avstånd på 9.091% mellan Chandler och Ross höjder
• 272% / 9,091% = 80% efter avrundning. Nu vet vi att 80 -talet^th percentilen är 80% av vägen mellan Ross och Chandlers höjder.
• Skillnaden mellan Ross ’höjd på 185 cm och Chandlers höjd på 183 cm är 2 cm
• 80% av 2cm är 1,6cm
• Lägg till det till Chandlers höjd, så får vi 184,6 cm

PERCENTIL i Google Kalkylark

PERCENTILE -funktionen fungerar exakt samma i Google Kalkylark som i Excel:

PERCENTILE Exempel i VBA

Du kan också använda funktionen PERCENTIL i VBA. Typ:
application.worksheetfunction.percentile (array, k)
För funktionsargumenten (array, etc.) kan du antingen ange dem direkt i funktionen eller definiera variabler som ska användas istället.

Återgå till listan över alla funktioner i Excel