Bienvenido a la mayor comunidad de usuarios EXCEL

Regístrate ahora y podrás: / 💪 Hacer preguntas a los expertos / ⬇️ Descargar ejemplos y plantillas / 🏅 Acceder a contenidos premium

Regístrate gratis

Distribución equitativa con stock inicial. Macro y añadir pesos.

picadeta

New member
Hola a todos. Me llamo Oscar y soy un fanático del Excel, autodidacta y con conocimientos básicos de VBA, que utilizo para mi trabajo y para intentar acelerar los procesos de cada persona que me pide consejo, siempre gratuitamente. Lo que recibes gratis dadlo gratis, ¡¡¡es mi lema!!!

Tengo una hoja que me resuelve la distribución del stock de un artículo entre almacenes diferentes, dado también un stock inicial de cada almacén. El caso es que lo resuelve bastante bien, pero lo calculo mediante una macro, copiando el stock a repartir en la celda C7 y el stock inicial de cada almacén en E7: M7. El resultado, después de las iteraciones de abajo, está en E9:M9. Como veréis, el cálculo funciona muy bien para un artículo, pero cuando quiero realizar esta distribución para alguna decena de ellos, la macro se vuelve muuuy lenta debido a que funciona copiando valores iniciales y recogiendo valores finales, de uno en uno, y traspasándolos a la hoja de resultados, que no es la misma que ésta de trabajo. Unos 20 artículos puede costarle más de 30 segundos...


Me ayudaría mucho si alguna mente más lúcida que la mía pudiera "traducir" lo que realiza esta hoja a código VBA y que fuera realmente rápido, ya que esta tarea la debo realizar cientos de veces al día con la consecuente pérdida de tiempo...

Y para poner un reto más y que a mí me salvaría la vida. Si os fijáis, arriba tengo una opción para dejar de repartir en algún almacén si no me hiciera falta, poniendo la cantidad inicial muy alta (en 100.000). También funciona bien. Pero me falta poder repartir por pesos de venta. No todos los artículos se venden igual en cada almacén, por lo que me interesaría que ese 100%, si lo cambiáramos a 120%, por ejemplo, repartiera a ese almacén un 20% más de producto que al que está al 100%, o si pone 80%, repartiera un 20% menos. Reto importante pero para quien tenga claro el concepto, seguro que apasionante. Gracias de antemano.
 

Gabriel Raigosa

Well-known member
Hola,

Aun no tengo la suficiente "lucidez" para proponer una solución a la consulta.

Se me ocurre que el método actual para repartir podria cambiarse por algo diferente.

Excel tiene una herramienta llamada Solver, la que se debe habilitar, está entre los complementos deshabilitados.

Por ahora solo voy a centrar mi atención en como repartir las 250 unidades en los 9 locales, obteniendo la minima desviación estándar del resultado final, suma de productos en existencia + suma de productos repartidos:

1617557044959.png

Creo un modelo con solver:

1617557105960.png

Se trata de obtener la minima desviación del "FIN DEL REPARTO", se establecen las restriciones y las celdas variables, para obtener una celda objetivo.

Lo que sigue después es estudiar como se establecerian las restricciones para los almacenes a los que NO se entregarian productos en un momento dado.

Adjunto libro con un ejemplo de la solución con solver, lo que elimina todo esto:

1617557344521.png

Saludos.
 

Adjuntos

  • DISTRIBUCIÓN EQUITATIVA CON STOCK INICIAL - Solver.xlsx
    57,7 KB · Visitas: 8

picadeta

New member
Gracias por tu rápida respuesta, es una solución muy bien pensada. Pero no es eficiente, tendría que arrancar Solver cada vez para cada artículo a repartir. Te adjunto un escenario real en el que en la primera hoja tengo una serie de artículos a repartir y las casillas vacías donde debería estar el reparto final de esa entrada de producto que se consigue tanto con Solver como con mi solución. Las dos requieren macro de copia pega, pero además en tu solución requiere arrancar Solver desde la macro para cada artículo, por lo que veo la solución más lenta que copiar y pegar sin arrancar solver. Mi idea es poder generar una solución a través de un For-next, un while o mediante matrices, pero ahí me pierdo.

1617564994575.png

El mayor problema es que el stock de cada almacén no se puede "tocar", ya que están en lugares diferentes. Si no la solución es muy fácil, sumar y dividir, en este caso, entre 9, y asignar el residuo por orden.

Además no hemos contemplado el reparto con pesos, y sería ideal poder tenerlo controlado. Por eso hablaba de una mente lúcida que vea cómo repartir este stock con algún método VBA. La verdad es que este problema creo que podría resolver a mucha gente que, como yo, trabaja en almacenes y debe distribuir producto entre otros almacenes o tiendas. Estoy seguro que no estamos dando con la solución lógica y fácil, yo seguro por embozamiento de cabeza...

Muchas gracias de antemano!!!
 

Adjuntos

  • DISTRIBUCIÓN EQUITATIVA CON STOCK INICIAL - Solver ESCENARIO REAL.xlsx
    64,5 KB · Visitas: 7

Gabriel Raigosa

Well-known member
Hola,

Al parecer si es mejor usar VBA, habria que crear algun tipo de rutina que cambiara los parametros del solver para cada fila, y de esta forma obtener la minima desviación para cada producto.

Por lo pronto sigo como observador 👀.

Saludos.
 

picadeta

New member
No veo la Macro que mencionas?

Quedaria así?

Ver archivo adjunto 66816
Saludos.


Adjunto la macro que uso para copiar pegar los resultados. Como veis, aun faltaría implementar los pesos por almacén para no repartir igual a cada uno de ellos sino en función, como digo más arriba, del peso que tenga cada almacén. Es una forma que funciona pero que no es eficiente, ya que voy asignando por iteraciones, por paquetes de unidades que se van reduciendo hasta alcanzar la cantidad final, pero que no admite pesos y que seguro hay alguna manera más rápida de hacerlo. Aun así, esta hoja les puede servir a muchos que deban repartir entre gente, o lugares, ciertas cantidades lo más equitativamente posible y sin tocar lo que cada uno de los lugares o de las personas ya tenía.

Averiguar esto me ha costado meses de trabajo, dados mis pocos conocimientos, y creo que es de ser agradecido compartirlo con vosotros ya que yo me he nutrido también de estos foros.

Así que si terminamos la faena con una buena macro VBA que extienda el potencial de este reparto equitativo, creo que tendremos una hoja de trabajo que servirá a muchos.
 

Adjuntos

  • DISTRIBUCIÓN EQUITATIVA CON STOCK INICIAL Y MACRO.xlsm
    100 KB · Visitas: 4

Leopoldo Blancas

Well-known member
Bueno, esta macro te reduce la mitad de tiempo en ejecutarse.

Código:
Sub DistibucionAlmacenes_LBV()   ' 0.5 seg.
Dim n&, t#
    t = Timer
    Application.ScreenUpdating = False
    For n = 5 To Hoja1.Range("B" & Rows.Count).End(xlUp).Row
        Sheet2.Range("C7") = Hoja1.Cells(n, "C")
        Hoja1.Range("D" & n & ":" & "L" & n).Copy
        Sheet2.Range("E7:M7").PasteSpecial Paste:=xlPasteValues
        Sheet2.Range("E9:M9").Copy
        Hoja1.Range("M" & n & ":" & "U" & n).PasteSpecial Paste:=xlPasteValues
    Next n
    Application.ScreenUpdating = True
    MsgBox Timer - t
End Sub


Y no entiendo lo de los "Pesos", que quieres decir?

Saludos.
 

picadeta

New member
Bueno, esta macro te reduce la mitad de tiempo en ejecutarse.

Código:
Sub DistibucionAlmacenes_LBV()   ' 0.5 seg.
Dim n&, t#
    t = Timer
    Application.ScreenUpdating = False
    For n = 5 To Hoja1.Range("B" & Rows.Count).End(xlUp).Row
        Sheet2.Range("C7") = Hoja1.Cells(n, "C")
        Hoja1.Range("D" & n & ":" & "L" & n).Copy
        Sheet2.Range("E7:M7").PasteSpecial Paste:=xlPasteValues
        Sheet2.Range("E9:M9").Copy
        Hoja1.Range("M" & n & ":" & "U" & n).PasteSpecial Paste:=xlPasteValues
    Next n
    Application.ScreenUpdating = True
    MsgBox Timer - t
End Sub


Y no entiendo lo de los "Pesos", que quieres decir?

Saludos.
Gracias Leopoldo. Esta macro está más depurada que la mía. Sólo una cosa, la expresión For n = 5 To Hoja1.Range("B" & Rows.Count).End(xlUp).Row debo cambiarla por mi NFILAS, ya que así para cuando no quedan artículos por repartir aunque la tabla tenga más filas, si no sigue hasta el final de la tabla. El resto, mucho más rápido, cierto.

Lo de los pesos es que, por ejemplo, si pone 120% es que quiero repartir a ese centro un 20% más que al resto, y si pone 90%, es que quiero repartirle un 10% menos a ese centro. Claro que esto trastoca mi hoja de distribución, porque no está contemplado, pero si se os ocurre la forma de tratarlo matemáticamente, en vez de con iteraciones, seguro que se le pueden aplicar pesos a cada centro.

Lo dicho, muchas gracias!!!
 

picadeta

New member
Gracias Leopoldo. Esta macro está más depurada que la mía. Sólo una cosa, la expresión For n = 5 To Hoja1.Range("B" & Rows.Count).End(xlUp).Row debo cambiarla por mi NFILAS, ya que así para cuando no quedan artículos por repartir aunque la tabla tenga más filas, si no sigue hasta el final de la tabla. El resto, mucho más rápido, cierto.

Lo de los pesos es que, por ejemplo, si pone 120% es que quiero repartir a ese centro un 20% más que al resto, y si pone 90%, es que quiero repartirle un 10% menos a ese centro. Claro que esto trastoca mi hoja de distribución, porque no está contemplado, pero si se os ocurre la forma de tratarlo matemáticamente, en vez de con iteraciones, seguro que se le pueden aplicar pesos a cada centro.

Lo dicho, muchas gracias!!!
De todas formas, Leopoldo, más o menos tardan lo mismo la antigua Macro y la que tú propones. Incluso la mía, no sé por qué, algo más rápida, siendo "a la antigua"...y esto sí que no lo cazo...te adjunto el archivo con las dos marcos para que lo compruebes.
 

Adjuntos

  • DISTRIBUCIÓN EQUITATIVA CON STOCK INICIAL Y MACRO.xlsm
    101,4 KB · Visitas: 5

Leopoldo Blancas

Well-known member
De todas formas, Leopoldo, más o menos tardan lo mismo la antigua Macro y la que tú propones. Incluso la mía, no sé por qué, algo más rápida, siendo "a la antigua"...y esto sí que no lo cazo...te adjunto el archivo con las dos marcos para que lo compruebes.
Algo pasa en la MATRIX, efectivamente tardan casi lo mismo en este archivo 0.8 segundos, pero en el anterior tu macro tarda 0.94 segundos y en el mio tarda 0.51 segundos.

Bueno bueno... qué le vamos a hacer.

También te comento que estoy en una PC lenta, Básica, si lo hago en mi otra Laptop, que si es rápida, casi tarda la mitad de tiempo.

Saludos.
 

Leopoldo Blancas

Well-known member
Lo de los pesos es que, por ejemplo, si pone 120% es que quiero repartir a ese centro un 20% más que al resto, y si pone 90%, es que quiero repartirle un 10% menos a ese centro. Claro que esto trastoca mi hoja de distribución, porque no está contemplado, pero si se os ocurre la forma de tratarlo matemáticamente, en vez de con iteraciones, seguro que se le pueden aplicar pesos a cada centro.
En alguna ocasión lo realice, pero tengo que darle vueltas a la "Jaula de la Ardilla" para recordar... tiene muchos años que realice algo semejante, por la producción de diferentes máquinas se les surtía diferente cantidad de materia prima, ya que a pesar de ser "iguales" no producían la misma cantidad en un día. Y para no tener mucha materia prima sin ocupar al final del día, se trataba de ser justo.

Saludos.
 

Lacayo

Member
Hola a todos,

Poco os voy a poder ayudar en este tema ya que todavía estoy en la fase de pedir mucha ayuda.

Picadeta, respecto al tema de la lentitud en la macro, bajo mi opinión, se debe a que mezclas las fórmulas de tu hoja con las macros. Cada vez que la macro actualiza una celda, se recalculan TODAS las fórmulas de la hoja. Esto hace que, en el ejemplo, la macro se mueva relativamente bien pero en tu modelo real se te irán los tiempos. En algunos caos se puede mejorar el rendimiento de la macro añadiendo "Application.Calculation = xlCalculationManual" al inicio del módulo y Application.Calculation = xlCalculationAutomatic al final del mismo.

En tu caso, como la macro depende del cálculo que realizas mediante fórmulas en la misma hoja, este sistema no te funcionará.
La recomendación es que hagas todos los cálculos en la macro.

Te paso un ejemplo, que hay que mejorar, ya que en alguna línea aparece un almacén de más. No sé por qué y no consigo corregirlo. Pero para que veas la idea y compares tiempos de carga te puede servir. Tampoco domino mucho las matrices.

Y, si de paso, alguien descubre el gazapo y lo repara, pues eso que nos llevamos tu y yo

Respecto al tema de los "pesos" no puedo ayudarte. Lo veo más factible a través de la macro pero me falta base matemática para realizar las operaciones necesarias.


Saludos
 

Adjuntos

  • DISTRIBUCIÓN EQUITATIVA CON STOCK INICIAL Y MACRO.xlsm
    108,4 KB · Visitas: 11

picadeta

New member
Hola a todos,

Poco os voy a poder ayudar en este tema ya que todavía estoy en la fase de pedir mucha ayuda.

Picadeta, respecto al tema de la lentitud en la macro, bajo mi opinión, se debe a que mezclas las fórmulas de tu hoja con las macros. Cada vez que la macro actualiza una celda, se recalculan TODAS las fórmulas de la hoja. Esto hace que, en el ejemplo, la macro se mueva relativamente bien pero en tu modelo real se te irán los tiempos. En algunos caos se puede mejorar el rendimiento de la macro añadiendo "Application.Calculation = xlCalculationManual" al inicio del módulo y Application.Calculation = xlCalculationAutomatic al final del mismo.

En tu caso, como la macro depende del cálculo que realizas mediante fórmulas en la misma hoja, este sistema no te funcionará.
La recomendación es que hagas todos los cálculos en la macro.

Te paso un ejemplo, que hay que mejorar, ya que en alguna línea aparece un almacén de más. No sé por qué y no consigo corregirlo. Pero para que veas la idea y compares tiempos de carga te puede servir. Tampoco domino mucho las matrices.

Y, si de paso, alguien descubre el gazapo y lo repara, pues eso que nos llevamos tu y yo

Respecto al tema de los "pesos" no puedo ayudarte. Lo veo más factible a través de la macro pero me falta base matemática para realizar las operaciones necesarias.


Saludos
Hola Lacayo. LA macro funciona muy bien, el tema de las matrices acelera una barbaridad el escenario.

He encontrado el "gazapo", simplemente cambiando esto añadiendo un -1: ReDim m(0 To Range(R(x, 3), R(x, 11)).Count - 1). Estabas creando 10 filas en la matriz cuando eran 9. A no ser que la décima fila la uses para algo.

Por otro lado, veo que vas repartiendo "trozos" del reparto a la cantidad mínima de la matriz. Pero siempre se queda sin repartir alguna cantidad, el residuo de la división, parece ser. Me encantaría entender tanto de matrices como tú y arreglarlo yo, pero me pierdo cuando llamas al procedimiento "repartos".

Si bien no hemos avanzado en los pesos para repartir, me encantaría poder integrar tu macro porque es con diferencia más rápida que ninguna, pero necesito que reparta TODA la cantidad que llega, aunque el resto se lo asignemos de uno en uno a cada centro por orden.

¿Me ayudas? Gracias de antemano.
 

Leopoldo Blancas

Well-known member
Hola Lacayo. LA macro funciona muy bien, el tema de las matrices acelera una barbaridad el escenario.
Si, las Matrices o Arrays son muy rápidos.

Por otro lado, veo que vas repartiendo "trozos" del reparto a la cantidad mínima de la matriz. Pero siempre se queda sin repartir alguna cantidad, el residuo de la división, parece ser. Me encantaría entender tanto de matrices como tú y arreglarlo yo, pero me pierdo cuando llamas al procedimiento "repartos".
Si, hay un sobrante, eso lo vemos en la semana, solo mire de reojo el archivo de Lacayo.

Para Lacayo (Solo un breviario cultural):

1.- Si son varias variables del mismo tipo en una sola línea... todas se declaran: Dim i As integer, j As Integer, n As Integer.
Si lo haces así: Dim i , j , n As Integer , La i y j quedan como Variant y solo la n como Integer.
2.- No uses Select, con referencia a Hojas y Rangos es suficiente.

Nota: En su momento a mi también me hicieron estas observaciones, ya que venimos de programar en otros Lenguajes y así nos acostumbramos.

Código:
Sub valores()
    Dim x%, y%, z%, reparto%
    Dim R As Range, S As Range
    Dim m() As Variant
    Dim t1#
    Application.ScreenUpdating = False
    t1 = Timer
    Hoja3.Range("a2:l100").ClearContents
    Set R = Hoja1.Range("repartir")
    z = Hoja1.Range("b1")
    For x = 1 To z
        reparto = R(x, 2)
        ReDim m(0 To Hoja1.Range(R(x, 3), R(x, 11)).Count)
        y = 0
        For Each S In Hoja1.Range(R(x, 3), R(x, 11))
            m(y) = S
            y = y + 1
        Next S
        Call repartos(reparto, m(), z, x)
    Next
    Application.ScreenUpdating = True
    MsgBox Timer - t1
End Sub

Sub repartos(reparto As Integer, almacen() As Variant, z As Integer, x As Integer)
    Dim min%, valor%, i%, parte%
    'Reparto hace referencia a la cantidad total a repartir
    'Parte hace referencia a la parte propocional.
    Do Until reparto = 0
        min = WorksheetFunction.min(almacen)
        For i = 0 To UBound(almacen)
            If almacen(i) = min Then
                parte = WorksheetFunction.Quotient(reparto, 9)
                valor = almacen(i)
                almacen(i) = valor + parte
                reparto = reparto - parte
                If parte = 0 Then Exit Do
            End If
        Next
    Loop
    For i = 0 To UBound(almacen)
        Hoja3.Cells(x + 1, i + 1) = almacen(i)
    Next i
End Sub




Saludos.
 
Última edición:

Leopoldo Blancas

Well-known member
me encantaría poder integrar tu macro
Que paso amigo... si sabes algo de VBA no te será difícil adaptarlo... OJO, fijate en donde lo escribe, en qué celdas... ya tu solamente cambias donde quieras que lo escribas... echale, tu puedes. Ya te ayudo mucho y ya no es difícil cambiar el código del amigo y hacer que no sobre nada, pero pon tus 20 centavos.

Saludos.
 

picadeta

New member
Que paso amigo... si sabes algo de VBA no te será difícil adaptarlo... OJO, fijate en donde lo escribe, en qué celdas... ya tu solamente cambias donde quieras que lo escribas... echale, tu puedes. Ya te ayudo mucho y ya no es difícil cambiar el código del amigo y hacer que no sobre nada, pero pon tus 20 centavos.

Saludos.
Algo se, de hecho he encontrado el gazapo... Y no es por las referencias, que tengo claro el cambiarlas a mi hoja final. Era por lo de los pesos. Y porque no reparte la cantidad entera, queda un residuo por repartir.

De todas maneras, esto es un no parar, en vez de ver películas en mis ratos libres me dedico al VBA, mi mujer se piensa que estoy loco...pero el gusanillo está ya metido en mi cabeza y no pararé hasta comprender el 100% de las macros que vosotros tan fácilmente creáis y que a mí me cuestan sudor y lágrimas...

Gracias por todo.
 
Última edición:

Temas similares

Arriba