01 / 15El Plan Que la Hoja Seguía Teniendo

SolverExcelSUMAPRODUCTO

Solver en Excel: optimizar con restricciones

Las Bandejas de Abril Costaron 111.906 € Frente a los 109.149 € de la Hoja, Porque Solver Escribe Números en las Celdas y la Respuesta de Marzo Seguía Ahí

17 oct 202616 min de lectura

UsaSUMAPRODUCTOMIN, MAXREDONDEARRESIDUO

Marsden Fold Packaging compra 24.000 bandejas moldeadas al mes a cuatro proveedores. El plan de abril estaba en el mismo libro que el de marzo, valorado al céntimo en 109.149 €. Las facturas sumaron 111.906 €.

No falló nada. La celda del coste se recalculó en cuanto entraron los precios de abril, así que el número en pantalla era nuevo. Las cantidades que había debajo tenían cinco semanas.

Solver no deja una fórmula detrás. Escribe constantes en las celdas que le dejas cambiar, y una constante no sabe que la capacidad de Cowden se redujo en dos tercios el 1 de abril.

MedidaMarzoAbril
Bandejas pedidas24.00024.000
Capacidad de Cowden6.0003.600
Precio de Ashgrove4,10 €4,38 €
Cantidades en las celdasresueltaslas de marzo
Coste según la hoja107.385 €109.149 €
Coste según las facturas107.385 €111.906 €

Tres fallos, y ninguno tenía una celda a su nombre. El plan estaba caducado, la capacidad que cambió vivía en un cuadro de diálogo, y la regla de calidad por la que existía el plan no podía cumplirse en abril.

Qué necesitas. Solver viene con Excel y llega desactivado. Archivo → Opciones → Complementos → Administrar: Complementos de Excel → Ir, y marca Solver; en Mac es Herramientas → Complementos de Excel. Aparece al final de la pestaña Datos.

Solver admite 200 celdas cambiantes y 100 restricciones, que es el tamaño de un plan y no de un modelo. Este usa cuatro celdas cambiantes, tres reglas y un objetivo.

El envase es solo el ejemplo. Cuadrantes de turnos, recetas de mezcla, presupuestos de medios, disposiciones de un préstamo: cualquier cosa con un total que cubrir, límites que respetar y un coste que contener.

01El Plan Que la Hoja Seguía Teniendo

Cuatro Proveedores, un Plan Caducado y 2.757 € de Urgencia

La conciliación de abril de Marsden Fold Packaging, en A2:I10. La columna B tiene los precios de abril y la D las capacidades de abril, ambas escritas el día 1. La columna E tiene las cantidades que Solver escribió en la hoja en marzo y que nadie volvió a resolver: 6.300, 7.500, 6.000 y 4.200 bandejas. La columna F es el exceso sobre la capacidad, y vale cero en todas las filas menos en Cowden, cuyo cierre técnico la dejó en 3.600 bandejas. La columna G es lo que se sirvió de verdad. Nada falló en ningún momento: la celda del coste es un SUMAPRODUCTO vivo, así que se recalculó en cuanto llegaron los precios de abril y mostró 109.149 € — el reparto de marzo con los precios de abril, la respuesta a una pregunta que nadie había hecho. Las facturas sumaron 111.906 €.

ABCDEFGHI
1
Supplier
April price
Defect %
April capacity
Plan in the cells
Over capacity
Actually shipped
Cost paid
What happened
2
Ashgrove
4.38
2.4
9000
6300
0
6300
27594
The cheap, dirty one: 2.4% defects against a 1.20% average cap, so the plan can only take so much of it. Its price rose 28p on 1 April, which the cost cell picked up and the quantities did not.
3
Brantham
4.35
1.1
7500
7500
0
7500
32625
At its capacity in both months, and right by accident. A supplier pinned to its limit is the one row a stale plan cannot get wrong.
4
Cowden
4.62
0.6
3600
6000
2400
3600
16632
A two-week shutdown cut April's capacity from 20 pallets to 12. The buyer typed 3,600 into column D on 1 April. Solver's own constraint still read 20 pallets, because it was typed into the dialog as a number in March.
5
Deeping
5.05
0.4
5100
4200
0
6600
35055
The clean, dear one. It covered Cowden's 2,400 at three days' notice: 5,100 trays at the £5.05 contract rate and 1,500 at the £6.20 expedite rate, which is where £2,757 of the overrun came from.
6
Total
25200
24000
2400
24000
111906
The order was met. Every tray arrived, the average defect rate came in at 1.17%, and the only thing that went wrong was the price — which is the kind of failure a sheet full of green cells is built to hide.
7
Cost when Solver ran
107385
March's solve, on March's prices and capacities: 21 pallets of Ashgrove, 25 of Brantham, 20 of Cowden, 14 of Deeping. A proven optimum, for a month that had ended.
8
Cost the sheet showed
109149
March's quantities at April's prices. A live SUMPRODUCT over dead inputs: new number, old plan, no warning. This is the figure that went into the forecast.
9
Best April plan
109176
What Solver returns when April's numbers are actually solved — and only once the 1.20% cap is lifted to the 1.30% that April's capacities allow. £2,730 less than the month cost, and available on the 1st.

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

La hoja de arriba es la conciliación de abril, no el plan de abril. Una fila por proveedor: lo que pedían las celdas, lo que el proveedor pudo servir y lo que decía la factura.

La columna E es la respuesta de marzo, donde Solver la escribió. La columna D es la capacidad de abril, escrita por el comprador el día 1. Nada en la hoja comparaba las dos.

ProveedorPlan en las celdasCapacidad de abrilServido
Ashgrove6.3009.0006.300
Brantham7.5007.5007.500
Cowden6.0003.6003.600
Deeping4.2005.1006.600

A Cowden le faltaron 2.400 bandejas. Deeping cubrió el hueco con tres días de aviso: 1.500 de ellas a una tarifa de urgencia de 6,20 € frente a los 5,05 € de su contrato.

Escenario: Abre el último libro con el que corriste Solver. Pon =MAX(0;E2-D2) junto a cada cantidad y =SUMA(F2:F5) debajo, y lee eso antes de leer el coste. En esta hoja devuelve 2.400, y cualquier cosa distinta de cero significa que el plan es más viejo que los límites.

Pruébalo en la cuadrícula

02Solver Escribe Números, no Fórmulas

Solver y Buscar objetivo trabajan igual: escribiendo en tu hoja. Cambian las celdas que les señalas hasta que el objetivo se porta bien, y dejan ahí los últimos números que probaron.

La respuesta es una foto, no un enlace. Cambia después un precio, una capacidad o la cantidad del pedido y no se vuelve a resolver nada.

El objetivo sí se recalcula, porque es una fórmula, y eso es justo lo que hace creíble a un plan caducado. La celda del coste de abril decía 109.149 €: las cantidades de marzo con los precios de abril.

Una celda lo detecta. Mantén el objetivo vivo en F14 y pega su valor en F16 en cuanto aceptes una solución:

=SI(REDONDEAR(F14-F16;2)<>0;"RESOLVER DE NUEVO";"resuelto con estas entradas")

F16 es el coste que Solver firmó. F14 es lo que cuestan hoy esas mismas cantidades. En cuanto se mueve una entrada, los dos se separan y la celda lo dice con una palabra.

Escenario: Añade ese par a tu modelo ahora. Pega el valor del objetivo en la celda de al lado, cambia un precio en un céntimo y mira cómo gira el aviso antes de que termines de escribir.

Pruébalo en la cuadrícula

03Una Restricción Vive en un Diálogo, no en la Hoja

Las restricciones de Solver se guardan con la hoja, pero no están en ella. Viven en el cuadro Parámetros de Solver, invisibles hasta que alguien lo abre.

Y aguantan tan contentas una cifra que ha caducado. La regla de marzo decía $K$4 <= 20. Veinte palés de Cowden, escritos como número, en marzo, por un comprador que ya cambió de equipo.

La capacidad de abril fue a la columna D, donde la hoja podía verla. El diálogo no se enteró. Escribe una referencia en Agregar restricción, nunca un número, y la regla leerá la celda que el comprador actualiza.

Una referencia además sobrevive a un cambio de diseño y aparece en Rastrear precedentes. Un número escrito a mano no hace ninguna de las dos cosas.

Escenario: Abre Solver en tu modelo y lee la lista de restricciones en voz alta. Sustituye cada número suelto por la celda que lo contiene, resuelve otra vez y comprueba que la respuesta sigue siendo la misma.

Pruébalo en la cuadrícula

04Escribe Cada Regla Donde Puedas Verla

Una regla que nadie ve es una regla que nadie comprueba. Dale a cada una tres celdas en la hoja: lo que usa el plan, cuál es el límite y la holgura entre ambos.

ReglaUsadoLímiteHolgura
Capacidad de Cowden6.0003.600−2.400
Total del pedido24.00024.0000
Defectos medios1,19 %1,20 %0,01 %

Luego apunta Solver a esas mismas celdas. La hoja y el diálogo dicen ya lo mismo, y la columna de holgura te enseña qué regla está haciendo el trabajo.

Una regla con holgura de sobra es una regla que podrías haber ignorado. La regla que aprieta es la que merece una discusión, porque es la que cuesta dinero: aquí, el límite del 1,20 %.

Escenario: Monta las tres columnas junto a tu modelo, con =D2-E2 para la holgura, y ordénalas de menor a mayor con =ORDENAR(H2:H5). Lo que quede arriba es la restricción que hay que llevarle a quien la fijó.

Pruébalo en la cuadrícula

05La Celda Objetivo Tiene Que Ser una Fórmula

Solver lee una celda y cambia otras. Si la celda que lee no depende de las celdas que cambia, informará de una solución sin haber movido absolutamente nada.

El objetivo aquí es una celda, viva, y no más lista de lo que hace falta:

=SUMAPRODUCTO(B2:B5;E2:E5)

Precios por cantidades. El modelo que hay debajo ocupa seis celdas:

CeldasContienen
K2:K5palés: las celdas cambiantes, números enteros
E2:E5bandejas, =K2*300
D2:D5capacidad en bandejas, escrita cada mes
F14el objetivo, =SUMAPRODUCTO(B2:B5;E2:E5)
F15defectos medios, =SUMAPRODUCTO(C2:C5;E2:E5)/SUMA(E2:E5)
F16el coste que Solver firmó, pegado como valor

Minimizar F14, cambiando K2:K5, con E2:E5 ≤ D2:D5, SUMA(E2:E5) = 24.000 y F15 ≤ 1,20 %. Ese es el modelo entero.

Una cosa más antes de resolver: Convertir variables sin restricciones en no negativas. Viene marcada en el Excel actual, y una casilla sin marcar es de donde sale un proveedor con una cantidad negativa.

Escenario: Busca la celda objetivo de tu modelo y pulsa F2. Si es un número en vez de una fórmula, o una fórmula que nunca llega a las celdas cambiantes, ahí tienes el fallo entero: reconstrúyela como un SUMAPRODUCTO sobre las dos columnas.

Pruébalo en la cuadrícula

06Simplex LP, GRG Nolineal y Evolutionary

Solver trae tres motores y viene con el que peor le sienta a una hoja de esta forma. La caja del método está debajo de las restricciones y nadie la lee dos veces.

MétodoÚsalo cuandoQué te devuelve
Simplex LPtoda fórmula es una suma o un múltiplo de las celdas cambiantesel óptimo demostrado
GRG Nolinealhay curvas suaves: cuadrados, tasas, crecimiento compuestouna respuesta local que depende de dónde empezó
Evolutionaryhay SI, REDONDEAR, MIN, MAX o BUSCARV entre las celdas y el objetivouna respuesta decente, despacio y sin demostrar nada

Este modelo es lineal. Un coste es un precio por una cantidad y una capacidad es una suma, así que Simplex LP lo resuelve en un segundo y te dice que no hay nada mejor.

GRG también llega, casi siempre. Y también llega a otro sitio cuando cambian los valores de partida, que es como dos personas con el mismo libro acaban con dos planes y sin forma de saber cuál vale.

Un modelo lleno de SI y REDONDEAR no tiene pendiente que seguir. Las búsquedas, los tramos y el redondeo hacen escalones, y un método de gradiente se atasca en un escalón. Es la misma escalera que derrota a Buscar objetivo.

07El Palé Que Alguien Redondeó a Mano

El óptimo de marzo era 21,25 palés de Ashgrove. Las bandejas vienen de 300 en palé, nadie sirve un cuarto de palé, y un comprador lo subió a 22 y le quitó uno a Deeping para cuadrar el total.

La hoja salió 285 € más barata. También rompió la única regla por la que existía el plan.

PlanAshgroveDeepingCosteDefectos medios
Solver, con decimales21,2513,75107.313,75 €1,20 %
Redondeado a mano2213107.100,00 €1,22 %
Solver, palés enteros2114107.385,00 €1,19 %

El redondeo movió un palé del proveedor más limpio al más sucio, que es la dirección en la que siempre lo mueve redondear hacia la columna barata.

Redondear es una decisión, así que deja que la tome el modelo. Pon los palés en las celdas cambiantes, deriva las bandejas con =K2*300 y añade una restricción: $K$2:$K$5 = int.

REDONDEAR y MULTIPLO.SUPERIOR siguen teniendo trabajo aquí — =MULTIPLO.SUPERIOR(6375/300;1) te dice qué saldría de una respuesta con decimales — pero describen una respuesta en lugar de elegirla.

Escenario: Coge un modelo cuya respuesta redondeaste a mano, añade la restricción de enteros a las celdas cambiantes y resuelve otra vez. Compara los dos costes y pasa tu versión redondeada por la regla que debía respetar.

Pruébalo en la cuadrícula

08La Optimalidad de Enteros no Es Cero Hasta Que la Pones a Cero

Añade int y Solver cambia en silencio su definición de terminado. Para cuando la mejor respuesta entera que ha encontrado está dentro de un porcentaje de lo mejor a lo que todavía podría aspirar.

Ese porcentaje no es cero. Opciones → Optimalidad de enteros (%): el Excel actual la trae al 1 y las versiones antiguas al 5, y la casilla está a tres clics de donde mira cualquiera.

En un plan de 107.385 €, un 1 % es permiso para dejarse unos mil euros por el camino. No lo verás pasar, porque Solver sigue diciendo que encontró una solución.

Ponla a 0 en un modelo de este tamaño y la resolución sigue tardando un segundo. Súbela a propósito, o no la subas — en un modelo con cientos de celdas enteras, esa holgura es la diferencia entre una respuesta y una tarde perdida.

09«Solver no Pudo Encontrar una Solución Factible» Es una Respuesta

Pasa por Solver los números reales de abril y no te devuelve un plan. Te devuelve una frase, y esa frase es lo más útil de esta página.

Los cuatro contratos de abril sumaban 25.200 bandejas frente a un pedido de 24.000, así que había que dejar 1.200 bandejas de capacidad sin usar. Con Cowden en 12 palés, el proveedor más limpio de la lista estaba racionado.

Quita los cuatro peores palés disponibles — los de Ashgrove, al 2,4 % — y la tasa media de defectos se queda igualmente en el 1,30 %. El límite era el 1,20 %.

No había ningún plan que cumpliera las reglas de abril, y ninguna fórmula de la hoja iba a decirlo.

Cuando Solver se niega, no empieces a borrar restricciones. Copia el modelo, convierte la regla que aprieta en el objetivo y pregunta por cuánto se pasa:

=SUMAPRODUCTO(C2:C5;E2:E5)/SUMA(E2:E5)

Minimiza eso, mantén las capacidades y el total del pedido, y Solver devuelve un 1,30 % con un coste de 109.176 €. Son 2.730 € menos de lo que costó abril, y estaban disponibles el día 1.

Lo infactible tiene fecha. Saber el día 1 que el límite no se puede cumplir es una llamada al cliente. Saberlo el día 28 es un recargo de urgencia.

Escenario: Haz imposible una de tus restricciones a propósito — parte una capacidad por la mitad — y ejecuta Solver. Lee el mensaje y vuelve a ejecutarlo con esa restricción como objetivo: apunta el número que no puede batir.

Pruébalo en la cuadrícula

10Conservar Solución de Solver, y la Base Que Sobrescribe

El cuadro de resultados ofrece Conservar solución de Solver o Restaurar valores originales, y viene marcado Conservar. Aceptas y la respuesta pasa a ser tu columna de entradas.

Está bien cuando era lo que querías. No lo está cuando solo probabas un escenario, porque el plan que tenías antes ya no está y la hoja parece tan deliberada como hace un minuto.

Ese mismo cuadro tiene un botón Guardar escenario. Escribe las celdas cambiantes actuales en el Administrador de escenarios con un nombre, y es el seguro más barato del complemento.

Ctrl+Z deshace una resolución, pero solo en ese momento. Guarda, cierra, vuelve a abrir, y los números que tenías antes no vuelven sin el escenario que no guardaste.

11Cinco Controles Encima de la Tabla

Cinco celdas, en la fila de encima del modelo, que se leen antes de que nadie lea el coste:

  1. ¿Sigue el plan siendo la respuesta? =SI(REDONDEAR(F14-F16;2)<>0;"RESOLVER DE NUEVO";"resuelto con estas entradas"), el objetivo vivo contra el que firmó Solver.
  2. ¿Algo se pasa de su límite? =SUMA(F2:F5) sobre =MAX(0;E2-D2) tiene que dar 0. En abril daba 2.400.
  3. ¿El pedido sigue cuadrando? =SUMA(E2:E5)-F7, con F7 la cantidad pedida. Cero, o el plan es de otro mes.
  4. ¿Se cumple la regla de calidad? =SUMAPRODUCTO(C2:C5;E2:E5)/SUMA(E2:E5) contra el límite. El plan redondeado a mano da 1,22 % frente a 1,20 %.
  5. ¿Son palés enteros? =SUMAPRODUCTO(--(RESIDUO(E2:E5;300)<>0)) cuenta las filas que no son múltiplo de 300, y =CONTAR.SI(E2:E5;"<0") caza la cantidad negativa que permite una casilla sin marcar.

12Cuando el Modelo no Necesita Solver

Si la capacidad es la única regla, lo más barato primero es el óptimo y el complemento sobra. Ordena los proveedores por precio y arrastra una fórmula:

=MIN(D2;$F$7-SUMA($E$1:E1))

Cada fila coge lo que puede hasta su capacidad, o lo que quede del pedido, lo que sea menor. =ORDENAR(A2:D5;2) los pone antes en orden de precio.

Ese es el plan que devuelve Solver para un modelo de una sola regla, y con los números de marzo cuesta 104.820 €: más barato que los 107.385 € de Solver, y con un 1,42 % de defectos frente a un límite del 1,20 %.

Solver se gana el sueldo en cuanto dos reglas tiran en sentidos contrarios. Una regla es un arrastre; dos son un compromiso, y ahí es donde se gastan a propósito los 2.565 € que separan esos dos planes.

13Diez Trampas

  1. Solver escribe valores. Nada se vuelve a resolver cuando cambia una entrada.
  2. Las restricciones aguantan números escritos a mano. Usa referencias, siempre.
  3. Un objetivo que es una constante deja que Solver cante éxito sin mover nada.
  4. GRG viene por defecto. En un modelo lineal, Simplex LP demuestra lo que GRG solo encuentra.
  5. SI y BUSCARV hacen escalones. Los métodos de gradiente se atascan en ellos.
  6. La Optimalidad de enteros no es 0. Es 1 % en el Excel actual y 5 % en los antiguos.
  7. Redondear a mano rompe restricciones. El modelo debe llevar int.
  8. Lo no negativo es una casilla. Desmárcala y las cantidades pueden bajar de cero.
  9. Conservar solución sobrescribe tus entradas. Guarda el escenario antes.
  10. Lo infactible es información. Es el único mensaje que te dice la verdad sobre el mes.

14Ejercicios

  1. Cadúcalo a propósito. Resuelve el modelo, sube un precio un 10 % y mira cómo se mueve el objetivo mientras las cantidades no.
  2. Endurece las reglas. Sustituye cada número escrito en la lista de restricciones por una referencia, cambia una capacidad en la hoja y confirma que la siguiente resolución la respeta.
  3. Redondéalo mal. Coge la respuesta con decimales, redondéala a mano y pásale el control 4 contra el límite de calidad.
  4. Cambia de motor. Resuelve el mismo modelo con Simplex LP y con GRG partiendo de cero, y compara las dos respuestas y los dos tiempos.
  5. Rómpelo del todo. Baja una capacidad hasta que Solver se niegue, resuelve con la regla de calidad como objetivo y apunta lo que abril podía conseguir de verdad.

Qué Llevarte

En este libro no había nada roto. La celda del coste era un SUMAPRODUCTO correcto, la columna de capacidad tenía los números reales de abril, y Solver había hecho exactamente lo que se le pidió en marzo.

Lo que nadie había escrito es que una respuesta de Solver es una foto del modelo en un momento: estos precios, estos límites, este pedido. La hoja se queda la foto y sigue recalculando a su alrededor.

Tres celdas lo habrían dicho. El objetivo vivo contra el firmado, la suma de los excesos de capacidad y la tasa media de defectos contra su límite.

Tres celdas, frente a 2.757 € de transporte urgente y un límite de calidad que en abril no se podía cumplir escribiera quien escribiera lo que escribiera.

Comparte este artículo:
Volver al Blog
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy