Come funziona la deviazione standard in Excel. Calcolo della dispersione, deviazione quadratica media (standard), coefficiente di variazione in Excel

CalcoliamoSMECCELLEREvarianza campionaria e deviazione standard. Calcoleremo anche la varianza di una variabile casuale se ne conosciamo la distribuzione.

Consideriamo prima dispersione, Poi deviazione standard.

Varianza di campionamento

Varianza di campionamento (varianza di campionamento,campionevarianza) caratterizza la diffusione dei valori nell'array rispetto a .

Tutte e 3 le formule sono matematicamente equivalenti.

Dalla prima formula è chiaro che varianza di campionamentoè la somma delle deviazioni al quadrato di ciascun valore nella matrice dalla media, diviso per la dimensione del campione meno 1.

varianze campioni viene utilizzata la funzione DISP(), inglese. il nome VAR, cioè Varianza. A partire dalla versione MS EXCEL 2010, si consiglia di utilizzare l'analogo DISP.V(), inglese. il nome VARS, cioè Varianza di campionamento. Inoltre, a partire dalla versione di MS EXCEL 2010, è disponibile la funzione DISP.Г(), in inglese. nome VARP, cioè Varianza della popolazione, che calcola dispersione Per popolazione. Tutta la differenza si riduce al denominatore: invece di n-1 come DISP.V(), DISP.G() ha solo n al denominatore. Prima di MS EXCEL 2010, la funzione VAR() veniva utilizzata per calcolare la varianza della popolazione.

Varianza di campionamento
=QUADROTCL(Campione)/(COUNT(Campione)-1)
=(SOMMA(Campione)-COUNT(Campione)*MEDIA(Campione)^2)/ (COUNT(Campione)-1)– formula abituale
=SOMMA((Campione -MEDIA(Campione))^2)/ (COUNT(Campione)-1) –

Varianza di campionamentoè uguale a 0, solo se tutti i valori sono uguali tra loro e, di conseguenza, uguali valore medio. Di solito, maggiore è il valore varianze, maggiore è la diffusione dei valori nell'array.

Varianza di campionamentoè una stima puntuale varianze distribuzione della variabile casuale da cui è stata ricavata campione. A proposito di costruzione intervalli di confidenza durante la valutazione varianze si può leggere nell'articolo.

Varianza di una variabile casuale

Calcolare dispersione variabile casuale, devi conoscerla.

Per varianze la variabile casuale X è spesso indicata con Var(X). Dispersione uguale al quadrato dello scostamento dalla media E(X): Var(X)=E[(X-E(X)) 2 ]

dispersione calcolato con la formula:

dove x i è il valore che può assumere una variabile casuale e μ è il valore medio (), p(x) è la probabilità che la variabile casuale assuma il valore x.

Se una variabile casuale ha , allora dispersione calcolato con la formula:

Dimensione varianze corrisponde al quadrato dell'unità di misura dei valori originali. Ad esempio, se i valori nel campione rappresentano le misurazioni del peso della parte (in kg), la dimensione della varianza sarebbe kg 2 . Questo può essere difficile da interpretare, quindi per caratterizzare la diffusione dei valori, un valore uguale alla radice quadrata di varianze – deviazione standard.

Alcune proprietà varianze:

Var(X+a)=Var(X), dove X è una variabile casuale e a è una costante.

Var(a×)=a 2 Var(X)

Var(X)=E[(X-E(X)) 2 ]=E=E(X 2)-E(2*X*E(X))+(E(X)) 2 =E(X 2)- 2*E(X)*E(X)+(E(X)) 2 =E(X 2)-(E(X)) 2

Questa proprietà di dispersione viene utilizzata in articolo sulla regressione lineare.

Var(X+Y)=Var(X) + Var(Y) + 2*Cov(X;Y), dove X e Y sono variabili casuali, Cov(X;Y) è la covarianza di queste variabili casuali.

Se le variabili casuali sono indipendenti, allora lo sono covarianzaè uguale a 0, e quindi Var(X+Y)=Var(X)+Var(Y). Questa proprietà di dispersione viene utilizzata nella derivazione.

Mostriamo che per quantità indipendenti Var(X-Y)=Var(X+Y). Infatti, Var(X-Y)= Var(X-Y)= Var(X+(-Y))= Var(X)+Var(-Y)= Var(X)+Var(-Y)= Var( X)+(- 1) 2 Var(Y)= Var(X)+Var(Y)= Var(X+Y). Questa proprietà di dispersione viene utilizzata per costruire .

Deviazione standard del campione

Deviazione standard del campioneè una misura di quanto sono sparsi i valori in un campione rispetto ai loro valori.

A priori, deviazione standard uguale alla radice quadrata di varianze:

Deviazione standard non tiene conto dell'entità dei valori in campione, ma solo il grado di dispersione dei valori attorno a loro media. Per illustrare ciò, diamo un esempio.

Calcoliamo la deviazione standard per 2 campioni: (1; 5; 9) e (1001; 1005; 1009). In entrambi i casi, s=4. È ovvio che il rapporto tra la deviazione standard e i valori dell'array differisce significativamente tra i campioni. Per questi casi viene utilizzato Il coefficiente di variazione(Coefficiente di variazione, CV) - rapporto Deviazione standard alla media aritmetica, espresso in percentuale.

In MS EXCEL 2007 e versioni precedenti per il calcolo Deviazione standard del campione viene utilizzata la funzione =STDEVAL(), inglese. nome DEV.ST, ad es. Deviazione standard. A partire dalla versione di MS EXCEL 2010 si consiglia di utilizzare l'analogo =STDEV.B() , inglese. nome DEV.ST.S, ovvero Esempio di deviazione standard.

Inoltre, a partire dalla versione di MS EXCEL 2010, è presente la funzione STANDARDEV.G(), inglese. nome DEV.ST.P, cioè Popolazione STandard DEViation, che calcola deviazione standard Per popolazione. Tutta la differenza si riduce al denominatore: invece di n-1 come in STANDARDEV.V(), STANDARDEVAL.G() ha solo n al denominatore.

Deviazione standard può anche essere calcolato direttamente utilizzando le formule seguenti (vedi file di esempio)
=ROOT(QUADROTCL(Campione)/(COUNT(Campione)-1))
=ROOT((SUM(Campione)-COUNT(Campione)*MEDIA(Campione)^2)/(COUNT(Campione)-1))

Altre misure di dispersione

La funzione SQUADROTCL() calcola con una somma dei quadrati delle deviazioni dei valori dai loro media. Questa funzione restituirà lo stesso risultato della formula =DISP.G( Campione)*CONTROLLO( Campione) , Dove Campione- un riferimento a un intervallo contenente un array di valori campione (). I calcoli nella funzione QUADROCL() vengono eseguiti secondo la formula:

La funzione SROTCL() è anche una misura della diffusione di un set di dati. La funzione SROTCL() calcola la media dei valori assoluti delle deviazioni dei valori da media. Questa funzione restituirà lo stesso risultato della formula =SUMPRODOTTO(ABS(Campione-MEDIA(Campione)))/COUNT(Campione), Dove Campione- un collegamento a un intervallo contenente una matrice di valori campione.

I calcoli nella funzione SROTCL () vengono effettuati secondo la formula:

Istruzioni

Lasciamo che ci siano più numeri che caratterizzano quantità omogenee. Ad esempio, i risultati di misurazioni, pesate, osservazioni statistiche, ecc. Tutte le quantità presentate devono essere misurate utilizzando la stessa misurazione. Per trovare la deviazione standard, procedi come segue:

Determina la media aritmetica di tutti i numeri: aggiungi tutti i numeri e dividi la somma per il numero totale di numeri.

Determina la dispersione (scattering) dei numeri: aggiungi i quadrati delle deviazioni precedentemente trovate e dividi la somma risultante per il numero di numeri.

Nel reparto ci sono sette pazienti con temperature di 34, 35, 36, 37, 38, 39 e 40 gradi Celsius.

È necessario determinare la deviazione media dalla media.
Soluzione:
“nel reparto”: (34+35+36+37+38+39+40)/7=37 ºС;

Deviazioni della temperatura dalla media (in questo caso il valore normale): 34-37, 35-37, 36-37, 37-37, 38-37, 39-37, 40-37, risultanti in: -3, - 2, -1 , 0, 1, 2, 3 (ºС);

Dividi la somma dei numeri ottenuti in precedenza per il loro numero. Per calcoli accurati, è meglio usare una calcolatrice. Il risultato della divisione è la media aritmetica dei numeri sommati.

Prestare attenzione a tutte le fasi del calcolo, poiché un errore anche in uno dei calcoli porterà a un indicatore finale errato. Controlla i tuoi calcoli in ogni fase. La media aritmetica ha lo stesso metro dei numeri sommati, cioè se determini la presenza media, tutti i tuoi indicatori saranno “persona”.

Questo metodo di calcolo viene utilizzato solo nei calcoli matematici e statistici. Ad esempio, la media aritmetica in informatica ha un algoritmo di calcolo diverso. La media aritmetica è un indicatore molto relativo. Mostra la probabilità di un evento, a condizione che abbia un solo fattore o indicatore. Per un’analisi più approfondita è necessario tenere conto di molti fattori. A questo scopo viene utilizzato il calcolo di quantità più generali.

La media aritmetica è una delle misure di tendenza centrale, ampiamente utilizzata in matematica e nei calcoli statistici. Trovare la media aritmetica per diversi valori è molto semplice, ma ogni attività ha le sue sfumature, che è semplicemente necessario conoscere per eseguire calcoli corretti.

Risultati quantitativi di esperimenti simili.

Come trovare la media aritmetica

Per trovare la media aritmetica di una matrice di numeri è necessario iniziare determinando la somma algebrica di questi valori. Ad esempio, se l'array contiene i numeri 23, 43, 10, 74 e 34, la loro somma algebrica sarà uguale a 184. Durante la scrittura, la media aritmetica è indicata con la lettera μ (mu) o x (x con a sbarra). Successivamente, la somma algebrica dovrebbe essere divisa per il numero di numeri nella matrice. Nell'esempio in esame i numeri erano cinque, quindi la media aritmetica sarà pari a 184/5 e sarà 36,8.

Caratteristiche di lavorare con numeri negativi

Se l'array contiene numeri negativi, la media aritmetica viene calcolata utilizzando un algoritmo simile. La differenza esiste solo quando si calcola nell'ambiente di programmazione o se il problema presenta condizioni aggiuntive. In questi casi, trovare la media aritmetica di numeri con segni diversi si riduce a tre passaggi:

1. Trovare la media aritmetica generale utilizzando il metodo standard;
2. Trovare la media aritmetica dei numeri negativi.
3. Calcolo della media aritmetica dei numeri positivi.

Le risposte per ciascuna azione sono scritte separate da virgole.

Frazioni naturali e decimali

Se una matrice di numeri è rappresentata da frazioni decimali, la soluzione viene eseguita utilizzando il metodo di calcolo della media aritmetica dei numeri interi, ma il risultato viene ridotto in base ai requisiti dell'attività per l'accuratezza della risposta.

Quando si lavora con le frazioni naturali, è necessario ridurle a un denominatore comune, che viene moltiplicato per il numero di numeri nell'array. Il numeratore della risposta sarà la somma dei numeratori indicati degli elementi frazionari originali.

In questo articolo parlerò di come trovare la deviazione standard. Questo materiale è estremamente importante per una piena comprensione della matematica, quindi un tutor di matematica dovrebbe dedicare una lezione separata o anche più al suo studio. In questo articolo troverai un collegamento a un video tutorial dettagliato e comprensibile che spiega cos'è la deviazione standard e come trovarla.

Deviazione standard consente di valutare la diffusione dei valori ottenuti a seguito della misurazione di un determinato parametro. Indicato dal simbolo (lettera greca "sigma").

La formula per il calcolo è abbastanza semplice. Per trovare la deviazione standard, devi prendere la radice quadrata della varianza. Quindi ora devi chiederti: “Cos’è la varianza?”

Cos'è la varianza

La definizione di varianza funziona così. La dispersione è la media aritmetica delle deviazioni al quadrato dei valori dalla media.

Per trovare la varianza, eseguire i seguenti calcoli in sequenza:

• Determinare la media (media aritmetica semplice di una serie di valori).
• Quindi sottrai la media da ciascun valore ed eleva al quadrato la differenza risultante (otterrai differenza quadrata).
• Il passaggio successivo è calcolare la media aritmetica delle differenze quadrate risultanti (puoi scoprire il motivo esattamente nei quadrati di seguito).

Diamo un'occhiata a un esempio. Diciamo che tu e i tuoi amici decidete di misurare l'altezza dei vostri cani (in millimetri). Come risultato delle misurazioni, hai ricevuto le seguenti misure di altezza (al garrese): 600 mm, 470 mm, 170 mm, 430 mm e 300 mm.

Calcoliamo la media, la varianza e la deviazione standard.

Per prima cosa troviamo il valore medio. Come già saprai, per fare ciò devi sommare tutti i valori misurati e dividerli per il numero di misurazioni. Avanzamento del calcolo:

Media mm.

Quindi, la media (media aritmetica) è 394 mm.

Ora dobbiamo determinare deviazione dell'altezza di ciascun cane dalla media:

Finalmente, per calcolare la varianza, eleviamo al quadrato ciascuna delle differenze risultanti, quindi troviamo la media aritmetica dei risultati ottenuti:

Dispersione mm2.

Pertanto, la dispersione è 21704 mm 2.

Come trovare la deviazione standard

Allora come possiamo ora calcolare la deviazione standard, conoscendo la varianza? Come ricordiamo, prendine la radice quadrata. Cioè, la deviazione standard è uguale a:

Mm (arrotondato al numero intero più vicino in mm).

Usando questo metodo, abbiamo scoperto che alcuni cani (ad esempio i Rottweiler) sono cani molto grandi. Ma ci sono anche cani molto piccoli (ad esempio i bassotti, ma non bisogna dirglielo).

La cosa più interessante è che la deviazione standard trasporta informazioni utili. Ora possiamo mostrare quali dei risultati ottenuti dalla misurazione dell'altezza rientrano nell'intervallo che otteniamo se tracciamo la deviazione standard dalla media (su entrambi i lati di essa).

Cioè, utilizzando la deviazione standard, otteniamo un metodo “standard” che ci consente di scoprire quale dei valori è normale (media statistica) e quale è straordinariamente grande o, al contrario, piccolo.

Cos'è la deviazione standard

Ma... tutto sarà un po' diverso se analizziamo campione dati. Nel nostro esempio abbiamo considerato popolazione generale. Cioè i nostri 5 cani erano gli unici cani al mondo che ci interessavano.

Ma se i dati sono un campione (valori selezionati da una vasta popolazione), i calcoli devono essere eseguiti diversamente.

Se ci sono valori, allora:

Tutti gli altri calcoli vengono eseguiti in modo simile, inclusa la determinazione della media.

Ad esempio, se i nostri cinque cani sono solo un campione della popolazione canina (tutti i cani del pianeta), dobbiamo dividere per 4, non 5, vale a dire:

Varianza campionaria = mm2.

In questo caso, la deviazione standard del campione è uguale a mm (arrotondato al numero intero più vicino).

Possiamo dire di aver apportato qualche “correzione” nel caso in cui i nostri valori siano solo un piccolo campione.

Nota. Perché differenze esattamente quadrate?

Ma perché prendiamo esattamente le differenze al quadrato quando calcoliamo la varianza? Diciamo che misurando alcuni parametri, hai ricevuto il seguente insieme di valori: 4; 4; -4; -4. Se semplicemente sommiamo le deviazioni assolute dalla media (differenze) insieme... i valori negativi si annullano con quelli positivi:

.

Si scopre che questa opzione è inutile. Allora forse vale la pena provare i valori assoluti delle deviazioni (cioè i moduli di questi valori)?

A prima vista, risulta positivo (il valore risultante, tra l'altro, è chiamato deviazione media assoluta), ma non in tutti i casi. Proviamo un altro esempio. Lascia che la misurazione dia come risultato il seguente insieme di valori: 7; 1; -6; -2. Quindi la deviazione media assoluta è:

Oh! Anche in questo caso abbiamo ottenuto un risultato di 4, anche se le differenze sono molto più ampie.

Ora vediamo cosa succede se eleviamo al quadrato le differenze (e poi prendiamo la radice quadrata della loro somma).

Per il primo esempio sarà:

.

Per il secondo esempio sarà:

Ora è una questione completamente diversa! Maggiore è la diffusione delle differenze, maggiore è la deviazione standard... che è ciò a cui miravamo.

In realtà, questo metodo utilizza la stessa idea del calcolo della distanza tra i punti, solo applicata in modo diverso.

E da un punto di vista matematico, l’uso dei quadrati e delle radici quadrate offre più vantaggi di quelli che potremmo ottenere dai valori di deviazione assoluta, rendendo la deviazione standard applicabile ad altri problemi matematici.

Sergey Valerievich ti ha spiegato come trovare la deviazione standard

La varianza è una misura di dispersione che descrive la deviazione comparativa tra i valori dei dati e la media. È la misura di dispersione più utilizzata nelle statistiche, calcolata sommando ed elevando al quadrato la deviazione di ciascun valore dei dati dalla media. La formula per il calcolo della varianza è riportata di seguito:

s 2 – varianza del campione;

x av: media campionaria;

N — dimensione del campione (numero di valori di dati),

(x i – x avg) è la deviazione dal valore medio per ciascun valore del set di dati.

Per comprendere meglio la formula, facciamo un esempio. Non mi piace molto cucinare, quindi lo faccio raramente. Tuttavia, per non morire di fame, di tanto in tanto devo mettermi ai fornelli per attuare il piano di saturare il mio corpo con proteine, grassi e carboidrati. Il set di dati di seguito mostra quante volte Renat cucina ogni mese:

Il primo passo nel calcolo della varianza è determinare la media campionaria, che nel nostro esempio è 7,8 volte al mese. Il resto dei calcoli può essere semplificato utilizzando la tabella seguente.

La fase finale del calcolo della varianza è simile alla seguente:

Per coloro a cui piace fare tutti i calcoli in una volta sola, l'equazione sarebbe simile a questa:

Utilizzando il metodo del conteggio crudo (esempio di cottura)

Esiste un modo più efficiente per calcolare la varianza, noto come metodo del conteggio grezzo. Anche se a prima vista l’equazione può sembrare complicata, in realtà non è poi così spaventosa. Puoi assicurartene e poi decidere quale metodo ti piace di più.

è la somma di ciascun valore di dati dopo il quadrato,

è il quadrato della somma di tutti i valori dei dati.

Non perdere la testa adesso. Mettiamo tutto in una tabella e vedrai che ci sono meno calcoli qui rispetto all'esempio precedente.

Come puoi vedere, il risultato è stato lo stesso di quando si utilizzava il metodo precedente. I vantaggi di questo metodo diventano evidenti all’aumentare della dimensione del campione (n).

Calcolo della varianza in Excel

Come probabilmente hai già intuito, Excel ha una formula che ti consente di calcolare la varianza. Inoltre, a partire da Excel 2010, puoi trovare 4 tipi di formule di varianza:

1) VARIANCE.V – Restituisce la varianza del campione. I valori booleani e il testo vengono ignorati.

2) DISP.G - Restituisce la varianza della popolazione. I valori booleani e il testo vengono ignorati.

3) VARIANZA - Restituisce la varianza del campione, tenendo conto dei valori booleani e di testo.

4) VARIANZA - Restituisce la varianza della popolazione, tenendo conto dei valori logici e di testo.

Innanzitutto, capiamo la differenza tra un campione e una popolazione. Lo scopo delle statistiche descrittive è quello di riassumere o visualizzare i dati in modo da ottenere rapidamente un quadro generale, una panoramica per così dire. L'inferenza statistica consente di fare inferenze su una popolazione sulla base di un campione di dati di quella popolazione. La popolazione rappresenta tutti i possibili risultati o misurazioni che ci interessano. Un campione è un sottoinsieme di una popolazione.

Ad esempio, siamo interessati a un gruppo di studenti di una delle università russe e dobbiamo determinare il punteggio medio del gruppo. Possiamo calcolare il rendimento medio degli studenti, e poi la cifra risultante sarà un parametro, poiché l'intera popolazione sarà coinvolta nei nostri calcoli. Tuttavia, se vogliamo calcolare il GPA di tutti gli studenti del nostro paese, allora questo gruppo sarà il nostro campione.

La differenza nella formula per calcolare la varianza tra un campione e una popolazione è il denominatore. Dove per il campione sarà pari a (n-1), e per la popolazione generale solo a n.

Ora diamo un'occhiata alle funzioni per calcolare la varianza con i finali UN, la cui descrizione afferma che nel calcolo vengono presi in considerazione testo e valori logici. In questo caso, quando si calcola la varianza di un particolare set di dati in cui si verificano valori non numerici, Excel interpreterà il testo e i falsi valori booleani come uguali a 0 e i veri valori booleani come uguali a 1.

Pertanto, se disponi di un array di dati, calcolarne la varianza non sarà difficile utilizzando una delle funzioni di Excel elencate sopra.

Uno dei principali strumenti di analisi statistica è il calcolo della deviazione standard. Questo indicatore consente di stimare la deviazione standard per un campione o per una popolazione. Impariamo come utilizzare la formula della deviazione standard in Excel.

Determiniamo immediatamente cos'è la deviazione standard e come appare la sua formula. Questa quantità è la radice quadrata della media aritmetica dei quadrati della differenza tra tutte le quantità della serie e la loro media aritmetica. Esiste un nome identico per questo indicatore: deviazione standard. Entrambi i nomi sono completamente equivalenti.

Ma, naturalmente, in Excel l'utente non deve calcolarlo, poiché il programma fa tutto per lui. Impariamo come calcolare la deviazione standard in Excel.

Calcolo in Excel

È possibile calcolare il valore specificato in Excel utilizzando due funzioni speciali DEV.ST.V(basato sulla popolazione campione) e DEV.ST.G(basato sulla popolazione generale). Il principio del loro funzionamento è assolutamente lo stesso, ma possono essere chiamati in tre modi, di cui parleremo di seguito.

Metodo 1: Creazione guidata funzione

Metodo 2: scheda Formule

Metodo 3: inserimento manuale della formula

C'è anche un modo in cui non avrai bisogno di chiamare la finestra degli argomenti. Per fare ciò, è necessario inserire manualmente la formula.

Come puoi vedere, il meccanismo per calcolare la deviazione standard in Excel è molto semplice. L'utente deve solo inserire i numeri della popolazione o i riferimenti alle celle che li contengono. Tutti i calcoli vengono eseguiti dal programma stesso. È molto più difficile capire quale sia l'indicatore calcolato e come i risultati del calcolo possano essere applicati nella pratica. Ma comprendere questo riguarda già più il campo della statistica che l’imparare a lavorare con il software.