Sådan bruger du Goal Seek i Excel

Goal Seek er et af Excels What-if-analyseværktøjer, der hjælper dig med at finde den korrekte inputværdi af en formel for at få det ønskede output. Det viser, hvordan en værdi i en formel påvirker en anden. Med andre ord, hvis du har en målværdi, du ønsker at opnå, kan du bruge målsøgning til at finde den rigtige inputværdi for at få den.

Lad os f.eks. sige, at du fik i alt 75 karakterer i et fag, og du skal have mindst 90 for at få en S-karakter i det pågældende fag. Heldigvis har du en sidste test, der kan hjælpe dig med at hæve din gennemsnitlige score. Du kan bruge Goal Seek til at finde ud af, hvor meget score du skal bruge på den sidste test for at få en S-karakter.

Goal Seek bruger en trial-and-error-metode til at spore problemet tilbage ved at indtaste gæt, indtil det når frem til det rigtige resultat. Goal Seek kan finde den bedste inputværdi for formlen på få sekunder, som ville have taget lang tid at finde ud af manuelt. I denne artikel viser vi dig, hvordan du bruger værktøjet Goal Seek i Excel med nogle eksempler.

Komponenter i målsøgningsfunktionen

Målsøgningsfunktionen består af tre parametre, nemlig:

  • Indstil celle – Dette er cellen, hvori formlen er indtastet. Det specificerer den celle, hvor du ønsker det ønskede output.
  • At værdsætte – Dette er den mål/ønskede værdi, du ønsker som et resultat af målsøgningsoperationen.
  • Ved at skifte celle – Dette angiver den celle, hvis værdi skal justeres for at få det ønskede output.

Sådan bruges målsøgning i Excel: Eksempel 1

For at demonstrere, hvordan det fungerer i et simpelt eksempel, forestil dig, at du kører en frugtbod. I nedenstående skærmbillede viser celle B11 (nedenfor) hvor meget det kostede dig at købe frugter til din bod, og celle B12 viser din samlede omsætning ved at sælge disse frugter. Og celle B13 viser procentdelen af ​​dit overskud (20%).

Hvis du gerne vil øge din fortjeneste til '30%', men du ikke er i stand til at øge din investering, så skal du øge din omsætning for at opnå fortjeneste. Men til hvor meget? Målsøgning vil hjælpe dig med at finde det.

Du bruger følgende formel i celle B13 til at beregne profitprocenten:

=(B12-B11)/B12

Nu vil du beregne overskudsgraden, så din overskudsprocent er 30%. Det kan du gøre med Goal Seek i Excel.

Den første ting at gøre er at vælge den celle, hvis værdi du vil justere. Hver gang du bruger Målsøgning, skal du vælge en celle, der indeholder en formel eller funktion. I vores tilfælde vælger vi celle B13, fordi den indeholder formlen til at beregne procentdelen.

Gå derefter til fanen 'Data', klik på knappen 'Hvad nu hvis Analyse' i prognosegruppen, og vælg 'Målsøgning'.

Dialogboksen Målsøgning vises med 3 felter:

  • Indstil celle – Indtast den cellereference, der indeholder formlen (B13). Dette er den celle, der skal have dit ønskede output.
  • At værdsætte – Indtast det ønskede resultat, du forsøger at opnå (30%).
  • Ved at skifte celle – Indsæt cellereferencen for den inputværdi, du ønsker at ændre (B12) for at nå frem til det ønskede resultat. Du skal blot klikke på cellen eller manuelt indtaste cellereferencen. Når du vælger cellen, tilføjer Excel tegnet '$' før kolonnebogstavet og rækkenummeret for at gøre det til en absolut celle.

Når du er færdig, skal du klikke på 'OK' for at teste den.

Derefter vil en 'Goal Seek Status'-dialogboks dukke op og informere dig, hvis den fandt en løsning som vist nedenfor. Hvis der er fundet en løsning, vil inputværdien i 'skiftende celle (B12)' blive justeret til en ny værdi. Det er det, vi ønsker. Så i dette eksempel fastslog analysen, at for at du kan nå dit mål på 30 %, skal du opnå en omsætning på 1635,5 USD (B12).

Klik på 'OK' og Excel vil ændre celleværdierne eller klik på 'Annuller' for at kassere løsningen og gendanne den oprindelige værdi.

Inputværdien (1635,5) i celle B12 er, hvad vi fandt ud af ved at bruge Goal Seek for at nå vores mål (30%).

Ting at huske, når du bruger Goal Seek

  • Sætcellen skal altid indeholde en formel, der er afhængig af cellen 'Ved at ændre celle'.
  • Du kan kun bruge målsøgning på en enkelt celle-inputværdi ad gangen
  • 'Ved at ændre celle' skal indeholde en værdi og ikke en formel.
  • Hvis Goal Seek ikke kan finde den rigtige løsning, viser den den tætteste værdi, den kan producere, og fortæller dig, at 'Målsøgende måske ikke har fundet en løsning'-meddelelsen.

Hvad skal man gøre, når Excel Goal Seek ikke virker

Men hvis du er sikker på, at der er en løsning, er der et par ting, du kan prøve for at løse dette problem.

Tjek målsøgningsparametre og værdier

Der er to ting, du bør kontrollere: For det første skal du kontrollere, om parameteren 'Indstil celle' refererer til formelcellen. For det andet skal du sørge for, at formelcellen (Set celle) afhænger, direkte eller indirekte, af den skiftende celle.

Justering af iterationsindstillinger

I Excel-indstillinger kan du ændre antallet af mulige forsøg, Excel kan gøre for at finde den rigtige løsning samt dens nøjagtighed.

For at ændre iterationsberegningsindstillinger skal du klikke på 'Filer' fra fanelisten. Klik derefter på 'Indstillinger' nederst.

I vinduet med Excel-indstillinger skal du klikke på 'Formler' i venstre rude.

Under "Beregningsindstillinger" skal du ændre disse indstillinger:

  • Maksimale iterationer – Det angiver antallet af mulige løsninger, som excel vil beregne; jo højere tal, jo flere iterationer kan den udføre. For eksempel, hvis du indstiller 'Maksimale iterationer' til 150, tester Excel 150 mulige løsninger.
  • Maksimal ændring – Det angiver nøjagtigheden af ​​resultaterne; det mindre tal giver højere nøjagtighed. For eksempel, hvis din inputcelleværdi lig med '0', men Goal Seek stopper med at beregne ved '0,001', bør ændring af dette til '0,0001' løse problemet.

Forøg 'Maksimale iterationer'-værdien, hvis du ønsker, at Excel skal teste flere mulige løsninger, og sænk 'Maksimal ændring'-værdien, hvis du ønsker et mere præcist resultat.

Skærmbilledet nedenfor viser standardværdien:

Ingen cirkulære referencer

Når en formel refererer tilbage til sin egen celle, kaldes det en cirkulær reference. Hvis du ønsker, at Excel Goal Seek skal fungere korrekt, bør formler ikke bruge cirkulær reference.

Excel-målsøgningseksempel 2

Lad os sige, at du låner penge på '$25000' af nogen. De låner dig pengene til en rente på 7% om måneden i en periode på 20 måneder, hvilket giver en tilbagebetaling på '$1327' om måneden. Men du har kun råd til at betale '$1.000' om måneden i 20 måneder med en rente på 7%. Goal Seek kan hjælpe dig med at finde det lånebeløb, der giver en månedlig betaling (EMI) på '$1000'.

Indtast de tre inputvariabler, som du skal bruge for at beregne din PMT-beregning, dvs. rente på 7%, løbetid på 20 måneder og hovedstol på $25.000.

Indtast følgende PMT-formel i celle B5, som giver dig et EMI-beløb på $1.327,97.

Syntaksen for PMT-funktionen:

=PMT(rente/12, løbetid, hovedstol)

Formlen:

=PMT(B3/12;B4;-B2)

Vælg cellen B5 (formelcelle) og gå til Data –> Hvad nu hvis Analyse –> Målsøgning.

Brug disse parametre i vinduet 'Goal Seek':

  • Indstil celle – B5 (den celle, der indeholder formlen, der beregner EMI)
  • At værdsætte – 1000 (formelresultatet/målet, du leder efter)
  • Ved at skifte celle – B2 (dette er lånebeløbet, som du ønsker at ændre for at opnå målværdien)

Tryk derefter på 'OK' for at beholde den fundne løsning eller 'Annuller' for at kassere den.

Analysen fortæller dig, at det maksimale beløb, du kan låne, er $18825, hvis du vil overskride dit budget.

Sådan bruger du Goal Seek i Excel.