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 Comment

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

One Comment

  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 un comentario

Required fields are marked *.


Excel Avanzado located at , Lima, Perú . Reviewed by usuarios rated: 4.7 / 5