Come calcolare un punteggio Z utilizzando Microsoft Excel

Un punteggio Z è un valore statistico che indica quante deviazioni standard si verifica un determinato valore dalla media dell'intero set di dati. È possibile utilizzare le formule MEDIA e DEV.ST o STDEV.P per calcolare la media e la deviazione standard dei dati e quindi utilizzare tali risultati per determinare il punteggio Z di ciascun valore.

Che cos'è un punteggio Z e cosa fanno le funzioni MEDIA, DEV.ST. e DEV.V.D.?

Un punteggio Z è un modo semplice per confrontare i valori di due diversi set di dati. È definito come il numero di deviazioni standard lontano dalla media di un punto dati. La formula generale ha questo aspetto:

= (DataPoint-MEDIA (DataSet)) / STDEV (DataSet)

Ecco un esempio per aiutare a chiarire. Supponi di voler confrontare i risultati dei test di due studenti di Algebra insegnati da diversi insegnanti. Sai che il primo studente ha ottenuto il 95% dell'esame finale in una classe e lo studente nell'altra classe ha ottenuto l'87%.

A prima vista, il grado del 95% è più impressionante, ma cosa succede se l'insegnante della seconda classe ha dato un esame più difficile? È possibile calcolare il punteggio Z del punteggio di ogni studente in base ai punteggi medi in ogni classe e alla deviazione standard dei punteggi in ciascuna classe. Confrontando i punteggi Z dei due studenti si potrebbe scoprire che lo studente con il punteggio dell'87% ha fatto meglio rispetto al resto della classe rispetto allo studente con il punteggio del 98% rispetto al resto della classe.

Il primo valore statistico necessario è "mean" e la funzione "MEDIA" di Excel calcola tale valore. Semplicemente somma tutti i valori in un intervallo di celle e divide quella somma per il numero di celle contenenti valori numerici (ignora le celle vuote).

L'altro valore statistico di cui abbiamo bisogno è la "deviazione standard" e 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 trattava i dati come "campione" di una popolazione. Excel 2010 l'ha suddiviso in due funzioni che calcolano la deviazione standard:

STDEV.S: 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 anticrash.
STDEV.P: Questa funzione calcola la deviazione standard mentre tratta i dati come l'intera popolazione. Un'intera popolazione sarebbe qualcosa come tutte le zanzare sulla Terra o ogni auto in una corsa di produzione di un modello specifico.

Quale scegli tu si basa sul tuo set di dati. La differenza sarà in genere piccola, ma il risultato della funzione "DEV.V.P" sarà sempre inferiore al risultato della funzione "DEV.ST." per lo stesso set di dati. È un approccio più conservativo per supporre che ci sia più variabilità nei dati.

Diamo un'occhiata ad un esempio

Per il nostro esempio, abbiamo due colonne ("Valori" e "Z-Score") e tre celle "helper" per memorizzare i risultati delle funzioni "MEDIA", "STDEV.S" e "STDEV.P". La colonna "Valori" contiene dieci numeri casuali centrati attorno a 500, e la colonna "Punteggio Z" è dove calcoleremo il punteggio Z utilizzando i risultati memorizzati nelle celle "helper".

Innanzitutto, calcoleremo la media dei valori usando la funzione "MEDIA". Seleziona la cella in cui archiverai il risultato della funzione "MEDIA".

Digitare la seguente formula e premere accedere -oppure- usa il menu "Formule".

= MEDIA (E2: E13)

Per accedere alla funzione tramite il menu "Formule", selezionare il menu a discesa "Altre funzioni", selezionare l'opzione "Statistico", quindi fare clic su "MEDIA".

Nella finestra Argomenti della 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 usando la funzione "STDEV.S" o "STDEV.P". In questo esempio, ti mostreremo come calcolare entrambi i valori, iniziando con "STDEV.S." 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 (oppure accedervi tramite il menu "Formule").

= STDEV.S (E3: E12)

Per accedere alla funzione tramite il menu "Formule", selezionare il menu a discesa "Altre funzioni", selezionare l'opzione "Statistico", scorrere verso il basso un bit, quindi fare clic sul comando "STDEV.S".

Nella finestra Argomenti della funzione, selezionare tutte le celle nella colonna "Valori" come input per il campo "Numero1". Non è necessario preoccuparsi del campo "Numero2" qui.

Ora premi "OK".

Successivamente, calcoleremo la deviazione standard usando la funzione "STDEV.P". Seleziona la cella in cui verrà archiviato il risultato.

Per calcolare la deviazione standard utilizzando la funzione "DEV.V.P", digita questa formula e premi Invio (oppure accedila tramite il menu "Formule").

= STDEV.P (E3: E12)

Per accedere alla funzione attraverso il menu "Formule", selezionare il menu a discesa "Altre funzioni", selezionare l'opzione "Statistica", scorrere verso il basso un po 'e quindi fare clic sulla formula "STDEV.P".

Nella finestra Argomenti della funzione, selezionare tutte le celle nella colonna "Valori" come input per il campo "Numero1". Di nuovo, non dovrai preoccuparti del campo "Numero2".

Ora premi "OK".

Ora che abbiamo calcolato la media e la deviazione standard dei nostri dati, abbiamo tutto il necessario per calcolare il punteggio Z. Possiamo utilizzare una semplice formula che fa riferimento alle celle contenenti i risultati delle funzioni "MEDIA" e "DEV.ST." o "DEV.V.P".

Seleziona la prima cella nella colonna "Z-Score". Utilizzeremo il risultato della funzione "DEV.ST." per questo esempio, ma potresti anche utilizzare il risultato da "DEV.V.P".

Digita la seguente formula e premi Invio:

= (I3- $ G $ 3) / $ H $ 3

In alternativa, puoi utilizzare i seguenti passaggi per inserire la formula anziché digitare:

Fare clic sulla cella F3 e digitare = (
Seleziona la cella E3. (È possibile premere il tasto left-arrow-chiave una volta o usa il mouse)
Digita il segno meno -
Selezionare la cella G3 quindi premere F4 aggiungere i caratteri "$" per creare un riferimento "assoluto" alla cella (scorrerà ciclicamente su "G3"> "$sol$3 ">" G$3 ">"$G3 ">" G3 "se si continua a premere F4)
genere ) /
Selezionare la cella H3 (o I3 se si utilizza "STDEV.P") e premere F4 per aggiungere i due caratteri "$".
premere Invio

Il punteggio Z è stato calcolato per il primo valore. È 0.15945 deviazioni standard al di sotto della media. Per verificare i risultati, è possibile moltiplicare la deviazione standard di 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 un senso.

Calcoliamo gli Z-Scores del resto dei valori. Evidenzia l'intera colonna 'Z-Score' a partire dalla cella contenente la formula.

Premi Ctrl + D, che copia la formula nella cella superiore verso il basso attraverso tutte le altre celle selezionate.

Ora la formula è stata "riempita" a tutte le celle e ciascuna farà sempre riferimento alle celle "MEDIA" e "STDEV.S" o "STDEV.P" corrette a causa dei caratteri "$". Se ricevi errori, torna indietro e assicurati che i caratteri "$" siano inclusi nella formula inserita.

Calcolo del punteggio Z senza utilizzare le celle 'Helper'

Le celle di supporto memorizzano un risultato, come quelli che memorizzano i risultati delle funzioni "MEDIA", "STDEV.S" e "STDEV.P". Possono essere utili ma non sempre sono necessari. Puoi saltarli del tutto quando invece calcoli un punteggio Z usando le seguenti formule generalizzate.

Ecco uno che usa la funzione "STDEV.S":

= (Valore medio (Valori)) / STDEV.S (Valori)

E uno con la funzione "STEV.P":

= (Valore medio (Valori)) / STDEV.P (Valori)

Quando si inseriscono gli intervalli di celle per i "Valori" nelle funzioni, assicurarsi di aggiungere riferimenti assoluti ("$" usando F4) in modo che quando si "riempi" non si calcoli la deviazione media o standard di un intervallo diverso di cellule in ogni formula.

Se si dispone di un set di dati di grandi dimensioni, potrebbe essere più efficiente utilizzare le celle di supporto perché non calcola il risultato delle funzioni "MEDIA" e "STDEV.S" o "STDEV.P" ogni volta, risparmiando risorse del processore e accelerare 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 di 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).