Goal Seek är ett av Excels What-if-analysverktyg som hjälper dig att hitta rätt inmatningsvärde för en formel för att få önskad utdata. Den visar hur ett värde i en formel påverkar ett annat. Med andra ord, om du har ett målvärde du vill uppnå kan du använda målsökning för att hitta rätt ingångsvärde för att få det.
Låt oss till exempel säga att du fick totalt 75 poäng i ett ämne och att du behöver minst 90 för att få ett S-betyg i det ämnet. Tack och lov har du ett sista test som kan hjälpa dig att höja din genomsnittliga poäng. Du kan använda Goal Seek för att ta reda på hur mycket poäng du behöver på det sista testet för att få ett S-betyg.
Goal Seek använder en trial-and-error-metod för att spåra tillbaka problemet genom att mata in gissningar tills det kommer fram till rätt resultat. Goal Seek kan hitta det bästa inmatningsvärdet för formeln på några sekunder som skulle ha tagit lång tid att räkna ut manuellt. I den här artikeln visar vi dig hur du använder målsökningsverktyget i Excel med några exempel.
Komponenter i målsökningsfunktionen
Målsökningsfunktionen består av tre parametrar, nämligen:
- Ställ in cell – Det här är cellen där formeln skrivs in. Den anger i vilken cell du vill ha önskad utdata.
- Att värdesätta – Det här är det mål/önskevärde som du vill ha som ett resultat av målsökningsoperationen.
- Genom att byta cell – Detta anger cellen vars värde måste justeras för att få önskad utdata.
Så här använder du målsökning i Excel: Exempel 1
För att visa hur det fungerar i ett enkelt exempel, föreställ dig att du driver ett fruktstånd. I skärmdumpen nedan visar cell B11 (nedan) hur mycket det kostade dig att köpa frukt till ditt stall och cell B12 visar dina totala intäkter på att sälja dessa frukter. Och cell B13 visar procentandelen av din vinst (20%).
Om du vill öka din vinst till "30%", men du kan inte öka din investering, så måste du öka dina intäkter för att få vinst. Men till hur mycket? Målsökning hjälper dig att hitta det.
Du använder följande formel i cell B13 för att beräkna vinstprocenten:
=(B12-B11)/B12Nu vill du beräkna vinstmarginalen så att din vinstprocent är 30%. Du kan göra detta med Goal Seek i Excel.
Det första du ska göra är att välja den cell vars värde du vill justera. Varje gång du använder målsökning måste du välja en cell som innehåller en formel eller funktion. I vårt fall väljer vi cell B13 eftersom den innehåller formeln för att beräkna procentandelen.
Gå sedan till fliken "Data", klicka på knappen "Tänk om analys" i prognosgruppen och välj "Målsökning".
Dialogrutan Målsökning visas med tre fält:
- Ställ in cell – Ange cellreferensen som innehåller formeln (B13). Det här är cellen som kommer att ha din önskade utdata.
- Att värdesätta – Ange önskat resultat du försöker uppnå (30%).
- Genom att byta cell – Infoga cellreferensen för inmatningsvärdet som du vill ändra (B12) för att komma fram till önskat resultat. Klicka bara på cellen eller ange cellreferensen manuellt. När du väljer cellen kommer Excel att lägga till "$"-tecknet före kolumnbokstaven och radnumret för att göra den till en absolut cell.
När du är klar klickar du på "OK" för att testa det.
Då dyker en "Målsökningsstatus"-dialogruta upp och informerar dig om den hittat någon lösning enligt nedan. Om en lösning har hittats kommer ingångsvärdet i "förändringscellen (B12)" att justeras till ett nytt värde. Det här är vad vi vill. Så i det här exemplet fastställde analysen att för att du ska nå ditt vinstmål på 30 % måste du uppnå en intäkt på 1 635,5 USD (B12).
Klicka på "OK" och Excel kommer att ändra cellvärdena eller klicka på "Avbryt" för att kassera lösningen och återställa det ursprungliga värdet.
Ingångsvärdet (1635,5) i cell B12 är vad vi fick reda på med Goal Seek för att nå vårt mål (30%).
Saker att komma ihåg när du använder Goal Seek
- Uppsättningscellen måste alltid innehålla en formel som är beroende av cellen "Genom att byta cell".
- Du kan bara använda målsökning på ett enskilt cellinmatningsvärde åt gången
- "Genom att ändra cell" måste innehålla ett värde och inte en formel.
- Om Goal Seek inte kan hitta den korrekta lösningen visar den det närmaste värdet den kan producera och talar om för dig att meddelandet 'GoalSeeking' kanske inte har hittat en lösning.
Vad du ska göra när Excel Goal Seek inte fungerar
Men om du är säker på att det finns en lösning finns det några saker du kan försöka lösa problemet.
Kontrollera målsökningsparametrar och -värden
Det finns två saker du bör kontrollera: först, kontrollera om parametern "Set cell" refererar till formelcellen. För det andra, se till att formelcellen (Ange cell) beror, direkt eller indirekt, på den förändrade cellen.
Justera iterationsinställningarna
I Excel-inställningar kan du ändra antalet möjliga försök som Excel kan göra för att hitta rätt lösning samt dess noggrannhet.
För att ändra inställningar för iterationsberäkning, klicka på "Arkiv" i fliklistan. Klicka sedan på "Alternativ" längst ned.
I fönstret för Excel-alternativ klickar du på "Formler" i den vänstra rutan.
Under "Beräkningsalternativ" ändrar du dessa inställningar:
- Maximala iterationer – Det indikerar antalet möjliga lösningar som excel kommer att beräkna; ju högre siffra desto fler iterationer kan den utföra. Om du till exempel ställer in "Maximala iterationer" till 150, testar Excel 150 möjliga lösningar.
- Maximal Change – Det indikerar noggrannheten av resultaten; det mindre antalet ger högre noggrannhet. Till exempel, om ditt inmatade cellvärde är lika med '0' men målsökning slutar beräkna vid '0,001', bör du lösa problemet om du ändrar detta till '0,0001'.
Öka värdet för 'Maximum Iterations' om du vill att Excel ska testa fler möjliga lösningar och minska värdet för 'Maximum Change' om du vill ha ett mer exakt resultat.
Skärmdumpen nedan visar standardvärdet:
Inga cirkulära referenser
När en formel refererar tillbaka till sin egen cell kallas det en cirkulär referens. Om du vill att Excel Goal Seek ska fungera korrekt bör formler inte använda cirkulär referens.
Excel målsökning exempel 2
Låt oss säga att du lånar pengar på "$25 000" från någon. De lånar dig pengarna till en ränta på 7% per månad under en period av 20 månader, vilket gör återbetalningen på "$1327" per månad. Men du har bara råd att betala "1 000 $" per månad i 20 månader med en ränta på 7%. Goal Seek kan hjälpa dig att hitta lånebeloppet som ger en månadsbetalning (EMI) på "$1000".
Mata in de tre indatavariablerna som du kommer att behöva för att beräkna din PMT-beräkning, dvs. en ränta på 7 %, en löptid på 20 månader och ett kapitalbelopp på 25 000 USD.
Ange följande PMT-formel i cell B5 som ger dig ett EMI-belopp på 1 327,97 USD.
Syntaxen för PMT-funktionen:
=PMT(Räntesats/12, löptid, huvudman)Formeln:
=PMT(B3/12;B4;-B2)
Välj cellen B5 (formelcell) och gå till Data –> Vad händer om analys –> Målsökning.
Använd dessa parametrar i fönstret 'Goal Seek':
- Ställ in cell – B5 (cellen som innehåller formeln som beräknar EMI)
- Att värdesätta – 1000 (formelresultatet/målet du letar efter)
- Genom att byta cell – B2 (detta är lånebeloppet som du vill ändra för att uppnå målvärdet)
Tryck sedan på "OK" för att behålla den hittade lösningen eller "Avbryt" för att kassera den.
Analysen säger att den maximala summan pengar du kan låna är $18825 om du vill överskrida din budget.
Det är så du använder Goal Seek i Excel.