agwebrunner.com

Un Z-Score és un valor estadístic que indica quantes desviacions estàndard passa a ser un valor concret de la mitjana de tot el conjunt de dades. Podeu utilitzar fórmules AVERAGE i STDEV.S o STDEV.P per calcular la mitjana i la desviació estàndard de les vostres dades i, a continuació, utilitzar aquests resultats per determinar la puntuació Z de cada valor.

Què és un Z-Score i què fan les funcions AVERAGE, STDEV.S i STDEV.P?

Un Z-Score és una forma senzilla de comparar valors de dos conjunts de dades diferents. Es defineix com el nombre de desviacions estàndard allunyades de la mitjana que es troba en un punt de dades. La fórmula general té aquest aspecte:

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

Aquí teniu un exemple per ajudar-vos a aclarir. Suposem que volíeu comparar els resultats de les proves de dos estudiants d’Àlgebra impartits per professors diferents. Sabeu que el primer estudiant va obtenir un 95% a l’examen final d’una classe i l’estudiant de l’altra classe va obtenir un 87%.

A primera vista, la nota del 95% és més impressionant, però, i si el professor de la segona classe fes un examen més difícil? Podeu calcular la puntuació Z de la puntuació de cada estudiant en funció de les puntuacions mitjanes de cada classe i de la desviació estàndard de les puntuacions de cada classe. La comparació de les puntuacions Z dels dos estudiants podria revelar que l’estudiant amb la puntuació del 87% va fer millor en comparació amb la resta de la seva classe que l’estudiant amb la puntuació del 98% en comparació amb la resta de la seva classe.

El primer valor estadístic que necessiteu és la "mitjana" i la funció "MITJANA" d'Excel calcula aquest valor. Simplement suma tots els valors d’un interval de cel·les i divideix aquesta suma pel nombre de cel·les que contenen valors numèrics (ignora les cel·les en blanc).

L’altre valor estadístic que necessitem és la ‘desviació estàndard’ i Excel té dues funcions diferents per calcular la desviació estàndard de maneres lleugerament diferents.

Les versions anteriors d'Excel només tenien la funció "STDEV", que calcula la desviació estàndard mentre es tracten les dades com una "mostra" d'una població. Excel 2010 es va dividir en dues funcions que calculen la desviació estàndard:

• STDEV.S: Aquesta funció és idèntica a la funció “STDEV” anterior. Calcula la desviació estàndard mentre tracta les dades com una "mostra" d'una població. Una mostra d’una població pot ser alguna cosa com els mosquits en concret recollits per a un projecte d’investigació o els cotxes que es van deixar de banda i s’utilitzaven per fer proves de seguretat contra accidents.
• STDEV.P: Aquesta funció calcula la desviació estàndard mentre tracta les dades com a tota la població. Tota una població seria com tots els mosquits de la Terra o tots els cotxes en una producció d’un model específic.

El que trieu es basa en el vostre conjunt de dades. La diferència sol ser petita, però el resultat de la funció “STDEV.P” sempre serà menor que el resultat de la funció “STDEV.S” per al mateix conjunt de dades. És un enfocament més conservador suposar que hi ha més variabilitat en les dades.

Vegem un exemple

Per al nostre exemple, tenim dues columnes ("Valors" i "Z-Score") i tres cel·les "auxiliars" per emmagatzemar els resultats de les funcions "AVERAGE", "STDEV.S" i "STDEV.P". La columna "Valors" conté deu números aleatoris centrats al voltant de 500 i la columna "Puntuació Z" és on calcularem la puntuació Z utilitzant els resultats emmagatzemats a les cel·les "ajudants".

En primer lloc, calcularem la mitjana dels valors mitjançant la funció "MITJANA". Seleccioneu la cel·la on desareu el resultat de la funció "MITJANA".

Escriviu la fórmula següent i premeu entrar -o- utilitzeu el menú "Fórmules".

= MITJÀ (E2: E13)

Per accedir a la funció a través del menú "Fórmules", seleccioneu el menú desplegable "Més funcions", seleccioneu l'opció "Estadística" i feu clic a "MITJÀ".

A la finestra Arguments de la funció, seleccioneu totes les cel·les de la columna "Valors" com a entrada per al camp "Número1". No us haureu de preocupar pel camp "Número2".

Ara premeu "D'acord".

A continuació, hem de calcular la desviació estàndard dels valors mitjançant la funció "STDEV.S" o "STDEV.P". En aquest exemple, us mostrarem com calcular els dos valors, començant per "STDEV.S". Seleccioneu la cel·la on s’emmagatzemarà el resultat.

Per calcular la desviació estàndard mitjançant la funció "STDEV.S", escriviu aquesta fórmula i premeu Retorn (o accediu-hi a través del menú "Fórmules").

= STDEV.S (E3: E12)

Per accedir a la funció a través del menú "Fórmules", seleccioneu el menú desplegable "Més funcions", seleccioneu l'opció "Estadística", desplaceu-vos una mica cap avall i feu clic a l'ordre "STDEV.S".

A la finestra Arguments de la funció, seleccioneu totes les cel·les de la columna "Valors" com a entrada per al camp "Número1". Tampoc no us haureu de preocupar pel camp "Número2".

Ara premeu "D'acord".

A continuació, calcularem la desviació estàndard mitjançant la funció "STDEV.P". Seleccioneu la cel·la on s’emmagatzemarà el resultat.

Per calcular la desviació estàndard mitjançant la funció "STDEV.P", escriviu aquesta fórmula i premeu Retorn (o accediu-hi a través del menú "Fórmules").

= STDEV.P (E3: E12)

Per accedir a la funció a través del menú "Fórmules", seleccioneu el menú desplegable "Més funcions", seleccioneu l'opció "Estadística", desplaceu-vos una mica cap avall i feu clic a la fórmula "STDEV.P".

A la finestra Arguments de la funció, seleccioneu totes les cel·les de la columna "Valors" com a entrada per al camp "Número1". Una vegada més, no us haureu de preocupar pel camp "Número2".

Ara premeu "D'acord".

Ara que hem calculat la mitjana i la desviació estàndard de les nostres dades, tenim tot el que necessitem per calcular el Z-Score. Podem utilitzar una fórmula senzilla que fa referència a les cel·les que contenen els resultats de les funcions “AVERAGE” i “STDEV.S” o “STDEV.P”.

Seleccioneu la primera cel·la de la columna "Puntuació Z". Utilitzarem el resultat de la funció "STDEV.S" per a aquest exemple, però també podeu utilitzar el resultat de "STDEV.P".

Escriviu la fórmula següent i premeu Retorn:

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

Com a alternativa, podeu fer els passos següents per introduir la fórmula en lloc d’escriure:

1. Feu clic a la cel·la F3 i escriviu =(
2. Seleccioneu la cel·la E3. (Podeu prémer el botó tecla de fletxa esquerra una vegada o utilitzeu el ratolí)
3. Escriviu el signe menys -
4. Seleccioneu la cel·la G3 i premeu F4 per afegir els caràcters "$" per fer una referència "absoluta" a la cel·la (passarà per "G3"> "$G$3 ″> “G$3″ > “$G3 ″> “G3” si continueu pressionant F4)
5. Tipus )/
6. Seleccioneu la cel·la H3 (o I3 si utilitzeu "STDEV.P") i premeu F4 per afegir els dos caràcters "$".
7. Premeu Retorn

La puntuació Z s'ha calculat per al primer valor. Es tracta de 0,15945 desviacions estàndard per sota de la mitjana. Per comprovar els resultats, podeu multiplicar la desviació estàndard per aquest resultat (6.271629 * -0.15945) i comprovar que el resultat sigui igual a la diferència entre el valor i la mitjana (499-500). Tots dos resultats són iguals, de manera que el valor té sentit.

Calculem les puntuacions Z de la resta de valors. Ressalteu tota la columna "Z-Score" començant per la cel·la que conté la fórmula.

Premeu Ctrl + D, que copia la fórmula a la cel·la superior cap avall a través de totes les altres cel·les seleccionades.

Ara, la fórmula s'ha omplert a totes les cel·les i cadascuna sempre farà referència a les cel·les "AVERAGE" i "STDEV.S" o "STDEV.P" correctes a causa dels caràcters "$". Si teniu errors, torneu enrere i assegureu-vos que els caràcters "$" estiguin inclosos a la fórmula que heu introduït.

Càlcul de la puntuació Z sense fer servir les cel·les "Ajudants"

Les cel·les auxiliars emmagatzemen un resultat, com les que emmagatzemen els resultats de les funcions "AVERAGE", "STDEV.S" i "STDEV.P". Poden ser útils però no sempre són necessaris. Podeu ometre-les del tot en calcular un Z-Score mitjançant les fórmules generalitzades següents.

En teniu una que utilitza la funció "STDEV.S":

= (Valor-AVERAGE (Valors)) / STDEV.S (Valors)

I un que utilitza la funció "STEV.P":

= (Valor-AVERAGE (Valors)) / STDEV.P (Valors)

Quan introduïu els intervals de cel·les dels "Valors" a les funcions, assegureu-vos d'afegir referències absolutes ("$" amb F4) de manera que quan "ompliu" no calculeu la desviació mitjana o estàndard d'un interval diferent de cèl·lules de cada fórmula.

Si teniu un conjunt de dades gran, pot ser més eficient utilitzar cel·les auxiliars perquè no calcula el resultat de les funcions “AVERAGE” i “STDEV.S” o “STDEV.P” cada cop, estalviant recursos del processador i accelerant el temps que es triga a calcular els resultats.

A més, "$ G $ 3" ​​necessita menys bytes per emmagatzemar i menys RAM per carregar que "AVERAGE ($ E $ 3: $ E $ 12)". Això és important perquè la versió estàndard de 32 bits d'Excel està limitada a 2 GB de RAM (la versió de 64 bits no té limitacions quant a la quantitat de RAM que es pot utilitzar).