Excel er meget velegnet, 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 Solver-tilføjelsen kan den. Og vi vil vise dig hvordan. Læs videre for en komplet vejledning om, 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 fanen Data i menuen What-If Analysis :
I dette eksempel bruger vi et meget simpelt sæt tal. Vi har tre fjerdedele værdien af 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 salgstal 114.706 enheder. Hvis vi ønsker at sælge 250.000 ved årets udgang, hvor mange skal vi sælge i 4. kvartal? Excel's Goal Seek vil fortælle os.
Sådan bruger du målsøgning trin for trin:
- Klik på Data> Hvad-Hvis analyse> Målsøgning . Du får se dette vindue:
- Sæt "ligestillings" -delen af din ligning i feltet Set Cell . Dette er det tal, som Excel vil forsøge at optimere. I vores tilfælde er det det samlede antal af vores salgstal i celle B5.
- Indtast din målværdi i feltet Til værdi . 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 feltet Ved ændring af celle . Vi ønsker at 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å:
- Tryk OK for at løse dit mål. Når det ser godt ud, skal du bare trykke på OK . Excel vil fortælle dig, når Goal Seek har fundet en løsning.
- Klik på OK igen, og du får vist den værdi, der løser din ligning i den celle, du valgte for Ved at skifte celle .
I vores tilfælde er løsningen 135.294 enheder. Selvfølgelig kunne vi lige have fundet det ved at trække løbende summen fra det årlige mål. Men Målsøgning kan også bruges på en celle, der allerede har data i den . Og det er mere nyttigt.
Bemærk, at Excel overskriver vores tidligere data. Det er en god ide at køre målsøgning på en kopi af dine data . Det er også en god ide at lave en notat om dine kopierede data, som den blev genereret ved hjælp af målsøgning. Du ønsker ikke at forvirre den for aktuelle, nøjagtige data.
Så Målsøgning er en nyttig Excel-funktion 16 Excel-formler, der kan hjælpe dig med at løse virkelige livsproblemer 16 Excel-formler, der kan hjælpe dig med at løse problemer i virkeligheden Det rigtige værktøj er halvdelen af arbejdet. Excel kan løse beregninger og behandle data hurtigere, end du kan finde din regnemaskine. Vi viser dig centrale Excel-formler og demonstrere, hvordan du bruger dem. Læs mere, men det er ikke så imponerende. Lad os se på et værktøj, der er meget mere interessant: Solver-tilføjelsen.
Hvad gør Excel's Solver Do?
Kort sagt, Solver er som en multivariativ version af målsøgning . Det tager en målvariabel og justerer en række andre variabler, indtil den får det svar, du ønsker.
Det kan løse for en maksimumsværdi af et tal, en mindste værdi af et tal eller et nøjagtigt tal.
Og det virker inden for begrænsninger, så hvis en variabel ikke kan ændres eller kun kan variere inden for et bestemt interval, tager Solver hensyn til det.
Det er en fantastisk måde at løse for 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 spring derefter ind i, hvordan du bruger Solver i Excel 2016.
Sådan indlæser du Solver-tilføjelsen
Excel har ikke Solver som standard. Det er en tilføjelse, så som andre kraftfulde Excel-funktioner Power Up Excel med 10 tilføjelser til at behandle, analysere og visualisere data som en Pro Power Up Excel med 10 tilføjelsesprogrammer til at behandle, analysere og visualisere data som en Pro Vanilla Excel er fantastisk, men du kan gøre det endnu mere kraftfuldt med tilføjelser. Uanset hvilke data du skal behandle, er der chancer for, at nogen har oprettet en Excel-app til den. Her er et udvalg. Læs mere, du skal først indlæse det. Heldigvis er det allerede på din computer.
Gå til Filer> Valg> Tilføjelser . Klik derefter på Gå ved siden af Administrer: Excel-tilføjelser .
Hvis denne dropdown siger noget andet end "Excel-tilføjelser", skal du ændre det:
I det resulterende vindue kan du se et par muligheder. Sørg for, at feltet ud for Solver Add-In er markeret, og tryk OK .
Du vil nu se knappen Solver i analysegruppen på fanen Data :
Hvis du allerede har brugt Data Analysis Toolpak Sådan udføres Basic Data Analysis i Excel Sådan foretages Basic Data Analysis i Excel Excel er ikke beregnet til dataanalyse, men det kan stadig håndtere statistikker. Vi viser dig, hvordan du bruger værktøjet Add-in til dataanalyse til at køre Excel-statistikker. Læs mere, du vil se knappen Dataanalyse. Hvis ikke, vises Solver alene.
Nu, hvor du har indlæst tilføjelsen, 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 får vist vinduet Solver Parameters nedenfor. (Hvis du ikke kan se solver-knappen, kan du se det forrige afsnit om, hvordan du indlæser Solver-tilføjelsen.)
- Indstil dit cellemål og fortæl Excel dit mål. Målet er øverst i Solver-vinduet, og det har to dele: objektivcellen og et valg af maksimere, minimere eller en bestemt værdi.
Hvis du vælger Max, justerer Excel dine variabler for at få det største antal muligt i din målcelle. Min er det modsatte: Solver vil minimere målnummeret. Værdi af kan du angive et bestemt nummer for Solver at søge efter. - Vælg de variable celler, som Excel kan ændre. De variable celler er indstillet med feltet By Changing Variable Cells . Klik på pilen ved siden af feltet, og klik derefter og træk for at vælge de celler, som Solver skal arbejde sammen med. Bemærk at disse er alle de celler, der kan variere. Hvis du ikke vil have en celle til at ændre, skal du ikke vælge den.
- Indstil begrænsninger for flere eller individuelle variabler. Endelig kommer vi til de begrænsninger. Det er her, hvor Solver er virkelig stærk. I stedet for at ændre nogle af de variable celler til et hvilket som helst nummer, det ønsker, kan du angive begrænsninger, der skal opfyldes. For detaljer, se afsnittet om, hvordan du indstiller begrænsninger nedenfor.
- Når alle disse oplysninger er på plads, skal du trykke på Løs for at få dit svar. Excel opdaterer dine data for at inkludere de nye variabler (det er derfor vi anbefaler at du opretter en kopi af dine data først).
Du kan også generere rapporter, som vi vil se nærmere på i vores Solver-eksempel nedenfor.
Sådan indstilles begrænsninger i løsningen
Du kan muligvis fortælle Excel, at en variabel skal være større 200. Når du prøver forskellige variable værdier, går Excel ikke under 201 med den pågældende variabel.
For at tilføje en begrænsning skal du klikke på knappen Tilføj ved siden af begrænsningslisten. Du får et nyt vindue. Vælg den celle (eller celler) der skal begrænses i feltet Cell Reference, og vælg derefter en operatør.
Her er de tilgængelige operatører:
- <= (mindre end eller lig med)
- = (lig med)
- => (større end eller lig med)
- int (skal være et helt tal)
- bin (skal være enten 1 eller 0)
- AllDifferent
AllDifferent er lidt forvirrende. Det specificerer, at hver celle i det område, du vælger til Cell Reference, 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, slutter du med tallene 1, 2 og 3 (men ikke nødvendigvis i den rækkefølge)
Endelig tilføj værdien for begrænsningen.
Det er vigtigt at huske at du kan vælge flere celler til Cell Reference. Hvis du vil have seks variabler til at have værdier over 10, kan du for eksempel vælge dem alle og fortælle Solver, at de skal være større end eller lig med 11. Du behøver ikke at tilføje en begrænsning for hver celle.
Du kan også bruge afkrydsningsfeltet i main Solver-vinduet for at sikre, at alle de værdier, du ikke angav begrænsninger for, er ikke-negative. Hvis du vil have dine variabler til at gå negativ, fjern markeringen i dette felt.
Et solvereksempel
For at se, hvordan alt dette virker, bruger vi Solver-tilføjelsen 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 medarbejder har arbejdet på hver enkelt af disse job i en given uge. Vi kan bruge Solver-tilføjelsen til at finde ud af, hvordan du maksimerer den samlede løn, mens du holder visse variabler inden for nogle begrænsninger.
Her er de begrænsninger, vi vil bruge:
- Ingen job kan falde under fire timer.
- Job 2 skal være større end otte timer .
- Job 5 skal være mindre end elleve timer .
- De samlede arbejdstimer skal være lig med 40 .
Det kan være nyttigt at skrive dine begrænsninger som dette, før du bruger Solver.
Sådan har vi set det i Solver:
Først bemærk at jeg har oprettet en kopi af bordet, så vi ikke overskriver den oprindelige, som indeholder vores nuværende arbejdstid.
Og for det andet, se, at værdierne i større end og mindre end begrænsninger er en højere eller lavere end hvad jeg nævnte ovenfor. Det skyldes, at der ikke er større end eller mindre end valgmuligheder. Der er kun større end eller lige-til og mindre end eller lige-til.
Lad os slå løs og se hvad der sker.
Solver fundet en løsning! Som du kan se til venstre for vinduet ovenfor, er vores indtjening steget med $ 130. Og alle de begrænsninger er blevet opfyldt.
For at beholde de nye værdier skal du sørge for, at Keep Solver Solution er markeret, og tryk OK .
Hvis du vil have flere oplysninger, kan du dog 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 OK .
Rapporterne genereres på nye ark i din projektmappe og giver dig oplysninger om den proces, som Solver-tilføjelsen gik igennem for at få dit svar.
I vores tilfælde er rapporterne ikke meget spændende, og der er ikke mange interessante oplysninger der. Men hvis du kører en mere kompliceret Solver-ligning, kan du finde nogle nyttige rapporteringsoplysninger i disse nye regneark. Bare klik på knappen + på siden af en rapport for at få flere oplysninger:
Solver Advanced Options
Hvis du ikke ved meget om statistik, kan du ignorere Solver's avancerede muligheder og bare køre den som-er. Men hvis du kører store, komplekse beregninger, kan du måske se dem ind.
Det mest oplagte er løsningsmetoden:
Du kan vælge mellem GRG Nonlinear, Simplex LP og Evolutionary. Excel giver en simpel forklaring om, hvornår du skal bruge hver enkelt. En bedre forklaring kræver en vis viden om statistik Lær statistik gratis med disse 6 ressourcer Lær statistik gratis med disse 6 ressourcer Statistikker har et ry for et emne, der er svært at forstå. Men at lære af den rigtige ressource hjælper dig med at forstå undersøgelsesresultater, valgrapporter og dine statistikopgaver på ingen tid. Læs mere og regression.
For at justere yderligere indstillinger skal du bare trykke på knappen Options . Du kan fortælle Excel om integeroptimitet, fastsætte beregningstidsbegrænsninger (nyttigt til massive datasæt), og justere, hvordan GRG og Evolutionary-løsningsmetoderne går ud på at lave 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 der skal bruges, har Engineer Excel en god artikel, der beskriver det for dig. Hvis du vil have maksimal nøjagtighed, er Evolutionary nok en god vej at gå. Bare vær opmærksom på, at det tager lang tid.
Målsøgning og løser: At tage Excel til næste niveau
Nu hvor du er komfortabel med det grundlæggende at løse for ukendte variabler i Excel, er en helt ny verden af regnearkberegning åben for dig.
Målsøgning kan hjælpe dig med at spare tid ved at lave nogle beregninger hurtigere, og Solver tilføjer en enorm mængde strøm til Excels beregningsevner. Sådan beregnes grundlæggende statistikker i Excel: En nybegyndervejledning Sådan beregnes grundlæggende statistikker i Excel: En nybegyndervejledning Microsoft Excel kan gør statistik! Du kan beregne procentsatser, gennemsnit, standardafvigelse, standardfejl og elevens T-test. Læs mere .
Det er bare et spørgsmål om at blive komfortabel med dem. Jo mere du bruger dem, desto mere nyttige bliver de.
Bruger du Målsøgning eller Solver i dine regneark? Hvilke andre tips kan du sørge for at få de bedste svar ud af dem? Del dine tanker i kommentarerne nedenfor!