Tasca de transport a Excel. Trobar el millor mètode de transport del venedor al comprador

Excel és un programa molt funcional. Es pot utilitzar per resoldre una gran capa de problemes als quals s'ha d'enfrontar als negocis. Un dels més habituals és el transport. Imagineu que hem d'entendre quin mètode de transport des del fabricant fins al comprador final és el més òptim en termes de temps, diners i altres recursos. Aquest problema és bastant popular, independentment del sector en què es trobi l'empresa. Per tant, fem una ullada més de prop a com implementar-lo mitjançant Excel.

Descripció de la tasca de transport

Així doncs, tenim dues contraparts que interactuen constantment entre elles. En el nostre cas, es tracta d'un comprador i un venedor. Hem d'esbrinar com transportar mercaderies de manera que els costos siguin mínims. Per fer-ho, cal presentar totes les dades en forma esquemàtica o matricial. A Excel, fem servir l'última opció. En general, hi ha dos tipus de tasques de transport:

1. Tancat. En aquest cas, l'oferta i la demanda estan en equilibri.
2. Obert. Aquí no hi ha igualtat entre l'oferta i la demanda. Per obtenir una solució a aquest problema, primer cal portar-lo al primer tipus, igualant l'oferta i la demanda. Per fer-ho, cal introduir un indicador addicional: la presència d'un comprador o venedor condicional. A més, heu de fer certs canvis a la taula de costos.

Com habilitar la funció Troba una solució a Excel

Per resoldre problemes de transport a Excel, hi ha una funció especial anomenada "Cerca una solució". No està habilitat per defecte, per la qual cosa heu de fer els passos següents:

1. Obriu el menú "Fitxer", que es troba a la cantonada superior esquerra de la finestra del programa.
2. Després d'això, feu clic al botó amb els paràmetres.
3. A continuació, trobem la subsecció "Configuració" i anem al menú de gestió de complements. Aquests són petits programes que s'executen dins de l'entorn de Microsoft Excel. Veiem que al principi vam fer clic al menú "Complements", i després a la part inferior dreta vam establir l'element "Complements d'Excel" i vam fer clic al botó "Vés". Totes les accions necessàries es ressalten amb rectangles i fletxes vermelles.
4. A continuació, activeu el complement "Cerca una solució", després confirmem les nostres accions prement el botó D'acord. A partir de la descripció de l'entorn, podem veure que està dissenyat per analitzar dades complexes, com ara científiques i financeres.
5. Després d'això, aneu a la pestanya "Dades", on veiem un botó nou, que s'anomena igual que el complement. Es pot trobar al grup d'eines d'anàlisi.

Només queda fer clic en aquest botó i procedim a la solució del problema del transport. Però abans d'això, hauríem de parlar una mica més de l'eina Solver a Excel. Aquest és un complement especial d'Excel que permet trobar la solució més ràpida a un problema. Un tret característic és la consideració de les restriccions que l'usuari estableix en l'etapa de preparació. En termes senzills, es tracta d'una subrutina que permet determinar la millor manera d'aconseguir una determinada tasca. Aquestes tasques poden incloure les següents:

1. Invertir, carregar un magatzem o qualsevol altra activitat similar. Incloent el lliurament de mercaderies.
2. La millor manera. Això inclou objectius com aconseguir el màxim benefici al mínim cost, com aconseguir la millor qualitat amb els recursos disponibles, etc.

A més de les tasques de transport, aquest complement també s'utilitza per als propòsits següents:

1. Elaboració d'un pla de producció. És a dir, quantes unitats d'un producte s'han de produir per aconseguir el màxim ingressos.
2. Trobeu la distribució del treball per a diferents tipus de treball de manera que el cost total de produir un producte o servei sigui el més petit.
3. Estableix el temps mínim que trigarà a completar tot el treball.

Com podeu veure, les tasques són molt diferents. La regla universal per aplicar aquest complement és que abans de resoldre el problema cal crear un model que es correspongui amb les característiques clau del problema plantejat. Un model és una col·lecció de funcions que utilitzen variables com a arguments. És a dir, valors que poden canviar.

És important tenir en compte que l'optimització d'un conjunt de valors es realitza exclusivament en un indicador, que s'anomena funció objectiu.

El complement Solver enumera els diferents valors de les variables que es passen a la funció objectiu de manera que sigui el màxim, mínim o igual a un valor determinat (aquesta és precisament la restricció). Hi ha una altra funció que és una mica semblant pel que fa al seu principi de funcionament, i que sovint es confon amb la "Cerca d'una solució". Es diu "Selecció d'opcions". Però si aprofundeixes, la diferència entre ells és enorme:

1. La funció de cerca d'objectius no funciona amb més d'una variable.
2. No preveu la possibilitat de posar límits a les variables.
3. És capaç de determinar només la igualtat de la funció objectiu amb un valor determinat, però no permet trobar el màxim i el mínim. Per tant, no és adequat per a la nostra tasca.
4. Capaç de calcular de manera eficient només si el model és de tipus lineal. Si el model no és lineal, trobarà el valor més proper al valor original.

La tasca de transport és molt més complicada en la seva estructura, de manera que el complement "Selecció de paràmetres" no és suficient per a això. Vegem més de prop com implementar la funció “Cerca una solució” a la pràctica utilitzant l'exemple d'un problema de transport.

Un exemple de resolució d'un problema de transport a Excel

Per tal de demostrar clarament com resoldre problemes de transport a la pràctica a Excel, posem un exemple.

Condicions de les tasques

Suposem que tenim 6 venedors i 7 compradors. La demanda i l'oferta entre elles es distribueixen respectivament de la següent manera: 36, 51, 32, 44, 35 i 38 unitats són venedors i 33, 48, 30, 36, 33, 24 i 32 unitats són compradors. Si sumeu tots aquests valors, trobareu que l'oferta i la demanda estan en equilibri. Per tant, aquest problema és de tipus tancat, que es resol de manera molt senzilla.

A més, tenim informació sobre quant cal gastar en el transport del punt A al punt B (es destaquen en cel·les grogues a l'exemple).

Solució: algorisme pas a pas

Ara, després d'haver-nos familiaritzat amb les taules amb les dades inicials, podem utilitzar el següent algorisme per resoldre aquest problema:

1. Primer, fem una taula formada per 6 files i 7 columnes.
2. Després d'això, anem a qualsevol cel·la que no contingui cap valor i que al mateix temps es trobi fora de la taula acabada de crear i inserim la funció. Per fer-ho, feu clic al botó fx, que es troba a l'esquerra de la línia d'entrada de la funció.
3. Tenim una finestra en la qual hem de seleccionar la categoria "Matemàtiques". Quina funció ens interessa? El destacat en aquesta captura de pantalla. Funció SUMPRODUCT multiplica intervals o matrius entre ells i els suma. Just el que necessitem. Després d'això, premeu la tecla D'acord.
4. A continuació, apareixerà una finestra a la pantalla en la qual cal especificar els paràmetres de la funció. Són els següents:
   1. Matriu 1. Aquest és el primer argument en què escrivim l'interval que està ressaltat en groc. Podeu configurar els paràmetres de la funció utilitzant el teclat o seleccionant l'àrea adequada amb el botó esquerre del ratolí.
   2. Matriu 2. Aquest és el segon argument, que és la taula acabada de crear. Les accions es realitzen de la mateixa manera.

Confirmeu la vostra acció prement el botó D'acord.

1. Després d'això, fem un clic esquerre del ratolí a la cel·la que serveix com a part superior esquerra de la taula acabada de crear. Ara feu clic de nou al botó d'inserció de funció.
2. Seleccionem la mateixa categoria que en el cas anterior. Però aquesta vegada ens interessa la funció SUM.
3. Ara arriba l'etapa d'omplir els arguments. Com a primer argument, escrivim la fila superior de la taula que vam crear al principi. De la mateixa manera que abans, això es pot fer seleccionant aquestes cel·les al full, o manualment. Confirmem les nostres accions prement el botó D'acord.
4. Veurem els resultats a la cel·la amb la funció. En aquest cas, és zero. A continuació, moveu el cursor a l'extrem inferior dret, després d'això apareixerà un marcador d'emplenament automàtic. Sembla un petit peluix negre. Si apareix, manteniu premut el botó esquerre del ratolí i moveu el cursor a l'última cel·la de la nostra taula.
5. Això ens dóna l'oportunitat de transferir la fórmula a totes les altres cel·les i obtenir els resultats correctes sense haver de realitzar càlculs addicionals.
6. El següent pas és seleccionar la cel·la superior esquerra i enganxar la funció SUM en ella. Després d'això, introduïm els arguments i utilitzem el marcador d'autocompletar per omplir totes les cel·les restants.
7. Després d'això, procedim directament a resoldre el problema. Per fer-ho, utilitzarem el complement que hem inclòs anteriorment. Aneu a la pestanya "Dades" i allà trobem l'eina "Cerca una solució". Fem clic en aquest botó.
8. Ara ens ha aparegut una finestra davant dels nostres ulls, a través de la qual podeu configurar els paràmetres del nostre complement. Fem una ullada a cadascuna d'aquestes opcions:
   1. Optimitzar la funció objectiu. Aquí hem de seleccionar la cel·la que conté la funció SUMPRODUCT. Veiem que aquesta opció permet seleccionar una funció per a la qual es buscarà una solució.
   2. Abans. Aquí establim l'opció "Mínim".
   3. Canviant les cel·les de les variables. Aquí indiquem l'interval corresponent a la taula que vam crear al principi (a excepció de la fila i columna de resum).
   4. Subjecte a restriccions. Aquí hem d'afegir restriccions fent clic al botó Afegeix.
   5. Recordem quin tipus de restricció hem de crear: la suma dels valors de les demandes dels compradors i les ofertes dels venedors ha de ser la mateixa.
9. La tasca de restriccions es porta a terme de la següent manera:
   1. Enllaç a cel·les. Aquí introduïm l'interval de la taula per als càlculs.
   2. Termes. Aquesta és una operació matemàtica amb la qual es verifica l'interval especificat al primer camp d'entrada.
   3. El valor de la condició o restricció. Aquí introduïm la columna adequada a la taula d'origen.
   4. Un cop finalitzats tots els passos, feu clic al botó D'acord, confirmant així les nostres accions.

Realitzem exactament les mateixes operacions per a les files superiors, establint la següent condició: han de ser iguals.

El següent pas és establir les condicions. Hem d'establir els criteris següents per a la suma de les cel·les de la taula: major o igual a zero, un nombre enter. Com a resultat, tenim una llista de condicions en què es resol el problema. Aquí us heu d'assegurar que la casella de selecció que hi ha al costat de l'opció "Fes que les variables sense límits no siguin negatives" estigui marcada. A més, en la nostra situació, es requereix que s'esculli el mètode per resoldre el problema: "Buscar una solució a problemes no lineals dels mètodes OPG". Ara podem dir amb seguretat que la configuració està feta. Per tant, només queda realitzar els càlculs. Per fer-ho, feu clic al botó "Troba una solució".

Després d'això, totes les dades es calcularan automàticament i, a continuació, Excel mostrarà una finestra amb els resultats. És necessari per comprovar el funcionament de l'ordinador, ja que es poden produir errors si prèviament les condicions s'havien establert incorrectament. Si tot és correcte, feu clic al botó "D'acord" i vegeu la taula acabada.

Si resulta que la nostra tasca s'ha convertit en un tipus obert, això és dolent, perquè cal editar la taula d'origen perquè la tasca es converteixi en una de tancada. Tanmateix, quan es fa això, l'algorisme restant serà el mateix.

Conclusió

Com podeu veure, Excel també es pot utilitzar per a càlculs molt complexos, que a primera vista no estan disponibles per a un simple programa informàtic instal·lat a gairebé tothom. Tanmateix, ho és. Avui ja hem tractat el nivell avançat d'ús. Aquest tema no és tan senzill, però com diuen, el camí el dominarà el caminant. El més important és seguir el pla d'acció i realitzar amb precisió totes les accions indicades anteriorment. Aleshores, no hi haurà errors i el programa realitzarà tots els càlculs necessaris de manera independent. No caldrà pensar quina funció utilitzar i així successivament.