Sarcina de transport în Excel. Găsirea celei mai bune metode de transport de la vânzător la cumpărător

Excel este un program foarte funcțional. Poate fi folosit pentru a rezolva un strat uriaș de probleme cu care trebuie să se confrunte în afaceri. Una dintre cele mai comune este transportul. Imaginați-vă că trebuie să înțelegem care metodă de transport de la producător la cumpărătorul final este cea mai optimă în ceea ce privește timpul, banii și alte resurse. Această problemă este destul de populară, indiferent de industria în care se află afacerea. Prin urmare, să aruncăm o privire mai atentă asupra modului de implementare a acesteia folosind Excel.

Descrierea sarcinii de transport

Deci, avem două contrapărți care interacționează constant între ele. În cazul nostru, acesta este un cumpărător și un vânzător. Trebuie să ne dăm seama cum să transportăm mărfurile în așa fel încât costurile să fie minime. Pentru a face acest lucru, trebuie să prezentați toate datele într-o formă schematică sau matrice. În Excel, folosim ultima opțiune. În general, există două tipuri de sarcini de transport:

1. Închis. În acest caz, cererea și oferta sunt în echilibru.
2. Deschis. Aici nu există egalitate între cerere și ofertă. Pentru a obține o soluție la această problemă, trebuie mai întâi să o aduceți la primul tip, egalând cererea și oferta. Pentru a face acest lucru, trebuie să introduceți un indicator suplimentar - prezența unui cumpărător sau vânzător condiționat. În plus, trebuie să faceți anumite modificări la tabelul de costuri.

Cum să activați funcția Găsiți soluție în Excel

Pentru a rezolva problemele de transport în Excel, există o funcție specială numită „Căutare soluție”. Nu este activat în mod implicit, așa că trebuie să faceți următorii pași:

1. Deschideți meniul „Fișier”, care se află în colțul din stânga sus al ferestrei programului.
2. După aceea, faceți clic pe butonul cu parametrii.
3. În continuare, găsim subsecțiunea „Setări” și mergem la meniul de gestionare a suplimentelor. Acestea sunt programe mici care rulează în mediul Microsoft Excel. Vedem că la început am făcut clic pe meniul „Suplimente”, iar apoi în partea din dreapta jos am setat elementul „Suplimente Excel” și am dat clic pe butonul „Go”. Toate acțiunile necesare sunt evidențiate cu dreptunghiuri și săgeți roșii.
4. Apoi, activați suplimentul „Căutați o soluție”, după care ne confirmăm acțiunile apăsând butonul OK. Pe baza descrierii decorului, putem observa că acesta este conceput pentru a analiza date complexe, cum ar fi științifice și financiare.
5. După aceea, mergeți la fila „Date”, unde vedem un buton nou, care se numește la fel ca suplimentul. Poate fi găsit în grupul de instrumente de analiză.

Rămâne doar să facem clic pe acest buton și trecem la rezolvarea problemei de transport. Dar înainte de asta, ar trebui să vorbim puțin mai mult despre instrumentul Solver din Excel. Acesta este un add-on special Excel care face posibilă găsirea celei mai rapide soluții la o problemă. O trăsătură caracteristică este luarea în considerare a restricțiilor pe care utilizatorul le stabilește în etapa de pregătire. În termeni simpli, aceasta este o subrutină care face posibilă determinarea celui mai bun mod de a realiza o anumită sarcină. Astfel de sarcini pot include următoarele:

1. Investiția, încărcarea unui depozit sau orice altă activitate similară. Inclusiv livrarea mărfurilor.
2. Cel mai bun mod. Aceasta include obiective precum obținerea unui profit maxim la costuri minime, cum să obțineți cea mai bună calitate cu resursele disponibile și așa mai departe.

Pe lângă sarcinile de transport, acest supliment este utilizat și în următoarele scopuri:

1. Elaborarea unui plan de producție. Adică câte unități dintr-un produs trebuie produse pentru a obține un venit maxim.
2. Găsiți distribuția forței de muncă pentru diferite tipuri de muncă, astfel încât costul total de producere a unui produs sau serviciu să fie cel mai mic.
3. Setați timpul minim necesar pentru finalizarea întregii lucrări.

După cum puteți vedea, sarcinile sunt foarte diferite. Regula universală pentru aplicarea acestui add-in este că înainte de a rezolva problema, este necesar să se creeze un model care să corespundă caracteristicilor cheie ale problemei puse. Un model este o colecție de funcții care folosesc variabile ca argumente. Adică valori care se pot schimba.

Este important de reținut că optimizarea unui set de valori se realizează exclusiv pe un indicator, care se numește funcție obiectiv.

Add-in-ul Solver enumerează diferitele valori ale variabilelor care sunt transmise funcției obiectiv în așa fel încât să fie maxim, minim sau egal cu o anumită valoare (aceasta este tocmai restricția). Există o altă funcție care este oarecum similară în principiul său de funcționare și care este adesea confundată cu „Căutarea unei soluții”. Se numește „Selectarea opțiunii”. Dar dacă sapi mai adânc, diferența dintre ele este enormă:

1. Funcția Goal Seek nu funcționează cu mai mult de o variabilă.
2. Nu prevede posibilitatea de a stabili limite pentru variabile.
3. Este capabil să determine doar egalitatea funcției obiectiv la o anumită valoare, dar nu face posibilă găsirea maximului și minimului. Prin urmare, nu este potrivit pentru sarcina noastră.
4. Capabil să calculeze eficient numai dacă modelul este de tip liniar. Dacă modelul este neliniar, atunci găsește valoarea care este cea mai apropiată de valoarea inițială.

Sarcina de transport este mult mai complicată în structura sa, așa că add-on-ul „Selectarea parametrilor” nu este suficient pentru aceasta. Să aruncăm o privire mai atentă asupra modului de implementare a funcției „Căutare soluție” în practică, folosind exemplul unei probleme de transport.

Un exemplu de rezolvare a unei probleme de transport în Excel

Pentru a demonstra clar cum să rezolvăm problemele de transport în practică în Excel, să dăm un exemplu.

Condiții sarcini

Să presupunem că avem 6 vânzători și 7 cumpărători. Cererea și oferta între ele sunt repartizate în felul următor: 36, 51, 32, 44, 35 și 38 de unități sunt vânzători și 33, 48, 30, 36, 33, 24 și 32 de unități sunt cumpărători. Dacă însumați toate aceste valori, veți descoperi că cererea și oferta sunt în echilibru. Prin urmare, această problemă este de tip închis, care se rezolvă foarte simplu.

În plus, avem informații despre cât trebuie să cheltuiți pentru transportul de la punctul A la punctul B (sunt evidențiate în celule galbene în exemplu).

Soluție – algoritm pas cu pas

Acum, după ce ne-am familiarizat cu tabelele cu datele inițiale, putem folosi următorul algoritm pentru a rezolva această problemă:

1. Mai întâi, facem un tabel format din 6 rânduri și 7 coloane.
2. După aceea, mergem la orice celulă care nu conține nicio valoare și, în același timp, se află în afara tabelului nou creat și inserăm funcția. Pentru a face acest lucru, faceți clic pe butonul fx, care este situat în stânga liniei de introducere a funcției.
3. Avem o fereastră în care trebuie să selectăm categoria „Matematică”. Ce funcție ne interesează? Cel evidențiat în această captură de ecran. Funcţie SUMPRODUCT înmulțește intervale sau matrice între ele și le însumează. Exact ce ne trebuie. După aceea, apăsați tasta OK.
4. În continuare, pe ecran va apărea o fereastră în care trebuie să specificați parametrii funcției. Acestea sunt următoarele:
   1. Matrice 1. Acesta este primul argument în care scriem intervalul care este evidențiat cu galben. Puteți seta parametrii funcției fie folosind tastatura, fie selectând zona corespunzătoare cu butonul stâng al mouse-ului.
   2. Matrice 2. Acesta este al doilea argument, care este tabelul nou creat. Acțiunile sunt efectuate în același mod.

Confirmați acțiunea dvs. apăsând butonul OK.

1. După aceea, facem un clic stânga al mouse-ului pe celula care servește drept partea din stânga sus în tabelul nou creat. Acum faceți clic din nou pe butonul funcției de inserare.
2. Selectăm aceeași categorie ca în cazul precedent. Dar de data aceasta ne interesează funcția USM.
3. Acum vine etapa de completare a argumentelor. Ca prim argument, scriem rândul de sus al tabelului pe care l-am creat la început. În același mod ca și înainte, acest lucru se poate face selectând aceste celule pe foaie sau manual. Confirmăm acțiunile noastre apăsând butonul OK.
4. Vom vedea rezultatele în celula cu funcția. În acest caz, este zero. Apoi, mutați cursorul în colțul din dreapta jos, după care va apărea un marcator de completare automată. Arată ca un mic plus negru. Dacă apare, țineți apăsat butonul stâng al mouse-ului și mutați cursorul la ultima celulă din tabelul nostru.
5. Acest lucru ne oferă posibilitatea de a transfera formula în toate celelalte celule și de a obține rezultatele corecte fără a fi nevoie să efectuăm calcule suplimentare.
6. Următorul pas este să selectați celula din stânga sus și să lipiți funcția USM în ea. După aceea, introducem argumentele și folosim marcatorul de completare automată pentru a completa toate celulele rămase.
7. După aceea, trecem direct la rezolvarea problemei. Pentru a face acest lucru, vom folosi suplimentul pe care l-am inclus mai devreme. Accesați fila „Date” și acolo găsim instrumentul „Căutați o soluție”. Facem clic pe acest buton.
8. Acum a apărut o fereastră în fața ochilor noștri, prin care puteți configura parametrii suplimentului nostru. Să aruncăm o privire la fiecare dintre aceste opțiuni:
   1. Optimizați funcția obiectiv. Aici trebuie să selectăm celula care conține funcția SUMPRODUCT. Vedem că această opțiune face posibilă selectarea unei funcții pentru care va fi căutată o soluție.
   2. Inainte de. Aici setăm opțiunea „Minim”.
   3. Prin schimbarea celulelor variabilelor. Aici indicăm intervalul corespunzător tabelului pe care l-am creat la început (cu excepția rândului și coloanei rezumative).
   4. Sub rezerva restricțiilor. Aici trebuie să adăugăm constrângeri făcând clic pe butonul Adăugare.
   5. Ne amintim ce fel de constrângere trebuie să creăm – suma valorilor cererilor cumpărătorilor și ofertelor vânzătorilor trebuie să fie aceeași.
9. Sarcina restricțiilor se realizează după cum urmează:
   1. Link la celule. Aici introducem intervalul tabelului pentru calcule.
   2. Termeni. Aceasta este o operație matematică față de care se verifică intervalul specificat în primul câmp de intrare.
   3. Valoarea condiției sau constrângerii. Aici introducem coloana corespunzătoare în tabelul sursă.
   4. După ce toți pașii sunt finalizați, faceți clic pe butonul OK, confirmând astfel acțiunile noastre.

Efectuăm exact aceleași operații pentru rândurile de sus, punând următoarea condiție: trebuie să fie egale.

Următorul pas este stabilirea condițiilor. Trebuie să setăm următoarele criterii pentru suma celulelor din tabel - mai mare sau egal cu zero, un număr întreg. Ca urmare, avem o astfel de listă de condiții în care problema este rezolvată. Aici trebuie să vă asigurați că caseta de selectare de lângă opțiunea „Faceți variabilele fără limite nenegative” este bifată. De asemenea, în situația noastră, se cere să se aleagă metoda de rezolvare a problemei – „Căutarea unei soluții la problemele neliniare ale metodelor OPG”. Acum putem spune cu siguranță că setarea este făcută. Prin urmare, rămâne doar să efectuați calculele. Pentru a face acest lucru, faceți clic pe butonul „Găsiți o soluție”.

După aceea, toate datele vor fi calculate automat, iar apoi Excel va afișa o fereastră cu rezultatele. Este necesar pentru a verifica de două ori funcționarea computerului, deoarece erorile sunt posibile dacă condițiile au fost setate anterior incorect. Dacă totul este corect, faceți clic pe butonul „OK” și vedeți tabelul terminat.

Dacă se dovedește că sarcina noastră a devenit un tip deschis, atunci acest lucru este rău, deoarece trebuie să editați tabelul sursă, astfel încât sarcina să se transforme într-una închisă. Cu toate acestea, când se face acest lucru, algoritmul rămas va fi același.

Concluzie

După cum puteți vedea, Excel poate fi folosit și pentru calcule foarte complexe, care la prima vedere nu sunt disponibile unui program de calculator simplu care este instalat în aproape toată lumea. Cu toate acestea, este. Astăzi am acoperit deja nivelul avansat de utilizare. Acest subiect nu este atât de simplu, dar după cum se spune, drumul va fi stăpânit de cel care merge pe jos. Principalul lucru este să urmați planul de acțiune și să efectuați cu exactitate toate acțiunile indicate mai sus. Atunci nu vor exista erori, iar programul va efectua independent toate calculele necesare. Nu va fi nevoie să vă gândiți la ce funcție să utilizați și așa mai departe.