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.
 

picadeta

New member
Os paso la versión actual con los cambios. SI os fijáis:

1618134836377.png

Falta siempre por repartir 8 unidades, el residuo de la división entre 9. Pero claro, fijaos por ejemplo en el artículo 6 del que sólo hay que repartir 8, no lo reparte por ser menor que 9.

Está claro que haría falta repartir, aunque fuera por orden de matriz, el residuo de la división. Si no no está completo el reparto.

Sigo dándole vueltas al tema de los pesos. Si una vez hecho el reparto, aplicáramos los pesos, y decidiéramos repartir de esa manera, sólo habría que comprobar que el reparto no fuera de menor cantidad que lo que ya tenía cada almacén (ya que no pueden mover mercancía entre sí). Si fuera así, habría que restar esta cantidad de algún centro para repartirle menos...No es la mejor idea matemática, seguro, pero podría funcionar.

Un saludo desde Valencia, España.
 

Adjuntos

  • DISTRIBUCIÓN EQUITATIVA CON STOCK INICIAL Y MACRO VERSIÓN 11-4-21.xlsm
    127,4 KB · Visitas: 3
Última edición:

Leopoldo Blancas

Well-known member
mi mujer se piensa que estoy loco...
Sí estas loco... jajaja
y que a mí me cuestan sudor y lágrimas...
Ya ya ... no es para tanto...



Entrale a las Macros... no te arrepentiras...



CON ESTA MACRO "repartos" YA NO SOBRA NADA.



Bueno ya que para matar unos minutos el aburrimiento...



Sub repartos(reparto%, almacen() As Variant, x%)

Dim min%, i%, parte%, nSobra%

' Hace un Do Until que ya no entra cuando el reparto sea igual a 0

Do Until reparto = 0

min = WorksheetFunction.min(almacen) ' Determina cual es la cantidad mínima

For i = 0 To UBound(almacen) ' hace un For de cada almacén para encontrar la cantidad mínima

If almacen(i) = min Then ' Si el almacén i es igual ala cantidad mínima entra al IF

parte = WorksheetFunction.Quotient(reparto, 9) ' Calcula la parte Entera de Dividir lo repartido entre 9

nSobra = reparto Mod 9 ' Calcula lo que sobra de la división

If parte > 0 Then' Si parte es mayor que 0 entra a este otro IF

almacen(i) = almacen(i) + parte ' Se lo suma al Almacén

reparto = reparto - parte ' Lo resta "parte" de reparto

ElseIf parte = 0 And nSobra >= 1 Then ' Si parte = 0 y nSobra es mayor o igual a 1

almacen(i) = almacen(i) + 1 ' Se lo suma al Almacén un 1

reparto = reparto - 1 ' Se lo resta de Reparto

End If

Exit For ' Aqui obligamos a Salir al For Nex, para que determine ahora cual es el almacén con el minimo

End If

Next i

Loop ' Regresa al inicio para saber si reparto = 0, cuando es = 0 ya no entra al Do Until

' Escribe en la hoja lo que hay en la Matriz o Array Almacén


For i = 0 To UBound(almacen)

Hoja3.Cells(x + 1, i + 1) = almacen(i)

Next i

End Sub


Saludos.
 

picadeta

New member
POr eso reparti de 1 en 1 cuando es menor que 9... :cool:
Muchas gracias. Estoy encantado con la solución. Creo que servirá para muchos. Hay que darle una vuelta a lo de los pesos, pero ahora mismo integro esta solución en mi hoja real.

La verdad es que el mundo VBA me atrae mucho, me faltan conocimientos básicos pero ya hago mis macros básicas/medias de seguido. Pero me ha picado mucho este tema de las matrices porque veo que se avanza mucho cuando tratas con tablas de datos, y eso es lo que yo hago normalmente.

Un saludo y agradecido.
 

picadeta

New member
Una cosa Leopoldo. La macro ya la he integrado en mi hoja de trabajo y funciona de maravilla. Pero me pasa una cosa curiosa...Cuando enciendo el ordenador y ejecuto la macro, funciona. Pero cuando abro otro libro de excel (con macros también) y luego vuelvo a la hoja de distribución y ejecuto la macro...se queda pensando, va muy lenta y debo forzar la salida de la macro y siembre está parada en el Next i del Sub Repartos...no entiendo si es que no se borran todos los parámetros de antes o qué narices pasa, pero se queda supercolgada. Si reinicio el ordenador y vuelvo a abrir primero la hoja de distribución, funciona de nuevo a la perfección...

Se queda colgado poniendo los valores en la hoja de trabajo, como te digo, en el bucle for next, devolviendo los datos calculados.
 

Leopoldo Blancas

Well-known member
A lo mejor la dejas sin saber a donde está y para donde va... :ROFLMAO: :ROFLMAO: :ROFLMAO:

Pon esto después de t=Timer...: Workbooks("DISTRIBUCIÓN EQUITATIVA CON STOCK INICIAL Y MACRO_LBV.xlsm").Activate

Workbooks("Nombre del Archivo").Activate

Y tambien fijate en los Nombres Internos de las hojas, si en el otro archivo son diferentes, entonces tienes que cambiarlas...

Hoja1...Hoja3.... es la misma en el otro archivo? Hay más ligares donde se hace referencia a los Nombres internos de las Hojas, La imagen solo lo muestro como ejemplo.

1618300229115.png
 

picadeta

New member
A lo mejor la dejas sin saber a donde está y para donde va... :ROFLMAO: :ROFLMAO: :ROFLMAO:

Pon esto después de t=Timer...: Workbooks("DISTRIBUCIÓN EQUITATIVA CON STOCK INICIAL Y MACRO_LBV.xlsm").Activate

Workbooks("Nombre del Archivo").Activate

Y tambien fijate en los Nombres Internos de las hojas, si en el otro archivo son diferentes, entonces tienes que cambiarlas...

Hoja1...Hoja3.... es la misma en el otro archivo? Hay más ligares donde se hace referencia a los Nombres internos de las Hojas, La imagen solo lo muestro como ejemplo.

Ver archivo adjunto 67107
Está todo bien cambiado, los nombres de las hojas y demás. Y funciona a la perfección mientras solo tengo abierta una hoja excel. Pero abro otra Ademásy se queda colgada...
1618342302412.png

Además, se queda colgado a mitad de escribir los datos en la hoja de trabajo:
1618342440522.png

He puesto a repartir 500 unidades en cada línea y hay unas 30 líneas, llega a la línea 8 y se queda pillado...ni idea de lo que puede ser. Pero lo dicho, sólo cuando tengo abierta otra hoja. Voy a probar a activar el libro cada vez, tardará más, pero...
 

picadeta

New member
Está todo bien cambiado, los nombres de las hojas y demás. Y funciona a la perfección mientras solo tengo abierta una hoja excel. Pero abro otra Ademásy se queda colgada...
Ver archivo adjunto 67112

Además, se queda colgado a mitad de escribir los datos en la hoja de trabajo:
Ver archivo adjunto 67113

He puesto a repartir 500 unidades en cada línea y hay unas 30 líneas, llega a la línea 8 y se queda pillado...ni idea de lo que puede ser. Pero lo dicho, sólo cuando tengo abierta otra hoja. Voy a probar a activar el libro cada vez, tardará más, pero...
Rectifico. Cuando abro otra hoja excel, en vez de tardar menos de 2 segundos tarda más de un minuto. No se queda colgado, es que yo lo paraba. Pero...¿por qué?
 

Lacayo

Member
Hola de nuevo,

He llevado una semana intensa y no me he podido seguir la evolución del problema.

No sé como abrir varias citas (como lo hace Leopoldo) así que voy a intentar responder a todo.

Leopoldo, gracias por los apuntes. Lo tendré en cuenta a futuro.

Picadeta,

Respecto al gazapo, me alegro que lo localizaras. Y respecto al problema de la diferencia en el reparto, intenté usar la misma fórmula que tú usaste en la Excel (cociente) . Sí que vi que no cuadraba bien pero esperaba que al corregir el gazapo se solucionara sólo.

De nuevo a Lepoldo, gracias, me apunto esa solución también 😊.

Con relación a la lentitud si hay varias hojas de excel abiertas, parece que le problema está en que, o bien no sabe hacia donde apuntar (habría que activar el libro) o bien hace cálculos de más.

A mi me pasa si tengo hojas abiertas con fórmulas. Lo que he hecho es lo que te comenté en mi entrada anterior: parar los cálculos hasta que se ejecute la macro. En el adjunto lo he agregado. A ver si así te es funcional. Si no lo es, no te puedo dar otra solución a corto plazo.

Le he dado una vuelta a lo de los pesos. Mi base matemática es bastante básica pero parece que con una simple "regla de tres" se puede hacer algo parecido a lo que buscas

1618391886709.png


Resumiendo un poco, las celdas coloreadas contienen las fórmulas. Lo que hago es aplicar un porcentaje en función de cómo hayas distribuido el reparto (un 100, un 80, 120, ...). Después multiplico la cantidad a repartir por el porcentaje y lo sumo a la cantidad original.

He agregado una lista de validación para que la búsqueda por artículo sea más sencilla.

Hay dos problemas que haya detectado:

He usado la fórmula "redondear" para distribuir los artículos. En algún caso se pierde 1 unidad por el camino. Si no uso redondear, Excel usa decimales y tampoco se muestra esa unidad.

El segundo problema afecta al 100%. Si se condiciona el reparto ese 100% se convierte en un 101%, 99%, ... no afecta mucho pero pierde la condicionalidad de mantenerse neutral.

Si el sistema te convence, habría que agregarlo a una macro

yo veo dos posibilidades:

La primera es terminar lo que te he preparado: Trabajar artículo por artículo. Así tienes pleno control sobre el reparto por artículo y almacén

Lo suyo sería crear una macro que copie la fila del reparto condicionado donde corresponda

La segunda posibilidad es una macro semi-automática. Crear una tabla que te muestre todos los valores de los artículos y así poder hacer control de errores o balanceos de última hora.
Antes de volcar los resultados que se calculan en la sub repartos, habría que multiplicarlos por el porcentaje calculado en B2, B3, ...

Después, habría que copiar los resultados de esa tabla a su ubicación final

El desarrollo te lo dejo a ti.

Espero que algo de todo esto te sea útil

Saludos
 

Adjuntos

  • DISTRIBUCIÓN EQUITATIVA.xlsm
    111,3 KB · Visitas: 5

picadeta

New member
Hola de nuevo,

He llevado una semana intensa y no me he podido seguir la evolución del problema.

No sé como abrir varias citas (como lo hace Leopoldo) así que voy a intentar responder a todo.

Leopoldo, gracias por los apuntes. Lo tendré en cuenta a futuro.

Picadeta,

Respecto al gazapo, me alegro que lo localizaras. Y respecto al problema de la diferencia en el reparto, intenté usar la misma fórmula que tú usaste en la Excel (cociente) . Sí que vi que no cuadraba bien pero esperaba que al corregir el gazapo se solucionara sólo.

De nuevo a Lepoldo, gracias, me apunto esa solución también 😊.

Con relación a la lentitud si hay varias hojas de excel abiertas, parece que le problema está en que, o bien no sabe hacia donde apuntar (habría que activar el libro) o bien hace cálculos de más.

A mi me pasa si tengo hojas abiertas con fórmulas. Lo que he hecho es lo que te comenté en mi entrada anterior: parar los cálculos hasta que se ejecute la macro. En el adjunto lo he agregado. A ver si así te es funcional. Si no lo es, no te puedo dar otra solución a corto plazo.

Le he dado una vuelta a lo de los pesos. Mi base matemática es bastante básica pero parece que con una simple "regla de tres" se puede hacer algo parecido a lo que buscas

Ver archivo adjunto 67139


Resumiendo un poco, las celdas coloreadas contienen las fórmulas. Lo que hago es aplicar un porcentaje en función de cómo hayas distribuido el reparto (un 100, un 80, 120, ...). Después multiplico la cantidad a repartir por el porcentaje y lo sumo a la cantidad original.

He agregado una lista de validación para que la búsqueda por artículo sea más sencilla.

Hay dos problemas que haya detectado:

He usado la fórmula "redondear" para distribuir los artículos. En algún caso se pierde 1 unidad por el camino. Si no uso redondear, Excel usa decimales y tampoco se muestra esa unidad.

El segundo problema afecta al 100%. Si se condiciona el reparto ese 100% se convierte en un 101%, 99%, ... no afecta mucho pero pierde la condicionalidad de mantenerse neutral.

Si el sistema te convence, habría que agregarlo a una macro

yo veo dos posibilidades:

La primera es terminar lo que te he preparado: Trabajar artículo por artículo. Así tienes pleno control sobre el reparto por artículo y almacén

Lo suyo sería crear una macro que copie la fila del reparto condicionado donde corresponda

La segunda posibilidad es una macro semi-automática. Crear una tabla que te muestre todos los valores de los artículos y así poder hacer control de errores o balanceos de última hora.
Antes de volcar los resultados que se calculan en la sub repartos, habría que multiplicarlos por el porcentaje calculado en B2, B3, ...

Después, habría que copiar los resultados de esa tabla a su ubicación final

El desarrollo te lo dejo a ti.

Espero que algo de todo esto te sea útil

Saludos
Perfecto, le voy a echar un vistazo.

Con respecto a los cuelgues, efectivamente, era problema de desactivar los cálculos, he puesto los correspondientes activa/desactiva al inicio y al final y funcionando.

Y con respecto a los pesos, esta regla de tres es la más evidente. Sólo que debe tener en cuenta el stock inicial de cada Centro, puesto que lo que no puede hacer es restarle unidades de las que ya tiene, sólo se reparte en una dirección. Mientras contemple esto, que sobre una unidad del reparto, luego por macro se puede asignar, para que esté distribuido el 100% del reparto. Sería perfecto. Aunque ya me vale así, la de ideas que me surgen con este tipo de reparto.

Muchas gracias a los dos. Tenéis un amigo por estas latitudes!!!
 

Leopoldo Blancas

Well-known member
Hola a Todos,

Por la suerte del destino mis "Pesos" no "respondían" adecuadamente... pero observe que las cantidades están un poco dispersas una con la otra...
y me salían las cantidades iguales :mad:, pero si se cumple la distribución por porcentajes diferentes, solo basta que por ejemplo en el primer artículo el mínimo es 26 y si le ponemos a otro almacén 27 la distribución por porcentajes se cumple, las cantidades ya no salen igual. Observar los valores que van tomando los almacenes en cada reparto. Observen de la fila 51 en adelante en la hoja "ejemplo".

O si cambiamos los porcentajes también lo que se reparte cambia. Cambiar los porcentajes en la Hoja Reparto.

Ahora si que todo va relacionado:

- La Cantidad a repartir
- Los almacenes que intervienen en la repartición
- Los porcentajes por Almacén

Por ejemplo, da las mismas cantidades en el Artículo 1 por qué solo 4 almacenes intervienen en la repartición y aunque variemos los porcentajes un poco, veremos que si cambian las cantidades a repartir, pero como solo son 4 almacenes entonces se reparten en las mismas cantidades. O será otra forma de resolverlo. Si fueran más almacenes o las cantidades muy cercanas entre los almacenes se vería más claro la repartición.

Saludos.
 

Adjuntos

  • DISTRIBUCIÓN EQUITATIVA.xlsm
    115,8 KB · Visitas: 4

picadeta

New member
Hola a Todos,

Por la suerte del destino mis "Pesos" no "respondían" adecuadamente... pero observe que las cantidades están un poco dispersas una con la otra...
y me salían las cantidades iguales :mad:, pero si se cumple la distribución por porcentajes diferentes, solo basta que por ejemplo en el primer artículo el mínimo es 26 y si le ponemos a otro almacén 27 la distribución por porcentajes se cumple, las cantidades ya no salen igual. Observar los valores que van tomando los almacenes en cada reparto. Observen de la fila 51 en adelante en la hoja "ejemplo".

O si cambiamos los porcentajes también lo que se reparte cambia. Cambiar los porcentajes en la Hoja Reparto.

Ahora si que todo va relacionado:

- La Cantidad a repartir
- Los almacenes que intervienen en la repartición
- Los porcentajes por Almacén

Por ejemplo, da las mismas cantidades en el Artículo 1 por qué solo 4 almacenes intervienen en la repartición y aunque variemos los porcentajes un poco, veremos que si cambian las cantidades a repartir, pero como solo son 4 almacenes entonces se reparten en las mismas cantidades. O será otra forma de resolverlo. Si fueran más almacenes o las cantidades muy cercanas entre los almacenes se vería más claro la repartición.

Saludos.
Muy buen planteamiento. Aclara mucho la forma de repartir con pesos. Y es una buena base de partida.

Ahora hay que plasmarlo en modo macro, no puedo ir artículo por artículo ya que me tiraría media vida, y terminar de repartir, ya que hay artículos que se quedan con cantidades sin repartir al aplicar los pesos. Pero nada que una macro que recoja estos principios pueda resolver.

Muchas gracias!!!
 

Leopoldo Blancas

Well-known member
Ahora hay que plasmarlo en modo macro, no puedo ir artículo por artículo ya que me tiraría media vida, y terminar de repartir, ya que hay artículos que se quedan con cantidades sin repartir al aplicar los pesos. Pero nada que una macro que recoja estos principios pueda resolver.
Ya esta la macro... solo quita lo que está señalado. Solo lo puse así para que se viera que si reparte diferente si cambias los datos o los pesos.

Si hay almacenes que quedan sin repartir, los almacenes si son distantes del minimo al maximo y son pocos las cantidades a repartir no les toca... por qué se supone que todos los almacenes tienen que tener cantidades similares, casi iguales, si pones una cantidad muy grande a repartir veras que todos alcanzan cantidades semejantes.

Saludos.
 

Temas similares

Arriba