Cómo calcular un puntaje Z usando Microsoft Excel

Una puntuación Z es un valor estadístico que le indica cuántas desviaciones estándar tiene un valor en particular respecto a la media de todo el conjunto de datos. Puede usar las fórmulas AVERAGE y STDEV.S o STDEV.P para calcular la media y la desviación estándar de sus datos y luego usar esos resultados para determinar el puntaje Z de cada valor.

¿Qué es un puntaje Z y qué hacen las funciones AVERAGE, STDEV.S y STDEV.P??

Una puntuación Z es una forma sencilla de comparar valores de dos conjuntos de datos diferentes. Se define como el número de desviaciones estándar fuera de la media en 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. Digamos que desea comparar los resultados de las pruebas de dos estudiantes de Álgebra impartidos por diferentes maestros. Usted sabe que el primer estudiante obtuvo un 95% en el examen final en una clase y el estudiante en la otra clase obtuvo un 87%..

A primera vista, la calificación del 95% es más impresionante, pero ¿y si el profesor de la segunda clase dio un examen más difícil? Podría calcular el puntaje Z de la puntuación de cada estudiante basándose en las puntuaciones promedio de cada clase y la desviación estándar de las puntuaciones en cada clase. La comparación de los puntajes Z de los dos estudiantes podría revelar que el estudiante con el 87% obtuvo mejores resultados en comparación con el resto de su clase que el estudiante con el 98% que lo hizo 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 maneras ligeramente diferentes.

Las versiones anteriores de Excel solo tenían la función "STDEV", que calcula la desviación estándar al tratar 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 los autos que fueron reservados y utilizados para las pruebas de seguridad en caso de choque..
STDEV.P: Esta función calcula la desviación estándar al tratar los datos como la población total. Una población completa sería algo así como todos los mosquitos 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 "STDEV.P" siempre será menor que el resultado de la función "STDEV.S" para el mismo conjunto de datos. Es un enfoque más conservador para 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 “de ayuda” para almacenar los resultados de las funciones “PROMEDIO”, “STDEV.S” y “STDEV.P”. La columna "Valores" contiene diez números aleatorios centrados alrededor de 500, y la columna "Puntuación Z" es donde calcularemos la Puntuación Z utilizando los resultados almacenados en las celdas de "ayudante".

Primero, calcularemos la media de los valores utilizando 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- usar el menú "Fórmulas".

= MEDIA (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" y luego haga clic en "PROMEDIO".

En la ventana Argumentos de función, seleccione todas las celdas en la columna "Valores" como la entrada para el campo "Número1". No tienes que preocuparte por el campo "Número2".

Ahora presiona "OK".

A continuación, debemos calcular la desviación estándar de los valores utilizando la función “STDEV.S” o “STDEV.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 utilizando la función "STDEV.S", escriba esta fórmula y presione Intro (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 hacia abajo un poco y luego haga clic en el comando "STDEV.S".

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

Ahora presiona "OK".

A continuación, calcularemos la desviación estándar utilizando la función “STDEV.P”. Selecciona la celda donde se guardará el resultado..

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

= STDEV.P (E3: E12)

En la ventana Argumentos de función, seleccione todas las celdas en la columna "Valores" como la entrada para el campo "Número1". De nuevo, no tendrá que preocuparse por el campo "Número2".

Ahora presiona "OK".

Ahora que hemos calculado la media y la desviación estándar de nuestros datos, tenemos todo lo que necesitamos para calcular el puntaje Z. Podemos usar una fórmula simple que hace referencia a las celdas que contienen los resultados de las funciones “AVERAGE” y “STDEV.S” o “STDEV.P”.

Seleccione la primera celda en la columna "Puntuación Z". 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 seguir los siguientes pasos para ingresar la fórmula en lugar de escribir:

Haga clic en la celda F3 y escriba = (
Selecciona la celda E3. (Puede presionar el 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"> "PSsolPS3 ">" GPS3 ">"PSG3 ">" G3 "si continúa presionando F4)
Tipo ) /
Seleccione la celda H3 (o I3 si está usando “STDEV.P”) y presione F4 para agregar los dos caracteres “$”.
Presiona enter

El puntaje Z se ha calculado para el primer valor. Es 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 'Puntuación-Z' que comienza 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 se ha "rellenado" para todas las celdas, y cada una siempre hará referencia a las celdas correctas "AVERAGE" y "STDEV.S" o "STDEV.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ó.

Cálculo del puntaje Z sin usar células 'ayudantes'

Las celdas auxiliares almacenan un resultado, como los que almacenan los resultados de las funciones “PROMEDIO”, “STDEV.S” y “STDEV.P”. Pueden ser útiles pero no siempre son necesarios. Puede omitirlos por completo al calcular un puntaje Z utilizando las siguientes fórmulas generalizadas, en lugar de.

Aquí hay uno que usa la función "STDEV.S":

= (Valor-PROMEDIO (Valores)) / STDEV.S (Valores)

Y uno usando la función “STEV.P”:

= (Valor-PROMEDIO (Valores)) / STDEV.P (Valores)

Al ingresar los rangos de celdas para los "Valores" en las funciones, asegúrese de agregar referencias absolutas ("$" con F4) para que cuando complete el proceso no esté calculando el promedio o la desviación estándar de un rango diferente. de celulas en cada formula.

Si tiene un conjunto de datos grande, puede ser más eficiente usar celdas auxiliares porque no calcula el resultado de las funciones "AVERAGE" y "STDEV.S" o "STDEV.P" cada vez, ahorrando recursos del procesador y Acelerando el tiempo que lleva calcular los resultados..

Además, "$ G $ 3" requiere menos bytes para almacenar y menos RAM para cargar que "AVERAGE ($ E $ 3: $ E $ 12)". Esto es importante porque la versión estándar de Excel de 32 bits 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).