Lineair programmeren met de Oplosser in Excel

In het rekenblad Excel2013 | Excel2016 | Excel2019 | Excel2021 kun je lineair programmeren met behulp van de invoegtoepassing 'Oplosser'. Dit hulpprogramma vind je bij 'Gegevens' en 'Analyse'. Zie je daar de Oplosser niet staan, dan moet je die eerst activeren. Dat gaat zo:

 

1. Excel openen, er komt een werkmap te voorschijn.
2. Kies bij Bestand voor Opties en daar voor Invoegtoepassingen, je krijgt dan een lijst te zien van invoegtoepassingen die je kunt oproepen (de Oplosser moet daar bij staan).
3. Kies onderaan bij 'Beheren' voor 'Excel-invoegtoepassingen' en klik op Start.
4. Je krijgt nu een dialoogscherm waarin je het vakje voor de Oplosser kunt aanvinken.
5. Doe dat en klik op OK. De Oplosser staat nu onder 'Gegevens' en 'Analyse'.

 

In dit practicum los je het volgende probleem op:

 

Een handelaar in fietsen, e-bikes en kinderzitjes kan deze artikelen aanschaffen bij een groothandel. Hij moet dan rekening houden met de inkoopprijs, maar ook met de opslagruimte. Verder is voor hem de winst per artikel van belang. Er gelden de volgende gegevens:     inkoopprijs (in €) opslagruimte (m2) winst per stuk (in €) fietsen 300 0,5 100 e-bikes 1200 1,0 300 kinderzitjes 36 0,1 20   Hij besluit hoogstens `100` fietsen en hoogstens `50` kinderzitjes te kopen. Hij beschikt over maximaal `101` m2 opslagruimte en over maximaal € 93.000,= om de artikelen aan te schaffen. Het gaat deze ondernemer om een zo groot mogelijke winst. Hoeveel moet hij van elk van deze artikelen aanschaffen?

 

Voor het oplossen van dit probleem kies je eerst de beslissingsvariabelen. Hier:
`x` = aantal fietsen
`y` = aantal e-bikes
`z` = aantal kinderzitjes

 

Je vindt dan in de tekst deze randvoorwaarden of restricties:

 

`300x + 1200y + 36z le 93000`
`0,5x + 1y + 0,1z le 101`
`0 le x le 100`
`0 le y`
`0 le z le 50`

 

Verder geldt als doelfunctie de totale winst: `W = 100x + 300y + 20z`.

 

Gezocht wordt een maximum van deze doelfunctie onder de beschreven randvoorwaarden.

 

Nu naar Excel met het bij dit practicum horende bestand:

 

LPfietsen.xlsx.

 

Open dit bestand met de rechtermuisknop en sla het op jouw computer op.
Je ziet hoe de gegevens hierboven zijn ingevoerd.
Er is een kolom 'aantal' gemaakt. De cellen C4, C5 en C6 stellen de variabelen `x`, `y` en `z` voor. Vooralsnog zijn daar de waarden `0`, `0` en `0` ingevoerd. Geef deze cellen via Formules, Naam definiëren ook de namen `x`, `y` en `z` in de juiste volgorde.
In cel D7 zie je hoe de totale inkoopprijs wordt berekend met de XL-formule: =$D$4*x+$D$5*y+$D$6*z.
Controleer zo ook de berekeningen van de cellen E7 en F7.
De cellen D7, E7, F7 hebben de namen "aantal", "ruimte" en "winst" gekregen
De restricties gelden voor de cellen C4 (`x`), C6 (`z`), D7 (inkoop) en E7 (ruimte).
De doelfunctie zit in cel F7 (winst).
Ga dat na!

 

Roep nu de Oplosser op en ga als volgt te werk:

 

1. Vul de doelfunctie in bij Doelfunctie bepalen: winst
2. Geef bij Naar aan: MAX
3. Geef de beslissingsvariabelen aan bij Door veranderen van variabelecellen: $C$4 : $C$6 (dit betekent: C4 t/m C6, je kunt ook x;y;z invullen)
4. Voeg bij 'Onderworpen aan de randvoorwaarden' alle randvoorwaarden in. De eerste is bijvoorbeeld: x < 100.
5. Hier voeg je vier randvoorwaarden toe.
6. Als alle randvoorwaarden kies je bij 'Selecteer oplossingsmethode' voor Simplex LP.
7. Tenslotte kies je voor Oplossen.

 

Na eventjes rekenen geeft de computer aan dat hij een oplossing heeft kunnen vinden. De resultaten staan dan in de oorspronkelijke werkmap.
Ga na, dat je vindt:
`x = 80`
`y = 56`
`z = 50`
`W = 25800` als maximale winst

 

Probeer het lineair programmeren met de Oplosser in Excel goed onder de knie te krijgen met behulp van lineair programmeringsproblemen uit je wiskundeboek.

---

Math4all