Excel: Le Funzioni PERCENTILE e QUARTILE di Excel

Le Funzioni Excel per il calcolo del PERCENTILE e del QUARTILE

I Percentili sono degli indicatori statistici e si utilizzano per confrontare la posizione di un valore rispetto ad una intera popolazione.
Per questo tipo di calcolo Excel mette a disposizione la Funzione PERCENTILE.
La Funzione PERCENTILE, per garantire una maggiore precisione di calcolo, nella versione 2010 del programma Excel è stata sviluppata in due nuove Funzioni ESC.PERCENTILE e INC.PERCENTILE.
Entrambe le Funzioni ESC.PERCENTILE e INC.PERCENTILE trovano il k-esimo Percentile di un set di dati fornito (la differenza fra le due Funzioni sarà successivamente definita nel tutorial).
Il Percentile viene definito con un punteggio, Il Rango percentile viene invece rappresentato come una percentuale.
In queste analisi statistiche di distribuzione il 50° Percentile coincide con la Funzione MEDIANA. Inoltre, Il venticinquesimo, il cinquantesimo e il settantacinquesimo Percentile vengono rispettivamente indicati come primo, secondo e terzo QUARTILE.
Anche la Funzione QUARTILE di Excel nel 2010 è stata sviluppata in due nuove Funzioni ESC.QUARTILE e INC.QUARTILE

Argomento abbastanza complesso, soprattutto in Funzione dei cambiamenti introdotti da Microsoft con la versione 2010.
In questo tutorial vengono presentate tutte le Funzioni ESC.PERCENTILE; INC.PERCENTILE; ESC.QUARTILE e INC.QUARTILE.
Le Funzioni vengono definite nella loro sintassi e spiegate con degli esempi di applicazione e comparazione. Comparazioni che coinvolgono anche altri due indicatori statistici: la Mediana e il Rango.

Contenuti del tutorial: Calcolare il Percentile e il Quartile con Excel

[1] - La Funzione PERCENTILE di Excel
[2] - Le nuove Funzioni ESC.PERCENTILE e INC.PERCENTILE
[3] - Sintassi delle Funzioni ESC.PERCENTILE e INC.PERCENTILE
[4] - Confronto e applicazione delle Funzioni ESC.PERCENTILE e INC.PERCENTILE e MEDIANA (2 esempi)
[5] - La Funzione QUARTILE di Excel
[6] - Sintassi delle Funzioni ESC.QUARTILE e INC.QUARTILE
[7] - Confronto e applicazione delle Funzioni ESC.QUARTILE e INC.QUARTILE
[8] - Calcolare il Percentile e il Rango con lo Strumento di Analisi di Excel (Componente aggiuntivo)

La Funzione PERCENTILE di Excel

La Funzione PERCENTILE di Excel è stata sostituita con due nuove Funzioni in grado di offrire una maggiore precisione di calcolo, le due Funzioni sono ESC.PERCENTILE e INC.PERCENTILE
Se in una cella del Foglio di lavoro si inizia a digitare il nome di questa Funzione, nei suggerimenti di Excel la Funzione PERCENTILE viene visualizzata con un punto esclamativo all'interno di un triangolo giallo.
Se con il mouse si punta la Funzione PERCENTILE, Excel ci informa che la Funzione è da considerarsi obsoleta e viene mantenuta solo per compatibilità con le vecchie versioni di Excel.

La sintassi della Funzione PERCENTILE presenta comunque gli stessi argomenti delle due nuove Funzioni ESC.PERCENTILE e INC.PERCENTILE

Le Funzioni ESC.PERCENTILE e INC.PERCENTILE di Excel

 Il Percentile (o Centile) è un indicatore statistico per individuare il minimo valore sotto al quale ricade una data percentuale degli altri elementi sotto osservazione.

Come anticipato, entrambe le Funzioni ESC.PERCENTILE e INC.PERCENTILE individuano il k-esimo percentile.
La differenza fra queste due Funzioni è che nella Funzione INC.PERCENTILE il valore k e compreso fra 0 (zero) e 1 (incluso).
Viceversa, ESC.PERCENTILE calcola il percentile in base a un intervallo compreso fra lo 0 (zero) e 1 (escluso).

Sintassi delle Funzioni ESC.PERCENTILE e INC.PERCENTILE

Entrambe le funzioni hanno la stessa identica sintassi, però basano il loro calcolo su un intervallo diverso, includendo o escludendo il numero 1.
=ESC.PERCENTILE(matrice;k)
=INC.PERCENTILE(matrice;k)
nella quale:
matrice rappresenta la matrice o l'intervallo dati per i quale si intende calcolare il k-esimo-percentile
k nel caso della Funzione ESC.PERCENTILE rappresenta un valore compreso fra 0 e 1 (escluso), nel caso della Funzione INC.Percentile rappresenta un valore compreso fra 0 e 1 (incluso).

:: l'argomento k della Funzione può essere imputato sia in formato percentuale sia decimale. Esempio 30% oppure 0,3

Confronto e applicazione delle Funzioni ESC.PERCENTILE e INC.PERCENTILE

Per confrontare le due Funzioni consideriamo il successivo esempio, nel quale è presente un elenco di dipendenti e per ognuno di essi viene considerata l'anzianità lavorativa.

Primo esempio calcolo PERCENTILE

Si vuole calcolare il 20° percentile. Ovvero individuare il 20% dei dipendenti con anzianità lavorativa più bassa.
Rispettivamente nelle celle D3 e D6 sono state inserite le due Funzioni:
=ESC.PERCENTILE(B2:B21;20%) =INC.PERCENTILE(B2:B21;20%)
Seppure le due Funzioni restituiscano un risultato leggermente diverso entrambe individuano il valore al disotto del quale è presente il 20% dei dipendenti con l'anzianità lavorativa più bassa.
Dal punto di vista pratico significa che il 20% (4 su 20) dei dipendenti hanno una anzianità lavorativa inferiore a 5,2 e 5,7.
La differenza dei due valori sta nell'algoritmo di calcolo che come precedentemente citato basa il suo calcolo su un intervallo (k) percentile diverso.

Nell'intervallo celle B2:B21, per mettere in evidenza il risultato, è stata impostata una formattazione condizionale che colora con testo rosso i valori inferiori al contenuto della cella D3.

Secondo esempio calcolo PERCENTILE

ll calcolo proposto in questo secondo esempio è molto simile al precedente, si vuole però calcolare il 50° percentile.
In questo caso, essendo la metà esatta, viene restituito lo stesso identico valore da entrambe le Funzioni applicate nelle celle D3 e D6.
=ESC.PERCENTILE(B2:B21;50%) =INC.PERCENTILE(B2:B21;50%)
Nell'esempio proposto sono stati inseriti altri due indicatori statistici la Mediana e il Quartile.
Come citato all'inizio del tutorial, in questo tipo di distribuzione il 50° Percentile coincide con con la Funzione MEDIANA, inoltre, Il venticinquesimo, il cinquantesimo, il settantacinquesimo Percentile vengono rispettivamente indicati come primo, secondo e terzo Quartile. Ovviamente, Anche il secondo Quartile individua la parte centrale esatta del campione.
Nella cella D9 è stata imputata la Funzione =MEDIANA(B2:B21)
Nelle sottostanti celle D12 e D15 sono state inserite le due Funzioni per il calcolo del secondo Quartile.

 Osservazione sulla Funzione MEDIANA inserita nell'esempio.
Si definisce come MEDIANA (o valore mediano) il valore o l'insieme di valori assunto dalle unità statistiche che si trovano nel mezzo della distribuzione. La Mediana è pertanto il numero che occupa la posizione centrale di un insieme di numeri, ovvero una metà dei numeri ha un valore superiore rispetto alla mediana, mentre l'altra metà ha un valore inferiore.
Qualora le serie dei valori (come in questo caso) fossero pari, la Funzione MEDIANA calcolerà la media dei due numeri che occupano la posizione centrale. Se si mettono in ordine crescente le anzianità di lavoro i due valori in posizione centrale sono il numero 10 e 11 la cui media restituisce come risultato  10,5

La Funzione QUARTILE di Excel

La Funzione QUARTILE restituisce il quartile (quarto di valori) all’interno di una serie di valori forniti.
La Funzione QUARTILE di Excel, nel 2010 è stata sviluppata in due nuove Funzioni in grado di offrire una maggiore precisione di calcolo. Le due Funzioni sono ESC.QUARTILE e INC.QUARTILE

Se in una cella del Foglio di lavoro si inizia a digitare il nome di questa Funzione, nei suggerimenti di Excel la Funzione QUARTILE viene visualizzata con un punto esclamativo all'interno di un triangolo giallo. Se con il mouse si punta la Funzione QUARTILE, Excel ci Informa la Funzione è da considerarsi obsoleta e viene mantenuta solo per compatibilità con vecchie versioni di Excel.

Sintassi delle Funzioni ESC.QUARTILE e INC.QUARTILE

Entrambe le Funzioni hanno la stessa identica sintassi, è solamente leggermente diverso il calcolo che queste effettuano.
=ESC.QUARTILE(matrice; quarto)
=INC.QUARTILE(matrice; quarto)
nella quale:
matrice rappresenta una matrice dati o intervallo di celle di valori numerici per i quali si desidera calcolare il quartile specificato
quarto nel caso di ESC.QUARTILE rappresenta un numero intero compreso fra 1 e 3 che rappresenta il quartile richiesto.
quarto nel caso di INC.QUARTILE rappresenta un numero intero compreso fra 0 e 4 che rappresenta il quartile richiesto.

La differenza fra queste due Funzioni, si evince nell'ipostazione dell'argomento quarto descritto nella sintassi.
La Funzione ESC.QUARTILE basa il suo calcolo su un intervallo percentile 1 e 3, mentre la Funzione INC.QUARTILE basa il calcolo su un intervallo percentile compreso fra 0 e 4 (inclusi).

OSSERVAZIONE.
Le due Funzioni INC.PERCENTILE e INC.QUARTILE sono in forte correlazione.
:: il  primo quartile corrisponde al 25° percentile.
:: il secondo quartile corrisponde la 50° percentile (vedi immagine 2° esercizio sul calcolo del percentile) a sua volta questo valore corrisponde anche con il calcolo della Mediana.
:: il terzo quartile corrisponde al 75° percentile
:: il quarto quartile corrisponde al 100° percentile

Confronto e applicazione delle Funzioni ESC.QUARTILE e INC.QUARTILE

Come per l'esempio del PERCENTILE, per confrontare le due Funzioni, consideriamo un esempio, nel quale è rappresentato un intervallo dati contenente un elenco di dipendenti e l'anzianità lavorativo di ognuno di essi.

Nell'ambito di questi dati si vuole calcolare il valore del primo e del secondo percentile.
Le Funzioni imputate nelle celle D4 e D6 sono rispettivamente: =ESC.QUARTILE(B2:B21;1) e =INC.QUARTILE(B2:B21;1)
Seppure le due Funzioni restituiscano un risultato diverse entrambe individuano il primo quarto ovvero il valore al disotto del quale è presente il 25% dei dipendenti con l'anzianità lavorativa più bassa.
Come precedentemente commentato, le due Funzioni nel secondo quartile indicano la metà esatta, pertanto coincidono esattamente come risultato.

 Come illustrato nel secondo esempio del Percentile a questo stesso identico valore coincide anche il 50° percentile calcolato con entrambe le Funzioni =ESC.PERCENTILE(B2:B21;50%) =INC.PERCENTILE(B2:B21;50%) e anche il calcolo della MEDIANA.

Calcolare il Percentile e il Rango con lo Strumento di Analisi di Excel
(Componente aggiuntivo)

Lo Strumento di analisi è un componente aggiuntivo del programma Excel e deve pertanto essere installato.
l'installazione è molto semplice: menu File, cliccare su Opzioni.
Una volta entrati nelle opzioni di Excel scegliere Componenti aggiuntivi e nella scheda a lato cliccare Vai...
Nella finestra di dialogo Componenti aggiuntivi  selezionare l'opzione Strumenti di analisi e confermare con un clic su OK.

Calcolare Percentile e Rango con lo Strumento di analisi

Una volta installato il componente aggiuntivo Strumento di analisi. nella scheda Dati presente sulla barra multifunzione viene reso disponibile il gruppo pulsanti Analisi dati, cliccando sul quale si apre l'omonima finestra di dialogo Analisi dati nella quale è possibile selezionare Rango e percentile.

Una volta selezionato Rango e percentile si apre una nuova finestra di dialogo nella quale è possibile impostare tutte le opzioni necessarie per il calcolo.
Intervallo di input: nel caso dell'esempio, devono essere indicati i soli dati dell'anzianità di lavoro, non deve essere selezionata la colonna con i dipendenti.
Se, come nell'esempio sottostante, nell'intervallo input è stata inclusa la cella di intestazione, selezionare l'opzione Etichette nella prima riga.
Quale ultima definizione, prima di confermare le impostazioni facendo clic su OK, individuare l'intervallo di output, ovvero la posizione della prima cella (in alto sinistra) dell'analisi dati.
Cliccato su ok, il risultato finale è visibile a destra nell'immagine sottostante.
Nella tabella creata dalla Analisi di dati è presente sia il calcolo del Percentile sia il calcolo del Rango.

Indice argomenti

[Valter Borsato: 03/01/2021]