Goal Seek-funktionen i Excel: komplett guide och exempel

La Målsökningsfunktionen i Excel Det är ett av de där verktygen som går obemärkt förbi tills du upptäcker det, och sedan kan du inte föreställa dig hur du någonsin hade klarat dig utan det. Det låter dig säga till Excel: "Det här är vad jag vill uppnå", och programmet självt beräknar värdet en specifik cell ska ha för att beräkningarna ska fungera.

Istället för att frenetiskt prova ut siffror tills du hittar det som passar, kan du använda Hitta målet så att Excel kan göra det hårda arbetetDet är extremt användbart inom finans (lån, investeringar, personliga budgetar), i akademiska frågor (betyg, medelvärden, stipendier) och i alla beräkningar där du har ställt in en formel och saknar en okänd.

Vad exakt är Målsökning i Excel?

Sök objekt Det är en del av verktygen för Analys Tänk om… i Excel. Istället för att fråga dig själv "om jag ändrar dessa data, vad händer med resultatet?", vänder du på tillvägagångssättet: du anger för Excel vilket resultat du vill få och ber det beräkna vilket värde en av cellerna som är involverade i formeln ska anta.

Med andra ord, verktyget fungerar "Motsatsen" till vad du normalt skulle göraDu vet redan det slutliga värdet du behöver, och du låter Excel hitta indatavärdet som gör att din formel returnerar det resultatet. Matematiskt sett är det som att lösa en ekvation med en enda obekant utan att behöva isolera den manuellt.

Den här funktionen finns tillgänglig i Excel och även i andra kontorspaket som t.ex. ONLYOFFICE Dokumentdär det har införlivats i deras kalkylblad för att erbjuda samma typ av scenarioanalys. I samtliga fall är filosofin densamma: Sätt ett mål, välj variabeln och låt programmet iterera. tills ett värde som passar hittas.

Några typiska situationer där det är mycket användbart att använda Goal Seek är beräkningar av låneamortering, planering för pensionssparandeUtformning av personliga eller företagsbudgetar och försäljningsanalyser som är nödvändiga för att nå en specifik vinst eller en break-even-punkt.

Viktiga begrepp innan du använder Goal Seek

Innan du börjar använda verktyget är det viktigt att vara tydlig med tre idéer: målcellen, det önskade värdet och cellen som ändrasOm du identifierar dessa element korrekt är resten en barnlek.

La målcell Det här är cellen där du har resultatet av en formel som du vill ge ett specifikt värde. Det kan till exempel vara medelvärdet av dina slutbetyg, den månatliga betalningen av ett lån eller den totala vinsten för en produkt. Viktigt: den här cellen måste innehålla en formel, inte ett handskrivet nummer.

El önskat värde Det här är det tal du vill att målcellen ska nå. Det kan vara ett lägsta medelbelopp för att behålla ett stipendium, den maximala månadsbetalningen som din budget tillåter eller den vinst du har satt som mål för ditt företag. Du bestämmer detta värde baserat på ditt mål..

La cell som förändras Detta är den okända i problemet, den data du vill att Excel ska beräkna. Det måste vara en cell som ingår i formeln i målcellen. Detta kan vara ett utestående saldo, lånebeloppet, antalet år att återbetala, försäljningspriset, antalet sålda enheter etc.

Det är viktigt att målcellen beror, direkt eller indirekt, på cellen som förändrasOm det inte finns något samband mellan dem i formeln kommer Goal Seek inte att kunna hitta några resultat. I huvudsak ersätter detta verktyg den klassiska "trial and error"-metoden med en automatiserad process av iterationer.

Så här fungerar Goal Seek steg för steg: exempel på lån

Ett klassiskt exempel för att förstå målsökning är en lån med periodiska betalningarTänk dig att du vet hur mycket pengar du vill låna, hur många månader du vill betala tillbaka det på och vad den maximala betalningen du har råd med är, men du har ingen aning om vilken ränta du behöver för att få siffrorna att stämma.

Denna typ av beräkning använder funktionen BETALNING (PMT) Excels funktion returnerar det belopp som ska betalas i varje period. Nyckeln här är att du redan vet den månatliga betalningen, så du använder Goal Seek för att få Excel att justera räntan tills du når det beloppet.

1. Förbered lånebladet

För det första är det en bra idé organisera data i en liten tabell För att hålla allt organiserat kan du skapa något liknande detta på ett nytt ark:

• I A1: ”Lånebelopp”. I B1: det kapital du vill begära, till exempel 100000.
• I A2: "Löptid i månader". I B2: antalet månader, till exempel 180 (vilket skulle vara 15 år).
• I A3: ”Ränta”. I B3Lämna det tomt för tillfället, för det kommer att vara det okända.
• I A4: ”Betalning”. I B4: här skulle formeln med BETALNING stå.

Skriv nu formeln i cell B4. =BETALNING(B3/12;B2;B1)Du dividerar B3 med 12 eftersom räntan vanligtvis uttrycks i årliga termer, och du beräknar betalningen månadsvis. Eftersom B3 är tomt antar Excel 0 % ränta och ger dig en månadsbetalning baserad endast på löptid och belopp.

I det här specifika exemplet kan du se att den beräknade avgiften är cirka 555,56Men det värdet är inte relevant för dig ännu. Det viktiga är att du redan har formelstruktur redo att använda Hitta mål.

2. Kör Goal Seek för att hitta räntan

Med formeln på plats kan du nu öppna analysverktyget. Gå till fliken Data och inom gruppen av kommandon För prognos- eller dataverktyg, välj Hypotesanalys > MålsökningEn dialogruta med tre fält öppnas.

I ramen "Ange cell" Du anger den cell som innehåller formeln vars resultat du vill korrigera, i det här fallet B4. Det är din målcell, där den månatliga betalningsberäkningen finns med hjälp av PMT-funktionen.

I fältet "Mod att" Du skriver det belopp du faktiskt vill betala. Om din idé är att betala 900 per månad, skriver du det här. -900Minustecknet är viktigt eftersom betalningar som kommer ur din ficka betraktas som negativa belopp i Excel.

I ramen "Byter cell" Du anger cellreferensen som Excel ska justera för att uppnå den betalningen. I det här exemplet blir det B3, där räntan ska placeras. När du klickar på OK börjar Goal Seek iterera och ändrar räntan tills den hittar en som lämnar den månatliga betalningen på -900.

När du är klar visar verktyget dig en statusfönster med resultatetDu ser den beräknade räntesatsen i B3 och den nya betalningen i B4. Excel hittar vanligtvis en ganska exakt lösning om formeln är korrekt och problemet är numeriskt begripligt.

Som grädde på moset kan du format cell B3 som en procentandel Från fliken Hem väljer du procentformat och justerar antalet decimaler med hjälp av ikonerna för att öka eller minska decimaler. Detta visar räntan i ett mer lättläst format.

Andra exempel på användning: sedlar, investeringar och krediter

Kraften i Goal Seek är inte begränsad till lån. Den kan tillämpas i en mängd olika vardagliga situationer där Du har ett numeriskt mål och du saknar en variabel som uppfyller det målet. Vi ska titta på flera mycket vanliga praktiska fall.

1. Att veta vilket betyg du behöver på sista provet

Ett av de mest intuitiva exemplen är det hos kvalifikationerTänk dig att du redan har flera betyg inlämnade och bara har ett prov kvar. Du vet vilket lägsta medelbetyg du behöver för att klara kursen eller behålla ett stipendium, men du är inte säker på vilket betyg du behöver för det sista provet.

Anta att du behöver ett genomsnitt på 70 för att kunna skriva slutprovet eller fortsätta få ditt stipendium. Du har redan gjort 3 prov, du har ett kvar och du vill att Excel ska berätta för dig Vilket betyg behöver du för att få på det fjärde provet? så att det totala genomsnittet är minst 70.

Först anger du de kända betygen i en kolumn, till exempel B2:B4. Lämna cell B5 tom eftersom det kommer att vara betyget för den kommande tentamen, och i en annan cell, låt oss säga B7, skriver du medelvärdesformeln: =MEDEL(B2:B5)Det kommer att vara din målcell.

Öppna Målsökning från Data > Tänk om-analys > Målsökning och Du konfigurerar det så här:

• Ange cellB7 (cellen med medelvärdesformeln).
• Värde för: 70 (genomsnittet du vill uppnå).
• Ändra cellB5 (betyget för den kommande tentamen).

Vid bekräftelse beräknar Excel värdet som B5 ska ha så att medelvärdet i B7 är 70. Till exempel kan det säga att du behöver en 85 i det sista provet för att nå det erforderliga medelvärdet. Samma idé kan användas med viktade medelvärden med hjälp av funktioner som SUMPRODUKT y Suma, om varje test har en annan viktning.

2. Viktade medelvärden och betyg med olika vikter

I många kurser viktas inte alla bedömningar lika. Uppgifter kan till exempel vara värda 30 %, prov 20 % och slutprovet 50 %. I dessa fall använder man vanligtvis formeln för att istället för ett enkelt medelvärde vägt genomsnitt.

Ett typiskt sätt att ställa in det är att ha betygen i en kolumn och vikterna (som decimaltal eller procent) i en annan. Du kan använda kombinationen SUMPRODUKT(betyg;vikter)/SUMMA(vikter) för att få slutbetyget.

Cellen som innehåller formeln för viktat medelvärde blir din målcell. Sedan, som tidigare, använder du Goal Seek för att få Excel att beräkna medelvärdet. justera betyget på ett specifikt prov tills det totala betyget når, säg, 50 % eller det värde du behöver för att klara.

Genom att följa den här metoden kan du till exempel upptäcka att Du behöver 57,5 ​​% på slutprovet så att kursbetyget, med viktningen av varje del, når de erforderliga 50 %.

3. Investerings- och sparplanering

Target Search är också en allierad när du förbereder dig investerings- eller sparplanerOm du redan vet den förväntade avkastningen på dina investeringar och tidsramen kan du ställa frågor som: "Hur mycket måste jag bidra varje månad för att nå X ackumulerat belopp?" eller "Vilket initialt belopp behöver jag investera om jag vill uppnå en viss avkastning?"

Finansiella funktioner som t.ex. FV (framtida värde), PV (nuvärde), HASTIGHET eller själva BETALNINGEN. Du ställer in formeln som beskriver din situation (till exempel det framtida värdet av en serie periodiska bidrag) och låter det månatliga bidraget eller startkapitalet vara variabel.

När modellen är monterad använder du Target Find för att låsa din position. besparingar eller lönsamhetsmål I resultatcellen justerar Excel sedan det periodiska bidraget eller det initiala investeringsbeloppet. Det är samma logik som för lån, men tillämpad på spar- eller investeringssidan.

4. Krediter och budgetbegränsningar

Om vi ​​går tillbaka till lån kan du gå ett steg längre. Tänk dig att du har beräknat med BETALNING hur mycket du ska betala årligen för ett lån på 12 000 euro över 20 år med en ränta på 5 %. Resultatet är en betalning på cirka 962,91 euro per år, men Din maxbudget är 900.

Med Goal Seek kan du beskriva problemet i omvänd ordning: Du vill att den årliga betalningen ska vara -900 Och du vill att lånebeloppet ska justeras. Du konfigurerar verktyget så att målcellen är den som innehåller resultatet BETALNING, det önskade värdet är -900 och cellen som ändras är lånekapitalet.

Resultatet kan till exempel bli ett lånebelopp på cirka 11.216På så sätt vet du exakt hur mycket du kan låna utan att överskrida den årliga gränsen på 900 euro. Om du istället för att fastställa kapitalbeloppet vill hålla lånebeloppet detsamma och ändra el tiempo För returen behöver du bara ange den cell som innehåller antalet år eller månader som den som ändras.

5. Analys av fördelar och nödvändig försäljning

I affärsvärlden är Goal Seek användbart för frågor som:Hur många enheter måste jag sälja för att tjäna 50 000?"eller till vilket pris måste jag sälja för att uppnå en viss marginal, givet en kostnad och en produktionskapacitet?"

Du kan sätta ihop en vinstformel så här: (Pris – Kostnad) × Sålda enheter och använd den som målcell. Beroende på dina behov ställer du in önskad vinst och lämnar cellen som ändras beroende på antalet enheter att sälja eller enhetspriset.

Om du till exempel definierar en vinstmål på 50 000 kan du låta Goal Seek beräkna hur många enheter som behöver säljas, hålla pris och kostnad fastEller tvärtom: sätt en försäljningsvolym begränsad av produktionskapaciteten och låt priset justeras för att uppnå önskad vinst.

Skillnader mellan målsökning och lösare

En viktig poäng är att förstå var nyttan slutar. Sök objekt och var börjar den ena? LösareEtt annat mycket kraftfullt Excel-tillägg. Båda används för scenarioanalys, men de löser inte samma typ av problem.

Sökmålet är utformat för att problem med en enda variabelMed andra ord kan du bara ändra en cell för att försöka nå målvärdet. Om du har mer än en okänd funktion blir detta inte tillräckligt bra, och det är där Solver kommer in i bilden.

Solver låter dig arbeta med flera variabla celler samtidigtDu kan ställa in begränsningar (till exempel att vissa celler inte får vara negativa, att ett visst maxvärde inte får överskridas, etc.) och bestämma om ditt mål är att maximera, minimera eller vara lika med ett specifikt värde. Det är ett mycket mer omfattande optimeringsverktyg.

Om du till exempel vill veta vilket betyg du behöver få i både engelska och kemi så att medelbetyget för båda plus andra ämnen är 84, står du inför en problem med två variablerMålsökning fungerar inte, men det gör Solver. I så fall ställer du in målcellen på medelvärdet, anger värdet till 84 och anger att cellerna som kan ändras är betygen i engelska och kemi.

Ett annat typiskt exempel med Solver är vinstmaximering under begränsningar, till exempel när ett företag säljer flera tjänster, var och en med sin egen vinst, och vill hitta den kombination av enheter att sälja som maximerar den totala vinsten samtidigt som vissa villkor respekteras (minimiförsäljning av en tjänst, maximalförsäljning av en annan, total gräns för antalet enheter, etc.).

Vanliga problem med Goal Seek och hur man undviker dem

Även om Goal Seek är väldigt lätt att använda, kan det ibland vara svårt att hitta en lösning eller ge konstiga resultat. I de flesta fall, Felet ligger i konfigurationen eller problemets numeriska egenskaper.inte i själva verktyget.

En vanlig orsak är att målcellen är inte beroende av den föränderliga cellenOm cellen du har angett som variabel inte ingår i målcellens formel (direkt eller indirekt), har Excel inget att justera, och målsökningen kommer att misslyckas oavsett.

De kan också orsaka problem för dig cirkulära referenserDet vill säga formler där en cell blir beroende av sig själv. Detta kan leda till att beräkningar inte konvergerar eller att Excel inaktiverar vissa alternativ. Det är lämpligt att kontrollera att dina formler är konsekventa och inte rekursivt självbärande utan kontroll.

En annan punkt att granska är iterationsalternativ Om det maximala antalet iterationer eller den maximalt tillåtna förändringen mellan iterationer är för låg i Excel, kan Goal Seek ge upp i förtid eller komma fram till en felaktig approximation. Dessa parametrar justeras i Arkiv > Alternativ > Formler, genom att öka det maximala antalet iterationer eller justera den maximala förändringen.

Slutligen är det värt att kontrollera att Det mål du föreslår bör vara numeriskt rimligt.Om du ber om något som är omöjligt med tanke på din modells begränsningar (till exempel en orealistiskt låg månadsbetalning för ett enormt lån med en mycket hög ränta) är det normalt att Excel inte kan hitta något värde som passar.

Målsökningsverktyget i Excel blir, när det används klokt, en kraftfull genväg för att besvara alla möjliga numeriska frågor: det låter dig gå från "vad händer om jag ändrar detta?" till "det här är vad jag vill uppnå, berätta vad jag behöver ändra", och med det Det sparar dig mycket tid med trial and error. både i att hantera din privatekonomi och i att analysera ditt företag eller dina studier.