Het gebruik van de doelzoeker en oplosser van Excel om onbekende variabelen op te lossen

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

Maar zou het niet leuk zijn als het zou kunnen op te lossen voor onbekende variabelen?

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

Hoe te gebruiken Doel zoeken in Excel

Doel zoeken is al in Excel ingebouwd. Het is onder de Gegevens tab, in de Wat als analyse menu:

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

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

U gebruikt Goal Seek als volgt, stap voor stap:

1. Klik Gegevens> Wat als analyse> Doel zoeken. Je ziet dit venster:
   
2. Zet de “is gelijk aan” deel van uw vergelijking in de Stel de cel in veld. Dit is het nummer dat Excel probeert te optimaliseren. In ons geval is dit het lopend totaal van onze verkoopaantallen in cel B5.
3. Typ uw doelwaarde in de Waarderen veld. We zijn op zoek naar een totaal van 250.000 verkochte eenheden, dus we zullen zetten “250.000” in dit veld.
4. Vertel Excel welke variabele moet worden opgelost in de Door van cel te veranderen veld. We willen zien wat onze verkopen in het vierde kwartaal moeten zijn. Dus we vertellen Excel om op te lossen voor cel D2. Het ziet er als volgt uit wanneer het klaar is om te gaan:
   
5. Raken OK om je doel te bereiken. Als het er goed uitziet, gewoon raken OK. Excel laat je weten wanneer Goal Seek een oplossing heeft gevonden.
   
6. Klik OK opnieuw en u zult de waarde zien die uw vergelijking oplost in de cel waarvoor u koos Door van cel te veranderen.
   

In ons geval is de oplossing 135.294 eenheden. Natuurlijk hadden we dat net kunnen vinden door het lopende totaal af te trekken van het jaarlijkse doel. Maar doelzoeken kan ook op een cel worden gebruikt bevat al gegevens. En dat is nuttiger.

Merk op dat Excel onze vorige gegevens overschrijft. Het is een goed idee om voer doel uit Zoek een kopie van uw gegevens. Het is ook een goed idee om een ​​notitie te maken van uw gekopieerde gegevens dat deze zijn gegenereerd met behulp van Doel zoeken. U wilt het niet verwarren met actuele, nauwkeurige gegevens.

Goal Seek is dus een nuttige Excel-functie 16 Excel-formules die u zullen helpen om problemen met het echte leven op te lossen 16 Excel-formules die u kunnen helpen om problemen met het echte leven op te lossen De juiste tool is het halve werk. Excel kan berekeningen verwerken en gegevens sneller verwerken dan u uw rekenmachine kunt vinden. We laten u de belangrijkste Excel-formules zien en laten zien hoe u ze kunt gebruiken. Lees meer, maar het is niet zo indrukwekkend. Laten we eens kijken naar een tool die veel interessanter is: de invoegtoepassing Oplosser.

Wat doet Excel's Solver?

Kortom, Solver is als een multivariate versie van Goal Seek. Het kost één doelvariabele en past een aantal andere variabelen aan totdat het het gewenste antwoord krijgt.

Het kan een maximumwaarde van een getal, een minimumwaarde van een getal of een exact getal oplossen.

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

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

Laten we een kijkje nemen bij het laden van de Oplosser-invoegtoepassing en vervolgens kijken hoe Solver in Excel 2016 wordt gebruikt.

De invoegtoepassing Oplosser laden

Excel heeft standaard geen Oplosser. Het is een invoegtoepassing dus, zoals andere krachtige Excel-functies Power Up Excel met 10 invoegtoepassingen om te verwerken, analyseren en visualiseren als een professional Start Excel met 10 invoegtoepassingen om gegevens te verwerken, analyseren en visualiseren als een pro-vanille Excel is geweldig, maar je kunt het nog krachtiger maken met invoegtoepassingen. Welke gegevens u ook moet verwerken, de kans is groot dat iemand hiervoor een Excel-app heeft gemaakt. Hier is een selectie. Lees Meer, je moet het eerst laden. Gelukkig staat het al op je computer.

Ga naar Bestand> Opties> Invoegtoepassingen. Klik vervolgens op Gaan naast Beheer: Excel-invoegtoepassingen.

Als deze vervolgkeuzelijst iets anders zegt dan “Excel-invoegtoepassingen,” je moet het veranderen:

In het resulterende venster ziet u enkele opties. Zorg ervoor dat het vakje naast Oplosser invoegtoepassing wordt gecontroleerd en geraakt OK.

Je ziet nu het solver knop in de Analyse groep van de Gegevens tab:

Als u al gebruik hebt gemaakt van de Data Analysis Toolpak Basisanalyses in Excel uitvoeren Basisgegevensanalyse in Excel Excel is niet bedoeld voor gegevensanalyse, maar het kan nog steeds statistieken verwerken. We laten u zien hoe u de Data Analysis Toolpak-invoegtoepassing gebruikt om Excel-statistieken uit te voeren. Meer lezen, u ziet de knop Gegevensanalyse. Zo niet, dan verschijnt Oplosser vanzelf.

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

Hoe Solver in Excel te gebruiken

Er zijn drie onderdelen voor elke Oplosser-actie: het doel, de variabele cellen en de beperkingen. We zullen door elk van de stappen lopen.

1. Klik Gegevens> Oplosser. U zult het venster Parameters van Oplosser hieronder zien. (Zie de vorige sectie over het laden van de Oplosser-invoegtoepassing als u de oplosser niet ziet.)
   
2. Stel uw mobiele doel in en vertel Excel uw doel. Het doel staat bovenaan het venster Oplosser en bestaat uit twee delen: de doelcel en een keuze uit maximaliseren, minimaliseren of een specifieke waarde.
   
   Als u selecteert Max, Excel past uw variabelen aan om het grootst mogelijke aantal in uw doelcel te krijgen. min is het tegenovergestelde: Oplosser zal het objectiefnummer minimaliseren. Waarde van laat je een specifiek nummer specificeren waar Solver naar moet zoeken.
3. Kies de variabele cellen die Excel kan wijzigen. De variabele cellen worden ingesteld met de Door veranderlijke cellen te veranderen veld. Klik op de pijl naast het veld en klik en sleep om de cellen te selecteren waarmee Oplosser moet werken. Merk op dat dit zijn alle cellen dat kan variëren. Als u niet wilt dat een cel verandert, selecteer deze dan niet.
   
4. Beperkingen instellen voor meerdere of afzonderlijke variabelen. Ten slotte komen we aan de beperkingen. Dit is waar Solver echt krachtig is. In plaats van een van de variabele cellen te veranderen in elk gewenst aantal, kunt u beperkingen specificeren waaraan moet worden voldaan. Zie het gedeelte over het instellen van onderstaande beperkingen voor meer informatie.
5. Zodra al deze informatie aanwezig is, druk op Oplossen om je antwoord te krijgen. Excel werkt uw gegevens bij met de nieuwe variabelen (daarom raden we aan dat u eerst een kopie van uw gegevens maakt).

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

Beperkingen instellen in Oplosser

U zou in Excel kunnen zeggen dat één variabele groter moet zijn dan 200. Bij het proberen van verschillende variabelewaarden, zal Excel niet onder de 201 gaan met die bepaalde variabele.

Om een ​​beperking toe te voegen, klikt u op Toevoegen knop naast de beperkingslijst. Je krijgt een nieuw venster. Selecteer de cel (of cellen) die moet worden beperkt in de Celreferentie veld en kies vervolgens een operator.

Hier zijn de beschikbare operators:

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

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

Voeg ten slotte de waarde voor de beperking toe.

Het is belangrijk om te onthouden dat dat kan selecteer meerdere cellen voor celverwijzing. Als u bijvoorbeeld wilt dat zes variabelen waarden van meer dan 10 hebben, kunt u ze allemaal selecteren en de Oplosser vertellen dat ze groter dan of gelijk aan 11 moeten zijn. U hoeft geen beperking voor elke cel 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 Oplosser Voorbeeld

Om te zien hoe dit allemaal werkt, gebruiken we de Oplosser-invoegtoepassing om snel een berekening uit te voeren. 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 elk van die banen in een bepaalde week heeft gewerkt. We kunnen de Oplosser van de Oplosser gebruiken om uit te zoeken hoe de totale beloning kan worden gemaximaliseerd terwijl bepaalde variabelen binnen bepaalde beperkingen blijven.

Dit zijn de beperkingen die we zullen gebruiken:

• Geen banen kan onder de vier uur vallen.
• Job 2 moet zijn meer dan acht uur.
• Job 5 moet zijn minder dan elf uur.
• Het totaal aantal gewerkte uren moet zijn gelijk aan 40.

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

Dit is hoe we dat in Solver zouden instellen:

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

En ten tweede, zie dat de waarden in de groter-dan en minder-dan beperkingen zijn één hoger of lager dan wat ik hierboven heb genoemd. Dat komt omdat er geen grotere - dan of minder - dan opties zijn. Er zijn alleen groter-dan-of-gelijk-aan en minder-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, is onze winst met $ 130 gestegen. En aan alle beperkingen is voldaan.

Zorg ervoor dat u de nieuwe waarden bewaart Keep Solver Solution wordt gecontroleerd en geraakt OK.

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

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

In ons geval zijn de rapporten niet erg spannend, en er is niet veel interessante informatie daar. Maar als u een meer gecompliceerde Oplosser-vergelijking uitvoert, vindt u mogelijk enkele 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 statistiek, kunt u de geavanceerde opties van Solver negeren en het gewoon uitvoeren zoals het is. Maar als u grote, complexe berekeningen uitvoert, wilt u misschien ernaar kijken.

De meest voor de hand liggende is de oplossingsmethode:

U kunt kiezen tussen GRG Niet-lineair, Simplex LP en Evolutionair. Excel biedt een eenvoudige uitleg over wanneer u elk apparaat zou moeten gebruiken. Een betere uitleg vereist enige kennis van statistieken. Leer gratis statistieken met deze 6 bronnen Leer gratis statistieken met deze 6 bronnen Statistieken hebben de reputatie van een onderwerp dat moeilijk te begrijpen is. Maar door te leren van de juiste bron kunt u de enquêteresultaten, verkiezingsrapporten en uw klassestoewijzingen in een mum van tijd begrijpen. Meer lezen en regressie.

Om extra instellingen aan te passen, drukt u gewoon op opties knop. U kunt Excel vertellen over integer-optimaliteit, calculatietijdbeperkingen instellen (handig voor enorme datasets), en aanpassen hoe de GRG en Evolutionaire oplossingsmethodes gaan over het maken van hun berekeningen.

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 u op schrift stelt. Als je maximale nauwkeurigheid wilt, is Evolutionary waarschijnlijk een goede manier om te gaan. Houd er rekening mee dat dit lang zal duren.

Doel zoeken en oplossen: Excel naar het volgende niveau brengen

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

Doel zoeken kan u helpen tijd te besparen door een aantal berekeningen sneller te maken, en Solver voegt een enorme hoeveelheid kracht toe aan Excel's berekeningsvaardigheden Berekening van basisstatistieken in Excel: een beginnershandleiding Berekening van basisstatistieken in Excel: een beginnershandleiding Microsoft Excel kan statistieken doen! U kunt percentages, gemiddelden, standaarddeviatie, standaardfout en T-tests van studenten berekenen. Lees verder .

Het is gewoon een kwestie van vertrouwd raken met hen. Hoe meer u ze gebruikt, hoe nuttiger ze worden.

Gebruik je Doel zoeken of Oplosser in je spreadsheets? Welke andere tips kunt u geven om de beste antwoorden te krijgen? Deel uw mening in de reacties hieronder!

Ontdek meer over: Microsoft Excel, Microsoft Office Tips, Spreadsheet.