Sådan bruges Excel's målsøgning og løsning til at løse for ukendte variabler

Excel er meget dygtig, når du har alle de data, du har brug for til dine beregninger.

Men ville det ikke være rart, hvis det kunne løse for ukendte variabler ?

Med Målsøgning og tilføjelsesprogrammet Solver kan det. Og vi viser dig hvordan. Læs videre for en komplet guide til, hvordan du løser for en enkelt celle med Målsøgning eller en mere kompliceret ligning med Solver.

Sådan bruges målsøgning i Excel

Målsøgning er allerede indbygget i Excel. Det er under Data fanen i Hvad-hvis-analyse menu:

I dette eksempel bruger vi et meget simpelt sæt tal. Vi har tre fjerdedels salgstal og et årligt mål. Vi kan bruge Goal Seek til at finde ud af, hvad tallene skal være i 4. kvartal for at nå målet.

Som du kan se, er det nuværende salgssum 114,706 enheder. Hvis vi vil sælge 250.000 ved årets udgang, hvor mange skal vi så sælge i 4. kvartal? Excel's Målsøgning vil fortælle os det.

Sådan bruges Målsøgning trin for trin:

Klik på Data> Hvad-hvis-analyse> Målsøgning . Du får vist dette vindue:
Sæt den 'lige' del af din ligning i Indstil celle Mark. Dette er det tal, som Excel vil forsøge at optimere. I vores tilfælde er det den samlede sum af vores salgstal i celle B5.
Indtast din målværdi i At værdsætte Mark. Vi leder efter i alt 250.000 solgte enheder, så vi sætter '250.000' i dette felt.
Fortæl Excel, hvilken variabel der skal løses i Ved at skifte celle Mark. Vi vil se, hvad vores salg i 4. kvartal skal være. Så vi fortæller Excel at løse for celle D2. Det ser sådan ud, når det er klar til at gå:
Hit Okay at løse for dit mål. Når det ser godt ud, skal du bare slå Okay . Excel giver dig besked, når Goal Seek har fundet en løsning.
Klik på Okay igen, og du vil se den værdi, der løser din ligning i den celle, du valgte til Ved at skifte celle .

I vores tilfælde er løsningen 135.294 enheder. Det kunne vi selvfølgelig lige have fundet ud af ved at trække den løbende sum fra det årlige mål. Men Målsøgning kan også bruges på en celle, der har allerede data i den . Og det er mere nyttigt.

Bemærk, at Excel overskriver vores tidligere data. Det er en god idé at køre Målsøgning på en kopi af dine data . Det er også en god idé at notere dine kopierede data, at de blev genereret ved hjælp af Målsøgning. Du vil ikke forvirre det for aktuelle, nøjagtige data.

hvad skal man gøre med gamle harddiske

Så Målsøgning er en nyttig Excel -funktion , men det er ikke så imponerende. Lad os se på et værktøj, der er meget mere interessant: tilføjelsesprogrammet Solver.

Hvad gør Excel's Solver?

Kort sagt er Solver som en multivariat version af Målsøgning . Det tager en målvariabel og justerer et antal andre variabler, indtil den får det svar, du ønsker.

Det kan løse en maksimal værdi for et tal, en minimumsværdi for et tal eller et præcist tal.

Og det fungerer inden for begrænsninger, så hvis en variabel ikke kan ændres, eller kun kan variere inden for et bestemt område, tager Solver det i betragtning.

Det er en fantastisk måde at løse flere ukendte variabler i Excel. Men at finde og bruge det er ikke ligetil.

Lad os tage et kig på at indlæse Solver-tilføjelsen, og derefter springe ind i, hvordan du bruger Solver i Excel 2016.

Sådan indlæses tilføjelsesprogrammet Solver

Excel har som standard ikke Solver. Det er et tilføjelsesprogram, så ligesom andre kraftfulde Excel-funktioner skal du først indlæse det. Heldigvis er det allerede på din computer.

Gå til Fil> Indstillinger> Tilføjelser . Klik derefter på Gå ved siden af Administrer: Excel-tilføjelsesprogrammer .

Hvis denne rullemenu siger noget andet end 'Excel-tilføjelsesprogrammer', skal du ændre det:

I det resulterende vindue ser du et par muligheder. Sørg for boksen ved siden af Tilføjelsesprogram til løsning er markeret, og ramt Okay .

Du vil nu se Løser knappen i Analyse gruppen af Data fane:

Hvis du allerede har brugt Dataanalyse Toolpak , ser du knappen Dataanalyse. Hvis ikke, vises Solver af sig selv.

Nu hvor du har indlæst tilføjelsesprogrammet, lad os se på, hvordan du bruger det.

Sådan bruges Solver i Excel

Der er tre dele til enhver Solver -handling: målet, de variable celler og begrænsningerne. Vi går gennem hvert trin.

Klik på Data> Solver . Du kan se vinduet Solver Parameters nedenfor. (Hvis du ikke kan se knappen Solver, kan du se det foregående afsnit om, hvordan du indlæser Solver-tilføjelsesprogrammet.)
Indstil dit cellemål og fortæl Excel dit mål. Målet er øverst i Solver -vinduet, og det har to dele: målcellen og et valg om maksimering, minimering eller en bestemt værdi. Hvis du vælger Maks , Excel vil justere dine variabler for at få det størst mulige antal i din objektcelle. Min er det modsatte: Solver vil minimere det objektive antal. Værdi af lader dig angive et specifikt nummer, som Solver skal kigge efter.
Vælg de variable celler, som Excel kan ændre. De variable celler indstilles med Ved at ændre variable celler Mark. Klik på pilen ud for feltet, og klik derefter på og træk for at vælge de celler, Solver skal arbejde med. Bemærk, at disse er alle cellerne der kan variere. Hvis du ikke vil have, at en celle skal ændres, skal du ikke markere den.
Angiv begrænsninger for flere eller individuelle variabler. Endelig kommer vi til begrænsningerne. Det er her, Solver er virkelig kraftfuld. I stedet for at ændre en af de variable celler til et hvilket som helst tal, kan du angive begrænsninger, der skal opfyldes. For detaljer, se afsnittet om, hvordan du angiver begrænsninger nedenfor.
Når alle disse oplysninger er på plads, skal du trykke på Løse for at få dit svar. Excel opdaterer dine data til at omfatte de nye variabler (derfor anbefaler vi, at du først opretter en kopi af dine data).

Du kan også generere rapporter, som vi ser kort på i vores Solver -eksempel herunder.

Sådan indstilles begrænsninger i Solver

Du kan fortælle Excel, at en variabel skal være større 200. Når du prøver forskellige variabelværdier, vil Excel ikke gå under 201 med den pågældende variabel.

Hvis du vil tilføje en begrænsning, skal du klikke på Tilføje knappen ved siden af begrænsningslisten. Du får et nyt vindue. Vælg cellen (eller cellerne), der skal begrænses i Cellereference feltet, og vælg derefter en operator.

Her er de tilgængelige operatører:

<= (mindre end eller lig med)
= (svarende til)
=> (større end eller lig med)
int (skal være et heltal)
er (skal enten være 1 eller 0)
Alle forskellige

Alle forskellige er lidt forvirrende. Det angiver, at hver celle i det område, du vælger til Cellereference skal være et andet nummer. Men det angiver også, at de skal være mellem 1 og antallet af celler. Så hvis du har tre celler, ender du med tallene 1, 2 og 3 (men ikke nødvendigvis i den rækkefølge)

Tilføj endelig værdien for begrænsningen.

Det er vigtigt at huske, at du kan vælge flere celler til cellereference. Hvis du f.eks. Vil have seks variabler til at have værdier over 10, kan du vælge dem alle og fortælle Solver, at de skal være større end eller lig med 11. Du behøver ikke tilføje en begrænsning for hver celle.

Du kan også bruge afkrydsningsfeltet i hovedvinduet i Solver for at sikre, at alle de værdier, du ikke har angivet begrænsninger for, ikke er negative. Hvis du vil have dine variabler til at blive negative, skal du fjerne markeringen i dette felt.

Et løsningseksempel

For at se, hvordan alt dette fungerer, bruger vi tilføjelsesprogrammet Solver til at foretage en hurtig beregning. Her er de data, vi starter med:

I den har vi fem forskellige job, der hver især betaler en anden sats. Vi har også det antal timer, en teoretisk arbejdstager har arbejdet på hvert af disse job i en given uge. Vi kan bruge tilføjelsesprogrammet Solver til at finde ud af, hvordan du maksimerer den samlede løn, samtidig med at visse variabler holdes inden for nogle begrænsninger.

Her er de begrænsninger, vi vil bruge:

Ingen job kan falde til under fire timer.
Job 2 skal være mere end otte timer .
Job 5 skal være mindre end elleve timer .
Den samlede arbejdstid skal være lig med 40 .

Det kan være nyttigt at skrive dine begrænsninger som denne, før du bruger Solver.

Sådan ville vi konfigurere det i Solver:

Bemærk først Jeg har lavet en kopi af tabellen så vi overskriver ikke den originale, som indeholder vores nuværende arbejdstid.

Og for det andet, se, at værdierne i begrænsningerne større end og mindre end er en højere eller lavere end hvad jeg nævnte ovenfor. Det er fordi der ikke er større end eller mindre end muligheder. Der er kun større-end-eller-lige-til og mindre-end-eller-lige-til.

Lad os slå Løse og se hvad der sker.

Solver fandt en løsning! Som du kan se til venstre i vinduet ovenfor, er vores indtjening steget med $ 130. Og alle begrænsninger er opfyldt.

æbleur 6 rustfrit stål vs aluminium

Sørg for at beholde de nye værdier Behold Solver Solution bliver kontrolleret og ramt Okay .

Hvis du vil have flere oplysninger, kan du vælge en rapport fra højre side af vinduet. Vælg alle de rapporter, du vil have, fortæl Excel, om du vil have dem skitseret (jeg anbefaler det), og tryk på Okay .

Rapporterne genereres på nye ark i din projektmappe og giver dig oplysninger om den proces, Solver-tilføjelsesprogrammet gennemgik for at få dit svar.

I vores tilfælde er rapporterne ikke særlig spændende, og der er ikke mange interessante oplysninger der. Men hvis du kører en mere kompliceret Solver -ligning, finder du muligvis nogle nyttige rapporteringsoplysninger i disse nye regneark. Bare klik på + knappen på siden af enhver rapport for at få flere oplysninger:

Solver Avancerede indstillinger

Hvis du ikke ved meget om statistik, kan du ignorere Solvers avancerede muligheder og bare køre det som det er. Men hvis du kører store, komplekse beregninger, kan du se nærmere på dem.

Den mest oplagte er løsningsmetoden:

Du kan vælge mellem GRG Nonlinear, Simplex LP og Evolutionary. Excel giver en enkel forklaring på, hvornår du skal bruge hver enkelt. En bedre forklaring kræver en vis viden om statistik og regression.

For at justere yderligere indstillinger skal du bare trykke på Muligheder knap. Du kan fortælle Excel om heltalsoptimalitet, angive beregningstidsbegrænsninger (nyttige til massive datasæt) og justere, hvordan GRG- og evolutionære løsningsmetoder går til deres beregninger.

Igen, hvis du ikke ved, hvad noget af dette betyder, skal du ikke bekymre dig om det. Hvis du vil vide mere om, hvilken løsningsmetode du skal bruge, har Engineer Excel en god artikel, der beskriver det for dig . Hvis du vil have maksimal nøjagtighed, er Evolutionary sandsynligvis en god vej at gå. Bare vær opmærksom på, at det vil tage lang tid.

Målsøgning og løsning: At tage Excel til det næste niveau

Nu hvor du er fortrolig med det grundlæggende i at løse ukendte variabler i Excel, er en helt ny verden med regnearksberegning åben for dig.

Målsøgning kan hjælpe dig med at spare tid ved at foretage nogle beregninger hurtigere, og Solver tilføjer en enorm mængde strøm til Excel's beregningsevner .

Det er bare et spørgsmål om at blive fortrolig med dem. Jo mere du bruger dem, jo mere nyttige bliver de.

Bruger du Goal Search eller Solver i dine regneark? Hvilke andre tips kan du give for at få de bedste svar ud af dem? Del dine tanker i kommentarerne herunder!

Del Del Tweet E -mail 5 tips til at overlade dine VirtualBox Linux -maskiner

Træt af den dårlige ydelse, der tilbydes af virtuelle maskiner? Her er hvad du skal gøre for at øge din VirtualBox -ydeevne.

Læs Næste Relaterede emner

Produktivitet
Regneark
Microsoft Excel
Microsoft Office -tip

Om forfatteren Derefter Albright(506 artikler udgivet)

Dann er en indholdsstrategi og marketingkonsulent, der hjælper virksomheder med at skabe efterspørgsel og kundeemner. Han blogger også om strategi og content marketing på dannalbright.com.

Mere fra Dann Albright

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