Doelzoekfunctie in Excel: complete handleiding en voorbeelden

La Doelzoekfunctie in Excel Het is een van die tools die je niet opmerkt totdat je hem ontdekt, en dan kun je je niet meer voorstellen hoe je ooit zonder hebt geleefd. Je kunt Excel ermee vertellen: "Dit is wat ik wil bereiken", en het programma berekent zelf de waarde die een specifieke cel moet hebben om de berekeningen te laten werken.

In plaats van koortsachtig getallen uit te proberen totdat je het juiste getal vindt, kun je Vind een doel, zodat Excel het zware werk kan doenHet is uiterst nuttig in de financiën (leningen, investeringen, persoonlijke budgetten), in academische zaken (cijfers, gemiddelden, beurzen) en bij elke berekening waarbij u een formule hebt opgesteld en een onbekende over het hoofd ziet.

Wat is Doelzoeken in Excel precies?

Buscar Objetivo Het maakt deel uit van de gereedschappen van Analyse Wat als… in Excel. In plaats van jezelf af te vragen: "Als ik deze gegevens wijzig, wat gebeurt er dan met het resultaat?", draai je de aanpak om: je vertelt Excel welk resultaat je wilt verkrijgen en vraagt ​​het om te berekenen welke waarde een van de cellen in de formule moet aannemen.

Met andere woorden: de tool werkt “Het tegenovergestelde” van wat je normaal zou doenJe weet de uiteindelijke waarde die je nodig hebt al en laat Excel de invoerwaarde vinden die je formule dat resultaat oplevert. Wiskundig gezien is het vergelijkbaar met het oplossen van een vergelijking met één onbekende, zonder deze handmatig te hoeven isoleren.

Deze functie is beschikbaar in Excel en ook in andere office-suites zoals ALLEEN OFFICE-documentenwaar het in hun spreadsheets is opgenomen om hetzelfde type scenario-analyse te bieden. In alle gevallen is de filosofie hetzelfde: Stel een doel, kies de variabele en laat het programma itereren. totdat er een passende waarde is gevonden.

Enkele typische situaties waarin het gebruik van Doelzoeken erg nuttig is, zijn: berekeningen van de aflossing van leningen, pensioenspaarplanningHet opstellen van persoonlijke of zakelijke budgetten en het uitvoeren van verkoopanalyses die nodig zijn om een ​​bepaalde winst of een break-evenpunt te bereiken.

Belangrijke concepten voordat u Doelzoeken gebruikt

Voordat u de tool gaat gebruiken, is het belangrijk dat u drie ideeën goed begrijpt: de doelcel, de gewenste waarde en de cel die verandertAls je deze elementen correct identificeert, is de rest een fluitje van een cent.

La doel cel Dit is de cel met het resultaat van een formule waarvan u een specifieke waarde wilt berekenen. Dit kan bijvoorbeeld het gemiddelde van uw eindcijfers, de maandelijkse aflossing van een lening of de totale winst van een product zijn. Belangrijk: deze cel moet een formule bevatten, geen handgeschreven getal.

El gewenste waarde Dit is het aantal dat de doelcel moet bereiken. Dit kan een minimumgemiddelde zijn om een ​​beurs te behouden, de maximale maandelijkse betaling die je budget toelaat, of de winst die je als doel voor je bedrijf hebt gesteld. U bepaalt deze waarde op basis van uw doelstelling..

La cel die verandert Dit is de onbekende in het probleem, de gegevens die u door Excel wilt laten berekenen. Het moet een cel zijn die deel uitmaakt van de formule in de doelcel. Dit kan een openstaand saldo zijn, het leenbedrag, het aantal jaren dat moet worden afgelost, de verkoopprijs, het aantal verkochte eenheden, enzovoort.

Het is essentieel dat De doelcel is, direct of indirect, afhankelijk van de cel die verandertAls er geen relatie tussen de twee in de formule is, kan Goal Seek geen resultaten vinden. In wezen vervangt deze tool de klassieke 'trial and error'-methode door een geautomatiseerd proces van iteraties.

Hoe Goal Seek stap voor stap werkt: voorbeeld van een lening

Een klassiek voorbeeld om doelzoeken te begrijpen is dat van een lening met periodieke betalingenStel je voor dat je weet hoeveel geld je wilt lenen, in hoeveel maanden je het wilt terugbetalen en wat de maximale afbetaling is die je kunt betalen, maar je hebt geen idee welke rente je nodig hebt om de bedragen te laten kloppen.

Bij dit type berekening wordt de functie gebruikt BETALING (PMT) De functie van Excel retourneert het te betalen bedrag per periode. Belangrijk hierbij is dat u de maandelijkse betaling al weet, dus u gebruikt Doelzoeken om Excel de rente te laten aanpassen totdat u dat bedrag bereikt.

1. Bereid het leningspreadsheet voor

Ten eerste is het een goed idee organiseer de gegevens in een kleine tabel Om alles georganiseerd te houden, kunt u zoiets op een nieuw werkblad maken:

• In A1: "Leenbedrag". In B1: het kapitaal dat u wilt opvragen, bijvoorbeeld 100000.
• In A2: “Termijn in maanden”. In B2: het aantal maanden, bijvoorbeeld 180 (wat 15 jaar zou zijn).
• In A3: “Rente”. In B3Laat het voorlopig leeg, want het zal het onbekende zijn.
• In A4: “Betaling”. In B4: hier zou de formule met BETALING staan.

Schrijf nu de formule in cel B4. =PMT(B3/12;B2;B1)Je deelt B3 door 12 omdat het tarief meestal in jaarlijkse termijnen wordt uitgedrukt en je de maandelijkse betaling berekent. Omdat B3 leeg is, gaat Excel uit van 0% rente en wordt een maandelijkse betaling berekend op basis van de looptijd en het bedrag.

In dit specifieke voorbeeld ziet u dat de berekende vergoeding ongeveer 555,56Maar die waarde is nog niet relevant voor jou. Het belangrijkste is dat je de formulestructuur Klaar voor gebruik: Zoek Doel.

2. Voer Doelzoeken uit om de rente te vinden

Nu de formule is ingevoerd, kunt u de analysetool openen. Ga naar het tabblad Gegevens en binnen de groep van commando's Selecteer voor prognose- of datatools Hypotheseanalyse > Doel zoekenEr wordt een dialoogvenster met drie velden geopend.

In het frame “Cel instellen” U geeft de cel op die de formule bevat waarvan u het resultaat wilt corrigeren, in dit geval B4. Dat is uw doelcel, waar de berekening van de maandelijkse betaling met behulp van de functie BET wordt uitgevoerd.

In het veld “Moed om” Je schrijft het bedrag op dat je daadwerkelijk wilt betalen. Als je idee is om 900 per maand te betalen, vul je dat hier in. -900Het minteken is belangrijk, omdat betalingen die u zelf doet, in Excel als negatieve bedragen worden beschouwd.

In het frame “Veranderende cel” U voert de celverwijzing in die Excel moet aanpassen om die betaling te realiseren. In dit voorbeeld is dat B3, waar de rente naartoe gaat. Wanneer u op OK klikt, begint Doelzoeken met itereren en wijzigt het tarief totdat er een wordt gevonden waarmee de maandelijkse betaling op -900 blijft.

Wanneer u klaar bent, zal de tool u een statusvenster met het resultaatU ziet de berekende rente in B3 en de nieuwe betaling in B4. Excel vindt meestal een vrij nauwkeurige oplossing als de formule klopt en de opgave numeriek logisch is.

Om het helemaal af te maken, kunt u formatear cel B3 als percentage Selecteer op het tabblad Start de percentagenotatie en pas het aantal decimalen aan met de pictogrammen voor het verhogen of verlagen van het aantal decimalen. Hierdoor wordt de rente in een leesbaarder formaat weergegeven.

Andere voorbeelden van gebruik: bankbiljetten, beleggingen en kredieten

De kracht van Goal Seek beperkt zich niet tot leningen. Het kan worden toegepast op een veelheid aan alledaagse situaties waarin Je hebt een numeriek doel en je mist een variabele die aan dat doel voldoet. We gaan een aantal veelvoorkomende praktijkgevallen bekijken.

1. Weten welk cijfer je nodig hebt voor het laatste examen

Een van de meest intuïtieve voorbeelden is dat van de kwalificatiesStel je voor dat je al meerdere cijfers hebt ingevoerd en nog maar één tentamen hebt. Je weet wat het minimale gemiddelde is dat je nodig hebt om het vak te halen of een beurs te behouden, maar je weet niet zeker welk cijfer je nodig hebt voor dat laatste tentamen.

Stel dat je gemiddeld 70 punten nodig hebt om het eindexamen te kunnen afleggen of je beurs te behouden. Je hebt al 3 examens afgelegd, je hebt er nog één te gaan en je wilt dat Excel je vertelt wat je moet doen. Welk cijfer heb je nodig voor het vierde examen? zodat het totale gemiddelde minimaal 70 bedraagt.

Voer eerst de bekende cijfers in een kolom in, bijvoorbeeld B2:B4. Laat cel B5 leeg, want dit is het cijfer voor het aanstaande examen, en schrijf in een andere cel, bijvoorbeeld B7, de formule voor het gemiddelde: =GEMIDDELD(B2:B5)Dat is uw doelcel.

Open Doel zoeken vanuit Gegevens > Wat-als-analyse > Doel zoeken en Je configureert het als volgt:

• Cel instellen: B7 (de cel met de gemiddelde formule).
• Waarde voor: 70 (het gemiddelde dat u wilt bereiken).
• Veranderende cel: B5 (het cijfer voor het openstaande examen).

Na bevestiging berekent Excel de waarde die B5 moet hebben, zodat het gemiddelde in B7 70 is. Het kan u bijvoorbeeld vertellen dat u een 85 in het laatste examen om het vereiste gemiddelde te halen. Ditzelfde idee kan worden gebruikt met gewogen gemiddelden met behulp van functies zoals SOMPRODUCT y SUMA, als elke test een ander gewicht heeft.

2. Gewogen gemiddelden en cijfers met verschillende wegingen

In veel cursussen wegen niet alle beoordelingen even zwaar mee. Opdrachten kunnen bijvoorbeeld 30% waard zijn, toetsen 20% en het eindtentamen 50%. In deze gevallen gebruik je in plaats van een eenvoudig gemiddelde meestal een formule: gewogen gemiddelde.

Een typische manier om het in te stellen is om de beoordelingen in de ene kolom te plaatsen en de wegingen (als decimaal of percentage) in een andere. U kunt de combinatie gebruiken SOMPRODUCT(cijfers;gewichten)/SOM(gewichten) om het eindcijfer te behalen.

De cel met de formule voor het gewogen gemiddelde is uw doelcel. Vervolgens gebruikt u, net als voorheen, Doelzoeken om Excel het gemiddelde te laten berekenen. het cijfer van een specifiek examen aanpassen totdat het totaalcijfer bijvoorbeeld 50% bedraagt ​​of de waarde die nodig is om te slagen.

Als u deze aanpak volgt, ontdekt u wellicht dat bijvoorbeeld: Je hebt 57,5% nodig voor het afsluitende examen zodat, rekening houdend met de wegingen van elk onderdeel, het cijfer voor de cursus de vereiste 50% bereikt.

3. Beleggings- en spaarplanning

Target Search is ook een bondgenoot wanneer u zich voorbereidt beleggings- of spaarplannenAls u al weet wat het verwachte rendement op uw beleggingen is en wat het tijdsbestek is, kunt u vragen stellen als: "Hoeveel moet ik elke maand investeren om het opgebouwde bedrag X te bereiken?" of "Welk beginbedrag moet ik investeren als ik een bepaald rendement wil behalen?"

Financiële functies zoals FV (toekomstige waarde), PV (huidige waarde), TARIEF of de BETALING zelf. U stelt de formule op die uw situatie beschrijft (bijvoorbeeld de toekomstige waarde van een reeks periodieke stortingen) en laat de maandelijkse bijdrage of het startkapitaal als variabele staan.

Zodra het model in elkaar is gezet, gebruikt u Target Find om uw positie te vergrendelen. besparings- of winstgevendheidsdoelstelling In de resultaatcel past Excel vervolgens de periodieke bijdrage of het initiële investeringsbedrag aan. Dezelfde logica als bij leningen, maar dan toegepast op de spaar- of investeringskant.

4. Credits en budgetbeperkingen

Terug naar leningen, je kunt nog een stap verder gaan. Stel je voor dat je met BETALING hebt berekend hoeveel je jaarlijks moet betalen voor een lening van € 12.000 over 20 jaar met een rente van 5%. Het resultaat is een betaling van ongeveer € 962,91 per jaar, maar Uw maximale budget is 900.

Met Goal Seek kunt u het probleem ook andersom formuleren: U wilt dat de jaarlijkse betaling -900 bedraagt En u wilt het leenbedrag aanpassen. U configureert de tool zo dat de doelcel de cel is met het BETALINGSresultaat, de gewenste waarde is -900 en de cel die verandert, de hoofdsom van de lening is.

Het resultaat kan bijvoorbeeld een leenbedrag zijn van ongeveer 11.216Zo weet u precies hoeveel u kunt lenen zonder de jaarlijkse limiet van € 900 te overschrijden. Wilt u in plaats van de hoofdsom vast te zetten, het leenbedrag gelijk houden en de rente wijzigen? de tijd Voor de aangifte hoeft u alleen de cel met het aantal jaren of maanden als de cel die wijzigt, op te geven.

5. Analyse van voordelen en noodzakelijke verkopen

In de zakenwereld is Goal Seek nuttig bij vragen als:Hoeveel eenheden moet ik verkopen om 50.000 te verdienen?"of tegen welke prijs moet ik verkopen om een ​​bepaalde marge te behalen, gegeven de kosten en de productiecapaciteit?"

U kunt een winstformule als volgt opstellen: (Prijs – Kosten) × Verkochte eenheden en gebruik deze als doelcel. Afhankelijk van uw behoeften stelt u de gewenste winst in en laat u de cel die verandert als het aantal te verkopen eenheden of de eenheidsprijs.

Als u bijvoorbeeld een doelwinst van 50.000 definieert, kunt u met Goal Seed berekenen hoeveel eenheden er moeten worden verkocht, de prijs en kosten vast houdenOf andersom: stel een verkoopvolume vast dat wordt beperkt door de productiecapaciteit en laat de prijs zich aanpassen om de gewenste winst te behalen.

Verschillen tussen Doelzoeken en Oplosser

Een belangrijk punt is het begrijpen waar het nut van iets ophoudt. Buscar Objetivo en waar begint de ene? OplosserNog een zeer krachtige Excel-invoegtoepassing. Beide worden gebruikt voor scenarioanalyse, maar ze lossen niet dezelfde problemen op.

Zoekdoelstelling is ontworpen om problemen met een enkele variabeleMet andere woorden, je kunt slechts één cel wijzigen om de doelwaarde te bereiken. Als je meer dan één onbekende hebt, schiet dit tekort, en daar komt Solver om de hoek kijken.

Met Solver kunt u werken met meerdere variabele cellen tegelijkU kunt beperkingen instellen (bijvoorbeeld dat bepaalde cellen niet negatief mogen zijn, dat een bepaald maximum niet mag worden overschreden, enz.) en bepalen of u een specifieke waarde wilt maximaliseren, minimaliseren of gelijk wilt maken. Het is een veel uitgebreidere optimalisatietool.

Als u bijvoorbeeld wilt weten welk cijfer u moet halen voor zowel Engels als scheikunde, zodat het gemiddelde van beide plus andere vakken 84 is, dan staat u voor een probleem met twee variabelenDoelzoeken werkt niet, maar Oplosser wel. In dat geval stelt u de doelcel in op het gemiddelde, stelt u de waarde in op 84 en specificeert u dat de cellen die kunnen veranderen de cijfers voor Engels en Scheikunde zijn.

Een ander typisch voorbeeld met Solver is de winstmaximalisatie onder beperkingen, zoals wanneer een bedrijf meerdere diensten verkoopt, elk met zijn eigen winst, en de combinatie van te verkopen eenheden wil vinden die de totale winst maximaliseert, terwijl aan bepaalde voorwaarden wordt voldaan (minimale verkoop van één dienst, maximale verkoop van een andere dienst, algemene limiet van eenheden, etc.).

Veelvoorkomende problemen bij het gebruik van Goal Seek en hoe u deze kunt vermijden

Hoewel Goal Seek heel eenvoudig te gebruiken is, biedt het soms geen oplossing of levert het vreemde resultaten op. In de meeste gevallen De fout ligt in de configuratie of de numerieke eigenschappen van het probleemniet in de tool zelf.

Een veel voorkomende oorzaak is dat de de doelcel is niet afhankelijk van de veranderende celAls de cel die u als variabele hebt aangegeven, niet is betrokken bij de formule van de doelcel (direct of indirect), hoeft Excel niets aan te passen en zal het zoeken naar het doel hoe dan ook mislukken.

Ze kunnen ook problemen veroorzaken circulaire verwijzingenDat wil zeggen, formules waarbij een cel uiteindelijk afhankelijk is van zichzelf. Dit kan ertoe leiden dat berekeningen niet convergeren of dat Excel bepaalde opties uitschakelt. Het is raadzaam om te controleren of uw formules consistent zijn en niet onbeheersbaar recursief zelfondersteunend.

Een ander punt om te herzien zijn de iteratie-opties Als in Excel het maximale aantal iteraties of de maximaal toegestane wijziging tussen iteraties te laag is, kan Goal Seek voortijdig stoppen of tot een onnauwkeurige benadering komen. Deze parameters kunt u aanpassen via Bestand > Opties > Formules, door het maximale aantal iteraties te verhogen of de maximale wijziging aan te passen.

Ten slotte is het de moeite waard om te controleren of de Het doel dat u voorstelt, moet numeriek logisch zijn.Als u om iets vraagt ​​dat onmogelijk is binnen de beperkingen van uw model (bijvoorbeeld een onrealistisch lage maandelijkse betaling voor een enorme lening met een zeer hoge rente), dan is het normaal dat Excel geen passende waarde kan vinden.

De tool Doel zoeken in Excel wordt, wanneer verstandig gebruikt, een krachtige snelkoppeling voor het beantwoorden van allerlei numerieke vragen: het stelt u in staat om van "wat gebeurt er als ik dit verander?" naar "dit is wat ik wil bereiken, vertel me wat ik moet veranderen" te gaan, en daarmee Het bespaart u veel tijd door te proberen en fouten te maken. zowel bij het beheren van je persoonlijke financiën als bij het analyseren van je bedrijf of je studie.