Un punteggio Z è un valore statistico che indica quante deviazioni standard risulta un determinato valore dalla media dell'intero set di dati. È possibile utilizzare le formule AVERAGE e STDEV.S o STDEV.P per calcolare la deviazione media e 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 AVERAGE, STDEV.S e STDEV.P?
Un punteggio Z è 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 alla seguente:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Ecco un esempio per chiarire. Supponi di voler confrontare i risultati dei test di due studenti di Algebra insegnati da insegnanti diversi. 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 voto 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 di ciascuna classe e alla deviazione standard dei punteggi di ciascuna classe. Il confronto dei punteggi Z dei due studenti potrebbe rivelare 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 è 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 “STDEV”, che calcola la deviazione standard trattando i dati come un “campione” di una popolazione. Excel 2010 lo ha suddiviso in due funzioni che calcolano la deviazione standard:
- STDEV.S: Questa funzione è identica alla precedente funzione “STDEV”. Calcola la deviazione standard mentre tratta i dati come un “campione” di una popolazione. Un campione di una popolazione potrebbe essere qualcosa di simile alle zanzare particolari raccolte per un progetto di ricerca o alle auto che sono state messe da parte e utilizzate per i test di sicurezza sugli incidenti.
- 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 macchina in una serie di modelli specifici.
La scelta effettuata si basa sul set di dati. La differenza sarà generalmente piccola, ma il risultato della funzione “STDEV.P” sarà sempre più piccolo del risultato della funzione “STDEV.S” per lo stesso set di dati. È un approccio più conservativo supporre che vi sia una maggiore variabilità nei dati.
Vediamo un esempio
Per il nostro esempio, abbiamo due colonne (“Valori” e “Punteggio Z”) e tre celle “helper” per la memorizzazione dei risultati delle funzioni “MEDIA”, “STDEV.S” e “STDEV.P”. La colonna “Valori” contiene dieci numeri casuali centrati intorno 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 utilizzando la funzione “MEDIA”. Seleziona la cella in cui memorizzerai il risultato della funzione “MEDIA”.
Digita la seguente formula e premi accedere -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 “Statistica”, 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 “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 “STDEV.S”, digitare questa formula e premere Invio (o 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 “Statistica”, scorrere un po 'verso il basso, quindi fare clic sul comando “STDEV.S”.
Nella finestra Argomenti funzione, selezionare 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 “STDEV.P”. Seleziona la cella in cui verrà archiviato il risultato.
Per calcolare la deviazione standard utilizzando la funzione “STDEV.P”, digitare questa formula e premere Invio (o accedervi tramite il menu “Formule”).
= STDEV.P (E3: E12)
Per accedere alla funzione tramite il menu “Formule”, selezionare il menu a discesa “Altre funzioni”, selezionare l'opzione “Statistica”, scorrere un po 'verso il basso, quindi fare clic sulla formula “STDEV.P”.
Nella finestra Argomenti funzione, selezionare tutte le celle nella colonna “Valori” come input per il campo “Numero1”. Ancora una volta, non dovrai preoccuparti del campo “Numero2”.
Ora premi “OK”.
Ora che abbiamo calcolato la deviazione media e standard dei nostri dati, abbiamo tutto ciò di cui abbiamo bisogno per calcolare il punteggio Z. Possiamo usare una semplice formula che fa riferimento alle celle contenenti i risultati delle funzioni “MEDIA” e “STDEV.S” o “STDEV.P”.
Seleziona la prima cella nella colonna “Punteggio Z”. Useremo il risultato della funzione “STDEV.S” per questo esempio, ma potresti anche usare il risultato di “STDEV.P”.
Digita la formula seguente e premi Invio:
=(E3-$G$3)/$H$3
In alternativa, è possibile utilizzare i passaggi seguenti per immettere la formula anziché digitare:
- Fai clic sulla cella F3 e digita
=( - Seleziona la cella E3. (È possibile premere il tasto left-arrow-chiave una volta o usa il mouse)
- Digita il segno meno
- - Seleziona la cella G3 quindi premi F4 per aggiungere i caratteri “$” per fare un riferimento “assoluto” alla cella (scorrerà tra “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. Sono 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 i punteggi Z 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 attraverso tutte le altre celle selezionate.
Ora la formula è stata “riempita” per tutte le celle e ognuna farà sempre riferimento alle celle “AVERAGE” 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 che hai inserito.
Calcolo del punteggio Z senza utilizzare le celle “Helper”
Le celle di supporto memorizzano un risultato, come quelle che memorizzano i risultati delle funzioni “MEDIA”, “STDEV.S” e “STDEV.P”. Possono essere utili ma non sono sempre necessari. Puoi invece saltarli del tutto quando calcoli un punteggio Z usando 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 si inseriscono gli intervalli di celle per i “Valori” nelle funzioni, assicurarsi di aggiungere riferimenti assoluti (“$” utilizzando F4) in modo che quando si esegue il “riempimento” non si stia calcolando 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 “AVERAGE” e “STDEV.S” o “STDEV.P” ogni volta, risparmiando risorse del processore e accelerando il tempo necessario per calcolare i risultati.
Inoltre, “$ G $ 3” richiede meno byte per l'archiviazione e meno RAM per il caricamento rispetto a “MEDIA ($ E $ 3: $ E $ 12).”. Questo è importante perché la versione standard di Excel a 32 bit è limitata a 2 GB di RAM (la versione a 64 bit non ha limiti sulla quantità di RAM che può essere utilizzata).
