Un Z-Score è un valore statistico che indica il numero di deviazioni standard un valore particolare capita di essere dalla media dell’intero set di dati. È possibile utilizzare MEDIA e STDEV.S o STDEV.P formule per calcolare la media e la deviazione standard dei dati e quindi utilizzare tali risultati per determinare il Punteggio Z di ogni valore.
Che cosa è un Punteggio Z e cosa fanno i MEDIA, STDEV.S, e STDEV.P funzioni?
Un Z-Score è un modo semplice per confrontare i valori di due diversi insiemi di dati. Essa è definita come il numero di deviazioni standard di distanza dalla media di un punto dati si trova. La formula generale assomiglia a questo:
=(DataPoint-MEDIA(DataSet))/VAR(DataSet)
Ecco un esempio per chiarire. Dire che si è voluto confrontare i risultati dei test di due Algebra studenti insegnata da insegnanti diversi. Sai che il primo studente ha ottenuto un 95% l’esame finale in una classe e lo studente in un’altra classe ha segnato all ‘ 87%.
A prima vista, il 95% di grado è più imponente, ma cosa succede se il maestro di seconda classe ha dato una più difficile dell’esame? È possibile calcolare il Punteggio Z di ogni studente punteggio in base alla media dei punteggi a ciascuna classe e la deviazione standard dei punteggi in ciascuna classe. Confrontando i Z i Punteggi dei due studenti potrebbe rivelare che lo studente, con il 87% punteggio ha fatto meglio rispetto al resto della classe che lo studente con il 98% punteggio fatto in confronto al resto della classe.
Il primo valore statistico di cui hai bisogno è la ‘media’ e di Excel “MEDIA” funzione calcola il valore. Si aggiunge semplicemente il backup di tutti i valori in un intervallo di celle e divide tale somma per il numero di celle contenenti i valori numerici (ignora celle vuote).
L’altro valore statistico di cui abbiamo bisogno è la ” deviazione standard’ e di Excel ha due diverse funzioni per calcolare la deviazione standard in modi leggermente diversi.
Nelle versioni precedenti di Excel avuto solo il “VAR” la funzione che calcola la deviazione standard, mentre il trattamento dei dati come un “campione” di una popolazione. Excel 2010 si è rotto in due funzioni che consentono di calcolare la deviazione standard:
- STDEV.S: Questa funzione è identica alla precedente “VAR”. Calcola la deviazione standard, mentre il trattamento dei dati come un “campione” di una popolazione. Un campione di una popolazione potrebbe essere qualcosa di simile al particolare zanzare raccolte per un progetto di ricerca o di auto che sono state messe da parte, e utilizzato per il crash test di sicurezza.
- STDEV.P: Questa funzione calcola la deviazione standard, mentre il trattamento dei dati dell’intera popolazione. Un’intera popolazione sarebbe qualcosa come tutte le zanzare della Terra o di ogni auto in un ciclo di produzione di un determinato modello.
Che si sceglie è basato su un insieme di dati. La differenza sarà solitamente di piccole dimensioni, ma il risultato di “STDEV.P” la funzione sarà sempre minore che è il risultato di “STDEV.S” funzione per lo stesso set di dati. Si tratta di un approccio più conservativo per assumere c’è più variabilità dei dati.
Vediamo un Esempio
Per il nostro esempio, abbiamo due colonne (“Valori” e “Z-Score”)e tre “helper” cellule per memorizzare i risultati della “MEDIA” “VAR.S” e “STDEV.P” funzioni. I “Valori” colonna contiene dieci numeri casuali centrata intorno al 500, e la “Z-Score” colonna è il luogo dove si calcolerà il Punteggio Z utilizzando i risultati memorizzati nel ‘helper’ cellule.
Primo, si calcolerà la media dei valori utilizzando la “MEDIA”. Selezionare la cella in cui si desidera memorizzare il risultato della “MEDIA”.
Digitare la seguente formula e premere invio, oppure utilizzare le “Formule” del menu.
=MEDIA(E2:E13)
Per accedere alla funzione attraverso le “Formule” menu, selezionare “altre Funzioni” a discesa, selezionare la “Statistica” opzione e quindi fare clic su “MEDIA”.
Nella finestra Argomenti Funzione, selezionare tutte le celle in “Valori” colonna come input per il “Numero 1” il campo”. Non c’è bisogno di preoccuparsi per la “Numero 2” del campo.
Ora premere il tasto “OK”.
A quel punto, abbiamo bisogno di calcolare la deviazione standard dei valori tramite “STDEV.S” o “STDEV.P” la funzione. In questo esempio, si mostra come calcolare i due valori, a partire da “STDEV.S.” Selezionare la cella in cui il risultato viene memorizzato.
Per calcolare la deviazione standard utilizzando il “STDEV.S” la funzione, tipo in questa formula e premere Invio (o l’accesso attraverso le “Formule” menu).
=STDEV.S(E3:E12)
Per accedere alla funzione attraverso le “Formule” menu, selezionare “altre Funzioni” a discesa, selezionare la “Statistica”, scorrere verso il basso un po’, e quindi fare clic su “STDEV.S” di comando.
Nella finestra Argomenti Funzione, selezionare tutte le celle in “Valori” colonna come input per il “Numero 1” il campo”. Non c’è bisogno di preoccuparsi per la “Numero2” campo qui.
Ora premere il tasto “OK”.
A quel punto, calcoliamo la deviazione standard utilizzando il “STDEV.P” la funzione. Selezionare la cella in cui il risultato viene memorizzato.
Per calcolare la deviazione standard utilizzando il “STDEV.P” la funzione, tipo in questa formula e premere Invio (o l’accesso attraverso le “Formule” menu).
=STDEV.P(E3:E12)
Per accedere alla funzione attraverso le “Formule” menu, selezionare “altre Funzioni” a discesa, selezionare la “Statistica”, scorrere verso il basso un po’, e quindi fare clic su “STDEV.P” formula.
Nella finestra Argomenti Funzione, selezionare tutte le celle in “Valori” colonna come input per il “Numero 1” il campo”. Di nuovo, non avrete bisogno di preoccuparsi per la “Numero 2” del campo.
Ora premere il tasto “OK”.
Ora che abbiamo calcolato la media e la deviazione standard dei nostri dati, abbiamo tutti abbiamo bisogno di calcolare lo Z-Score. Possiamo utilizzare una semplice formula che fa riferimento a celle contenenti i risultati della “MEDIA” e “STDEV.S” o “STDEV.P” funzioni.
Selezionare la prima cella in “Z-Score” colonna. Useremo il risultato della “STDEV.S” la funzione di questo esempio, ma si potrebbe utilizzare anche il risultato “STDEV.P.”
Digitare la formula seguente e premere Invio:
=(E3-$G$3)/$H$3
In alternativa, è possibile utilizzare la seguente procedura per immettere la formula invece di scrivere:
- Fare clic nella cella F3 e digitare =(
- Selezionare la cella E3. (È possibile premere la freccia sinistra-una volta il tasto del mouse)
- Digitare il segno meno
- Selezionare la cella G3 quindi premere F4 per aggiungere i caratteri “$” per fare un ‘assoluto’ riferimento alla cella (in bicicletta attraverso il “G3” > “$G$3″ > “G$3″ > “$G3″ > “G3” se si continua a premere il tasto F4)
- Tipo )/
- Selezionare la cella H3 (o I3 se si utilizza “STDEV.P”) e premere il tasto F4 per aggiungere i due caratteri“$”.
- Premere Invio
Lo Z-Score è stato calcolato per il primo valore. È 0.15945 deviazioni standard sotto la media. Per verificare i risultati, si può moltiplicare la deviazione standard di questo risultato (6.271629 * -0.15945) e verificare che il risultato è pari alla differenza tra il valore e la media (499-500). Entrambi i risultati sono uguali, quindi il valore di senso.
Proviamo a calcolare lo Z-Score di il resto dei valori. Evidenziare l’intero “Z-Score” colonna inizia con la cella contenente la formula.
Premere Ctrl+D, che copia la formula nella cella superiore attraverso tutte le altre celle selezionate.
Ora la formula è stata “riempita” di tutte le cellule, e ognuno si fa sempre riferimento corretto “MEDIA” e “STDEV.S” o “STDEV.P” cellule a causa dei caratteri“$”. Se si verificano errori, tornare indietro e verificare che i caratteri “$” che sono inclusi nella formula che hai inserito.
Il calcolo di Z-Score senza l’utilizzo di ‘aiuto’ di Celle
Cellule Helper archivio di conseguenza, come quelli di memorizzare i risultati della “MEDIA” “VAR.S” e “STDEV.P” funzioni. Possono essere utili, ma non sono sempre necessarie. È possibile saltare il tutto per il calcolo di un Punteggio Z utilizzando il seguente generalizzata formule, invece.
Ecco uno usando il STDEV.S” la funzione:
=(Valore MEDIO(Valori))/VAR.S(Valori)
E uno che utilizza il “STEV.P” la funzione:
=(Valore MEDIO(Valori))/VAR.P(Valori)
Quando si entra in intervalli di celle per i “Valori” delle funzioni, assicurarsi di aggiungere i riferimenti assoluti (“$” che utilizza F4) in modo che quando si e ‘riempire-down’ non calcolando il valore medio o deviazione standard di un diverso intervallo di celle in ogni formula.
Se si dispone di un grande insieme di dati, può essere più efficiente di utilizzare cellule helper, perché non calcolare il risultato della “MEDIA” e “STDEV.S” o “STDEV.P” funzioni di ogni tempo, risparmio di risorse del processore e accelerare il tempo necessario per calcolare i risultati.
Inoltre, “$G$3” richiede un minor numero di byte per memorizzare e meno RAM per il carico di “MEDIA($E$3:$E$12).”. Questo è importante perché la standard a 32-bit versione di Excel è limitata a 2 gb di RAM (la versione a 64 bit non hanno limitazioni sulla quantità di RAM può essere utilizzato).