Calculando Covarianzas con Visual Basic

De la misma forma que hemos visto cómo calcular Varianzas con Visual Basic, también podemos calcular Covarianzas con Visual Basic, transcribiendo las funciones de Excel o bien el procedimiento seguido por el Análisis de Datos. Para el cálculo de las covarianzas en Visual Basic tenemos 3 funciones.

Si queremos calcular la covarianza de dos variables de una muestra utilizaremos COVARIANZA.M(), para utilizar esta variable con Visual Basic debemos introducir la siguiente instrucción:

ActiveCell.FormulaR1C1 = «=COVARIANCE.S(R[-31]C[-2]:R[-12]C[-2],R[-31]C[-1]:R[-12]C[-1])»

La función a escribir es =COVARIANCE.S() dentro debemos introducir los datos de cada una de las dos variables separadas por una coma. La S es de Sample, muestra en inglés.

Para la covarianza de una población la función a utilizar era COVARIANCE.P(), y nos devolvía la covarianza entre dos variables para toda la población. Esta función se puede reflejar en Visual Basic de la siguiente manera:

  ActiveCell.FormulaR1C1 = «=COVARIANCE.P(R[-30]C[-2]:R[-11]C[-2],R[-30]C[-1]:R[-11]C[-1])»

Debemos escribir =COVARIANCE.P() y una vez más dentro del paréntesis los datos de cada una de las dos variables separados por una coma. La P proviene de Population, población en inglés.

Otra función que se mantiene de versiones anteriores de Excel es COVAR() que nos devuelve también el valor de la covarianza de las dos variables en una población. En Visual Basic esta función se refleja así:

ActiveCell.FormulaR1C1 = «=COVAR(R[-29]C[-2]:R[-10]C[-2],R[-29]C[-1]:R[-10]C[-1])»

Debemos escribir =COVAR() y en el interior del paréntesis los valores de las dos variables separados por una coma.

Como ya vimos, para calcular la covarianza se puede utilizar también la herramienta Análisis de Datos. Este análisis de la covarianza mediante Análisis de Datos se puede reproducir también en Visual Basic. Para ello debemos utilizar la siguiente estructura:

     Application.Run «ATPVBAEN.XLAM!Mcovar», ActiveSheet.Range(«$A$32:$E$51»), _

        ActiveSheet.Range(«$W$47»), «C», False

Vemos que se trata de una aplicación. Debemos tener activado el Análisis de datos en VBA, para poder utilizar esta aplicación en Visual Basic, se activa siguiendo el mismo proceso que para activar la Herramienta de análisis de datos para Excel, o el Solver.

Vemos que la aplicación a utilizar es Mcovar, matríz de covarianzas.

Application.Run «ATPVBAEN.XLAM!Mcovar»,

A continuación tenemos el rango de celdas de donde recoge los valores de las variables de las que queremos conocer las distintas covarianzas entre ellas:

ActiveSheet.Range(«$A$32:$E$51»),

El siguiente fragmento recoge el lugar de salida de la tabla de covarianzas. En este caso es la hoja activa en la celda W47. Pero podemos querer que los datos se coloquen en otra hoja:

     Application.Run «ATPVBAEN.XLAM!Mcovar», ActiveSheet.Range(«$A$32:$E$51»), _

        “”, «C», False

Una hoja nueva sin nombre prefijado en este caso. ,””,

O en un libro nuevo:

     Application.Run «ATPVBAEN.XLAM!Mcovar», ActiveSheet.Range(«$A$32:$E$51»), ,_

       «C», False

En este caso no debemos colocar nada entre las comas: , ,

El siguiente argumento nos indica si las variables están ordenadas por filas o columnas. Si cada columna representa una variable deberemos colocar una “C” de Column, columna en inglés. En cambio si cada fila representa una variable deberemos colocar una “R” de Row, fila en inglés.

Por último debemos indicar si la primera columna (o fila) contiene títulos. En caso que los contenga colocaremos TRUE, y si no los tiene FALSE.

Una vez hayamos rellenado todas las opciones podemos ejecutar la aplicación y obtendremos nuestra matríz de covarianzas. Estas covarianzas se corresponden a covarianzas poblacionales. En la diagonal principal donde calculamos la covarianza de cada variable con ella misma encontramos la varianza poblacional de esa variable.

Calculando varianzas y covarianzas utilizando el Análisis de datos

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.

Calculando covarianzas con Excel

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.