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 fanen Data, i menyen Hva-Hvis-analyse :
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:
- Klikk Data> Hva-Hvis analyse> Målsøk . Du ser dette vinduet:
- Sett "lik" delen av ligningen din i feltet Set Cell . Dette er nummeret som Excel vil prøve å optimalisere. I vårt tilfelle er det totalt antall salgstall i celle B5.
- Skriv inn målverdien din i feltet Til verdi . Vi leter etter totalt 250 000 solgte enheter, så vi legger "250 000" i dette feltet.
- Fortell Excel hvilken variabel som skal løses i feltet Ved å endre celle . 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å:
- Trykk OK for å løse målet ditt. Når det ser bra ut, trykk bare OK . Excel vil gi 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 å endre 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 allerede har 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øre 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 en 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 virker 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.
Gå til File> Options> Add-Ins . Deretter klikker du på Gå ved siden av Administrer: Excel-tillegg .
Hvis denne dropdownen sier noe annet enn "Excel-tillegg, må du endre det:
I det resulterende vinduet får du se noen alternativer. Pass på at boksen ved siden av Solver Add-In er merket, og trykk OK .
Du vil nå se Solver- knappen i analysegruppen på fanen Data :
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.
- Klikk Data> Løsning . 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.)
- 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 av maksimere, minimere eller en bestemt verdi.
Hvis du velger Max, justerer Excel dine variabler for å få størst mulig antall i målcellen. Min er motsatt: Solver vil minimere målnummeret. Verdi av lar deg spesifisere et bestemt nummer for Solver å lete etter. - Velg de variable cellene som Excel kan endre. De variable cellene er satt med feltet By Changing Variable Cells . 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.
- 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.
- Når all denne informasjonen er på plass, trykk Løs 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 ved siden av begrensningslisten. Du får et nytt vindu. Velg cellen (eller cellene) som skal begrenses i feltet Cell Reference, og 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 området du velger for Cell Reference, 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 velge flere celler for Cell Reference. 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.
- Job 2 må være større enn åtte timer .
- Job 5 må være mindre enn elleve timer .
- De totale arbeidstidene 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:
Først merk at jeg har opprettet en kopi av bordet, slik at vi ikke overskriver 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 det 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øs 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 å beholde de nye verdiene, kontroller at Keep Solver Solution er merket og trykk 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 trykk OK .
Rapportene genereres på nye ark i arbeidsboken din og gir deg informasjon om prosessen som tilleggsinnlegget Solver 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 trykk Alternativer- knappen. Du kan fortelle Excel om heltalloptimalitet, angi beregningstidsbegrensninger (nyttig for massive datasett), og justere hvordan GRG og Evolutionary løsningsmetodene går til å gjøre 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 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!