Existen Casos en lo que se puede aproximar el valor de un resultado como una función que depende de una componente constante y una componente variable. A este método de estimación se le llama método de mínimos cuadrados o MCO.
De esta manera a partir de una base de datos se pueden calcular las componente de dicha función:
y = b1 + b2 * x
y: La variable resultante
x: La variable generadora de cambio
b1: Componente Fijo
b2: Componente Fijo por Unidad
donde
(Para mayor visualización de la imagen, hacer click en la misma)
Como por ejemplo en la determinación de costos totales con una componente de costos fijos y costos variables.
De Excel se toma una base de Datos de n Componentes:
Sin importar la cantidad de datos ingresados se calculara cada uno de los componentes de la siguiente manera en Excel
Sub MCO()
'i: contador de fila'
i = 2
'Sumai: suma de resultados'
Sumai = 0
'Sumaj: suma de generadores'
Sumaj = 0
'Sumai2: suma de cuadrados'
Sumai2 = 0
'Sumaij: suma de productos'
Sumaij = 0
'Se repetira el proceso hasta que se encuentren celdas vacias'
Do While Cells(i, 1).Value <> ""
Sumai = Sumai + Cells(i, 2).Value
Sumaj = Sumaj + Cells(i, 1).Value
Sumai2 = Sumai2 + (Cells(i, 2).Value) ^ 2
Sumaij = Sumaij + ((Cells(i, 1).Value) * (Cells(i, 2).Value))
i = i + 1
Loop
'Contador de variables'
n = i - 2
'b1 componente fijo'
b1 = ((Sumai2 * Sumaj) - (Sumai * Sumaij)) / ((n * Sumai2) - (Sumai * Sumai))
'b2 componente variable'
b2 = ((n * Sumaij) - (Sumai * Sumaj)) / (n * Sumai2) - (Sumai * Sumai)
Con las componentes calculadas se decidió mostrar la ecuación mediante una ventana con la siguiente configuración en VBA:
MsgBox "La Función de Estimación es la siguiente y = " & b1 & " + " & b2 & " * x"
Cabe resaltar que cada coeficiente debe ser sometido a un examen estadístico de significancia con posterioridad
Luis Loaiza
Ejemplo de Do loop aplicado para establecer una valor resultante por medio de un función
17/02/2013 a las 12:42 pm
Me parece muy interesante esta aplicacion, pues a partir de cualquier tipo de base de datos se pueden calcular las componente de una función.
Por ejemplo, utilice la misma lógica para calcular el promedio anual de ventas de una empresa.
Sub PromedioVentasEmpresaA()
Dim sumai As Long
Dim PromedioVentasEmpresaA As Single
'i:contador de fila'
i = 2
'sumai: suma de resultados'
sumai = 0
'repertirse hasta encontrar celdas 13'
Do While Cells(i, 7).Value “13”
sumai = sumai + Cells(i, 7).Value
i = i + 1
Loop
'contador de variales'
n = i - 2
PromedioVentasEmpresaA = Round(sumai / n, 0)
ActiveSheet.Range("G14").Value = PromedioVentasEmpresaA
End Sub
Espero pueda ayudarlos a aclarar dudas.
Saludos.