Funzioni PERCENTILE di Excel - Calcola k-esimo percentile

Scarica esempio di cartella di lavoro

Scarica la cartella di lavoro di esempio

Questo tutorial mostra come usare il Funzione PERCENTILE di Excel in Excel per calcolare il percentile.

Panoramica della funzione PERCENTILE

La funzione PERCENTILE Calcola il k-esimo percentile.

Per utilizzare la funzione del foglio di lavoro Excel PERCENTILE, selezionare una cella e digitare:

(Notare come appaiono gli input della formula)

Funzione PERCENTILE Sintassi e input:

1	=PERCENTILE(matrice,k)

Vettore - Una serie di numeri.

K - Il valore percentile compreso tra 0 e 1.

Che cos'è la funzione PERCENTILE?

PERCENTILE è una delle funzioni statistiche di Excel. Restituisce il k-esimo percentile di un dato intervallo di dati.

PERCENTILE è una funzione di "compatibilità"

A partire da Excel 2010, Microsoft ha sostituito PERCENTILE con due varianti: PERCENTILE.INC e PERCENTILE. ECC.

PERCENTILE funziona ancora, quindi i fogli di lavoro meno recenti che lo utilizzano continueranno a funzionare normalmente. Tuttavia, se non hai bisogno che il tuo foglio di calcolo rimanga compatibile con le versioni precedenti di Excel, dovresti usare PERCENTILE.INC o PERCENTILE.EXC.

Discuteremo tutti e tre in modo che tu capisca i dettagli di ciascuno.

Qual è il percentile?

Il percentile ti dice come un valore si confronta con gli altri valori in un dato intervallo. Quando un valore è al k-esimo percentile, significa che è superiore al k% degli altri valori del gruppo.

Ad esempio, se sei un maschio che vive negli Stati Uniti e sei alto 180 cm, sei nel 71° percentile: sei più alto del 71% degli altri maschi negli Stati Uniti. Se sei una donna e sei alta 180 cm, sei nel 99^ns percentile - sei più alto del 99% delle altre femmine.

Quando hai a che fare con i percentili, nota che il 50^ns percentile è uguale alla mediana <> dei dati.

La funzione PERCENTILE di Excel ti dice qual è il punto di interruzione per un dato percentile, ovvero quanto deve essere alto un valore per essere nel k-esimo percentile.

Come utilizzare la funzione PERCENTILE

Usa PERCENTILE in questo modo:

1	=PERCENTILE(C4:C13,F3)

Qui abbiamo alcuni amici che hanno deciso di calcolare l'80^ns percentile dell'altezza del loro gruppo e vediamo il risultato - 183,4 cm.

Il primo argomento della funzione è l'intervallo di dati - le loro altezze, che abbiamo esposto in C4:C13.

L'argomento successivo è il percentile. In questo esempio, ho inserito il percentile nella cella F3 e poi ho fatto riferimento a quella cella nella funzione. In questo modo, è facile modificare il percentile e vedere i nuovi risultati.

Tuttavia, se lo desideri, puoi inserire il percentile direttamente nella formula. Puoi definirlo come un decimale, in questo modo:

1	=PERCENTILE(C4:C13,.8)

O in percentuale, in questo modo:

1	=PERCENTILE(C4:C13,80%)

E questi restituiscono tutti lo stesso valore.

Alcune cose da tenere presente con PERCENTILE:

• Se il percentile che definisci non è un numero, la funzione restituirà un #VALORE! errore
• Se il percentile è minore di 0 o maggiore di 1, otterrai un #NUM! errore
• Il testo e le celle vuote all'interno dell'intervallo di dati verranno ignorate

Due metodi di calcolo dei percentili

Mettiamo in ordine i nostri dati per un secondo:

Potresti chiederti perché gli anni '80^ns percentile non è 183, poiché Chandler è l'8^ns persona più alta del gruppo.

Bene, ci sono in realtà molti modi per calcolare matematicamente il percentile e nessun metodo standardizzato o preferito per tutte le situazioni. Questo è qualcosa su cui gli statistici discutono nei documenti accademici.

Excel ti offre due modi per farlo, il che ci porta a PERCENTILE.INC e PERCENTILE.EXC.

PERCENTILE.INC

La funzione PERCENTILE.INC di Excel è uguale a PERCENTILE. La parte "INC" è l'abbreviazione di inclusivo, perché può calcolare qualsiasi percentile valido (ovvero, qualsiasi valore compreso tra 0% e 100%).

Lo usi così:

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

Come puoi vedere, restituisce lo stesso risultato di PERCENTILE in precedenza.

Quindi, tornando al perché gli anni '80^ns percentile non è esattamente uguale all'altezza di Chandler. Ricorda, stiamo facendo un calcolo inclusivo qui, quindi stiamo includendo tutti i valori k da 0 a 1 (o da 0% a 100%).

Ciò significa che la nostra amica più bassa, Janice, è allo 0^ns percentile, e Richard, il più alto, è il 100^ns percentile. Tutti gli altri amici hanno incrementi uguali tra i due e tale incremento è uguale a 1/(n-1), dove n è il numero di punti dati nell'intervallo.

Nel nostro caso, questo è:

1	1 / (10 - 1) = 11.111…

Ciò significa che Chandler non è in realtà agli 80^ns percentile, è a 77.777^ns percentile. Se inseriamo quel numero in PERCENTILE.INC, dovremmo ottenere l'altezza di Chandler…

… e lo facciamo.

Interpolazione lineare

Ora, ogni volta che il valore k specificato non è un multiplo di 1/(n-1), PERCENTILE.INC applicherà un processo chiamato interpolazione lineare per calcolare il risultato. Sembra complicato, ma fondamentalmente significa che Excel si accontenterà di un valore tra i due.

Allora perché abbiamo ottenuto 183.4 quando abbiamo chiesto l'80^ns percentile prima?

Il calcolo funziona come segue:

• L'80^ns percentile cade tra Chandler e Ross, Chandler al 77.777^ns percentile e Ross all'88,888°.
• L'80^ns il percentile è del 2,222% avanti rispetto all'altezza di Chandler
• Sappiamo che c'è una distanza dell'11% tra le altezze di Chandler e Ross
• 222% / 11,111% = 20% dopo l'arrotondamento. Ora sappiamo che l'80^ns percentile è il 20% della distanza tra le altezze di Ross e Chandler.
• La differenza tra l'altezza di 185 cm di Ross e l'altezza di 183 cm di Chandler è di 2 cm
• Il 20% di 2 cm è 0,4 cm
• Aggiungilo all'altezza di Chandler e otteniamo 183,4 cm

PERCENTILE.EXC

Usi PERCENTILE.EXC essenzialmente allo stesso modo:

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

Ok, abbiamo gli stessi dati, vogliamo lo stesso percentile, ma abbiamo risultati diversi. Perché?

È perché PERCENTILE.EXC esclude il primo e l'ultimo valore quando calcola i suoi intervalli percentili. Vedi sotto:

Anziché l'intervallo è uguale a 1/(n-1), con PERCENTILE.EXC l'intervallo è 1/(n+1), o in questo caso 9,091%.

Tutto il resto funziona allo stesso modo di PERCENTILE.INC. Ancora una volta, l'80^ns percentile è tra Chandler e Ross, quindi Excel applica lo stesso metodo di interpolazione lineare:

• L'80^ns il percentile cade tra Chandler e Ross, Chandler al 72.727^ns percentile e Ross all'81,818°.
• L'80^ns il percentile è del 7,272% avanti rispetto all'altezza di Chandler
• Sappiamo che c'è una distanza del 9,091% tra le altezze di Chandler e Ross
• 272% / 9,091% = 80% dopo l'arrotondamento. Ora sappiamo che l'80^ns percentile è l'80% della distanza tra le altezze di Ross e Chandler.
• La differenza tra l'altezza di 185 cm di Ross e l'altezza di 183 cm di Chandler è di 2 cm
• L'80% di 2 cm è 1,6 cm
• Aggiungilo all'altezza di Chandler e otteniamo 184,6 cm

PERCENTILE in Fogli Google

La funzione PERCENTILE funziona esattamente allo stesso modo in Fogli Google come in Excel:

PERCENTILE Esempi in VBA

Puoi anche usare la funzione PERCENTILE in VBA. Tipo:
application.worksheetfunction.percentile(array,k)
Per gli argomenti della funzione (array, ecc.), puoi inserirli direttamente nella funzione o definire le variabili da utilizzare al loro posto.

Torna all'elenco di tutte le funzioni in Excel