Goal Seek is een van Excel's What-if-analysetools waarmee u de juiste invoerwaarde van een formule kunt vinden om de gewenste uitvoer te krijgen. Het laat zien hoe de ene waarde in een formule een andere beïnvloedt. Met andere woorden, als u een doelwaarde heeft die u wilt bereiken, kunt u doel zoeken gebruiken om de juiste invoerwaarde te vinden om deze te krijgen.
Laten we bijvoorbeeld zeggen dat je in totaal 75 punten hebt behaald voor een vak en dat je minimaal 90 nodig hebt om een S-cijfer voor dat vak te behalen. Gelukkig heb je nog een laatste test die je kan helpen om je gemiddelde score te verhogen. Je kunt Goal Seek gebruiken om erachter te komen hoeveel score je nodig hebt op die laatste test om een S-cijfer te krijgen.
Goal Seek gebruikt een trial-and-error-methode om het probleem terug te vinden door gissingen in te voeren totdat het tot het juiste resultaat komt. Goal Seek kan de beste invoerwaarde voor de formule vinden in een kwestie van seconden, wat veel tijd zou kosten om handmatig te achterhalen. In dit artikel laten we u met enkele voorbeelden zien hoe u de tool Doel zoeken in Excel gebruikt.
Onderdelen van de doelzoekfunctie
De doelzoekfunctie bestaat uit drie parameters, namelijk:
- cel instellen – Dit is de cel waarin de formule wordt ingevoerd. Het specificeert de cel waarin u de gewenste uitvoer wilt.
- Waarderen – Dit is de doelwaarde/gewenste waarde die u wilt als resultaat van de doelzoekactie.
- Door van cel te veranderen – Dit specificeert de cel waarvan de waarde moet worden aangepast om de gewenste uitvoer te krijgen.
Doel zoeken in Excel gebruiken: voorbeeld 1
Om aan de hand van een eenvoudig voorbeeld te demonstreren hoe het werkt, stelt u zich voor dat u een fruitkraam runt. In de onderstaande schermafbeelding laat cel B11 (hieronder) zien hoeveel het u heeft gekost om fruit voor uw kraam te kopen en cel B12 toont uw totale inkomsten bij het verkopen van dat fruit. En cel B13 toont het percentage van uw winst (20%).
Als u uw winst wilt verhogen tot '30%', maar u kunt uw investering niet verhogen, moet u uw inkomsten verhogen om winst te maken. Maar tot hoeveel? Doel zoeken zal u helpen het te vinden.
Je gebruikt de volgende formule in cel B13 om het winstpercentage te berekenen:
=(B12-B11)/B12Nu wilt u de winstmarge berekenen zodat uw winstpercentage 30% is. Dit doe je met Goal Seek in Excel.
Het eerste dat u moet doen, is de cel selecteren waarvan u de waarde wilt aanpassen. Elke keer dat u Doel zoeken gebruikt, moet u een cel selecteren die een formule of functie bevat. In ons geval selecteren we cel B13 omdat deze de formule bevat om het percentage te berekenen.
Ga vervolgens naar het tabblad 'Gegevens', klik op de knop 'Wat als analyse' in de groep Prognose en selecteer 'Doel zoeken'.
Het dialoogvenster Doel zoeken verschijnt met 3 velden:
- cel instellen – Voer de celverwijzing in die de formule bevat (B13). Dit is de cel die de gewenste output zal hebben.
- Waarderen – Voer het gewenste resultaat in dat u probeert te bereiken (30%).
- Door van cel te veranderen – Voer de celverwijzing in voor de invoerwaarde die u zou willen wijzigen (B12) om tot het gewenste resultaat te komen. Klik eenvoudig op de cel of voer de celverwijzing handmatig in. Wanneer u de cel selecteert, voegt Excel het '$'-teken toe vóór de kolomletter en het rijnummer om er een absolute cel van te maken.
Als u klaar bent, klikt u op 'OK' om het te testen.
Er verschijnt dan een dialoogvenster 'Doel zoeken Status' en u wordt geïnformeerd als er een oplossing is gevonden, zoals hieronder wordt weergegeven. Als er een oplossing is gevonden, wordt de invoerwaarde in de ‘wisselcel (B12)’ aangepast naar een nieuwe waarde. Dit is wat we willen. Dus in dit voorbeeld heeft de analyse vastgesteld dat, om uw 30% winstdoelstelling te bereiken, u een omzet van $ 1635,5 (B12) moet behalen.
Klik op 'OK' en Excel zal de celwaarden wijzigen of klik op 'Annuleren' om de oplossing te negeren en de oorspronkelijke waarde te herstellen.
De invoerwaarde (1635.5) in cel B12 is wat we hebben ontdekt met behulp van Doel zoeken om ons doel (30%).
Dingen om te onthouden bij het gebruik van Goal Seek
- De Set Cell moet altijd een formule bevatten die afhankelijk is van de cel ‘Door van cel te veranderen’.
- U kunt Doelzoeken slechts voor één celinvoerwaarde tegelijk gebruiken
- De ‘Door cel wijzigen’ moet een waarde bevatten en geen formule.
- Als Goal Seek de juiste oplossing niet kan vinden, toont het de dichtstbijzijnde waarde die het kan produceren en vertelt het je dat het bericht 'Doel zoeken mogelijk geen oplossing heeft gevonden'.
Wat te doen als Excel Goal Seek niet werkt?
Als u er echter zeker van bent dat er een oplossing is, zijn er een paar dingen die u kunt proberen om dit probleem op te lossen.
Controleer de parameters en waarden van Doel zoeken
Er zijn twee dingen die u moet controleren: controleer eerst of de parameter 'Set cell' verwijst naar de formulecel. Ten tweede, zorg ervoor dat de formulecel (Set cell) direct of indirect afhankelijk is van de veranderende cel.
De iteratie-instellingen aanpassen
In de Excel-instellingen kunt u het aantal mogelijke pogingen wijzigen dat Excel kan doen om de juiste oplossing te vinden, evenals de nauwkeurigheid ervan.
Om de instellingen voor de iteratieberekening te wijzigen, klikt u op 'Bestand' in de lijst met tabbladen. Klik dan onderaan op ‘Opties’.
Klik in het venster Excel-opties op 'Formules' in het linkerdeelvenster.
Wijzig deze instellingen in het gedeelte 'Berekeningsopties':
- Maximale iteraties - Het geeft het aantal mogelijke oplossingen aan dat Excel zal berekenen; hoe hoger het getal, hoe meer iteraties het kan uitvoeren. Als u bijvoorbeeld 'Maximum iteraties' instelt op 150, test Excel 150 mogelijke oplossingen.
- Maximale verandering - Het geeft de nauwkeurigheid van de resultaten aan; het kleinere getal geeft een hogere nauwkeurigheid. Als uw invoercelwaarde bijvoorbeeld gelijk is aan '0' maar Doel zoeken stopt met berekenen bij '0,001', zou het probleem moeten worden opgelost door deze te wijzigen in '0,0001'.
Verhoog de waarde voor 'Maximum iteraties' als u wilt dat Excel meer mogelijke oplossingen test en verlaag de waarde voor 'Maximale wijziging' als u een nauwkeuriger resultaat wilt.
De onderstaande schermafbeelding toont de standaardwaarde:
Geen circulaire verwijzingen
Wanneer een formule terugverwijst naar zijn eigen cel, wordt dit een kringverwijzing genoemd. Als u wilt dat Excel Goal Seek correct werkt, mogen formules geen kringverwijzing gebruiken.
Excel doel zoeken voorbeeld 2
Stel dat u geld leent van '$ 25.000' van iemand. Ze lenen u het geld tegen een rentepercentage van 7% per maand voor een periode van 20 maanden, wat neerkomt op de terugbetaling van '$ 1327' per maand. Maar u kunt het zich alleen veroorloven om 20 maanden lang '$ 1.000' per maand te betalen met een rente van 7%. Goal Seek kan u helpen het geleende bedrag te vinden dat een maandelijkse betaling (EMI) van '$ 1000' oplevert.
Voer de drie invoervariabelen in die u nodig hebt om uw PMT-berekening te berekenen, d.w.z. rente van 7%, looptijd van 20 maanden en hoofdsom van $ 25.000.
Voer de volgende PMT-formule in cel B5 in, waarmee u een EMI-bedrag van $ 1.327,97 krijgt.
De syntaxis van de PMT-functie:
=PMT (rentevoet/12, termijn, hoofdsom)De Formule:
=PMT(B3/12,B4,-B2)
Selecteer cel B5 (formulecel) en ga naar Gegevens -> Wat als analyse -> Doel zoeken.
Gebruik deze parameters in het venster 'Doel zoeken':
- Cel instellen – B5 (de cel die de formule bevat die EMI berekent)
- Waarderen – 1000 (het formuleresultaat/doel dat u zoekt)
- Door van cel te veranderen – B2 (dit is het leenbedrag dat u wilt wijzigen om de doelwaarde te behalen)
Druk vervolgens op 'OK' om de gevonden oplossing te behouden of op 'Annuleren' om deze te verwijderen.
De analyse vertelt u dat het maximale bedrag dat u kunt lenen $ 18825 is als u uw budget wilt overschrijden.
Zo gebruik je Goal Seek in Excel.