Uno Z-Score è un valore statistico che indica quante deviazioni standard risulta essere un determinato valore dalla media dell’intero set di dati. È possibile utilizzare le formule MEDIA e DEV.ST o DEV.ST.P per calcolare la media e la deviazione standard dei dati, quindi utilizzare tali risultati per determinare il punteggio Z di ciascun valore.
Sommario:
Che cos’è un punteggio Z e cosa fanno le funzioni MEDIA, DEV.ST.P e DEV.ST.
Uno Z-Score è un modo semplice per confrontare i valori di due diversi set di dati. È definito come il numero di deviazioni standard dalla media di un punto dati. La formula generale è simile a questa:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Ecco un esempio per aiutare a chiarire. Supponi di voler confrontare i risultati del test di due studenti di algebra tenuti da insegnanti diversi. Sai che il primo studente ha ottenuto un 95% all’esame finale in una classe e lo studente dell’altra classe ha ottenuto l’87%.
A prima vista, il voto del 95% è più impressionante, ma cosa succederebbe se l’insegnante della seconda classe facesse un esame più difficile? È possibile calcolare il punteggio Z del punteggio di ogni studente in base ai punteggi medi in ciascuna classe e alla deviazione standard dei punteggi in ciascuna classe. Il confronto dei punteggi Z dei due studenti potrebbe rivelare che lo studente con il punteggio dell’87% ha ottenuto risultati migliori rispetto al resto della classe rispetto allo studente con il punteggio del 98% rispetto al resto della classe.
Il primo valore statistico di cui hai bisogno è la “media” e la funzione “MEDIA” di Excel calcola quel valore. Aggiunge semplicemente tutti i valori in un intervallo di celle e divide tale somma per il numero di celle contenenti valori numerici (ignora le celle vuote).
L’altro valore statistico di cui abbiamo bisogno è la “deviazione standard” ed Excel ha due diverse funzioni per calcolare la deviazione standard in modi leggermente diversi.
Le versioni precedenti di Excel avevano solo la funzione “DEV.ST”, che calcola la deviazione standard mentre tratta i dati come un “campione” di una popolazione. Excel 2010 lo ha suddiviso in due funzioni che calcolano la deviazione standard:
DEV.ST: questa funzione è identica alla precedente funzione “DEV.ST”. Calcola la deviazione standard mentre tratta i dati come un “campione” di una popolazione. Un campione di una popolazione potrebbe essere qualcosa come le particolari zanzare raccolte per un progetto di ricerca o le auto che sono state messe da parte e utilizzate per i test di sicurezza in caso di incidente.
DEV.ST.P: questa funzione calcola la deviazione standard trattando i dati come l’intera popolazione. Un’intera popolazione sarebbe qualcosa come tutte le zanzare sulla Terra o ogni macchina in una serie di produzione di un modello specifico.
Quello che scegli è basato sul tuo set di dati. La differenza sarà generalmente piccola, ma il risultato della funzione “DEV.ST.P” sarà sempre inferiore al risultato della funzione “DEV.ST.s.” per lo stesso set di dati. È un approccio più conservativo presumere che vi sia una maggiore variabilità nei dati.
Diamo un’occhiata a un esempio
Per il nostro esempio, abbiamo due colonne (“Values” e “Z-Score”) e tre celle “helper” per memorizzare i risultati delle funzioni “MEDIA”, “DEV.ST.” E “DEV.ST.P”. La colonna “Values” contiene dieci numeri casuali centrati intorno a 500, e la colonna “Z-Score” è dove calcoleremo lo Z-Score utilizzando i risultati memorizzati nelle celle “helper”.
Innanzitutto, calcoleremo la media dei valori utilizzando la funzione “MEDIA”. Selezionare la cella in cui memorizzare il risultato della funzione “MEDIA”.
Digitare la seguente formula e premere Invio -oppure- utilizzare il menu “Formule”.
=AVERAGE(E2:E13)
Per accedere alla funzione tramite il menu “Formule”, selezionare il menu a discesa “Altre funzioni”, selezionare l’opzione “Statistiche”, quindi fare clic su “MEDIA”.
Nella finestra Argomenti funzione, selezionare tutte le celle nella colonna “Valori” come input per il campo “Numero1”. Non devi preoccuparti del campo “Numero2”.
Ora premi “OK”.
Successivamente, dobbiamo calcolare la deviazione standard dei valori utilizzando la funzione “DEV.ST./S” o “DEV.ST.P”. In questo esempio, ti mostreremo come calcolare entrambi i valori, iniziando con “DEV.ST.” Seleziona la cella in cui verrà archiviato il risultato.
Per calcolare la deviazione standard utilizzando la funzione “DEV.ST”, digitare questa formula e premere Invio (o accedervi dal menu “Formule”).
=STDEV.S(E3:E12)
Per accedere alla funzione tramite il menu “Formule”, selezionare il menu a tendina “Altre funzioni”, selezionare l’opzione “Statistiche”, scorrere un po ‘verso il basso, quindi fare clic sul comando “DEV.ST”.
Nella finestra Argomenti funzione, seleziona tutte le celle nella colonna “Valori” come input per il campo “Numero1”. Non devi nemmeno preoccuparti del campo “Numero2” qui.
Ora premi “OK”.
Successivamente, calcoleremo la deviazione standard utilizzando la funzione “DEV.ST.P”. Seleziona la cella in cui verrà archiviato il risultato.
Per calcolare la deviazione standard utilizzando la funzione “DEV.ST.P”, digitare questa formula e premere Invio (o accedervi tramite il menu “Formule”).
= DEV.ST.P (E3: E12)
Per accedere alla funzione tramite il menu “Formule”, selezionare il menu a tendina “Altre funzioni”, selezionare l’opzione “Statistica”, scorrere un po ‘verso il basso, quindi fare clic sulla formula “DEV.ST.P”.
Nella finestra Argomenti funzione, selezionare tutte le celle nella colonna “Valori” come input per il campo “Numero1”. Anche in questo caso, non dovrai preoccuparti del campo “Numero2”.
Ora premi “OK”.
Ora che abbiamo calcolato la media e la deviazione standard dei nostri dati, abbiamo tutto ciò di cui abbiamo bisogno per calcolare lo Z-Score. Possiamo usare una semplice formula che fa riferimento alle celle contenenti i risultati delle funzioni “MEDIA” e “DEV.ST./S” o “DEV.ST.P”.
Seleziona la prima cella nella colonna “Punteggio Z”. Useremo il risultato della funzione “DEV.ST.P.” Per questo esempio, ma potresti anche utilizzare il risultato di “DEV.ST.P”.
Digita la seguente formula e premi Invio:
=(E3-$G$3)/$H$3
In alternativa, puoi utilizzare i seguenti passaggi per inserire la formula invece di digitare:
Fare clic sulla cella F3 e digitare = (
Seleziona la cella E3. (Puoi premere il tasto freccia sinistra una volta o utilizzare il mouse)
Digita il segno meno –
Seleziona la cella G3, quindi premi F4 per aggiungere i caratteri “$” per creare un riferimento “assoluto” alla cella (scorrerà tra “G3″> “$ G $ 3″> “G $ 3″> “$ G3″> “G3 “Se continui a premere F4)
Genere )/
Selezionare la cella H3 (o I3 se si utilizza “DEV.ST.P”) e premere F4 per aggiungere i due caratteri “$”.
premere Invio
Lo Z-Score è stato calcolato per il primo valore. È 0,15945 deviazioni standard al di sotto della media. Per verificare i risultati, è possibile moltiplicare la deviazione standard per questo risultato (6.271629 * -0.15945) e verificare che il risultato sia uguale alla differenza tra il valore e la media (499-500). Entrambi i risultati sono uguali, quindi il valore ha senso.
Calcoliamo gli Z-Score del resto dei valori. Evidenzia l’intera colonna “Punteggio Z” a partire dalla cella contenente la formula.
Premi Ctrl + D, che copia la formula nella cella in alto verso il basso attraverso tutte le altre celle selezionate.
Ora la formula è stata ‘riempita’ per tutte le celle, e ciascuna farà sempre riferimento alle celle “MEDIA” e “DEV.ST.ST” o “DEV.ST.P” corrette a causa dei caratteri “$”. Se ricevi errori, torna indietro e assicurati che i caratteri “$” siano inclusi nella formula che hai inserito.
Calcolo dello Z-Score senza usare le celle “Helper”
Le celle helper memorizzano un risultato, come quelle che memorizzano i risultati delle funzioni “MEDIA”, “DEV.ST” e “DEV.ST.P”. Possono essere utili ma non sempre sono necessari. Puoi saltarli del tutto quando calcoli un punteggio Z utilizzando invece le seguenti formule generalizzate.
Eccone uno che utilizza la funzione “STDEV.S”:
=(Value-AVERAGE(Values))/STDEV.S(Values)
E uno che utilizza la funzione “STEV.P”:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Quando inserisci gli intervalli di celle per i “Valori” nelle funzioni, assicurati di aggiungere riferimenti assoluti (“$” utilizzando F4) in modo che quando “riempi” non stai calcolando la media o la deviazione standard di un intervallo diverso di celle in ogni formula.
Se si dispone di un set di dati di grandi dimensioni, potrebbe essere più efficiente utilizzare le celle helper perché non calcola il risultato delle funzioni “MEDIA” e “DEV.ST” o “DEV.ST.P” ogni volta, risparmiando risorse del processore e accelerando il tempo necessario per calcolare i risultati.
Inoltre, “$ G $ 3” richiede meno byte da memorizzare e meno RAM da caricare rispetto a “MEDIA ($ E $ 3: $ E $ 12)”. Questo è importante perché la versione standard a 32 bit di Excel è limitata a 2 GB di RAM (la versione a 64 bit non ha limitazioni sulla quantità di RAM che può essere utilizzata).