Excel Solver: Optimaliseer complexe beslissingen met meerdere variabelen

Wat-alsanalyse laat je verschillende scenario’s testen om te zien hoe ze je uitkomsten beïnvloeden. Het geeft je een veilige ruimte om te experimenteren met variabelen zoals kosten of capaciteit om de meest effectieve oplossing te vinden. Deze aanpak is cruciaal voor iedereen die middelen beheert, planningen maakt of strategische zakelijke beslissingen neemt.

Handmatig trial-and-error kost uren en mist vaak het optimale antwoord. Solver garandeert in seconden wiskundige optimaliteit. Dat is het verschil tussen gokken bij middelenallocatie en zeker weten dat je de best mogelijke beslissing neemt.

Deze tutorial leidt je door Excel Solver, de geavanceerde functie die is gebouwd voor dit soort optimalisatieproblemen. Ik laat je zien hoe je je spreadsheet opzet, de analyse uitvoert en de bevindingen interpreteert aan de hand van praktijkvoorbeelden.

Excel Solver begrijpen: basisconcepten

Voordat ik je laat zien hoe je Solver gebruikt, leg ik de drie bouwstenen van elk optimalisatieprobleem uit. Dit zijn geen modewoorden, ze zorgen ervoor dat Solver werkt.

Doelcel, beslissingsvariabelen en beperkingen

Denk aan de doelcel als je doel. Het is de formule die je wilt maximaliseren of minimaliseren, zoals totale winst of totale kosten. Dit moet een formule zijn, niet zomaar een getal, omdat Solver moet zien hoe het verandert wanneer je dingen aanpast.

Beslissingsvariabelen zijn wat je probeert te achterhalen. Hoeveel stoelen moeten we maken? Hoeveel moeten we uitgeven aan elk marketingkanaal? Ik begin deze altijd op nul, zodat je makkelijk ziet wat er is veranderd nadat Solver heeft gedraaid.

Beperkingen zijn je limieten en regels. Je kunt niet meer uitgeven dan je budget. Je kunt geen negatieve hoeveelheden produceren. Sommige projecten hebben minimale investeringsniveaus nodig. Deze beperkingen maken je wiskunde realistisch en weerspiegelen echte zakelijke randvoorwaarden.

Zo werkt het: Solver past je beslissingsvariabelen aan om je doel te optimaliseren en zorgt er tegelijk voor dat alle beperkingen worden gerespecteerd. Als je operationeel onderzoek hebt gedaan, klinkt dit bekend. Zo niet, denk er dan aan als slimme trial-and-error met garanties.

Vergelijking met Doel zoeken

Hier krijg ik vaak vragen over. Doel zoeken is eenvoudiger. Het verandert één cel om een specifiek doel te halen. Gebruik het wanneer je het gewenste antwoord weet en alleen de invoer wilt vinden die je daar brengt.

Solver is anders. Het vindt het beste antwoord wanneer je meerdere beslissingen moet nemen en beperkingen moet volgen. ISolver is used if je iets moet maximaliseren of minimaliseren terwijl je meerdere variabelen in balans houdt.

Mijn vuistregel: als je precies kunt zeggen welk getal je wilt, gebruik Doel zoeken. Als je je afvraagt: "wat is de beste manier om dit te doen?" gebruik Solver.

De Solver-invoegtoepassing laden en activeren

Solver wordt meegeleverd met Excel, maar je moet het eerst inschakelen. Geen zorgen, het kost minder dan een minuut.

Installatiestappen voor Windows en Mac

Voor Windows:

1. Bestand > Opties > Invoegtoepassingen
2. Selecteer Excel-invoegtoepassingen in de beheer-lijst, klik op Start
3. Vink Solver-invoegtoepassing aan, klik op OK
4. Ga naar het tabblad Gegevens en zoek Solver in de groep Analyseren.

Voor Mac:

1. Extra > Excel Invoegtoepassingen
2. Vink Solver-invoegtoepassing aan, klik op OK

Als je Excel Online gebruikt, heb ik slecht nieuws. Dat ondersteunt Solver niet. Je hebt de desktopversie nodig (2016 of later).

Configuratie

Zodra het is geactiveerd, open je Solver via het tabblad Gegevens > knop Solver. De standaardinstellingen werken prima voor de meeste problemen, dus denk hier niet te veel over na.

Dat gezegd hebbende, twee instellingen zijn relevant als je problemen tegenkomt. Precisie bepaalt hoe strikt Solver is bij het naleven van beperkingen. Ik stel dit strakker in voor financiële modellen waar centen tellen. Convergentie vertelt Solver wanneer te stoppen met het verbeteren van de oplossing. Ik verlaag dit als Solver te vroeg stopt met een net-niet-optimale uitkomst.

Je vindt beide onder het dialoogvenster Solver Parameters > knop Opties, maar eerlijk gezegd raak ik ze zelden aan.

Je probleem opzetten: modelformulering in Excel

Een overzichtelijke spreadsheet maakt het configureren van Solver eenvoudig.

Laat me je meenemen door een voorbeeld: een meubelfabrikant die stoelen en tafels maakt. Stoelen leveren $60 winst op (vereisen 4 arbeidsuren, 2 eenheden hout). Tafels leveren $75 winst op (vereisen 6 arbeidsuren, 2 eenheden hout). We hebben 240 arbeidsuren en 100 eenheden hout beschikbaar. Hoeveel van elk moeten we maken?

Probleemdefinitie en spreadsheetopzet

Ik organiseer elk Solver-model op dezelfde manier. Vier secties, altijd:

1. Invoergegevens (productinfo, middelenlimieten)
2. Beslissingsvariabelen (te produceren hoeveelheden)
3. Doelfunctie (totale winst)
4. Beperkingsberekeningen (ingezette middelen vs. beschikbaar)

Compleet Solver-model met alle vier secties. Afbeelding door auteur.

Deze indeling maakt alles transparant. Iedereen die naar je model kijkt, ziet direct wat je optimaliseert en met welke beperkingen je werkt.

Verder maak ik altijd zogeheten benoemde bereiken voor belangrijke cellen. In plaats van =B8*B3+B9*B4 te schrijven, kan ik =SUMPRODUCT(QuantityToProduce,ProfitPerUnit) schrijven. Veel makkelijker te begrijpen zes maanden later als je bent vergeten wat die celverwijzingen betekenen.

Benoemde bereiken maken formules leesbaar. Afbeelding door auteur.

Maken: selecteer cellen, klik in het Naamvak, typ een naam, druk op Enter. Kost twee seconden, bespaart later hoofdpijn.

Beslissingsvariabelen en doelfunctie

Ik begin beslissingsvariabelen op nul. Dit helpt bij het vergelijken. Sommigen starten met schattingen, dat is prima, maar nul werkt voor mij.

De doelfunctie moet een formule zijn. Voor ons meubelvoorbeeld gebruik ik:

=SUMPRODUCT(B8:B9,B3:B4)

Dit berekent: (Stoelen × $60) + (Tafels × $75)

Simpele vermenigvuldiging en optelling. Dat is alles wat je nodig hebt voor de meeste zakelijke problemen.

Beperkingen

Beperkingen zijn waar je echte zakelijke limieten naar wiskunde vertaalt. Onze meubelmaker heeft twee middelenbeperkingen:

• Arbeid: =SUMPRODUCT(B8:B9,C3:C4) <= 240

• Hout: =SUMPRODUCT(B8:B9,D3:D4) <= 100

En hier is iets wat ik met schade en schande heb geleerd: voeg altijd niet-negativiteitsbeperkingen toe (B8:B9 >= 0). Zonder die kan Solver voorstellen om negatieve stoelen te maken, wat in de echte wereld natuurlijk niet werkt.

Ik voeg ook een statuskolom toe met =ALS()-formules zoals  =ALS(B16<=C16,"OK","OVERSCHREDEN"). Dit geeft me direct visuele feedback nog vóór ik Solver draai. Groen licht is gaan, rood betekent dat er iets mis is met je setup.

Stap voor stap: zo gebruik je Solver in Excel

Oké, het model staat. Nu laat ik je zien hoe je Solver daadwerkelijk draait. Dit deel kost hooguit 30 seconden zodra je weet wat je doet.

Het dialoogvenster Solver-parameters gebruiken

Open Solver via het tabblad Gegevens > knop Solver.

Solver-dialoog vóór configuratie. Afbeelding door auteur.

Dit vul je in:

Doel instellen: Klik in dit veld en klik dan op cel B12 (onze cel Totale winst).

Naar: Selecteer Max omdat we de winst willen maximaliseren.

Door de variabele cellen te wijzigen: Selecteer B8:B9 (de hoeveelheden waarover we beslissen).

Onderworpen aan de beperkingen: Klik op Toevoegen voor elke beperking:

• Arbeid: $B$16 <= $C$16

• Hout: $B$17 <= $C$17

• Niet-negativiteit: $B$8:$B$9 >= 0

Een oplossingsmethode selecteren: Kies Simplex LP. Ik leg de andere methoden later uit, maar voor lineaire problemen zoals deze is Simplex LP wat je wilt.

Solver geconfigureerd en klaar om op te lossen. Afbeelding door auteur.

Het model oplossen

Voordat je op Oplossen klikt, kijk even naar je huidige waarden. Alles zou op nul moeten staan.

Initiële staat met overal nullen. Afbeelding door auteur.

Klik nu op Oplossen. Voor eenvoudige problemen zoals het onze duurt dit slechts enkele seconden.

Gelukt! Solver vond de optimale oplossing. Afbeelding door auteur.

Wanneer het dialoogvenster Solverresultaten verschijnt, zou er moeten staan "Solver heeft een oplossing gevonden." Vink onder Rapporten de vakjes Antwoord en Gevoeligheid aan. Deze geven je gedetailleerde analyses die we later bekijken. Klik daarna op OK.

De optimale oplossing: 30 stoelen en 20 tafels. Afbeelding door auteur.

En daar is het. De oplossing laat zien dat we 30 stoelen en 20 tafels moeten maken voor $3.300 winst. Let op dat beide beperkingen "OK" tonen, wat betekent dat we onze middelen efficiënt gebruiken zonder limieten te overschrijden.

Workflow-samenvatting

Dit is het proces dat ik elke keer volg:

1. Bouw het model (inputs, variabelen, doel, beperkingen)
2. Configureer Solver (stel in wat je optimaliseert en wat je aanpast)
3. Klik op Oplossen en wacht
4. Check of de oplossing bedrijfsmatig logisch is
5. Bekijk de rapporten voor diepere inzichten

Die laatste stap is belangrijk. Alleen omdat Solver een antwoord vindt, betekent niet dat het het juiste antwoord is voor jouw bedrijf. Doe altijd een sanity check op de resultaten.

Solver-methoden, parameters en geavanceerde opties

Solver biedt drie verschillende algoritmen. De juiste kiezen maakt enorm verschil in snelheid en nauwkeurigheid.

Overzicht van oplossingsmethoden

Drie oplossingsmethoden voor verschillende probleemtypes. Afbeelding door auteur.

Simplex LP

Simplex LP gebruik ik 80% van de tijd. Het is voor lineaire problemen waar alles neerkomt op optellen en vermenigvuldigen. Productmix? Budgetallocatie? Middelenplanning? Simplex LP. Het is snel en garandeert dat je het beste antwoord krijgt.

GRG Nonlinear

GRG Nonlinear is voor wanneer je krommen, kwadraten of exponenten in je formules hebt. Denk aan prijsmodellen waar de vraag daalt naarmate de prijs stijgt, maar niet in een rechte lijn. Of portefeuille-optimalisatie met variantieberekeningen. Het vindt lokale optima, die misschien niet absoluut het beste zijn, maar meestal goed genoeg.

Evolutionair

Evolutionair is de brute-forceoptie. Gebruik het als je integerbeperkingen hebt (alleen hele eenheden), binaire beslissingen (ja of nee), of =ALS()-formules in je rekenbladen. Het is trager omdat het in feite een hoop willekeurige oplossingen probeert en de goede bewaart. Maar het werkt wanneer de andere methoden falen.

Mijn beslisregel is simpel: begin met Simplex LP. Als je formules exponenten of producten van variabelen hebben, schakel over naar GRG Nonlinear. Als je integers of binaire variabelen nodig hebt, ga voor Evolutionair.

Solver-opties en -instellingen

Klik op Opties in het Solver-dialoogvenster voor geavanceerde instellingen. Ik rommel hier zelden mee, maar dit is relevant:

Beperk hoe lang Solver draait met Maximale tijd. De standaard is 100 seconden, wat ruim voldoende is voor de meeste problemen. Ik verhoog dit voor echt grote modellen die meer rekentijd nodig hebben.

Zet een limiet op het aantal pogingen met Iteraties. Als Solver deze limiet haalt zonder op te lossen, verhoog het dan.

Bepaal hoe strikt Solver met beperkingen omgaat met Precisie. Ik zet dit strakker voor financiële modellen waar elke cent telt, maar laat het verder met rust.

Rapportage en gevoeligheidsanalyse

Weet je nog dat ik zei dat je die rapportvakjes moest aanvinken? Dit is wat je krijgt.

Antwoordrapport legt de oplossing vast. Afbeelding door auteur.

Het Antwoordrapport is je papieren spoor. Het laat zien wat er veranderde: beslissingsvariabelen gingen van 0 naar 30 stoelen en 20 tafels, winst ging van $0 naar $3.300. Het vertelt ook welke beperkingen bindend zijn (volledig benut) versus niet-bindend (met speling). In ons geval zijn zowel arbeid als hout bindend, wat betekent dat we alle beschikbare capaciteit benutten.

Hoeveel inputs kunnen veranderen voordat de oplossing wijzigt. Afbeelding door auteur.

Het Gevoeligheidsrapport is waar het interessant wordt. De kolommen Toegestane verhoging/verlaging tonen hoeveel je inputs kunnen veranderen voordat je een andere oplossing nodig hebt. Brede bereiken betekenen dat je antwoord robuust is. Smalle bereiken betekenen dat kleine wijzigingen alles kunnen omgooien.

Schaduwprijzen tonen de waarde van middelen. Afbeelding door auteur.

Maar het echte goud is de schaduwprijs. Die vertelt je wat één extra eenheid van een middel waard is. Als arbeid een schaduwprijs van $7,5 heeft, levert één extra arbeidsuur $7,5 extra winst op. Dus als je uitzendkrachten kunt inhuren voor $5/uur, doen. Bij $10/uur, laten.

Belang van gevoeligheidsanalyse

Ik gebruik deze rapporten om drie vragen te beantwoorden. In welke middelen moet ik investeren? Is mijn oplossing fragiel? Wat als mijn forecasts fout zijn? De rapporten geven duidelijke antwoorden op alle drie.

Eerst kijk ik naar schaduwprijzen om te bepalen in welke middelen ik moet investeren. Hoge schaduwprijs betekent hoge waarde.

Daarna check ik de toegestane bereiken om te zien of mijn oplossing fragiel is. Als de winst al bij $1 wijziging verandert, sta je op glad ijs. Als het $50 kan veranderen, zit je goed.

Tot slot vraag ik me af wat als mijn forecasts fout zijn? De toegestane bereiken vertellen hoe ver je ernaast kunt zitten voordat je moet herberekenen.

Mijn advies: check altijd schaduwprijzen voordat je een budget goedkeurt. Als een middel een schaduwprijs van nul heeft, betekent dat dat je overcapaciteit hebt. Daar meer in investeren is zonde. Zet je geld in waar de schaduwprijzen het hoogst zijn.

Probleemoplossing en foutafhandeling

De foutmeldingen van Solver klinken eng, maar zijn meestal makkelijk te verhelpen. Dit zijn de meest voorkomende.

Veelvoorkomende fouten en meldingen

Solver kon geen haalbare oplossing vinden

Dit betekent dat je beperkingen met elkaar in conflict zijn.

 Geen oplossing voldoet aan alle beperkingen. Afbeelding door auteur.

Dit gebeurt meestal wanneer je iets onmogelijks vraagt, zoals een winstdoel proberen te halen dat de beschikbare middelen simpelweg niet ondersteunen. De oplossing is om beperkingen één voor één te verwijderen totdat Solver werkt; zo identificeer je precies welke beperking het conflict veroorzaakt.

Waardes van doelcel convergeren niet

Dit betekent dat je doel onbeperkt kan groeien. Je mist ergens een bovengrens. Echte bedrijven hebben altijd limieten, voeg die toe.

Solver kan de huidige oplossing niet verbeteren

Dit gebeurt meestal bij niet-lineaire problemen. Probeer andere startwaarden of schakel over naar de Evolutionaire methode. Soms is een lokaal optimum goed genoeg.

Fouten in modelformulering

Cirkelverwijzingen

Dit gebeurt wanneer cel A afhankelijk is van cel B, die weer afhankelijk is van cel A. Excel waarschuwt je hiervoor. Gebruik tabblad Formules > Voorgangers traceren om de lus te vinden en te doorbreken.

Integerbeperkingen

Integerbeperkingen toevoegen voor hele getallen. Afbeelding door auteur.

Beperkingen van Solver en alternatieven voor grootschalige problemen

Excel Solver is geweldig, maar heeft grenzen waar je van op de hoogte moet zijn.

De beperkingen van Solver begrijpen

Standaard Solver kan maximaal 200 beslissingsvariabelen en 100 beperkingen aan. Dat klinkt als veel tot je 200 medewerkers over 50 tijdslots wilt inroosteren. Dat zijn 10.000 variabelen, ver boven wat Solver aankan.

Ik gebruik Solver voor maandelijkse productieplannen, kwartaalbudgetten en projectselectie. Het is perfect voor tactische beslissingen. Maar het is niet gebouwd voor enterprise-schaalproblemen zoals supplychainnetwerken met duizenden locaties.

Alternatieven voor grote problemen

OpenSolver

OpenSolver is gratis en haalt die limieten weg. Het kan zoveel variabelen aan als het geheugen van je computer toelaat, gebruikt dezelfde interface als Excel Solver en je kunt het downloaden via OpenSolver.org. Ik raad het aan zodra je de standaard Solver ontgroeit.

Frontline Solver Premium

Frontline Solver kost geld ($300-$1.500/jaar) maar ondersteunt duizenden variabelen met betere algoritmen. Dit zou ik gebruiken als ik dit fulltime professioneel deed.

Losse software

Tools zoals CPLEX of Gurobi kunnen miljoenen variabelen aan maar vereisen programmeerkennis en een serieuze investering. Dat is op enterprise-niveau.

Wat mij betreft dekt Excel Solver voor de meeste business-analisten 95% van wat je nodig hebt. Als je de grenzen raakt, is dat eigenlijk een goed teken: je doet werk dat geavanceerd genoeg is om betere tools te rechtvaardigen.

Conclusie

We hebben veel behandeld, van de basisopzet tot het interpreteren van schaduwprijzen. 

Nu kun je in plaats van meningen te debatteren in een vergaderruimte, de cijfers op het scherm zetten. "Dit is het wiskundig optimale plan." Dat verandert het gesprek. Je stopt met gokken en begint met bewijzen.

Ga het vooral proberen op je volgende lastige probleem, dat planningsconflict of budgetkrapte waar je omheen loopt. Misschien blijkt het perfecte antwoord al die tijd al in je spreadsheet te zitten.

En als je meer technieken wilt leren om je baas te imponeren, volg dan onze  Data-analyse in Excel-cursus om verder te bouwen.