contingut
Formulació del problema
Suposem que l'empresa on treballes té tres magatzems, des d'on la mercaderia va a cinc de les teves botigues repartides per Moscou.
Cada botiga és capaç de vendre una determinada quantitat de béns que coneixem. Cadascun dels magatzems té una capacitat limitada. La tasca és triar racionalment des de quin magatzem a quines botigues lliurar la mercaderia per tal de minimitzar els costos totals de transport.
Abans de començar l'optimització, serà necessari compilar una taula senzilla en un full d'Excel: el nostre model matemàtic que descriu la situació:
S'entén que:
- La taula groc clar (C4:G6) descriu el cost d'enviament d'un article de cada magatzem a cada botiga.
- Les cel·les morades (C15:G14) descriuen la quantitat de mercaderies que es necessita per vendre cada botiga.
- Les cèl·lules vermelles (J10:J13) mostren la capacitat de cada magatzem: la quantitat màxima de mercaderies que pot contenir el magatzem.
- Les cel·les grogues (C13:G13) i blaves (H10:H13) són les sumes de files i columnes de les cel·les verdes, respectivament.
- El cost total d'enviament (J18) es calcula com la suma dels productes del nombre de mercaderies i els seus corresponents costos d'enviament; per al càlcul, la funció s'utilitza aquí SUMPRODUCT (SUMAPRODUCTE).
Així, la nostra tasca es redueix a la selecció de valors òptims de cèl·lules verdes. I perquè l'import total de la línia (cel·les blaves) no superi la capacitat del magatzem (gèl·lules vermelles) i alhora cada botiga rebi la quantitat de mercaderies que necessita vendre (l'import per a cada botiga al les cèl·lules grogues han d'estar el més a prop possible dels requisits: cèl·lules morades).
Solució
En matemàtiques, aquests problemes d'elecció de la distribució òptima dels recursos s'han formulat i descrit durant molt de temps. I, per descomptat, les maneres de resoldre'ls s'han desenvolupat durant molt de temps no mitjançant una enumeració contundent (que és molt llarga), sinó en un nombre molt reduït d'iteracions. Excel proporciona a l'usuari aquesta funcionalitat mitjançant un complement. Solucions de cerca (Solucionador) de la pestanya dades (Data):
Si a la pestanya dades el vostre Excel no té aquesta ordre, està bé, vol dir que el complement simplement no està connectat encara. Per activar-lo obriu Dossier, A continuació, seleccioneu Paràmetres - Complements del Firefox - Qui som? (Opcions — Complements — Vés a). A la finestra que s'obre, marqueu la casella al costat de la línia que necessitem Solucions de cerca (Solucionador).
Executem el complement:
En aquesta finestra, heu de configurar els paràmetres següents:
- Optimitzar la funció objectiu (Estableix tla money cel·la) – aquí cal indicar l'objectiu principal final de la nostra optimització, és a dir, la caixa rosa amb el cost total d'enviament (J18). La cel·la objectiu es pot minimitzar (si es tracta de despeses, com en el nostre cas), maximitzar (si és, per exemple, beneficis) o intentar portar-la a un valor determinat (per exemple, ajustar-se exactament al pressupost assignat).
- Canvi de cel·les variables (By canvi cèl · lules) – aquí indiquem les cel·les verdes (C10: G12), variant els valors dels quals volem aconseguir el nostre resultat: el cost mínim de lliurament.
- D'acord amb les restriccions (Assumpte a la restriccions) – una llista de restriccions que s'han de tenir en compte a l'hora d'optimitzar. Per afegir restriccions a la llista, feu clic al botó Add (Afegeix) i introduïu la condició a la finestra que apareix. En el nostre cas, aquesta serà la restricció de demanda:
i límit del volum màxim de magatzems:
A més de les òbvies limitacions associades a factors físics (capacitat dels magatzems i mitjans de transport, limitacions de pressupost i temps, etc.), de vegades cal afegir restriccions "especials per a Excel". Així, per exemple, Excel pot organitzar fàcilment que "optimitzeu" el cost del lliurament oferint-vos el transport de mercaderies des de les botigues de tornada al magatzem; els costos es tornaran negatius, és a dir, obtindrem beneficis! 🙂
Per evitar que això passi, el millor és deixar la casella de selecció activada. Feu que les variables il·limitades no siguin negatives o fins i tot de vegades registrar explícitament aquests moments a la llista de restriccions.
Després de configurar tots els paràmetres necessaris, la finestra hauria de ser així:
A la llista desplegable Seleccioneu un mètode de resolució, a més, haureu de seleccionar el mètode matemàtic adequat per resoldre una selecció de tres opcions:
- Mètode simplex és un mètode senzill i ràpid per resoldre problemes lineals, és a dir, problemes on la sortida depèn linealment de l'entrada.
- Mètode general de degradat rebaixat (OGG) – per a problemes no lineals, on hi ha dependències no lineals complexes entre les dades d'entrada i de sortida (per exemple, la dependència de les vendes dels costos publicitaris).
- Recerca evolutiva d'una solució – un mètode d'optimització relativament nou basat en els principis de l'evolució biològica (hola Darwin). Aquest mètode funciona moltes vegades més que els dos primers, però pot resoldre gairebé qualsevol problema (no lineal, discret).
La nostra tasca és clarament lineal: 1 peça lliurada - 40 rubles gastats, 2 peces lliurades - 80 rubles gastats. etc., de manera que el mètode simplex és la millor opció.
Ara que s'han introduït les dades per al càlcul, premeu el botó Trobar una solució (Resol)per començar l'optimització. En casos greus amb moltes cel·les i restriccions canviants, trobar una solució pot trigar molt de temps (especialment amb el mètode evolutiu), però la nostra tasca per a Excel no serà un problema: en un parell de moments obtindrem els resultats següents :
Fixeu-vos en l'interès de la distribució dels volums de subministrament entre les botigues, sense excedir la capacitat dels nostres magatzems i satisfer totes les peticions del nombre de mercaderies requerit per a cada botiga.
Si la solució trobada ens convé, podem desar-la o tornar als valors originals i tornar-ho a provar amb altres paràmetres. També podeu desar la combinació de paràmetres seleccionada com a Escenari. A petició de l'usuari, Excel pot crear tres tipus Informes sobre el problema que es resol en fulls separats: un informe sobre els resultats, un informe sobre l'estabilitat matemàtica de la solució i un informe sobre els límits (restriccions) de la solució, però, en la majoria dels casos, només són d'interès per als especialistes. .
Hi ha, però, situacions en què Excel no pot trobar una solució adequada. És possible simular un cas així si indiquem en el nostre exemple els requeriments de les botigues en una quantitat superior a la capacitat total dels magatzems. Aleshores, quan realitzeu una optimització, Excel intentarà apropar-se el més possible a la solució i, a continuació, mostrarà un missatge que no es pot trobar la solució. No obstant això, fins i tot en aquest cas, tenim molta informació útil; en particular, podem veure els "enllaços febles" dels nostres processos empresarials i comprendre les àrees de millora.
L'exemple considerat, per descomptat, és relativament simple, però s'escala fàcilment per resoldre problemes molt més complexos. Per exemple:
- Optimització de la distribució dels recursos financers per partida de despesa en el pla d'empresa o pressupost del projecte. Les restriccions, en aquest cas, seran la quantitat de finançament i el moment del projecte, i l'objectiu de l'optimització és maximitzar els beneficis i minimitzar els costos del projecte.
- Optimització de la programació dels empleats per tal de minimitzar el fons salarial de l'empresa. Les restriccions, en aquest cas, seran la voluntat de cada treballador d'acord amb el calendari laboral i els requisits de la plantilla.
- Optimització de les inversions d'inversió – la necessitat de distribuir correctament els fons entre diversos bancs, valors o accions d'empreses per, de nou, maximitzar els beneficis o (si és més important) minimitzar els riscos.
En qualsevol cas, complement Solucions de cerca (solucionador) és una eina d'Excel molt potent i bonica i digna de la vostra atenció, ja que pot ajudar-vos en moltes situacions difícils que us heu d'enfrontar en els negocis moderns.