Dado el problema:

Vida S.A. es una empresa constructora de viviendas para el sector medio en Lima Metropolitana. Uno de sus nuevos proyectos consiste en construir viviendas para los trabajadores del sector público financiado por el sector Vivienda. Para ver la factibilidad del proyecto, Vida decide llevar a cabo un muestreo por conglomerados tomando en cuenta 10 entidades del sector público. De esta forma, eligió una entidad y de todos los empleados clasificados como de sector medio se tomó una muestra y se obtuvo de ella sus ingresos mensuales y su capacidad de ahorro (en miles de dólares).

Estos datos se muestran en la siguiente tabla:

El procedimiento que vamos a implementar, deberá definir los dos vectores X e Y y todas aquellas variables públicas usando DIM. Puesto que ellos deben ser "conocidos" por aquellos procedimientos a los cuales no se les pasa como parámetros, deben ser declarados como variables públicas. 

Sub Regre()

End Sub

Este procedimiento contendrá un menú, desde donde se harán varias tareas como la creación de la hoja cuando en la primera vez y la cabecera de la nueva hoja (Header), el ingreso de datos IngDatosXY, la carga de datos( CargaDatos(...)) , la estimación de los parámetros y los r (Estimación(...)) y el procedimiento que usará la herramienta del Excel para obtener muchos otros estimadores del análisis de regresión lineal (Herramienta()).

Abra su libro MisMacros03.xlsm y Ventas 2015.xlsm haciendo clic en cada uno de los siguientes enlaces:

Mis Macros.xlsm

Ventas 2015.xlsx
 

Vaya al editor, inserte un nuevo módulo y haga que su Name sea ModRegresion.

He aquí el procedimiento:

Dim X(100), Y(100) As Double
Dim r, R2 As Double
Dim n As Integer

Sub Regre()

fName = Application.GetOpenFilename
Workbooks.Open fName

xOp = 99
While Val(xOp) <> 0
xOp = Val(InputBox("Menú de la Regresión:" + Chr(13) + _
"Digite el número de la opción deseada" + Chr(13) + Chr(13) +
_ "0. Para terminar" + Chr(13) + _
"1. Crear la hoja e ingresar la cabecera" + Chr(13) + _
"2. Ingresar los datos a partir de la fila 3" + Chr(13) + _
"3. Realizar los cálculos de la estimación lineal" + Chr(13) +
_ "4. Usar la herramienta del Excel"))

Select Case xOp
Case 0: ActiveWorkbook.Save
Case 1: Header
Case 2: IngDatosXY
Case 3: Call CargaDatos(X, Y)
Call Estimacion(beta0, beta1)
MsgBox ("La ecuación estimada es: Y = " + Str(beta0) + " + "+Str(beta1) + "X" + _
Chr(13) + "El coeficiente de correlación es: " + Str(r) + _
Chr(13) + "El coeficiente de determinación es: " + Str(R2))

Case 4: Herramienta
Case Else:
MsgBox ("Error. Digite la opción correcta ...")
End Select

Wend

End Sub
 

Sub IngDatosXY()

Sheets("RegLineal").Activate
Range("A3").Select

n = Val(InputBox("Número de datos a procesar:"))
For iX = 1 To n
C = InputBox("Ingrese X e Y, separado por coma")
Cells(iX + 2, 1) = Val(Trim(Left(C, InStr(C, ",") - 1)))
Cells(iX + 2, 2) = Val(Trim(Mid(C, InStr(C, ",") + 1)))
Next

End Sub
 

Sub CargaDatos(X, Y)

Sheets("RegLineal").Activate
n = 0
Range("A3").Select
While Trim(ActiveCell) <> ""
n = n + 1
X(n) = Val(Cells(n + 2, 1))
Y(n) = Val(Cells(n + 2, 2))
ActiveCell.Offset(1, 0).Select
Wend

End Sub
 

Sub Estimacion(b0, b1)

For i = 1 To n
Sx = Sx + X(i)
Sy = Sy + Y(i)
Sx2 = Sx2 + X(i) * X(i)
Sxy = Sxy + X(i) * Y(i)
Sy2 = Sy2 + Y(i) * Y(i)
Next

b1 = (n * Sxy - Sx * Sy) / (n * Sx2 - Sx * Sx)
xProm = Sx / n
yProm = Sy / n
b0 = yProm - b1 * xProm
Dx = Sqr((Sx2 - n * xProm * xProm) / (n - 1))
Dy = Sqr((Sy2 - n * yProm * yProm) / (n - 1))
r = (n * Sxy - Sx * Sy) / Sqr((n * Sx2 - Sx * Sx) * (n * Sy2 - Sy * Sy))
' r = (n * Sxy - Sx * Sy) / (n*n*Dx * Dy)
R2 = r * r

End Sub
 

Sub Header()

xKey = 0
For i = 1 To Sheets.Count
If Sheets(i).Name = "RegLineal" Then
xKey = 1
End If
Next

If xKey = 0 Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "RegLineal"
Range("A1").Select
ActiveCell = "Estimación de parámetros en un modelo de regresión lineal"
Range("A1:E1").Merge
Range("A2") = "X"
Range("B2") = "Y"
Range("A2:B2").Justify
End If
Sheets("RegLineal").Activate

End Sub
 

Sub Herramienta()

Sheets("RegLineal").Select
'
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$B$2:$B$12"), _
ActiveSheet.Range("$A$2:$A$12"), False, True, , "", False, False, False _
, False, , False

Range("A:A").ColumnWidth = 30
Range("B:G").ColumnWidth = 20

End Sub



Nota:
 

Las opciones del menú pueden ejecutarse independiente uno de otro:

Se puede crear solamente y terminar (digitando 0 para terminar)
Se puede ingresar los datos solamente y terminar
Se puede ejecutar usando la opción 3 si ya los datos han sido ingresados

Cada vez que se usa la opción 0 para terminar, guarda el libro Ventas 2015.xlsx

En la siguiente imagen se tiene una parte de este procedimiento.

Vaya a la siguiente sesión haciendo clic aquí