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 oplossen 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 staat onder het tabblad Gegevens in het menu Wat-als-analyse :
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 eind 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:
- Klik op Gegevens> Wat als analyse> Doel zoeken . Je ziet dit venster:
- Zet het "gelijken" deel van uw vergelijking in het veld Cel instellen . Dit is het nummer dat Excel probeert te optimaliseren. In ons geval is dit het lopend totaal van onze verkoopaantallen in cel B5.
- Typ uw doelwaarde in het veld Aan waarde . We zijn op zoek naar in totaal 250.000 verkochte eenheden, dus we zullen "250.000" in dit veld plaatsen.
- Vertel Excel welke variabele moet worden opgelost in het veld Door cel te veranderen . 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:
- Druk op OK om op te lossen voor je doel. Als het er goed uitziet, druk je gewoon op OK . Excel laat je weten wanneer Goal Seek een oplossing heeft gevonden.
- Klik nogmaals op OK en u ziet de waarde waarmee uw vergelijking wordt opgelost in de cel waarvoor u hebt gekozen. 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 Doel zoeken kan ook worden gebruikt op een cel die al gegevens bevat . En dat is nuttiger.
Merk op dat Excel onze vorige gegevens overschrijft. Het is een goed idee om Goal Seek uit te voeren op 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 de oplosser van Excel?
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 één variabele niet kan worden gewijzigd of alleen binnen een bepaald bereik kan variëren, houdt Solver daar rekening mee.
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 eens kijken naar het laden van de Oplosser-invoegtoepassing en vervolgens inlossen over het gebruik van Oplosser in Excel 2016.
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 Go naast Beheren: Excel-invoegtoepassingen .
Als deze vervolgkeuzelijst iets anders dan 'Excel-invoegtoepassingen' zegt, moet u deze wijzigen:
In het resulterende venster ziet u enkele opties. Zorg ervoor dat het vakje naast invoegtoepassing Oplosser is aangevinkt en klik op OK .
U ziet nu de knop Oplosser in de groep Analyse op het tabblad Gegevens :
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.
- Klik op 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.)
- 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 Max selecteert, past Excel uw variabelen aan om het grootste aantal mogelijk te maken in uw doelcel. Min is het tegenovergestelde: Oplosser minimaliseert het objectiefnummer. Waarde van laat u een specifiek nummer specificeren waarnaar Solver zoekt. - Kies de variabele cellen die Excel kan wijzigen. De variabele cellen worden ingesteld met het veld Door veranderende variabele cellen . Klik op de pijl naast het veld en klik en sleep om de cellen te selecteren waarmee Oplosser moet werken. Merk op dat dit alle cellen zijn die kunnen variëren. Als u niet wilt dat een cel verandert, selecteer deze dan niet.
- 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 naar een willekeurig aantal te veranderen, kunt u beperkingen specificeren waaraan moet worden voldaan. Zie het gedeelte over het instellen van onderstaande beperkingen voor meer informatie.
- Zodra al deze informatie aanwezig is, klikt u op Oplossen om uw 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 de knop Toevoegen naast de beperkingslijst. Je krijgt een nieuw venster. Selecteer de cel (of cellen) die moet worden beperkt in het veld Celreferentie en kies vervolgens een operator.
Hier zijn de beschikbare operators:
- <= (kleiner dan of gelijk aan)
- = (gelijk aan)
- => (groter dan of gelijk aan)
- int (moet een geheel getal zijn)
- bin (moet 1 of 0 zijn)
- Allemaal anders
AllDifferent is een beetje verwarrend. Het geeft aan dat elke cel in het bereik die u selecteert voor celverwijzing een ander nummer moet 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 u meerdere cellen voor celverwijzing kunt selecteren . 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 voor elke cel geen 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 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 enkele taak kan minder dan vier uur bedragen.
- Taak 2 moet langer zijn dan acht uur .
- Taak 5 moet minder dan elf uur zijn .
- Het totaal aantal gewerkte uren moet gelijk zijn 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 allereerst op dat ik een kopie van de tabel heb gemaakt, zodat we de originele niet overschrijven, die onze huidige werkuren bevat.
En ten tweede, zie dat de waarden in de groter dan en minder dan beperkingen één hoger of lager zijn 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 Oplossen en zien 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.
Als u de nieuwe waarden wilt behouden, zorgt u ervoor dat Keep Solver Solution is aangevinkt en drukt u op 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 werkbladen 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 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 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 de knop Opties . U kunt Excel vertellen over integer-optimaliteit, calculatietijdbeperkingen instellen (handig voor enorme datasets), en aanpassen hoe de GRG en Evolutionaire oplossingsmethoden worden toegepast bij 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, is er een geheel nieuwe wereld van spreadsheetberekening 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 je mening in de comments hieronder!