VARIANCE - Excel y Google Sheets

Este tutorial demuestra cómo utilizar el Función VARIANCE de Excel en Excel para estimar la varianza según una muestra determinada.

Descripción general de la función VARIANCE

La función VARIANCE Calcula la varianza estimada en función de una muestra determinada.

Para usar la función de hoja de cálculo de Excel VARIANCE, seleccione una celda y escriba:

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

Función VARIANCE Sintaxis y entradas:

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

números- Valores para obtener varianza

Cómo calcular la varianza en Excel

La varianza le dice cuán separados están los valores en un conjunto de datos de la media. Hablando matemáticamente, la varianza es el promedio de la diferencia al cuadrado de cada puntaje con respecto a la media (pero llegaremos a eso en breve).

Excel le ofrece una serie de funciones para calcular la varianza: VAR.S, VAR.P, VARA, VARPA y dos funciones más antiguas, VAR y VARP.

Antes de profundizar en estas funciones y aprender a usarlas, hablemos sobre la varianza y cómo se calcula.

¿Qué es la varianza?

Al analizar datos, un primer paso común es calcular la media. Por supuesto, esta es una estadística útil para calcular, pero no le brinda una imagen completa de lo que está sucediendo con sus datos.

Tome el siguiente conjunto de datos, que podría ser un grupo de resultados de pruebas puntuados sobre 100:

1	48,49,50,51,52

La media de este rango es 50 (sume los números y divida por n, donde n es el número de valores).

A continuación, tome el siguiente conjunto de resultados de la prueba:

1	10,25,50,75,90

La media de este rango es además 50, pero obviamente tenemos dos rangos de datos muy diferentes aquí.

Por sí misma, la media no puede decirle nada sobre cuán dispersos están los puntajes. No le dice si los valores están agrupados como en los primeros ejemplos, o muy separados como en el segundo. La variación puede ayudarte a aprender esto.

La varianza también se utiliza como punto de base para una variedad de procedimientos estadísticos más complejos.

Cómo se calcula la varianza

Trabajemos con un ejemplo básico y calculemos la varianza a mano. De esta manera, sabrá lo que sucede detrás de escena cuando realmente comience a poner en acción las funciones de variación de Excel.

Digamos que tenemos un conjunto de datos que representa tres naipes, un 4, un 6 y un 8.

Para calcular la varianza, siga este proceso:

1) Calcule la media

Primero, calculamos la media. Sabemos que nuestro rango de datos es 4, 6, 8, por lo que la media será:

1	(6 + 4 + 8) / 3 = 6

Lo he confirmado a continuación con la función PROMEDIO de Excel <>:

1	= PROMEDIO (C4: C6)

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

A continuación, restamos la media de cada uno de nuestros valores.

Hice esto con la siguiente fórmula:

1	= C4- $ H $ 4

La media se almacena en H4, así que solo resto eso de cada valor en la tabla. Los signos de dólar aquí simplemente "bloquean" esa referencia de celda a H4, de modo que cuando lo copie en la columna, permanezca igual.

Los resultados:

Tenemos:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

Necesitamos obtener el promedio de estas diferencias de la media, ¡pero la media de estos tres valores es cero! Por lo tanto, debemos enfatizar las diferencias, lo que hacemos al cuadrarlas.

3) Cuadrar las diferencias

Agreguemos una nueva columna y elevemos al cuadrado los números de la columna D:

1

= D4 * D4

Está bien, eso es mejor. Ahora que las diferencias no tienen un promedio de cero, podemos calcular la varianza.

4) Calcular el promedio de las diferencias al cuadrado

Aquí nos encontramos con una bifurcación en el camino. Hay dos formas de calcular la varianza, y la que usa depende del tipo de datos que tenga.

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

Los datos de población significan que tiene la totalidad de los datos que necesita, por ejemplo, si desea la edad promedio de los maestros en una escuela en particular y tiene los datos de edad para cada maestro en esa escuela, tiene datos de población.

Los datos de muestra significa que no tiene todos sus datos, solo una muestra tomada de una población más grande. Entonces, si desea conocer la edad promedio de los maestros en todo el país y solo tiene datos sobre los maestros en una escuela, tiene datos de muestra.

En nuestro ejemplo, tenemos datos de población. Solo estamos interesados ​​en nuestras tres tarjetas, esa es la población, y no hemos tomado una muestra de ellas. Entonces, podemos tomar el promedio de las diferencias al cuadrado de la manera normal:

1	= PROMEDIO (E4: E8)

Entonces la varianza de nuestra población es 2.666.

Si esto era datos de muestra (quizás hayamos sacado estas tres tarjetas de un conjunto más grande), calcularíamos el promedio de la siguiente manera:

1	Varianza de la muestra = (4 + 0 + 4) / (3-1)

O:

1	Varianza de la muestra = 8/2 = 4

¿Por qué dividir por n-1 con datos de muestra, en lugar de solo n?

La respuesta corta a esta pregunta es "Porque da la respuesta correcta". ¡Pero imagino que querrás un poco más que eso! Este es un tema complejo, por lo que solo daré una breve descripción aquí.

Piénselo así: si toma una muestra de datos de una población, esos valores tenderán a estar más cerca de la media del muestra de lo que son a la media del población.

Esto significa que si divide entre n, subestimará un poco la varianza de la población. Dividir por n-1 corrige esto un poco.

Con nuestro juego de tres cartas, estamos en un buen lugar para probar esta teoría. Debido a que solo hay tres tarjetas, hay una pequeña cantidad de muestras que posiblemente podamos tomar.

Tomemos muestras de dos cartas. Escogeremos una carta, la devolveremos, la barajaremos y luego escogeremos otra carta. Eso significa que hay nueve combinaciones de dos cartas que podemos elegir.

Con solo nueve muestras posibles, podemos calcular cada posible varianza muestral usando ambos métodos (dividir por n y dividir por n-1), tomar el promedio de ellas y ver cuál nos da la respuesta correcta.

En la siguiente tabla, he presentado todo. Cada fila de la tabla es una muestra diferente, y las columnas B y C muestran las dos tarjetas que se seleccionaron en cada muestra. Luego agregué dos columnas más: una en la que calculé la varianza de esa muestra de dos tarjetas dividiéndola por n, y otra en la que dividí entre n - 1.

Echar un vistazo:

A la derecha de la tabla, he mostrado los promedios de las columnas D y E.

El promedio de la columna D, al dividir por n, nos da una varianza de 1.333.

El promedio de la columna E, al dividir por n-1, nos da una varianza de 2.666.

Ya sabemos por nuestro ejemplo anterior que la varianza de la población es 2.666. Entonces, dividir por n-1 cuando se utilizan datos de muestra nos da estimaciones más precisas.

Las funciones de Excel para calcular la varianza

Ahora que ha visto un ejemplo de cómo se calcula la varianza, pasemos a las funciones de Excel.

Tienes varias opciones aquí:

• PAG devuelve la varianza de los datos de población (utilizando el método de división por n)
• S devuelve la varianza de los datos de muestra (se divide por n-1)
• VAR es una función anterior que funciona exactamente de la misma manera que VAR.S
• VARA es lo mismo que VAR.S, excepto que incluye celdas de texto y valores booleanos
• VARPA es lo mismo que VAR.P, excepto que incluye celdas de texto y valores booleanos

Repasemos estos uno por uno.

La función Excel VAR.P

VAR.P calcula la varianza de los datos de población (utilizando el método de división por n). Úselo así:

1	= VAR.P (C4: C6)

Solo define un argumento en VAR.P: el rango de datos para el que desea calcular la varianza. En nuestro caso, esos son los valores de las cartas en C4: C6.

Como puede ver arriba, VAR.P devuelve 2.666 para nuestro conjunto de tres cartas. Este es el mismo valor que calculamos a mano anteriormente.

Tenga en cuenta que VAR.P ignora por completo las celdas que contienen texto o valores booleanos (VERDADERO / FALSO). Si necesita incluirlos, utilice VARPA en su lugar.

La función Excel VAR.S

VAR.S calcula la varianza de los datos de la muestra (dividiendo por n-1). Lo usas así:

1	= VAR.S (C4: C6)

Nuevamente, solo hay un argumento: su rango de datos.

En este caso, VAR.S devuelve 4. Obtuvimos la misma cifra en el paso 4 cuando hicimos el cálculo manual anterior.

VAR.S ignora por completo las celdas que contienen texto o valores booleanos (VERDADERO / FALSO). Si necesita incluirlos, utilice VARA en su lugar.

La función Excel VAR

VAR es completamente equivalente a VAR.S: calcula las varianzas para datos de muestra (usando el método n-1). A continuación, le indicamos cómo utilizarlo:

1	= VAR (C4: C6)

VAR es una "función de compatibilidad". Esto significa que Microsoft está en proceso de eliminar esta función de Excel. Por el momento, todavía está disponible para su uso, pero debería usar VAR.S en su lugar, para que sus hojas de cálculo sigan siendo compatibles con futuras versiones de Excel.

La función Excel VARA

VARA también devuelve la varianza de los datos de muestra, pero tiene algunas diferencias clave con VAR y VAR.S. Es decir, incluye valores booleanos y de texto en su cálculo:

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

Así es como se usa:

1	= VARA (C4: C11)

Agregamos cinco filas más a la tabla: J, Q, K, VERDADERO y FALSO. La columna D muestra cómo VARA interpreta estos valores.

Debido a que ahora tenemos un nuevo lote de valores bajos en nuestra tabla, la varianza ha aumentado a 10.268.

La función Excel VARPA

VARPA calcula la varianza de los datos de población. Es similar a VAR.P, excepto que también incluye valores booleanos y cadenas de texto en el cálculo:

• 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	= VARPA (C4: C12)

Agregamos cinco filas más a la tabla: J, Q, K, VERDADERO y FALSO. La columna D muestra cómo VARPA interpreta estos valores.

Como resultado de agregar este grupo de valores más bajos a los datos, la varianza ha aumentado a 8,984.

Función VARIANCE en Google Sheets

La función CORREL funciona exactamente igual en Google Sheets que en Excel: