Oplosser
- 25/04/2007
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Excel heeft een handige tool genaamd Oplosser (Solver in het Engels) die je vindt onder het menu Extra. Het laat je toe het bepalen van een aantal getallen zodanig dat één of ander criterium maximaal, minimaal of gelijk aan een gekozen getal is. Bv. Hoe zet je een aantal prijzen van producten zodat de verwachte winst maximaal is?, Hoeveel punten moet je scoren op testen op school om uiteindelijk gemiddeld 70 % te halen?, Of Met welk vervoersmiddelen leg je een traject af tegen minimale kosten?
Je kan meerdere voorwaarden opleggen aan de te zoeken getallen. Het eerste voorbeeld in bovenstaand bestandje hieronder zal opleggen dat die getallen binair moeten zijn, dus 0 of 1. Stel dat een boekhouder vele afzonderlijke factuurbedragen, en wilt weten welke facturen opgeteld een bepaald getal geven. Bv. zie deze topic op het ondertussen afgesloten Worksheet forum. Er is een nog openstaand bedrag, waarvoor facturen gezocht worden die sommeren tot dat bedrag. Bekijk het bestandje in de downloads sectie voor een mogelijke oplossing.
Aan de slag
Lees de info op het eerste tabblad, en ga dan verder naar het tweede tabblad. Zet de bedragen in kolom A. Als je meer dan 4 facturen hebt, kopieer dan cel A4 naar de cellen eronder, zover als nodig, en pas vervolgens de getallen aan. Zo zorg je ervoor dat de voorwaardelijke opmaak in de cellen van kolom A behouden blijft. Vul het te zoeken bedrag in de gele cel in. Ga dan naar Extra > Oplosser. De instellingen van de Oplosser worden onthouden door Excel. Meer dan waarschijnlijk moet je enkel nog de bereiken van de Oplosser aanpassen.
Voer de Oplosser uit. De truc die we toepassen is als volgt. Oplosser zet in kolom B een 0 of een 1 achter elk factuurbedrag. (Alle nullen op het blad worden verborgen via het uitvinken van Extra > Opties > Weergave > Nulwaarden). De SOMPRODUCT functie in cel E3 telt de getallen met een 1 op. Oplosser zoekt dan naar die eentjes en nulletjes die het SOMPRODUCT gelijk maken aan het bedrag dat je zoekt (in de gele cel). Of, m.a.w., het verschil tussen die twee cellen met gelijk zijn aan exact 0 (zie cel E5 die je op 0 laat komen). Als Oplosser inderdaad een oplossing gevonden heeft (dat is niet gegarandeerd), dan krijgen de juiste factuurbedragen een groene celkleur door de voorwaardelijke opmaak. (Als je Oplosser niet kan vinden in het menu Extra, ga dan naar Extra > Invoegtoepassingen, en kijk of Oplosser of Solver in het lijstje staan en aangevinkt zijn. Staan die niet in het lijstje, zoek dan naar hun locatie op de harde schijf.)
Andere toepassingen
Andere mogelijke toepassingen zijn legio. Denk maar bij wijze van voorbeeld aan het zagen van houtblokken: je minimaliseert het verlies aan houtstukken wanneer je kleinere blokken hout uit grotere blokken snijdt. Dat heb ik voor jullie eens uitgewerkt in het tweede voorbeeldje, dat je vindt in het derde tabblad van het bestand. De gedachte is als volgt. Geef de oppervlaktes in van elk van de kleinere houtstukken en van het grote stuk hout. We minimaliseren dan het verlies aan hout en Oplosser kiest het aantal van elk houtstuk in functie daarvan. Open het bestandje eens en het wordt zeker duidelijker. Nog een laatste toepassing: een tijdje geleden heb ik ook de Oplosser gebruikt voor het zoeken van de nulpunten van een 4de graadsfunctie. Spreek je eigen inspiratie aan en je ontdekt ongetwijfeld nog veel meer mogelijke toepassingen. Succes ermee!
