Il concetto di percentuale di deviazione implica la differenza tra due valori numerici in percentuale. Facciamo un esempio specifico: diciamo che un giorno sono stati venduti 120 compresse dal magazzino all'ingrosso e il giorno successivo - 150 pezzi. La differenza nei volumi di vendita è evidente, con 30 tablet in più venduti il ​​giorno successivo. Sottraendo il numero 120 da 150, otteniamo la deviazione, che è uguale al numero +30. Sorge la domanda: qual è la deviazione percentuale?

Come calcolare la deviazione percentuale in Excel

La percentuale di deviazione viene calcolata sottraendo il vecchio valore dal nuovo valore e quindi dividendo il risultato per il vecchio valore. Il risultato del calcolo di questa formula in Excel dovrebbe essere visualizzato nel formato percentuale della cella. In questo esempio, la formula di calcolo è simile alla seguente (150-120)/120=25%. La formula è facile da controllare 120+25%=150.

Nota! Se scambiamo il vecchio e il nuovo numero, avremo una formula per calcolare il markup.

La figura seguente mostra un esempio di come presentare il calcolo sopra come una formula di Excel. La formula nella cella D2 calcola la variazione percentuale tra le vendite correnti e quelle dell'anno scorso: =(C2-B2)/B2

È importante prestare attenzione alla presenza di parentesi in questa formula. Per impostazione predefinita, in Excel, la divisione ha sempre la precedenza sulla sottrazione. Pertanto, se non mettiamo parentesi, il valore verrà prima diviso e quindi da esso verrà sottratto un altro valore. Tale calcolo (senza parentesi) sarà errato. La chiusura della prima parte del calcolo in una formula tra parentesi aumenta automaticamente la precedenza dell'operazione di sottrazione rispetto all'operazione di divisione.

Correttamente tra parentesi, inserisci la formula nella cella D2, quindi copiala semplicemente nel resto delle celle vuote nell'intervallo D2:D5. Per copiare la formula nel modo più veloce, basta spostare il cursore del mouse sull'indicatore del cursore della tastiera (nell'angolo in basso a destra) in modo che il cursore del mouse cambi da una freccia a una croce nera. Dopodiché, basta fare doppio clic con il tasto sinistro del mouse ed Excel riempirà automaticamente le celle vuote con la formula, mentre determinerà l'intervallo D2:D5, che deve essere riempito fino alla cella D5 e non di più. Questo è un trucco molto utile in Excel.



Formula alternativa per calcolare la percentuale di deviazione in Excel

In una formula alternativa che calcola deviazione relativa i valori di vendita dell'anno in corso vengono immediatamente divisi per i valori di vendita dell'anno precedente, e solo allora ne viene sottratto uno dal risultato: =C2/B2-1.

Come puoi vedere nella figura, il risultato del calcolo della formula alternativa è lo stesso della precedente, il che significa che è corretto. Ma la formula alternativa è più facile da scrivere, anche se potrebbe essere più difficile per qualcuno leggerla per capire come funziona. Oppure è più difficile capire quale valore produce la formula data come risultato del calcolo se non è firmata.

L'unico inconveniente di questa formula alternativa è l'impossibilità di calcolare la deviazione percentuale per i numeri negativi nel numeratore o nel sostituto. Anche se utilizziamo la funzione ABS nella formula, la formula restituirà un risultato errato con un numero negativo nel segnaposto.

Poiché in Excel, per impostazione predefinita, l'operatore di divisione ha la precedenza sull'operatore di sottrazione, non è necessario utilizzare le parentesi in questa formula.

Per trovare il valore medio in Excel (che sia un valore numerico, testuale, percentuale o altro), ci sono molte funzioni. E ognuno di loro ha le sue caratteristiche e vantaggi. Dopotutto, in questa attività possono essere impostate determinate 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, aggiungi tutti i numeri nell'insieme e dividi la somma per il numero. Ad esempio, i voti di uno studente in informatica: 3, 4, 3, 5, 5. Cosa vale per un trimestre: 4. Abbiamo trovato la media aritmetica usando la formula: \u003d (3 + 4 + 3 + 5 + 5) / 5.

Come farlo rapidamente utilizzando le funzioni di Excel? Prendi, ad esempio, la serie numeri casuali in linea:

Oppure: attiva la cella e inserisci semplicemente manualmente la formula: =MEDIA(A1:A8).

Ora vediamo cos'altro può fare la funzione MEDIA.

Trova la media aritmetica dei primi due e degli ultimi tre numeri. Formula: =MEDIA(A1:B1;F1:H1). Risultato:



Media per condizione

La condizione per trovare la media aritmetica può essere un criterio numerico o testuale. Useremo la funzione: =MEDIA.SE().

Trova la media numeri aritmetici che sono maggiori o uguali a 10.

Funzione: =SEMEDIASE(LA1:LA8,">=10")

Il risultato dell'utilizzo della funzione AVERAGEIF alla condizione ">=10":

Il terzo argomento - "Intervallo medio" - viene omesso. In primo luogo, non è richiesto. In secondo luogo, l'intervallo analizzato dal programma contiene SOLO valori numerici. Nelle celle specificate nel primo argomento, la ricerca verrà eseguita in base alla condizione specificata nel secondo argomento.

Attenzione! Il criterio di ricerca può essere specificato in una cella. E nella formula per farvi riferimento.

Troviamo il valore medio dei numeri con il criterio del testo. Ad esempio, le vendite medie del prodotto "tavoli".

La funzione sarà simile a questa: =AVERAGEIF($A$2:$A$12;A7;$B$2:$B$12). Intervallo: 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 medio - 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 di testo (condizione), è necessario specificare l'intervallo di media.

Come calcolare il prezzo medio ponderato in Excel?

Come facciamo a sapere il prezzo medio ponderato?

Formula: =SOMMAPRODOTTO(C2:C12,B2:B12)/SOMMA(C2:C12).

Utilizzando la formula SUMPRODUCT, scopriamo il ricavo totale dopo la vendita dell'intera quantità di merce. E la funzione SOMMA - riassume la quantità di merci. Dividendo il ricavo totale della vendita di beni per il numero totale di unità di beni, abbiamo trovato 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

Distinguere tra la deviazione standard per la popolazione generale e per il campione. Nel primo caso, questa è la radice della varianza generale. Nella seconda, dalla varianza campionaria.

Per calcolare questo indicatore statistico formula per la dispersione La radice è presa da esso. Ma in Excel c'è una funzione già pronta per trovare la deviazione standard.

La deviazione standard è collegata alla scala dei dati di origine. Ciò non basta per una rappresentazione figurativa della variazione del range analizzato. Per ottenere il livello relativo di dispersione nei dati, viene calcolato il coefficiente di variazione:

deviazione standard / media aritmetica

La formula in Excel si presenta così:

STDEV (intervallo di valori) / MEDIA (intervallo di valori).

Il coefficiente di variazione è calcolato in percentuale. Pertanto, impostiamo il formato percentuale nella cella.

La deviazione standard è uno di quei termini statistici nel mondo aziendale che alza il profilo delle persone che riescono a sbagliare con successo in una conversazione o presentazione e lascia un vago malinteso per coloro che non sanno cosa sia ma si vergognano di Chiedi. In effetti, la maggior parte dei manager non comprende il concetto di deviazione standard e, se sei uno di loro, è ora che smetta di vivere la menzogna. Nell'articolo di oggi, ti mostrerò come questa statistica sottovalutata può aiutarti a comprendere meglio i dati con cui stai lavorando.

Cosa misura la deviazione standard?

Immagina di essere il proprietario di due negozi. E per evitare perdite, è importante che vi sia un chiaro controllo dei saldi azionari. Nel tentativo di capire chi è il miglior gestore di azioni, decidi di analizzare i titoli delle ultime sei settimane. Il costo medio settimanale dello stock di entrambi i negozi è approssimativamente lo stesso ed è di circa 32 unità convenzionali. A prima vista, il valore medio del titolo mostra che entrambi i gestori lavorano allo stesso modo.

Ma se osservi più da vicino l'attività del secondo negozio, puoi vedere che sebbene il valore medio sia corretto, la variabilità dello stock è molto alta (da 10 a 58 USD). Pertanto, si può concludere che la media non stima sempre correttamente i dati. È qui che entra in gioco la deviazione standard.

La deviazione standard mostra come sono distribuiti i valori rispetto alla media nel nostro . In altre parole, puoi capire quanto è grande il ballottaggio di settimana in settimana.

Nel nostro esempio abbiamo usato Funzione Excel DEV.ST per calcolare il punteggio della deviazione standard insieme alla media.

Nel caso del primo manager, la deviazione standard era 2. Questo ci dice che ogni valore nel campione devia in media di 2 dalla media. È buono? Esaminiamo la domanda da un'angolazione diversa: una deviazione standard di 0 ci dice che ogni valore nel campione è uguale al suo valore medio (nel nostro caso, 32,2). Ad esempio, una deviazione standard di 2 non è molto diversa da 0, indicando che la maggior parte dei valori è vicina alla media. Più la deviazione standard è vicina a 0, più affidabile è la media. Inoltre, una deviazione standard vicina a 0 indica poca variabilità nei dati. Cioè, un valore sink con una deviazione standard di 2 indica l'incredibile coerenza del primo manager.

Nel caso del secondo negozio, la deviazione standard era 18,9. Cioè, il costo del deflusso devia in media di 18,9 dal valore medio di settimana in settimana. Diffusione pazzesca! Più la deviazione standard è lontana da 0, meno accurata è la media. Nel nostro caso, la cifra di 18,9 indica che non ci si può fidare del valore medio ($ 32,8 a settimana). Ci dice anche che il deflusso settimanale è molto variabile.

Questo è il concetto di deviazione standard in poche parole. Anche se non fornisce informazioni su altri importanti misurazioni statistiche(Modalità, Mediana...), infatti, la deviazione standard gioca un ruolo cruciale nella maggior parte dei calcoli statistici. Comprendere i principi della deviazione standard farà luce sull'essenza di molti processi nella tua attività.

Come calcolare la deviazione standard?

Quindi, ora sappiamo cosa dice la cifra della deviazione standard. Vediamo come conta.

Considera un set di dati da 10 a 70 con incrementi di 10. Come puoi vedere, ho già calcolato la deviazione standard per loro usando la funzione STDEV nella cella H2 (arancione).

Di seguito sono riportati i passaggi eseguiti da Excel per arrivare a 21.6.

Si prega di notare che tutti i calcoli sono visualizzati per una migliore comprensione. In Excel, infatti, il calcolo è istantaneo, lasciando dietro le quinte tutti i passaggi.

Excel trova prima la media del campione. Nel nostro caso, la media è risultata essere 40, che viene sottratta da ciascun valore campione nel passaggio successivo. Ogni differenza risultante è al quadrato e sommata. Abbiamo ottenuto la somma pari a 2800, che deve essere divisa per il numero di elementi del campione meno 1. Poiché abbiamo 7 elementi, risulta che dobbiamo dividere 2800 per 6. Dal risultato troviamo la radice quadrata, questa cifra sarà la deviazione standard.

Per coloro che non sono del tutto chiari sul principio del calcolo della deviazione standard utilizzando la visualizzazione, fornisco un'interpretazione matematica per trovare questo valore.

Funzioni di calcolo della deviazione standard in Excel

Esistono diverse varietà di formule di deviazione standard in Excel. Devi solo digitare =STDEV e vedrai di persona.

Vale la pena notare che le funzioni DEV.ST.V e DEV.ST.G (la prima e la seconda funzione nell'elenco) duplicano le funzioni DEV.ST e DEV.ST (quinta e sesta funzione nell'elenco), rispettivamente, che sono state mantenute per compatibilità con le precedenti versioni di Excel.

In generale, la differenza nelle desinenze In e le funzioni G indicano il principio di calcolo della deviazione standard di un campione o di una popolazione. Ho già spiegato la differenza tra questi due array nel precedente.

Una caratteristica delle funzioni STDEV e STDEVPA (la terza e la quarta funzione nell'elenco) è che quando si calcola la deviazione standard di un array, vengono presi in considerazione i valori logici e di testo. Il testo e i veri booleani sono 1 e i falsi booleani sono 0. È difficile per me immaginare una situazione in cui avrei bisogno di queste due funzioni, quindi penso che possano essere ignorate.

La funzione di deviazione standard è già fuori categoria matematica superiore relativo alla statistica. In Excel sono disponibili diverse opzioni per l'utilizzo della funzione di deviazione standard:

• DEV.ST.
• DEV.ST.
• DEV.ST

Avremo bisogno di queste funzioni nelle statistiche di vendita per identificare la stabilità delle vendite (analisi XYZ). Questi dati possono essere utilizzati sia per il prezzo che per la formazione (correzione) della matrice dell'assortimento e per altre utili analisi di vendita, di cui parlerò sicuramente nei prossimi articoli.

Prefazione

Diamo un'occhiata alle formule prima in linguaggio matematico, quindi (sotto nel testo) analizzeremo nel dettaglio la formula in Excel e come viene applicato il risultato risultante nell'analisi delle statistiche di vendita.

Quindi, la deviazione standard è una stima della deviazione standard variabile casuale X riguardo a lei aspettativa matematica sulla base di una stima imparziale della sua varianza)))) Non aver paura delle parole incomprensibili, abbi pazienza e capirai tutto!

Descrizione della formula: La deviazione standard è misurata in unità della variabile casuale stessa e viene utilizzata per calcolare l'errore standard della media aritmetica, quando si costruiscono intervalli di confidenza, quando si verificano statisticamente ipotesi, quando si misura una relazione lineare tra variabili casuali. Definito come la radice quadrata della varianza di una variabile casuale

Ora la deviazione standard è una stima della deviazione standard di una variabile casuale X rispetto alla sua aspettativa matematica basata su una stima imparziale della sua varianza:

Dispersione;

- io-esimo elemento campione;

Misura di prova;

Esempio di media aritmetica:

Va notato che entrambe le stime sono distorte. Nel caso generale, è impossibile costruire una stima imparziale. Tuttavia, una stima basata su una stima della varianza imparziale è coerente.

regola dei tre sigma() - quasi tutti i valori di una variabile casuale normalmente distribuita si trovano nell'intervallo. Più precisamente, con una probabilità di circa 0,9973, il valore di una variabile casuale normalmente distribuita si trova nell'intervallo specificato (a condizione che il valore sia vero e non ottenuto come risultato dell'elaborazione del campione). Useremo un intervallo arrotondato di 0,1

Se il valore vero è sconosciuto, dovresti usare not, but S. In questo modo, regola del tre sigma viene convertito alla regola del tre S. È questa regola che ci aiuterà a determinare la stabilità delle vendite, ma ne parleremo più avanti...

Ora funzione di deviazione standard in Excel

Spero di non averti sopraffatto con la matematica? Forse qualcuno questa informazione sarà richiesto per un abstract o per qualche altro scopo. Ora vediamo come funzionano queste formule in Excel...

Per determinare la stabilità delle vendite, non è necessario approfondire tutte le opzioni per le funzioni di deviazione standard. Ne useremo solo uno:

DEV.ST

DEV.ST(numero 1;numero 2;... )

Numero1, Numero2,..- da 1 a 30 argomenti numerici corrispondenti alla popolazione generale.

Vediamo ora un esempio:

Creiamo un libro e un foglio di calcolo improvvisato. Puoi scaricare questo esempio in Excel alla fine dell'articolo.

Continua!!!

Ciao di nuovo. Bene!? Hai un minuto gratis. Continuiamo?

E così la stabilità delle vendite con l'aiuto Funzioni DEVST

Per chiarezza, prendiamo alcuni beni improvvisati:

Nell'analisi, che si tratti di una previsione, di una ricerca o di qualcos'altro relativo alle statistiche, è sempre necessario prendere tre periodi. Può essere una settimana, un mese, un trimestre o un anno. È possibile e anche meglio prendere il maggior numero possibile di periodi, ma non meno di tre.

Ho mostrato in particolare vendite esagerate, dove puoi vedere ad occhio nudo cosa viene venduto in modo coerente e cosa no. In questo modo sarà più facile capire come funzionano le formule.

E quindi abbiamo le vendite, ora dobbiamo calcolare i valori medi delle vendite per periodo.

Formula del valore medio MEDIA (dati del periodo) nel mio caso, la formula è simile a questa = MEDIA (C6: E6)

Allarghiamo la formula per tutti i prodotti. Questo può essere fatto tenendo premuto l'angolo destro della cella selezionata e trascinandola alla fine dell'elenco. Oppure posiziona il cursore sulla colonna con il prodotto e premi le seguenti combinazioni di tasti:

Ctrl + Giù sposta il cursore in fondo all'elenco.

Ctrl + Destra, il cursore si sposterà sul lato destro della tabella. Ancora una volta a destra e arriveremo alla colonna con la formula.

Ora fermiamo

Ctrl + Maiusc e premi su. Quindi selezioniamo l'area di allungamento della formula.

E la combinazione di tasti Ctrl + D allungherà la funzione dove ne abbiamo bisogno.

Ricorda queste combinazioni, aumentano davvero la tua velocità in Excel, specialmente quando lavori con array di grandi dimensioni.

La fase successiva, la stessa funzione di deviazione standard, come ho detto, ne useremo solo una DEV.ST

Prescriviamo la funzione e nei valori di funzione mettiamo i valori di vendita di ogni periodo. Se hai vendite nella tabella una dopo l'altra, puoi utilizzare l'intervallo, come nella mia formula =SDV(C6:E6) o elencare le celle richieste con un punto e virgola =SDV(C6;D6;E6)

Ecco tutti i calcoli e pronti. Ma come fai a sapere cosa vende costantemente e cosa no? Mettiamo giù la convenzione XYZ dove,

X è stabile

Y - con piccole deviazioni

Z - non stabile

Per fare ciò, utilizziamo intervalli di errore. se si verificano fluttuazioni entro il 10%, si presume che le vendite siano stabili.

Se tra il 10 e il 25 percento, sarà Y.

E se i valori di variazione superano il 25%, questa non è stabilità.

Per impostare correttamente le lettere per ogni prodotto, utilizzeremo la formula SE in modo più dettagliato. nella mia tavola data funzione sarà simile a questo:

SE(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))

Di conseguenza, estendiamo tutte le formule per tutti i nomi.

Cercherò di rispondere immediatamente alla domanda, perché gli intervalli del 10% e del 25%?

In effetti, gli intervalli possono essere diversi, tutto dipende dall'attività specifica. In particolare ti ho mostrato valori di vendita esagerati, dove la differenza è visibile ad "occhio". È ovvio che il prodotto 1 non viene venduto in modo coerente, ma la dinamica mostra un aumento delle vendite. Lascia stare questo articolo...

Ma il prodotto 2, c'è già una destabilizzazione sul viso. E i nostri calcoli mostrano Z, che ci parla dell'instabilità delle vendite. L'articolo 3 e l'articolo 5 mostrano prestazioni stabili, si prega di notare che la variazione è entro il 10%.

Quelli. L'elemento 5 con punteggi di 45, 46 e 45 mostra una variazione dell'1%, che è una serie numerica stabile.

Ma il prodotto 2 con punteggi di 10, 50 e 5 mostra una variazione del 93%, che NON è una serie numerica stabile.

Dopo tutti i calcoli, puoi mettere un filtro e filtrare la stabilità, quindi se la tua tabella è composta da diverse migliaia di articoli, puoi facilmente selezionare quali non sono stabili nelle vendite o, al contrario, quali sono stabili.

"Y" non ha funzionato nella mia tabella, penso che per chiarezza della serie numerica, debba essere aggiunto. Disegnerò Merci 6...

Vedete, le serie numeriche 40, 50 e 30 mostrano una variazione del 20%. Sembra che non ci sia un grosso errore, ma comunque lo spread è significativo...

E quindi per riassumere:

10,50,5 - Z non è stabile. Variazione oltre il 25%

40,50,30 - Y puoi prestare attenzione a questo prodotto e migliorarne le vendite. Variazione inferiore al 25% ma superiore al 10%

45,46,45 - X è stabilità, non c'è ancora niente da fare con questo prodotto. Variazione inferiore al 10%

È tutto! Spero di aver spiegato tutto in modo chiaro, in caso contrario, chiedere ciò che non è chiaro. E ti sarò grato per ogni commento, sia esso lode o critica. Quindi saprò che stai leggendo me e te, il che è molto IMPORTANTE, interessante. E di conseguenza, appariranno nuove lezioni.

Andrej Lipov

In parole povere, la deviazione standard mostra quanto fluttua il prezzo di uno strumento nel tempo. Cioè, maggiore è questo indicatore, maggiore è la volatilità o la variabilità di un numero di valori.

La deviazione standard può e deve essere utilizzata per analizzare insiemi di valori, poiché due insiemi con apparentemente la stessa media possono risultare completamente diversi in termini di diffusione dei valori.

Esempio

Prendiamo due righe di numeri.

a) 1,2,3,4,5,6,7,8,9. Media - 5. Art. deviazione = 2,7386

b) 20.1.7.1.15, -1, -20.4,18.5. Media - 5. Art. deviazione = 12.2066

Se non tieni l'intera serie di numeri davanti ai tuoi occhi, la deviazione standard mostra che nel caso di "b" i valori sono molto più sparsi attorno al loro valore medio.

In parole povere, nella riga "b" il valore è 5 più o meno 12 (in media) - non esattamente, ma rivela il significato.

Come calcolare la deviazione standard

Per calcolare la deviazione standard è possibile utilizzare la formula presa in prestito dal calcolo della deviazione standard dei rendimenti dei fondi comuni:

dove N è il numero di valori,
DOMedia - la media di tutti i valori,
Periodo DOH - il valore di N.

In Excel, la funzione corrispondente si chiama DEV.ST (o DEV.ST nella versione inglese del programma).

Le istruzioni passo passo sono:

1. Calcola la media di una serie di numeri.
2. Per ogni valore, determinare la differenza tra la media e questo valore.
3. Calcola la somma dei quadrati di queste differenze.
4. Dividi la somma risultante per il numero di numeri della serie.
5. Prendi la radice quadrata del numero ottenuto nell'ultimo paragrafo.

I tuoi amici trarranno vantaggio da queste informazioni. Condividi con loro!