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

martes, 5 de diciembre de 2017

Ciudades elegidas

Puede descargar el archivo ciudadesElegidas.xlsm

Hoja1

Disponemos de una lista de ciudades, son en total 735 ciudades, y deseamos elegir aleatoriamente unas cuantas de ellas. La elección se realizará por dos métodos, ambos con la posibilidad de que salgan ciudades repetidas. Si obtenemos ciudades repetidas las detectaremos con Formato condicional.

Los métodos empleados son los siguientes.
  • Método 1: con la función INDICE
  • Método 2: con la función DESREF



Hoja2

Disponemos de una lista de ciudades en la columna A y de ellas deseamos elegir aleatoriamente y sin repetición un cierto número de ellas. Vamos a resolver este caso con la ayuda de una macro de Excel.


 Sub EligeNombresAleatorios()  
 Borra  
 Dim numElegidas As Integer  
 Dim numNombres As Long  
 Dim numAlea As Integer  
 Dim Nombres() As String  
 Dim i As Byte, j As Byte  
 Dim Fila As Long  
 Dim Repe As Boolean 'para ver si está repetida la ciudad seleccionada  
 numElegidas = Range("C3").Value  
 Fila = 6  
 ReDim Nombres(1 To numElegidas)  
 numNombres = Application.CountA(Range("A:A")) - 1  
 For i = 1 To numElegidas  
   Do  
     Repe = False 'inicialmente supondremos que no está repetida la ciudad  
     numAlea = Application.RandBetween(1, numNombres)  
     'veamos si la ciudad seleccionada ya ha sido elegida  
     For j = LBound(Nombres) To UBound(Nombres)  
       If Nombres(j) = Cells(numAlea + 1, 1).Value Then Repe = True: Exit For  
     Next j  
   Loop While Repe  
   Nombres(i) = Cells(numAlea + 1, 1).Value ' Assign random name to the array  
 Next i  
 'Escribe las ciudades elegidas  
 For j = LBound(Nombres) To UBound(Nombres)  
   Cells(Fila, 3) = Nombres(j)  
   Fila = Fila + 1  
 Next j  
 End Sub  
   
 Sub Borra()  
 Range("C6").Select  
 Range(Selection, Selection.End(xlDown)).ClearContents  
 Range("C3").Select  
 End Sub  


Y en la Hoja2 tenemos el siguiente código.

 Private Sub Worksheet_Change(ByVal Target As Range)  
   If Target.Address = "$C$3" Then  
     Call EligeNombresAleatorios  
   End If  
 End Sub