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:
Utilizzo del metodo di 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 sono necessari meno calcoli 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 sopra elencate.
Le statistiche utilizzano un numero enorme di indicatori e uno di questi sta calcolando la varianza in Excel. Se lo fai manualmente, ci vorrà molto tempo e potresti commettere molti errori. Oggi vedremo come scomporre le formule matematiche in funzioni semplici. Diamo un'occhiata ad alcuni dei metodi di calcolo più semplici, veloci e convenienti che ti permetteranno di fare tutto in pochi minuti.
Calcola la varianza
La varianza di una variabile casuale è l'aspettativa matematica della deviazione al quadrato di una variabile casuale dalla sua aspettativa matematica.
Calcoliamo in base alla popolazione generale
Per calcolare il mat. In attesa che il programma utilizzi la funzione DISP.G, e la sua sintassi è questa: “=DISP.G(Numero1;Numero2;…)”.
È possibile utilizzare un massimo di 255 argomenti, non di più. Gli argomenti possono essere numeri primi o riferimenti alle celle in cui sono specificati. Diamo un'occhiata a come calcolare la varianza in Microsoft Excel:
1. Il primo passo è selezionare la cella in cui verrà visualizzato il risultato del calcolo, quindi fare clic sul pulsante "Inserisci funzione".
2. Si aprirà la shell di gestione delle funzioni. Lì devi cercare la funzione “DISP.G”, che può trovarsi nella categoria “Statistica” o “Elenco alfabetico completo”. Una volta trovato, selezionalo e fai clic su "OK".
3. Si aprirà una finestra con gli argomenti della funzione. In esso è necessario selezionare la riga "Numero 1" e sul foglio selezionare l'intervallo di celle con la serie numerica.
4. Successivamente, i risultati del calcolo verranno visualizzati nella cella in cui è stata inserita la funzione.
Ecco come puoi trovare facilmente la varianza in Excel.
Effettuiamo calcoli in base al campione
In questo caso, la varianza campionaria in Excel viene calcolata con il denominatore che indica non il numero totale di numeri, ma uno in meno. Questo viene fatto per un errore più piccolo utilizzando la funzione speciale DISP.V, la cui sintassi è =DISP.V(Numero1;Numero2;...). Algoritmo delle azioni:
- Come nel metodo precedente, è necessario selezionare la cella per il risultato.
- Nella procedura guidata delle funzioni, dovresti trovare "DISP.B" nella categoria "Elenco alfabetico completo" o "Statistica".
- Successivamente, apparirà una finestra e dovresti procedere come nel metodo precedente.
Video: calcolo della varianza in Excel
Conclusione
La varianza in Excel viene calcolata in modo molto semplice, molto più veloce e più conveniente rispetto a farlo manualmente, perché la funzione di aspettativa matematica è piuttosto complessa e calcolarla può richiedere molto tempo e impegno.
Il programma Excel è molto apprezzato sia dai professionisti che dai dilettanti, perché utenti di qualsiasi livello possono lavorarci. Ad esempio, chiunque abbia competenze minime di “comunicazione” in Excel può disegnare un semplice grafico, realizzare un piatto decente, ecc.
Allo stesso tempo, questo programma consente anche di eseguire vari tipi di calcoli, ad esempio calcoli, ma ciò richiede un livello di formazione leggermente diverso. Tuttavia, se hai appena iniziato a conoscere da vicino questo programma e sei interessato a tutto ciò che ti aiuterà a diventare un utente più avanzato, questo articolo fa per te. Oggi ti dirò qual è la formula della deviazione standard in Excel, perché è necessaria e, in senso stretto, quando viene utilizzata. Andare!
Cos'è
Cominciamo con la teoria. La deviazione standard è solitamente chiamata radice quadrata ottenuta dalla media aritmetica di tutte le differenze quadrate tra i valori disponibili, nonché dalla loro media aritmetica. A proposito, questo valore è solitamente chiamato la lettera greca "sigma". La deviazione standard viene calcolata utilizzando la formula STANDARDEVAL; di conseguenza, il programma lo fa per l'utente stesso.
L'essenza di questo concetto è identificare il grado di variabilità di uno strumento, che è, a suo modo, un indicatore derivato dalla statistica descrittiva. Identifica i cambiamenti nella volatilità di uno strumento in un determinato periodo di tempo. Le formule DEV.ST possono essere utilizzate per stimare la deviazione standard di un campione, ignorando i valori booleani e di testo.
Formula
La formula fornita automaticamente in Excel aiuta a calcolare la deviazione standard in Excel. Per trovarlo, devi trovare la sezione della formula in Excel, quindi selezionare quella chiamata STANDARDEVAL, quindi è molto semplice.
Successivamente, apparirà davanti a te una finestra in cui dovrai inserire i dati per il calcolo. In particolare, è necessario inserire due numeri in campi appositi, dopodiché il programma stesso calcolerà la deviazione standard del campione.
Indubbiamente, le formule e i calcoli matematici sono una questione piuttosto complessa e non tutti gli utenti riescono ad affrontarli immediatamente. Tuttavia, se scavi un po’ più a fondo e guardi la questione in modo un po’ più dettagliato, si scopre che non tutto è così triste. Spero che tu ne sia convinto usando l'esempio del calcolo della deviazione standard.
Video per aiutare
Il concetto di deviazione percentuale si riferisce alla differenza tra due valori numerici come percentuale. Facciamo un esempio concreto: diciamo che un giorno sono state vendute 120 compresse da un magazzino all'ingrosso e il giorno successivo - 150 pezzi. La differenza nei volumi di vendita è evidente: il giorno successivo sono state vendute 30 compresse in più. Sottraendo il numero 120 da 150, otteniamo una deviazione pari al numero +30. La domanda sorge spontanea: cos'è la deviazione percentuale?
Come calcolare la deviazione percentuale in Excel
La deviazione percentuale viene calcolata sottraendo il vecchio valore dal nuovo valore e quindi dividendo il risultato per il vecchio valore. Il risultato di questo calcolo della formula in Excel dovrebbe essere visualizzato nel formato percentuale di cella. In questo esempio, la formula di calcolo è la seguente (150-120)/120=25%. La formula è facile da verificare: 120+25%=150.
Nota! Se scambiamo i numeri vecchi e nuovi, avremo una formula per calcolare il margine.
La figura seguente mostra un esempio di come presentare il calcolo precedente come formula Excel. La formula nella cella D2 calcola la deviazione percentuale tra i valori di vendita dell'anno corrente e dell'anno scorso: =(C2-B2)/B2
È importante prestare attenzione alla presenza di parentesi in questa formula. Per impostazione predefinita, in Excel l'operazione di divisione ha sempre la precedenza sull'operazione di sottrazione. Pertanto, se non mettiamo parentesi, il valore verrà prima diviso e poi ne verrà sottratto un altro. Tale calcolo (senza la presenza di parentesi) sarà errato. Chiudendo la prima parte di un calcolo in una formula con parentesi si aumenta automaticamente la priorità dell'operazione di sottrazione rispetto all'operazione di divisione.
Inserisci correttamente la formula tra parentesi nella cella D2, quindi copiala semplicemente nelle restanti celle vuote dell'intervallo D2: D5. Per copiare la formula nel modo più veloce, basta spostare il cursore del mouse sul marcatore del cursore della tastiera (nell'angolo in basso a destra) in modo che il cursore del mouse cambi da freccia a croce nera. Quindi basta fare doppio clic con il pulsante sinistro del mouse ed Excel riempirà automaticamente le celle vuote con la formula e determinerà l'intervallo D2:D5, che deve essere riempito fino alla cella D5 e non oltre. Questo è un trucchetto molto utile per Excel.
Formula alternativa per il calcolo della deviazione percentuale in Excel
In una formula alternativa che calcola la deviazione relativa dei valori di vendita rispetto all'anno in corso, si divide immediatamente per i valori di vendita dell'anno precedente, e solo allora si sottrae uno dal risultato: =C2/B2-1.
Come puoi vedere nella figura, il risultato del calcolo della formula alternativa è lo stesso di quello precedente, e quindi corretto. Ma la formula alternativa è più facile da scrivere, anche se per alcuni può essere più difficile da leggere per comprenderne il principio di funzionamento. Oppure è più difficile capire quale valore produce una data formula come risultato di un calcolo se non è firmata.
L'unico inconveniente di questa formula alternativa è l'impossibilità di calcolare la deviazione percentuale per i numeri negativi al numeratore o al sostituto. Anche se utilizziamo la funzione ABS nella formula, la formula restituirà un risultato errato se il numero nel sostituto è negativo.
Poiché in Excel, per impostazione predefinita, la priorità dell'operazione di divisione è superiore a quella dell'operazione di sottrazione, non è necessario utilizzare le parentesi in questa formula.
Per trovare il valore medio in Excel (non importa se si tratta di un valore numerico, di testo, percentuale o altro), ci sono molte funzioni. E ognuno di loro ha le sue caratteristiche e vantaggi. In effetti, in questo compito possono essere poste alcune condizioni.
Ad esempio, i valori medi di una serie di numeri in Excel vengono calcolati utilizzando funzioni statistiche. Puoi anche inserire manualmente la tua formula. Consideriamo varie opzioni.
Come trovare la media aritmetica dei numeri?
Per trovare la media aritmetica, devi sommare tutti i numeri dell'insieme e dividere la somma per la quantità. Ad esempio, i voti di uno studente in informatica: 3, 4, 3, 5, 5. Cosa è compreso nel trimestre: 4. Abbiamo trovato la media aritmetica utilizzando la formula: =(3+4+3+5+5) /5.
Come farlo rapidamente utilizzando le funzioni di Excel? Prendiamo ad esempio una serie di numeri casuali in una stringa:
Oppure: crea la cella attiva e inserisci semplicemente la formula manualmente: = MEDIA (A1: A8).
Ora vediamo cos'altro può fare la funzione MEDIA.
Troviamo la media aritmetica dei primi due e degli ultimi tre numeri. Formula: =MEDIA(A1:B1;F1:H1). Risultato:
Condizioni nella media
La condizione per trovare la media aritmetica può essere un criterio numerico o testuale. Utilizzeremo la funzione: =MEDIA.SE().
Trova la media aritmetica dei numeri maggiori o uguali a 10.
Funzione: =MEDIA.SE(A1:A8;">=10")
Il risultato dell'utilizzo della funzione MEDIA.SE nella condizione ">=10": Il terzo argomento – “Intervallo di media” – viene omesso. Innanzitutto non è obbligatorio. In secondo luogo, l'intervallo analizzato dal programma contiene SOLO valori numerici. Le celle specificate nel primo argomento verranno cercate in base alla condizione specificata nel secondo argomento.
Attenzione! Il criterio di ricerca può essere specificato nella cella. E crea un collegamento ad esso nella formula.
Troviamo il valore medio dei numeri utilizzando il criterio del testo. Ad esempio, le vendite medie dei prodotti “tavoli”.
La funzione sarà simile a questa: =MEDIA.SE($A$2:$A$12,A7,$B$2:$B$12). Gamma: una colonna con i nomi dei prodotti. Il criterio di ricerca è un collegamento ad una cella con la parola “tabelle” (puoi inserire la parola “tabelle” al posto del collegamento A7). Intervallo di media: quelle celle da cui verranno presi i dati per calcolare il valore medio.
Come risultato del calcolo della funzione, otteniamo il seguente valore:
Attenzione! Per un criterio testuale (condizione), è necessario specificare l'intervallo della media.
Come calcolare il prezzo medio ponderato in Excel?
Come abbiamo scoperto il prezzo medio ponderato?
Formula: =SOMMAPRODOTTO(C2:C12;B2:B12)/SOMMA(C2:C12).
Utilizzando la formula SUMPRODOTTO, troviamo le entrate totali dopo aver venduto l'intera quantità di beni. E la funzione SOMMA riassume la quantità di beni. Dividendo i ricavi totali derivanti dalla vendita di beni per il numero totale di unità di beni, abbiamo ottenuto il prezzo medio ponderato. Questo indicatore tiene conto del “peso” di ciascun prezzo. La sua quota nella massa totale dei valori.
Deviazione standard: formula in Excel
Esistono deviazioni standard per la popolazione generale e per il campione. Nel primo caso, questa è la radice della varianza generale. Nel secondo, dalla varianza campionaria.
Per calcolare questo indicatore statistico, viene compilata una formula di dispersione. Da esso si estrae la radice. Ma in Excel esiste una funzione già pronta per trovare la deviazione standard.
La deviazione standard è legata alla scala dei dati di origine. Ciò non è sufficiente per una rappresentazione figurata della variazione dell'intervallo analizzato. Per ottenere il livello relativo di dispersione dei dati, viene calcolato il coefficiente di variazione:
deviazione standard/media aritmetica
La formula in Excel è simile alla seguente:
DEV.ST (intervallo di valori) / MEDIA (intervallo di valori).
Il coefficiente di variazione è calcolato in percentuale. Pertanto, impostiamo il formato percentuale nella cella.