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
.
Para calcular la varianza de una población debíamos utilizar la función VAR.P(), dicha función de Excel adquiere la siguiente forma en Visual Basic:
ActiveCell.FormulaR1C1 = «=VAR.P(R[-29]C:R[-10]C)»
Vemos que debemos introducir =VAR.P() y a continuación el rango de la matriz de datos entre los paréntesis. Como veremos a lo largo de toda la entrada la función de Visual Basic coincide con el nombre de la función en Excel.
En versiones anteriores de Excel encontramos la función VARP() que realizaba la misma tarea, su programación en Visual Basic es la siguiente:
ActiveCell.FormulaR1C1 = «=VARP(R[-32]C:R[-13]C)»
Debemos escribir VARP() introduciendo la matriz de números entre los paréntesis
Si queremos calcular la varianza de una muestra debemos utilizar VAR.S(), para escribirlo en Visual Basic debemos programar una instrucción similar a la que vemos a continuación:
ActiveCell.FormulaR1C1 = «=VAR.S(R[-30]C:R[-11]C)»
La función a escribir es =VAR.S() y en el interior de los paréntesis debemos referenciar la matriz de datos de la que deseamos conocer la varianza.
Otra opción para la varianza de una muestra que se mantiene de versiones más antiguas de Excel es la función VAR(), su estructura en Visual Basic es la siguiente:
ActiveCell.FormulaR1C1 = «=VAR(R[-31]C:R[-12]C)»
Al igual que las dos anteriores debemos escribir =VAR() e introducir la matriz de datos entre los paréntesis.
Para finalizar las funciones de Varianza tenemos la función VARA() y VARPA() que nos permiten calcular varianzas incluyendo valores no númericos, codificando valores de Verdadero o Falso como 1 o 0. Para utilizar dichas funciones en Visual Basic debemos escribir la siguiente estructura:
ActiveCell.FormulaR1C1 = «=VARA(R[-33]C:R[-14]C)»
ActiveCell.FormulaR1C1 = «=VARPA(R[-34]C:R[-15]C)»
Como ya hemos visto la varianza se puede calcular también utilizando la herramienta Análisis de datos. Esta herramienta también la podemos trasladar a Visual Basic mediante esta instrucción:
Application.Run «ATPVBAEN.XLAM!Anova1», ActiveSheet.Range(«$A$32:$E$51»), _
ActiveSheet.Range(«$O$55»), «C», False, 0.05
Vemos que se trata de una aplicación, para poderla utilizar tenemos que activar la utilidad de Análisis de datos para VBA en el mismo sitio donde activamos el Análisis de datos para Excel, o Solver. La aplicación utilizada es Anova1, análisis de varianza para un variable. Primero nos encontramos con el fragmento ActiveSheet.Range(«$A$32:$E$51»), que nos indica el conjunto de datos de los que queremos conocer las varianzas. El segundo fragmento nos indica el lugar de salida, en este caso: ActiveSheet.Range(«$O$55»), la celda O55 de la hoja activa.
En estos dos ejemplos vemos como se colocaría en una hoja nueva (en la celda A1):
Application.Run «ATPVBAEN.XLAM!Anova1», ActiveSheet.Range(«$A$1:$E$20»), _
«A1», «R», False, 0.05
Application.Run «ATPVBAEN.XLAM!Anova1», ActiveSheet.Range(«$A$1:$E$20»), «» _
, «C», False, 0.05
En el primer caso bautizamos la hoja como A1; “A1”. En el segundo no colocamos nombre a la hoja, “”.
Si queremos enviar los resultados a un nuevo libro la estructura es la siguiente:
Application.Run «ATPVBAEN.XLAM!Anova1», ActiveSheet.Range(«$A$1:$E$20»), _
, «R», True, 0.05
Vemos que después del fragmento de entrada no debemos colocar nada: , , .
Después de indicar el lugar de salida debemos de indicar si las variables se encuentran por filas o por columnas, si cada columna pertenece a una variable colocaremos “C” de Column, en cambio si cada variable se sitúa en una fila colocaremos “R” de Row.
En el siguiente apartado debemos indicar si la primera columna (o fila) contiene Títulos, si los contiene debemos escribir TRUE, verdadero en inglés, y si no encontramos títulos FALSE, falso en inglés.
En último lugar indicaremos que valor queremos para el alfa, en nuestros casos 0.05, el punto es el indicador de decimal.
De esta forma podemos programar nuestro análisis de datos para crear una salida con toda la información del análisis de varianza de una variable. Las varianzas mostradas se corresponden con las varianzas de una muestra, aunque la información detallada de este análisis incluye también una tabla de las covarianzas y varianzas poblacionales.
Píldoras de Excel 