Cómo calcular la puntuación z en Excel

Como saben, 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 esta es la primera vez que usa Excel, le recomendamos que tome cinco minutos para leer este tutorial de 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.

Las empresas a veces usan Z-Score para pronosticar y estimar la quiebra inminente. Además, es una gran métrica para determinar la posición financiera de una institución. Los investigadores también utilizan el puntaje Z 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 desviación media y estándar, primero deberá calcular la desviación promedio y estándar de su conjunto de datos. Aunque puede extraer la desviación media y estándar 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 compañía en papel. Ahora calculemos la puntuación Z de la calificación de los empleados.

Calcular 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 clave y presione Ingresar. La fórmula debe verse como la siguiente:

= Promedio (B2: B11)

Debería ver el valor medio o promedio del conjunto de datos en la celda que ingresó a 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 en unos pocos clics. 

Seleccione una celda en la columna "Desviación estándar", escriba = Stdev.PAG(, luego seleccione el primer valor En el rango, presione el clave de columna, Seleccione el último valor, ingrese el paréntesis de cierre, y presionar Ingresar. Si tiene dudas, la fórmula resultante debe ser similar a la siguiente:

= Stdev.P (B2: B11)

Calcule la puntuación z en Excel: Método 1

Excel tiene una función de estandarización que proporciona el puntaje Z de un conjunto de datos en una distribución. Seleccione la primera celda en la columna de puntaje Z y siga los pasos a continuación.

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

2. Pasar el ratón en el Estadístico opción y seleccione el ESTANDARIZAR.

Eso lanzará una nueva ventana de argumentos de función donde podrá calcular el puntaje Z de la distribución.

3. Ingrese la referencia celular del primer valor en el campo "x".

4. Ingrese la referencia celular del promedio aritmético en el campo "media" y presione el F4 en su teclado para bloquear la referencia de la celda.

5. Finalmente, ingrese la referencia de la celda de la desviación estándar en el campo "Standard_Dev" y presione F4 Para bloquear la referencia de la celda. La herramienta mostrará una vista previa del valor Z. Prensa DE ACUERDO para proceder.

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

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

Calcule la puntuación z en Excel: Método 2

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

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

La fórmula final debería parecer 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 puntaje Z para el primer valor en la celda seleccionada.

2. Pase el mouse hacia la esquina inferior derecha de la primera celda de puntuación Z y arrastre el más (+) icono por la columna.

Interpretando la puntuación Z

Su conjunto de datos probablemente contenga una combinación de puntajes Z negativos y positivos. Una puntuación z positiva indica que el valor/puntaje es mayor que el promedio medio del conjunto de datos. Una puntuación z negativa, por supuesto, dice lo contrario: el valor se encuentra por debajo del promedio medio. 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 es un punto de datos, mayor es su puntaje z. Revise su hoja de trabajo y se dará cuenta de que los valores pequeños tienen puntajes Z más bajos. Del mismo modo, los valores más pequeños que la media aritmética tendrán puntajes Z negativos.

En nuestra hoja de trabajo de muestra, por ejemplo, descubrirá que "Michael" tenía la calificación más alta (78) y la puntuación Z más alta (1.679659). "Dwight" y "Kevin", por otro lado, tenían las clasificaciones más bajas (34) y la puntuación Z más baja (-1.59047).

Conviértete en un experto en Excel: repositorio tutorial

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 para calcular la varianza, eliminar filas duplicadas en su conjunto de datos y cómo usar funciones de resumen en Excel para resumir los datos.