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.