Slik bruker du Goal Seek i Excel

Goal Seek er et av Excels What-if-analyseverktøy som hjelper deg med å finne riktig inngangsverdi til en formel for å få ønsket utdata. Den viser hvordan en verdi i en formel påvirker en annen. Med andre ord, hvis du har en målverdi du ønsker å oppnå, kan du bruke målsøk for å finne riktig inngangsverdi for å få den.

La oss for eksempel si at du fikk totalt 75 karakterer i et emne, og du trenger minst 90 for å få en S-karakter i det faget. Heldigvis har du en siste test som kan hjelpe deg med å øke den gjennomsnittlige poengsummen din. Du kan bruke Goal Seek for å finne ut hvor mye poengsum du trenger på den siste testen for å få en S-karakter.

Goal Seek bruker en prøv-og-feil-metode for å spore problemet tilbake ved å legge inn gjetninger til det kommer til riktig resultat. Goal Seek kan finne den beste inngangsverdien for formelen i løpet av sekunder som ville ha tatt lang tid å finne ut manuelt. I denne artikkelen viser vi deg hvordan du bruker målsøkingsverktøyet i Excel med noen eksempler.

Komponenter i målsøkefunksjonen

Målsøkefunksjonen består av tre parametere, nemlig:

  • Sett celle – Dette er cellen der formelen legges inn. Den spesifiserer cellen der du vil ha ønsket utgang.
  • Å verdsette – Dette er mål-/ønskeverdien du ønsker som et resultat av målsøkingsoperasjonen.
  • Ved å bytte celle – Dette spesifiserer cellen hvis verdi må justeres for å få ønsket utgang.

Slik bruker du målsøk i Excel: Eksempel 1

For å demonstrere hvordan det fungerer i et enkelt eksempel, forestill deg at du driver en fruktbod. I skjermbildet nedenfor viser celle B11 (nedenfor) hvor mye det kostet deg å kjøpe frukt til boden din, og celle B12 viser den totale inntekten på å selge disse fruktene. Og celle B13 viser prosentandelen av overskuddet ditt (20%).

Hvis du ønsker å øke fortjenesten til «30 %», men du kan ikke øke investeringen din, så må du øke inntektene dine for å få fortjeneste. Men til hvor mye? Målsøk vil hjelpe deg å finne det.

Du bruker følgende formel i celle B13 for å beregne fortjenesteprosenten:

=(B12-B11)/B12

Nå vil du beregne fortjenestemarginen slik at fortjenesteprosenten din er 30%. Du kan gjøre dette med Goal Seek i Excel.

Det første du må gjøre er å velge cellen du vil justere verdien for. Hver gang du bruker Målsøk, må du velge en celle som inneholder en formel eller funksjon. I vårt tilfelle velger vi celle B13 fordi den inneholder formelen for å beregne prosentandelen.

Gå deretter til 'Data'-fanen, klikk på 'Hva om Analyse'-knappen i Prognose-gruppen, og velg 'Målsøk'.

Målsøk-dialogboksen vises med tre felt:

  • Sett celle – Skriv inn cellereferansen som inneholder formelen (B13). Dette er cellen som skal ha ønsket utgang.
  • Å verdsette – Skriv inn ønsket resultat du prøver å oppnå (30 %).
  • Ved å bytte celle – Sett inn cellereferansen for inngangsverdien du ønsker å endre (B12) for å komme frem til ønsket resultat. Bare klikk på cellen eller skriv inn cellereferansen manuelt. Når du velger cellen, vil Excel legge til "$"-tegnet før kolonnebokstaven og radnummeret for å gjøre det til en absolutt celle.

Når du er ferdig, klikk "OK" for å teste den.

Deretter vil en "Målsøkestatus"-dialogboks dukke opp og informere deg om den fant noen løsning som vist nedenfor. Hvis en løsning har blitt funnet, vil inngangsverdien i 'endringscellen (B12)' bli justert til en ny verdi. Det er dette vi ønsker. Så i dette eksemplet bestemte analysen at for at du skal nå målet ditt på 30 %, må du oppnå en inntekt på $1635,5 (B12).

Klikk "OK" og Excel vil endre celleverdiene eller klikk "Avbryt" for å forkaste løsningen og gjenopprette den opprinnelige verdien.

Inndataverdien (1635,5) i celle B12 er det vi fant ut ved å bruke Goal Seek for å nå målet vårt (30%).

Ting å huske når du bruker Goal Seek

  • Sett-cellen må alltid inneholde en formel som er avhengig av cellen "Ved å endre celle".
  • Du kan bare bruke målsøk på en enkelt celleinndataverdi om gangen
  • "Ved å endre celle" må inneholde en verdi og ikke en formel.
  • Hvis Goal Seek ikke kan finne den riktige løsningen, viser den den nærmeste verdien den kan produsere og forteller deg at meldingen "Målsøking har kanskje ikke funnet en løsning".

Hva du skal gjøre når Excel Goal Seek ikke fungerer

Men hvis du er sikker på at det er en løsning, er det et par ting du kan prøve for å fikse dette problemet.

Sjekk Målsøk-parametere og -verdier

Det er to ting du bør sjekke: først, sjekk om "Sett celle"-parameteren refererer til formelcellen. For det andre, sørg for at formelcellen (Sett celle) avhenger, direkte eller indirekte, av cellen i endring.

Justering av iterasjonsinnstillinger

I Excel-innstillinger kan du endre antall mulige forsøk Excel kan gjøre for å finne den riktige løsningen, samt dens nøyaktighet.

For å endre innstillingene for iterasjonsberegning, klikk "Fil" fra fanelisten. Klikk deretter "Alternativer" nederst.

I vinduet for Excel-alternativer klikker du på "Formler" i ruten til venstre.

Under "Beregningsalternativer" endrer du disse innstillingene:

  • Maksimal gjentakelse – Det indikerer antall mulige løsninger excel vil beregne; jo høyere tall, desto flere iterasjoner kan den utføre. For eksempel, hvis du setter 'Maksimal gjentakelse' til 150, tester Excel 150 mulige løsninger.
  • Maksimal endring – Det indikerer nøyaktigheten av resultatene; det mindre tallet gir høyere nøyaktighet. For eksempel, hvis inndatacelleverdien er lik "0", men målsøk slutter å beregne ved "0,001", bør endring av dette til "0,0001" løse problemet.

Øk 'Maksimal gjentakelse'-verdien hvis du vil at Excel skal teste flere mulige løsninger og reduser 'Maksimal endring'-verdien hvis du vil ha et mer nøyaktig resultat.

Skjermbildet nedenfor viser standardverdien:

Ingen sirkulære referanser

Når en formel refererer tilbake til sin egen celle, kalles den en sirkulær referanse. Hvis du vil at Excel Goal Seek skal fungere riktig, bør formler ikke bruke sirkulær referanse.

Excel målsøk eksempel 2

La oss si at du låner penger på "$25000" fra noen. De låner deg pengene til en rente på 7% per måned i en periode på 20 måneder, noe som gir tilbakebetalingen på "$1327" per måned. Men du har bare råd til å betale "$1000" per måned i 20 måneder med en rente på 7%. Goal Seek kan hjelpe deg med å finne lånebeløpet som gir en månedlig betaling (EMI) på "$1000".

Skriv inn de tre inngangsvariablene du trenger for å beregne PMT-beregningen, dvs. rente på 7 %, løpetid på 20 måneder og hovedbeløp på $25 000.

Skriv inn følgende PMT-formel i celle B5 som vil gi deg et EMI-beløp på $1 327,97.

Syntaksen til PMT-funksjonen:

=PMT(rente/12, termin, hovedstol)

Formelen:

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

Velg celle B5 (formelcelle) og gå til Data –> Hva om Analyse –> Målsøk.

Bruk disse parameterne i 'Målsøk'-vinduet:

  • Sett celle – B5 (cellen som inneholder formelen som beregner EMI)
  • Å verdsette – 1000 (formelresultatet/målet du leter etter)
  • Ved å bytte celle – B2 (dette er lånebeløpet du ønsker å endre for å oppnå målverdien)

Trykk deretter "OK" for å beholde den funnet løsningen eller "Avbryt" for å forkaste den.

Analysen forteller deg at det maksimale beløpet du kan låne er $18825 hvis du ønsker å overskride budsjettet.

Det er slik du bruker Goal Seek i Excel.