De Excel Solver-invoegtoepassing voert wiskundige optimalisatie uit. Dit wordt meestal gebruikt om complexe modellen op gegevens te passen of iteratieve oplossingen voor problemen te vinden. U wilt bijvoorbeeld een curve door enkele gegevenspunten laten passen met behulp van een vergelijking. De Oplosser kan de constanten in de vergelijking vinden die het beste passen bij de gegevens. Een andere toepassing is waar het moeilijk is om een model te herschikken om de vereiste uitvoer het onderwerp van een vergelijking te maken.
Waar is Oplosser in Excel?
De Oplosser-invoegtoepassing wordt meegeleverd met Excel, maar wordt niet altijd geladen als onderdeel van een standaardinstallatie. Om te controleren of het is geladen, selecteert u het tabblad DATA en zoekt u naar het pictogram Solver in de sectie Analysis.
Als u Oplosser niet kunt vinden onder het tabblad DATA, moet u de invoegtoepassing laden:
-
Selecteer het tabblad FILE en selecteer vervolgens Options.
Image -
Selecteer in het Options dialoogvenster Add-Ins op de tabbladen aan de linkerkant.
Image -
Selecteer onder in het venster Excel Add-ins in de vervolgkeuzelijst Manage en selecteer Go…
Image -
Vink het selectievakje naast Solver Add-in aan en selecteer OK.
Image -
De Solver opdracht zou nu moeten verschijnen op het DATA tabblad. U bent klaar om Oplosser te gebruiken.
Image
Oplosser gebruiken in Excel
Laten we beginnen met een eenvoudig voorbeeld om te begrijpen wat de Oplosser doet. Stel je voor dat we willen weten welke straal een cirkel geeft met een oppervlakte van 50 vierkante eenheden. We kennen de vergelijking voor de oppervlakte van een cirkel (A=pi r2). We kunnen deze vergelijking natuurlijk herschikken om de straal te geven die nodig is voor een bepaald gebied, maar laten we als voorbeeld doen alsof we niet weten hoe we dat moeten doen.
Maak een spreadsheet met de straal in B1 en bereken de oppervlakte in B2 met behulp van de vergelijking =pi()B1^2.
We kunnen de waarde in B1 handmatig aanpassen totdat B2 een waarde toont die dicht genoeg bij 50 ligt. Afhankelijk van hoe nauwkeurig we moet zijn, kan dit een praktische benadering zijn. Als we echter heel precies moeten zijn, zal het lang duren om de vereiste aanpassingen te maken. Eigenlijk is dit in wezen wat Oplosser doet. Het maakt aanpassingen aan waarden in bepaalde cellen en controleert de waarde in een doelcel:
- Selecteer DATA tab en Solver, om het Solver Parameters dialoogvenster te laden
-
Stel de cel Objectief in als het gebied, B2. Dit is de waarde die wordt gecontroleerd, andere cellen worden aangepast totdat deze de juiste waarde bereikt.
Image -
Selecteer de knop voor Waarde van: en stel een waarde van 50 in. Dit is de waarde die B2 zou moeten bereiken.
Image -
Voer in het vak met de titel By Change Variable Cells: de cel in die de straal bevat, B1.
Image -
Laat de andere opties zoals ze standaard zijn en selecteer Oplossen. De optimalisatie wordt uitgevoerd, de waarde van B1 wordt aangepast totdat B2 50 is en het dialoogvenster Solver Results wordt weergegeven.
Image -
Selecteer OK om de oplossing te behouden.
Image
Dit eenvoudige voorbeeld liet zien hoe de oplosser werkt. In dit geval hadden we de oplossing gemakkelijker op andere manieren kunnen krijgen. Vervolgens zullen we enkele voorbeelden bekijken waarin Oplosser oplossingen geeft die op een andere manier moeilijk te vinden zijn.
Een complex model aanpassen met behulp van de Excel Solver-invoegtoepassing
Excel heeft een ingebouwde functie om lineaire regressie uit te voeren, waarbij een rechte lijn door een set gegevens wordt geplaatst. Veel voorkomende niet-lineaire functies kunnen worden gelineariseerd, wat betekent dat lineaire regressie kan worden gebruikt om functies zoals exponentiëlen te passen. Voor complexere functies kan de Oplosser worden gebruikt om een 'kleinste kwadratenminimalisatie' uit te voeren. In dit voorbeeld zullen we overwegen een vergelijking van de vorm ax^b+cx^d aan te passen aan de onderstaande gegevens.
Dit omvat de volgende stappen:
- Rangschik de dataset met de x-waarden in kolom A en de y-waarden in kolom B.
- Maak de 4 coëfficiëntwaarden (a, b, c en d) ergens op de spreadsheet, deze kunnen willekeurige startwaarden krijgen.
-
Maak een kolom met aangepaste Y-waarden met behulp van een vergelijking van de vorm ax^b+cx^d die verwijst naar de coëfficiënten die in stap 2 zijn gemaakt en de x-waarden in kolom A. Merk op dat om de formule naar beneden te kopiëren de kolom, moeten de verwijzingen naar de coëfficiënten absoluut zijn, terwijl de verwijzingen naar x-waarden relatief moeten zijn.
Image -
Hoewel dit niet essentieel is, kunt u een visuele indicatie krijgen van hoe goed de vergelijking past door beide y-kolommen uit te zetten tegen de x-waarden op een enkel XY-spreidingsdiagram. Het is logisch om markeringen te gebruiken voor de originele datapunten, aangezien dit discrete waarden met ruis zijn, en om een lijn te gebruiken voor de gepaste vergelijking.
Image -
Vervolgens hebben we een manier nodig om het verschil tussen de gegevens en onze aangepaste vergelijking te kwantificeren. De standaardmanier om dit te doen is om de som van de gekwadrateerde verschillen te berekenen. In een derde kolom wordt voor elke rij de oorspronkelijke gegevenswaarde voor Y afgetrokken van de aangepaste vergelijkingswaarde en wordt het resultaat gekwadrateerd. Dus in D2 wordt de waarde gegeven door =(C2-B2)^2 De som van al deze gekwadrateerde waarden wordt dan berekend. Omdat de waarden gekwadrateerd zijn, kunnen ze alleen positief zijn.
Image -
U bent nu klaar om de optimalisatie uit te voeren met Oplosser. Er zijn vier coëfficiënten die moeten worden aangepast (a, b, c en d). Je hebt ook een enkele objectieve waarde om te minimaliseren, de som van de gekwadrateerde verschillen. Start de oplosser, zoals hierboven, en stel de oplosserparameters in om naar deze waarden te verwijzen, zoals hieronder weergegeven.
Image -
Deselecteer de optie om Onbeperkte variabelen niet-negatief te maken, dit zou alle coëfficiënten dwingen om positieve waarden aan te nemen.
Image -
Selecteer Solve en bekijk de resultaten. De grafiek wordt bijgewerkt en geeft een goede indicatie van de goedheid van de pasvorm. Als de oplosser bij de eerste poging niet goed past, kunt u proberen deze opnieuw uit te voeren. Als de pasvorm is verbeterd, probeer dan op te lossen vanuit de huidige waarden. Anders kunt u proberen om de pasvorm handmatig te verbeteren voordat u de oplossing oplost.
Image - Zodra een goede pasvorm is verkregen, kunt u de oplosser verlaten.
Een model iteratief oplossen
Soms is er een relatief eenvoudige vergelijking die een output geeft in termen van input. Wanneer we echter proberen het probleem om te keren, is het niet mogelijk om een eenvoudige oplossing te vinden. Het door een voertuig verbruikte vermogen wordt bijvoorbeeld bij benadering gegeven door P=av + bv^3 waarbij v de snelheid is, a een coëfficiënt voor de rolweerstand en b een coëfficiënt voor aerodynamische weerstand. Hoewel dit een vrij eenvoudige vergelijking is, is het niet eenvoudig om deze te herschikken om een vergelijking te geven van de snelheid die het voertuig zal bereiken bij een bepaald opgenomen vermogen. We kunnen echter Oplosser gebruiken om deze snelheid iteratief te vinden. Zoek bijvoorbeeld de snelheid die wordt bereikt met een opgenomen vermogen van 740 W.
-
Maak een eenvoudig rekenblad met de snelheid, de coëfficiënten a en b en het daaruit berekende vermogen.
Image -
Lanceer de Oplosser en voer de kracht, B5, in als het doel. Stel een objectieve waarde van 740 in en selecteer de snelheid, B2, als de variabele cellen die moeten worden gewijzigd. Selecteer solve om de oplossing te starten.
Image -
De oplosser past de waarde van de snelheid aan totdat het vermogen heel dicht bij 740 ligt, wat de snelheid levert die we nodig hebben.
Image - Het op deze manier oplossen van modellen kan vaak sneller en minder foutgevoelig zijn dan het omkeren van complexe modellen.
Het kan behoorlijk moeilijk zijn om de verschillende beschikbare opties in de oplosser te begrijpen. Als je moeite hebt om een verstandige oplossing te vinden, is het vaak handig om randvoorwaarden toe te passen op de veranderlijke cellen. Dit zijn grenswaarden waarboven ze niet mogen worden aangepast. In het vorige voorbeeld mag de snelheid bijvoorbeeld niet kleiner zijn dan nul en zou het ook mogelijk zijn om een bovengrens in te stellen. Dit zou een snelheid zijn waarvan u vrij zeker bent dat het voertuig niet sneller kan gaan dan. Als je grenzen kunt stellen aan de veranderlijke variabele cellen, dan werken ook andere, meer geavanceerde opties beter, zoals multistart. Dit zal een aantal verschillende oplossingen uitvoeren, beginnend bij verschillende beginwaarden voor variabelen.
Het kiezen van de oplossingsmethode kan ook moeilijk zijn. Simplex LP is alleen geschikt voor lineaire modellen, als het probleem niet lineair is, zal het mislukken met de melding dat niet aan deze voorwaarde is voldaan. De andere twee methoden zijn beide geschikt voor niet-lineaire methoden. GRG Nonlinear is de snelste, maar de oplossing kan sterk afhankelijk zijn van de initiële startomstandigheden. Het heeft de flexibiliteit dat het geen variabelen vereist om grenzen te stellen. De evolutionaire oplosser is vaak de meest betrouwbare, maar het vereist dat alle variabelen zowel boven- als ondergrenzen hebben, wat misschien moeilijk van tevoren uit te werken is.
De Excel Solver-invoegtoepassing is een zeer krachtig hulpmiddel dat op veel praktische problemen kan worden toegepast. Om volledig toegang te krijgen tot de kracht van Excel, probeer Oplosser te combineren met Excel-macro's.
