Cómo calcular una puntuación 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 utilizar las fórmulas PROMEDIO y DESVEST.S o DESVEST.P para calcular la desviación media y estándar de sus datos y después utilizar esos resultados para establecer el puntaje Z de cada valor.

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

Un Z-Score es una forma simple de comparar valores de dos conjuntos de datos diferentes. Se establece 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í:

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

Aquí hay un ejemplo para ayudar a explicar. Supongamos que desea comparar los resultados de las pruebas de dos estudiantes de álgebra enseñados por diferentes maestros. Sabes que el primer estudiante obtuvo un 95% en el examen final en 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 maestro de la segunda clase diera un examen más difícil? Puede calcular la puntuación Z de la puntuación de cada estudiante 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 la puntuación del 87% lo hizo mejor en comparación con el resto de su clase que el estudiante con la puntuación del 98% en comparación con el resto de su 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 lo dividió 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 en particular recolectados para un proyecto de investigación o automóviles que se dejaron de lado y se usaron para 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 de la Tierra o cada automóvil en una producción de un modelo específico.

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

Veamos un ejemplo

Para nuestro ejemplo, tenemos dos columnas («Valores» y «Puntaje Z») y tres celdas «auxiliares» para guardar 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 «Puntaje Z» es donde calcularemos el Puntaje Z usando los resultados almacenados en las celdas «auxiliares».

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 próxima fórmula y presione ingresar -o bien- use el menú «Fórmulas».

=AVERAGE(E2:E13)

Para ingresar 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 después 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 usando la función “DESVEST.S” o “DESVEST.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 “DESVEST.S”, ingrese esta fórmula y presione Enter (o acceda a ella a través del menú “Fórmulas”).

=STDEV.S(E3:E12)

Para ingresar 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 después haga clic en el comando «DESVEST.S».

En la ventana Argumentos de función, seleccione todas las celdas de la columna «Valores» como entrada para el campo «Número1». Tampoco tiene que preocuparse por el campo «Número2» aquí.

Ahora presione «Aceptar».

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

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

= DESVEST.P (E3: E12)

Para ingresar 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 después haga clic en la fórmula «DESVEST.P».

En la ventana Argumentos de función, seleccione todas las celdas de la columna «Valores» como entrada para el campo «Número1». Nuevamente, 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 utilizar 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 de la columna «Z-Score». Usaremos el resultado de la función «DESVEST.S» para este ejemplo, pero además podría utilizar el resultado de «DESVEST.P».

Escriba la próxima fórmula y presione Enter:

=(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 el tecla de flecha izquierda una vez o utiliza el mouse)
3. Escriba el signo menos -
4. Seleccione la celda G3 y después presione F4 para agregar los caracteres «$» para hacer una referencia «absoluta» a la celda (pasará por «G3″> «PSGRAMOPS3 ″> “GPS3 «>»PSG3 ″> “G3” si continúa presionando F4)
5. Escribe )/
6. Seleccione la celda H3 (o I3 si está usando «STDEV.P») y presione F4 para agregar los dos caracteres «$».
7. Presione Entrar

La puntuación Z se ha calculado para el primer valor. Está 0,15945 desviaciones estándar por debajo de la media. Para verificar los resultados, puede multiplicar la desviación estándar por este resultado (6.271629 * -0.15945) y verificar que el resultado sea igual a la diferencia entre el valor y la media (499-500). Ambos resultados son iguales, por lo que el valor tiene sentido.

Calculemos las puntuaciones Z 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 ha sido ‘completada’ en todas las celdas, y cada una siempre hará referencia a las celdas correctas «PROMEDIO» y «DESVEST.S» o «DESVEST.P» debido a los caracteres «$». Si recibe errores, regrese y asegúrese de que los caracteres «$» estén incluidos en la fórmula que ingresó.

Calcular el Z-Score sin utilizar celdas ‘auxiliares’

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. En su lugar, puede omitirlos por completo al calcular un Z-Score usando las siguientes fórmulas generalizadas.

Aquí hay uno que utiliza la función «STDEV.S»:

=(Value-AVERAGE(Values))/STDEV.S(Values)

Y uno que utiliza la función «STEV.P»:

=(Value-AVERAGE(Values))/STDEV.P(Values)

Al ingresar los rangos de celda para los «Valores» en las funciones, asegúrese de agregar referencias absolutas («$» usando F4) para que cuando ‘complete’ no esté calculando la desviación promedio o estándar de un rango distinto de celdas en cada fórmula.

Si tiene un conjunto de datos grande, puede ser más eficiente utilizar celdas auxiliares debido a que no calcula el resultado de las funciones «PROMEDIO» y «DESVEST.S» o «DESVEST.P» cada vez, lo que ahorra recursos del procesador y acelerando el tiempo necesario para calcular los resultados.

Al mismo tiempo, “$ G $ 3” necesita menos bytes para guardar y menos RAM para cargar que “PROMEDIO ($ E $ 3: $ E $ 12)”. Esto es esencial debido a que 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 utilizar).