¿Cómo uso el solucionador de Excel para resolver esto?

Question

ABC Corporation es un distribuidor global de piezas y componentes eléctricos. La empresa contrata la compra de componentes y piezas de Europa y los entrega en almacenes en tres puertos europeos, E1, E2, E3. Los diversos componentes y piezas se cargan en contenedores según la demanda de los clientes estadounidenses. Cada puerto tiene un número fijo limitado de contenedores disponibles cada mes. Luego, los contenedores se envían al extranjero en buques portacontenedores a los puertos de P1, P2, P3 y P4. Desde estos puertos marítimos, los contenedores normalmente se acoplan a camiones y se transportan a los puertos interiores en I1, I2 e I3. Hay un número fijo de transportistas de carga disponibles en cada puerto cada mes. Estos puertos interiores a veces se denominan “pueblos de carga” o cruces intermodales, donde los contenedores se recogen y transfieren de un modo de transporte a otro (es decir, del camión al ferrocarril y viceversa). Desde los puertos interiores, los contenedores se transportan hasta los centros de distribución de ABC en D1, D2, D3, D4 y D5. A continuación se detallan los costos de manejo y envío ($/contenedor) entre cada uno de los puntos de embarque y destino a lo largo de esta cadena de suministro en el extranjero y los contenedores disponibles en cada puerto:

hoja de excel de muestra

Una celda roja significa que la ruta particular no es una opción viable y, por lo tanto, no se puede considerar. Determine los envíos óptimos desde cada punto de embarque a cada destino a lo largo de esta cadena de suministro en el extranjero que darán como resultado un costo de envío total mínimo y el costo de envío total incurrido en cada etapa.

Answer 1

A continuación se muestra un ejemplo de cómo configurar su problema para Solver. Es posible que puedas hacerlo más compacto, pero la forma en que lo configuré es fácil de diagnosticar cualquier problema.

1. Utilice sus datos originales en una pestaña "Datos".
2. Cree una nueva pestaña, la llamé "Ecuación", para configurar el problema de Solver.
3. Las columnas A a D se utilizan para configurar todas las combinaciones posibles de rutas. por ejemplo, la fila 1 son encabezados; La fila 2 es E1, P1, I1, D1; La fila 2 es E1, P1, I1, D2; etc.
4. Las columnas E a G son los costos asociados de Europa al puerto de EE. UU., del puerto de EE. UU. al puerto interior y del puerto interior al centro de distribución. Esto se completa usando BUSCARV y COINCIDIR. por ejemplo, en E2 es =VLOOKUP(A2,Data!$A$3:$E$5,MATCH(B2,Data!$A$2:$E$2,0),FALSE); en F2 es =VLOOKUP(B2,Data!$A$11:$D$14,MATCH(C2,Data!$A$10:$D$10,0),FALSE); en G2 es =VLOOKUP(C2,Data!$A$20:$F$22,MATCH(D2,Data!$A$19:$F$19,0),FALSE). Todos estos están llenos.
5. La columna H es el costo unitario total. Para hacer obvias las rutas no viables, establecí el costo total para ellas en 1.000.000. por ejemplo, H2 es =IF(OR(E2=0,F2=0,G2=0),1000000,SUM(E2:G2)). Esto está lleno.
6. La columna I es el número de unidades (contenedores) que recorren esa ruta. Esta columna será manipulada por el solucionador. Inicialicé cada fila con 1.
7. La columna J es el costo total de la ruta. por ejemplo, J2 está =H2*I2y está lleno.
8. Se crea otra tabla en la misma pestaña para contener la función objetivo y las restricciones. Está en L1 hasta O19. Algunos ejemplos de los cálculos aquí incluyen: nUnidades son sumas de la tabla anterior, por lo que la cantidad de contenedores de E1 es =SUMIFS($I$2:$I$181,$A$2:$A$181,L2), la cantidad de contenedores de P1 es =SUMIFS($I$2:$I$181,$B$2:$B$181,L5), etc.; maxUnits se extraen de la pestaña Datos, por lo que el máximo de contenedores de E1 es =VLOOKUP(L2,Data!$A$3:$F$5,6,FALSE), el máximo de contenedores de I1 es =HLOOKUP(L9,Data!$B$10:$D$15,6,FALSE), etc.; de manera similar, las unidades de demanda se extraen de la pestaña Datos.
9. Agregué una restricción adicional para garantizar que no se seleccionen rutas no viables.
10. El objetivo es el costo total, que es la suma de la Columna J.

Aquí hay una captura de pantalla de la pestaña Ecuación, antes de ejecutar Solver. Varias filas están ocultas.

La configuración del Solver es la siguiente:

1. Objetivo establecido: es$M$19
2. Para: es min
3. Cambiando celdas variables: es$I$2:$I$181
4. Hacer que las variables sin restricciones no sean negativas no está seleccionado (esto se maneja con restricciones)
5. Seleccione un método de resolución: es evolutivo. El evolutivo es mucho más rápido que los otros métodos cuando maneja restricciones de números enteros.
6. Sujeto a las restricciones: son muchos...
   • $I$2:$I$181 = integer- debe tener números enteros de contenedores
   • $I$2:$I$181 >= 0 - no puede incurrir en costos negativos
   • $M$2 <= $N$2duplicado para cada fila para $M$11 <=$N$11no violar el número máximo de contenedores
   • $M$12 = $O$12duplicado para cada fila para $M$16 = $O$16satisfacer la demanda
   • $M$17 = $N$17- no utilice opciones no viables

A continuación se muestra una captura de pantalla del cuadro de diálogo Parámetros de Solver...

Con esta configuración, obtengo un costo total de $1,661,119.00 con toda la demanda satisfecha y sin violar ninguna restricción. A continuación se muestra una captura de pantalla de la tabla de restricciones...

A continuación se muestra una captura de pantalla de la tabla de costos, con todas las rutas cero filtradas...

Answer 2

Si desea una respuesta única, la ruta más barata tiene E2 > P4 > I2 > D2un costo de $1,452.

El método que utilicé no fue elegante. Hice una tabla de todas las opciones posibles, utilicé INDEXy MATCHpara buscar el costo de cada tramo del viaje, y luego las clasifiqué por costo total de manera ascendente. Así es como se ve mi hoja:

Los datos de la izquierda son los que copié de tu hoja de cálculo. La tabla de la derecha es la que creé. Para cada uno de los campos E, P, I y D, los escribí manualmente en grupos. D1, D2, D3, D4, D5. Copia esos cinco y pégalos mucho. I1, copie hacia abajo para un conjunto de D, I2, copie hacia abajo para un conjunto de D, etc. hasta que se llenó la tabla. Para el campo $EP, combiné INDEXy MATCHbusqué el costo de viajar de E(lo que sea) a P(lo que sea).

=1/(1/INDEX($B$3:$E$5,MATCH([@E],$A$3:$A$5,0),MATCH([@P],$B$2:$E$2,0)))

INDEXtoma una matriz y devuelve una celda desde algún lugar dentro de esa matriz. Para encontrar la fila que quería, solía MATCHencontrar dónde estaba el valor E en el rango A3:A5. Para encontrar la columna, busqué el valor P en B2:B4. Eso me dio el costo de pasar de E a P. Agregué el 1/(1/...)envoltorio alrededor de todo para que devolviera un error si el tramo del viaje fuera cero, lo que indica que no estaba disponible.

Luego copié esa fórmula en los campos $PI y $ID, ajustando cada uno para hacer referencia a los rangos correctos. Finalmente, el campo Costo total simplemente sumó esos tres tramos. Ordené ascendente y encontré la ruta más barata. Todos los errores (los que utilizan rutas no disponibles) se enviaron al final.

---

Si desea un sistema continuo en el que desee realizar un seguimiento de cuántos transportes hay actualmente en ruta para cada tramo del viaje para poder seleccionar la ruta más barata según lo que esté disponible actualmente, es posible que desee buscar una solución de base de datos. Alternativamente, puede usar la lista ordenada y comenzar desde arriba, trabajando hacia abajo hasta encontrar una ruta donde cada tramo esté disponible actualmente.