Produktivitet

Slik bruker du Excels målsøk og -oppløsning for å løse for ukjente variabler

Slik bruker du Excels målsøk og -oppløsning for å løse for ukjente variabler / Produktivitet

Excel er svært dyktig når du har alle dataene du trenger for beregningene dine.

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

Med Målsøk og Solver-tillegget kan det. Og vi skal vise deg hvordan. Les videre for en full guide om 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-Hvis analyse Meny:

For dette eksempelet bruker vi et veldig enkelt sett med tall. Vi har tre fjerdedeler av salgsnumre og et årlig mål. Vi kan bruke Målsøk for å finne ut hva tallene må være i 4. kvartal for å gjøre målet.

Som du kan se, er det nåværende salgssummen 114.706 enheter. Hvis vi ønsker å selge 250 000 innen årets slutt, hvor mange trenger vi å selge i 4. kvartal? Excels målsøk vil fortelle oss.

Slik bruker du målsøk, trinnvis:

  1. Klikk Data> Hva-Hvis analyse> Målsøk. Du ser dette vinduet:
  2. Sett “er lik” en del av ligningen din i Sett Cell felt. Dette er nummeret som Excel vil prøve å optimalisere. I vårt tilfelle er det totalt antall salgstall i celle B5.
  3. Skriv inn målverdien din i Å verdsette felt. Vi leter etter totalt 250.000 solgte enheter, så vi legger “250000” i dette feltet.
  4. Fortell Excel hvilken variabel som skal løses for i Ved å bytte celle felt. Vi ønsker å se hva vårt salg i 4. kvartal må være. Så vi forteller Excel for å løse for celle D2. Det ser slik ut når det er klart å gå:
  5. Truffet OK å løse for målet ditt. Når det ser bra ut, bare slå OK. Excel vil gi deg beskjed når Goal Seek har funnet en løsning.
  6. Klikk OK igjen, og du vil se verdien som løser ligningen din i cellen du valgte Ved å bytte celle.

I vårt tilfelle er løsningen 135.294 enheter. Selvfølgelig kunne vi nettopp funnet at ved å trekke løpende summen fra det årlige målet. Men Målsøk 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 ide å Kjør Målsøk på en kopi av dataene dine. Det er også en god idé å lage et notat om dine kopierte data som den ble generert ved hjelp av målsøk. Du ønsker ikke å forvirre den for nåværende, nøyaktige data.

Så målsøk er en nyttig Excel-funksjon 16 Excel-formler som vil hjelpe deg med å løse virkelige livsproblemer 16 Excel-formler som vil hjelpe deg med å løse virkelige problemer. Det riktige verktøyet er halvparten av arbeidet. Excel kan løse beregninger og behandle data raskere enn du kan finne kalkulatoren din. Vi viser deg viktige Excel-formler og demonstrerer hvordan du bruker dem. Les mer, men det er ikke så imponerende. La oss se på et verktøy som er mye mer interessant: Solver-tillegget.

Hva gjør Excel Solver?

Kort sagt, Solver er som a multivariate versjon av målsøk. Det tar en målvariabel og justerer en rekke andre variabler til den får svaret du vil ha.

Den kan løse for en maksimumsverdi av et tall, en minimumsverdi av 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, vil Solver ta hensyn til det.

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

La oss se på å laste inn Solver-tillegget, og hoppe inn i hvordan du bruker Solver i Excel 2016.

Slik legger du inn løsningsoppdateringen

Excel har ikke Solver som standard. Det er et tillegg, slik som andre kraftige Excel-funksjoner, Kraft opp Excel med 10 tillegg, for å behandle, analysere og visualisere data som en proffs-opp Excel med 10 tillegg til å behandle, analysere og visualisere data som en Pro Vanilla Excel er fantastisk, men du kan gjøre det enda sterkere med tillegg. Uansett hvilke data du trenger å behandle, er det sjansene for at noen opprettet en Excel-app for den. Her er et utvalg. Les mer, du må laste det først. Heldigvis er det allerede på datamaskinen.

Drar mot Fil> Valg> Add-Ins. Klikk deretter på Gå ved siden av Administrer: Excel-tillegg.

Hvis denne dropdownen sier noe annet enn “Excel-tillegg,” du må endre det:

I det resulterende vinduet får du se noen alternativer. Pass på at boksen ved siden av Løsningstilføyning er merket og treff OK.

Nå ser du Solver knappen i Analyse gruppe av Data tab:

Hvis du allerede har brukt Data Analysis Toolpak Slik gjør du grunnleggende dataanalyse i Excel Hvordan gjøre grunnleggende dataanalyse i Excel Excel er ikke ment for dataanalyse, men det kan fortsatt håndtere statistikk. Vi viser deg hvordan du bruker Data Analysis Toolpak-tillegget til å kjøre Excel-statistikk. Les mer, du vil se knappen Dataanalyse. Hvis ikke, vises Solver av seg selv.

Nå som du har lastet inn tillegget, la oss se 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 hver av trinnene.

  1. Klikk Data> Solver. Du får se vinduet Solver Parameters nedenfor. (Hvis du ikke ser oppløserknappen, kan du se den forrige delen om hvordan du legger inn tilleggsprogrammet Solver.)
  2. Sett ditt cellemål og fortell Excel ditt mål. Målet er øverst i Solver-vinduet, og det har to deler: Målcellen og et valg om å maksimere, minimere eller en bestemt verdi.

    Hvis du velger Max, Excel vil justere variablene for å få størst mulig antall i målcellen. min er motsatt: Solver vil minimere målnummeret. Verdien av Lar deg spesifisere et bestemt nummer for Solver å lete etter.
  3. Velg de variable cellene som Excel kan endre. De variable cellene er satt med Ved å endre variabelceller felt. Klikk pilen ved siden av feltet, klikk deretter og dra for å velge de cellene som Solver skal fungere med. Merk at disse er alle cellene som kan variere. Hvis du ikke vil at en celle skal endres, må du ikke velge den.
  4. Angi begrensninger på flere eller individuelle variabler. Til slutt kommer vi til begrensningene. Det er her Solver er veldig kraftig. I stedet for å endre noen av de variable cellene til et hvilket som helst nummer det vil, kan du angi begrensninger som må oppfylles. For detaljer, se delen om hvordan du angir begrensninger nedenfor.
  5. Når all denne informasjonen er på plass, trykk Løse for å få svaret ditt. Excel vil oppdatere dataene dine for å inkludere de nye variablene (det er derfor vi anbefaler at du oppretter en kopi av dataene dine først).

Du kan også generere rapporter, som vi vil se nærmere på i løsningseksempelet nedenfor.

Slik stiller du inn begrensninger i løsningen

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

For å legge til en begrensning, klikk på Legg til knappen ved siden av begrensningslisten. Du får et nytt vindu. Velg cellen (eller cellene) som skal begrenses i Cellreferanse feltet, velg deretter en operatør.

Her er de tilgjengelige operatørene:

  • <= (mindre enn eller lik)
  • = (lik)
  • => (større enn eller lik)
  • int (må være et heltall)
  • bin (må være enten 1 eller 0)
  • AllDifferent

AllDifferent er litt forvirrende. Det spesifiserer at hver celle i det området du velger for Cellreferanse må være et annet nummer. Men det 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 vil at seks variabler skal ha verdier over 10, kan du for eksempel 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 avkrysningsruten i hovedoppløsningsvinduet for å sikre at alle verdiene du ikke angir begrensninger for, er ikke-negative. Hvis du vil at variablene dine skal gå negative, fjerner du merket i denne boksen.

Et løsningseksempel

For å se hvordan alt dette fungerer, bruker vi innlasting til Solver for å gjøre en rask beregning. Her er dataene vi starter med:

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

Her er de begrensningene vi skal bruke:

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

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

Slik kan vi sette opp det i Solver:

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

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

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

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

For å holde de nye verdiene, sørg for Hold Solver Solution er merket og truffet OK.

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

Rapportene genereres på nye ark i arbeidsboken din og gir deg informasjon om prosessen som 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 noen nyttige rapporteringsinformasjon i disse nye regnearkene. Bare klikk på + knappen på siden av en rapport for å få mer informasjon:

Løsne avanserte alternativer

Hvis du ikke vet mye om statistikk, kan du ignorere Solver's avanserte alternativer og bare kjøre den som-er. Men hvis du kjører store, komplekse beregninger, vil du kanskje se på dem.

Den mest åpenbare er løsningsmetoden:

Du kan velge mellom GRG Nonlinear, Simplex LP, og Evolutionary. Excel gir en enkel forklaring om når du skal bruke hver enkelt. En bedre forklaring krever litt kunnskap om statistikk Lær statistikk gratis med disse 6 ressursene Lær statistikk gratis med disse 6 ressursene Statistikk har et rykte om et emne som er vanskelig å forstå. Men lære av riktig ressurs hjelper deg med å forstå undersøkelsesresultater, valgrapporter og statistikkoppgaver på kort tid. Les mer og regresjon.

For å justere flere innstillinger, bare klikk på alternativer knapp. Du kan fortelle Excel om heltalloptimalitet, angi beregningstidsbegrensninger (nyttig for massive datasett), og justere hvordan GRG og Evolutionary løsningsmetoder går om å gjøre sine beregninger.

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

Målsøk og løser: Tar Excel til neste nivå

Nå som du er komfortabel med grunnleggende om å løse for 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 legger stor kraft til Excels beregningsevner. Slik beregner du grunnleggende statistikk i Excel: En nybegynnersveiledning Slik beregner du grunnleggende statistikk i Excel: En nybegynnersveiledning Microsoft Excel kan gjør statistikk! Du kan beregne prosenter, gjennomsnitt, standardavvik, standardfeil og studentens T-tester. Les mer .

Det er bare et spørsmål om å bli komfortabel med dem. Jo mer du bruker dem, jo ​​mer nyttig blir de.

Bruker du Målsøk eller Solver i regnearkene dine? Hvilke andre tips kan du sørge for å få de beste svarene ut av dem? Del dine tanker i kommentarene nedenfor!

Utforsk mer om: Microsoft Excel, Microsoft Office Tips, regneark.