Come calcolare i percentili in Excel

Come calcolare i percentili in Excel

Excel offre due funzioni principali per calcolare i percentili: PERCENTILE.INC() e PERCENTILE.EXC().

A voler essere precisi, esiste anche una terza funzione—PERCENTILE()—usata nelle vecchie versioni del software (Excel 2007 e precedenti) e mantenuta oggi per compatibilità. Questa vecchia funzione funziona allo stesso modo di PERCENTILE.INC().

In questo tutorial ci concentreremo sulle funzioni più recenti—PERCENTILE.INC() e PERCENTILE.EXC().

Funzione Excel PERCENTILE.INC()

Sintassi

PERCENTILE.INC(array, k)

dove:

• array—l'array o l'intervallo di dati per cui calcolare il percentile,

• k—il valore del percentile nell'intervallo da 0 a 1, inclusi.

Entrambi gli argomenti sono obbligatori.

La funzione PERCENTILE.INC() restituisce il k-esimo percentile dei valori in un intervallo, dove k è compreso tra 0 e 1, inclusi i valori 0 e 1. In altre parole, questa funzione include il primo e l'ultimo valore dell'array.

Algoritmo

Dietro le quinte, la funzione PERCENTILE.INC() in Excel esegue i seguenti passaggi:

1. Ordina i valori dell'array fornito in ordine crescente.
2. Determina il valore del percentile in base all'argomento k e al numero N di punti dati nell'array usando la formula: k(N-1) + 1.
3. Se il risultato del passaggio precedente non è un intero, l'algoritmo interpola tra i valori più vicini dell'array.

Esempio

Supponiamo di voler calcolare il 30º percentile per un array di interi da 1 a 10 inclusi, usando la funzione PERCENTILE.INC() di Excel. Ecco come apparirà la nostra formula:

Nota: Sebbene i valori dell'array sopra siano ordinati a scopo illustrativo, in realtà non è necessario ordinarli in anticipo, poiché l'algoritmo lo farà automaticamente.

Di seguito il risultato del nostro calcolo:

Possibili errori

• #NUM!— quando l'array fornito è vuoto, oppure k < 0, oppure k > 1.

• #VALUE!—quando k non è numerico.

Funzione Excel PERCENTILE.EXC()

Sintassi

PERCENTILE.EXC(array, k)

dove:

• array—l'array o l'intervallo di dati per cui calcolare il percentile,

• k—il valore del percentile nell'intervallo da 0 a 1, esclusi.

Entrambi gli argomenti sono obbligatori.

La funzione PERCENTILE.EXC() restituisce il k-esimo percentile dei valori in un intervallo, dove k è compreso tra 0 e 1, escludendo i valori 0 e 1. Più precisamente, per un array con N punti dati, il valore di k deve soddisfare entrambe le seguenti condizioni:

1. K > 1/(N+1)
2. K < N/(N+1)

In parole semplici, la funzione PERCENTILE.EXC() definisce i percentili solo per probabilità strettamente comprese tra 0 e 1. Ciò significa che non calcola valori per il minimo assoluto (0º percentile) o per il massimo (100º percentile), sebbene tutti i punti dati vengano comunque utilizzati nell'interpolazione.

Algoritmo

Dietro le quinte, la funzione PERCENTILE.EXC() in Excel esegue i seguenti passaggi:

1. Ordina i valori dell'array fornito in ordine crescente.
2. Determina il valore del percentile in base all'argomento k e al numero N di punti dati nell'array usando la formula: k(N+1).
3. Se il risultato del passaggio precedente non è un intero, l'algoritmo interpola tra i valori più vicini dell'array.

Esempio

Supponiamo di voler calcolare il 30º percentile per un array di interi da 1 a 10 inclusi, usando la funzione PERCENTILE.EXC() di Excel. Ecco come apparirà la nostra formula:

Nota: Sebbene i valori dell'array sopra siano ordinati a scopo illustrativo, in realtà non è necessario ordinarli in anticipo, poiché l'algoritmo lo farà automaticamente.

Di seguito il risultato del nostro calcolo:

Possibili errori

• #NUM!— quando l'array fornito è vuoto, oppure k <= 1/(N+1), oppure k >= N/(N+1).

• #VALUE!—quando k non è numerico.

Per imparare o ripassare altre funzioni popolari di Excel, consulta questo tutorial: The 15 Basic Excel Formulas Everyone Needs to Know.

PERCENTILE.INC vs. PERCENTILE.EXC in Excel

Finora è chiaro che ci sono molte somiglianze tra le funzioni PERCENTILE.INC() e PERCENTILE.EXC() di Excel. Tuttavia, esistono anche differenze nette che le rendono due funzioni separate e portano a risultati diversi. Ricapitoliamo in che modo le funzioni PERCENTILE.INC() e PERCENTILE.EXC() in Excel sono simili e in cosa differiscono.

Somiglianze:

1. Sintassi della funzione.

2. Logica generale dell'algoritmo (tranne che per la formula usata per determinare il valore del percentile).

3. Errori possibili (#NUM! quando l'array è vuoto o k è fuori dall'intervallo possibile e #VALUE! quando k non è numerico).

Differenze:

1. Intervallo possibile per l'argomento k:

• PERCENTILE.INC()—da 0 a 1, inclusi.

• PERCENTILE.EXC()—da 1/(N+1) a N/(N+1), esclusi.

2. Trattamento dei valori di un array:

• PERCENTILE.INC()—include il primo e l'ultimo valore.

• PERCENTILE.EXC()—non definisce i percentili al primo e all'ultimo valore (0% e 100%), ma include comunque tutti i dati nella sua interpolazione.

3. Formula per determinare il valore del percentile:

• PERCENTILE.INC()—k(N-1) + 1.

• PERCENTILE.EXC()—k(N+1).

Vediamo le funzioni PERCENTILE.INC() e PERCENTILE.EXC() in azione applicandole entrambe allo stesso array di numeri interi da 1 a 10 inclusi e giocando con diversi valori di k:

La tabella sopra mostra che la funzione PERCENTILE.EXC() non è definita per valori di k al di fuori o ai limiti del suo intervallo valido (k ≤ 1/(N+1) o k ≥ N/(N+1)). Questo non è un difetto: riflette la definizione matematica dei percentili esclusivi. Le piccole differenze tra i risultati di .INC() e .EXC() vicino agli estremi derivano dal modo in cui ciascuna funzione interpola le posizioni percentile.

D'altra parte, va ricordato che nella stragrande maggioranza dei casi, valori di k troppo bassi o troppo alti non sono particolarmente utili dal punto di vista statistico. Queste limitazioni raramente rappresentano un problema nella pratica. Inoltre, nella vita reale, di solito dobbiamo calcolare i percentili per array molto più grandi di quello del nostro esempio, il che attenua le differenze di risultato.

Nella maggior parte dei casi, ha più senso optare per la funzione PERCENTILE.INC() di Excel, che corrisponde anche alle formule predefinite usate per calcolare i percentili in molti linguaggi di programmazione, tra cui Python e R.

Tuttavia, se vogliamo evitare di definire i percentili ai margini del dataset, la funzione PERCENTILE.EXC() è la scelta appropriata. Non calcolerà risultati per il 0º o il 100º percentile, ma utilizza comunque tutti i punti dati quando interpola tra i ranghi.

È importante avere dati puliti e rappresentativi per ricavarne insight di business di valore. Il corso Data Preparation in Excel e la Data Manipulation in Excel Cheat Sheet sono risorse utili per imparare a preparare i dati in modo efficiente per ulteriori analisi.

Conclusione

Abbiamo discusso la teoria statistica essenziale alla base dei percentili, la loro definizione, i principali ambiti di applicazione e le due funzioni principali per calcolarli in Excel, includendone sintassi, algoritmi, esempi di calcolo ed errori potenziali.

Abbiamo anche confrontato le due funzioni di Excel, riassunto analogie e differenze importanti, applicato entrambe sullo stesso array di dati per confrontarne i risultati e individuato lo scenario migliore per ciascuna.

Per sfruttare appieno la potenza di Excel, considera il percorso di competenze per principianti Excel Fundamentals, che ti aiuterà a imparare, con esercizi pratici, sia le funzionalità e formule di base sia quelle avanzate per la preparazione, la visualizzazione e l'analisi dei dati in Excel.