
La deviazione standard su Excel
In questa lezione ti spiego come calcolare la deviazione standard sul foglio Excel
Cos'è la deviazione standard? E' un indice di dispersione statistico di una popolazione intorno alla media aritmetica. La deviazione standard è anche conosciuta come scarto quadratico medio ed è uguale alla radice quadrata della varianza. $$ \sigma = \sqrt{ \frac{ \sum_{i=1}^n \ (x_i - \mu )^2 }{ n } } $$
Per calcolare la deviazione standard su Excel utilizza la funzione
=DEV.ST.P(x1;x2;...)
I parametri x1,x2,... sono i valori della popolazione o un intervallo di celle sul foglio di calcolo, separati tra loro dal punto e virgola.
La funzione calcola la deviazione standard della popolazione rispetto alla media aritmetica.
Nota. La funzione DEV.ST.P() esclude dal computo le celle con valori logici vero/falso o con testo convertibile in valori. Per includere anche i valori logici e il testo convertibile nel calcolo utilizza la funzione =DEV.ST.POP.VALORI().
Ti faccio un esempio pratico
Digita la funzione =DEV.ST.P(2;4;6) nella cella B2
La funzione calcola la deviazione standard dei valori 2, 4, 6 rispetto alla loro media
La media aritmetica dei valori (2+4+6)/3 è uguale a μ=4
$$ \sigma = \sqrt{ \frac{ (2-μ)^2 + (4-μ)^2 + (6-μ)^2 }{ 3 } } $$
$$ \sigma = \sqrt{ \frac{ (2-4)^2 + (4-4)^2 + (6-4)^2 }{ 3 } } $$
$$ \sigma = \sqrt{ \frac{(-2)^2+0^2+2^2}{3} } $$
$$ \sigma = \sqrt{ \frac{8}{3} } $$
$$ \sigma = \sqrt{ 2,66 } $$
$$ \sigma = 1,632993162 $$
La deviazione standard è uguale a 1,632993162
Nota. La funzione =DEV.ST.P() ammette fino a 255 parametri. Quindi, se inserisci dei valori numerici all'interno della funzione puoi inserirne al massimo 255. Per superare questo limite ti consiglio di digitare i valori sul foglio Excel e indicare l'intervallo come parametro della funzione. E' la via migliore per calcolare la deviazione standard. Ti permette di modificare i dati più facilmente e il risultato si aggiorna automaticamente.
Ti faccio un altro esempio.
Inserisci alcuni valori nell'intervallo B2:B6 del foglio Excel.
Digita la funzione =DEV.ST.P(B2:B6) nella cella B8
La funzione calcola la deviazione standard dei valori nell'intervallo B2:B6 intorno alla loro media.
La media dei valori (1+3+1+3+2)/5 è uguale a μ=2
$$ \sigma = \sqrt{ \frac{ (1-μ)^2 + (3-μ)^2 + (1-μ)^2 + (3-μ)^2 + (2-μ)^2 }{ 5 } } $$
$$ \sigma = \sqrt{ \frac{ (1-2)^2 + (3-2)^2 + (1-2)^2 + (3-2)^2 + (2-2)^2 }{ 5 } } $$
$$ \sigma = \sqrt{ \frac{ (-1)^2 + 1^2 + (-1)^2 + 1^2 + 0^2 }{ 5 } } $$
$$ \sigma = \sqrt{ \frac{ 1 + 1 + 1 + 1 + 0 }{ 5 } } $$
$$ \sigma = \sqrt{ \frac{ 4 }{ 5 } } $$
$$ \sigma = \sqrt{ 0,8 } $$
$$ \sigma = 0,894427191 $$
La deviazione standard è uguale a 0,894427191
Nota. La funzione DEV.ST.P() esclude dal calcolo dello scarto quadratico medio le celle dell'intervallo che contengono dei valori logici o del testo convertibile.
In alternativa, puoi calcolare la deviazione standard anche usando la funzione
=DEV.ST.POP.VALORI()
Questa funzione calcola la deviazione standard includendo nel computo anche le celle con valori logici o testo convertibile.
Ad esempio, digita la funzione =FALSO() nella cella B4
La deviazione standard ottenuta precedentemente con la funzione =DEV.ST.P(B2:B6) nella cella B8 si aggiorna automaticamente perché i valori della popolazione sono diventati solo quattro.
Il valore logico FALSO() nella cella B4 è del tutto ignorato da questa funzione.
La deviazione standard aggiornata è uguale a 0,829156198
Spiegazione. Nel calcolo della deviazione standard su quattro valori 1, 3, 3, 2 devi considerare n=4 e una media (1+3+3+2)/4 uguale a μ=2,25. $$ \sigma = \sqrt{ \frac{ (1-μ)^2 + (3-μ)^2 + (3-μ)^2 + (2-μ)^2 }{ 4 } } $$ $$ \sigma = \sqrt{ \frac{ (1-2,25)^2 + (3-2,25)^2 + (3-2,25)^2 + (2-2,25)^2 }{ 4 } } $$ $$ \sigma = \sqrt{ \frac{ (-1,25)^2 + (0,75)^2 + (0,75)^2 + (-0,25)^2 }{ 4 } } $$ $$ \sigma = \sqrt{ \frac{ 1,5625 + 1,5625 + 1,5625 + 0,0625 }{ 4 } } $$ $$ \sigma = \sqrt{ 0,6875 } $$ $$ \sigma =0,829156198 $$
Digita la funzione=DEV.ST.POP.VALORI(B2:B6) nella cella C8
La funzione DEV.ST.POP.VALORI() considera il valore logico FALSO() come uno zero e lo include nel calcolo.
In questo caso la deviazione standard viene calcolata su cinque valori 1,3,0,3,2 la cui media (1+3+0+3+2)/5 è uguale a μ=1,8
$$ \sigma = \sqrt{ \frac{ (1-μ)^2 + (3-μ)^2 + (0-μ)^2 + (3-μ)^2 + (2-μ)^2 }{ 5 } } $$
$$ \sigma = \sqrt{ \frac{ (1-1,8)^2 + (3-1,8)^2 + (0-1,8)^2 + (3-1,8)^2 + (2-1,8)^2 }{ 5 } } $$
$$ \sigma = \sqrt{ \frac{ (-0,8)^2 + (1,2)^2 + (-1,8)^2 + (1,2)^2 + (0,2)^2 }{ 5 } } $$
$$ \sigma = \sqrt{ \frac{ 0,64 + 1,44 + 3,24 + 1,44 + 0,04 }{ 5 } } $$
$$ \sigma = \sqrt{ \frac{ 6,8 }{ 5 } } $$
$$ \sigma = \sqrt{ 1,36 }$$
$$ \sigma = 1,166190379 $$
La deviazione standard è uguale a 1,166190379
Nota. La funzione DEV.ST.POP.VALORI() considera il valore logico FALSO() come zero e il valore logico VERO() come un numero intero uguale a uno. Viceversa, la funzione DEV.ST.P() li ignora e non li include nel calcolo.