Sådan bruges målsøgning i Microsoft Excel til scenarier med hvad-hvis

Sådan bruges målsøgning i Microsoft Excel til scenarier med hvad-hvis

Excel's What-If-analyse giver dig mulighed for at se, hvordan ændring af en celle kan påvirke en formels output. Du kan bruge Excel's værktøjer til dette formål til at beregne effekten af ​​at ændre værdien af ​​en celle i en formel.





Excel har tre typer What-If-analyseværktøjer: Scenario Manager, Målsøgning og datatabel. Med Målsøgning kan du bestemme, hvilket input du har brug for for at konvertere formlen baglæns til et bestemt output.



Målsøgningsfunktionen i Excel er trial and error, så hvis den ikke producerer det, du ønsker, arbejder Excel på at forbedre denne værdi, indtil den gør det.





Hvad er formler for Excel -målsøgning?

Målsøgning i Excel opdeles i det væsentlige i tre hovedkomponenter:

  1. Indstil celle : Den celle, du vil bruge som dit mål.
  2. At værdsætte : Den værdi, du ønsker som dit mål.
  3. Ved at skifte celle : Den celle, du gerne vil ændre for at nå din målværdi.

Med disse tre indstillinger indstillet, vil Excel forsøge at forbedre værdien i den celle, du har angivet Ved at skifte celle indtil den celle, du har angivet Indstil celle når den værdi, du har bestemt i At værdsætte .



Hvis alt det lyder forvirrende, vil eksemplerne herunder give dig en god idé om, hvordan Goal Seek fungerer.

Målsøgningseksempel 1

Lad os f.eks. Sige, at du har to celler (A & B) og en tredje celle, der beregner gennemsnittet af disse to.



Antag nu, at du har en statisk værdi for A, og du ønsker at hæve gennemsnitsniveauet ved at ændre værdien for B. Ved at bruge Målsøgning kan du beregne, hvilken værdi af B der vil producere det gennemsnit, du ønsker.

Relateret: Tips til at spare tid i Microsoft Excel



windows 10 altid på 100 disk

Sådan bruges målsøgning i Excel

  1. Klik på celle i Excel C1 .
  2. Indtast følgende kode i formellinjen: | _+_ | Denne formel får Excel til at beregne gennemsnittet af værdierne i celler A1 og B1 og output det i cellen C1 . Du kan sikre dig, at din formel fungerer ved at indtaste tal i A1 og B1. C1 skal vise deres gennemsnit.
  3. I dette eksempel skal du ændre værdien af A1 celle til 16 .
  4. Vælg celle C1 , og derefter fra båndet, gå til Data fane.
  5. Under Fanen Data , Vælg Hvad-hvis-analyse og så Målsøgning . Dette åbner vinduet Målsøgning.
  6. I vinduet Målsøgning skal du indstille Indstil celle til C1 . Dette vil være din målcelle. Efter at have markeret C1, før du åbner vinduet Målsøgning, indstilles det automatisk Indstil celle .
  7. I At værdsætte , indsæt den ønskede målværdi. I dette eksempel vil vi have, at vores gennemsnit er 26, så målværdien vil også være 26.
  8. Endelig i Ved at skifte celle , vælg den celle, du vil ændre for at nå målet. I dette eksempel vil dette være celle B2 .
  9. Klik på Okay at få Goal Seek til at virke sin magi.

Når du klikker på OK, vises en dialog, der informerer dig om, at Målsøgning har fundet en løsning.

Værdien af ​​celle A1 ændres til løsningen, og dette vil overskrive de tidligere data. Det er en god idé at køre Målsøgning på en kopi af dit datablad for at undgå at miste dine originale data.

Relaterede: Skøre Excel -formler, der gør fantastiske ting

Målsøgningseksempel 2

Målsøgning kan være nyttig, når du bruger den i virkelige scenarier. For at bruge Goal Seek til sit fulde potentiale skal du dog have en ordentlig formel til at begynde med.

For dette eksempel, lad os tage en lille banksag. Antag, at du har en bankkonto, der giver dig en årlig rente på 4% af de penge, du har på din konto.

Ved hjælp af What-If-analyse og målsøgning kan du beregne, hvor mange penge du skal have på din konto for at få en vis månedlig rentebetaling.

For dette eksempel, lad os sige, at vi ønsker at få $ 350 hver måned fra rentebetalinger. Sådan kan du beregne det:

underretning om påstået krænkelse af ophavsretten foretaget under DMCA
  1. I cellen A1 type Balance .
  2. I cellen A2 type Årlig sats .
  3. I cellen A4 type Månedlig fortjeneste .
  4. I cellen B2 type 4% .
  5. Vælg celle B4 og i formellinjen skal du indtaste formlen herunder: | _+_ | Det månedlige overskud svarer til kontosaldoen ganget med den årlige rente og derefter divideret med antallet af måneder i et år, 12 måneder.
  6. Gå til Data fane, klik på Hvad-hvis-analyse, og vælg derefter Målsøgning .
  7. I Målsøgning vindue, type B4 i Indstil celle .
  8. Skriv 350 i At værdsætte celle. (Dette er det månedlige overskud, du vil have)
  9. Type B1 i Ved at skifte celle . (Dette ændrer saldoen til en værdi, der giver $ 350 månedligt)
  10. Klik på Okay . Det Status for målsøgning dialog vil dukke op.

I dialogboksen Status for målsøgning kan du se, at Målsøgning har fundet en løsning i celle B4. I celle B1 kan du se løsningen, som burde være 105.000.

Krav til målsøgning

Med Målsøgning kan du se, at det kun kan ændre én celle for at nå målværdien. Det betyder, at Målsøgning ikke kan løse og nå en løsning, hvis den har mere end én variabel.

I Excel kan du stadig løse mere end én variabel, men du skal bruge et andet værktøj kaldet Solver. Du kan læse mere om Excel's Solver ved at læse vores artikel om Sådan bruges Excel's Solver .

Begrænsninger for målsøgning

En målsøgningsformel bruger prøve-og-forbedringsprocessen til at nå den optimale værdi, hvilket kan give nogle problemer, når formlen producerer en udefineret værdi. Sådan kan du selv teste det:

  1. I cellen A1 , type 3 .
  2. Vælg celle C1 , og indtast derefter formlen nedenfor i formellinjen: | _+_ | Denne formel deler en med A1-5, og hvis A1-5 tilfældigvis er 0, vil resultatet være en udefineret værdi.
  3. Gå til Data fane, klik Hvad-hvis-analyse og vælg derefter Målsøgning .
  4. I Indstil celle , type C1 .
  5. I At værdsætte , type 1 .
  6. Endelig i Ved at skifte celle type A1 . (Dette ændrer A1 -værdien til at nå 1 i C1)
  7. Klik på Okay .

Dialogboksen Målsøgningsstatus fortæller dig, at den muligvis ikke har fundet en løsning, og du kan kontrollere, at A1-værdien er langt væk.

Nu ville løsningen på denne målsøgning være at have 6 i A1. Dette vil give 1/1, hvilket svarer til 1. Men på et tidspunkt under forsøgs- og forbedringsprocessen forsøger Excel 5 i A1, hvilket giver 1/0, hvilket er udefineret, hvilket dræber processen.

En vej udenom dette målsøgningsproblem er at have en anden startværdi, en der kan undgå det udefinerede i forsøgs- og forbedringsprocessen.

Som et eksempel, hvis du ændrer A1 til et hvilket som helst tal større end 5 og derefter gentager de samme trin til Målsøgning for 1, bør du få det korrekte resultat.

ændre itunes backup placering windows 10

Gør beregningerne mere effektive med målsøgning

Hvad-hvis-analyse og målsøgning kan fremskynde dine beregninger med dine formler med en stor målestok. At lære de rigtige formler er et andet vigtigt skridt mod at blive en excel -mester.

Del Del Tweet E -mail 15 Excel -formler, der hjælper dig med at løse problemer i det virkelige liv

Excel er ikke kun for virksomheder. Her er flere Microsoft Excel -formler, der hjælper dig med at løse komplekse daglige problemer.

Læs Næste Relaterede emner
  • Produktivitet
  • Regneark
  • Microsoft Excel
  • Microsoft Office -tip
  • Dataanalyse
Om forfatteren Amir M. Efterretning(39 artikler udgivet)

Amir er en farmaceutstuderende med en passion for teknologi og spil. Han kan lide at spille musik, køre bil og skrive ord.

Mere fra Amir M. Bohlooli

Abonner på vores nyhedsbrev

Tilmeld dig vores nyhedsbrev for at få tekniske tips, anmeldelser, gratis e -bøger og eksklusive tilbud!

Klik her for at abonnere