domingo, 13 de mayo de 2018

Puntos linealmente separables

Puede descargar el archivo separarPuntos.xlsm

Vamos a generar 1000 puntos en un plano cartesiano unos rojos y otros azules. Las coordenadas de los puntos se generan de forma aleatoria pero lo que diferencia su color es que pueden ser separados por una línea recta.


Disponemos de una macro que genera los puntos y los parámetros de la recta en su forma implícita. La macro se denomina nuevaRecta y se lanza con un botón con el mismo nombre.


La ecuación implícita de la recta es la siguiente.

ω+ ωx+ ωx= 0

De ella despejamos la variable dependiente y obtenemos la ecuación explícita de la recta.

x= - (ω1 /  ω2) - (ω0 / ω2)

Los parámetros que manejaremos para obtener la recta son ω0, ω1, ω2.

La macro que genera los puntos y la recta de forma aleatoria es la siguiente.

 Sub nuevaRecta()  
 Dim i As Integer  
 Dim R1 As Range, R2 As Range  
 Dim A, B  
 Set R1 = Range("B40:B1039")  
 Set R2 = Range("C40:C1039")  
 A = R1  
 B = R2  
 Randomize  
 For i = 1 To 1000  
  A(i, 1) = Rnd() * 10 - 5  
  B(i, 1) = Rnd() * 10 - 5  
 Next i  
 R1 = A  
 R2 = B  
 [C35] = Int(Rnd() * 80 - 40) / 10 'w0  
 [C36] = Int(Rnd() * 80 - 40) / 10 'w1  
 [C37] = Int(Rnd() * 80 - 40) / 10 'w2  
 End Sub  

Esta recta generada de forma aleatoria no separa bien los puntos rojos y azules, por lo que será necesario recurrir a algún procedimiento que nos de una nueva recta que si separe los puntos por colores.

Para conseguir separar los puntos mediante una recta procedemos a calcular en la celda amarilla (Q12) el número de errores cometidos. Cada punto rojo que esté por debajo de la recta y cada punto azul que esté por encima de la recta supondrán un error.


Para conseguir que el número de errores sea cero y que por tanto los puntos rojos queden por encima de la recta y los azules por debajo, recurrimos a una fantástica herramienta de Excel denominada 'Tabla de datos' que podemos encontrar en Datos / Análisis de hipótesis /Tabla de datos.


Lo que hacemos es calcular el error mínimo que se comete según diferentes valores de los parámetros de la recta ω0, ω1, ω2. La macro que se lanza con el botón 'mínimos' realiza la búsqueda de los errores mínimos. Este botón se ha de lanzar varias veces hasta conseguir que el error (celda amarilla) sea cero.

Este es un ejemplo que muestra la potencia de la herramienta Tabla de datos.

Veamos un gif animado donde se generan nuevos puntos y una nueva recta pulsando sobre el botón que llama a la macro nuevaRecta y luego pulsamos reiteradamente sobre el botón mínimos que lanza la macro que va minimizando los errores hasta que el error de la celda amarilla se hace cero. En ese momento tendremos la recta que separa completamente los puntos de colores en el plano.


viernes, 11 de mayo de 2018

Máquina de Galton en Excel

Puede descargar el archivo maquinaGalton.xlsm

La máquina de Galton nos permite ver cómo una distribución binomial tiende a una distribución normal cuando el número de tiradas va creciendo.


La máquina se puede ver en algunos museos de ciencias. También podríamos construirla nosotros con un tablero inclinado con una ranura superior por la que van cayendo bolitas que rebotan en una serie de clavos o pivotes. En cada rebote la bola tiene probabilidad 1/2 de ir hacia la izquierda o hacia la derecha. Esto se repite una y otra vez hasta que al final la bola termina en una cierta posición y cae por un carril que hemos construido con unas tablas verticales para separar unos de otros. Lo que vemos, si lanzamos un gran número de bolitas, es que los carriles de abajo forman una campana de Gauss. La distribución normal se forma como si de un histograma de frecuencias se tratara.

Vamos a construir una máquina de Galton virtual utilizando Excel y una macro que nos permite hacer el trabajo de la iteraciones de una forma rápida.

Hoja1

Primero creamos la macro que hace la que bolita baje rebotando entre los pivotes. En cada movimiento hacia abajo la bola tiene una probabilidad del 50% de ir hacia la izquierda y otro 50% de ir hacia la derecha. Es similar a un árbol binomial.



La macro que hace que la bola baje es la siguiente.

 Sub baja()  
 'árbol binomial de 32 etapas  
 Dim col As Byte  
 Worksheets("Hoja1").Activate  
 Randomize  
 Range("B2:BN68").Font.Bold = False  
 col = 34  
 Cells(2, 34) = "O"  
 Cells(4, 34) = "O"  
 Cells(4, 34).Font.Bold = True  
 For i = 1 To 32  
  If Rnd < 0.5 Then  
   col = col - 1  
  Else  
   col = col + 1  
  End If  
  Cells(i * 2 + 4, col) = "O"  
  Cells(i * 2 + 4, col).Font.Bold = True  
 Next i  
 End Sub  

Hoja2

Creamos un bucle FOR...NEXT que lanza un gran número de bolas, por ejemplo, 1000. De esta forma podemos ver en que columna ha quedado cada una de ellas al realizar el recorrido hacia abajo.

Veamos el código, similar al anterior pero incluyendo el bucle.

 Sub baja2()  
 Call BorraO  
 'árbol binomial de 32 etapas  
 Dim n As Long 'nº de tiradas  
 Dim col As Byte  
 Worksheets("Hoja2").Activate  
 Randomize  
 n = 1000  
 Application.ScreenUpdating = False  
 For j = 1 To n  
  col = 34  
  Cells(2, 34) = "O"  
  Cells(4, 34) = "O"  
  For i = 1 To 32  
   'una forma alternativa de sumar o restar 1 de forma aleatoria  
   col = col + WorksheetFunction.RandBetween(0, 1) * 2 - 1  
   Cells(i * 2 + 4, col) = "O"  
  Next i  
  'anotamos en la fila 99 los resultados  
  Cells(99, col) = Cells(99, col) + 1  
 Next j  
 Application.ScreenUpdating = True  
 End Sub  

Este es un proceso que puede tardar bastante tiempo en función del valor que demos a n. Para intentar reducir el tiempo de proceso podemos incluir al inicio la siguiente línea.

Application.ScreenUpdating = False


Con ella lo que hacemos el anular el envío de refresco a la pantalla de nuestro ordenador. Al final de la macro, dejamos el refresco activado para poder ver el resultado.

Application.ScreenUpdating = True

Veamos el resultado tras lanzar 10.000 veces la bola.



Hoja3

Nos gustaría poder ver la campana de Gauss que se forma con las tiradas. En la Hoja3 hemos creado unas columnas que se van rellenando con las bolas en vertical hasta que la columna más alta llegue a una altura de 30 bolas.


Con algo de imaginación podemos ver el bosquejo de una campana de Gauss. Para que veamos algo que nos recuerde más a la curva de una distribución normal tendríamos que usar muchas más tiradas.

Veamos el código.

 Sub baja3()  
 'árbol binomial de 32 etapas  
 Dim col As Byte  
 Call BorraO  
 Worksheets("Hoja3").Activate  
 Randomize  
 Do  
  col = 34  
  Cells(2, 34) = "O"  
  Cells(4, 34) = "O"  
  For i = 1 To 32  
   If Rnd < 0.5 Then  
    col = col - 1  
   Else  
    col = col + 1  
   End If  
   Cells(i * 2 + 4, col) = "O"  
  Next i  
  Cells(99, col) = Cells(99, col) + 1  
  miMax = Application.WorksheetFunction.Max(Range("Z99:AP99"))  
  Call BorraTri  
  Cells(98 - Cells(99, col), col) = "O"  
 Loop While miMax < 30  
 End Sub  


Hoja4 y Gráfico

En la Hoja4 lo que hacemos es traernos los valores obtenidos en la Hoja2, y presentarlos en forma de columna. Para ello usamos la siguiente expresión en la celda C4.

=INDIRECTO("Hoja2!"&"F99C"&B4*2;0)

Podemos ver aquí un potente uso de la función indirecto que toma valores de otra hoja para trasponerlos.

Con los valores obtenidos hacemos un histograma de frecuencias que se asemeja a una campana de Gauss. La apariencia será tanto mejor cuanto mayor sea el valor de tiradas (n).



jueves, 3 de mayo de 2018

Pesos y umbrales

Puede descargar el archivo pesosUmbrales.xlsm

He leído el siguiente artículo que es una introducción a la inteligencia artificial.


En el artículo se plantea un caso sencillo que podría resolver un perceptrón. Se trata de entender el comportamiento de una única neurona que tiene dos entradas con sus pesos omega 1 (ω1) y omega 2 (ω2), y un umbral de activación.

Planteamiento del problema

Un profesor realiza dos exámenes y pone la calificación final sin explicar cómo ha obtenido ésta. En la calificación final únicamente dice si el alumno ha aprobado o ha suspendido. Los alumnos desean averiguar que pesos da el profesor en cada examen (ω1 y ω2) para obtener la calificación media y con que nota media aprueba el profesor. Esa nota de corte sería el umbral de activación. Por ejemplo, una respuesta al problema podría ser la siguiente.

ω1=0,30 → El primer examen pesa un 30% en la nota final
ω2=0,70 → El segundo examen pesa un 70% en la nota final
u=0,4 → La asignatura se aprueba con un 4

Los pesos ω1 y ω2 se expresan en tanto por uno y su suma siempre es 1, que equivale al 100%.
La notas de los exámenes van entre 0 y 10, pero se divididen entre 10 para que su rango de variación esté entre 0 y 1. Lo mismo sucede con el umbral de activación (u), también se divide entre 10, por lo que aprobar con un 4 equivale a tener un umbral de 0,4.

Solución en Excel

No vamos a plantear la resolución en Excel mediante el uso de redes neuronales. Simplemente pretendemos plantear este caso sencillo mediante el uso de la hoja de cálculo. Es una pequeña aproximación a los fundamentos que rigen el comportamiento de una única neurona en un perceptrón sencillo de una capa. En lugar de ir buscando el camino más apropiado para llegar a la solución lo que hacemos es explorar todos los casos dentro de los valores que damos a ω1, ω2 y u.


Hacemos variar ω1 entre 0 y 1 en intervalos de 0,1. Como ω2 es la parte complementaria (ambas omegas suman 1), obtendremos que ω2 varía en el mismo intervalo, pero justo en orden inverso.

Hacemos variar el umbral u entre 0,1 y 0,9 con intervalos de 0,1.

Paso 1

Disponemos en la Hoja1 de los datos de los exámenes. Tenemos una tabla con las calificaciones obtenidas por los alumnos en el examen 1 y en el examen 2 (columnas C y D). En color naranja tenemos los pesos que ha asignado el profesor a cada examen y la nota de corte o umbral utilizado para aprobar.


Nuestro sistema será alimentado con los valores de color rosa correspondientes a 30 alumnos. Las columnas G y H contienen las notas de los dos exámenes divididas entre 10, ya que al perceptrón se le alimenta con datos estandarizados que van entre 0 y 1. La columna I indica si se ha aprobado el examen (1) o se ha supendido (0). La columan I es la misma que la columan F.



Paso 2

A la Hoja2 llevamos las notas de 30 alumnos, para ello copiamos y pegamos con pegado especial valores las tres columnas de color rosa.



Para cada uno de los 30 alumnos vamos a calcular la nota final ponderando con los valores de ω1y ω2 que van entre 0 y 1 con variación de 0,1. De esta forma se crea una tabla en las columnas de la E hasta la O. Así, la fórmula de la celda E7 es la siguiente fórmula matricial.

=SUMAPRODUCTO(TRANSPONER(E$4:E$5);$B7:$C7)

Esta fórmula multiplica la nota del examen 1 por el peso ω1 más el producto de la nota 2 por el peso ω2. También podríamos haber conseguido el mismo resultado con la fórmula siguiente para la celda E7.

=$B7*E$4+$C7*E$5



Paso 3


En las columnas desde la P hasta la Z, vamos a calcular la calificación de aprobado (1) o suspenso (0) teniendo en cuenta la nota media calculada en la tabla anterior y el umbral (u) que se encuentra en la celda P2. Si el umbral es 0,4 quiere decir que se aprueba con un 4 en la nota media.



La fórmula de la celda P7 es un condicional que nos dice si se aprubeba o no según se supere o no el umbral.

=SI(E7>=$P$2;1;0)


Paso 4

Ahora vamos a calcular los errores cometidos comparando el vector de ceros y unos de cada una de las columnas P:Z con los valores de la columna D que contienen los aprobados y suspensos publicados por el profesor.



La celda AA7 contiene la siguiente fórmula que nos permite realizar la comparación .

=--(P7<>$D7)

Si obtenemos un vector completamente de ceros lo vamos a colorear en amarillo usando Formato condicional.

En la fila 1, en el rango AA1:AK1 calculamos la suma de los errores cometidos en cada una de la columnas. Lo que nos interesa es detectar que el error sea cero. En AN1 calculamos el mínimo de ese rango y si llegamos a obtener un cero quiere decir que estamos ante un caso donde los valores de ω1, ω2 y u explican bien las calificaciones publicadas por el profesor.

Pero tenemos que calcular esta tabla para cada uno de los posibles valores de u desde 0,1 hasta 0,9. Para realizar este cálculo sin tener que ir variando el valor de forma manual o sin tener que hacer más tablas hemos recurrido a una estupenda herramienta de Excel denominada Tabla de datos y su resultado se muestra en el rango AM6:AN17.

Con ayuda de Formato condicional para los colores y con un par de botones que lanzan macros, podemos obtener la solución que buscamos. Si en el rango AP6:AP9 obtenemos las cuatro celdas con valores VERDADERO quiere decir que hemos encontrado la solución a un caso.

Si deseamos hacer un nuevo caso pulsaremos el botón denominado "cambia pesos" que lo que hace es cambiar los valores de los omegas y el umbral en la Hoja1, y lanzando la macro que intenta resolver el caso. El caso queda resuelto cando obtenemos los cuatro verdaderos.

Pero existe la posibilidad de que las 30 notas de los alumnos no sean suficientes para resolver el caso y entonces necesitemos otra muestra de valores. Esto se consigue pulsando el botón "Toma datos", que habrá que pulsar reiteradamente en algunas ocasiones para llegar a conseguir los cuatro verdaderos y por tanto la solución final del problema planteado.




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.