¿Cómo calcular la puntuación Z en Excel?

¿Qué es la puntuación Z o la puntuación estándar?

La puntuación estándar o la puntuación z es una forma de calcular cuánto se desvía la puntuación real de la media en unidades de desviación estándar. También nos permite comparar dos muestras de poblaciones diferentes. La puntuación z puede ser negativa o positiva. La puntuación z negativa significa que el valor está por debajo del valor aritmético y la puntuación z positiva significa que nuestro valor está por encima del valor aritmético.

La puntuación z se calcula restando la media del conjunto de datos del punto de datos que queremos calcular y dividiéndola por la desviación típica del conjunto de datos.

¿Para qué sirve la puntuación Z en nuestra vida cotidiana?

No todo el mundo es estadístico o matemático, pero todo el mundo trata con conjuntos de datos casi a diario. Ya sea su tiempo de pantalla, el examen final, la clase o cuánto gastó en ropa el año pasado. Eso significa que también utilizamos medidas estadísticas como la media o la desviación.

Pero, ¿dónde utilizar la puntuación z?

1. Podemos utilizarla al comparar dos conjuntos de datos con distribuciones diferentes. Por ejemplo:

Tenemos dos chefs expertos en el arte de hacer soufflé. Digamos que el nombre de nuestro primer chef es Sasha y el del otro es David. David participó en un concurso de cocina y obtiene una puntuación de 472 sobre 500. Sasha también participa en este tipo de concursos y obtiene la puntuación de 82 sobre 100. Ahora tenemos que decidir quién tuvo más éxito. ¿Pero cómo? La puntuación de David es superior a la de Sasha en valor, pero no podemos compararlos así. En estas situaciones, convertimos las puntuaciones brutas en puntuaciones z. Así podemos calcular quién lo hizo mejor en el concurso.

En el concurso en el que compite David, la media fue de 392 y la desviación típica de 100. Para calcular la puntuación estándar restamos 392, la media, de la puntuación de David 472 y la dividimos por la desviación estándar. La ecuación correcta para ello es «472 -392 /100″ que es +0,08. La puntuación de David está 0, 08 desviación típica por encima de la media.

En el concurso de Sasha, la media fue de 52 y la desviación típica de 30. Ahora aplicamos la misma fórmula que utilizamos para el concurso de David. Restamos la media de la puntuación y la dividimos entre la desviación típica. La ecuación para ello es » 82 -52 /30 «. La puntuación z de Sasha es +1,0. Significa que Sasha está 1,0 desviación típica por encima de la media.

Ahora podemos comparar fácilmente la puntuación de David y Sasha. David estaba 0,8 desviación estándar por encima y Sasha está 1,0 desviación estándar por encima de la media. Esto significa que Sasha es mejor pastelera de soufflé que David; esto es lo que nos dicen las estadísticas. Pero quizá no deberíamos decidir sin probar a los dos.

«Otro ámbito en el que se utiliza la puntuación estándar es la medición del crecimiento de los niños de todo el mundo. Se utiliza para calcular el peso y la talla para la edad o la talla para el peso. A los niños que se sitúan por debajo de la desviación estándar -2 o -3 se les diagnostica malnutrición^«.1

Consejo: Añada siempre menos o más a las puntuaciones z e intente convertirlas en números de dos cifras después de 0,x.Y siempre debe añadir el 0 antes de la coma si el valor es inferior a 1. Por ejemplo, 0, 8798 por encima de la media debe convertirse en +0, 88.

2. Dos comparan la frecuencia de una puntuación

Mirando la escala z que acabamos de hacer puedo determinar cuántos de los concursantes obtienen la puntuación de Sasha o David. Mirando la escala z me dice que la gente que obtuvo la puntuación de David es más que la gente que obtuvo la puntuación de Sasha. Porque la mayoría de los datos se reúnen alrededor de 1 desviación estándar por debajo o por encima de la media.

3. Nos permite adivinar la puntuación

Observando la escala z podemos determinar si es probable que Sasha o David saquen sus puntuaciones. Podemos utilizar esto en mediciones hipotéticas.

«¿Pero cómo nos ayudó a compararlos la conversión de datos brutos a puntuaciones z? Es porque la distribución de puntuaciones z siempre tiene una media de 0 y una desviación típica de 1,00. Incluso si la distribución original no es normal, la media es 0 y la desviación típica es 1,00. Esto nos ayuda a estandarizar y comparar dos conjuntos de datos diferentes, incluso si miden de forma distinta como en nuestro ejemplo.

La mayoría de los datos de una distribución de puntuaciones z están entre por debajo y por encima de 1,00 desviaciones estándar de la media. Hay menos datos que estén entre 2 desviaciones estándar por encima y por debajo de la media y es muy raro encontrar datos superiores a más y menos 3 desviaciones estándar^«.2

¿Por qué necesitamos la puntuación Z?

Como podemos deducir de los ejemplos anteriores, necesitamos la puntuación z para comparar dos conjuntos de datos diferentes normalizando los datos de cualquier tipo de medición.

Convertir sus datos a la puntuación z es básicamente reescalar y estandarizar sus datos. Por eso también se denomina puntuación estándar.

Podemos imaginar una puntuación z como un cortador de galletas. Puede cortar masa para galletas o espuma de poliestireno, pero ambos seguirán pareciéndose al hombre de jengibre.

¿Cómo calcular matemáticamente la puntuación Z?

La puntuación Z o puntuación estándar se calcula mediante el punto de datos menos el hombre dividido por la desviación estándar. Pero, ¿qué significan estos términos?

Cuando recopilamos información individual sobre algo, llamamos datos a todo el grupo, conjunto de datos o población. En este artículo todos los datos son numéricos pero los datos pueden ser cualquier tipo de información en la vida real. Un solo dato puede llamarse punto de datos o individuo y me referiré a él de estas dos formas para que no se confunda.

La media es el «promedio» en estadística. Se calcula sumando todos los datos y dividiéndolos por el número de datos. Daré un ejemplo y le aconsejo encarecidamente que siga y calcule conmigo durante los mismos. Por ejemplo, digamos que tenemos curiosidad por saber cuántas horas pasamos frente a la pantalla cada día, así que anotamos nuestro tiempo diario frente a la pantalla durante una semana 2, 2, 5, 3, 7, 1. Sumamos todos estos números y el resultado es 21. Ahora tenemos 7 puntos de datos individuales así que dividimos 21 entre 7 y hallamos el resultado 3. Pasamos 3 horas de media frente a la pantalla cada semana.

Para obtener más información sobre la media y cómo calcularla en Excel puede consultar nuestro artículo Cómo utilizar la función MEDIA de Excel.

Otra cosa que tenemos que encontrar para determinar la puntuación z es la desviación típica. La desviación típica es un término estadístico para determinar la diferencia entre la media y los datos reales. Una desviación estándar pequeña significa que los datos sen consistentes y normales. Los puntos de datos están próximos entre sí. Una desviación típica alta significa que el conjunto de datos es incoherente, inesperado y, a veces, poco fiable. Significa que los individuos del conjunto de datos no están próximos sino dispersos por la línea numérica.

Para calcular la desviación típica restamos todos los números de la media y los elevamos al cuadrado para evitar obtener valores negativos. Sumamos estos números elevados al cuadrado y los dividimos por uno menos del número de individuos de nuestro conjunto de datos.

Para nuestro ejemplo del tiempo de pantalla podemos explicarlo así:

La media es 3, así que restaremos 3 a cada punto de datos. Pasamos 2 horas delante de la pantalla el lunes por lo que significa «2 -3 = -1». Ahora elevamos al cuadrado el resultado «- 1» que encontramos y llegamos al número 1.

Aplicaremos la misma fórmula para cada individuo de nuestro conjunto de datos. Luego sumaremos estos números y obtendremos el resultado de √30. Lo dividiremos entre uno menos del número de puntos de datos que es 6. Esto nos dará la conclusión de 0,9. Este es nuestro valor de desviación estándar.

Para obtener más información sobre la desviación típica y cómo calcularla en Excel puede visitar nuestro artículo Cómo calcular la desviación típica en Excel.

Ahora que tenemos la media y la desviación típica podemos calcular la puntuación z.

Siguiendo con el ejemplo del tiempo de pantalla calcularemos la puntuación z del lunes. Para ello debemos restar la media de nuestro punto de datos y dividirla por el valor de la desviación estándar. Para nosotros significa «2 -3 = -1″. Ahora dividimos -1 entre 0,9 que es nuestro valor de desviación estándar, » -1 /0,9 = -1,1 «. El resultado es -1,1. Significa que nuestro tiempo frente a la pantalla el lunes se aleja más de 1 y menos de 2 desviaciones estándar de la media.

¿Cómo calcular la puntuación Z en Excel?

Como hemos explicado en los párrafos anteriores la puntuación z se calcula mediante el punto de datos menos la media dividido por la desviación típica. Para calcular la puntuación z en excel primero tenemos que escribir nuestros datos en la columna uno debajo del otro.

Podemos calcular la puntuación z en excel utilizando fórmulas específicas. Lo bueno de utilizar fórmulas de excel es que podemos adaptar las fórmulas para grandes conjuntos de datos que. Lo que nos permite calcular de forma más rápida y sencilla. Aprendamos a hacerlo paso a paso.

Paso 1: Determine su conjunto de datos

Supongamos que estamos calculando los exámenes finales de los estudiantes de lengua y literatura inglesas y digamos que sus puntuaciones son: 72, 90, 87, 65, 50, 78, 82, 63, 55 y 80. Estos diez puntos de datos se escriben desde la columna A y la fila 2 hasta la columna A fila 11. Elegí escribir «Puntuaciones de las pruebas» en A1, «Puntuación Z» en B1 y «Puntuación Z» en C1 en nuestra hoja para ordenar las cosas fácilmente. He añadido dos columnas «Puntuación Z» porque le mostraré cómo calcularla tanto manual como automáticamente.

Consejo: Mientras escribe las fórmulas, la celda a la que hace referencia se resaltará, por lo que no tendrá que volver a comprobar todo lo que escribe.

Paso 2: Calcular la media

Puesto que utilizaremos la media para determinar la puntuación z de nuestro conjunto de datos, necesitamos calcular la media. Media significa promedio matemático en un grupo de datos. Matemáticamente significa sumar todos los números y dividirlos por 10, pero en Excel utilizamos una fórmula sencilla para calcularla rápidamente. Escribamos «Media» en una celda distinta de las columnas A, B o C. Puede ser cualquier fila y columna distinta de estas. Elijo F2 para este ejemplo. Haga clic en la celda en blanco de al lado, que es G2 para nosotros, y escriba la fórmula en el espacio de fórmulas encima de nuestra hoja para calcular la media:

=MEDIA(A2: A11)

Pulse Intro y ya tenemos la media. Es 72,2

Paso 3: Calcular la desviación típica

Después hemos calculado la desviación típica. La desviación típica es la medida que nos indica cuánto se alejan los datos individuales de la media. Matemáticamente la calculamos restando todos los números de la media y elevándolos al cuadrado para que todos los resultados sean positivos en lugar de negativos. Se sumarán todos estos números elevados al cuadrado entre sí y se dividirán por uno menos del número de individuos de nuestro conjunto de datos (que es 10 en este ejemplo). Excel también tiene una fórmula para eso, por supuesto.

Escribamos «Desviación estándar» en una celda. He elegido F3 para nuestra práctica. Vamos a la celda en blanco de al lado, que es G3, y escribimos nuestra fórmula en el espacio de fórmulas de encima de nuestra hoja.

Para los que utilizan las últimas versiones de excel la fórmula debería ser:

=STDEV.S(A2: A11)

Pero para las versiones anteriores, utilizamos la fórmula STDEV en lugar de la fórmula STDEV.S. Que es

=STDEV(A2:11)

Pulse e intro y la desviación típica es aproximadamente 13,55

Consejo: Si no está seguro de si está utilizando las últimas versiones o no, no se preocupe, Excel le indica que cambie la fórmula que utiliza por una fórmula actualizada.

PASO 4: Calcular la puntuación Z manualmente

Ahora que hemos calculado la media y la fórmula podemos empezar a calcular la puntuación z.

Podemos empezar a determinar la puntuación z de nuestro puño individual, 72 para nosotros. Restaremos la media del individuo y la dividiremos entre la desviación estándar.

Un rápido recordatorio de que nuestra media estaba en la celda G2 y el valor de la desviación estándar en la celda G3.

Así que lo que vamos a hacer es hacer clic en la celda B2 en blanco y escribir

=(A2- G2)/ G3

Y esto nos dará el resultado, -0,01476. La puntuación z de nuestro primer individuo es -0,01476.

Ahora podemos calcular las puntuaciones z de otros individuos introduciendo la misma fórmula, excepto que en lugar de A2 pondremos la celda para la que estamos calculando. Pero hay una forma más fácil de calcularlos todos.

Hacemos clic en la celda con la fórmula y bloqueamos la celda del valor de la desviación estándar y la celda del valor medio. Para ello utilizamos el símbolo del dólar ($). Ponemos un signo de dólar junto a la letra de la columna y el número de fila para G2 y G3. No bloqueamos la columna A2 porque tiene que cambiar para cada individuo que vayamos a calcular. Nuestra fórmula debería tener este aspecto:

=(A2:$G$2)/$G$3

Ahora que hemos bloqueado nuestras celdas podemos hacer clic en la esquina inferior derecha de la celda B2 y arrastrarla hasta B11. Ahora tenemos las puntuaciones z para todo nuestro conjunto de datos.

Paso 5: Fórmula ESTANDARIZADA para la puntuación Z

Esta fue la forma manual de calcular la puntuación z. También puede utilizar la fórmula STANDARDIZE para determinarla.

Digamos que utilizamos el mismo conjunto de datos. 72, 90, 87, 65, 50, 78, 82, 63, 55 y 80 desde la celda A2 hasta la celda A11. Nuestra media está en la celda G2 y el valor de la desviación típica en la celda G3.

Hemos creado dos columnas de «puntuación Z» al principio. Por lo tanto, cualquiera que sea la columna que haya rellenado, deberá rellenar la columna vacía de al lado. En nuestra práctica, es la columna con la letra C.

Hacemos clic en la celda C2 en blanco y escribimos la fórmula ESTANDARIZAR en el espacio de fórmulas de encima de nuestra hoja:

=ESTANDARIZAR (A2, G2, G3)

Pulsamos intro y el valor es el mismo que el de nuestra otra columna de puntuación z. Es -0,01476.

Podemos hacerlo para todo el conjunto de datos de la misma forma anterior. Bloqueamos la celda del valor medio y la celda del valor de la desviación estándar añadiendo el signo del dólar ($) junto a la letra de la columna y el número de la fila. De nuevo no bloquearemos la celda del punto de datos porque queremos que cambie para cada celda. Ahora nuestra fórmula debería tener este aspecto

=ESTANDARIZAR (A2,$G$2,$G$3)

Ahora que hemos bloqueado nuestras celdas podemos hacer clic en la esquina inferior derecha de la celda C2 y arrastrarla hacia abajo hasta C11. Ahora tenemos puntuaciones z para todo nuestro conjunto de datos.