La Parábola de los mínimos cuadrados con Excel, Graph y Geogebra

Año LA PARÁBOLA DE LOS MÍNIMOS CUADRADOS Autor: Mario Suárez La parábola de mínimos cuadrados que aproxima el conjunto de puntos (??1 , ??1 ), (??2 , ??2 ), (??3 , ??3 ), … (???? , ???? ) tiene ecuación dada por ?? = ??0 + ??1 ?? + ??2 ??2 , donde las constantes ??0 , ??1 y ??2 se determinan al resolver simultáneamente el sistema de ecuaciones que se forma al multiplicar la ecuación ?? = ??0 + ??1 ?? + ??2 ??2 por 1, ??, ?? sucesivamente, y sumando después.

S?? = ??0 ?? + ??1 S?? + ??2 S??2 { S???? = ??0 S?? + ??1 S??2 + ??2 S??3 S??2 ?? = ??0 S??2 + ??1 S??3 + ??2 S??4

Ejemplo ilustrativo

La siguiente tabla muestra la población de un país en los años 1960-2010 en intervalos de 5 años.

196019651970197519801985 1990 1995 2000 2005 2010 Población (millones) 4,52 5,18 6,25 7,42 8,16 9,12 10,9211,62 12,6813,1213,97

1) Ajustar una parábola de mínimos cuadrados de la forma ?? = ??0 + ??1 ?? + ??2 ??2

2) Calcular los valores de tendencia para los años dados.

3) Estimar la población para los años 2015 y 2020.

4) Calcular el coeficiente de determinación.

5) Elaborar un diagrama de dispersión, y en el mismo diagrama graficar la parábola de los mínimos cuadrados.

Nota: Se recomienda codificar o cambiar la numeración de los años, tratando que X = 0 esté ubicado en lo posible en el centro.

Solución:

1) Para ajustar una parábola de mínimos cuadrados se llena la siguiente tabla: Año 1960 1965 1970 1975 1980 1985 1990 1995 2000 2005 2010 S ?? -5 -4 -3 -2 -1 0 1 2 3 4 5 0 ?? 4,52 5,18 6,25 7,42 8,16 9,12 10,92 11,62 12,68 13,12 13,97 102,96 ??2 25 16 9 4 1 0 1 4 9 16 25 110 ??3 -125 -64 -27 -8 -1 0 1 8 27 64 125 0 ??4 625 256 81 16 1 0 1 16 81 256 625 1958 ???? -22,6 -20,72 -18,75 -14,84 -8,16 0 10,92 23,24 38,04 52,48 69,85 109,46 ??2 ?? 113 82,88 56,25 29,68 8,16 0 10,92 46,48 114,12 209,92 349,25 1020,66

Se remplaza valores en el sistema y se obtiene:

S?? = ??0 ?? + ??1 S?? + ??2 S??2 { S???? = ??0 S?? + ??1 S??2 + ??2 S??3 S??2 ?? = ??0 S??2 + ??1 S??3 + ??2 S??4 102,96 = a0 · 11 + a1 · 0 + a2 · 110 11a0 + 0a1 + 110a2 = 102,96 { 109,46 = a0 · 0 + a1 · 110 + a2 · 0 ? { 0a0 + 110a1 + 0a2 = 109,46 1020,66 = a0 · 110 + a1 · 0 + a2 · 1958 110a0 + 0a1 + 1958a2 = 1020,66 Resolviendo el sistema empleando determinantes (regla de Cramer) se obtiene: a0 = 22175524,8 + 0 + 0 – 12349986 – 0 – 0 9825538,8 = 2369180 + 0 + 0 – 1331000 – 0 – 0 1038180 = 9,464 ??1 = 23577549,48 + 0 + 0 – 1324466 – 0 – 0 2357549,48 = 1038180 1038180 = 0,995 ??2 = 1234998,6 + 0 + 0 – 1245816 – 0 – 0 -10817,4 = 1038180 1038180 = -0,01

El sistema resuelto en Excel se muestra en la siguiente figura: Para resolver el sistema en GeoGebra se sigue los siguientes pasos: a) Clic en Vista

b) Clic en CAS-Cálculo Simbólico. Escribir soluciones en la casilla 1

c) Escoger la opción Soluciones[ , ]

d) Escribir la lista de ecuaciones y la lista de variables. Enter

Soluciones[ {11x+0y+110z=102.96,0x+110y+0z=109.46,110x+0y+1958z=1020.66}, {x,y,z} ] 67669 7150 = 9,464 ; 5473 5500 = 0,995 ; – 149 14300 = -0,01 Remplazando los valores encontrados se obtiene la ecuación de la parábola de mínimos cuadrados:

?? = ??0 + ??1 ?? + ??2 ??2 ? ?? = 9,464 + 0,995?? – 0,01??2

9 4 1 4 9 S ?? = = 2) Los valores de tendencia se obtienen al remplazar los valores de X en la ecuación de la parábola de mínimos cuadrados, los cuales se presenta en la siguiente tabla: Año ?? ?? Valores de tendencia ?? = 9,464 + 0,995?? – 0,01??2 1960 1965 1970 1975 1980 1985 1990 1995 2000 2005 2010 -5 -4 -3 -2 -1 0 1 2 3 4 5 4,52 5,18 6,25 7,42 8,16 9,12 10,92 11,62 12,68 13,12 13,97 4,24 5,32 6,39 7,43 8,46 9,46 10,45 11,41 12,36 13,28 14,19 3) Para estimar la población de los años 2015 y 2020 se transforma estos años a X siguiendo la secuencia de la tabla anterior, siendo X = 6 para el año 2015 y X= 7 para el 2020

Entonces para el 2015 se tiene: Y = 9,464 + 0,995X – 0,01X2 =9,464 + 0,995(6) – 0,01(6)2 = 9,464 + 5,97-0,36 =15,074

Para el 2020 se tiene: Y = 9,464 + 0,995X – 0,01X2 =9,464 + 0,995(7) – 0,01(7)2 = 9,464 + 6,965-0,49 =15,939

4) Se llena la siguiente tabla y se aplica la ecuación para calcular el coeficiente de Pearson Año ?? ?? ??2 ???? ??2 1960 1965 1970 1975 1980 -5 4,52 -4 5,18 -3 6,25 -2 7,42 -1 8,16 25 16

1 -22,6 -20,72 -18,75 -14,84 -8,16 20,430 26,832 39,063 55,056 66,586 1985 0 9,12 0 0 83,174 1990 1995 2000 2005 2010 1 10,92 2 11,62 3 12,68 4 13,12 16 5 13,97 25 0 102,96 110 10,92 23,24 38,04 52,48 69,85 109,46 119,246 135,024 160,782 172,134 195,161 1073,490 ?? ? ???? – (? ??)(? ??) v[?? ? ??2 – (? ??)2 ][?? ? ??2 – (? ??)2 ] ?? = 0,996 11 · 109,46 – 0 · 102,96 v[11 · 110 – (0)2 ][11 · 1073,490 – (102,96)2 ]

Elevando al cuadrado coeficiente de Pearson queda calculado el coeficiente de determinación. Coeficiente de determinación = ?? 2 = (0,996)2 = 0,992 El coeficiente de determinación calculado en Excel se muestra en la siguiente figura: 5) El diagrama de dispersión y la parábola de los mínimos cuadrados mediante Excel se muestra en la siguiente figura:

1960- Año Año Empleando el programa Graph se obtiene la siguiente figura: TAREA DE INTERAPRENDIZAJE 1) La siguiente tabla muestra la población aproximada de la Provincia de Imbabura en los años 2010 en intervalos de 5 años. 19601965197019751980 198519901995200020052010 Población (miles) 123 140 170 201 221 247 296 315 344 356 379 1.1) Ajuste una parábola de mínimos cuadrados de la forma ?? = ??0 + ??1 ?? + ??2 ??2 manera manual, empleando Excel y GeoGebra. Y = 256,464 + 26,991X – 0,265X2 1.2) Calcule los valores de tendencia para los años dados de manera manual y empleando Excel. 1960 1965 1970 1975 1980 1985 1990 1995 2000 2005 2010 Valor de tendencia 114,88144,26173,11201,42229,21256,46283,19309,39335,05360,19 384,79 1.3) Estime la población para los años 2015 y 2020 Año 2015 = 408,87 miles de habitantes Año 2020 = 432,42 miles de habitantes

1.4) Calcule el coeficiente de determinación de manera manual y empleando Excel. 0,992 1.5) Elabore un diagrama de dispersión, y en el mismo diagrama graficar la parábola de los mínimos cuadrados de manera manual, empleando Excel y empleando Graph. 2) Cree y resuelva un ejercicio de aplicación de la parábola de los mínimos cuadrados con datos de la población del Ecuador o de cualquier otro país de manera manual, empleando Excel y Graph. 3) Consulte en la biblioteca o en el internet un ejercicio de aplicación de la Parábola de los mínimos cuadrados. Presente el ejercicio resuelto con GeoGebra y Graph.