Cómo calcular un puntaje Z usando Microsoft Excel

Una puntuación Z es un valor estadístico que le indica cuántas desviaciones estándar tiene un valor particular de la media de todo el conjunto de datos. Puede usar las fórmulas PROMEDIO y STDEV.S o STDEV.P para calcular la media y la desviación estándar de sus datos y luego usar esos resultados para determinar el Z-Score de cada valor.

¿Qué es un Z-Score y qué hacen las funciones PROMEDIO, DESVEST.S y DESVEST.P?

Un puntaje Z es una forma simple de comparar valores de dos conjuntos de datos diferentes. Se define como el número de desviaciones estándar de la media en la que se encuentra un punto de datos. La fórmula general se ve así:

 =(PuntoDatos-PROMEDIO(ConjuntoDatos))/DESVEST(ConjuntoDatos)

Aquí hay un ejemplo para ayudar a aclarar. Digamos que desea comparar los resultados de las pruebas de dos estudiantes de álgebra enseñados por diferentes maestros. Usted sabe que el primer estudiante obtuvo un 95 % en el examen final de una clase y el estudiante de la otra clase obtuvo un 87 %.

A primera vista, la calificación del 95 % es más impresionante, pero ¿y si el profesor de la segunda clase hiciera un examen más difícil? Puede calcular la puntuación Z de la puntuación de cada alumno en función de las puntuaciones medias de cada clase y la desviación estándar de las puntuaciones de cada clase. La comparación de las puntuaciones Z de los dos estudiantes podría revelar que el estudiante con una puntuación del 87 % obtuvo mejores resultados en comparación con el resto de la clase que el estudiante con una puntuación del 98 % en comparación con el resto de la clase.

El primer valor estadístico que necesita es la 'media' y la función "PROMEDIO" de Excel calcula ese valor. Simplemente suma todos los valores en un rango de celdas y divide esa suma por el número de celdas que contienen valores numéricos (ignora las celdas en blanco).

El otro valor estadístico que necesitamos es la 'desviación estándar' y Excel tiene dos funciones diferentes para calcular la desviación estándar de formas ligeramente diferentes.

Las versiones anteriores de Excel solo tenían la función "STDEV", que calcula la desviación estándar mientras trata los datos como una "muestra" de una población. Excel 2010 dividió eso en dos funciones que calculan la desviación estándar:

• STDEV.S: Esta función es idéntica a la función anterior “STDEV”. Calcula la desviación estándar mientras trata los datos como una "muestra" de una población. Una muestra de una población podría ser algo así como los mosquitos particulares recolectados para un proyecto de investigación o los automóviles que se apartaron y se usaron para las pruebas de seguridad en choques.
• STDEV.P: esta función calcula la desviación estándar mientras trata los datos como la población completa. Una población entera sería algo así como todos los mosquitos en la Tierra o cada automóvil en una serie de producción de un modelo específico.

El que elija se basa en su conjunto de datos. La diferencia suele ser pequeña, pero el resultado de la función "STDEV.P" siempre será menor que el resultado de la función "STDEV.S" para el mismo conjunto de datos. Es un enfoque más conservador asumir que hay más variabilidad en los datos.

Veamos un ejemplo

Para nuestro ejemplo, tenemos dos columnas ("Valores" y "Z-Score") y tres celdas "auxiliares" para almacenar los resultados de las funciones "PROMEDIO", "DESVEST.S" y "DESVEST.P". La columna "Valores" contiene diez números aleatorios centrados alrededor de 500, y la columna "Z-Score" es donde calcularemos el Z-Score usando los resultados almacenados en las celdas 'ayudantes'.

Primero, calcularemos la media de los valores usando la función “PROMEDIO”. Seleccione la celda donde almacenará el resultado de la función "PROMEDIO".

Escriba la siguiente fórmula y presione enter -o- use el menú "Fórmulas".

 =PROMEDIO(E2:E13)

Para acceder a la función a través del menú "Fórmulas", seleccione el menú desplegable "Más funciones", seleccione la opción "Estadística" y luego haga clic en "PROMEDIO".

En la ventana Argumentos de función, seleccione todas las celdas de la columna "Valores" como entrada para el campo "Número1". No necesita preocuparse por el campo "Número2".

Ahora presione "Aceptar".

A continuación, necesitamos calcular la desviación estándar de los valores utilizando la función "STDEV.S" o "STDEV.P". En este ejemplo, le mostraremos cómo calcular ambos valores, comenzando con "STDEV.S". Seleccione la celda donde se almacenará el resultado.

Para calcular la desviación estándar usando la función “STDEV.S”, ingrese esta fórmula y presione Enter (o acceda a ella a través del menú “Fórmulas”).

 =DESVEST.S(E3:E12)

Para acceder a la función a través del menú "Fórmulas", seleccione el menú desplegable "Más funciones", seleccione la opción "Estadística", desplácese un poco hacia abajo y luego haga clic en el comando "STDEV.S".

En la ventana Argumentos de función, seleccione todas las celdas de la columna "Valores" como entrada para el campo "Número1". Tampoco necesita preocuparse por el campo "Número2".

Ahora presione "Aceptar".

A continuación, calcularemos la desviación estándar usando la función “STDEV.P”. Seleccione la celda donde se almacenará el resultado.

Para calcular la desviación estándar usando la función “STDEV.P”, ingrese esta fórmula y presione Enter (o acceda a ella a través del menú “Fórmulas”).

=DESVEST.P(E3:E12)

Para acceder a la función a través del menú "Fórmulas", seleccione el menú desplegable "Más funciones", seleccione la opción "Estadística", desplácese un poco hacia abajo y luego haga clic en la fórmula "STDEV.P".

En la ventana Argumentos de función, seleccione todas las celdas de la columna "Valores" como entrada para el campo "Número1". Una vez más, no tendrá que preocuparse por el campo "Número2".

Ahora presione "Aceptar".

Ahora que hemos calculado la media y la desviación estándar de nuestros datos, tenemos todo lo que necesitamos para calcular el Z-Score. Podemos usar una fórmula simple que haga referencia a las celdas que contienen los resultados de las funciones "PROMEDIO" y "DESVEST.S" o "DESVEST.P".

Seleccione la primera celda en la columna "Z-Score". Usaremos el resultado de la función "STDEV.S" para este ejemplo, pero también podría usar el resultado de "STDEV.P".

Escriba la siguiente fórmula y presione Entrar:

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

Alternativamente, puede usar los siguientes pasos para ingresar la fórmula en lugar de escribir:

1. Haga clic en la celda F3 y escriba =(
2. Seleccione la celda E3. (Puede presionar la tecla de flecha izquierda una vez o usar el mouse)
3. Escriba el signo menos -
4. Seleccione la celda G3 y luego presione F4 para agregar los caracteres “$” para hacer una referencia 'absoluta' a la celda (se desplazará a través de “G3” > “ $ G $ 3″ > “G $ 3″ > “ $ G3″ > “G3” si continúa presionando F4 )
5. Tipo )/
6. Seleccione la celda H3 (o I3 si está usando "STDEV.P") y presione F4 para agregar los dos caracteres "$".
7. Presiona Entrar

El Z-Score ha sido calculado para el primer valor. Está 0,15945 desviaciones estándar por debajo de la media. Para comprobar los resultados, puedes multiplicar la desviación estándar por este resultado (6,271629 * -0,15945) y comprobar que el resultado es igual a la diferencia entre el valor y la media (499-500). Ambos resultados son iguales, por lo que el valor tiene sentido.

Calculemos los Z-Scores del resto de los valores. Resalte toda la columna 'Z-Score' comenzando con la celda que contiene la fórmula.

Presione Ctrl+D, que copia la fórmula en la celda superior hacia abajo a través de todas las demás celdas seleccionadas.

Ahora la fórmula se ha 'rellenado' en todas las celdas, y cada una siempre hará referencia a las celdas "PROMEDIO" y "DESVEST.S" o "DESVEST.P" correctas debido a los caracteres "$". Si obtiene errores, regrese y asegúrese de que los caracteres "$" estén incluidos en la fórmula que ingresó.

Cálculo del puntaje Z sin usar celdas 'ayudantes'

Las celdas auxiliares almacenan un resultado, como las que almacenan los resultados de las funciones "PROMEDIO", "DESVEST.S" y "DESVEST.P". Pueden ser útiles, pero no siempre son necesarios. Puede omitirlos por completo al calcular un Z-Score utilizando las siguientes fórmulas generalizadas, en su lugar.

Aquí hay uno que usa la función "STDEV.S":

 =(Valor-PROMEDIO(Valores))/DESVEST.S(Valores)

Y uno usando la función “STEV.P”:

 =(Valor-PROMEDIO(Valores))/STDEV.P(Valores)

Al ingresar los rangos de celdas para los "Valores" en las funciones, asegúrese de agregar referencias absolutas ("$" usando F4) para que cuando 'rellene' no esté calculando el promedio o la desviación estándar de un rango diferente de celdas en cada fórmula.

Si tiene un conjunto de datos grande, puede ser más eficiente usar celdas auxiliares porque no calcula el resultado de las funciones "PROMEDIO" y "DESVEST.S" o "DESVEST.P" cada vez, ahorrando recursos del procesador y acelerando el tiempo que se tarda en calcular los resultados.

Además, "$G$3" requiere menos bytes para almacenar y menos RAM para cargar que "PROMEDIO($E$3:$E$12)". Esto es importante porque la versión estándar de 32 bits de Excel está limitada a 2 GB de RAM (la versión de 64 bits no tiene limitaciones sobre la cantidad de RAM que se puede usar).