Un Z-Score 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 DESVEST.S o DESVEST.P para calcular la desviación media y estándar de sus datos y luego usar esos resultados para determinar el puntaje Z de cada valor.
Tabla de contenido
¿Qué es un Z-Score y qué hacen las funciones PROMEDIO, DESVEST.S y DESVEST.P?
Un Z-Score es una forma sencilla 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í:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Aquí hay un ejemplo para ayudar a aclarar. 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.
DESVEST.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 normalmente 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 “Puntuación 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 «Z-Score» es donde calcularemos el Z-Score 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 siguiente fórmula y presione enter -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í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 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 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 «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 utilizando 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ística», 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 «STDEV.S» para este ejemplo, pero también podría usar el resultado de «STDEV.P».
Escriba la siguiente fórmula y presione Enter:
=(E3-$G$3)/$H$3
Alternativamente, 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 y luego presione F4 para agregar los caracteres «$» para hacer una referencia «absoluta» a la celda (pasará por «G3″> «$ G $ 3 ″>» G $ 3 ″> «$ G3 ″>» G3 ”Si continúa presionando F4)
Tipo )/
Seleccione la celda H3 (o I3 si está usando «DESVEST.P») y presione F4 para agregar los dos caracteres «$».
Presione Entrar
El Z-Score 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 usar 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 necesarios. En su lugar, puede omitirlos por completo al calcular un puntaje Z 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 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 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 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 ninguna limitación sobre la cantidad de RAM que se puede usar).