Cómo calcular el puntaje Z en Excel

Como sabe, hay muchos cálculos matemáticos que puede realizar con Microsoft Excel. En este tutorial, lo guiaremos a través de los pasos involucrados en el cálculo de Z-Score en Excel.

Si es la primera vez que usa Excel, le recomendamos que se tome cinco minutos para leer detenidamente este Tutorial para principiantes de Microsoft Excel. Aprenderá cómo funcionan las funciones esenciales, los atajos de navegación, la creación de libros de trabajo, el formateo de datos y todo lo que hay que saber sobre el uso del programa de hoja de cálculo como principiante.

Cuando haya terminado con eso, continúe con la siguiente sección para aprender a calcular la puntuación Z en Excel. Pero primero, expliquemos brevemente Z-Score, sus usos, por qué es posible que deba calcular uno y cómo hacerlo.

¿Qué es una puntuación Z?

Z-Score (también conocido como «Puntaje estándar») es una métrica que resalta la relación entre los valores en una distribución. Más precisamente, describe la posición de los valores en un conjunto de datos con respecto a su media y desviación estándar. Z-Score permite una medición y comparación precisas de valores en un conjunto de datos.

La fórmula matemática para calcular Z-Score es (x-µ) / σ; donde x = Valor de celda, µ = Media y σ = Desviación estándar.

En ocasiones, las empresas utilizan Z-Score para pronosticar y estimar una quiebra inminente. Además, es una gran métrica para determinar la posición financiera de una institución. Los investigadores también utilizan Z-Score para comparar las observaciones obtenidas de diferentes muestras o poblaciones.

Cómo calcular la puntuación Z en Excel

Dado que Z-Score es una función de la media y la desviación estándar, primero deberá calcular la desviación promedio y estándar de su conjunto de datos. Aunque puede extraer la desviación estándar y media en cualquier celda, creamos columnas dedicadas para «Media» y «Desviación estándar» en nuestra hoja de trabajo. También creamos una columna para «Z-Score».

Nuestro documento de muestra contiene las calificaciones de desempeño de 10 empleados en una empresa de papel. Ahora calculemos el Z-Score de la calificación de los empleados.

Calcular el promedio medio

Para calcular el promedio medio de su conjunto de datos, escriba = PROMEDIO (, Selecciona el primer valor en el conjunto de datos, presione el clave de columna, Selecciona el último valor dentro del rango del conjunto de datos, presione el paréntesis de cierre y presione Ingresar. La fórmula debería verse como la siguiente:

= PROMEDIO (B2: B11)

Debería ver el valor medio o promedio del conjunto de datos en la celda en la que ingresó la fórmula.

Calcular la desviación estándar

Excel también hace que sea bastante fácil calcular la desviación estándar de su conjunto de datos con unos pocos clics.

Seleccione una celda en la columna «Desviación estándar», escriba = DESVEST.P (, luego seleccione el primer valor en el rango, presione el clave de columna, seleccione el último valor, introduzca el paréntesis de cierrey presione Ingresar. Si tiene dudas, la fórmula resultante debería ser similar a la siguiente:

= DESVEST.P (B2: B11)

Calcular Z-Score en Excel: Método 1

Excel tiene una función ESTANDARIZAR que proporciona la puntuación Z de un conjunto de datos en una distribución. Seleccione la primera celda en la columna Z-Score y siga los pasos a continuación.

1. Ve a la Fórmulas pestaña y seleccione Más fórmulas.

2. Coloque el mouse sobre el Estadístico opción y seleccione la ESTANDARIZAR.

Eso lanzará una nueva ventana de Argumentos de función donde podrá calcular el Z-Score de la distribución.

3. Ingrese la referencia de celda del primer valor en el campo «X».

4. Ingrese la referencia de celda del promedio aritmético en el campo «Media» y presione la tecla F4 en su teclado para bloquear la referencia de celda.

5. Finalmente, ingrese la referencia de celda de la desviación estándar en el campo «Standard_dev» y presione F4 para bloquear la referencia de celda. La herramienta mostrará una vista previa del valor Z. prensa OK para proceder.

Para obtener el puntaje Z para otros valores, coloque el cursor en la esquina inferior derecha de la celda y arrastre el icono más (+) por la columna.

Excel copiará la fórmula en la columna y generará automáticamente el Z-Score para otros valores en las filas correspondientes.

Calcular Z-Score en Excel: Método 2

Como se mencionó anteriormente, puede obtener el puntaje Z de un punto de datos restando la media del conjunto de datos del punto de datos y dividiendo el resultado por la desviación estándar. Usando (x-µ) / σ, puede calcular el puntaje z en Excel ingresando estos valores manualmente.

1. Seleccione la primera celda de la columna Z-Score, escriba un signo igual (=) seguido de un paréntesis abiertoy seleccione el primer valor en la columna que contiene los conjuntos de datos cuyo Z-Score desea calcular. Luego, escriba un guión, seleccione la media aritmética, presione F4 para hacer la media absoluta / fija, y presione el cerrar paréntesis icono. Finalmente, presione el barra inclinada (/), seleccione la desviación estándar y presione F4 para bloquear la referencia de celda.

La fórmula final debería tener un aspecto similar a esto: = (B2- $ C $ 2) / $ D $ 2. prensa Ingresar para ejecutar la fórmula.

Tenga en cuenta que la fórmula solo calculará el Z-Score para el primer valor en la celda seleccionada.

2. Mueva el mouse hacia la esquina inferior derecha de la primera celda de Z-Score y arrastre el icono más (+) por la columna.

Interpretación de Z-Score

Es muy probable que su conjunto de datos contenga una combinación de puntuaciones Z negativas y positivas. Un puntaje Z positivo indica que el valor / puntaje es más alto que el promedio del conjunto de datos. Una puntuación Z negativa, por supuesto, dice lo contrario: el valor se encuentra por debajo de la media. Si la puntuación Z de un punto de datos es cero (0), es porque su valor es igual a la media aritmética.

Cuanto más grande sea un punto de datos, mayor será su Z-Score. Revise su hoja de trabajo y se dará cuenta de que los valores pequeños tienen puntuaciones Z más bajas. Del mismo modo, los valores más pequeños que la media aritmética tendrán puntuaciones Z negativas.

En nuestra hoja de trabajo de muestra, por ejemplo, descubrirá que «Michael» obtuvo la calificación más alta (78) y el puntaje Z más alto (1.679659). “Dwight” y “Kevin”, por otro lado, tuvieron las calificaciones más bajas (34) y la puntuación Z más baja (-1,59047).

Conviértase en un experto en Excel: repositorio de tutoriales

Ahora sabe cómo calcular la puntuación Z de un conjunto de datos. Deje un comentario a continuación si tiene preguntas u otros consejos útiles sobre el cálculo de Z-Score en Excel.

Recomendamos leer tutoriales relacionados con Excel en calcular la varianza, eliminar filas duplicadas en su conjunto de datos y cómo usar funciones de resumen en Excel para resumir los datos.