Slik bruker du Excel sitt målsøk og løsning for å løse for ukjente variabler

Excel er veldig dyktig når du har alle dataene du trenger for beregningene dine.

Men hadde det ikke vært fint om det kunne løse for ukjente variabler ?

Med Goal Seek og Solver-tillegget kan det. Og vi viser deg hvordan. Les videre for en fullstendig guide til hvordan du løser for en enkelt celle med Målsøk eller en mer komplisert ligning med Solver.

Slik bruker du målsøk i Excel

Målsøk er allerede innebygd i Excel. Det er under Data -fanen i Hva-om-analyse Meny:

I dette eksemplet bruker vi et veldig enkelt tallsett. Vi har tre fjerdedels salgstall og et årlig mål. Vi kan bruke Goal Seek til å finne ut hva tallene må være i 4. kvartal for å nå målet.

Som du kan se er den nåværende salgssummen 114 706 enheter. Hvis vi ønsker å selge 250 000 innen utgangen av året, hvor mange trenger vi å selge i 4. kvartal? Excel's Goal Seek vil fortelle oss.

Slik bruker du Målsøk, trinn for trinn:

Klikk Data> Hva-om-analyse> Målsøk . Du ser dette vinduet:
Sett 'lik' -delen av ligningen din i Sett celle felt. Dette er tallet som Excel vil prøve å optimalisere. I vårt tilfelle er det den totale summen av salgstallene våre i celle B5.
Skriv inn målverdien din i Å verdsette felt. Vi leter etter totalt 250.000 solgte enheter, så vi legger '250.000' i dette feltet.
Fortell Excel hvilken variabel du skal løse for i Ved å bytte celle felt. Vi ønsker å se hva salget vårt i 4. kvartal må være. Så vi vil fortelle Excel om å løse for celle D2. Det vil se slik ut når det er klart:
Truffet OK å løse for målet ditt. Når det ser bra ut, er det bare å slå OK . Excel gir deg beskjed når Goal Seek har funnet en løsning.
Klikk OK igjen, og du vil se verdien som løser ligningen din i cellen du valgte for Ved å bytte celle .

I vårt tilfelle er løsningen 135 294 enheter. Selvfølgelig kunne vi nettopp funnet det ved å trekke den løpende summen fra det årlige målet. Men Goal Seek kan også brukes på en celle som har allerede data i den . Og det er mer nyttig.

Vær oppmerksom på at Excel overskriver våre tidligere data. Det er en god idé å kjør Målsøk på en kopi av dataene dine . Det er også en god idé å notere de kopierte dataene at de ble generert ved hjelp av Målsøk. Du vil ikke forvirre det for nåværende, nøyaktige data.

itunes endre sikkerhetskopieringsplassering windows 10

Så Målsøk er et nyttig Excel -funksjon , men det er ikke så imponerende. La oss se på et verktøy som er mye mer interessant: Solver-tillegget.

Hva gjør Excel's Solver?

Kort sagt, Solver er som en multivariat versjon av Målsøk . Den tar en målvariabel og justerer en rekke andre variabler til den får svaret du ønsker.

Den kan løse en maksimal verdi for et tall, en minimumsverdi for et tall eller et eksakt tall.

Og det fungerer innenfor begrensninger, så hvis en variabel ikke kan endres, eller bare kan variere innenfor et spesifisert område, tar Solver hensyn til det.

Det er en fin måte å løse flere ukjente variabler i Excel. Men å finne og bruke det er ikke enkelt.

La oss ta en titt på å laste inn Solver-tillegget, og deretter hoppe til hvordan du bruker Solver i Excel 2016.

Slik laster du inn Solver-tillegget

Excel har ikke Solver som standard. Det er et tillegg, så som andre kraftige Excel-funksjoner må du laste det først. Heldigvis er den allerede på datamaskinen din.

Setter kursen mot Fil> Alternativer> Tillegg . Klikk deretter på Gå ved siden av Administrer: Excel-tillegg .

Hvis denne rullegardinmenyen sier noe annet enn 'Excel-tillegg', må du endre den:

I det resulterende vinduet ser du noen alternativer. Sørg for boksen ved siden av Løser-tillegg blir sjekket og trykket OK .

Du vil nå se Løser -knappen i Analyse gruppen av Data fane:

Hvis du allerede har brukt Data Analysis Toolpak , ser du knappen Dataanalyse. Hvis ikke, vises Solver av seg selv.

Nå som du har lastet tillegget, la oss ta en titt på hvordan du bruker det.

Slik bruker du Solver i Excel

Det er tre deler til enhver Solver -handling: målet, de variable cellene og begrensningene. Vi går gjennom hvert av trinnene.

Klikk Data> Løser . Du vil se vinduet Solver Parameters nedenfor. (Hvis du ikke ser løsningsknappen, kan du se den forrige delen om hvordan du laster inn Solver-tillegget.)
Angi cellemål og fortell målet ditt i Excel. Målet er øverst i Solver -vinduet, og det har to deler: målcellen og et valg om maksimering, minimering eller en bestemt verdi. Hvis du velger Maks , Excel vil justere variablene dine for å få det største antallet mulige i målcellen. Min er det motsatte: Solver vil minimere objektivtallet. Verdien av lar deg spesifisere et spesifikt nummer for Solver å se etter.
Velg de variable cellene som Excel kan endre. De variable cellene er satt med Ved å endre variable celler felt. Klikk på pilen ved siden av feltet, klikk deretter og dra for å velge cellene som Solver skal jobbe med. Vær oppmerksom på at disse er alle cellene som kan variere. Hvis du ikke vil at en celle skal endres, ikke velg den.
Sett begrensninger på flere eller individuelle variabler. Til slutt kommer vi til begrensningene. Det er her Solver er virkelig kraftig. I stedet for å endre noen av de variable cellene til et hvilket som helst tall den ønsker, kan du angi begrensninger som må oppfylles. For detaljer, se avsnittet om hvordan du angir begrensninger nedenfor.
Når all denne informasjonen er på plass, trykker du på Løse for å få svaret ditt. Excel vil oppdatere dataene dine for å inkludere de nye variablene (det er derfor vi anbefaler at du lager en kopi av dataene dine først).

Du kan også generere rapporter, som vi ser kort på i vårt Solver -eksempel nedenfor.

Hvordan sette begrensninger i Solver

Du kan fortelle Excel at en variabel må være større 200. Når du prøver forskjellige variabelverdier, vil Excel ikke gå under 201 med den aktuelle variabelen.

Klikk på for å legge til en begrensning Legge til -knappen ved siden av begrensningslisten. Du får et nytt vindu. Velg cellen (eller cellene) som skal begrenses i Cellehenvisning feltet, og velg deretter en operator.

Her er de tilgjengelige operatørene:

<= (mindre enn eller lik)
= (lik)
=> (større enn eller lik)
int (må være et heltall)
er (må være enten 1 eller 0)
Alt forskjellig

Alt forskjellig er litt forvirrende. Den spesifiserer at hver celle i området du velger for Cellehenvisning må være et annet tall. Men den spesifiserer også at de må være mellom 1 og antall celler. Så hvis du har tre celler, vil du ende opp med tallene 1, 2 og 3 (men ikke nødvendigvis i den rekkefølgen)

Til slutt legger du til verdien for begrensningen.

Det er viktig å huske at du kan velg flere celler for cellereferanse. Hvis du for eksempel vil at seks variabler skal ha verdier over 10, kan du velge dem alle og fortelle Solver at de må være større enn eller lik 11. Du trenger ikke legge til en begrensning for hver celle.

Du kan også bruke avmerkingsboksen i hovedvinduet for å sikre at alle verdiene du ikke angav begrensninger for, er ikke-negative. Hvis du vil at variablene skal bli negative, fjerner du merket for denne boksen.

Et løsningseksempel

For å se hvordan alt dette fungerer, bruker vi Solver-tillegget for å foreta en rask beregning. Her er dataene vi starter med:

I den har vi fem forskjellige jobber, som hver betaler en annen rate. Vi har også antall timer en teoretisk arbeider har jobbet på hver av disse jobbene i en gitt uke. Vi kan bruke Solver-tillegget for å finne ut hvordan du maksimerer den totale lønnen mens du holder visse variabler innenfor noen begrensninger.

Her er begrensningene vi skal bruke:

Ingen jobber kan falle under fire timer.
Jobb 2 må være mer enn åtte timer .
Jobb 5 må være mindre enn elleve timer .
Den totale arbeidstiden må være lik 40 .

Det kan være nyttig å skrive ut begrensningene dine som dette før du bruker Solver.

Slik setter vi opp det i Solver:

Vær først oppmerksom på det Jeg har laget en kopi av tabellen så vi overskriver ikke den opprinnelige, som inneholder vår nåværende arbeidstid.

Og for det andre, se at verdiene i begrensningene større enn og mindre enn er en høyere eller lavere enn det jeg nevnte ovenfor. Det er fordi det ikke er større eller mindre alternativer. Det er bare større enn-eller-like-til og mindre-enn-eller-like-til.

La oss slå Løse og se hva som skjer.

Solver fant en løsning! Som du kan se til venstre i vinduet ovenfor, har inntektene våre økt med $ 130. Og alle begrensningene er oppfylt.

hvordan får jeg lyden tilbake på Windows 10

Sørg for å beholde de nye verdiene Behold Solver Solution blir sjekket og truffet OK .

Hvis du vil ha mer informasjon, kan du velge en rapport fra høyre side av vinduet. Velg alle rapportene du vil ha, fortell Excel om du vil at de skal skisseres (jeg anbefaler det), og trykk på OK .

Rapportene genereres på nye ark i arbeidsboken din og gir deg informasjon om prosessen Solver-tillegget gikk gjennom for å få svaret ditt.

I vårt tilfelle er rapportene ikke veldig spennende, og det er ikke mye interessant informasjon der. Men hvis du kjører en mer komplisert Solver -ligning, kan du finne nyttig rapporteringsinformasjon i disse nye regnearkene. Bare klikk på + -knappen på siden av en rapport for å få mer informasjon:

Solver Avanserte alternativer

Hvis du ikke vet mye om statistikk, kan du ignorere Solvers avanserte alternativer og bare kjøre den som den er. Men hvis du kjører store, komplekse beregninger, kan det være lurt å se nærmere på dem.

Den mest åpenbare er løsningsmetoden:

Du kan velge mellom GRG Nonlinear, Simplex LP og Evolutionary. Excel gir en enkel forklaring på når du skal bruke hver enkelt. En bedre forklaring krever litt kunnskap om statistikk og regresjon.

For å justere flere innstillinger, trykker du bare på Alternativer knapp. Du kan fortelle Excel om heltalloptimalitet, angi tidsbegrensninger for beregning (nyttig for massive datasett), og justere hvordan GRG- og evolusjonære løsningsmetoder gjør beregningene sine.

Igjen, hvis du ikke vet hva noe av dette betyr, ikke bekymre deg for det. Hvis du vil vite mer om hvilken løsningsmetode du skal bruke, har Engineer Excel en god artikkel som legger det ut for deg . Hvis du vil ha maksimal nøyaktighet, er sannsynligvis Evolutionary en god vei å gå. Bare vær oppmerksom på at det vil ta lang tid.

Målsøk og løsning: Ta Excel til neste nivå

Nå som du er komfortabel med det grunnleggende om å løse ukjente variabler i Excel, er en helt ny verden av regnearkberegning åpen for deg.

Målsøk kan hjelpe deg med å spare tid ved å gjøre noen beregninger raskere, og Solver tilfører enorm mengde strøm Excel beregner evner .

Det er bare å bli komfortabel med dem. Jo mer du bruker dem, jo mer nyttige blir de.

Bruker du Goal Search eller Solver i regnearkene dine? Hvilke andre tips kan du gi for å få de beste svarene ut av dem? Del tankene dine i kommentarene nedenfor!

Dele Dele kvitring E -post 5 tips for å overbelaste VirtualBox Linux -maskinene dine

Lei av den dårlige ytelsen som tilbys av virtuelle maskiner? Her er hva du bør gjøre for å øke VirtualBox -ytelsen.

Les neste Relaterte temaer

Produktivitet
Regneark
Microsoft Excel
Microsoft Office -tips

Om forfatteren Deretter Albright(506 artikler publisert)

Dann er en innholdsstrategi og markedsføringskonsulent som hjelper bedrifter med å generere etterspørsel og kundeemner. Han blogger også om strategi og innholdsmarkedsføring på dannalbright.com.

Mer fra Dann Albright

Abonner på vårt nyhetsbrev

Bli med i vårt nyhetsbrev for tekniske tips, anmeldelser, gratis ebøker og eksklusive tilbud!

Klikk her for å abonnere