WisFaq!

De digitale vraagbaak voor het wiskundeonderwijs

HOME

samengevat

\require{AMSmath}

Optimale verdeling in Excel van waarden

Ik wil verschillende vouchers met een bepaalde waarde verdelen aan winnaars van bedragen. De waarden van de vouchers bedragen:

- A: 34,30
- B: 28,00
- C: 15,80
- D: 35,00
- E: 51,50
- F: 33,60

Stel een persoon heeft vouchers in waarde van 100 EURO gewonnen, hoe kan ik in exel dan laten berekenen hoeveel vouchers hij krijgt van welk type om zo optimaal mogelijk aan in de buurt van het gewonnen bedrag van 100 EURO te komen? Omdat het preciese bedrag niet kan worden bereikt moet de voucher waarde altijd BOVEN de gewonnen waarde uitkomen, nooit eronder, maar met een zo minimaal mogelijk verschil.
Hoe krijg ik dit in excel voor elkaar?

Alvast heel erg bedankt voor de hulp!
Marcel
Iets anders - zaterdag 9 februari 2008

Antwoord

Hallo Marcel,

Als ik je vraag goed begrepen hebt wil je het verschil tussen het gewonnen bedrag en de som van de gewonnen vouchers minimaliseren. Dus als iemand 100 euro heeft gewonnen, kan je 1 keer voucher E en 2 keer voucher B geven. Dan is het verschil 7,50. (51.5 + 28*2 - 100). Een betere oplossing zou zijn 2 keer voucher D en 1 keer voucher F. Het verschil is dan 3,60. Dit probleem heeft niks met kansverdelingen te maken, maar is een lineair programmeren probleem. En om specifiek te zijn een integer lineair programmeren probleem.

Omdat je vraag specifiek is hoe je dit in excel voor elkaar krijgt zal ik je niet lastigvallen met de wiskundige achtergrond om dit probleem op te lossen. Mocht je daar toch meer over willen weten, kan ik je aanraden bij http://en.wikipedia.org/wiki/Linear_programming een kijkje te nemen.

In excel moet je eerst de solver add-in installeren voordat excel dit probleem kan oplossen. Dit kan via het menu Tools = Ad-Ins..

Om het probleem op te lossen heb ik eerst een sheet gemaakt met in cellen B2-B7 de waarden van de vouchers gezet. In cellen C2-C7 heb ik in elke cel een 0 gezet. In deze cellen moet het aantal vouchers zo door excel worden gezet. In cel C9 heb ik de gewonnen prijs neergezet. In deze specifiek vraag dus 100. In cel C10 heb ik de volgende formule gebruikt: "=SUMPRODUCT(B2:B7;C2:C7)-C9".
In cel C10 word nu dus het verschil uitgerekend tussen de gewonnen prijs en de som van de waarden van de uit te keren vouchers. Als je de excelsheet zo hebt opgesteld kan je de solver gaan gebruiken.

De solver kan gestart worden via Tools=Solver... Als je daarop klikt krijg je een scherm "solver parameters". Hier moeten een aantal dingen ingevult worden. Allereerst de target cell. Hiermee word bedoeld de cel die gemaximaliseerd of geminimaliseerd moet worden. Hier is dat dus C10. Er moet ook een keuze gemaakt worden tussen max, min en value of. In dit geval moet geminimaliseerd worden en moet je dus voor min kiezen.

In de solver moet onder het kopje "By changing cells:" de cellen worden ingevoerd die de solver moet aanpassen om tot een oplossing te komen. Hier zijn dat dus de cellen C2 tot en met C7, omdat ik hier het aantal vouchers heb geplaatst.

Tot slot moet bij de solver onder het kopje "Subject to the constraints:" worden ingevuld aan welke eisen voldaan moet worden. Er zijn hier drie voorwaarden:

Voorwaarde 1: Het aantal vouchers moet een geheel getal zijn. Deze voorwaarde kan toegevoegd worden door op Add te klikken. Nu kom je in een "Add constraint" schermpje. Bij "Cell reference" moet je de cel invullen waarvoor de voorwaarde geld. In het midden kan je het type voorwaarde instellen. En als het nodig is kan je rechts ("Constraint:") een parameter aan de voorwaarde meegeven. Voor deze voorwaarde moet dit zes keer gedaan worden. Er zijn immers 6 vouchers. Voor de eerste wordt bij Cell reference ingevuld C2. Bij het type voorwaarde moet om te zorgen dat het een geheel getal moet zijn gekozen worden voor "int". Als je vervolgens op Add klikt word de voorwaarde toegevoegd. Dit moet dus voor alle 6 de vouchers gedaan worden.

Voorwaarde 2: Het aantal vouchers mag geen negatief getal zijn. Deze voorwaarde wordt weer op dezelfde manier ingevoerd. Nu moet alleen als type voorwaarde worden ingevuld "=" (groter dan of gelijk aan). Hier moet wel een parameter aan de voorwaarde worden meegegeven. Dus rechts word 0 ingevuld. Dit betekent dat de target cell dus groter dan of gelijk aan 0 moet zijn. Dit moet dus ook voor alle zes de vouchers appart worden ingevuld.

Voorwaarde 3: De totale voucher waarde moet boven de 100 uitkomen. (Dus cel C10 mag niet negatief zijn) Dit kan op dezelfde manier als voorwaarde twee worden aangegeven alleen nu met cel C10 als target cell.

Wanneer alle voorwaarden erin staan kan je op "Cancel" klikken. Je komt nu weer terecht in het Solver parameters scherm. Hier zie je nu ook alle zojuist ingevoerde voorwaarden staan. Als je nu op "Solve" klikt berekent excel de meest optimale oplossing. In dit voorbeeld (een prijs van 100 euro) is de oplossing als het goed is twee keer voucher A en twee keer voucher C, waarmee de totale voucher waarde uitkomt op 100,20 euro.

Het is een beetje een lang verhaal geworden, maar ik hoop dat het zo duidelijk is. Mochten er nog vragen zijn, dan merk ik het wel.

mvg.
David

DvdB

Vragen naar aanleiding van dit antwoord? Klik rechts..!
maandag 11 februari 2008

Re: Optimale verdeling in Excel van waarden