La distribuzione normale su Excel
In questa lezione ti spiego come calcolare la distribuzione normale (curva normale o gaussiana) di una distribuzione di valori X per una media e una deviazione standard specificata.
Cos'è la distribuzione normale? Per costruire la distribuzione normale di una sequenza di valori si utilizza la formula $$ y = \frac{1}{\sigma \cdot \sqrt{2 \pi}} \cdot e^{- \frac{(x-\mu)^2}{2 \sigma^2}} $$ Dove x sono i valori della distribuzione X, μ è la media, σ è la deviazione standard. Il risultato è la tipica curva "a campana" in cui i dati partono da zero o da valori molto piccoli, raggiungono il massimo e poi tornano a zero.
La distribuzione normale
Per calcolare la curva gaussiana di una distribuzione su Excel usa la funzione
=DISTRIB.NORM.N(x,m,d,c)
La funzione ha quattro parametri
- x è un valore della distribuzione di valori originaria X
- m è la media di X da usare per calcolare la gaussiana
- d è la deviazione standard (scarto quadratico medio) di X per calcolare la gaussiana
- c è un parametro booleano per ottenere il cumulativo (1) della distribuzione normale oppure no (0)
Il risultato è la distribuzione normale.
Nota. Per ottenere la curva gaussiana indica zero c=0 o c=Falso come ultimo parametro. Se indichi c=1 o c=Verola funzione calcola il cumulativo ossia l'integrale della distribuzione normale da meno infinito a x.
Ti faccio un esempio pratico
Digita questa distribuzione di valori nell'intervallo di celle B3:B9.
Inserisci la funzione =MEDIA(B3:B9) nella cella B11 per calcolare la media della distribuzione.
La media aritmetica dei valori è 24
Inserisci la funzione =DEV.ST.P(B3:B9) nella cella B12 per calcolare la deviazione standard.
La deviazione standard dei valori è 4
Ora digita la funzione =DISTRIB.NORM.N(B3;$B$11;$B$12;0) nella cella C3
La funzione calcola il valore 18 della cella B3 nella distribuzione normale.
Il risultato è 0,032379399
Infine copia la cella C3 nell'intervallo C4:C9
Il risultato finale è la distribuzione normale dei dati calcolata sulla media μ=24 e la deviazione standard σ=4.
Se rappresenti graficamente i dati nell'intervallo C3:C9 viene visualizzato il classico grafico a campana della curva normale (curva gaussiana).
Nota. In questo caso i dati sono molto pochi e la curva a campana è molto grezza. Tuttavia, l'andamento è quello. La curva raggiunge il massimo in corrispondenza al valore medio (μ=24). Si può già notare a colpo d'occhio che la distribuzione dei valori {18,20,22,24,26,28,30} è simmetrica perché nella curva normale le code a sinistra e destra del massimo sono uguali.
Se modifichi l'ultimo parametro della formula da 0 a 1 ottieni il cumulativo.
Ad esempio digita =DISTRIB.NORM.N(B3;$B$11;$B$12;1) nella cella C3
Poi copia la cella C3 nell'intervallo C4:C9
In questo caso il risultato è il cumulativo da 0 a 1
Il grafico della curva cumulata è crescente.
La distribuzione normale standard
Se la distribuzione di origine è una distribuzione normale standard, per ottenere la curva normale puoi usare anche la funzione DISTRIB.NORM.ST.N(x;c)
=DISTRIB.NORM.ST.N(x;c)
La funzione ha due parametri obbligatori
- x è un valore della distribuzione standardizzata di valori originaria X
- c è un parametro booleano per calcolare il cumulativo (1) oppure no (0)
Il risultato è la distribuzione normale.
Cos'è la distribuzione normale standardizzata? E' una distribuzione in cui la media dei valori è uguale a zero e la deviazione standard è uguale a 1. Si ottiene con la funzione NORMALIZZA().
Ti faccio un esempio pratico.
Riprendi la distribuzione non standardizzata del caso precedente.
Digita =NORMALIZZA(B3;$B$11;$B$12) nella cella E3 per standardizzare i dati della distribuzione
La funzione calcola il valore normale standard (-1,5) del valore 18 contenuto nella cella B3
Poi copia la cella E3 nell'intervallo E4:E9
I valori nella colonna E sono la distribuzione normale standardizzata
La distribuzione normale standardizzata è caratterizzata da una media uguale a zero e una deviazione standard uguale a uno.
Verifica. Digita la funzione =MEDIA(E3:E9) nella cella E11 e la funzione =DEV.ST.P(E3;E9) nella cella E12. In questo caso la media della distribuzione standardizzata è nulla e la deviazione standard è unitaria.
Ora digita =DISTRIB.NORM.ST.N(E3;0) nella cella F3
La funzione calcola il relativo valore nella distribuzione normale
Nota. Il valore 0,129517596 ottenuto con la funzione =DISTRIB.NORM.ST.N(E3;0) nella cella F3 è lo stesso risultato che avresti ottenuto digitando la funzione =DISTRIB.NORM.N(E3;0;1;0) nella stessa cella specificando la media uguale a zero e la deviazione standard uguale a uno.
Ora copia la cella F3 nell'intervallo F4:F9
Il risultato finale è la distribuzione normale dei dati standardizzati calcolati sulla media μ=0 e la deviazione standard σ=1.
Se rappresenti graficamente i dati nell'intervallo F3:F9 ottieni il grafico a campana della curva normale (curva gaussiana).
In questo modo puoi calcolare sul foglio Excel la curva normale e il cumulativo di qualsiasi distribuzione di valori.
Un esempio pratico
In questo esempio ti spiego come usare la distribuzione normale e a cosa serve.
Considera questa distribuzione di dati.
Questi dati mostrano le altezze degli studenti in una classe misurate in metri
Calcola la media delle altezze tramite la funzione =MEDIA(B3:B11) nella cella B13
Poi calcola la deviazione standard tramite la funzione =DEV.ST.P(B3:B11) nella cella B14
La conoscenza della media e della deviazione standard ti permette di calcolare la distribuzione normale delle probabilità.
Digita le classi delle altezze in ordine crescente.
Ora calcoliamo le varie probabilità delle classi.
La probabilità dell'altezza fino a 1,60 metri
La probabilità di avere un'altezza inferiore a 1,60 metri è uguale al distribuzione cumulata fino a 1,60.
Digita la funzione =DISTRIB.NORM.N(1,60;$B$13;$B$14;1) nella cella E3
La probabilità che uno studente abbia un'altezza inferiore a 1,60 metri è 0,83%
Spiegazione. Questo dato è tratto dalla parte iniziale della distribuzione normale cumulata. La probabilità fino a 1,60 metri è circa 0,0083 ossia 0,83%
La probabilità dell'altezza tra 1,60 e 1,65 metri
La probabilità di avere un'altezza trainferiore a 1,60 e 1,65 metri è uguale alla differenza tra la distribuzione cumulata fino a 1,65 e quella fino a 1,60.
Digita la funzione =DISTRIB.NORM.N(1,65;$B$13;$B$14;1)-DISTRIB.NORM.N(1,60;$B$13;$B$14;1) nella cella E4
La probabilità che uno studente abbia un'altezza tra 1,60 e 1,65 metri è 3,03%
Spiegazione. La probabilità fino a 1,65 metri è 0,038 (ossia 3,8%) quella fino a 1,60 metri è 0,008 (ossia 0,8%). Pertanto, la probabilità che l'altezza sia compresa tra 1,60 e 1,65 è uguale a 0,038 - 0,008 = 0,03 ossia al 3%.
La probabilità dell'altezza tra 1,65 e 1,70 metri
La probabilità di avere un'altezza tra 1,65 e 1,70 metri è uguale alla differenza tra la distribuzione cumulata fino a 1,70 e quella fino a 1,65.
Digita la funzione =DISTRIB.NORM.N(1,70;$B$13;$B$14;1)-DISTRIB.NORM.N(1,65;$B$13;$B$14;1) nella cella E5
La probabilità che uno studente abbia un'altezza tra 1,65 e 1,70 metri è 8,84%
Spiegazione. La probabilità fino a 1,70 metri è 0,127 (ossia 12,7%) quella fino a 1,65 metri è 0,038 (ossia 3,8%). Pertanto, la probabilità che l'altezza sia compresa tra 1,65 e 1,70 è uguale approssimativamente a 0,127 - 0,038 = 0,089 ossia al 8,9%.
La probabilità dell'altezza tra 1,70 e 1,75 metri
La probabilità di avere un'altezza tra 1,70 e 1,75 metri è uguale alla differenza tra la distribuzione cumulata fino a 1,75 e quella fino a 1,70.
Digita la funzione =DISTRIB.NORM.N(1,75;$B$13;$B$14;1)-DISTRIB.NORM.N(1,70;$B$13;$B$14;1) nella cella E6
La probabilità che uno studente abbia un'altezza tra 1,70 e 1,75 metri è 17,63%
Spiegazione. La probabilità fino a 1,75 metri è 0,303(ossia 30,3%) quella fino a 1,70 metri è 0,127 (ossia 12,7%). Pertanto, la probabilità che l'altezza sia compresa tra 1,70 e 1,75 è uguale approssimativamente a 0,303 - 0,127 = 0,176 ossia al 17,6%.
La probabilità dell'altezza tra 1,75 e 1,80 metri
La probabilità di avere un'altezza tra 1,75 e 1,80 metri è uguale alla differenza tra la distribuzione cumulata fino a 1,80 e quella fino a 1,75.
Digita la funzione =DISTRIB.NORM.N(1,80;$B$13;$B$14;1)-DISTRIB.NORM.N(1,75;$B$13;$B$14;1) nella cella E7
La probabilità che uno studente abbia un'altezza tra 1,75 e 1,80 metri è 24,07%
Spiegazione. La probabilità fino a 1,80 metri è 0,544(ossia 54,4%) quella fino a 1,75 metri è 0,303 (ossia 30,3%). Pertanto, la probabilità che l'altezza sia compresa tra 1,75 e 1,80 è uguale approssimativamente a 0,544 - 0,303 = 0,241 ossia al 24,1%.
La probabilità dell'altezza tra 1,80 e 1,85 metri
La probabilità di avere un'altezza tra 1,80 e 1,85 metri è uguale alla differenza tra la distribuzione cumulata fino a 1,85 e quella fino a 1,80.
Digita la funzione =DISTRIB.NORM.N(1,85;$B$13;$B$14;1)-DISTRIB.NORM.N(1,80;$B$13;$B$14;1) nella cella E8
La probabilità che uno studente abbia un'altezza tra 1,80 e 1,85 metri è 22,51%
Spiegazione. La probabilità fino a 1,85 metri è 0,769 (ossia 76,9%) quella fino a 1,80 metri è 0,544(ossia 54,4%). Pertanto, la probabilità che l'altezza sia compresa tra 1,80 e 1,85 è uguale approssimativamente a 0,769 - 0,544 = 0,225 ossia al 22,5%.
La probabilità dell'altezza tra 1,85 e 1,90 metri
La probabilità di avere un'altezza tra 1,85 e 1,90 metri è uguale alla differenza tra la distribuzione cumulata fino a 1,90 e quella fino a 1,85.
Digita la funzione =DISTRIB.NORM.N(1,90;$B$13;$B$14;1)-DISTRIB.NORM.N(1,85;$B$13;$B$14;1) nella cella E9
La probabilità che uno studente abbia un'altezza tra 1,85 e 1,90 metri è 14,4%
Spiegazione. La probabilità fino a 1,90 metri è 0,913 (ossia 91,3%) quella fino a 1,85 metri è 0,769 (ossia 76,9%). Pertanto, la probabilità che l'altezza sia compresa tra 1,85 e 1,90 è uguale approssimativamente a 0,913 - 0,769 = 0,144 ossia al 14,4%.
La probabilità dell'altezza tra 1,90 e 1,95 metri
La probabilità di avere un'altezza tra 1,90 e 1,95 metri è uguale alla differenza tra la distribuzione cumulata fino a 1,95 e quella fino a 1,90.
Digita la funzione =DISTRIB.NORM.N(1,95;$B$13;$B$14;1)-DISTRIB.NORM.N(1,90;$B$13;$B$14;1) nella cella E10
La probabilità che uno studente abbia un'altezza tra 1,90 e 1,95 metri è 6,31%
Spiegazione. La probabilità fino a 1,95 metri è 0,976 (ossia 97,6%) quella fino a 1,90 metri è 0,913 (ossia 91,3%). Pertanto, la probabilità che l'altezza sia compresa tra 1,90 e 1,95 è uguale approssimativamente a 0,976 - 0,913 = 0,063 ossia al 6,3%.
La probabilità dell'altezza oltre 1,95 metri
La probabilità di avere un'altezza oltre 1,95 metri è uguale alla differenza tra 1 e la somma delle probabilità cumulate fino 1,95
Digita la funzione =1-DISTRIB.NORM.N(1,95;$B$13;$B$14;1) nella cella E11
La probabilità che uno studente abbia un'altezza oltre 1,95 metri è 2,34%
In questo modo, conoscendo la media e la deviazione standard di una popolazione hai calcolato le varie probabilità secondo la distribuzione normale.
Nella distribuzione normale le classi vicino al valore medio hanno probabilità maggiori.
E' un semplice esempio che aiuta a capire come si usa la distribuzione normale su Excel.
