Un Z-Score es un valor estadístico que le dice cuántas desviaciones estándar tiene un valor particular de la media del conjunto de datos. Puede usar las fórmulas PROMEDIO y DESVEST.S o DESVEST.P para calcular la media y la desviación estándar de sus datos, y luego usar esos resultados para determinar el puntaje Z para cada valor.
¿Qué es un Z-Score y para qué sirven las funciones PROMEDIO, DESVEST.S y DESVEST.P?
Un Z-Score es una forma sencilla de comparar los valores de dos conjuntos de datos diferentes. Se define como el número de desviaciones estándar de la media de un punto de datos. La fórmula general se ve así:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Aquí hay un ejemplo para ayudar a aclarar. Suponga que desea comparar los puntajes de las pruebas de dos estudiantes de álgebra enseñados por diferentes maestros. Sabes que el primer estudiante obtuvo el 95% en el examen final en una clase y el estudiante de la otra clase obtuvo el 87%.
A primera vista, la nota del 95% es más impresionante, pero ¿y si el maestro de segunda clase diera una prueba 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 al estudiante con una puntuación del 87% le fue mejor en comparación con el resto de su clase que al estudiante con una puntuación del 98% en comparación con el resto de su clase.
El primer valor estadístico que necesita es el «promedio» y la función «PROMEDIO» de Excel calcula este valor. Simplemente suma todos los valores en un rango de celdas y divide esa suma por el número de celdas que tienen valores numéricos (ignora las celdas vacías).
El otro valor estadístico que necesitamos es «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 ha dividido en dos funciones que calculan la desviación estándar:
- STDEV.S: Esta función es idéntica a la función «STDEV» anterior. 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 como mosquitos particulares recolectados para un proyecto de investigación o automóviles que se han dejado de lado y se han utilizado para pruebas de seguridad en caso de choque.
- STDEV.P: Esta función calcula la desviación estándar mientras trata los datos como toda la población. Una población entera sería algo así como cada mosquito en 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 generalmente 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 asumir 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 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 «Puntaje Z» es donde calcularemos el Puntaje Z usando los resultados almacenados en las celdas de «ayuda».
Primero, calcularemos el promedio 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 Entrar -o- use el menú «Fórmulas».
=AVERAGE(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ísticas», 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 tiene que 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 «STDEV.S» o «STDEV.P». En este ejemplo, le mostraremos cómo calcular los dos 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 acceder a la función a través del menú “Fórmulas”, seleccione el menú desplegable “Más funciones”, seleccione la opción “Estadísticas”, desplácese un poco hacia abajo, luego 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».
Luego, 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 acceder a la función a través del menú “Fórmulas”, seleccione el menú desplegable “Más funciones”, seleccione la opción “Estadísticas”, desplácese un poco hacia abajo y luego 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 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 de la columna «Z-Score». Usaremos el resultado de la función «DESVEST.S» para este ejemplo, pero también puede usar el resultado de «DESVEST.P. «
Escriba la siguiente fórmula y presione Entrar:
=(E3-$G$3)/$H$3
También puede utilizar los siguientes pasos para ingresar la fórmula en lugar de escribir:
- Haga clic en la celda F3 y escriba
=( - Seleccione la celda E3. (Puede presionar la tecla de flecha izquierda una vez o usar el mouse)
- Escriba el signo menos
- - Seleccione la celda G3, luego presione F4 para agregar los caracteres «$» para hacer una referencia «absoluta» a la celda (se desplazará «G3″> «PSgramoPS3 ″> «GPS3 ″> «PSG3 ″> “G3” si sigues presionando F4)
- Pegar
)/ - Seleccione la celda H3 (o I3 si está usando «DESVEST.P») y presione F4 para agregar los dos caracteres «$».
- presione enter
Se calculó el Z-Score para el primer valor. Es una desviación estándar de 0,15945 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). Los dos 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 de la celda superior a todas las demás celdas seleccionadas.
Ahora la fórmula se ha «llenado» en todas las celdas, y cada una siempre se referirá a las celdas «PROMEDIO» y «DESVEST.S» o «DESVEST.P» correctas debido a los caracteres «$». Si obtiene algún error, regrese y asegúrese de que los caracteres «$» estén incluidos en la fórmula que ingresó.
Calcule el Z-Score sin usar las 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. Puede ignorarlos por completo al calcular un Z-Score utilizando las siguientes fórmulas generalizadas.
Aquí hay uno que usa la función «STDEV.S»:
=(Value-AVERAGE(Values))/STDEV.S(Values)
Y uno que usa la función «STEV.P»:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Cuando ingrese rangos de celdas para «Valores» en funciones, asegúrese de agregar referencias absolutas («$» usando F4) para que cuando «complete» 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, lo que ahorra recursos de CPU y velocidad. el tiempo necesario para calcular los resultados.
Además, «$ G $ 3» necesita 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 limitación en la cantidad de RAM que se puede usar).