Dimensiune: px

Începeți să afișați de pe pagină:

Transcriere

1 Ministerul Educației și Științei Federației Ruse Bugetul de stat federal Instituția de învățământ de învățământ profesional superior „Universitatea de Stat din Pacific” Rezolvarea problemelor de programare liniară în Microsoft Excel 00 Orientări pentru efectuarea lucrărilor de laborator în informatică pentru studenții de la toate licența și cu normă întreagă programe de specialitate Editura Khabarovsk TOGU 05

2 UDC 68.58(076.5) Rezolvarea problemelor de programare liniară în Microsoft Excel 00: linii directoare pentru efectuarea lucrărilor de laborator în informatică pentru studenții din toate programele de specialitate de licență și cu normă întreagă / comp. N. D. Berman, N. I. Shadrina. Khabarovsk: Editura Pacific. stat universitate, p. Orientările au fost compilate la Departamentul de Informatică. Include informații generale despre probleme de programare liniară, sarcini pentru efectuarea lucrărilor de laborator cu variante de probleme și o bibliografie recomandată. Publicat în conformitate cu hotărârile Catedrei de Informatică și ale Consiliului Metodologic al Facultății de Informatică și Științe de bază. Universitatea de Stat din Pacific, 05

3. PROBLEME DE PROGRAMARE LINEARĂ ÎN MICROSOFT EXCEL 00. INFORMAȚII GENERALE Caracteristicile generale ale problemelor de optimizare Problemele de optimizare liniară aparțin unei clase larg răspândite de probleme întâlnite în diverse domenii de activitate: în afaceri, în producție, în viața de zi cu zi. Cum să vă gestionați optim bugetul sau să ajungeți la locul potrivit în oraș în cel mai scurt timp, cum să planificați cel mai bine întâlnirile de afaceri, să minimizați riscurile investițiilor de capital, să determinați rezervele optime de materii prime din depozit - acestea sunt sarcinile în de care trebuie să găsiți cea mai bună dintre toate soluțiile posibile. Se disting următoarele tipuri de probleme de optimizare liniară: probleme de transport, de exemplu, minimizarea costurilor de livrare a mărfurilor din mai multe fabrici către mai multe magazine, ținând cont de cerere; sarcini de repartizare a locurilor de muncă, de exemplu, minimizarea costurilor cu personalul în conformitate cu cerințele prevăzute de lege; managementul sortimentului de produse: extragerea profitului maxim prin variarea sortimentului de mărfuri (în același timp cu îndeplinirea cerințelor clienților). O problemă similară apare la vânzarea mărfurilor cu structuri de costuri, indicatori de rentabilitate și cerere diferiți; înlocuirea sau amestecarea materialelor, de exemplu, manipularea materialelor pentru a reduce costurile, a menține nivelul necesar de calitate și a îndeplini cerințele clienților; problema cu dieta. Din produsele disponibile, este necesar să se creeze o dietă care, pe de o parte, să satisfacă nevoile nutriționale minime ale organismului (proteine, grăsimi, carbohidrați, săruri minerale, vitamine), iar pe de altă parte ar necesita cel mai mic cost; sarcina alocării resurselor, de exemplu, distribuirea resurselor între locuri de muncă în așa fel încât să maximizeze profiturile, sau să minimizeze costurile, sau să determine componența locurilor de muncă care pot fi finalizate folosind resursele disponibile și, în același timp, să se realizeze o definiție maximă .

4 măsuri împărțite ale eficienței sau calculați ce resurse sunt necesare pentru a finaliza o anumită lucrare la cel mai mic cost. Formularea matematică a problemei de programare liniară Să considerăm cea mai comună clasă de probleme de optimizare - problemele de programare liniară. Această clasă include probleme descrise de modele matematice liniare. O problemă generală de programare liniară este o sarcină care constă în determinarea valorii maxime (minime) a funcției () în condițiile: () () () (3) () (4) unde valorile constante date și Funcția () se numește funcția obiectivă a problemei și condițiile ()(4) restricțiile problemei. Mulțimea numerelor () care satisfac constrângerile problemei se numește soluție admisibilă. Soluția în care funcția obiectiv a problemei ia valoarea maximă (minimă) se numește optimă. Utilizarea unui program de completare Excel pentru a rezolva probleme de programare liniară Căutarea unei soluții este un program de completare EXCEL care vă permite să rezolvați probleme de optimizare. Dacă comanda Găsiți o soluție sau grupul de analiză lipsește, trebuie să descărcați suplimentul Găsiți o soluție. 4

5 În fila Fișier, selectați comanda Opțiuni, apoi categoria Add-ins (Fig.). Orez. În caseta Gestionare, selectați Add-ins Excel și faceți clic pe Go. În câmpul Suplimente disponibile, bifați caseta de selectare de lângă Căutare soluție (Figură) și faceți clic pe OK. Orez. Un exemplu de rezolvare a problemelor liniare de optimizare în MS Excel 00 Schema de rezolvare a problemelor de programare liniară în MS Excel 00 este următoarea: 5

6. Creați un model matematic Introduceți condițiile problemei pe foaia de lucru Excel: a) creați un formular pe foaia de lucru pentru introducerea condițiilor problemei; b) introduceți datele inițiale, funcția obiectivă, restricțiile și condițiile limită. 3. Specificați parametrii în caseta de dialog Căutare soluție. 4. Analizați rezultatele obținute. Să luăm în considerare rezolvarea problemei de optimizare folosind un exemplu. Exemplu. Sarcina de a determina gama optimă de produse Întreprinderea produce două tipuri de produse P și P, care sunt vândute cu ridicata. Pentru fabricarea produselor se folosesc doua tipuri de materii prime A si B Rezervele maxime posibile de materii prime pe zi sunt de 9 si 3 unitati. respectiv. Consumul de materii prime pe unitate de produs tip P și tabelul P Materii Prime Consumul de materii prime pe unitate. produse P P Stoc de materii prime, unitati. A 3 9 B 3 3 Experiența a arătat că cererea zilnică pentru produsele P nu depășește niciodată cererea pentru produsele P cu mai mult de o unitate. În plus, se știe că cererea de produse P nu depășește niciodată unitățile. pe zi. Preturile cu ridicata pe unitatea de productie sunt egale cu: 3 unitati pentru P si 4 unitati pentru P. Ce cantitate din fiecare tip de produs ar trebui sa produca intreprinderea pentru ca venitul din vanzarea produselor sa fie maxim? Soluţie. Să construim un model matematic pentru a rezolva problema. Să presupunem că întreprinderea va produce x unități de produs P și x unități de produs P. Întrucât producția este limitată de materiile prime de fiecare tip de care dispune întreprinderea și de cererea pentru aceste produse și, de asemenea, ținând cont de faptul că numărul a produselor fabricate nu poate fi negativă, trebuie satisfăcute următoarele inegalități: 6

7 Venitul din vânzarea a x unități de produs P și x unități de produs P va fi Dintre toate soluțiile nenegative ale acestui sistem de inegalități liniare, este necesar să se găsească una la care funcția F să ia valoarea maximă F max. Problema luată în considerare aparține categoriei problemelor tipice de optimizare a programului de producție al unei întreprinderi. Următoarele pot fi utilizate, de asemenea, ca criterii de optimitate în aceste probleme: profitul, costul, gama de produse produse și costurile de timp ale mașinii. Să creăm un formular pe foaia de lucru pentru introducerea datelor inițiale (Fig. 3). Celulele pentru introducerea funcțiilor sunt evidențiate cu umplere. Orez. 3 În celula E5, introduceți formula funcției obiectiv (Fig. 4). Folosind denumirile celulelor corespunzătoare în Excel, formula pentru calcularea funcției obiectiv poate fi scrisă ca suma produselor fiecăreia dintre celulele alocate pentru valorile variabilelor problemei (B3, C3) de către celulele corespunzătoare alocați pentru coeficienții funcției obiectiv (B5, C5). 7

8 Fig. 4 În mod similar, formulele pentru calcularea părții stângi a restricțiilor sunt introduse în celulele D0:D (Fig. 5). Orez. 5 În fila Date, în grupul Analiză, selectați comanda Căutare soluție. În caseta de dialog Solution Search Parameters, setați următoarele (Fig. 6): 8

9 în câmpul Optimizați funcția obiectiv, selectați celula cu valoarea funcției obiectiv E5; alegeți dacă să maximizați sau să minimizați funcția obiectiv; în câmpul Modificare celule variabile, selectați celule cu valorile variabilelor dorite B3:C3 (atâta timp cât acestea conțin zerouri sau goale); în zona În conformitate cu restricțiile, folosind butonul Adăugare, plasăm toate restricțiile sarcinii noastre (Fig. 7); în câmpul Selectare metoda soluției, indicați Căutare soluții la probleme liniare folosind metoda simplex; Faceți clic pe butonul Găsiți soluție. Orez. 6 9

10 Adăugați restricții pentru sarcina noastră. Pentru inegalități, indicați intervalul D0:D în câmpul Link to cells, selectați semnul inegalității din lista derulantă, selectați intervalul F0:F în câmpul Constrângere și faceți clic pe butonul Adaugă (Fig. 7) pentru a accepta constrângere și adăugați următoarea constrângere. Pentru a accepta constrângerea și a reveni la caseta de dialog Găsiți o soluție, faceți clic pe Ok. Orez. 7 Să arătăm ferestre pentru adăugarea restricțiilor: convertiți în (Fig. 8); Orez. 80

11 (Fig. 9); Orez. 9, (Fig. 0). Orez. 0 După selectarea butonului Găsire soluție, apare fereastra Rezultatele căutării soluției (Fig.). Orez.

12 Pentru a salva soluția rezultată, trebuie să utilizați comutatorul Salvare soluție găsită din caseta de dialog Rezultatele căutării soluției care se deschide. După care foaia de lucru va lua forma prezentată în Fig.. Fig. Puteți salva modelul de căutare a soluției după cum urmează:) când salvați registrul de lucru Excel după căutarea unei soluții, toate valorile introduse în ferestrele de dialog Căutare soluție sunt salvate împreună cu datele din foaia de lucru. Cu fiecare foaie de lucru din registrul de lucru, puteți salva un set de valori pentru parametrii de căutare a soluției;) dacă într-o singură foaie de lucru Excel trebuie să luați în considerare mai multe modele de optimizare (de exemplu, găsiți maximul și minimul unei funcții sau maximul valorile mai multor funcții), atunci este mai convenabil să salvați aceste modele, folosind butonul Încărcare/Salvare din fereastra Opțiuni de căutare a soluției. Intervalul pentru modelul salvat conține informații despre celula țintă, despre celulele care trebuie modificate, despre fiecare dintre constrângeri și toate valorile din dialogul Opțiuni. Selectarea unui model pentru rezolvarea unei anumite probleme de optimizare se realizează cu ajutorul butonului Load/Save din caseta de dialog Solution Search Parameters; 3) puteți salva modelul sub formă de scripturi denumite, pentru a face acest lucru, trebuie să faceți clic pe butonul Salvare script din caseta de dialog Rezultatele căutării soluției (vezi figura). Pe lângă inserarea valorilor optime în celulele editate, Solver vă permite să prezentați rezultate sub forma a trei rapoarte (Rezultate,

13 Stabilitate și limite). Pentru a genera unul sau mai multe rapoarte, trebuie să le selectați numele în caseta de dialog Rezultatele căutării soluției (Fig.). Să aruncăm o privire mai atentă la fiecare dintre ele. Raportul de reziliență (Figura 3) oferă informații despre cât de sensibilă este celula țintă la modificările constrângerilor și variabilelor. Acest raport are două secțiuni: una pentru celule modificabile și una pentru restricții. Coloana din dreapta din fiecare secțiune conține informații de sensibilitate. Fiecare celulă și restricțiile care pot fi modificate sunt listate pe o linie separată. Când utilizați constrângeri întregi, Excel afișează mesajul Rapoartele de stabilitate și Limitele nu sunt aplicabile pentru problemele cu constrângerile întregi. Orez. 3 Raportul privind rezultatele (Fig. 4) conține trei tabele: primul conține informații despre funcția obiectiv înainte de începerea calculului, al doilea conține valorile variabilelor căutate obținute ca urmare a rezolvării problemei, iar al treilea conține rezultatele soluției optime pentru constrângeri. Acest raport conține, de asemenea, informații despre starea și diferența fiecărei constrângeri. Statutul poate avea trei stări: legat, nelegat sau neîmplinit. Valoarea diferenței este diferența dintre valoarea afișată în celula de constrângere la obținerea soluției și numărul specificat în partea dreaptă a formulei de constrângere. O constrângere legată este o constrângere pentru care valoarea diferenței este zero. Fără legătură 3

14 constrângere este o constrângere care a fost satisfăcută cu o valoare a diferenței diferite de zero. Orez. 4 Raportul de limite (Fig. 5) conține informații despre limitele în care valorile celulelor modificate pot fi mărite sau micșorate fără a încălca constrângerile sarcinii. Pentru fiecare celulă care este modificată, acest raport conține valoarea optimă, precum și cele mai mici valori pe care celula le poate accepta fără a-și încălca constrângerile. Orez. 5 4

15 Soluția rezultată înseamnă că volumul de producție al produselor de tip P trebuie să fie egal cu .4 unități, iar al produselor P. 4 unități. produse. Venitul primit în acest caz va fi de 8 unități Să presupunem că la condițiile problemei a fost adăugată cerința ca valorile tuturor variabilelor să fie întregi. În acest caz, procesul de introducere a condițiilor de problemă descris mai sus trebuie completat cu următorii pași. În fereastra Căutare soluție, faceți clic pe butonul Adăugare și în fereastra Adăugarea de constrângeri care apare, introduceți constrângeri după cum urmează (Fig. 6): în câmpul Link to cells, introduceți adresele celulelor variabilelor sarcinii B3 :C3; setați câmpul de introducere a semnului limită la un număr întreg; Confirmați introducerea restricției apăsând butonul OK. Orez. 6 Rezolvarea problemei cu condiția ca variabilele acesteia să fie întregi fig. 7. Fig. 7 5

16 . LUCRĂRI DE LABORATOR Lucrări de laborator Sarcină Aflați maximul unei funcții liniare într-un sistem dat de constrângeri. Opțiune Funcție obiectiv F Constrângeri ( ( ( ( 3 ( ( 4 ( ( 5 ( ( 6 ( ( 7 ( ( 8 ( ( 9 ( ( 0 ( ( ( ( ( 3 ( ( 4 ( ( 5 ( ( 6)

17 Lucrări de laborator Misiunea. Construiți un model matematic al problemei. Prezentați-l sub formă de tabel pe o foaie Excel. 3. Găsiți o soluție la problemă utilizând suplimentul Căutare soluție. 4. Raportați rezultatele și sustenabilitatea. Opțiune Pentru a produce mese și dulapuri, o fabrică de mobilă folosește resursele necesare. Ratele de cheltuire a resurselor pentru un produs de un anumit tip, profitul din vânzarea unui produs și cantitatea totală de resurse disponibile pentru fiecare tip sunt tabelul Resurse Lemn, m 3: - al-lea tip - al-lea tip Rate de cheltuieli pentru un produs Tabel Cabinet 0, 0, 0 , 0,3 Suma totală de resurse Intensitatea muncii, ore persoană, 5 37,4 Profit din vânzarea unui produs, frec. 6 8 Determinați câte mese și dulapuri ar trebui să producă fabrica pentru a maximiza profitul din vânzarea lor. Răspuns. Profit 940 rub. cu numărul de mese și dulapuri fiind 0 și 66. Opțiune Pentru producerea a două tipuri de produse A și B se folosesc echipamente de strunjire, frezare și șlefuire. Normele de timp petrecut pentru fiecare tip de echipament pe un produs de un anumit tip, timpul total de lucru pentru fiecare tip de echipament, precum și profitul din vânzarea unui produs în tabel. 3.7

18 Tabel 3 Timp consum, oră mașină, Tip de echipament pentru prelucrarea unui produs A B Frezare 0 8 Strunjire 5 0 Măcinare 6 Profit din vânzarea unui produs, frec. 4 8 Timpul total de lucru util al echipamentelor, h Găsiți un plan de producție pentru produsele A și B care să asigure profit maxim din vânzarea acestora. Răspuns. Profit 76 rub. la producerea produselor și 6. Opțiunea 3 Pentru fabricarea a trei tipuri de produse A, B și C se folosesc echipamente de strunjire, frezare, sudură și șlefuire. Timpul alocat procesării unui produs pentru fiecare tip de echipament, timpul total de lucru pentru fiecare tip de echipament utilizat, profitul din vânzarea unui produs din acest tip de masă. 4. Tabelul 4 Tipul echipamentului Frezare Strung Sudare Polizare Timp cheltuit, mașină-oră, pentru prelucrarea unui produs de tip A B C Profit, frec. 0 4 Timpul total de funcționare al echipamentului, h Este necesar să se determine câte produse și ce tip ar trebui să producă întreprinderea pentru ca profitul din vânzarea acestora să fie maximizat. Răspuns. Profit 49 rub. la eliberarea produselor 4, 8, 0. 8

19 Opțiunea 4 Pentru a menține funcțiile normale de viață, o persoană trebuie să consume cel puțin 8 g de proteine, 56 g de grăsimi, 500 g de carbohidrați și 8 g de săruri minerale în fiecare zi. Cantitatea de nutrienți conținută în kg din fiecare tip de aliment consumat, precum și prețul pe kg al fiecăruia dintre aceste produse, tabel. 5 Tabel 5 Conținut de nutrienți, g, de nutrienți per kg de produse Carne Pește Lapte Unt Brânză Crupe Cartofi Proteine ​​Grăsimi Glucide Săruri minerale Preț kg produse, rub., 8,0 0,8 3,4,9 0,5 0, Alcătuiește o dietă zilnică care să conțină cel puțin necesarul zilnic minim al unei persoane de nutrienți esențiali la un cost total minim al produselor consumate. Răspuns. Cost total minim 0, frecați. cu numarul de produse: carne 0; pește 0; lapte 0; ulei 0,03335; brânză 0; cereale 0,9053; cartofi 0. Opțiunea 5 O fabrică de cofetărie pentru producerea a trei tipuri de caramel A, B și C utilizează trei tipuri de materii prime principale: zahăr granulat, melasă și piure de fructe. Ratele de consum ale fiecărui tip de materie primă pentru producția de tone de caramel de un anumit tip, cantitatea totală de materii prime de fiecare tip, profit din vânzarea de tone de masă de caramel. 6.9

20 Tabelul 6 Tipul de materii prime Zahăr granulat Melasă Piure de fructe Rate consum de materie primă, t, per t caramel A B C 0,8 0,4 0,5 0,4 0, 0,6 0,3 0, Profit din vânzări t produse, p Cantitatea totală de materii prime, t Găsiți a plan de productie de caramel care asigura profit maxim din vanzarea acestuia. Răspuns. Profit maxim p. la producerea caramelului 00, 0, 00 t Opțiunea 6 La o fabrică de îmbrăcăminte, țesătura din trei articole poate fi utilizată pentru a produce patru tipuri de produse. Ratele de consum de țesături ale tuturor articolelor pentru coaserea unui produs, cantitatea totală de țesături din fiecare articol disponibilă în fabrică și prețul unui produs de acest tip sunt tabel. 7. Tabelul 7 Articol de țesătură I II III Rata consumului de țesături, m, pentru un produs de tipul 3 4 Prețul unui produs, p Cantitatea totală de țesătură, m Determinați câte produse de fiecare tip ar trebui să producă fabrica în funcție de cost de produse fabricate să fie maximă. Răspuns. Costul maxim al produselor este de 5 ruble. la eliberarea produselor 95, 0, 0, 0. 0

21 Opțiunea 7 Compania produce patru tipuri de produse și utilizează trei tipuri de echipamente principale: strunjire, frezare și șlefuire. Timpul alocat producerii unei unități de produs pentru fiecare tip de echipament, timpul total de lucru pentru fiecare tip de echipament și profitul din vânzarea unui produs de acest tip sunt tabel. 8. Tabel 8 Timp consum, mașină-oră, Tip echipament pe unitate de produs tip 3 4 Strunjire Frezare Măcinare Profit din vânzările a 3 unități de produs, frec. 8 3 Fond total timp de lucru, stan.-h Determinați volumul de producție al fiecărui produs la care profitul total din vânzarea acestora este maxim. Răspuns. Profit maxim 965 rub. la eliberarea produselor 70, 35, 0, 0. Opțiunea 8 O întreprindere comercială intenționează să organizeze vânzarea a patru tipuri de mărfuri, folosind doar două tipuri de resurse: timpul de lucru al vânzătorilor în valoare de 840 de ore și suprafața de vânzări. de 80 m. În acest caz, sunt cunoscute standardele de cost planificate ale acestor resurse pe unitatea de marfă și profit din tabelul lor de vânzare. 9. Tabelul 9 Indicatori Consumul de timp de muncă pe unitatea de marfă, h Utilizarea suprafeței de vânzare pe unitatea de marfă, m Produs A B C D 0,6 0,8 0,6 0,4 0, 0, 0,4 0, Profit din vânzări unitare, p Valoarea totală a resurselor

22 Este necesar să se determine structura optimă a cifrei de afaceri comerciale care să asigure întreprinderii comerciale profit maxim. Răspuns. Profit maxim 6 00 rub. la vânzarea mărfurilor 0, 0, 0, 800. Opțiunea 9 Din trei tipuri de materii prime este necesar să se creeze un amestec, care trebuie să cuprindă cel puțin 6 unități. substanta chimica A, 30 unitati. substanțele B și 4 unități. substanțe C. Numărul de unități dintr-o substanță chimică conținute într-un kg de materie primă de fiecare tip, prețul unui kg de materie primă din fiecare tip tabel. 0 Tabel 0 Substanță A B C Preț kg materii prime, frec. Numărul de unități dintr-o substanță conținute într-un kg de materie primă de un tip Compune un amestec care conține cel puțin cantitatea necesară de substanțe de un anumit tip și având un cost minim. Răspuns. Costul minim 6 ruble. cu cantitatea 0; 0; 0; 6,5 kg. Opțiunea 0 Pentru a produce trei tipuri de produse, întreprinderea folosește două tipuri de echipamente tehnologice și două tipuri de materii prime. Standarde pentru costul materiilor prime și timpul de fabricație a unui produs de fiecare tip, timpul total de lucru al fiecărui grup de echipamente tehnologice, volumul de materii prime disponibile de fiecare tip, prețul unui produs de fiecare tip, restricții asupra posibilei producţii a fiecărui produs din tabel.

23 Resurse Productivitatea echipamentelor în ore standard: tipul I tip II Materii prime, kg: tipul -tipul -tipul Prețul unui produs, frec. Ieșire, buc.: minim maxim Standarde de cost pentru un produs de tip Tabel Cantitatea totală de resurse Întocmește un plan de producție conform căruia se va fabrica numărul necesar de produse de fiecare tip, la costul total maxim al tuturor produselor fabricate. Răspuns. Cost total 495 ruble. la producerea produselor 0, 33, 45. Opțiune La producerea a patru tipuri de cablu se execută cinci grupe de operații tehnologice. Tarife de cost pe km de cablu de un anumit tip pentru fiecare grup de operațiuni, profit din vânzarea de km a fiecărui tip de cablu, precum și timpul total de lucru în care se pot efectua aceste operațiuni, Tabel. Tabel Funcționare tehnologică Norme de timp petrecut, h, pentru prelucrarea km de cablu tip 3 4 Desen Aplicarea izolației Răsucirea elementelor într-un cablu Conducător Testare și control, 0 6.4 3.0.8 0.4 5.6.5.6 0.8 6.0.8 0.8.4 0.7 8.0. 4 3.0 Profit din vânzarea km de cablu, rub., 0.8.0.3 Timp total de lucru, h

24 Determinați planul de producție de cabluri la care profitul total din vânzarea produselor fabricate este maxim. Răspuns. Profit total din vânzări 939,48 57 rub. la lansarea 00; 64,8 57; 0; 0. Opțiune Tijele de oțel de 0 cm lungime trebuie tăiate în bucăți de 45, 35 și 50 cm lungime. Numărul necesar de bucăți de acest tip este de 40, 30 și respectiv 0 bucăți. Opțiunile de tăiere posibile și cantitatea de deșeuri pentru fiecare dintre ele sunt în tabel. 3. Tabel 3 Opțiuni de tăiere Lungimea piesei de prelucrat, cm Cantitatea deșeurilor, cm Determinați câte tije pentru fiecare dintre opțiunile posibile trebuie tăiate pentru a obține cel puțin numărul necesar de piese de prelucrat de fiecare tip cu deșeuri minime. Răspuns. Deșeul minim este de 550 cm cu numărul de tije 0, 0, 0, 0, 0, 0 buc. Opțiunea 3 Pentru a produce trei tipuri de produse A, B, C, compania utilizează patru tipuri de materii prime. Tarife de cost pentru fiecare tip de materie primă pentru producerea unei unități de produs de un anumit tip, profit din vânzarea unui produs de fiecare tip, tabel. 4.4

25 Tabelul 4 Ratele de cost pentru materii prime, kg, pe unitate de produs Tipul de materii prime A B C I II III IV Profit din vânzarea unui produs Produsele A, B și C pot fi produse în orice proporție (vânzările sunt asigurate), dar pentru producția lor întreprinderea poate folosi materii prime tip I nu mai mult de 00 kg, tip II nu mai mult de 0 kg, tip III nu mai mult de 80 kg, tip IV nu mai mult de 38 kg. Determinați planul de producție conform căruia profitul total al întreprinderii din vânzarea tuturor produselor ar fi cel mai mare. Răspuns. Planul de producție pentru produse este de 7, 5, 0 kg cu un profit total de 5 kg. Opțiunea 4 O agenție de turism urmează să comande o editură să producă albume de artă de trei tipuri A, B, C. Producția lor este limitată de costurile a trei tipuri de resurse, ale căror costuri unitare sunt date în tabel. 5. Tipul resursei Finanțe, $ Hârtie, l. Costuri cu forța de muncă, oameni h Tabel 5 Costuri specifice cu resurse pentru lansarea albumelor A B C 4 4 Editura a primit resurse financiare în valoare de 3.600 USD pentru a onora comanda, are l. hârtie și poate folosi resurse de muncă în valoare de 00 persoane. h. Agenția plătește pentru lansarea unui album de tip A, 8 USD, pentru albumul B, 8 USD, pentru albumul C, 30 USD. 5

26 Câte albume de fiecare tip ar trebui să producă un editor pentru a obține cel mai mare profit? Răspuns. Venitul total maxim USD, număr de albume: 400; 800; 0 buc. Opțiunea 5 O întreprindere de comerț cu ridicata poate vinde T j, j, 4 grupe de mărfuri. Pentru aceasta sunt folosite mai multe tipuri de resurse. Date inițiale pentru construirea unui tabel model matematic. 6. Limitarea resurselor și indicatori Grupa de produse T T T 3 T4 Volumul resursei Tabel 6 Spațiu depozit, m Resurse forță de muncă, ore persoană Costuri de distribuție, den. unitati Stocuri, den. unități Planul cifrei de afaceri comerciale, den. unități Valori minime admise ale cifrei de afaceri comerciale pentru a j-a grupă, unități. Profit pe unitatea de cifra de afaceri a grupei j-a, den. unitati Tipul de limitare Este necesar să se calculeze un plan pentru activitatea economică a unei întreprinderi comerciale care să asigure un profit maxim sub anumite restricții privind spațiul depozitului, resursele de muncă, costurile de distribuție, stocul și valoarea cifrei de afaceri, dacă profitul comercial pe unitatea de este dată cifra de afaceri a grupei j-a. Răspuns. Profit maxim. unitati Cifra de afaceri pe grupe: T 00 unitati, T 000 unitati, T unitati, T unitati. 6

27 3. LISTA BIBLIOGRAFICĂ RECOMANDATĂ. Akulich, I. L. Programare matematică în exemple și probleme: manual. manual pentru studenții de economie. specialist. Suzov / I. L. Akulici. M.: Mai sus. școală, pp. Leonenkov, A. V. Rezolvarea problemelor de optimizare în MS Excel / A. V. Leonenkov. SPb. : BHV-Petersburg, p. 3. Vasiliev, A. N. Modelare financiară și optimizare folosind Excel007 / A. N. Vasiliev. SPb. : Petru, p. 4. Walkenbach, J. Microsoft Excel 00. Biblia utilizatorului: trad. din engleza / J. Walkenbach. M.: I. D. Williams, 0. 9 p. 5. Walkenbach, J. Formule în Microsoft Excel 00: trad. din engleza / J. Walkenbach. M.: I. D. Williams, p. 6. Ivanov, I. Microsoft Excel 00 pentru un utilizator calificat / I. Ivanov. M.: Academia IT, p. 7. Ajutor și instrucțiuni pentru Excel // Suport pentru Microsoft Office [Resursă electronică]. Mod de acces: (data accesului:). 8. Rezolvarea problemelor de optimizare a managementului folosind MS Excel 00 // NOU “INTUIT” [Resursa electronică]. Mod de acces: (data accesului:). Cuprins. Probleme de programare liniară în Microsoft Excel 00. Informații generale... 3 Caracteristicile generale ale problemelor de optimizare... 3 Formularea matematică a unei probleme de programare liniară... 4 Utilizarea unui add-in Excel pentru a rezolva probleme de programare liniară... 4 An exemplu de rezolvare a problemelor de optimizare liniară în MS Excel Lucrări de laborator... 6 Lucrări de laborator... 6 Lucrări de laborator Bibliografie recomandată

28 Rezolvarea problemelor de programare liniară în Microsoft Excel 00 Ghid pentru efectuarea lucrărilor de laborator în informatică pentru studenții din toate programele de licență și de specialitate cu normă întreagă Nina Demidovna Berman Nina Ivanovna Shadrina Redactor-șef L. A. Suevalova Editor E. N. Yarulina Semnat pentru publicare Format 60 x 84 / 6. Hârtie de scris. Căști „Calibri”. Printare digitala. Condiţional cuptor l., 68. Tiraj 60 exemplare. Ordinul 70. Editura Universității de Stat din Pacific, Khabarovsk, st. Pacific, 36. Departamentul de tipărire operațională al editurii Universității de Stat din Pacific, Khabarovsk, st. Pacific, 36. 8

PLANIFICAREA VOLUMETRICĂ A OPERĂRII SISTEMELOR DE MAȘINI TEHNOLOGICE Khabarovsk 2 0 0 9 Agenția Federală pentru Educație Instituție de stat de învățământ profesional superior

Lecția practică 3. 1. Pentru aceste condiții, formulați o problemă de optimizare, creați un model matematic, găsiți planul optim de producție folosind add-in-ul „Solution Search” în EXCEL.

Ministerul Educației și Științei al Federației Ruse Instituția de învățământ superior bugetară de stat „Universitatea de Stat din Pacific” N. I. Shadrina, N.

Întocmirea, rezolvarea și analizarea problemelor de programare liniară în Excel TASK. Construiți un model matematic al problemei și rezolvați-l folosind Excel. Notați problema asociată. Efectuați analize și faceți

Problema alocării resurselor întreprinderii Declarația satisfăcătoare a problemei Fabrica produce genți pentru femei, bărbați și de călătorie. Date despre materialele folosite la producerea pungilor și aprovizionarea lunară

Lucrări de laborator 11 Rezolvarea problemei alocării optime a resurselor Sarcină Întreprinderea produce mai multe tipuri de produse. Pentru producerea lor se folosesc diverse tipuri de materii prime. Standardele sunt cunoscute

Lucrări de laborator 3_9. Găsirea și luarea deciziilor în Excel. Ce se stăpânește și se studiază? Rezolvarea problemei de determinare a planului optim și a problemei de transport folosind suplimentul „Solution Search”. Exercițiu

Lucrări de laborator 3. Găsirea unei soluții în Microsoft Excel Scopul lucrării de laborator este de a studia capacitățile instrumentului Finding a Solution în MS Excel pentru rezolvarea problemelor de optimizare. La protecția laboratorului

MINISTERUL EDUCAȚIEI ȘI ȘTIINȚEI AL RF INSTITUȚIA DE ÎNVĂȚĂMÂNT BUGETARE DE STAT FEDERALĂ DE ÎNVĂȚĂMÂNTUL PROFESIONAL SUPERIOR „UNIVERSITATEA TEHNICĂ DE STAT DON” Departamentul „Tehnologie”

MINISTERUL EDUCAȚIEI ȘI ȘTIINȚEI AL FEDERAȚIEI RUSE BUGET FEDERAL DE STAT INSTITUȚIA DE ÎNVĂȚĂMÂNT SUPERIOR „UNIVERSITATEA DE STAT PACIFICA” Lucrări comune

LUCRUL DE LABORATOR INSTRUMENTE DE SPRIJIN DECIZII CA FUNCȚII EXCEL Selecția parametrilor echipei Sarcina 1. Luați în considerare o problemă compilată pe baza sarcinii de utilizare a funcției NPV. Tu ești întrebat

OPȚIUNE Pentru fabricarea a două tipuri de produse sunt disponibile 00 kg de metal. Pentru un produs de tipul --lea se consumă kg de metal, iar pentru un produs - kg. Întocmește un plan de producție care să asigure cel mai mare posibil

Lucrări de laborator 4 Tema de lucru: Rezolvarea problemei alocării optime a resurselor la producerea produselor folosind procedura de soluție Căutare Microsoft Excel. Scopul muncii: Învață să folosești

Lucrări practice 5.4. Rezolvarea problemei alocării optime a resurselor la lansarea produselor folosind procedura „Căutare soluție” în Microsoft Excel Scopul lucrării. După finalizarea acestei lucrări, veți învăța:

Academia de Stat de Tehnologie Chimică Fină din Moscova numită după M.V. Lomonosov Kornyushko V.F., Morozova O.A. Modele deterministe ale sistemelor economice Manual metodologic pentru disciplina Matematică

MINISTERUL EDUCAȚIEI AL FEDERAȚIEI RUSE DEPARTAMENTUL UNIVERSITĂȚII DE STAT KURGAN DEPARTAMENTUL „INFORMATICĂ” IMPLEMENTAREA MODELELOR DE OPTIMIZARE ÎN MEDIUL EXCEL Orientări pentru efectuarea testelor de laborator

Optimizarea programului de producție Orientări pentru lucrările de laborator privind economia industriei electrice Ulyanovsk 009 V 9 Vasiliev, V. N. Optimizarea programului de producție

Metode economico-matematice şi modelare. Lucrări practice 2. Metoda simplex pentru rezolvarea problemelor de programare liniară. Rezolvați o problemă de programare liniară (LP) folosind metoda simplex. Calcule

LUCRAREA 2 REZOLVAREA PROBLEMELOR DE PROGRAMARE LINEARĂ Scopul lucrării: familiarizarea cu metodele de rezolvare a problemelor de programare liniară în procesorul de calcul Ecel. Rezolvarea problemelor economice, de regulă, implică

AGENȚIA FEDERALĂ DE ÎNVĂȚĂMÂNT Instituție de învățământ de stat de învățământ profesional superior „Universitatea de Stat din Pacific” Departamentul „Tehnologia prelucrării lemnului” MODELARE

ANALIZA DATELOR ÎN MS EXCEL Gedranovich Valentina Vasilievna 27 iunie 2012 Rezumat Capitolul 11 ​​din UMK: Gedranovich, V.V. Fundamentele tehnologiilor informatice informatice: metoda educaţională. complex / V.V. Gedranovici,

Rezolvarea unei probleme de programare liniară folosind metoda grafică, metoda simplex și prin „Căutare soluție” în Ecel TASK. Compania produce două tipuri de produse: Produs și Produs. Pentru producerea unei unități

Lucrări de laborator 3. Add-in Căutați o soluție în Microsoft Excel. Manager de scripturi în Microsoft Excel. Scopul acestui laborator este de a explora capacitățile instrumentului Microsoft Solution Finder.

Instituție privată de învățământ non-statală de învățământ profesional superior Institutul Ural al Bursei de Valori Departamentul de Economie a Întreprinderilor ECONOMIA COMPANIEI Culegere de cazuri pe tema „Planificare”

Lecția practică 4. Pentru condițiile problemei, formulează o problemă duală și găsește estimări determinate obiectiv. Analizați utilizarea optimă a resurselor. Opțiunea 1. Pentru producție

MINISTERUL EDUCAȚIEI ȘI ȘTIINȚEI AL FEDERĂȚIA RUSĂ Instituție de învățământ bugetar de stat federal de învățământ profesional superior Departamentul „Universitatea de Stat Kurgan”

LAB 6 Subiect: Analiza datelor în OpenOffice Calc 1. Concepte de bază Procesul de modificare a valorilor celulelor și analiza impactului acestor modificări asupra rezultatului calculelor de formule în OpenOffice.org Calc se numește

Selectarea unui parametru Când se prelucrează date tabelare, este adesea nevoie să se prezică rezultatul pe baza datelor inițiale cunoscute sau, dimpotrivă, să se determine care ar trebui să fie datele inițiale

2 PLAN DE PRELEȚII: ANALIZA DATELOR ÎN MS EXCEL Informatică Semestrul II Kondratenko Olga Bronislavovna [email protected] Instrumentul de analiză What-if Instrumentul de analiză What-if creează tabele de date cu unul

Lucrări practice 13 Tema: PROBLEME DE OPTIMIZARE (CĂUTAREA SOLUȚIILOR) ÎN MICROSOFT EXCEL Scopul lecției. Studierea tehnologiei de găsire a soluțiilor pentru problemele de optimizare (minimizare, maximizare). Sarcina 13.1. Minimizarea

Anexă Conținutul cazului Sarcina 1 O companie comercială nou organizată a decis să producă două tipuri de scaune x1 și x2. Pentru producerea lor sunt necesare două tipuri de materiale: lemn și țesătură. Ferme lunar

LUCRĂRI DE LABORATOR 2 UTILIZAREA MICROSOFT EXCEL 2007 ÎN REZOLVAREA PROBLEMELOR PRACTICE (PENTRU ELEVII DIRECŢIEI 100800.62) 2.1 Rezolvarea problemelor de optimizare Problemă. Fabrica produce dispozitive electronice

COLEGIUL DE INGINERI RADIOALE DE LA MOSCOVA numit după. A.A Raspletina LUCRĂRI DE LABORATOR La tema „Metode matematice” „Probleme de programare liniară cu doi indici” Întocmită de: Profesor MRTK numit după A.A

MINISTERUL EDUCAȚIEI ȘI ȘTIINȚEI AL RF Instituția de învățământ autonomă de stat federală de învățământ superior „UNIVERSITATEA POLITEHNICĂ DE CERCETARE NAȚIONALĂ TOMSK” AM APROBAT

CONŢINUT. SARCINA.... ETAPELE LUCRĂRII..... Formarea unui model matematic al problemei..... Rezolvarea problemei directe folosind metoda simplex..... Construirea problemei duale... 6.4. Rezolvarea directă și duală

LUCRĂRI DE LABORATOR REZOLVAREA PROBLEMELOR DE PROGRAMARE LINEARĂ UTILIZAREA Microsoft Ecel OBIECTIVUL LUCRĂRII Dobândirea deprinderilor de rezolvare a problemelor de programare liniară (LP) în editorul de foi de calcul Microsoft

MINISTERUL EDUCAȚIEI ȘI ȘTIINȚEI AL RUSIEI Bugetarul Federal de Stat Instituția de învățământ de învățământ profesional superior „UNIVERSITATEA TEHNICĂ DE STAT SAMARA” Departamentul „Tehnologia ingineriei mecanice”

Ministerul Educației și Științei al Federației Ruse Bugetul Federal de Stat Instituția de Învățământ de Învățământ Superior „UNIVERSITATEA TEHNICĂ DE STAT NIZHNY NOVGOROD IM. R.

Tver Abstract Service Cuprins Sarcina 1. Gama de produse... 3 Condițiile problemei... 3 Formularea matematică a problemei... 3 Modelul tabelar al problemei... 5 Raport privind rezultatele rezolvării problemei 1... 6 Concluzie ...

SARCINA LUCRĂRII PRACTICE 4 ȘI LUCRĂRII PRACTICE 5 Probleme de optimizare liniară Construcția modelelor economico-matematice (EMM). Rezolvarea problemelor de optimizare liniară folosind tehnologia informației.

LUCRĂRI DE LABORATOR CU MS EXCEL 2007 LUCRĂRI DE LABORATOR 1.... 1 LUCRĂRI DE LABORATOR 2... 3 LUCRĂRI DE LABORATOR 3... 4 LUCRĂRI DE LABORATOR 4... 7 LUCRĂRI DE LABORATOR 5... 8 LUCRĂRI DE LABORATOR 6... 10

Agenția Federală pentru Educație Instituție de învățământ de stat de învățământ profesional superior Universitatea Tehnică de Stat Ulyanovsk SISTEME DE INFORMAȚII ÎN ECONOMIE

1 Lucrări de laborator 3 Rezolvarea problemelor. Selectarea parametrilor, căutarea unei soluții 1. Implementarea unui model matematic în Excel Un model matematic este o descriere a stării de comportament a unui sistem real (obiect,

Gnumeric: foaie de calcul pentru toți I.A Khakhaev, 2007-2010 7 Optimizarea liniară (căutarea unei soluții) 7.1 Optimizarea ca problemă de programare liniară Să fie o funcție numită țintă, liniar.

AGENȚIA FEDERALĂ A TRANSPORTULUI FERROVIAR Instituție de învățământ de stat de învățământ profesional superior „UNIVERSITATEA DE STAT DE COMUNICAȚII din Moscova” Institutul de Economie

MINISTERUL ECONOMIEI AL Bugetului Federal de Stat RUS Instituția de Învățământ de Învățământ Superior „Universitatea Tehnică de Stat Samara” FACULTATEA DE INGINERIE ȘI ECONOMIE DEPARTAMENTUL DE ECONOMIE

LECȚIA SOLUȚIA APROXIMATĂ A ECUATIILOR NELINIARE Separarea rădăcinilor Fie dată ecuația f () 0, () unde funcția f () C[ a; Definiție Un număr se numește rădăcina unei ecuații () sau zero al unei funcții f () dacă

Ministerul Educației și Științei din Federația Rusă Agenția Federală pentru Educație Universitatea Tehnică de Stat Saratov SOLUȚIONAREA PROBLEMELOR DE OPTIMIZARE ÎN MEDIUL MS EXCEL Ghid

„Southwestern State University” SWSU) Departamentul de proiectare și tehnologie a instrumentelor electronice de calcul METODE DE OPTIMIZARE CONDIȚIONATĂ Orientări pentru efectuarea lucrărilor de laborator

MINISTERUL EDUCAȚIEI ȘI ȘTIINȚEI AL FEDERAȚIEI RUSĂ Bugetul de stat federal Instituție de învățământ de învățământ profesional superior „Universitatea de Stat din Pacific”

AGENȚIA FEDERALĂ DE TRANSPORT FERROVIAR INSTITUȚIA DE ÎNVĂȚĂMÂNT DE STAT FEDERALĂ DE ÎNVĂȚĂMÂNTUL PROFESIONAL SUPERIOR „UNIVERSITATEA DE STAT DE COMUNICAȚII DE LA MOSCOVA” (MIIT)

MINISTERUL EDUCAȚIEI ȘI ȘTIINȚEI AL RUSIEI Instituția de învățământ de la bugetul de stat federal de învățământ profesional superior Departamentul „Samara State Technical University” (FSBEI HPE „SamSTU”)

Ministerul Educației și Științei al Federației Ruse Bugetul federal de stat Instituția de învățământ de învățământ profesional superior Departamentul universitar de silvicultură de stat Ural

Lucrări de laborator 4 „Foile de calcul Excel și automatizarea calculelor pe un PC” SECȚIUNEA 4. Rezolvarea sistemelor de ecuații și a problemelor de optimizare. Capacitățile de calcul ale Excel sunt destul de largi,

Introducere Programarea liniară este o ramură a matematicii în care teoria și metodele numerice de rezolvare a problemelor de găsire a extremului (maxim sau minim) al unei funcții liniare a mai multor variabile în prezența

AGENȚIA FEDERALĂ DE TRANSPORT FERROVIAR BUGET FEDERAL DE STAT INSTITUȚIA DE ÎNVĂȚĂMÂNTUL SUPERIOR PROFESIONAL „UNIVERSITATEA DE STAT DE COMUNICĂȚI LA MOSCOVA”

ANALIZA DURABILITĂȚII ACTIVITĂȚII COMERCIALE A ÎNTREPRINDERII Conf. univ. dr. Degtyareva Nina Adamovna Munca comercială este activitatea unei întreprinderi care vizează rezolvarea unui set special de probleme. Studiu

LUCRĂRI DE LABORATOR 2 REZOLVAREA PROBLEMELOR DE PROGRAMARE LINEARĂ 1. Obiectivele lucrării: construirea unui model matematic al unei probleme de programare liniară; rezolvarea grafică a unei probleme de programare liniară

Să ne uităm la programarea liniară în Excel folosind exemplul unei probleme rezolvate anterior.

Sarcină. Nikolai Kuznetsov conduce o mică fabrică mecanică. Luna viitoare, intenționează să producă două produse (A și B), pentru care profitul marginal specific este estimat la 2.500 și, respectiv, 3.500 de ruble. Ambele produse necesită prelucrare, materii prime și costuri de muncă pentru a fi realizate. Fiecare unitate de produs A necesită 3 ore de prelucrare, 16 unități de materii prime și 6 unități de muncă pentru a produce. Cerințele de unitate corespunzătoare pentru Produsul B sunt 10, 4 și 6. Nicholas prezice că luna viitoare va putea furniza 330 de ore de prelucrare, 400 de unități de materii prime și 240 de unități de muncă. Tehnologia procesului de producție este de așa natură încât cel puțin 12 unități de produs B trebuie produse într-o anumită lună. Trebuie să se determine numărul de unități de produse A și B pe care Nikolay trebuie să le producă în următoarea lună pentru a maximiza marja de contribuție.

Descărcați nota în format, exemplu în format

1. Să folosim modelul matematic construit. Acesta este modelul:

Maximizați: Z = 2500 * x 1 + 3500 * x 2

Cu condiția ca: 3 * x 1 + 10 * x 2 ≤ 330

16 * x 1 + 4 * x 2 ≤ 400

6 * x 1 + 6 * x 2 ≤ 240

2. Să creăm un formular de ecran și să introducem datele inițiale în el (Fig. 1).

Orez. 1. Formular de ecran pentru introducerea datelor pentru o problemă de programare liniară

Acordați atenție formulei din celula C7. Aceasta este formula funcției obiective. În mod similar, formulele pentru calcularea părții stângi a restricțiilor sunt introduse în celulele C16:C18.

3. Verificați dacă aveți instalat suplimentul „Căutați o soluție” (Fig. 2), omiteți acest punct.

Orez. 2. Este instalat programul de completare Căutare soluție; Fila Date, grup Analiză

Dacă nu găsiți programul de completare „Solution Search” pe panglica Excel, faceți clic pe butonul Microsoft Office, apoi pe Opțiuni Excel (Fig. 3).

Orez. 3. Opțiuni Excel

Selectați linia Add-in-uri, apoi în partea de jos a ferestrei „Manage Microsoft Excel Add-ins”, selectați „Go” (Fig. 4).

Orez. 4. Suplimente Excel

În fereastra „Suplimente”, bifați caseta de selectare „Căutați o soluție” și faceți clic pe Ok (Fig. 5). (Dacă Solver nu este listat în câmpul Add-ons, faceți clic pe Răsfoire pentru a găsi programul de completare. Dacă primiți un mesaj că programul de completare Solver nu este instalat pe computer, faceți clic pe Da pentru a-l instala.)

Orez. 5. Activarea suplimentului „Căutare soluție”.

După încărcarea programului de completare pentru căutarea unei soluții, comanda Căutare soluție devine disponibilă în grupul Analiză din fila Date (Fig. 2).

4. Următorul pas este să completați fereastra Excel „Căutați o soluție” (Fig. 6)

Orez. 6. Completarea ferestrei „Căutați o soluție”.

În câmpul „Setare celulă țintă”, selectați celula cu valoarea funcției țintă – $C$7. Alegem dacă maximizăm sau minimizăm funcția obiectiv. În câmpul „Schimbarea celulelor”, selectați celulele cu valorile variabilelor dorite $C$4:$D$4 (atâta timp cât acestea conțin zerouri sau goale). În zona „Constrângeri”, folosind butonul „Adăugați”, plasăm toate restricțiile modelului nostru. Faceți clic pe „Run”. În fereastra „Solution Search Result” care apare, selectați toate cele trei tipuri de rapoarte (Fig. 7) și faceți clic pe Ok. Aceste rapoarte sunt necesare pentru a analiza soluția rezultată. Puteți citi mai multe despre datele prezentate în rapoarte.

Orez. 7. Selectarea tipurilor de rapoarte

Pe foaia principală au apărut valorile funcției obiectiv maximizate - 130.000 de ruble. și parametri variabili x 1 = 10 și x 2 = 30. Astfel, pentru a maximiza venitul marginal, Nicholas ar trebui să producă 10 unități de produs A și 30 de unități de produs B luna viitoare.

Dacă apare altceva în locul ferestrei „Rezultatul căutării soluției”, Excel nu a reușit să găsească o soluție. Verificați dacă fereastra „Căutați o soluție” este completată corect. Și încă un mic truc. Încercați să reduceți precizia căutării soluției. Pentru a face acest lucru, în fereastra „Căutare soluție”, faceți clic pe Parametri (Fig. 8.) și creșteți eroarea de calcul, de exemplu, la 0,001. Uneori, din cauza preciziei ridicate, Excel nu are timp să găsească o soluție în 100 de iterații. Puteți citi mai multe despre parametrii pentru găsirea unei soluții.

Orez. 8. Creșterea erorii de calcul

Un exemplu de rezolvare a unei probleme de programare liniară folosind MS excela

Ferma este specializată în agricultura de câmp pentru producția de cereale, sfeclă de zahăr și floarea soarelui. În agricultură Întreprinderea dispune de 3.200 de hectare de teren arabil, resurse de muncă în valoare de 7.000 de zile-om și îngrășăminte minerale în valoare de 15.000 c.d.w. Este necesar să se găsească o combinație de suprafață care să asigure un profit maxim.

De asemenea, trebuie avut în vedere faptul că

- suprafața însămânțată cu culturi industriale (sfeclă de zahăr și floarea soarelui) nu trebuie să depășească 25% din suprafața totală a terenului arabil;

- Ferma a încheiat un contract de vânzare de cereale în valoare de 65.000 c.

Pentru a dezvolta un model economic și matematic, este necesară pregătirea informațiilor de intrare (Tabelul 1).

tabelul 1

Indicatori	Culturi agricole
	cereale	sfeclă de zahăr	floarea soarelui
Productivitate, c/ha
Preț de vânzare de 1 cent de produse, rub./c.
Costul produselor comercializabile pe 1 ha, mie de ruble.	5,59	20,62	6,73
Costuri pe 1 ha: MDS, mii de ruble.		12,7
muncă, zile-om
îngrășăminte minerale, c.d.v.
Profitați de 1 ha, frecați.	2,89	7,93	3,63

Ca necunoscute vom lua suprafața sub culturi după tip:

X 1 - culturi de cereale

X 2 - sfecla de zahar

X 3 - floarea soarelui

Pentru a construi un model economic și matematic al problemei, este necesar să se țină cont de toate condițiile. În acest caz, conform acestor condiții, pot fi întocmite cinci restricții:

- suma suprafețelor însămânțate cu culturi agricole nu trebuie să depășească suprafața disponibilă în fermă (3200 hectare). Coeficienții pentru necunoscutele din această limitare caracterizează consumul de teren arabil la 1 hectar din fiecare cultură. În acest caz, coeficienții tehnici și economici pentru necunoscute vor fi egali cu unu. Suprafața totală a terenului arabil este înregistrată în partea dreaptă.

1) X1+X2+X3<=3200

- suma suprafețelor însămânțate cu culturi industriale nu trebuie să depășească suprafața care poate fi alocată în acest scop (3200*0,25=800 ha). Coeficienții pentru necunoscutele din această limitare caracterizează consumul de teren arabil alocat pentru însămânțarea culturilor industriale la 1 hectar din fiecare cultură agricolă industrială. În acest caz, coeficienții tehnici și economici pentru necunoscutele X2 și X3 vor fi egali cu unu, iar pentru culturile agricole netehnice (X3) - zero. În partea dreaptă este scrisă suprafața maximă de teren arabil care poate fi alocată pentru plantarea culturilor industriale.

2) X2+X3<=800

- a treia și a patra restricție asigură că utilizarea resurselor de muncă și a îngrășămintelor minerale nu depășește disponibilitatea acestora în fermă. Cu alte cuvinte, suma produselor din ratele consumului de resurse la 1 hectar pe suprafața însămânțată cu culturile agricole corespunzătoare nu trebuie să depășească volumul resurselor disponibile în agricultură. afacere. Coeficienții pentru necunoscutele în aceste constrângeri vor fi ratele consumului de resurse (în a treia constrângere - resursele de muncă, în a patra - îngrășăminte minerale) la 1 hectar de suprafață de cultură. În acest caz, coeficienții tehnico-economici sunt preluați din Tabelul 1. Disponibilitatea acestor resurse în fermă este înregistrată în partea dreaptă.

3) 1,5X1+4,5X2+1,5X3<=7000

4) 2Х1+15Х2+2,3Х3<=15000

- a cincea constrângere garantează producerea volumului planificat de cereale. Coeficienții pentru variabile sunt randamentul de cereale la 1 hectar de suprafață de cultură agricolă. culturi Când X1 este necunoscut, acesta este randamentul de cereale (Tabelul 1). Pentru variabilele X2 și X3, acest coeficient este zero. Planul de producție de cereale este scris în partea dreaptă.

5) 26Х1>=65000

Ca rezultat, se obține un sistem de cinci inegalități liniare cu trei necunoscute. Este necesar să se găsească astfel de valori nenegative ale acestor necunoscute X1>=0; X2>=0; X3>=0, care ar satisface acest sistem de inegalități și ar asigura un profit maxim al industriei de producție a culturilor în ansamblu:

Z max = 2,89Х1+7,93Х2+3,53Х3

Coeficienții pentru necunoscutele din funcția obiectiv sunt profitul primit din 1 hectar de suprafață cultivată. Acești coeficienți sunt calculați pe baza datelor din tabelul 1.

Deoarece această problemă este rezolvată folosind MS excela , atunci este recomandabil să pregătiți toate informațiile de intrare pentru construirea unui model economic și matematic folosind acest procesor de foi de calcul (Figura 1). Acest lucru facilitează nu numai calculele coeficienților tehnici și economici și a altor date, dar face și posibilă în viitor actualizarea automată a informațiilor în modelul economic și matematic.

Poza 1

Toate informațiile dezvoltate sunt rezumate într-un model economic și matematic detaliat și introduse în fișa de lucru MS Excela. (Fig. 2.)

Figura 2

Se recomandă introducerea datelor în model sub formă de legături către celule cu informații relevante în foile de calcul sau foile de lucru cu informații inițiale. Figura 3 arată cum într-o celulă F9 se ofera informatii cu privire la rata consumului de ingrasamant la 1 hectar de semanat de floarea soarelui.

Figura 3

La coloane A («№»), ÎN("Restricții"), CU(„Unități”) șiH(„Tipul constrângerii”), datele corespunzătoare sunt introduse direct în model (Fig. 1). Ele nu sunt utilizate în calcule și servesc în scopuri informaționale și pentru a facilita înțelegerea conținutului modelului. La coloană eu(„Domeniul de aplicare al restricțiilor”), linkurile sunt introduse către celulele care conțin informații corespunzătoare numelui coloanei (valorile părților din dreapta ale inegalităților construite mai devreme).

Pentru valorile dorite ale variabilelor X1, X2, X3 am lăsat celule goale – în consecință D5, E 5, F 5. Programul de celule inițial goale MS Excel percepe ca celule a căror valoare este zero. Coloană G, numit de noi " Suma produselor", are scopul de a determina suma produselor valorilor necunoscutelor necunoscute (celule D5, E 5, F 5) și coeficienți tehnici și economici conform restricțiilor corespunzătoare (rândurile 6-10) și funcției obiectiv (rândul 11). Astfel, în coloană G definit:

- - cantitatea de resurse utilizate (celula G6– suprafața totală de teren arabil; G7– teren arabil care poate fi folosit pentru însămânțarea culturilor industriale; G8– resurse de muncă; G9– îngrășăminte minerale);

- - cantitatea de cereale produsă (celulă G10);

- - valoarea profitului (celula G11).

Figura 2 arată cum într-o celulă G11 se implementează înregistrarea sumei produselor valorilor variabilelor (suprafețe însămânțate cu culturi agricole - celule D5, E 5, F 5) pentru profiturile corespunzătoare din 1 hectar din culturile lor (celule D11, E 11, F 11)folosind funcția MS excela « SUMPRODUS" Din moment ce la scrierea acestei formule, adresarea absolută la celulele din D5 inainte deF 5,această formulă poate fi copiată în alte celule dinG 6 inainte de G10.

Astfel, a fost construit un plan de referință (Fig. 2) și a fost obținută prima soluție fezabilă. Valorile necunoscutelor X1, X2, X3 sunt egale cu zero (celule D5, E 5, F 5 -celule goale), celule de coloană G„Suma de produse” din toate constrângerile (liniile 6-10) și linia țintă (linia 11) au, de asemenea, valori zero.

Interpretarea economică a primului plan de bază este următoarea: ferma dispune de resurse, au fost calculați toți coeficienții tehnici și economici, dar procesul de producție nu a început încă; resursele nu au fost folosite și, în consecință, nu a existat profit.

Pentru a optimiza planul existent, vom folosi instrumentul Găsirea unei soluții care se află în meniu Serviciu. Dacă nu există o astfel de comandă în meniu Serviciu, necesar la un moment dat Suprastructură bifeaza casuta Găsirea unei soluții. După aceasta, această procedură va deveni disponibilă în meniu Serviciu.

După selectarea acestei comenzi, va apărea o casetă de dialog (Fig. 4).

Figura 4

Din moment ce am ales ca criteriu de optimizare maximizarea profitului, în teren Setați celula țintă Introduceți un link către celula care conține formula de calcul al profitului. În cazul nostru, aceasta este celula 11 USD. Pentru a maximiza valoarea celulei finale prin modificarea valorilor celulelor de influență (celulele de influență, în acest caz acestea sunt celulele în schimbare, sunt celulele care sunt concepute pentru a stoca valorile necunoscutelor necunoscute), puneți comutatorul în poziție valoare maximă;

În câmp Schimbarea celulelor introduceți referințe la celulele de schimbat, separându-le cu virgule; sau, dacă celulele sunt adiacente, indicând prima și ultima celulă, separându-le cu două puncte ( $ D$5:$F$5).

În câmp Restricții introduceți toate restricțiile impuse în căutarea unei soluții. Să luăm în considerare adăugarea unei constrângeri folosind exemplul de adăugare a primei constrângeri pe suprafața totală a terenului arabil.

În capitolul Restricții căsuță de dialog Găsirea unei soluții faceți clic pe butonul Adăuga. Va apărea următoarea casetă de dialog (Fig. 5)

Figura 5

În câmp Referință de celulă Introduceți adresa celulei a cărei valoare este supusă restricțiilor. În cazul nostru, aceasta este celula $ G$6, unde este formula de calcul al terenului arabil utilizat în planul actual.

Selectați o declarație condiționată din lista derulantă <= , care ar trebui să fie situat între legătură și constrângere.

În câmp Prescripţie Introduceți un link către celula care conține valoarea disponibilității terenului arabil în fermă sau un link către această valoare. În cazul nostru, aceasta este celula $ eu 6 dolari

Ca rezultat, caseta de dialog va lua următoarea formă (Fig. 6).

Figura 6

Pentru a accepta restricția și a începe să introduceți una nouă, faceți clic pe butonul Adăuga. Alte restricții sunt introduse în mod similar. Pentru a reveni la caseta de dialog Găsirea unei soluții, apasa butonul Bine.

După ce ați urmat instrucțiunile de mai sus, caseta de dialogGăsirea unei soluțiiva avea următoarea formă (Fig. 7).

Figura 7

Pentru a modifica sau elimina restricțiile din listă Restricții căsuță de dialog Găsirea unei soluții specificați restricția pe care doriți să o modificați sau să o eliminați. Selectați o echipă Schimbareși faceți modificări sau faceți clic pe butonul Șterge.

Caseta de bifat Model liniarîn caseta de dialog Opțiuni Găsirea unei soluții(Fig. 8) vă permite să setați orice număr de restricții. Caseta de bifat Valori nenegative ne va permite să respectăm condiția de non-negativitate a variabilelor (la rezolvarea problemei noastre, aceasta este obligatorie). Puteți lăsa parametrii rămași neschimbați sau puteți seta parametrii de care aveți nevoie, folosind ajutorul dacă este necesar.

Figura 8

Pentru a începe sarcina de soluție, faceți clic pe butonul A executași faceți una dintre următoarele:

- pentru a restabili datele originale, selectați opțiunea Restabiliți valorile originale.

Figura 9

Pentru a întrerupe căutarea unei soluții, apăsați tasta ESC.

Foaia Microsoft Excel va fi recalculată ținând cont de valorile găsite ale celulelor de influență. Ca urmare a rezolvării și salvării rezultatelor căutării pe foaie, modelul va lua următoarea formă (Tabelul 10).

Figura 10

În celule D5-F5 se obțin valorile necunoscutelor necesare (suprafața de cultură este egală cu: cereale - 2500 ha, sfeclă de zahăr - 661 ha, floarea soarelui - 39 ha), în celule G6-G9 au fost determinate volumele de resurse utilizate (suprafața totală a terenului arabil - 3200 hectare; suprafața terenului arabil care poate fi folosit pentru semănat culturi industriale - 700 hectare; forță de muncă - 6781,9 om-zile; îngrășăminte minerale - 15000 c.d.v.) , în celulă G10 s-a stabilit cantitatea de cereale produsă (65.000 cenţi). Cu toate aceste valori, profitul ajunge la 12603,5 mii de ruble. (celula G11).

Dacă căutarea nu a găsit o soluție care să îndeplinească condițiile specificate, în caseta de dialog Rezultatele căutării soluției va apărea un mesaj corespunzător (Fig. 11).

Figura 11

Unul dintre cele mai frecvente motive ale imposibilității de a găsi o soluție optimă este situația în care, în urma rezolvării unei probleme, se dovedește că există restricții care nu sunt îndeplinite. După ce ați salvat soluția găsită pe foaie, trebuie să comparați valorile obținute ale coloanelor „Suma produselor” și „Volumul constrângerilor” rând cu linie și verificați dacă relația dintre ele satisface constrângerea din „Tipul de coloana Constrângeri”. După ce au constatat astfel restricții neîndeplinite, este necesar să se identifice și să se elimine motivele care fac imposibilă respectarea acestei condiții specifice (aceasta poate fi, de exemplu, volume planificate prea mari sau, dimpotrivă, foarte mici de restricții etc.).

Dacă există o mulțime de restricții în model, atunci din punct de vedere vizual este destul de dificil să compari și să verifici acuratețea fiecărei linii. Pentru a facilita acest lucru, se recomandă adăugarea unei alte coloane „Validare” la model, unde se utilizează funcții MS excela « DACĂ" Și " RUNDĂ» puteți organiza o verificare automată (Fig. 12).

Figura 12

Scopul lucrării: studierea software-ului modern pentru rezolvarea problemelor de programare liniară; rezolvarea practică a problemelor de programare liniară folosind metoda grafică, metoda simplex și instrumentele Microsoft Excel; implementarea software a metodei simplex într-un limbaj de programare de nivel înalt.

1. Partea teoretică

Există un add-in pentru rezolvarea problemelor de programare liniară în Microsoft Excel Găsirea unei soluții, accesat din meniu Serviciu.

Dacă echipa Găsirea unei soluții nu in meniu Serviciu, apoi trebuie să instalați suplimentul „Căutare soluție”. Pentru a face acest lucru în meniu Serviciu echipa este selectată Suplimente, care deschide caseta de dialog prezentată în Fig. 1.

Vom demonstra utilizarea suplimentului „Căutați o soluție” folosind exemplul de rezolvare a următoarei probleme.

Formularea problemei

Compania produce și vinde trei tipuri de produse: P 1 , R 2 și R 3. Trei tipuri de resurse sunt folosite pentru a produce produse - componente, materii prime și materiale. Rezervele de resurse și consumul acestora pentru producerea unei unități din fiecare tip de produs sunt date în tabel. 1.

tabelul 1

Profitul din vânzarea unei unități de produs de fiecare tip este de 240, 210 și 180 de unități monetare pt. P 1 , R 2 și R 3 respectiv.

Este necesar să se determine programul de producție al întreprinderii în așa fel încât profitul din vânzarea produselor să fie maximizat.

Modelul matematic al problemei

Să notăm prin variabile X 1 , X 2 și X 3 volume de producție necesare pentru tipuri de produse P 1 , R 2 și R 2, și după F– profitul întreprinderii. Apoi formularea matematică a problemei prezentate ia următoarea formă.

Determinați valorile variabilelor X 1 , X 2 și X 3 pentru care se realizează maximul funcţiei obiectiv

F = 240 X 1 + 210 X 2 + 180 X 3

cu restrictii:

Funcția obiectiv descrie profitul total din vânzarea produselor fabricate de toate cele trei tipuri. Constrângerile (1), (2) și (3) țin cont de consumul și stocurile de componente, materii prime și, respectiv, provizii. Deoarece volumele de producție nu pot fi negative, se adaugă condiții

X 1 ≥ 0; X 2 ≥ 0; X 3 ≥ 0.

Ordinea rezolvării optime a problemei

Prezentăm acțiunile aproximative necesare pentru a rezolva o problemă de programare liniară folosind Excel ca o secvență de pași.

Pasul 1. Datele sursă ale sarcinii sunt înregistrate pe o foaie de lucru. Una dintre opțiuni este prezentată în Fig. 2.

Cometariu. Dacă cunoașteți soluția de bază fezabilă inițială, atunci puteți accelera oarecum procesul de găsire a soluției optime. Pentru a face acest lucru, valorile inițiale ale unora sau ale tuturor variabilelor pot fi setate manual. În acest exemplu, celulele $B$2, $C$2 și $D$2 sunt folosite pentru a le stoca. Dacă nu este specificată o soluție de bază validă, Excel determină automat valorile inițiale ale variabilelor problemei.

Pasul 2. Introduceți formula în celula E3

SUMAPRODUS(B3:D3, $B$2:$D$2)

pentru a calcula valoarea curentă a funcției obiectiv, care găsește suma produselor perechi ale celulelor (B3:D3) cu coeficienți pentru variabile în expresia funcției obiectiv pe celule ($B$2:$D$2) cu curentul valorile variabilelor.

Pasul 3. Pentru a seta constrângerile problemei care se rezolvă, formula din celula E3 este copiată în celulele E5, E6 și E7. După aceasta, formulele prezentate în tabel trebuie obținute în celulele indicate. 2.

masa 2

	SUMPRODUS(B5:D5, $B$2:$D$2)
	SUMAPRODUS(B6:D6, $B$2:$D$2)
	SUMAPRODUS(B7:D7, $B$2:$D$2)

Pasul 4. După crearea unui tabel cu datele sursă, cursorul este plasat în celula E3, care conține formula de calcul al funcției obiectiv. Următorul în meniu Serviciu echipa este selectată Găsirea unei soluții, care deschide caseta de dialog prezentată în Fig. 3.

În câmp Setați celula țintă fereastra „Căutare soluție” prezentată în Fig. 3, ar trebui să apară adresa celulei cu formula funcției obiectiv (în acest exemplu este celula $E$3).

Apoi, în această fereastră (Fig. 3) sunt completate următoarele câmpuri ale acestei ferestre:

În câmp Egal comutatorul pentru tipul de extremum al funcției obiectiv este setat pe poziție valoare maximă (sau valoarea minima cu formularea adecvată a problemei);

În câmp Schimbarea celulelor indică intervalul de celule cu valorile variabilelor sarcinii, alocate pe foaia de calcul (în exemplu, acestea sunt celule $B$2:$D$2);

În câmp Restricții sunt specificate constrângerile problemei iniţiale. Pentru a face acest lucru, plasați cursorul în câmpul de introducere a restricțiilor și apăsați butonul Adăuga . Ca rezultat, apare caseta de dialog „Adăugați constrângere”, prezentată în Fig. 4.

În această fereastră din câmp Referință de celulă introduceți adresa celulei cu formula constrângerii corespunzătoare (de exemplu, pentru constrângerea (1) aceasta va fi celula E5), iar în câmp Prescripţie este indicată valoarea limită pe care o poate lua constrângerea selectată (în acest exemplu, partea dreaptă a constrângerii (1) se află în celula G5).

De remarcat faptul că completarea câmpurilor Referință de celulă Și Prescripţie în fereastra „Adăugați constrângere”, puteți face acest lucru selectând celulele corespunzătoare din foaia de calcul.

Apoi este selectat tipul de relație care conectează părțile din stânga și din dreapta ale constrângerii, așa cum se arată în Fig. 5.

După apăsarea butonului Adăuga în fereastra „Adăugați constrângere”. (sau butoane Bine Pentru introducând ultima constrângere), această constrângere este inclusă în lista de constrângeri a problemei care se rezolvă. Folosind butoane Șterge Și Schimbare Puteți șterge restricțiile evidențiate în listă sau le puteți face corecturi.

cometariu. În fereastra „Adăugați constrângere”, puteți specifica că toate sau unele variabile trebuie să ia numai valori întregi (Fig. 5). Acest lucru vă permite să obțineți soluții la problemele de programare liniară întregi (întregi sau parțial).

Pasul 5. După ce ați completat toate câmpurile ferestrei „Căutare soluție”, apăsați butonul Opțiuni (Fig. 3), care deschide caseta de dialog „Solution Search Options” prezentată în Fig. 6.

În această fereastră trebuie să bifați casetele Model liniar pentru a rezolva o problemă de programare liniară și Valori nenegative , dacă o astfel de condiţie este impusă tuturor variabilelor sarcinii.

Aici (Fig. 6) puteți determina și parametrii procesului de soluție: timpul maxim pentru găsirea unei soluții, numărul maxim de iterații, acuratețea etc. Caseta de bifat Arată rezultate iterații vă permite să urmăriți căutarea unei soluții pas cu pas. Caseta de bifat Scalare automată se aprinde atunci când răspândirea valorilor variabilelor este foarte mare.

Pasul 6. După ce ați specificat parametrii necesari în fereastra „Parametri de căutare a soluției”, faceți clic pe butonul A executa pentru a căuta o soluție la problemă (Fig. 3) în fereastra „Căutare soluție”. Dacă se găsește o soluție, pe ecran este afișată o fereastră cu un mesaj corespunzător (Fig. 7).

Rezultatele obținute sunt afișate pe o foaie de lucru, așa cum se arată în Fig. 8. În special, valorile variabilelor sunt în celulele $B$2:$D$2, valoarea funcției obiectiv este în celula E3.

Astfel, soluția optimă a problemei inițiale se obține sub forma unui vector
, Unde
,
Și
, pentru care valoarea funcției obiectiv F maxim și se ridică la F* = 129825.

Rezultatele rezolvării unei probleme de programare liniară pot fi, de asemenea, salvate ca foi de lucru separate numite Raport de rezultate, Raport de sustenabilitate Și Raport limită. Pentru a salva rezultatele sub formă de rapoarte, trebuie mai întâi să intrați în câmp Tip de raport selectați tipurile de rapoarte necesare (Fig. 7). În aceeași fereastră, puteți respinge soluțiile obținute și restabiliți valorile inițiale ale variabilelor.

Raport de rezultate pentru problema considerată este prezentată în Fig. 9.

Acest raport prezintă soluția optimă pentru o problemă de programare liniară și localizarea acesteia în regiunea soluțiilor fezabile. În grafice Rezultat sunt afișate valorile optime ale funcției obiectiv F* și variabilele sarcinii
, precum și valorile acestora pentru soluția de bază inițială de la care a început căutarea soluției optime (grafic Valoarea originală ). Starea restricțiilor (coloana stare ) caracterizează amplasarea punctului
în zona soluţiilor fezabile. Numara Diferență arată diferențele dintre valorile părților din stânga și din dreapta ale constrângerilor (reziduuri). Pentru constrângerea asociată, reziduul este zero, ceea ce indică locația punctului
la limita regiunii soluțiilor fezabile, care este specificată de această constrângere. Dacă constrângerea nu are legătură, atunci nu afectează soluția optimă.

cometariu. În interpretarea economică, constrângerile legate corespund resurselor limitate. Pentru constrângerile grafice care nu au legătură Diferență arată volumele rămase de resurse neutilizate nerare. În problema luată în considerare, constrângerile (1) și (3) corespund componentelor și materialelor care sunt resurse limitate. Constrângerea (2) nu are legătură, adică nu afectează planul optim de producţie după criteriul profitului maxim. Aceasta înseamnă că a doua resursă (materii prime) nu a fost folosită în valoare de 292,5 unități.

ÎN raport de sustenabilitate ( orez. 10 ) sunt date limitele de stabilitate ale variabilelor problemei (grafice Creștere permisă Și Reducere admisă coeficienții funcției obiectiv), precum și limitele de stabilitate ale prețurilor umbră (adică variabilele problemei duale), în cadrul cărora soluția optimă nu se modifică. Valorile mari ale limitelor (1E+30) înseamnă absența efectivă a limitelor corespunzătoare, adică. variabila se poate schimba la infinit.

În coloană Cost nivelat elementul din a doua linie (-150) arată cât de mult va scădea valoarea funcției dacă soluția conține o variabilă X 2 cresc cu unu. Pe de altă parte, cu o creștere acceptabilă a coeficientului funcției pentru o necunoscută X 2 cu 150 de unități valoarea acestei variabile nu se va modifica, adică. necunoscut X 2 va fi egal cu zero, iar dacă depășiți creșterea permisă (coeficientul la X 2 cresc cu mai mult de 150), apoi necunoscut X 2 în soluție va fi mai mare decât zero.

ÎN raport asupra limitelor(Fig. 11) arată limitele inferioare și superioare ale posibilelor modificări ale variabilelor (în intervalul de soluții fezabile) și valorile corespunzătoare ale funcției obiectiv (grafic Rezultatul țintă ) cu aceste modificări. În special, dacă X 1 = 0 și X 2 și X 3 rămân neschimbate, deci F= 2400 + 2100 + 180191,25 = 34425; la X 3 = 0 și neschimbat X 1 și X 2 primim F = 240397,5 + 2100 + 1800 = 95400.

Rezolvarea problemelor de programare liniară în MS Excel

Un instrument pentru rezolvarea problemelor de optimizare în MS Excel este programul de completare „Solution Search”. . Procedura de căutare a soluției vă permite să găsiți valoarea optimă a formulei conținute într-o celulă numită celula țintă. Această procedură funcționează pe un grup de celule care sunt direct sau indirect legate de o formulă din celula țintă. Pentru a obține un rezultat specificat din formula conținută în celula țintă, procedura modifică valorile din celulele de influență.

Dacă acest supliment este instalat, atunci „Căutați o soluție” este lansat din meniul „Instrumente”. Dacă nu există un astfel de articol, ar trebui să executați comanda „Tools - Add-ons...” și să bifați caseta de lângă suplimentul „Search for a solution”.

Soluția problemei de optimizare constă în trei etape.

A. Crearea unui model al problemei de optimizare.

B. Găsirea unei soluții la problema de optimizare.

C. Analiza soluţiei găsite la problema de optimizare.

Să aruncăm o privire mai atentă asupra acestor etape.

Etapa A.

În etapa de creare a modelului, sunt introduse denumirile necunoscutelor, intervalele de pe foaia de lucru sunt completate cu datele inițiale ale problemei și se introduce formula pentru funcția obiectiv.

Etapa B.

Comanda „Serviciu - Căutați o soluție” deschide caseta de dialog „Căutați o soluție”, care, la rândul său, conține următoarele câmpuri:

„Setare celulă țintă” - servește pentru a specifica celula țintă a cărei valoare ar trebui să fie maximizată, minimizată sau setată la un număr specificat. Această celulă trebuie să conțină o formulă.

„Egal” - servește la selectarea unei opțiuni pentru optimizarea valorii celulei țintă (maximizarea, minimizarea sau selectarea unui anumit număr). Pentru a seta un număr, introduceți-l în câmp.

„Schimbarea celulelor” - servește pentru a indica celulele ale căror valori se modifică în timpul căutării unei soluții până când sunt îndeplinite restricțiile impuse și condiția de optimizare a valorii celulei specificate în câmpul „Setare celulă țintă”.

„Presumați” - folosit pentru a găsi automat celule care afectează formula la care se face referire în câmpul „Setare celulă țintă”. Rezultatul căutării este afișat în câmpul „Modificarea celulelor”.

„Constrângeri” - servește la afișarea unei liste de condiții limită ale sarcinii.

„Adăugați” - servește la afișarea casetei de dialog „Adăugați constrângere”.

„Editare” - servește la afișarea casetei de dialog „Editare constrângere”.

„Șterge” – servește la eliminarea restricției specificate.

„Run” – servește pentru a începe căutarea unei soluții la sarcină.

„Închidere” - servește la ieșirea din fereastra de dialog fără a începe căutarea unei soluții la sarcină. În același timp, se salvează setările făcute în ferestrele de dialog care apar după ce ați apăsat pe butoanele „Opțiuni, Adăugare, Modificare sau Ștergere”.

„Parametri” - servește la afișarea casetei de dialog „Opțiuni de căutare a soluției”, în care puteți încărca sau salva modelul pentru a fi optimizat și specificați opțiunile de căutare a soluției disponibile.

„Restaurare” - servește pentru a șterge câmpurile ferestrei de dialog și a restabili valorile implicite ale parametrilor de căutare a soluției.

Pentru a rezolva o problemă de optimizare, urmați acești pași:

1. În meniul „Instrumente”, selectați comanda „Căutare soluție”.

2. În câmpul „Set target cell” introduceți adresa sau numele celulei care conține formula modelului de optimizat.

3. Pentru a maximiza valoarea celulei țintă prin modificarea valorilor celulelor de influență, setați comutatorul în poziția de valoare maximă.

Pentru a minimiza valoarea celulei țintă prin modificarea valorilor celulelor de influență, setați comutatorul în poziția corespunzătoare valorii minime.

Pentru a seta valoarea din celula țintă la un anumit număr prin modificarea valorilor celulelor care influențează, setați comutatorul la Valoare și introduceți numărul dorit în câmpul corespunzător.

4. În câmpul „Schimbarea celulelor”, introduceți numele sau adresele celulelor de schimbat, separându-le prin virgule. Celulele care sunt modificate trebuie să fie direct sau indirect legate de celula țintă. Pot fi instalate până la 200 de celule variabile.

Pentru a găsi automat toate celulele care afectează formula modelului, faceți clic pe butonul Ghici.

5. În câmpul „Constrângeri”, introduceți orice restricții care se aplică la căutarea unei soluții.

6. Faceţi clic pe butonul Run.

Pentru a restabili datele originale, setați comutatorul în poziția „Restabiliți valorile originale”.

Etapa C.

Pentru a afișa un mesaj final despre rezultatul soluției, utilizați caseta de dialog „Rezultatele căutării soluției”.

Caseta de dialog Rezultate căutare soluție conține următoarele câmpuri:

„Restaurați valorile originale” - servește la restabilirea valorilor originale ale celulelor modelului care influențează.

„Rapoarte” - servește la indicarea tipului de raport plasat pe o foaie separată a cărții.

„Rezultate” - folosit pentru a crea un raport format din celula țintă și o listă a celulelor modelului care influențează, valorile inițiale și finale ale acestora, precum și formule de constrângeri și informații suplimentare despre constrângerile impuse.

Robustness - Folosit pentru a genera un raport care să conțină informații despre sensibilitatea unei soluții la modificări mici ale formulei (câmpul Setați celula țintă, caseta de dialog Găsiți soluție) sau în formulele de constrângere.

„Constrângeri” - folosit pentru a crea un raport constând dintr-o celulă țintă și o listă de celule model care influențează, valorile acestora și limitele inferioare și superioare. Acest raport nu este generat pentru modelele ale căror valori sunt limitate la multe numere întregi. Limita inferioară este cea mai mică valoare pe care o poate conține celula de influență, în timp ce valorile celulelor de influență rămase sunt fixe și satisfac restricțiile impuse. Prin urmare, limita superioară este cea mai mare valoare.

„Salvare script” - servește la afișarea casetei de dialog Salvare script, în care puteți salva scriptul pentru rezolvarea problemei pentru a-l utiliza ulterior folosind MS Excel Script Manager.

Una dintre posibilele probleme și modele de optimizare liniară este problema planificării producției.

Întreprinderea trebuie să producă produse de următoarele tipuri: , Mai mult, cantitatea fiecărui produs fabricat nu trebuie să depășească cererea și, în același timp, să nu fie mai mică decât valorile planificate. Merge la fabricarea produselor m tipuri de materii prime , ale căror rezerve sunt limitate corespunzător de valori Se știe că pentru producție i-ro produsele merg in unitati j-materiile prime. Profitul primit din vânzarea produselor este egal în mod corespunzător . Este necesar să se planifice producția de produse în așa fel încât profitul să fie maximizat și, în același timp, planul de producție a fiecărui produs să fie îndeplinit, dar cererea pentru acesta să nu fie depășită.