Desviación estándar: Excel y Google Sheets

Este tutorial demuestra cómo utilizar el Función de desviación estándar de Excel en Excel para calcular la desviación estándar de toda una población.

Descripción general de la función DESVIACIÓN ESTÁNDAR

La función DESVIACIÓN ESTÁNDAR Calcula calcular la desviación estándar para una población completa.

Para usar la función de hoja de cálculo de Excel DESVIACIÓN ESTÁNDAR, seleccione una celda y escriba:

(Observe cómo aparecen las entradas de la fórmula)

Función DESVIACIÓN ESTÁNDAR Sintaxis y entradas:

1	= STDEV (número1, [número2],…)

números- Valores para obtener la varianza estándar

Cómo calcular la desviación estándar en Excel

Siempre que esté tratando con datos, querrá ejecutar algunas pruebas básicas para ayudarlo a comprenderlos. Por lo general, comenzará calculando la media, utilizando la función PROMEDIO de Excel <>.

Esto le da una idea de dónde está el "medio" de los datos. Y a partir de ahí, querrá ver qué tan dispersos están los datos alrededor de este punto medio. Aquí es donde entra la desviación estándar.

Excel le ofrece una serie de funciones para calcular la desviación estándar: STDEV, STDEV.P, STDEV.S y DSTDEV. Llegaremos a todos ellos, pero primero, aprendamos cuál es la desviación estándar es, exactamente.

¿Qué es la desviación estándar?

La desviación estándar le da una idea de qué tan lejos están sus puntos de datos de la media. Saque el siguiente conjunto de datos de puntajes de pruebas de 100:

1	48,49,50,51,52

La media de este conjunto de datos es 50 (sume todos los números y divida entre n, donde n es el número de valores en el rango).

Ahora mire este siguiente conjunto de datos:

1	10,25,50,75,90

La media de este conjunto de datos es además 50, pero los dos rangos cuentan una historia muy diferente. Si solo usó la media, podría pensar que los dos grupos eran aproximadamente iguales en sus aptitudes, y en promedio, lo son.

Pero en el primer grupo, tenemos 5 personas que obtuvieron puntajes muy similares, muy mediocres. Y en el segundo grupo, un par de jugadores de alto vuelo compensados ​​por un par de malos puntajes, con una persona en el medio. los propagar de las puntuaciones es muy diferente, lo que hace que su interpretación de los datos también sea muy diferente.

La desviación estándar es una medida de este margen.

Cómo se calcula la desviación estándar

Para comprender qué es la desviación estándar y cómo funciona, puede ser útil trabajar con un ejemplo a mano. De esa manera, sabrá lo que está sucediendo "bajo el capó" una vez que lleguemos a las funciones de Excel que puede usar.

Para calcular la desviación estándar, siga este proceso:

1) Calcule la media

Tomemos nuestro primer conjunto de datos anterior: 48,49,50,51,52

Ya conocemos la media (50), que he confirmado aquí con la función PROMEDIO de Excel <>:

1	= PROMEDIO (C4: C8)

2) Reste la media de cada valor en el conjunto de datos

Hice esto con la siguiente fórmula:

1	= C4- $ H $ 4

Nuestra media está en H4, y he "bloqueado" la referencia de celda colocando los signos de dólar antes de la columna y la fila (presionando F4). Esto significa que puedo copiar la fórmula en la columna sin que se actualice la referencia de celda.

El resultado:

Ahora, hagamos una pausa aquí por un segundo. Si echas un vistazo a la nueva columna, verás que los números aquí suman cero. La media de estos números también es cero.

Por supuesto, la dispersión de nuestros datos no puede ser cero; sabemos que hay alguna variación allí. Necesitamos una forma de representar esta variación, sin que el promedio resulte ser cero.

3) Cuadrar las diferencias

Podemos lograr esto cuadrando las diferencias. Entonces, agreguemos una nueva columna y elevemos al cuadrado los números en la columna D:

1

= D4 * D4

Esto se ve mejor. Ahora tenemos algo de variación, y la cantidad de variación está relacionada con qué tan lejos está cada puntaje de la media.

4) Calcule la varianza: la media de las diferencias al cuadrado

El siguiente paso es obtener el promedio de esas diferencias al cuadrado. En realidad, hay dos formas de hacer esto al calcular la desviación estándar.

• Si esta usando datos de población, simplemente toma la media (suma los valores y divide por n)
• Si esta usando Data de muestra, se suma la suma de los valores y se divide por n-1

Los datos de población significan que tiene el "conjunto completo" de sus datos, por ejemplo, tiene datos sobre cada estudiante en una clase determinada.

Los datos de muestra significa que no tiene todos sus datos, solo una muestra tomada de una población más grande. Por lo general, su objetivo con datos de muestra es hacer una estimación de cuál es el valor en la población más grande.

Una encuesta de opinión política es un buen ejemplo de datos de muestra: los investigadores encuestan, digamos, a 1.000 personas para tener una idea de lo que está pensando todo un país o estado.

Aquí no tenemos una muestra. Solo tenemos cinco miembros de la familia con mentalidad estadística que quieren calcular la desviación estándar de una prueba que todos tomaron. Tenemos todos los puntos de datos y no estamos haciendo una estimación de un grupo más grande de personas. Estos son datos de población, por lo que podemos tomar el promedio aquí:

1	= PROMEDIO (E4: E8)

Bien, tenemos 2. Esta puntuación se conoce como la "varianza" y es el punto base para muchas pruebas estadísticas, incluida la desviación estándar. Puede leer más sobre la varianza en su página principal: cómo calcular la varianza en Excel <>.

5) Obtenga la raíz cuadrada de la varianza

Elevamos nuestros números al cuadrado antes, lo que obviamente infla un poco los valores. Entonces, para volver a alinear la cifra con las diferencias reales de los puntajes con respecto a la media, necesitamos enraizar al cuadrado el resultado del paso 4:

1	= SQRT (H4)

Y tenemos nuestro resultado: la desviación estándar es 1.414

Debido a que hemos establecido la raíz cuadrada de nuestros números previamente al cuadrado, la desviación estándar se da en las mismas unidades que los datos originales. Entonces, nuestra desviación estándar aquí es 1.414 puntos de prueba.

Desviación estándar cuando los datos están más dispersos

Anteriormente teníamos un segundo rango de datos de ejemplo: 10,25,50,75,90

Solo por diversión, veamos qué sucede cuando calculamos la desviación estándar de estos datos:

Todas las fórmulas son exactamente las mismas que antes (tenga en cuenta que la media general sigue siendo 50).

Lo único que cambió fue la distribución de los puntajes en la columna C. Pero ahora, nuestra desviación estándar es mucho más alta, con 29.832 puntos de prueba.

Por supuesto, dado que solo tenemos 5 puntos de datos, es muy fácil ver que la distribución de las puntuaciones es diferente entre los dos conjuntos. Pero cuando tiene cientos o miles de puntos de datos, no puede saberlo simplemente escaneando rápidamente los datos. Y esa es exactamente la razón por la que usamos la desviación estándar.

Las funciones de Excel para calcular la desviación estándar

Ahora que sabe cómo funciona la desviación estándar, no necesita pasar por todo ese proceso para llegar a la desviación estándar. Puede usar una de las funciones integradas de Excel.

Excel tiene varias funciones para este propósito:

• PAG calcula la desviación estándar para los datos de población (usando el método exacto que usamos en el ejemplo anterior)
• S calcula la desviación estándar para los datos de muestra (utilizando el método n-1 que mencionamos anteriormente)
• STDEV es exactamente igual que STDEV.S. Esta es una función anterior que ha sido reemplazada por STDEV.S y STDEV.P.
• STDEVA es muy similar a STDEV.S, excepto que incluye celdas de texto y celdas booleanas (VERDADERO / FALSO) al realizar su cálculo.
• STDEVPA es muy similar a STDEV.P, excepto que incluye celdas de texto y celdas booleanas (VERDADERO / FALSO) al realizar su cálculo.

¡Vaya, hay muchas opciones aquí! No se deje intimidar: en la gran mayoría de los casos, utilizará STDEV.P o STDEV.S.

Repasemos cada uno de estos, comenzando con STDEV.P, ya que ese es el método con el que acabamos de trabajar.

La función STDEV.P de Excel

STDEV.P calcula la desviación estándar para los datos de población. Lo usas así:

1	= DESVEST.P (C4: C8)

Defina un argumento en DESVEST.P: el rango de datos para el que desea calcular la desviación estándar.

Este es el mismo ejemplo que analizamos paso a paso arriba cuando calculamos la desviación estándar a mano. Y como puede ver arriba, obtenemos exactamente el mismo resultado: 1.414.

Nota DESVEST.P ignora cualquier celda que contenga texto o valores booleanos (VERDADERO / FALSO). Si necesita incluirlos, use STDEVPA.

La función STDEV.S de Excel

STDEV.S calcula la desviación estándar de los datos de muestra. Úselo así:

1	= DESVEST.S (C4: C8)

Nuevamente, se necesita un argumento: el rango de datos para el que desea conocer la desviación estándar.

Antes de entrar en un ejemplo, analicemos la diferencia entre STDEV.S y STDEV.P.

Como ya hemos comentado, STDEV.S debe usarse en datos de muestra, cuando sus datos son una parte de un conjunto más grande. Supongamos ahora que, en nuestro ejemplo anterior, más personas han realizado la prueba. Queremos estimar la desviación estándar de todos los que realizaron la prueba, utilizando solo estos cinco puntajes. Ahora estamos usando datos de muestra.

Ahora, el cálculo difiere del paso (4) anterior, cuando calculamos la varianza: el promedio de la diferencia al cuadrado de cada puntaje con respecto a la media general.

En lugar de usar el método normal, sumar todos los valores y dividir por n, sumaríamos todos los valores y dividiríamos entre n-1:

1	= SUMA (E4: E8) / (CUENTA (E4: E8) -1)

En esta fórmula:

• SUM obtiene la suma de las diferencias al cuadrado
• COUNT devuelve nuestra n, de la cual restamos 1
• Luego simplemente dividimos nuestra suma por nuestro n-1

Esta vez, la media de las diferencias al cuadrado es 2,5 (puede recordar que anteriormente era 2, por lo que es un poco más alta).

Entonces, ¿por qué dividimos por n-1 en lugar de n cuando se trata de datos de muestra?

La respuesta es bastante compleja, y si solo está tratando de ejecutar sus números para comprender sus datos, no es algo de lo que realmente deba preocuparse. Solo asegúrese de usar STDEV.S para datos de muestra y STDEV.P para datos de población, y estará bien.

Si tiene mucha curiosidad por saber por qué, consulte la página principal sobre cómo calcular la varianza en Excel <>.

De acuerdo, ahora tenemos la varianza de la muestra, por lo que para obtener la desviación estándar de la muestra, solo obtendríamos la raíz cuadrada de la varianza:

1	= SQRT (H4)

Obtenemos 1.581.

STDEV.S realiza todos los cálculos anteriores por nosotros y devuelve la desviación estándar de la muestra en una sola celda. Así que veamos qué se le ocurre …

1	= DESVEST.S (C4: C8)

Sí, 1.581 de nuevo.

La función STDEV de Excel

La función STDEV de Excel funciona exactamente de la misma manera que STDEV.S, es decir, calcula la desviación estándar para una muestra de datos.

Lo usas de la misma manera:

1	= STDEV (C4: C8)

Nuevamente obtenemos el mismo resultado.

Nota IMPORTANTE: STDEV es una "función de compatibilidad", lo que básicamente significa que Microsoft se está deshaciendo de ella. Todavía funciona por ahora, por lo que las hojas de cálculo antiguas seguirán funcionando con normalidad. Pero en versiones futuras de Excel, Microsoft podría eliminarlo por completo, por lo que debe usar STDEV.S en lugar de STDEV siempre que sea posible.

La función STDEVA de Excel

STDEVA también se usa para calcular la desviación estándar de una muestra, pero tiene un par de diferencias importantes que debe conocer:

• Los valores VERDADEROS se cuentan como 1
• Los valores FALSOS se cuentan como 0
• Las cadenas de texto se cuentan como 0

Úselo de la siguiente manera:

1	= DESVESTA (C4: C8)

Cuatro amigos y familiares más han dado sus calificaciones en las pruebas. Estos se muestran en la columna C, y la columna D indica cómo STDEVA interpreta estos datos.

Debido a que estas celdas se interpretan como valores tan bajos, esto crea una distribución mucho más amplia entre nuestros datos de la que vimos antes, lo que ha aumentado considerablemente la desviación estándar, ahora en 26.246.

La función STDEVPA de Excel

DESVESTPA calcula la desviación estándar de una población de la misma forma que DESVEST.P. Sin embargo, también incluye valores booleanos y cadenas de texto en el cálculo, que se interpretan de la siguiente manera:

• Los valores VERDADEROS se cuentan como 1
• Los valores FALSOS se cuentan como 0
• Las cadenas de texto se cuentan como 0

Lo usas así:

1	= DESVESTPA (C4: C12)

Filtrar datos antes de calcular la desviación estándar

En el mundo real, no siempre tendrá los datos exactos que necesita en una tabla ordenada y agradable. A menudo, tendrá una hoja de cálculo grande llena de datos, que deberá filtrar antes de calcular la desviación estándar.

Puede hacer esto muy fácilmente con las funciones de la base de datos de Excel: DSTDEV (para muestras) y DSTDEVP (para poblaciones).

Estas funciones le permiten crear una tabla de criterios, en la que puede definir todos los filtros que necesita. Las funciones aplican estos filtros detrás de escena antes de devolver la desviación estándar. De esta manera, no es necesario tocar un autofiltro o extraer datos en una hoja separada: DSTDEV y SDTDEVP pueden hacer todo eso por usted.

Obtenga más información en la página principal de las funciones DSTDEV y DSTDEVP de Excel <>.

Función DESVIACIÓN ESTÁNDAR en Hojas de cálculo de Google

La función DESVIACIÓN ESTÁNDAR funciona exactamente igual en Google Sheets que en Excel: