domingo, 22 de abril de 2018

Gráfico con datos matriciales

Puede descargar el archivo manchasSolares.xlsm
Disponemos de dos casos, en ambos creamos un gráfico de dispersión XY con puntos constituidos por parejas de datos aleatorios. En el primer caso los datos se encuentran en la propia hoja de cálculo y en segundo caso los datos se generan de forma aleatoria en la propia macro, usando simplemente código VBA.

Hoja1

La capacidad de representar datos en un gráfico está limitada en Excel según la versión. Seguidamente se muestran las limitaciones de la versión 2016.


La información anterior está obtenida de la página de Microsoft. En la Hoja1 hemos creado 30.000 parejas de valores.


En las columnas A y B creamos aleatoriamente el radio y el ángulo que son las dos coordenadas polares que vamos a manejar. Luego en las columnas C y D convertimos las coordenadas polares en coordenadas cartesianas con la fórmula siguiente.


Si el radio se obtiene con la función aleatorio() esto nos permite crear un gráfico como el siguiente.


Observamos una mayor concentración de puntos en el centro. Si queremos que la distribución de puntos aleatorios sea uniforme en toda la circunferencia hemos de elegir el radio como la raiz cuadrada de una uniforme cero uno.

=raiz(aleatorio())

Con este cambio obtendremos el siguiente gráfico que podemos ver en forma de gif animado obtenido al recalcular los valores aleatorios pulsando la tecla de función F9 de forma reiterada.


Hoja2

Con el segundo caso veremos cómo generar los puntos aleatorios con una macro. Mediante código VBA crearemos una matriz para el eje X y otra matriz para el eje Y. Luego introduciremos ambas matrices como las series de valores para poder generar el gráfico de tipo dispersión XY.


Primera macro

 Sub generaChartConArray1()  
 Dim A(16000) As Variant  
 Dim B(16000) As Variant  
 Dim i As Long  
 Dim grafico As ChartObject  
 Dim c As Byte  
 Dim ChtObj As ChartObject  
 Worksheets("Hoja2").Activate  
 c = 0  
 Randomize  
 For i = 1 To 16000  
  A(i) = Rnd  
  B(i) = Rnd  
 Next i  
 For Each grafico In Worksheets("Hoja2").ChartObjects  
   If grafico.Name = "migas" Then  
     c = c + 1  
   End If  
 Next  
 If c = 0 Then  
  Set ChtObj = Worksheets("Hoja2").ChartObjects.Add(Left:=10, Top:=10, _  
       Width:=400, Height:=400)  
  With ChtObj  
   .Chart.ChartType = xlXYScatter  
   .Chart.SetSourceData Source:=Range("Hoja2!$A$1:$B$2")  
   .Name = "migas"  
 End With  
 End If  
 ActiveSheet.ChartObjects("migas").Activate  
 ActiveChart.SeriesCollection(1).XValues = A  
 ActiveChart.SeriesCollection(1).Values = B  
 ActiveChart.Axes(xlCategory).MaximumScale = 1  
 ActiveChart.Axes(xlCategory).MinimumScale = 0  
 ActiveChart.Axes(xlValue).MaximumScale = 1  
 ActiveChart.Axes(xlValue).MinimumScale = 0  
 ActiveChart.SetElement (msoElementLegendNone)  
 ActiveChart.SetElement (msoElementPrimaryValueGridLinesNone)  
 ActiveChart.SetElement (msoElementPrimaryCategoryAxisNone)  
 ActiveChart.SetElement (msoElementPrimaryValueAxisNone)  
 ActiveChart.SetElement (msoElementChartTitleNone)  
 ActiveChart.FullSeriesCollection(1).MarkerStyle = -4118  
 ActiveChart.FullSeriesCollection(1).MarkerSize = 2  
 Range("A1").Select  
 End Sub  

Segunda macro

 Sub generaChartConArray2()  
 Dim A() As Variant  
 Dim B() As Variant  
 Dim i As Long  
 Dim radio As Double  
 Dim angulo As Double  
 Dim grafico As ChartObject  
 Dim c As Byte  
 Dim n As Long  
 Dim ChtObj As ChartObject  
 Worksheets("Hoja2").Activate  
 c = 0  
 n = 16384  
 ReDim A(n)  
 ReDim B(n)  
 Randomize  
 For i = 1 To n  
  radio = Rnd  
  angulo = Rnd * 2 * (WorksheetFunction.Pi)  
  A(i) = radio * Cos(angulo)  
  B(i) = radio * Sin(angulo)  
 Next i  
 For Each grafico In Worksheets("Hoja2").ChartObjects  
   If grafico.Name = "migas" Then  
     c = c + 1  
   End If  
 Next  
 If c = 0 Then  
  Set ChtObj = Worksheets("Hoja2").ChartObjects.Add(Left:=10, Top:=10, _  
       Width:=400, Height:=400)  
  With ChtObj  
   .Chart.ChartType = xlXYScatter  
   .Chart.SetSourceData Source:=Range("Hoja2!$A$1:$B$2")  
   .Name = "migas"  
 End With  
 End If  
 ActiveSheet.ChartObjects("migas").Activate  
 ActiveChart.SeriesCollection(1).XValues = A  
 ActiveChart.SeriesCollection(1).Values = B  
 ActiveChart.Axes(xlCategory).MaximumScale = 1  
 ActiveChart.Axes(xlCategory).MinimumScale = -1  
 ActiveChart.Axes(xlValue).MaximumScale = 1  
 ActiveChart.Axes(xlValue).MinimumScale = -1  
 ActiveChart.SetElement (msoElementLegendNone)  
 ActiveChart.SetElement (msoElementPrimaryValueGridLinesNone)  
 ActiveChart.SetElement (msoElementPrimaryCategoryAxisNone)  
 ActiveChart.SetElement (msoElementPrimaryValueAxisNone)  
 ActiveChart.SetElement (msoElementChartTitleNone)  
 ActiveChart.FullSeriesCollection(1).MarkerStyle = -4118  
 ActiveChart.FullSeriesCollection(1).MarkerSize = 2  
 Range("A1").Select  
 End Sub  

Tercera macro

 Sub generaChartConArray3()  
 Dim A() As Variant  
 Dim B() As Variant  
 Dim i As Long  
 Dim radio As Double  
 Dim angulo As Double  
 Dim grafico As ChartObject  
 Dim c As Byte  
 Dim n As Long  
 Dim ChtObj As ChartObject  
 Worksheets("Hoja2").Activate  
 c = 0  
 n = 16384  
 ReDim A(n)  
 ReDim B(n)  
 Randomize  
 For i = 1 To n  
  radio = Sqr(Rnd)  
  angulo = Rnd * 2 * (WorksheetFunction.Pi)  
  A(i) = radio * Cos(angulo)  
  B(i) = radio * Sin(angulo)  
 Next i  
 For Each grafico In Worksheets("Hoja2").ChartObjects  
   If grafico.Name = "migas" Then  
     c = c + 1  
   End If  
 Next  
 If c = 0 Then  
  Set ChtObj = Worksheets("Hoja2").ChartObjects.Add(Left:=10, Top:=10, _  
       Width:=400, Height:=400)  
  With ChtObj  
   .Chart.ChartType = xlXYScatter  
   .Chart.SetSourceData Source:=Range("Hoja2!$A$1:$B$2")  
   .Name = "migas"  
 End With  
 End If  
 ActiveSheet.ChartObjects("migas").Activate  
 ActiveChart.SeriesCollection(1).XValues = A  
 ActiveChart.SeriesCollection(1).Values = B  
 ActiveChart.Axes(xlCategory).MaximumScale = 1  
 ActiveChart.Axes(xlCategory).MinimumScale = -1  
 ActiveChart.Axes(xlValue).MaximumScale = 1  
 ActiveChart.Axes(xlValue).MinimumScale = -1  
 ActiveChart.SetElement (msoElementLegendNone)  
 ActiveChart.SetElement (msoElementPrimaryValueGridLinesNone)  
 ActiveChart.SetElement (msoElementPrimaryCategoryAxisNone)  
 ActiveChart.SetElement (msoElementPrimaryValueAxisNone)  
 ActiveChart.SetElement (msoElementChartTitleNone)  
 ActiveChart.FullSeriesCollection(1).MarkerStyle = -4118  
 ActiveChart.FullSeriesCollection(1).MarkerSize = 2  
 Range("A1").Select  
 End Sub  

La segunda y tercera macro son prácticamente iguales, únicamente cambia la forma en la que elegimos el radio. En la segunda macro se hace según una distribución de probabilidad uniforme entre cero y uno. En la tercera macro lo que pretendemos es que los puntos del gráfico se distribuyan de forma uniforme por el área de la circunferencia por lo que hemos tenido que modificar la distribución de probabilidad introduciendo la raiz cuadrada de una uniforme entre cero y uno.

Puede ver un desarrollo parecido en otro lenguaje de programación en el siguiente enlace.

Canvas en HTML5+CSS+JS creando puntitos aleatorios

El él se emplea HTML5+CSS+JavaScript.

viernes, 30 de marzo de 2018

Regresión Múltiple

Puede descargar el archivo regresionMultiple.xlsx

En una ciudad establecen un impuesto sobre la propiedad de las casas en función del año de construcción de la vivienda, la superficie, el número de ventanas que tiene y la anchura de la calzada de su calle. Determinar la ecuación lineal que relaciona las cuatro variables independientes x1, x2, x3 y x4 con al variable dependiente y.

El año de construcción influye de forma negativa, esto supone que pagan más impuesto las viviendas más recientes. El resto de variables tienen una influencia positiva. Esto supone que el coeficiente del año m1 será negativo y los coeficientes m2, m3 y m4 serán positivos.


Siendo b el término independiente.

La columna B contiene el valor conocido de la variable dependiente y. Las columnas C, D, E y F contienen los valores conocidos de las cuatro variables independientes x. En la columna G calculamos el valor teórico de la variable y usando la siguiente expresión.

=SUMAPRODUCTO(C6:F6;$J$14:$M$14)+$N$14

Los coeficientes m1, m2, m3, m4 y b se obtienen usando la función matricial ESTIMACION.LINEAL.

=ESTIMACION.LINEAL(B6:B105;C6:F105;1;1)

Para saber más sobre esta función puede consultar un post de este blog titulado:

Regresión polinómica





La función ESTIMACION.LINEAL nos da los valores ajustados de los coeficientes en orden inverso al necesario. Nos da m4m3m2m1 cuando nosotros necesitamos el orden m1m2m3m4. Esto supone que hemos tenido que invertir el orden en el rango J14:M14 usando la fórmula matricial siguiente.

=INDICE(J7:M7;COLUMNA(M7)-COLUMNA(J7:M7)+1)

El motivo por el que hemos cambiado el orden es para poder usar la función SUMAPRODUCTO en la celda G6.


En la celda J9, de color rosa, tenemos el coeficiente de determinación r2.

jueves, 29 de marzo de 2018

Regresión polinómica

Puede descargar el archivo regresionPolinomica.xlsx

Podemos ajustar una nube de puntos mediante una recta, ésta sería la típica regresión lineal y=a+bx. También podemos ajustar esa misma nube de puntos mediante un polinomio de grado dos, una parábola y=ax2+bx+c. Podemos ir subiendo el grado del polinomio a grado tres, o cuatro, o aún mayor para ver si el ajuste mejora.

En Excel disponemos de la función ESTIMACION.LINEAL que nos permite obtener los coeficientes de estos polinomios y el coeficiente de determinación R2, así como otras magnitudes estadísticas de errores y desviaciones propias del ajuste realizado. El coeficiente de correlación R es la raíz cuadrada de R2. El signo del coeficiente de correlación R nos indica si la recta es creciente (signo positivo) o es decreciente (signo negativo).

Disponemos de una nube de puntos formada por dos variables X e Y. La variable X es la variable independiente y la variable Y es la variable dependiente, que depende de X.


Para hacer más didáctica la explicación de la regresión polinómica hemos elegido unos valores que se ajustan perfectamente a un polinomio de grado 3. Concretamente se trata del polinomio siguiente.

y=2x3-7x2+9x+2

Pero esto no lo sabemos aún, y lo que pretendemos es ir probando grados 1, 2, 3, 4 hasta comprobar que el polinomio de grado 3 es el mejor.

Polinomio de grado 1. Línea recta

Excel nos permite construir un gráfico de tipo dispersión (XY) donde se representan las cinco parejas de puntos correspondientes a la nube de puntos. Sobre este gráfico podemos pedir, pulsando con el botón derecho del ratón, la opción 'Agregar línea de tendencia'.


Obtenemos a la derecha un menú denominado 'Formato de línea de tendencia', en el que podemos elegir que la línea de tendencia sea 'Lineal', con lo que obtendremos una línea recta.



En nuestro gráfico hemos marcado las opciones.
  • Presentar ecuación en el gráfico
  • Representar el valor R cuadrado en el gráfico

El gráfico obtenido es el siguiente.


La ecuación de la recta tiene la siguiente expresión funcional.

y=11,8x-5,6

Donde
  • a es el término independiente, es el punto de corte con el eje vertical (eje de ordenadas)
  • b es la pendiente de la recta, es la tangente del ángulo que forma la recta con el eje horizontal (eje de ordenadas). Si es positiva la recta es creciente, si es negativa la recta es decreciente y si es cero la recta es horizontal.

En Excel podemos calcular a y b mediante las siguientes funciones, así como r que es coeficiente de correlación y su cuadrado que es el coeficiente de determinación.
  • INTERSECCION.EJE(Conocido_y; Conocido_x)  → permite calcular a
  • PENDIENTE(Conocido_y; Conocido_x)  → permite calcular b
  • COEF.DE.CORREL(matriz1; matriz2) → permite calcular r
  • COEFICIENTE.R2(Conocido_y; Conocido_x)  → permite calcular r2


Cálculo de los coeficientes del polinomio

La función ESTIMACION.LINEAL es una función matricial. Recordemos los tres pasos necesarios para utilizar una función matricial.

  1. Seleccionamos las celdas donde la función matricial dejará sus resultados
  2. Escribimos la función matricial con sus argumentos
  3. Validamos pulsando simultáneamente tres teclas: CONTROL+SHIFT+ENTER
La sintaxis de función es la siguiente.

=ESTIMACION.LINEAL(conocido_y, [conocido_x], [constante], [estadística])

En nuestro caso hemos escrito la siguiente función en la Hoja1.

=ESTIMACION.LINEAL(C5:C9;B5:B9;;1)


Hemos indicado el rango de la variable y, el rango de la variable x. Luego hemos dejado vacío el lugar correspondiente a la 'constante' lo que indica que no deseamos que se fuerce la intersección entre los ejes. Esto supone que no deseamos forzar para que la función pase por el punto (0,0). Finalmente hemos puesto un 1 o VERDADERO en 'estadística' porque deseamos que se muestren ciertos valores estadísticos de los que nos interesa especialmente el coeficiente de determinación, que aparece en color rosa, en la 3ª fila, 1ª columna de la tabla de resultados.


Con la función INDICE podemos extraer un valor individual de la tabla matricial. Esto se ha realizado en la celda F17 de la Hoja1.

=INDICE(ESTIMACION.LINEAL(C5:C9;B5:B9;;1);3;1)

La expresión anterior extrae el valor del coeficiente de determinación r2, celda de color rosa.

Polinomio de grado 2. Parábola

Ahora vamos a realizar una regresión polinómica de grado 2. Obtendremos una parábola de la forma:


La ecuación resulta ser la siguiente.

y=5x2-8,2x+4,4

Podemos ver el gráfico obtenido pidiendo una línea de tendencia polinómica de grado 2.


Para obtener los coeficientes del polinomio y demás magnitudes estadística utilizamos la función matricial siguiente.

=ESTIMACION.LINEAL(C5:C9;B5:B9^{1\2};;1)

El primer argumento es el rango C5:C9 correspondiente a los valores conocidos de la variable y.
El segundo argumento es el ranto B5:B9 correspondiente a los valores conocidos de la variable x. Este rango se debe elevar a un vector, que va entre llaves {} donde aparecen dos elementos 1 y 2 separados por una barra inclinada \. Esto indica que en el polinomio existe un coeficiente (b) que acompaña a la variable x elevada a la 1, y otro coeficiente (a) que acompaña a la variable x elevada a 2.




Nota

En algunas versiones de Excel o según el idioma configurado, el separador de los elementos del vector no será la barra inclinada para ese lado (\), sino la contraria (/), o incluso el puno y coma (;) o la coma (,).

También funciona, en nuestro caso, poner la fórmula con la siguiente expresión.

=ESTIMACION.LINEAL(C5:C9;B5:B9^TRANSPONER({1;2});;1)

Recuerde que cuando se trabaja con vectores es importante manejar los vectores correctamente según sean de fila o columna. Este es el motivo por el que hemos usado TRANSPONER y hemos separado los elementos 1 y 2 con punto y coma.

Polinomio de grado 3

Efectuamos una regresión polinómica de grado 3 con la siguiente estructura.


La ecuación resulta ser la siguiente.

y=2x3-7x2+9x+2

Podemos ver el gráfico obtenido pidiendo una línea de tendencia polinómica de grado 3.


Hemos utilizado la función matricial siguiente.

=ESTIMACION.LINEAL(C5:C9;B5:B9^{1\2\3};;1)

Poniendo {1\2\3} estamos pidiendo que calcule los coeficientes de grado 1, 2 y 3.


Podríamos no haber puesto alguno de los elementos del vector {1\2\3}, y en ese caso se ajustaría un polinomio donde el coeficiente que no aparece se fuerza a que sea cero. Por ejemplo, podemos probar con la siguiente expresión donde falta el elemento que lleva la variable x elevada a 2.

=ESTIMACION.LINEAL(C5:C9;B5:B9^{1\3};;1)

En este caso obtendríamos el siguiente polinomio que no tiene término en x2.

y=0,865740741x3-1,532407407x+3,75

Polinomio de grado 4

Efectuamos una regresión polinómica de grado 4 con la siguiente estructura.


La ecuación resulta ser la siguiente.

y=2,87531·10-15x4+2x3-7x2+9x+2

Podemos ver el gráfico obtenido pidiendo una línea de tendencia polinómica de grado 4.


Hemos utilizado la función matricial siguiente.

=ESTIMACION.LINEAL(C5:C9;B5:B9^{1\2\3\4};;1)

Poniendo {1\2\3\4} estamos pidiendo que calcule los coeficientes de grado 1, 2, 3 y 4.



Nuestro caso es especial

Observe que el coeficiente de x4 es prácticamente cero. Con la función ESTIMACION.LINEAL obtenemos 2,87531·10-15 y en el ajuste gráfico obtenemos -7·10-13. Ambos valores son muy cercanos a cero. Esto indica que no hemos ganado nada pasando de un polinomio de grado 3 a un polinomio de grado 4.

Con el polinomio de grado 3 ya obtuvimos un coeficiente de determinación r2 igual a 1, que supone correlación perfecta. Este es el motivo de que al aumentar el grado del polinomio no ganemos nada, puesto que ya con el polinomio de grado 3 teníamos perfectamente explicada la nube de puntos. Todos los puntos conocidos, los pares (x,y) caen perfectamente en el polinomio de grado 3, por lo que no es necesario aumentar el grado del polinomio.

El caso que hemos planteado es muy especial ya que justamente hemos obtenido los pares (x,y) de la nube de puntos usando previamente el polinomio de grado 3.

y=2x3-7x2+9x+2

En un caso donde los datos sean más realistas será complicado llegar justo a un coeficiente de correlación igual a 1. Lo que observaremos, en general, es que aumentando el grado del polinomio se mejora el ajuste, pero aquí debemos llegar a un grado de compromiso, ya que en ocasiones pasar de grado 7 a grado 8, por ejemplo, no mejorará de forma apreciable la bondad del ajuste, no mejorando gran cosa el coeficiente de correlación. Por tanto, hemos de decidir con qué grado nos queremos quedar. Normalmente esto se dilucida aplicando un poco de sentido común, simplemente respondiendo a la siguiente cuestión ¿realmente al aumentar un grado más mejora significativamente el ajuste?

miércoles, 21 de febrero de 2018

Sucesión de Fibonacci en Excel

Puede descargar el archivo fibonacci.xlsm

Las sucesión de Fibonacci es bastante famosa. Los dos primeros valores son cero y uno, y los restantes se forman sumando los dos anteriores.

Estos son los números de Fibonacci.

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, ... ... ... 

Vamos a crear una hoja de cálculo en la que vamos a calcular la serie de tres formas diferentes.
  1. Mediante fórmulas de Excel
  2. Mediante VBA, con la Macro 1
  3. Mediante VBA, con la Macro 2

Veamos las macros.


 Sub Borra()  
 Range("D5:E34").ClearContents  
 End Sub  
   
 Sub fibonacci1()  
 [D5] = 0  
 [D6] = 1  
 For i = 3 To 30  
  Cells(i + 4, 4) = Cells(i + 3, 4) + Cells(i + 2, 4)  
 Next i  
 End Sub  
   
 Sub fibonacci2()  
 Dim A(30) As Long  
 A(1) = 0: [E5] = 0  
 A(2) = 1: [E6] = 1  
 For i = 3 To 30  
  A(i) = A(i - 1) + A(i - 2)  
  Range("E" & i + 4) = A(i)  
 Next i  
 End Sub  


  1. En la Macro 1 denominada fibonacci1 trabajamos con cells.
  2. En la Macro 2 denominanda fibonacci2 trabajamos con una array, con la matriz A.

viernes, 22 de diciembre de 2017