Hoe Excel's Goal Seek en Solver te gebruiken om onbekende variabelen op te lossen

Excel is zeer capabel als u alle gegevens hebt die u nodig hebt voor uw berekeningen.

Maar zou het niet mooi zijn als het kon? oplossen voor onbekende variabelen ?

Met Goal Seek en de Solver-invoegtoepassing kan dat. En we laten je zien hoe. Lees verder voor een volledige gids over het oplossen van een enkele cel met Goal Seek of een meer gecompliceerde vergelijking met Oplosser.

Doel zoeken gebruiken in Excel

Goal Seek is al ingebouwd in Excel. Het is onder de Gegevens tabblad, in de Wat als analyse menu:

Voor dit voorbeeld gebruiken we een heel eenvoudige reeks getallen. We hebben voor driekwart aan verkoopcijfers en een jaarlijks doel. We kunnen Goal Seek gebruiken om erachter te komen wat de cijfers in het vierde kwartaal moeten zijn om het doel te bereiken.

Zoals u kunt zien, is het huidige verkooptotaal 114.706 eenheden. Als we tegen het einde van het jaar 250.000 willen verkopen, hoeveel moeten we er dan in het vierde kwartaal verkopen? Excel's Goal Seek zal het ons vertellen.

Ga als volgt te werk om Goal Seek te gebruiken, stap voor stap:

Klik Gegevens > Wat-als-analyse > Doel zoeken . Je ziet dit venster:
Zet het 'is gelijk aan'-gedeelte van je vergelijking in de Stel cel in veld. Dit is het aantal dat Excel zal proberen te optimaliseren. In ons geval is dit het lopende totaal van onze verkoopaantallen in cel B5.
Typ uw doelwaarde in de Waarderen veld. We zoeken in totaal 250.000 verkochte eenheden, dus we zullen '250.000' in dit veld plaatsen.
Vertel Excel welke variabele moet worden opgelost in de Door van cel te veranderen veld. We willen zien wat onze omzet in het vierde kwartaal moet zijn. Dus we zullen Excel vertellen om op te lossen voor cel D2. Het ziet er zo uit als het klaar is om te gaan:
Raken Oke op te lossen voor je doel. Als het er goed uitziet, klik je gewoon op Oke . Excel laat je weten wanneer Goal Seek een oplossing heeft gevonden.
Klik Oke nogmaals en je ziet de waarde die je vergelijking oplost in de cel waarvoor je hebt gekozen Door van cel te veranderen .

In ons geval is de oplossing 135.294 eenheden. Dat hadden we natuurlijk net kunnen vinden door het lopende totaal van het jaardoel af te trekken. Maar Goal Seek kan ook worden gebruikt op een cel die: bevat al gegevens . En dat is handiger.

Merk op dat Excel onze eerdere gegevens overschrijft. Het is een goed idee om voer Goal Seek uit op een kopie van uw gegevens . Het is ook een goed idee om bij uw gekopieerde gegevens te noteren dat deze zijn gegenereerd met Doelzoeken. U wilt het niet verwarren met actuele, nauwkeurige gegevens.

hoe een flashstation te formatteren?

Dus Goal Seek is een handige Excel-functie , maar het is allemaal niet zo indrukwekkend. Laten we eens kijken naar een tool die veel interessanter is: de Oplosser-invoegtoepassing.

Wat doet de Oplosser van Excel?

Kortom, Oplosser is als een multivariate versie van Goal Seek . Er is één doelvariabele nodig en een aantal andere variabelen worden aangepast totdat het het gewenste antwoord krijgt.

Het kan een maximale waarde van een getal, een minimale waarde van een getal of een exact getal oplossen.

En het werkt binnen beperkingen, dus als een variabele niet kan worden gewijzigd, of alleen kan variëren binnen een bepaald bereik, houdt Oplosser daar rekening mee.

Het is een geweldige manier om meerdere onbekende variabelen in Excel op te lossen. Maar het vinden en gebruiken ervan is niet eenvoudig.

Laten we eens kijken naar het laden van de Oplosser-invoegtoepassing en vervolgens ingaan op het gebruik van Oplosser in Excel 2016.

Hoe de Oplosser-invoegtoepassing te laden

Excel heeft standaard geen Oplosser. Het is een invoegtoepassing, dus, net als andere krachtige Excel-functies, moet u deze eerst laden. Gelukkig staat het al op je computer.

ga naar Bestand > Opties > Invoegtoepassingen . Klik dan op Gaan naast Beheren: Excel-invoegtoepassingen .

Als deze vervolgkeuzelijst iets anders zegt dan 'Excel-invoegtoepassingen', moet u dit wijzigen:

In het resulterende venster ziet u een paar opties. Zorg ervoor dat het vakje naast Oplosser-invoegtoepassing is aangevinkt en druk op Oke .

Je ziet nu de Oplosser knop in de Analyse groep van de Gegevens tabblad:

Als je de al hebt gebruikt Toolpakket voor gegevensanalyse , ziet u de knop Gegevensanalyse. Zo niet, dan verschijnt Oplosser vanzelf.

Nu u de invoegtoepassing hebt geladen, gaan we eens kijken hoe u deze kunt gebruiken.

Oplosser gebruiken in Excel

Elke Oplosser-actie bestaat uit drie delen: het doel, de variabele cellen en de beperkingen. We zullen elk van de stappen doorlopen.

Klik Gegevens > Oplosser . U ziet het onderstaande venster Oplosserparameters. (Als u de knop Oplosser niet ziet, raadpleegt u het vorige gedeelte over het laden van de invoegtoepassing Oplosser.)
Stel uw celdoel in en vertel Excel uw doel. Het doel staat bovenaan het Oplosser-venster en bestaat uit twee delen: de doelcel en een keuze uit maximaliseren, minimaliseren of een specifieke waarde. Als u selecteert: Max , zal Excel uw variabelen aanpassen om het grootst mogelijke aantal in uw objectieve cel te krijgen. Min is het tegenovergestelde: Oplosser zal het doelgetal minimaliseren. Waarde van laat u een specifiek nummer specificeren waarnaar de Oplosser moet zoeken.
Kies de variabele cellen die Excel kan wijzigen. De variabele cellen worden ingesteld met de Door variabele cellen te wijzigen veld. Klik op de pijl naast het veld en klik en sleep om de cellen te selecteren waarmee Oplosser moet werken. Let op: dit zijn alle cellen dat kan variëren. Als u niet wilt dat een cel verandert, selecteert u deze niet.
Stel beperkingen in voor meerdere of individuele variabelen. Ten slotte komen we bij de beperkingen. Dit is waar Oplosser echt krachtig is. In plaats van een van de variabele cellen te wijzigen in een willekeurig getal, kunt u beperkingen opgeven waaraan moet worden voldaan. Zie het gedeelte over het instellen van beperkingen hieronder voor meer informatie.
Zodra al deze informatie aanwezig is, drukt u op Oplossen om je antwoord te krijgen. Excel zal uw gegevens bijwerken om de nieuwe variabelen op te nemen (daarom raden we u aan eerst een kopie van uw gegevens te maken).

U kunt ook rapporten genereren, die we kort zullen bekijken in ons Oplosser-voorbeeld hieronder.

Beperkingen instellen in Oplosser

U kunt Excel vertellen dat één variabele groter moet zijn dan 200. Wanneer u verschillende variabelewaarden probeert, gaat Excel niet onder de 201 met die specifieke variabele.

Om een beperking toe te voegen, klikt u op de Toevoegen knop naast de lijst met beperkingen. Je krijgt een nieuw venster. Selecteer de cel (of cellen) die moeten worden beperkt in de Celverwijzing veld en kies vervolgens een operator.

Dit zijn de beschikbare operators:

<= (minder dan of gelijk aan)
= (gelijk aan)
=> (groter dan of gelijk aan)
int (moet een geheel getal zijn)
ben (moet 1 of 0 zijn)
Allemaal anders

Allemaal anders is een beetje verwarrend. Het specificeert dat elke cel in het bereik waarvoor u selecteert Celverwijzing moet een ander nummer zijn. Maar het specificeert ook dat ze tussen 1 en het aantal cellen moeten liggen. Dus als je drie cellen hebt, krijg je de nummers 1, 2 en 3 (maar niet noodzakelijk in die volgorde)

Voeg ten slotte de waarde voor de beperking toe.

Het is belangrijk om te onthouden dat je kunt selecteer meerdere cellen voor celreferentie. Als u bijvoorbeeld wilt dat zes variabelen waarden hoger dan 10 hebben, kunt u ze allemaal selecteren en Oplosser vertellen dat ze groter dan of gelijk moeten zijn aan 11. U hoeft niet voor elke cel een beperking toe te voegen.

U kunt ook het selectievakje in het hoofdvenster van Oplosser gebruiken om ervoor te zorgen dat alle waarden waarvoor u geen beperkingen hebt opgegeven, niet-negatief zijn. Als u wilt dat uw variabelen negatief worden, schakelt u dit selectievakje uit.

Een voorbeeld van een oplosser

Om te zien hoe dit allemaal werkt, gebruiken we de Oplosser-invoegtoepassing om een snelle berekening te maken. Dit zijn de gegevens waarmee we beginnen:

Daarin hebben we vijf verschillende banen, die elk een ander tarief betalen. We hebben ook het aantal uren dat een theoretische werknemer in een bepaalde week aan elk van die banen heeft gewerkt. We kunnen de Oplosser-invoegtoepassing gebruiken om erachter te komen hoe u het totale loon kunt maximaliseren terwijl u bepaalde variabelen binnen bepaalde beperkingen houdt.

Dit zijn de beperkingen die we zullen gebruiken:

Geen banen onder de vier uur kan komen.
Taak 2 moet zijn langer dan acht uur .
Taak 5 moet zijn minder dan elf uur .
Het totaal aantal gewerkte uren moet gelijk aan 40 .

Het kan handig zijn om uw beperkingen op deze manier op te schrijven voordat u Oplosser gebruikt.

Hier is hoe we dat zouden instellen in Oplosser:

Merk eerst op dat Ik heb een kopie van de tabel gemaakt dus we overschrijven niet de originele, die onze huidige werkuren bevat.

En ten tweede, zie dat de waarden in de groter-dan- en kleiner-dan-beperkingen zijn: een hoger of lager dan wat ik hierboven vermeldde. Dat komt omdat er geen groter-dan- of minder-dan-opties zijn. Er zijn alleen groter-dan-of-gelijk-aan en kleiner-dan-of-gelijk-aan.

Laten we slaan Oplossen en kijk wat er gebeurt.

Oplosser heeft een oplossing gevonden! Zoals u aan de linkerkant van het bovenstaande venster kunt zien, zijn onze inkomsten met $ 130 gestegen. En aan alle beperkingen is voldaan.

windows xp virtuele machine op windows 10

Zorg ervoor dat: om de nieuwe waarden te behouden: Oplosser-oplossing behouden is gecontroleerd en hit Oke .

Als u echter meer informatie wilt, kunt u aan de rechterkant van het venster een rapport selecteren. Selecteer alle rapporten die u wilt, vertel Excel of u ze wilt weergeven (ik raad het aan) en druk op Oke .

De rapporten worden gegenereerd op nieuwe bladen in uw werkmap en geven u informatie over het proces dat de Oplosser-invoegtoepassing heeft doorlopen om uw antwoord te krijgen.

In ons geval zijn de rapporten niet erg spannend, en er is niet heel veel interessante informatie. Maar als u een meer gecompliceerde Oplosser-vergelijking uitvoert, vindt u wellicht wat nuttige rapportage-informatie in deze nieuwe werkbladen. Klik gewoon op de + knop aan de zijkant van een rapport voor meer informatie:

Oplosser geavanceerde opties

Als u niet veel weet over statistieken, kunt u de geavanceerde opties van Oplosser negeren en het gewoon uitvoeren zoals het is. Maar als u grote, complexe berekeningen uitvoert, wilt u daar misschien naar kijken.

De meest voor de hand liggende is de oplossingsmethode:

U kunt kiezen tussen GRG Nonlinear, Simplex LP en Evolutionary. Excel biedt een eenvoudige uitleg over wanneer u ze allemaal moet gebruiken. Een betere uitleg vereist enige kennis van statistiek en regressie.

Om extra instellingen aan te passen, drukt u op de Opties knop. U kunt Excel vertellen over de optimaliteit van gehele getallen, rekentijdbeperkingen instellen (handig voor enorme datasets) en aanpassen hoe de GRG en Evolutionaire oplossingsmethoden hun berekeningen maken.

Nogmaals, als je niet weet wat dit betekent, maak je er dan geen zorgen over. Als u meer wilt weten over welke oplossingsmethode u moet gebruiken, heeft Engineer Excel een: goed artikel dat het voor je uitlegt . Als u maximale nauwkeurigheid wilt, is Evolutionary waarschijnlijk een goede manier om te gaan. Houd er alleen rekening mee dat het lang zal duren.

Doel zoeken en oplossen: Excel naar een hoger niveau tillen

Nu u vertrouwd bent met de basisprincipes van het oplossen van onbekende variabelen in Excel, gaat er een geheel nieuwe wereld van spreadsheetberekening voor u open.

Goal Seek kan u helpen tijd te besparen door sommige berekeningen sneller uit te voeren, en Solver voegt een enorme hoeveelheid kracht toe aan De rekenvaardigheden van Excel .

Het is gewoon een kwestie van je op je gemak bij hen voelen. Hoe meer je ze gebruikt, hoe nuttiger ze zullen worden.

Gebruik je Goal Seek of Solver in je spreadsheets? Welke andere tips kunt u geven om er de beste antwoorden uit te halen? Deel uw mening in de reacties hieronder!

Deel Deel Tweeten E-mail 5 tips om uw VirtualBox Linux-machines een boost te geven

Moe van de slechte prestaties van virtuele machines? Dit is wat u moet doen om uw VirtualBox-prestaties te verbeteren.

Lees volgende Gerelateerde onderwerpen

productiviteit
Spreadsheet
Microsoft Excel
Microsoft Office-tips

Over de auteur Dan Albright(506 artikelen gepubliceerd)

Dann is een contentstrategie- en marketingconsultant die bedrijven helpt bij het genereren van vraag en leads. Hij blogt ook over strategie en contentmarketing op dannalbright.com.

Meer van Dann Albright

Abonneer op onze nieuwsbrief

Word lid van onze nieuwsbrief voor technische tips, recensies, gratis e-boeken en exclusieve deals!

Klik hier om je te abonneren