Doel: problemen leren oplossen lineaire programmering in Excel met behulp van de Solution Search-invoegtoepassing.

Korte theoretische informatie

Optimalisatieproblemen worden veel gebruikt op verschillende gebieden van praktische activiteit: bij het organiseren van werk transportsystemen, bij het beheer van industriële ondernemingen, bij de voorbereiding van projecten voor complexe systemen. Veel voorkomende probleemklassen systeem analyse In het bijzonder passen de problemen van optimale planning, verdeling van verschillende hulpbronnen, voorraadbeheer, planning en interindustrieel evenwicht in het raamwerk van lineaire programmeermodellen.

Verklaring van het lineaire programmeerprobleem (LPP).

Er zijn veel variabelen X= (x 1, x 2,..., x n). De doelfunctie hangt lineair af van de gecontroleerde parameters:

Er zijn beperkingen die dat wel zijn lineaire vormen

Waar (2)

Het is vereist om het maximum (minimum) van een lineaire functie te bepalen

op voorwaarde dat het punt (x 1, x 2,..., x n) tot een verzameling D behoort, die wordt bepaald door het systeem lineaire ongelijkheden

(4)

Elke reeks waarden (x 1 *, x 2 *,..., x n *) die voldoet aan het systeem van ongelijkheden (4) van het lineaire programmeringsprobleem is een geldige oplossing voor dit probleem. Als de ongelijkheid blijft bestaan

c 1 x 1 o + c 2 x 2 o +..+ c n x n o ≥ c 1 x 1 + c 2 x 2 +..+ c n x n

voor de gehele reeks waarden x 1, x 2,..., x n, dan is de waarde x 1 o .. x n o de optimale oplossing voor het lineaire programmeerprobleem.

Een voorbeeld van het construeren van een wiskundig model en het oplossen van een ZLP.

Taak. Het is noodzakelijk om te bepalen in welke hoeveelheid het nodig is om producten van de vier typen A, B, C en D te produceren, waarvan de productie drie soorten hulpbronnen vereist: arbeid, grondstoffen en financiën. De hoeveelheid van elk type hulpbron die nodig is om een ​​productie-eenheid te produceren van dit type, wordt het verbruikspercentage genoemd. De consumptiecijfers, evenals de winst uit de verkoop van een eenheid van elk type product, worden weergegeven in tabel 1. Ook de beschikbaarheid van beschikbare middelen wordt daar weergegeven.

Tabel1.

Laten we een wiskundig model maken, waarvoor we de volgende notatie introduceren:

x ik - hoeveelheid producten van het i-de type, i = 1,2,3,4

b j – hoeveelheid beschikbare hulpbron van het j-de type, j = 1,2,3

een ji – consumptiesnelheid van de j-de hulpbron voor de productie van i-de producten

c ik – winst uit de verkoop van een eenheid product van het i-de type.

Zoals blijkt uit Tabel 1, voor een eenheid output A Er zijn 6 eenheden grondstoffen nodig, wat betekent dat alle producten moeten worden geproduceerd A 6 vereist x 1 eenheden grondstoffen, waar x 1- hoeveelheid geproduceerde producten A. Rekening houdend met het feit dat voor andere soorten producten de afhankelijkheden vergelijkbaar zijn, zal de beperking op grondstoffen er als volgt uitzien:

6x 1+ 5x 2+ 4x 3+ 3x 4≤ 110

In deze beperking is de linkerkant gelijk aan de hoeveelheid van de vereiste hulpbron, en toont de rechterkant de hoeveelheid van de beschikbare hulpbron.

Op dezelfde manier kunt u beperkingen maken voor andere typen bronnen en er afhankelijkheden voor schrijven objectieve functie. Het wiskundige model van het probleem ziet er dan als volgt uit:

x 1+ x 2+ x 3+ x 4≤ 16

6x 1+ 5x 2+ 4x 3+ 3x 4≤ 110

4x 1+ 6x 2+ 10x 3+ 13x 4≤ 100

x ik≥ 0, i=1,2,3,4

1. Om de voorwaarden van het probleem in te voeren, maken we een formulier in Excel (Fig. 1). Cellen B3:E3 geven de berekende waarden weer x ik.

Afb.1. Formulier voor het invoeren van probleemomstandigheden

2. Laten we de coëfficiënten van de objectieve functie en beperkingen in het formulier invoeren. Laten we afhankelijkheden uit het wiskundige model introduceren. De ingevoerde gegevens worden getoond in figuur 2.

Afb.2. Taakinvoergegevens

In cel F6 wordt de formule van de objectieve functie geschreven, in F9-F11 - de linkerdelen van de beperkingen uit het wiskundige model. In afb. Figuur 3 toont de formulepresentatiemodus. Ga naar dit regime kan worden gedaan met behulp van de volgende reeks acties: druk op de knop Microsoft Office , klik Excel-opties open het tabblad Aanvullend en vink het vakje aan Toon formules, niet hun waarden.

Afb.3. Presentatiemodus voor formules.

3. Download de add-on en zoek naar een oplossing Gegevens│Analyse│Een oplossing vinden.

4. In het veld Doelcel instellen Laten we een link naar de doelcel invoeren door de cursor in het veld te plaatsen en met de linkermuisknop op cel F6 te klikken.

5. Selecteer de zoekrichting door het vakje aan te vinken gelijk aan maximale waarde.

6. Plaats de cursor in het veld Cellen veranderen en gebruik de muis om de namen in te voeren van de cellen B3:E3 die u wilt wijzigen. In deze cellen wordt, als resultaat van het zoeken naar een oplossing, de oplossing weergegeven: de waarden van de variabelen x ik., waarbij de doelfunctie onder gegeven beperkingen een maximale waarde heeft.

7. Laten we beperkingen invoeren voor de vereiste variabelen: x ik ≥ 0 (standaard ondergrens is 0, uitvoerhoeveelheid kan niet negatief zijn). We zullen ook beperkingen op de hulpbronnen invoeren (Er kunnen niet meer hulpbronnen worden gebruikt dan hun reserves). Laten we op de knop klikken Toevoegen, in het venster dat verschijnt Een beperking toevoegen voer in het linkerveld met de muis een link in naar cel B3 en selecteer het teken in de vervolgkeuzelijst ≥, klik in het rechterveld op cel B4 (Fig. 4). Laten we de resterende beperkingen op dezelfde manier invoeren.

Afb.4. Venster voor het toevoegen van beperkingen.

Figuur 5 toont het voltooide venster Zoeken naar een oplossing.

Fig.5 Gevuld venster Zoeken naar een oplossing

8. Klik vervolgens op de knop Uitvoeren. Het dialoogvenster Zoekresultaten oplossing verschijnt (Fig. 6). De oplossing is gevonden. Aan alle beperkingen en optimaliteitsvoorwaarden is voldaan. Wij bewaren de gevonden oplossing. In dit venster kunt u ook drie soorten rapporten krijgen: over resultaten, stabiliteit en limieten worden rapporten gegenereerd in nieuwe werkbladen.

Afb.6. Venster met zoekresultaten voor oplossingen

De resultaten van de optimale oplossing voor het probleem worden weergegeven in de tabel (Fig. 7).

Afb.7. Optimale oplossingsresultaten

Zo bleek optimale oplossing(10;0;6;0), d.w.z. Het is raadzaam om 10 eenheden product A en 6 eenheden product C te produceren. De maximale winst is 1320 geldeenheden, terwijl alle arbeids- en financiële middelen worden gebruikt, 84 eenheden grondstoffen en 26 eenheden grondstoffen op voorraad blijven.

Laboratoriumwerkopdrachten.

Maak een wiskundig model en los het resulterende lineaire programmeerprobleem op in Excel met behulp van de Solution Search-invoegtoepassing.

Voor het vervoeren van goederen worden machines van het type A en B gebruikt. Het draagvermogen van beide typen machines is hetzelfde en gelijk aan h t. In één rit verbruikt machine A een 11 kg smeermiddelen en een 12 l brandstof, auto B - een 21 kg smeermiddelen en een 22 l brandstof. De basis heeft d1 kg smeermiddelen en d2 l brandstof. De winst uit het vervoeren van één auto A bedraagt van 1 rub., auto's B - vanaf 2 wrijven. Het is noodzakelijk om Ht vracht te vervoeren (de initiële gegevens staan ​​in de onderstaande tabel).

Hoeveel voertuigen van beide typen moeten worden gebruikt om de inkomsten uit vrachtvervoer te maximaliseren?

Instructies voor het uitvoeren van laboratoriumwerkzaamheden.

1. Theoretisch materiaal bestuderen.
2. Voer het gegeven voorbeeld uit.
3. Selecteer uw optie op basis van het laatste cijfer.
4. Maak een wiskundig model van het probleem.
5. Vind de optimale oplossing met Solution Finder.
6. Trek conclusies op basis van de verkregen oplossingen, genereer rapporten over de oplossingsresultaten, stabiliteit en limieten.
7. Maak een laboratoriumrapport.

1. Voorpagina.
2. Mondelinge verklaring van het probleem.
3. Wiskundige formulering van het probleem.
4. Gevuld venster Zoek naar een oplossing
5. Resultaten van het zoeken naar een oplossing (tabel).
6. Conclusies over de verkregen oplossingen.

Lijst met bronnen

1. Gelman V.Ya. Oplossing wiskundige problemen met behulp van Excel: Werkplaats. – Sint-Petersburg: Peter, 2003
2. Kuritsky B.Ya. Zoek naar optimale oplossingen met behulp van Excel. – Sint-Petersburg: BHV-St.-Petersburg, 1997
3. Pazyuk K.T. Wiskundige methoden en modellen in de economie. – Khabarovsk: KhSTU-uitgeverij, 2002
4. Johannes Walkenbach. MS OfficeExcel 2007 - Gebruikersbijbel, Uitgever: Williams, 2008

Het is noodzakelijk om te bepalen in welke hoeveelheid het nodig is om producten van de vier typen Prod1, Prod2, Prod3, Prod4 te produceren, waarvan de productie drie soorten hulpbronnen vereist: arbeid, grondstoffen en financiën. De hoeveelheid van elk type hulpbron die nodig is om een ​​producteenheid van een bepaald type te produceren, wordt het verbruikspercentage genoemd. De consumptiecijfers, evenals de winst die wordt ontvangen uit de verkoop van een eenheid van elk type product, worden weergegeven in Fig. 1.

Figuur 1.

Wiskundig model de taak heeft de vorm:

waarbij xj de hoeveelheid vervaardigde producten van het j-de type is; F – doelfunctie; de linkerzijden van de beperkingsuitdrukkingen geven de waarden aan benodigde hulpbron, en de rechterkant toont de hoeveelheid beschikbare hulpbron.

Taakvoorwaarden invoeren

Om het probleem mee op te lossen met behulp van Excel U moet een formulier maken om de initiële gegevens in te voeren en deze in te voeren. Het invoerformulier is te zien in figuur 2. 2.

In cel F6 wordt een uitdrukking voor de doelfunctie geïntroduceerd als de som van de producten van de winstwaarden uit de vrijgave van een producteenheid van elk type door het aantal producten van het overeenkomstige type. Voor de duidelijkheid: in afb. Figuur 3 toont het formulier voor het invoeren van initiële gegevens in de formule-uitvoermodus.

De linkerdelen van de beperkingen voor bronnen van elk type worden ingevoerd in de cellen F8:F10.

Figuur 2.

Figuur 3.

Een lineair programmeerprobleem oplossen

Gebruik om lineaire programmeerproblemen in Excel op te lossen krachtig hulpmiddel, genaamd Een oplossing vinden . Toegang tot het zoeken naar een oplossing vindt plaats via het menu Dienst verschijnt het dialoogvenster Zoeken naar een oplossing op het scherm (Fig. 4).

Figuur 4.

Het invoeren van de voorwaarden van een probleem om de oplossing ervan te vinden, bestaat uit de volgende stappen:

1 Wijs een doelfunctie toe door de cursor in het veld te plaatsen Doelcel instellen venster Zoek een oplossing en klik in cel F6 in het invoerformulier;

2 Zet de schakelaar aan voor de waarde van de objectieffunctie, d.w.z. geef het aan Gelijkwaardig Maximale waarde ;

3 Voer de adressen in van de variabelen die u wilt wijzigen (x j): plaats hiervoor de cursor in het veld Cellen veranderen venster Zoek naar een oplossing en selecteer vervolgens het celbereik B3:E3 in het invoerformulier;

4 Druk op de knop Toevoegen Zoekvensters voor oplossingen voor het invoeren van beperkingen voor een lineair programmeerprobleem; er verschijnt een venster op het scherm Een beperking toevoegen (Afb. 5) :

Voer hiervoor randvoorwaarden voor de variabelen x j (x j ³0) in het veld in Celreferentie geef cel B3 aan die overeenkomt met x 1, selecteer het gewenste teken (³) uit de lijst in het veld Beperking geef de cel van het invoerformulier aan waarin de overeenkomstige waarde van de randvoorwaarde is opgeslagen (cel B4), klik op de knop Toevoegen ; herhaal de beschreven stappen voor de variabelen x 2, x 3 en x 4;

Voer beperkingen in voor elk type resource in het veld Celreferentie ramen Een beperking toevoegen geef cel F9 van het invoerformulier aan, die de uitdrukking bevat van de linkerkant van de beperking die wordt opgelegd aan de arbeidsmiddelen in de velden Beperking geef het £-teken en het H9-adres aan de rechterkant van de beperking weer en druk op de knop Toevoegen ; op vergelijkbare wijze beperkingen invoeren voor andere soorten hulpbronnen;

Na binnenkomst laatste beperking in plaats van Toevoegen druk OK en keer terug naar het venster Zoeken naar een oplossing.

Figuur 5.

Het oplossen van een lineair programmeerprobleem begint met het instellen van de zoekparameters:

In het raam Een oplossing vinden druk op de knop Opties , verschijnt er een venster op het scherm Zoekopties voor oplossingen (Fig. 6);

Selectievakje Lineair model, die het gebruik van de simplexmethode garandeert;

Geef het maximale aantal iteraties op (de standaardwaarde is 100, wat geschikt is voor het oplossen van de meeste problemen);

Selectievakje , als u alle fasen van het zoeken naar de optimale oplossing moet doornemen;

Klik OK , terug naar venster Een oplossing vinden .

Figuur 6.

Om het probleem op te lossen, drukt u op de knop Uitvoeren in het raam Een oplossing vinden , er is een venster op het scherm Zoekresultaten voor oplossingen (Fig. 7), die het bericht bevat De oplossing is gevonden. Aan alle beperkingen en optimaliteitsvoorwaarden is voldaan. Als de omstandigheden van het probleem inconsistent zijn, wordt er een bericht weergegeven Zoeken kan geen passende oplossing vinden. Als de doelfunctie niet beperkt is, verschijnt het bericht Doelcelwaarden convergeren niet.

Figuur 7.

Voor het beschouwde voorbeeld is een oplossing gevonden en wordt het resultaat van de optimale oplossing voor het probleem weergegeven in de vorm van invoer: de waarde van de corresponderende doelfunctie maximale winst en gelijk aan 1320, aangegeven in cel F6 van het invoerformulier, het optimale productieplan x 1 =10, x 2 =0, x 3 =6, x 4 =0 wordt aangegeven in de cellen B3:C3 van het invoerformulier (Fig. 8).

De hoeveelheid grondstoffen die wordt gebruikt om producten te produceren wordt weergegeven in de cellen F9:F11: arbeid - 16, grondstoffen - 84, financiën - 100.

Figuur 8.

Als, bij het instellen van parameters in het venster Zoekopties voor oplossingen (Fig. 6) Het selectievakje is aangevinkt Toon iteratieresultaten , dan worden alle zoekstappen opeenvolgend weergegeven. Er verschijnt een venster op het scherm (Afb. 9). In dit geval worden de huidige waarden van de variabelen en de doelfuncties weergegeven in het invoerformulier. Aldus de resultaten van de eerste iteratie van het zoeken naar een oplossing origineel probleem weergegeven in het invoerformulier in Figuur 10.

Figuur 9.

Figuur 10.

Om verder te zoeken naar een oplossing, klikt u op de knop Doorgaan in het raam Huidige status op zoek naar een oplossing .

Analyse van de optimale oplossing

Voordat we verder gaan met het analyseren van de oplossingsresultaten, moeten we eerst het oorspronkelijke probleem in de vorm presenteren

door extra variabelen voor i te introduceren, die de waarden van ongebruikte bronnen vertegenwoordigen.

Laten we een duaal probleem creëren voor het oorspronkelijke probleem en extra duale variabelen introduceren vi.

Analyse van de resultaten van de zoektocht naar een oplossing zal ons in staat stellen deze te koppelen aan de variabelen van de initiële en dubbele problemen.

Een raam gebruiken Zoekresultaten voor oplossingen U kunt drie soorten rapporten oproepen waarmee u de gevonden optimale oplossing kunt analyseren:

Resultaten,

Duurzaamheid,

Grenzen.

Een rapport in een veld oproepen Rapporttype titel benadrukken het juiste type en druk op OK .

1 Resultatenrapport(Fig. 11) bestaat uit drie tabellen:

Tabel 1 bevat informatie over de doelfunctie; in kolom Oorspronkelijk de waarde van de doelfunctie wordt aangegeven voordat de berekeningen beginnen;

Tabel 2 bevat de waarden van de vereiste variabelen x j verkregen als resultaat van het oplossen van het probleem (optimaal productieplan);

Tabel 3 toont de resultaten van de optimale oplossing voor de beperkingen en voor de randvoorwaarden.

Voor Beperkingen in de kolom Formule de afhankelijkheden die zijn ingevoerd bij het instellen van beperkingen in het venster worden weergegeven Een oplossing vinden ; in de kolom Betekenis de waarden van de gebruikte bron worden aangegeven; in de kolom Verschil toont de hoeveelheid ongebruikte bronnen. Als de bron volledig wordt gebruikt, dan in de kolom Staat bericht wordt weergegeven verwant ; als de bron niet volledig wordt gebruikt, geeft deze kolom aan niet verbonden. Voor Randvoorwaarden Er worden vergelijkbare waarden gegeven met als enige verschil dat in plaats van een ongebruikte hulpbron het verschil wordt getoond tussen de waarde van de variabele x j in de gevonden optimale oplossing en de daarvoor gespecificeerde randvoorwaarde (x j ³0).

Het staat in de kolom Verschil je kunt de waarden van aanvullende variabelen y i van het oorspronkelijke probleem zien in formulering (2). Hier is y 1 =y 3 =0, d.w.z. de hoeveelheid ongebruikte arbeid en financiële middelen is nul. Deze middelen worden volledig benut. Tegelijkertijd is de hoeveelheid ongebruikte grondstoffen voor grondstoffen y 2 = 26, wat betekent dat er een overschot aan grondstoffen is.

Figuur 11.

2 Duurzaamheidsrapport(Fig. 12)bestaat uit twee tabellen.

Tabel 1 laat zien volgende waarden:

Het resultaat van het oplossen van het probleem (optimaal releaseplan);

- Normir. prijs, d.w.z. waarden die laten zien hoeveel de objectieve functie zal veranderen wanneer een productie-eenheid van het overeenkomstige type gedwongen wordt opgenomen in het optimale plan;

Objectieve functiecoëfficiënten;

Grenswaarden voor de toename van coëfficiënten van de doelfunctie waarbij het optimale productieplan wordt gehandhaafd.

Tabel 2 bevat vergelijkbare gegevens voor beperkingen:

Hoeveelheid gebruikte middelen;

- Schaduwprijs, die laat zien hoe de doelfunctie verandert wanneer de waarde van de overeenkomstige hulpbron met één verandert;

Geldige waarden toename van de hulpbronnen waarbij het optimale productieplan wordt gehandhaafd.

Figuur 12.

Het duurzaamheidsrapport maakt dubbele beoordelingen mogelijk.

Zoals bekend laten dubbele variabelen z i zien hoe de doelfunctie verandert wanneer de hulpbron van het i-de type met één verandert. In een Excel-rapport wordt de dubbele schatting aangeroepen Schaduwprijs.

In ons voorbeeld wordt de grondstof niet volledig benut en is de hulpbron y 2 = 26. Uiteraard zal een toename van de hoeveelheid grondstoffen, bijvoorbeeld naar 111, geen toename van de doelfunctie met zich meebrengen. Daarom geldt voor de tweede beperking de dubbele variabele z 2 =0. Dus als volgens deze hulpbron er is dus sprake van een reserve extra variabele groter zal zijn dan nul, en dubbele beoordeling van deze beperking is nul.

In het beschouwde voorbeeld werden de arbeidsmiddelen en financiën volledig benut, dus hun aanvullende variabelen zijn gelijk aan nul (y 1 =y 3 =0). Als een hulpbron volledig wordt gebruikt, zal de toename of afname ervan het volume van de output beïnvloeden, en dus de waarde van de objectieve functie. Dubbele schattingen van beperkingen op arbeid en financiële middelen zijn verschillend van nul, d.w.z. z1=20, z3=10.

De waarden van de dubbele schattingen zijn te vinden in Duurzaamheidsrapport, in tabel 2, in de kolom Schaduwprijs.

Bij een toename (afname) van de arbeidsmiddelen met één eenheid, zal de doelfunctie met 20 eenheden toenemen (afnemen) en gelijk zijn aan

F=1320+20×1=1340 (met vergroting).

Op dezelfde manier zal, wanneer het financiële volume met één eenheid toeneemt, de objectieve functie zijn

F=1320+10×1=1330.

Hier, in de grafieken Toegestane verhoging En Toegestane reductie Tabel 2 toont de toegestane limieten voor het wijzigen van de hoeveelheid hulpbronnen van het j-de type. Wanneer de toename van de waarde van arbeidsmiddelen bijvoorbeeld verandert van –6 naar 3,55, zoals weergegeven in de tabel, blijft de structuur van de optimale oplossing behouden, d.w.z. de grootste winst wordt behaald door de output van Prod1 en Prod3, maar in verschillende hoeveelheden.

Extra dubbele variabelen worden ook weerspiegeld in Duurzaamheidsrapport in de kolom Normir. prijs tafel 1.

Als de belangrijkste variabelen niet zijn opgenomen in de optimale oplossing, d.w.z. gelijk zijn aan nul (in het voorbeeld x 2 =x 4 =0), dan hebben de bijbehorende aanvullende variabelen positieve waarden (v 2 =10, v 4 =20). Als de hoofdvariabelen zijn opgenomen in de optimale oplossing (x 1 =10, x 3 =6), dan zijn hun aanvullende dubbele variabelen gelijk aan nul (v 1 =0, v 3 =0).

Deze waarden laten zien hoeveel de doelfunctie zal afnemen (dus het minteken in de waarden van de variabelen v 2 en v 4) bij de gedwongen vrijgave van een eenheid van dit product. Als we daarom met kracht een producteenheid van het type Prod3 willen vrijgeven, zal de doelfunctie met 10 eenheden afnemen en gelijk zijn aan 1320 -10×1 =1310.

Laten we de verandering in de coëfficiënten van de objectieve functie in het oorspronkelijke model (1) aangeven met Dcj. Deze coëfficiënten bepalen de winst die wordt ontvangen uit de verkoop van een eenheid product van het j-de type.

In grafieken Toegestane verhoging En Toegestane reductie tafel 1 Duurzaamheidsrapport de grenzen van verandering in Dсj worden getoond waarbij de structuur van het optimale plan behouden blijft, d.w.z. Het zal winstgevend zijn om producten van het type Prodj te blijven produceren. Als Dc 1 bijvoorbeeld binnen het bereik van -12 £ Dc 1 £ 40 verandert, zoals weergegeven in het rapport, zal het nog steeds winstgevend zijn om producten van het type Prod1 te produceren. In dit geval zal de waarde van de doelfunctie F=1320+x 1 ×Dс j =1320+10×Dс j zijn.

3 Limietrapport getoond in afb. 13. Het laat zien binnen welke grenzen de waarden xj in de optimale oplossing kunnen veranderen met behoud van de structuur van de optimale oplossing. Bovendien worden voor elk type product de waarden van de objectieve functie gegeven, verkregen door in de optimale oplossing de waarde van de ondergrens van de productie van producten van het overeenkomstige type te vervangen door constante waarden van de output van andere typen. Als we bijvoorbeeld voor de optimale oplossing x 1 =10, x 2 =0, x 3 =6, x 4 =0 x 1 =0 (ondergrens) plaatsen met x 2, x 3 en x 4 ongewijzigd, dan waarde van de doelfunctie zal gelijk zijn aan 60×0+70×0+120×6+130×0=720.

Een add-in is een hulpmiddel voor het oplossen van optimalisatieproblemen in MS Excel Een oplossing vinden. Dankzij de zoekprocedure voor oplossingen kunnen we vinden optimale waarde formule in een cel die de doelcel wordt genoemd. Deze procedure werkt op een groep cellen die direct of indirect gerelateerd zijn aan een formule in de doelcel. Om een ​​gespecificeerd resultaat te verkrijgen uit de formule in de doelcel, verandert de procedure de waarden in de beïnvloedende cellen.

Als deze toevoeging geïnstalleerd, dan Een oplossing vinden gelanceerd vanuit het menu Dienst. Als een dergelijk item niet bestaat, moet u de opdracht uitvoeren Dienst → Add-ons... en vink het vakje aan voor de invoegtoepassing
Een oplossing vinden(Afb. 2.1).

In het raam Een oplossing vinden beschikbaar volgende velden:

Doelcel instellen– dient om de doelcel te specificeren waarvan de waarde moet worden gemaximaliseerd, geminimaliseerd of op een bepaald aantal moet worden ingesteld. Deze cel moet een formule bevatten.

Gelijkwaardig– dient om een ​​optie te selecteren voor het optimaliseren van de waarde van de doelcel (maximalisatie, minimalisatie of selectie gegeven nummer). Om een ​​getal in te stellen, voert u dit in het veld in.

Cellen veranderen– dient om cellen aan te geven waarvan de waarden veranderen tijdens het zoeken naar een oplossing totdat aan de opgelegde beperkingen en de voorwaarde voor het optimaliseren van de waarde van de cel die is opgegeven in het veld Doelcel instellen is voldaan.

Gok– gebruikt voor automatisch zoeken cellen die van invloed zijn op de formule waarnaar wordt verwezen in het vak Doelcel instellen. Het zoekresultaat wordt weergegeven in het veld Cellen bewerken.

Beperkingen– dient om een ​​lijst met randvoorwaarden van de taak weer te geven.

Toevoegen- dient om het dialoogvenster Beperking toevoegen weer te geven.

Wijziging- Geeft het dialoogvenster Limiet bewerken weer.

Verwijderen- Dient om de opgegeven beperking te verwijderen.

Uitvoeren– Dient om een ​​zoektocht naar een oplossing voor een bepaald probleem op gang te brengen.

Dichtbij– Dient om het dialoogvenster te verlaten zonder te zoeken naar een oplossing voor de taak.

Zoekopties voor oplossingen, waarin u het te optimaliseren model kunt laden of opslaan en de geboden mogelijkheden voor het vinden van een oplossing kunt aangeven.

Herstellen– Dient om de dialoogvenstervelden te wissen en de standaardwaarden van de zoekparameters voor oplossingen te herstellen.

Volg deze stappen om een ​​optimalisatieprobleem op te lossen:

1. In het menu Dienst team selecteren Een oplossing vinden.

2. In het veld Doelcel instellen Voer het adres of de naam in van de cel die de formule bevat van het model dat moet worden geoptimaliseerd.

3. Om de waarde van de doelcel te maximaliseren door de waarden van de beïnvloedende cellen te wijzigen, zet u de schakelaar op maximale waarde.

Om de waarde van een doelcel te minimaliseren door de waarden van beïnvloedende cellen te wijzigen, zet u de schakelaar op
minimale waarde.

Om de waarde in een doelcel in te stellen op een getal door de waarden van de beïnvloedende cellen te wijzigen, zet u de schakelaar op betekenis en voer het vereiste nummer in het daarvoor bestemde veld in.

4. In het veld Cellen veranderen Voer de namen of adressen in van de cellen die u wilt wijzigen, gescheiden door komma's. Veranderlijke cellen moet direct of indirect gerelateerd zijn aan de doelcel. Er kunnen maximaal 200 variabele cellen worden geïnstalleerd.

Als u automatisch alle cellen wilt vinden die van invloed zijn op de modelformule, klikt u op Aannemen.

5. In het veld Beperkingen voer alle beperkingen in die zijn opgelegd aan het zoeken naar een oplossing.

6. Klik op de knop Uitvoeren.

Om de originele gegevens te herstellen, zet u de schakelaar op

Fase C. Analyse van de gevonden oplossing voor het optimalisatieprobleem.

Om een ​​definitief bericht over het resultaat van de beslissing weer te geven, wordt een dialoogvenster gebruikt Resultaten van de zoektocht naar een oplossing.

Dialoogvenster Zoekresultaten oplossing bevat de volgende velden:

Herstel originele waarden– dient voor restauratie initiële waarden beïnvloedende cellen van het model.

Rapporten– dient om aan te geven op welk type rapport gepost wordt apart blad boeken.

Resultaten. Wordt gebruikt om een ​​rapport te maken dat bestaat uit een doelcel en een lijst van beïnvloedende modelcellen, hun begin- en eindwaarden, en beperkingsformules en aanvullende informatie over de opgelegde beperkingen.

Duurzaamheid. Wordt gebruikt om een ​​rapport te maken met informatie over de gevoeligheid van de oplossing voor kleine veranderingen in de formule (veld Doelcel instellen, dialoogvenster Zoek naar een oplossing) of in beperkingsformules.

Beperkingen. Wordt gebruikt om een ​​rapport te maken dat bestaat uit een doelcel en een lijst met beïnvloedende modelcellen, hun waarden en onder- en bovengrenzen. Dit rapport wordt niet gegenereerd voor modellen waarvan de waarden beperkt zijn tot veel gehele getallen. De ondergrens is de kleinste waarde die de beïnvloedende cel kan bevatten, terwijl de waarden van de overige beïnvloedende cellen vast staan ​​en voldoen aan de opgelegde beperkingen. Dienovereenkomstig is de bovengrens de grootste waarde.

Script opslaan– dient om een ​​dialoogvenster weer te geven Het script opslaan waarin u een script kunt opslaan voor het oplossen van een probleem, zodat u dit later kunt gebruiken met behulp van de MS Excel-scriptmanager. In de volgende secties zullen we er verschillende bekijken specifieke modellen lineaire optimalisatie en voorbeelden van hun oplossingen met behulp van MS Excel.

2.4 Probleem met productieplanning

Verklaring van het probleem. Het bedrijf moet producten produceren N soorten: en 1 ,en 2 ,...en p en de hoeveelheid van elk geproduceerd product mag de vraag niet overschrijden β 1, β 2,..., β n en mag tegelijkertijd niet minder zijn dan de geplande waarden b 1 ,b 2 ,...,b n respectievelijk. Het wordt gebruikt voor de vervaardiging van producten M soorten grondstoffen s l ,s 2 ,...,s m, waarvan de reserves respectievelijk worden beperkt door de waarden van γ 1 , γ 2 ,..., γ M. Dat is bekend voor de productie i-de product komt eraan en IJ eenheden J-de grondstoffen. Winst verkregen uit de verkoop van producten jij 1, ,en 2,...en p dienovereenkomstig gelijk vanaf 1, vanaf 2,..., vanaf p. Het is vereist om de productie van producten zo te plannen dat de winst wordt gemaximaliseerd en tegelijkertijd het plan voor de productie van elk product wordt vervuld, maar de vraag ernaar niet wordt overschreden.

Wiskundig model. Laten we aanduiden met x 1, x 2,...x n aantal eenheden producten jij 1, ,en 2,...en p, geproduceerd door de onderneming. De winst die het plan oplevert (doelfunctie) is gelijk aan:

z = z(x 1 ,x 2 ,...,x n) = c 1 x 1 + c 2 x 2 + ...+c n x n → maximaal Beperkingen op de uitvoering van het plan zullen worden geschreven in de vorm: x ik ≥β ik for i = 1,2,...,n Om de vraag niet te overschrijden, is het noodzakelijk om de productie van producten te beperken: x ik ≤β ik Voor i= 1,2,...n. En ten slotte zullen de beperkingen op grondstoffen worden geschreven in de vorm van een systeem van ongelijkheid:

α 11 x 1 + α 12 x 2 +...+ α 1n x n ≤b 1

α 21 x 1 + α 22 x 2 +...+ α 2n x n ≤b 2

................................................

α m1 x 1 + α m2 x 2 +...+ α mn x n ≤b m

mits x 1, x 2,...x n niet-negatief.

Voorbeeld 2.1:

Laten we eens overwegen concreet voorbeeld problemen over productieplanning en geef de volgorde van de acties die nodig zijn om het probleem op te lossen met behulp van MS Excel.

Probleemtoestand. Het bedrijf produceert twee soorten producten van gewapend beton: trappen en balkonplaten. Voor de productie van één trap is 3,5 kubieke meter nodig. beton en 1 pakket wapening, en voor de productie van platen - 1 kubieke meter. beton en 2 pakketten wapening. Voor elke productie-eenheid is 1 mandag arbeid nodig. De winst uit de verkoop van 1 trap is 200 roebel en één plaat is 100 roebel. De onderneming heeft 150 mensen in dienst en het is bekend dat de onderneming niet meer dan 350 kubieke meter per dag produceert. beton en er worden niet meer dan 240 pakketten wapening geïmporteerd. Er moet een productieplan worden opgesteld, zodat de winst uit de geproduceerde producten maximaal is.

Oplossing.

1. Op een blad werkboek MS Excel vult de tabel met taakparameters in (Fig. 2.2).

2. Maak een probleemmodel en vul de cellen in voor de variabelewaarden (aanvankelijk de cellen x (en x z gevuld met willekeur numerieke waarden, bijvoorbeeld de waarde 10), objectieve functie (cel bevat formule) en beperkingen (cellen bevatten formules)
(Afb. 2.2)

3. Voer de opdracht uit Dienst Zoek naar een oplossing en stel de vereiste waarden in de velden van het dialoogvenster in Een oplossing vinden beperkingen aan het venster toevoegen Beperkingen toevoegen.

Opmerking. In het raam Beperkingen toevoegen indien nodig is het mogelijk om beperkingen te stellen aan de integriteit van modelvariabelen.

4. Klik op de knop Uitvoeren en stel de parameters in het venster in Zoekresultaten voor oplossingen(schakelaar Bewaar de gevonden oplossing of Herstel originele waarden En Rapporttype).

Opmerking: Als er fouten in formules, beperkingen of onjuiste modelparameters voorkomen, kunnen de volgende berichten in dit venster verschijnen: 'Doelcelwaarden convergeren niet', 'Zoeken kan geen oplossing vinden' of 'Er is niet voldaan aan de lineaire modelvoorwaarden .” In dit geval moet de schakelaar in de stand worden gezet Herstel originele waarden, controleer de gegevens op het blad en herhaal de procedure om een ​​oplossing te vinden.

5. Als gevolg hiervan, in cellen met taakvariabelen waarden die overeenkomen met het optimale plan verschijnen (80 trappen en 70 vloerplaten per dag), en in de cel voor de doelfunctie - de overeenkomstige winstwaarde (23.000 roebel) dit plan(Afb. 2.3)

6. Als de verkregen oplossing bevredigend is, kunt u het optimale plan opslaan en de zoekresultaten bekijken, die op een apart blad worden weergegeven.

Oefening:

Ex. 2.1. Het bedrijf produceert televisies, stereosystemen en luidsprekersystemen gebruikmakend van een gemeenschappelijk magazijn met componenten. Chassisvoorraden in het magazijn zijn 450 stuks, beeldbuizen - 250 stuks, luidsprekers - 800 stuks, voedingen - 450 stuks, borden - 600 stuks. Voor elk product is het aantal componenten vereist dat in de tabel wordt aangegeven:

De winst uit de productie van één tv bedraagt ​​90 USD, één stereosysteem – 50 en een audiosysteem – 45. Het is noodzakelijk om optimale verhouding productvolumes waarbij de winst uit de productie van alle producten maximaal zal zijn.

Om lineaire programmeerproblemen op te lossen simplex methode in de MS Excel-omgeving worden cellen gevuld met brongegevens in getalmodus en wiskundige modelformules.

Met MS Excel kunt u een optimale oplossing verkrijgen zonder de dimensie van het systeem van ongelijkheden van de objectieve functie te beperken.

Laten we het probleem van gefabriceerde producten oplossen met behulp van de simplex-methode met behulp van de invoegtoepassing "Solution Search" in MS Excel.

1. Vul in Excel-spreadsheet in cijfermodus (Fig. 1)

2. Vul de Excel-tabel in de formulemodus in (Fig. 2)

Fig.1 Tabel in nummermodus

Fig.1 Tabel in formulemodus

Hier: B9:C9 – resultaat ( optimale hoeveelheid producten van elk type);

В6:С6 – coëfficiënten van de objectieve functie;

B10 – waarde van de objectieve functie;

В3:С5 – beperkingscoëfficiënten;

D12:D14 – rechterkant van beperkingen;

B12:B14 – berekende (werkelijke) waarden van de linkerkant van de beperkingen.

Laten we het probleem oplossen met de opdracht Data/Solution Search. Het dialoogvenster Zoeken naar oplossing verschijnt op het scherm.

In het veld Doelfunctie instellen een link naar actieve cel, d.w.z. op B10. Bovendien is deze link absoluut. Zet in de sectie Gelijke de schakelaar op Maximale (minimale) waarde, afhankelijk van de doelfunctie. Beperkingen worden ingesteld met de knop Toevoegen, waarmee het invoerdialoogvenster Beperking toevoegen wordt geopend.

Geef in het invoerveld Cellink: het adres op van de cel met de formule aan de linkerkant van de beperking. Vervolgens wordt het teken van de verhouding uit de lijst geselecteerd. Het veld Beperking specificeert het adres van de cel die dit bevat rechterkant beperkingen. Klik op de knop Toevoegen en herhaal dit tot de volgende beperking. Nadat u alle beperkingen heeft ingevoerd, klikt u op OK.

Omdat alle variabelen niet-negativiteitscondities met zich meebrengen, wordt hun positiviteit ingesteld via de knop Parameters in het dialoogvenster Zoeken naar een oplossing. Nadat u erop hebt geklikt, verschijnt het venster Oplossingszoekopties op het scherm.

Schakel het selectievakje Maak onbeperkte variabelen niet-negatief in en selecteer Oplossingsmethode Zoeken naar oplossingen voor lineaire problemen met behulp van de simplexmethode. Klik op de knop Oplossing zoeken.

Excel zal een venster met zoekresultaten voor oplossingen weergeven met een bericht dat er een oplossing is gevonden of dat er geen geschikte oplossing kan worden gevonden.

Als de berekening succesvol was, zal Excel het volgende overzichtsvenster weergeven. Je kunt ze houden of weggooien. Bovendien kunt u er één krijgen drie soorten rapporten (Resultaten , Duurzaamheid , Limieten) die ons in staat stellen de verkregen resultaten beter te begrijpen, inclusief het beoordelen van hun betrouwbaarheid.

Nadat de oplossing is gevonden, verschijnt het optimale aantal producten van elk type in cellen B9:C9.

Wanneer u het rapport opslaat, selecteert u – Rapporteren over resultaten (Fig. 3).

Uit het rapport blijkt dat hulpbron 1 met 150 kg niet volledig wordt benut, terwijl hulpbron 2 en 3 wel volledig worden benut.

Hierdoor is een optimaal plan verkregen waarbij producten van type 1 in een hoeveelheid van 58 stuks geproduceerd moeten worden, en producten van type 2 in een hoeveelheid van 42 stuks. Tegelijkertijd is de winst uit hun verkoop maximaal en bedraagt ​​deze 4.660 duizend roebel.

Afb.3 Resultatenrapport

1. Passagiers- en snelle treinen bestaande uit gereserveerde zitplaatsen, coupés en zachte rijtuigen vertrekken dagelijks vanaf het formatiestation. Het aantal zitplaatsen in een gereserveerde zitrijtuig is 54, in een coupérijtuig – 36, in een zachte rijtuig – 18. De tabel toont de samenstelling van elk treintype en het aantal beschikbare rijtuigen in de vloot verschillende soorten. Bepaal het aantal snelle en passagierstreinen dat dagelijks moet worden gevormd om het aantal vervoerde passagiers te maximaliseren.

Transportproblemen oplossen

Transportproblemen zijn de taken van het bepalen van het optimale plan voor het transporteren van vracht van bepaalde vertrekpunten naar bepaalde consumptiepunten.

	b1	b2	…	b k	…	bg
een 1	} GERELATEERDE ARTIKELEN Windows 7 Hoe verander ik de frequentie van een radiografisch bestuurbare auto? Moederborden Het concept van informatie, de verschillende interpretaties ervan Behuizingen PDF online in pagina's splitsen Moederborden HTTP Live Streaming: beste recepten Veiligheid Hoe en wat u een login op Skype maakt Voedingen Hoe u een nieuwe UIN voor ICQ kunt krijgen Internet Xiaomi ziet de computer niet, redenen, oplossingen Telefoon ziet de computer niet: controleer vanaf pc Moederborden “Thema's van bronnen van derden worden niet ondersteund” in MIUI: hoe je het verbod kunt omzeilen Verwerkers In welk land wordt Sony Xperia geproduceerd? Wij raden aan Ander Wat is Windows-bitdiepte? Er zijn verschillende eenvoudige manieren om de editie, versie, build en bitness van Windows 10 te achterhalen. Met release bedoelen we varianten van Windows 10, en dit... Poster Professioneel account Prijzen en installatie Verwerkers De beste draadloze Bluetooth-hoofdtelefoons uit China De beste goedkope hoofdtelefoons van AliExpress Windows 7 Wat is de CWD-bestandsextensie? Monitoren Bestanden op schijf branden Een reservekopie maken Windows 8 Bitgroottes van processors en besturingssystemen Videokaarten Standaard- en externe rapporten instellen Rapportinstellingen opslaan Ander Proplossen Hoe u alle bestanden in een map kunt hernoemen met Total Commander. Hoe u alle bestanden in een map in één keer kunt hernoemen Firmware bijwerken voor Samsung Galaxy-smartphones Firmware voorbereiden Hoe u een Express-paneel toevoegt Hoe verander ik de frequentie van een radiografisch bestuurbare auto? Het concept van informatie, de verschillende interpretaties ervan PDF online in pagina's splitsen HTTP Live Streaming: beste recepten Hoe en wat u een login op Skype maakt Hoe u een nieuwe UIN voor ICQ kunt krijgen Xiaomi ziet de computer niet, redenen, oplossingen Telefoon ziet de computer niet: controleer vanaf pc “Thema's van bronnen van derden worden niet ondersteund” in MIUI: hoe je het verbod kunt omzeilen In welk land wordt Sony Xperia geproduceerd? Wat is Windows-bitdiepte? Poster Professioneel account Prijzen en installatie De beste draadloze Bluetooth-hoofdtelefoons uit China De beste goedkope hoofdtelefoons van AliExpress Wat is de CWD-bestandsextensie? Bestanden op schijf branden Een reservekopie maken Copyright 2024 - Computer- en laptopreparatie. Videokaarten, harde schijven, internet, monitoren.