Hemos visto como calcular distintas maneras la varianza con Excel, bien con fórmulas bien con el análisis de datos, en esta entrada veremos cómo programar con Visual Basic las funciones de Excel y la herramienta de análisis de datos.
Empezaremos con las funciones de varianza que nos permitían calcular la varianza de una muestra o de una población, incluso de matrices que contuvieran datos no numéricos
Además de las fórmulas de Excel para calcular la varianza de una variable o la covarianza entre dos variables podemos utilizar la herramienta Análisis de datos para calcular las varianzas o las covarianzas de un conjunto de variables.
Para acceder al Análisis de datos debemos ir a la pestaña de Datos, si lo tenemos activado aparecerá en el grupo de Análisis.
En caso de no tenerlo activado deberemos activarlo a través del menú de opciones como se explica en esta entrada.
Además de calcular la varianza de una variable con Excel, podemos calcular también la covarianza que hay entre diversas variables.
La covarianza es una medida de dispersión, nos muestra promedio de los productos de las desviaciones para cada pareja de puntos de datos en dos conjuntos de datos:
De esta fórmula podemos deducir que la varianza es un caso concreto de la covarianza donde la variable x y la variable y es la misma. Sería como calcular la covarianza consigo mismo.
En las últimas versiones Excel encontramos 3 funciones para calcular covarianzas. La última COVAR() se mantiene en las versiones más actuales de Excel para que sean compatibles con versiones anteriores, donde era la única función de covarianza. Las otras dos funciones sirven para calcular la covarianza de dos variables de una población en el caso de COVARIANCE.P() y la covarianza de dos variables de una muestra en el caso de COVARIANZA.M()
Empezaremos con la función de covarianza para poblaciones, COVARIANCE.P()
Vemos que tenemos dos argumentos, cada uno se trata de una matriz de datos correspondiente a cada una de las dos variables, así pues en matriz1 introduciremos los datos de la primera variable y en matriz 2 los datos de la segunda variable.
También podemos acceder a la función COVARIANCE.P() a través de la pestaña de Fórmulas dentro de Mas funciones en el grupo de Estadísticas. Si seleccionamos la función COVARIANCE.P() se abre la siguiente ventana:
Vemos que tenemos que introducir dos matrices de datos una para cada variable. Una vez seleccionados nuestros datos pulsamos Aceptar y se calculará la covarianza poblacional.
La función COVAR() que se puede utilizar todavía y que era la única existente en versiones anteriores calcula también la covarianza poblacional, devuelve el mismo resultado que la actual COVARIANCE.P(). Su estructura es la siguiente:
Encontramos también sólo dos argumentos que se corresponden a dos matrices de datos una para cada variable.
En el caso de la función COVAR() no se puede acceder a través de la pestaña de funciones ya que una vez implementadas las otras dos funciones de covarianza permanece como un residuo para garantizar la compatibilidad de versiones de Excel, por lo tanto siempre que sea posible es mejor utilizar COVARIANCE.P() o COVARIANZA.M() en nuestros cálculos.
La última función de covarianza a explicar se trata de COVARIANZA.M(), vemos que su estructura es idéntica a las dos anteriores. Sus argumentos son dos matrices para poder colocar los datos de cada una de las dos variables de las que queremos conocer la covarianza.
Para esta función sí que podemos utilizar la pestaña de funciones y a partir del grupo de Más funciones dentro de las funciones Estadísticas seleccionar COVARIANZA.M() para abrir la siguiente ventana.
Como ya hemos ido viendo en anteriores funciones debemos introducir los conjuntos de datos de cada una de las variables en una de las matrices.
La diferencia entre COVARIANZA.M() y COVARIANCE.P() reside en el numerador que utiliza para calcular, en el caso de tener una muestra en lugar de toda la población se divide entre (n-1) como ya sucedía con las funciones de varianza, dando resultados algo superiores (a mayor muestra menor diferencia entre los dos valores).
También es fácil de ver que si introducimos tanto en la matriz 1 como en la matriz 2 el mismo conjunto de datos, el resultado que nos da se corresponde con el resultado obtenido con la varianza.
Espero que esta explicación haya sido de utilidad y estaré encantado de resolver cualquier duda o problema que se pueda plantear.
Si alguna vez hemos tenido que realizar algún análisis estadístico en Excel es posible que hayamos necesitado calcular una varianza.
La varianza es una medida de dispersión, nos muestra que dispersión hay entre cada uno de los datos de una variable y su valor medio.
La fórmula de la varianza es la siguiente:
Siendo n el número de observaciones, Xi el valor de la observación i y X el valor de la media.
Así pues la varianza es la suma del cuadrado de la diferencia entre el valor de cada observación y su media. Dividiendo el resultado total de la suma por el número de observaciones.
En Excel encontramos varias funciones para calcular la varianza: VAR() y VARP() que vienen de versiones anteriores, y por otro lado VAR.S(), VAR.P(), VARA() y VARPA() en las últimas versiones.
Píldoras de Excel 