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 curva normale gaussiana

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.

un esempio di distribuzione di valori

Inserisci la funzione =MEDIA(B3:B9) nella cella B11 per calcolare la media della distribuzione.

digita =MEDIA(B3:B9)

La media aritmetica dei valori è 24

la media dei valori è 24

Inserisci la funzione =DEV.ST.P(B3:B9) nella cella B12 per calcolare la deviazione standard.

digita  =DEV.ST.P(B3:B9)

La deviazione standard dei valori è 4

la deviazione standard della distribuzione è uguale a 4

Ora digita la funzione =DISTRIB.NORM.N(B3;$B$11;$B$12;0) nella cella C3

digita =DISTRIB.NORM.N(B3;$B$11;$B$12;0)

La funzione calcola il valore 18 della cella B3 nella distribuzione normale.

Il risultato è 0,032379399

il valore di B3 nella distribuzione normale

Infine copia la cella C3 nell'intervallo C4:C9

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.

la distribuzione normale dei dati

Se rappresenti graficamente i dati nell'intervallo C3:C9 viene visualizzato il classico grafico a campana della curva normale (curva gaussiana).

il grafico della distribuzione normale

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

digita =DISTRIB.NORM.N(B3;$B$11;$B$12;1)

Poi copia la cella C3 nell'intervallo C4:C9

In questo caso il risultato è il cumulativo da 0 a 1

il risultato è il cumulativo

Il grafico della curva cumulata è crescente.

la curva cumulata

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.

la distribuzione precedente

Digita =NORMALIZZA(B3;$B$11;$B$12) nella cella E3 per standardizzare i dati della distribuzione

digita =NORMALIZZA(B3;$B$11;$B12)

La funzione calcola il valore normale standard (-1,5) del valore 18 contenuto nella cella B3

il valore normale standard

Poi copia la cella E3 nell'intervallo E4:E9

I valori nella colonna E sono la distribuzione normale standardizzata

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.
la media è uguale a zero e la deviazione standard è uguale a 1

Ora digita =DISTRIB.NORM.ST.N(E3;0) nella cella F3

digita =DISTRIB.NORM.ST.N(E3;0)

La funzione calcola il relativo valore nella distribuzione normale

il valore 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.

i dati nella distribuzione normale

Se rappresenti graficamente i dati nell'intervallo F3:F9 ottieni il grafico a campana della curva normale (curva gaussiana).

il grafico della distribuzione normale

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.

un esempio 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

digita =MEDIA(B3:B11)

Poi calcola la deviazione standard tramite la funzione =DEV.ST.P(B3:B11) nella cella B14

digita =DEV.ST(B3:B11)

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.

le classi delle altezze

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%

la probabilità della classe da 0 a 1,6

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 curva della distribuzione normale cumulata

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%

la probabilità tra 1,60 e 1,65 metri

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 spiegazione del calcolo

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%

la probabilità tra 1,65 e 1,70

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 spiegazione del calcolo

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%

la probabilità tra 1,70 e 1,75

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 spiegazione del calcolo

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%

la probabilità tra 1,75 e 1,80

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 spiegazione del calcolo

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%

la probabilità tra 1,80 e 1,85

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 spiegazione del calcolo

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%

la probabilità tra 1,85 e 1,90

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 spiegazione del calcolo

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%

la probabilità tra 1,90 e 1,95

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 spiegazione del calcolo

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%

la probabilità oltre 1,95

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.

la distribuzione normale delle probabilità

E' un semplice esempio che aiuta a capire come si usa la distribuzione normale su Excel.




Non hai risolto il tuo problema? Scrivi una domanda




FacebookTwitterLinkedinLinkedin