Deviazione standard - Excel e Fogli Google

Questo tutorial mostra come usare il Funzione di deviazione standard di Excel in Excel per calcolare la deviazione standard per un'intera popolazione.

DEVIAZIONE STANDARD Panoramica delle funzioni

La funzione DEVIAZIONE STANDARD Calcola calcola la deviazione standard per un'intera popolazione.

Per utilizzare la funzione del foglio di lavoro Excel DEVIAZIONE STANDARD, selezionare una cella e digitare:

(Notare come appaiono gli input della formula)

Funzione DEVIAZIONE STANDARD Sintassi e ingressi:

1	=DEV.ST(numero1,[numero2],… )

numeri- Valori per ottenere la varianza standard

Come calcolare la deviazione standard in Excel

Ogni volta che hai a che fare con i dati, ti consigliamo di eseguire alcuni test di base per aiutarti a capirli. In genere inizierai calcolando la media, utilizzando la funzione MEDIA di Excel<>.

Questo ti dà un'idea di dove si trova il "mezzo" dei dati. E da lì, vorrai vedere come sono distribuiti i dati intorno a questo punto medio. È qui che entra in gioco la deviazione standard.

Excel offre una serie di funzioni per calcolare la deviazione standard: DEV.ST, DEV.ST.P, DEV.ST.S e DEV.ST.D. Arriveremo a tutti loro, ma prima, impariamo qual è la deviazione standard è, Esattamente.

Che cos'è la deviazione standard?

La deviazione standard ti dà un'idea di quanto sono lontani i tuoi punti dati dalla media. Prendi il seguente set di dati dei punteggi dei test su 100:

1	48,49,50,51,52

La media di questo set di dati è 50 (somma tutti i numeri e dividi per n, dove n è il numero di valori nell'intervallo).

Ora guarda questo prossimo set di dati:

1	10,25,50,75,90

La media di questo set di dati è anche 50 - ma le due gamme raccontano una storia molto diversa. Se hai appena usato la media, potresti pensare che i due gruppi siano più o meno uguali nella loro attitudine - e in media lo sono.

Ma nel primo gruppo abbiamo 5 persone che hanno ottenuto punteggi molto simili e molto mediocri. E nel secondo gruppo, noi un paio di high flyer bilanciati da un paio di scarsi punteggi, con una persona nel mezzo. Il propagazione dei punteggi è molto diverso, rendendo anche la tua interpretazione dei dati molto diversa.

La deviazione standard è una misura di questo spread.

Come viene calcolata la deviazione standard

Per capire cos'è la deviazione standard e come funziona, può essere utile elaborare un esempio a mano. In questo modo, saprai cosa sta succedendo "sotto il cofano" una volta arrivati ​​alle funzioni di Excel che puoi utilizzare.

Per calcolare la deviazione standard, segui questo processo:

1) Calcola la media

Prendiamo il nostro primo set di dati sopra: 48,49,50,51,52

Conosciamo già la media (50), che ho confermato qui con la funzione MEDIA di Excel<>:

1	=MEDIA(C4:C8)

2) Sottrarre la media da ciascun valore nel set di dati

L'ho fatto con la seguente formula:

1

=C4-$H$4

La nostra media è in H4 e ho "bloccato" il riferimento di cella inserendo i simboli del dollaro prima della colonna e della riga (premendo F4). Ciò significa che posso copiare la formula lungo la colonna senza l'aggiornamento del riferimento di cella.

Il risultato:

Ora, fermiamoci qui per un secondo. Se dai un'occhiata alla nuova colonna, vedrai che i numeri qui si sommano a zero. Anche la media di questi numeri è zero.

Naturalmente, la diffusione dei nostri dati non può essere zero: sappiamo che c'è qualche variazione. Abbiamo bisogno di un modo per rappresentare questa variazione, senza che la media risulti zero.

3) Piazza le differenze

Possiamo raggiungere questo obiettivo eguagliando le differenze. Quindi, aggiungiamo una nuova colonna e quadra i numeri nella colonna D:

1

=D4*D4

Questo sembra migliore. Ora abbiamo qualche variazione e la quantità di variazione è correlata a quanto è lontano ogni punteggio dalla media.

4) Calcola la varianza - la media delle differenze al quadrato

Il passo successivo è ottenere la media di queste differenze al quadrato. Ci sono in realtà due modi per farlo quando si calcola la deviazione standard.

• Se stai usando dati sulla popolazione, prendi semplicemente la media (somma i valori e dividi per n)
• Se stai usando dati di esempio, prendi la somma dei valori e dividi per n-1

I dati sulla popolazione significano che hai il "set completo" dei tuoi dati, ad esempio, hai dati su ogni studente in una determinata classe.

Dati di esempio significa che non hai tutti i tuoi dati, solo un campione prelevato da una popolazione più ampia. In genere, il tuo obiettivo con i dati di esempio è fare una stima di quale sia il valore nella popolazione più ampia.

Un sondaggio di opinione politica è un buon esempio di dati campione: i ricercatori intervistano, diciamo, 1.000 persone per avere un'idea di cosa sta pensando un intero paese o stato.

Qui non abbiamo un campione. Abbiamo solo cinque membri della famiglia con una mentalità statistica che vogliono calcolare la deviazione standard di un test che hanno fatto tutti. Abbiamo tutti i dati e non stiamo facendo una stima di un gruppo più ampio di persone. Questi sono dati sulla popolazione, quindi possiamo solo prendere la media qui:

1	=MEDIA(E4:E8)

OK, quindi abbiamo 2. Questo punteggio è noto come "varianza" ed è il punto base per molti test statistici, inclusa la deviazione standard. Puoi leggere di più sulla varianza nella sua pagina principale: come calcolare la varianza in Excel<>.

5) Ottieni la radice quadrata della varianza

Abbiamo al quadrato i nostri numeri in precedenza, il che ovviamente gonfia un po' i valori. Quindi, per riportare la cifra in linea con le differenze effettive dei punteggi dalla media, dobbiamo fare la radice quadrata del risultato del passaggio 4:

1

=RADQ(H4)

E abbiamo il nostro risultato: la deviazione standard è 1.414

Poiché abbiamo radicato al quadrato i nostri numeri precedentemente quadrati, la deviazione standard è data nelle stesse unità dei dati originali. Quindi la deviazione standard qui è 1.414 punti di prova.

Deviazione standard quando i dati sono più distribuiti

In precedenza avevamo un secondo intervallo di dati di esempio: 10,25,50,75,90

Giusto per divertimento, vediamo cosa succede quando calcoliamo la deviazione standard su questi dati:

Tutte le formule sono esattamente le stesse di prima (si noti che la media complessiva è ancora 50).

L'unica cosa che è cambiata è stata la diffusione dei punteggi nella colonna C. Ma ora, la nostra deviazione standard è molto più alta, a 29,832 punti di test.

Ovviamente, poiché abbiamo solo 5 punti dati, è molto facile vedere che la diffusione dei punteggi è diversa tra i due set. Ma quando hai 100 o 1.000 punti dati, non puoi dirlo semplicemente scansionando rapidamente i dati. Ed è proprio per questo che usiamo la deviazione standard.

Le funzioni di Excel per calcolare la deviazione standard

Ora che sai come funziona la deviazione standard, non è necessario seguire l'intero processo per arrivare alla deviazione standard. Puoi semplicemente utilizzare una delle funzioni integrate di Excel.

Excel ha diverse funzioni per questo scopo:

• P calcola la deviazione standard per i dati sulla popolazione (usando il metodo esatto che abbiamo usato nell'esempio sopra)
• S calcola la deviazione standard per i dati del campione (usando il metodo n-1 che abbiamo toccato in precedenza)
• STDEV è esattamente uguale a STDEV.S. Questa è una funzione precedente che è stata sostituita da DEV.ST.S e DEV.ST.P.
• STDEVA è molto simile a STDEV.S, tranne per il fatto che include celle di testo e celle booleane (VERO/FALSO) durante il calcolo.
• STDEVPA è molto simile a DEV.ST.P, tranne per il fatto che include celle di testo e celle booleane (VERO/FALSO) durante il calcolo.

Wow, molte opzioni qui! Non lasciarti intimidire: nella stragrande maggioranza dei casi, utilizzerai STDEV.P o STDEV.S.

Esaminiamo ciascuno di questi a turno, iniziando con STDEV.P, poiché questo è il metodo su cui abbiamo appena lavorato.

La funzione DEV.ST.P di Excel

DEV.ST.P calcola la deviazione standard per i dati sulla popolazione. Lo usi così:

1	=DEV.ST.P(C4:C8)

Definisci un argomento in DEV.ST.P: l'intervallo di dati per il quale desideri calcolare la deviazione standard.

Questo è lo stesso esempio che abbiamo seguito passo dopo passo quando abbiamo calcolato la deviazione standard a mano. E come puoi vedere sopra, otteniamo esattamente lo stesso risultato - 1.414.

Nota DEV.ST.P ignora qualsiasi cella contenente testo o valori booleani (VERO/FALSO). Se è necessario includerli, utilizzare STDEVPA.

La funzione DEV.ST.ST di Excel

STDEV.S calcola la deviazione standard per i dati di esempio. Usalo in questo modo:

1	=DEV.ST.S(C4:C8)

Ancora una volta, richiede un argomento: l'intervallo di dati per cui si desidera conoscere la deviazione standard.

Prima di entrare in un esempio, discutiamo la differenza tra STDEV.S e STDEV.P.

Come abbiamo già discusso, DEV.ST.S dovrebbe essere utilizzato sui dati di esempio, quando i dati sono una parte di un insieme più ampio. Quindi supponiamo ora che nel nostro esempio sopra, più persone abbiano fatto il test. Vogliamo stimare la deviazione standard di tutti coloro che hanno sostenuto il test, utilizzando solo questi cinque punteggi. Ora stiamo usando dati di esempio.

Ora, il calcolo differisce dal passaggio (4) sopra, quando calcoliamo la varianza, la media della differenza al quadrato di ciascun punteggio dalla media complessiva.

Invece di usare il metodo normale - sommare tutti i valori e dividere per n, sommare tutti i valori e dividere per n-1:

1	=SOMMA(E4:E8) / (CONTEGGIO(E4:E8)-1)

In questa formula:

• SUM ottiene la somma delle differenze al quadrato
• COUNT restituisce il nostro n, da cui sottraiamo 1
• Quindi dividiamo semplicemente la nostra somma per il nostro n-1

Questa volta, la media delle differenze al quadrato è 2,5 (potresti ricordare che prima era 2, quindi è un po' più alta).

Allora perché dividiamo per n-1 invece di n quando abbiamo a che fare con dati di esempio?

La risposta è abbastanza complessa e se stai solo cercando di eseguire i tuoi numeri per comprendere i tuoi dati, non è qualcosa di cui devi davvero preoccuparti. Assicurati solo di utilizzare DEV.ST.S per i dati di esempio e DEV.ST.P per i dati della popolazione e andrà tutto bene.

Se sei davvero curioso di sapere perché, consulta la pagina principale su come calcolare la varianza in Excel<>.

OK, ora abbiamo la varianza per il campione, quindi per ottenere la deviazione standard per il campione, otterremmo solo la radice quadrata della varianza:

1

=RADQ(H4)

Otteniamo 1.581.

DEV.ST.S esegue tutti i calcoli precedenti per noi e restituisce la deviazione standard del campione in una sola cella. Allora vediamo cosa ne viene fuori…

1	=DEV.ST.S(C4:C8)

Sì, ancora 1.581.

La funzione DEV.ST di Excel

La funzione DEV.ST di Excel funziona esattamente allo stesso modo di DEV.ST.S, ovvero calcola la deviazione standard per un campione di dati.

Lo usi allo stesso modo:

1	=DEV.ST(C4:C8)

Ancora una volta otteniamo lo stesso risultato.

Nota importante: STDEV è una "funzione di compatibilità", che sostanzialmente significa che Microsoft se ne sta sbarazzando. Per ora funziona ancora, quindi tutti i fogli di calcolo precedenti continueranno a funzionare normalmente. Ma nelle versioni future di Excel, Microsoft potrebbe eliminarlo completamente, quindi dovresti usare STDEV.S invece di STDEV ove possibile.

La funzione DEV.ST.VALORI di Excel

DEV.ST.VALORI viene anche utilizzato per calcolare la deviazione standard per un campione, ma presenta un paio di importanti differenze che è necessario conoscere:

• I valori VERI vengono contati come 1
• I valori FALSE vengono conteggiati come 0
• Le stringhe di testo vengono conteggiate come 0

Usalo come segue:

1	=DEV.ST.VALORI(C4:C8)

Altri quattro amici e familiari hanno dato nei loro punteggi dei test. Questi sono mostrati nella colonna C e la colonna D indica come STDEVA interpreta questi dati.

Poiché queste celle vengono interpretate come valori così bassi, ciò crea una diffusione molto più ampia tra i nostri dati rispetto a quella che abbiamo visto prima, il che ha notevolmente aumentato la deviazione standard, ora a 26.246.

La funzione DEV.ST.POP di Excel

DEV.ST.POP calcola la deviazione standard per una popolazione allo stesso modo di DEV.ST.P. Tuttavia, include anche valori booleani e stringhe di testo nel calcolo, che vengono interpretati come segue:

• I valori VERI vengono contati come 1
• I valori FALSE vengono conteggiati come 0
• Le stringhe di testo vengono conteggiate come 0

Lo usi così:

1	=DEV.ST.POP(C4:C12)

Filtraggio dei dati prima di calcolare la deviazione standard

Nel mondo reale, non avrai sempre i dati esatti di cui hai bisogno in una bella tabella ordinata. Spesso avrai un grande foglio di calcolo pieno di dati, che dovrai filtrare prima di calcolare la deviazione standard.

Puoi farlo molto facilmente con le funzioni del database di Excel: DSTDEV (per i campioni) e DSTDEVP (per le popolazioni).

Queste funzioni ti consentono di creare una tabella di criteri, nella quale puoi definire tutti i filtri di cui hai bisogno. Le funzioni applicano questi filtri dietro le quinte prima di restituire la deviazione standard. In questo modo non è necessario toccare un filtro automatico o estrarre i dati in un foglio separato: DSTDEV e SDTDEVP possono fare tutto questo per te.

Ulteriori informazioni nella pagina principale per le funzioni DSTDEV e DSTDEVP di Excel<>.

Funzione DEVIAZIONE STANDARD in Fogli Google

La funzione DEVIAZIONE STANDARD funziona esattamente allo stesso modo in Fogli Google come in Excel: