Contidos
Formulación do problema
Supoñamos que a empresa onde traballas ten tres almacéns, desde onde os produtos van ata cinco das túas tendas espalladas por Moscova.
Cada tenda é capaz de vender unha determinada cantidade de produtos que coñecemos. Cada un dos almacéns ten unha capacidade limitada. A tarefa é elixir racionalmente desde que almacén a que tendas entregar a mercadoría para minimizar os custos totais de transporte.
Antes de comezar a optimización, será necesario compilar unha táboa sinxela nunha folla de Excel: o noso modelo matemático que describe a situación:
Enténdese que:
- A táboa amarela clara (C4:G6) describe o custo do envío dun artigo de cada almacén a cada tenda.
- As celas moradas (C15:G14) describen a cantidade de mercadorías necesarias para vender cada tenda.
- Os glóbulos vermellos (J10:J13) mostran a capacidade de cada almacén: a cantidade máxima de mercadorías que pode albergar o almacén.
- As celas amarelas (C13:G13) e azuis (H10:H13) son as sumas das filas e das columnas das celas verdes, respectivamente.
- O custo total de envío (J18) calcúlase como a suma dos produtos do número de mercadorías e os seus correspondentes custos de envío; para o cálculo, a función úsase aquí SUMPRODUCT (SUMAPRODUTO).
Así, a nosa tarefa redúcese á selección dos valores óptimos das células verdes. E para que o importe total da liña (células azuis) non supere a capacidade do almacén (células vermellas) e, ao mesmo tempo, cada tenda reciba a cantidade de mercadorías que necesita vender (o importe de cada tenda no as células amarelas deben estar o máis próximas posible aos requisitos: células vermellas).
solución
En matemáticas, este tipo de problemas de elección da distribución óptima dos recursos foron formulados e descritos durante moito tempo. E, por suposto, as formas de resolvelos foron desenvolvidas durante moito tempo non mediante unha enumeración contundente (que é moi longa), senón nun número moi reducido de iteracións. Excel ofrece ao usuario tal funcionalidade mediante un complemento. Busca solucións (Solucionador) dende a pestana Data (Data):
Se está na pestana Data o teu Excel non ten tal comando - está ben - significa que o complemento simplemente non está conectado aínda. Para activalo abre Arquivo, A continuación, seleccione Parámetros - Add-ons - sobre (Opcións — Complementos — Ir a). Na xanela que se abre, marque a caixa situada ao carón da liña que necesitamos Busca solucións (Solucionador).
Imos executar o complemento:
Nesta xanela, cómpre configurar os seguintes parámetros:
- Optimizar a función de destino (Establecer tdiñeiro cela) – aquí é necesario indicar o obxectivo principal final da nosa optimización, é dicir, caixa rosa co custo total de envío (J18). A cela obxectivo pódese minimizar (se se trata de gastos, como no noso caso), maximizarse (se é, por exemplo, beneficios) ou tentar levala a un valor determinado (por exemplo, encaixar exactamente no orzamento asignado).
- Cambiando celas variables (By cambio células) – aquí indicamos as celas verdes (C10: G12), variando os valores dos que queremos acadar o noso resultado: o custo mínimo de entrega.
- Coherente coas restricións (tema a o restricións) – unha lista de restricións que se deben ter en conta á hora de optimizar. Para engadir restricións á lista, faga clic no botón Engadir (Engadir) e introduza a condición na xanela que aparece. No noso caso, esta será a restrición de demanda:
e límite ao volume máximo de almacéns:
Ademais das obvias limitacións asociadas a factores físicos (capacidade dos almacéns e medios de transporte, limitacións orzamentarias e de tempo, etc.), ás veces é necesario engadir restricións "especiales para Excel". Así, por exemplo, Excel pode organizar facilmente para que "optimice" o custo da entrega ofrecéndolle transportar mercadorías desde as tendas ata o almacén; os custos serán negativos, é dicir, obteremos beneficios. 🙂
Para evitar que isto ocorra, é mellor deixar activada a caixa de verificación. Fai que as variables ilimitadas non sexan negativas ou mesmo ás veces rexistrar de forma explícita tales momentos na lista de restricións.
Despois de configurar todos os parámetros necesarios, a xanela debería verse así:
Na lista despregable Seleccionar un método de resolución, tamén debes seleccionar o método matemático adecuado para resolver unha selección de tres opcións:
- Método simplex é un método sinxelo e rápido para resolver problemas lineais, é dicir, problemas onde a saída depende linealmente da entrada.
- Método xeral de degradado degradado (OGG) – para problemas non lineais, onde existen dependencias non lineais complexas entre os datos de entrada e saída (por exemplo, a dependencia das vendas dos custos de publicidade).
- Busca evolutiva dunha solución – un método de optimización relativamente novo baseado nos principios da evolución biolóxica (ola Darwin). Este método funciona moitas veces máis que os dous primeiros, pero pode resolver case calquera problema (non lineal, discreto).
A nosa tarefa é claramente lineal: 1 peza entregada - gastou 40 rublos, entrega 2 pezas - gastou 80 rublos. etc., polo que o método simplex é a mellor opción.
Agora que se introducen os datos para o cálculo, prema o botón Busca unha solución (Resolver)para iniciar a optimización. En casos graves con moitas celas cambiantes e restricións, atopar unha solución pode levar moito tempo (especialmente co método evolutivo), pero a nosa tarefa para Excel non será un problema: nun par de momentos obteremos os seguintes resultados. :
Preste atención ao interesante reparto dos volumes de subministración entre as tendas, sen exceder a capacidade dos nosos almacéns e satisfacer todas as solicitudes do número de mercadorías necesario para cada tenda.
Se a solución atopada nos convén, podemos gardala ou volver aos valores orixinais e tentalo de novo con outros parámetros. Tamén pode gardar a combinación seleccionada de parámetros como Escenario. A petición do usuario, Excel pode construír tres tipos Informes sobre o problema que se está a resolver en follas separadas: un informe sobre os resultados, un informe sobre a estabilidade matemática da solución e un informe sobre os límites (restricións) da solución, non obstante, na maioría dos casos, son de interese só para especialistas. .
Non obstante, hai situacións nas que Excel non pode atopar unha solución adecuada. É posible simular tal caso se indicamos no noso exemplo as necesidades das tendas en cantidade superior á capacidade total dos almacéns. Entón, ao realizar unha optimización, Excel tentará achegarse o máis posible á solución e, a continuación, mostrará unha mensaxe de que non se pode atopar a solución. Non obstante, mesmo neste caso, temos moita información útil; en particular, podemos ver os "vínculos débiles" dos nosos procesos comerciais e comprender as áreas de mellora.
O exemplo considerado, por suposto, é relativamente sinxelo, pero escala facilmente para resolver problemas moito máis complexos. Por exemplo:
- Optimización da distribución dos recursos financeiros por partida de gasto no plan de empresa ou orzamento do proxecto. As restricións, neste caso, serán a cantidade de financiamento e o momento do proxecto, e o obxectivo da optimización é maximizar os beneficios e minimizar os custos do proxecto.
- Optimización da programación dos empregados co fin de minimizar o fondo salarial da empresa. As restricións, neste caso, serán os desexos de cada traballador segundo o calendario laboral e os requisitos do cadro de persoal.
- Optimización dos investimentos – a necesidade de distribuír correctamente os fondos entre varios bancos, valores ou accións de empresas para, de novo, maximizar os beneficios ou (se é máis importante) minimizar os riscos.
En todo caso, complemento Busca solucións (solucionador) é unha ferramenta de Excel moi poderosa e fermosa e digna da túa atención, xa que pode axudar en moitas situacións difíciles que tes que enfrontar nos negocios modernos.