Excel Avanzado

Macros, Vba en Excel y muchos ejemplos de nuestro Curso de Excel Avanzado

Excel Avanzado

Ejemplo de Do loop para establecer un resultante

| 1 comentario

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

2012 09 22 19 53 50 1312 300x104 Ejemplo de Do loop para establecer un resultante

(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 t%c3%adtulo Ejemplo de Do loop para establecer un resultante

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"

324 Ejemplo de Do loop para establecer un resultante

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

Un comentario

  1. 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.

Deja una respuesta

Los campos requeridos estan marcados con *.