Een complete gids voor het gebruik van Solver in Excel voor probleemoplossing

Microsoft Excel is uitgerust met een verscheidenheid aan functies die het proces van het uitvoeren van berekeningen en het oplossen van vergelijkingen stroomlijnen, waardoor de productiviteit wordt verbeterd. Een van deze functionaliteiten is de Solver-tool, die overeenkomsten vertoont met de Goal Seek-functie.

Solver wordt doorgaans gebruikt voor ‘What-if’-analyses en stelt gebruikers in staat de waarde van een bepaalde cel onder meerdere beperkingen te bepalen. Het kan worden gebruikt om een specifieke waarde, een minimumdrempel of een maximumlimiet voor een getal te bepalen. Hoewel het mogelijk niet elk probleem aanpakt, is Solver een onschatbare bron voor optimalisatiescenario’s waarbij het vinden van de best mogelijke beslissing cruciaal is.

Deze tool werkt door de waarden van specifieke cellen, bekend als beslissingsvariabelen, in een spreadsheet aan te passen om de maximum- of minimumwaarde van een andere cel, de objectieve cel, te identificeren. Solver is toepasbaar voor verschillende programmeertypen, waaronder lineaire en niet-lineaire programmering, integerprogrammering en doelgerichte taken.

Typische toepassingen van Solver zijn onder meer het minimaliseren van transportkosten, het opstellen van optimale werkschema’s, het vaststellen van het beste budget voor reclame-initiatieven of het maximaliseren van investeringsrendementen.

Oplosser activeren in Excel

Om Solver te kunnen gebruiken, moet u eerst deze add-in inschakelen, aangezien deze niet standaard is geactiveerd zoals de Goal Seek-functie. Gelukkig is het proces vrij eenvoudig.

Begin met het selecteren van het menu Bestand boven aan het scherm en klik vervolgens op ‘Opties’.

Klik vervolgens op ‘Invoegtoepassingen’ aan de linkerkant van het venster Opties.

Selecteer nu ‘Excel-invoegtoepassingen’ in het vervolgkeuzemenu ‘Beheren’ onderaan en klik op ‘Ga’.

In het dialoogvenster dat nu verschijnt, vinkt u het vakje naast ‘Solver Add-in’ aan om deze in te schakelen en klikt u op ‘OK’.

U zou nu de Oplosser moeten zien wanneer u op het tabblad ‘Gegevens’ in Excel klikt.

Belangrijkste componenten van Solver

Voordat Solver de optimale waarde voor een probleem kan identificeren, moeten drie hoofdcomponenten worden vastgesteld:

Doelcel: Deze cel bevat de formule die het doel of de doelstelling van het probleem weergeeft: minimaliseren, maximaliseren of een specifieke waarde bereiken.
Variabele cellen: Deze cellen bevatten de variabelen die Solver zal aanpassen om het doel te bereiken. Er kunnen maximaal 200 variabele cellen worden aangewezen in Solver.
Beperkingen: Beperkingen zijn de parameters waarbinnen Solver moet opereren om het gewenste resultaat te bereiken. Ze definiëren de voorwaarden waaraan moet worden voldaan bij het bepalen van de vereiste waarden.

Solver toepassen

Zodra Solver is toegevoegd aan Excel, kunt u het gaan gebruiken. In dit voorbeeld gebruiken we Solver om de winst van een palletproductiebedrijf te berekenen op basis van bekende resourcewaarden, zoals de benodigde resources per pallet, samen met de beschikbaarheid van verschillende pallettypen.

De cellen B3 tot en met E3 geven de verschillende typen pallets weer die het bedrijf moet produceren. De rij direct daaronder geeft het aantal pallets weer dat voor elk type moet worden geproduceerd, geïnitialiseerd op nul. De daaropvolgende rij geeft de winst weer die aan elk pallettype is gekoppeld. Ons doel is om te bepalen hoeveel pallets er voor elk type moeten worden geproduceerd, waarbij de totale winst wordt weergegeven in cel F5. De beperkingen hier zijn de beschikbare bronnen, die bepalen hoeveel pallets het bedrijf haalbaar kan produceren.

Om te beginnen klikt u op ‘Solver’ in de rechterbovenhoek, waarna het dialoogvenster Solver verschijnt. Voer een naam of celverwijzing in voor de doelcel en zorg ervoor dat deze een formule bevat. In dit scenario fungeert cel F5 als de doelfunctie, die de totale winst voor alle pallettypen samen oplevert, rekening houdend met zowel de beschikbare bronnen als de te produceren pallets.

Selecteer in het veld ‘By Changing Variable Cells’ het bereik B4:E4 door uw muis te slepen of door de celnamen rechtstreeks in te typen. Deze cellen vertegenwoordigen het aantal pallets per type en zijn momenteel ingesteld op nul. Solver past deze waarden aan tijdens de uitvoering.

Klik vervolgens op de knop ‘Toevoegen’ om beperkingen in te voeren. Solver berekent hoeveel pallets het bedrijf kan produceren op basis van de beschikbaarheid van het materiaal, zoals lijm, persen, dennen- en eikenhoutsnippers. U ziet dat de waarden in de kolom ‘Gebruikt’, die momenteel op nul staan, veranderen wanneer u Solver uitvoert.

Typ F8:F11 voor de ‘Celverwijzing’, die overeenkomt met de kolom ‘Gebruikt’, en G8:G11 voor de kolom ‘Beschikbaar’ in het veld Beperking. Zorg ervoor dat de relatie <=standaard is ingesteld, wat aangeeft dat waarden in de kolom Gebruikt kleiner of gelijk moeten zijn aan die in de kolom Beschikbaar.

Nadat u alle variabelen en beperkingen hebt ingevoerd, klikt u nogmaals op ‘Toevoegen’ in het dialoogvenster ‘Beperking toevoegen’ en sluit u het. U zult ook merken dat de optie ‘Maak onbeperkte variabelen niet-negatief’ standaard is ingeschakeld in het dialoogvenster Solver-parameters, zodat alle variabelen niet-negatief blijven, zelfs als er geen specifieke beperkingen zijn ingesteld.

Nadat u de gegevens in het dialoogvenster Oplosserparameters hebt ingevuld, klikt u op de knop ‘Oplossen’ en wacht u tot Excel de resultaten levert.

Wanneer de resultaten zijn gegenereerd, verschijnt het dialoogvenster Solver Results, met de nieuwe waarden in cellen B4 tot en met E4. Houd er rekening mee dat Solver uw gegevens wijzigt. Als u liever terug wilt naar de oorspronkelijke waarden, kunt u de optie ‘Restore Original Values’ selecteren. Nadat u hebt besloten of u de oplossing wilt behouden of wilt terugkeren naar de oorspronkelijke gegevens, controleert u of ‘Answer’ aan de rechterkant is aangevinkt en klikt u op ‘OK’ om het dialoogvenster te sluiten.

Als u ervoor kiest om de nieuwe oplossing te behouden, wordt dit weergegeven in uw spreadsheet wanneer u het Solver-dialoogvenster sluit. De productie van het bedrijf omvat 23 Tahoe-pallets, 15 Pacific-pallets, 39 Savannah-pallets en geen van de Aspen-pallets, die worden aangegeven in de rij Pallets van B4 tot D4. Bovendien wordt de cel voor totale winst bijgewerkt van nul naar $ 58.800.

Belangrijke overwegingen

Net als de functie Doelzoeken in Excel, vereist Solver dat u vooraf de benodigde formules instelt om correct te kunnen functioneren.
U kunt de probleemoplossingsmethode beïnvloeden door de knop ‘Opties’ te selecteren in het dialoogvenster Solver-parameters. Daar kunt u waarden opgeven voor ‘Alle methoden’, ‘GRG Non-lineair’ en ‘Evolutionair’.
Bovendien kunt u met Solver modellen opslaan en laden voor later gebruik. Zorg ervoor dat u bij het laden van bestaande modellen de referentie invoert voor het volledige bereik van cellen die relevant zijn voor het probleem.
Het is raadzaam om met een kopie van uw gegevens te werken wanneer u Solver gebruikt, omdat Solver de oorspronkelijke gegevens wijzigt nadat de bewerking is uitgevoerd. Herstel van de gegevens is mogelijk niet meer mogelijk nadat er wijzigingen zijn aangebracht.

Bron