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

  • Harry James
  • 0
  • 3174
  • 556
Reklame

Excel er meget kapabel, 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 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 antal tal. Vi har et salgsnummer på tre kvartaler og et årligt mål. Vi kan bruge målsøgning 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 salgssumma 114.706 enheder. Hvis vi vil sælge 250.000 ved udgangen af ​​året, hvor mange skal vi sælge i 4. kvartal? Excel's målsøgning fortæller os.

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

  1. Klik Data> Hvad-hvis-analyse> Målsøgning. Du ser dette vindue:
  2. Put den “lige med” del af din ligning i Indstil celle Mark. Dette er det nummer, som Excel vil prøve at optimere. I vores tilfælde er det det samlede antal af vores salgstal i celle B5.
  3. Skriv din målværdi i At sætte pris på Mark. Vi leder efter i alt 250.000 solgte enheder, så vi lægger “250.000” på dette felt.
  4. Fortæl Excel, hvilken variabel du skal løse for i Ved at skifte celle Mark. 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å:
  5. Hit Okay at løse for dit mål. Når det ser godt ud, skal du bare ramme Okay. Excel fortæller dig, når Goal Seek har fundet en løsning.
  6. Klik 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. Selvfølgelig kunne vi lige have fundet det ved at trække det samlede løb fra det årlige mål. Men målsøgning kan også bruges på en celle der har allerede data i det. Og det er mere nyttigt.

Bemærk, at Excel overskriver vores tidligere data. Det er en god ide at gøre kør målsøgning på en kopi af dine data. Det er også en god ide at notere dine kopierede data, at de blev genereret ved hjælp af målsøgning. Du ønsker ikke at forveksle det med aktuelle, nøjagtige data.

Så målsøgning er en nyttig Excel-funktion 16 Excel-formler, der hjælper dig med at løse virkelige problemer 16 Excel-formler, der hjælper dig med at løse virkelige livsproblemer Det rigtige værktøj er halvdelen af ​​arbejdet. Excel kan løse beregninger og behandle data hurtigere, end du kan finde din lommeregner. Vi viser dig centrale Excel-formler og demonstrerer, hvordan du bruger dem. , 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?

Kort sagt, Solver er som en multivariat version af Goal Seek. Det tager én målvariabel og justerer et antal andre variabler, indtil det får det ønskede svar.

Det kan løse for en maksimal værdi af et tal, en minimumsværdi af et tal eller et nøjagtigt tal.

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

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æses Solver-tilføjelsen

Excel har ikke Solver som standard. Det er en tilføjelse, ligesom andre kraftfulde Excel-funktioner Power Up Excel med 10 tilføjelsesprogrammer til behandling, analyse og visualisering af data som en Pro Power Up Excel med 10 tilføjelsesprogrammer til behandling, analyse og visualisering af data som en Pro Vanilla Excel er forbløffende, men du kan gøre det endnu mere kraftfuldt med tilføjelser. Uanset hvilke data du har brug for at behandle, er chancerne for, at nogen har oprettet en Excel-app til den. Her er et valg. , skal du indlæse det først. Heldigvis er det allerede på din computer.

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

Hvis denne dropdown siger noget andet end “Excel-tilføjelser,” skal du ændre det:

I det resulterende vindue ser du et par muligheder. Sørg for, at boksen ved siden af Solver-tilføjelse er kontrolleret og ramt Okay.

Du vil nu se Solver knappen i Analyse gruppe af Data fane:

Hvis du allerede har brugt dataanalyseværktøjspakken Sådan foretager du grundlæggende dataanalyse i Excel Sådan udføres basisdataanalyse i Excel Excel er ikke beregnet til dataanalyse, men det kan stadig håndtere statistikker. Vi viser dig, hvordan du bruger tilføjelsesprogrammet Data Analysis Toolpak til at køre Excel-statistikker. , vises knappen Data Analyse. Hvis ikke, vises Solver af sig selv.

Nu hvor du har indlæst add-in, 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 af trinene.

  1. Klik Data> Solver. Du ser vinduet Solver Parameters nedenfor. (Hvis du ikke ser solver-knappen, kan du se det foregående afsnit om, hvordan du indlæser Solver-tilføjelsen.)
  2. 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, Excel justerer dine variabler for at få det størst mulige antal i din målcelle. Min er det modsatte: Solver minimerer objektivet. Værdi af lader dig angive et specifikt nummer, som Solver skal kigge efter.
  3. Vælg de variable celler, som Excel kan ændre. De variable celler indstilles med Ved at ændre variable celler Mark. Klik på pilen ved siden af ​​feltet, og klik derefter og træk for at vælge de celler, som Solver skal arbejde med. Bemærk, at disse er alle cellerne der kan variere. Hvis du ikke ønsker, at en celle skal ændres, skal du ikke vælge den.
  4. Indstil begrænsninger for flere eller individuelle variabler. Endelig kommer vi til begrænsningerne. Det er her Solver virkelig er magtfuld. I stedet for at ændre nogen af ​​de variable celler til et hvilket som helst nummer, det ønsker, kan du specificere begrænsninger, der skal overholdes. For detaljer, se afsnittet om, hvordan du indstiller begrænsninger nedenfor.
  5. Når alle disse oplysninger er på plads, skal du ramme Løse for at få dit svar. Excel opdaterer dine data til 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 kort ser på i vores Solver-eksempel nedenfor.

Sådan indstilles begrænsninger i Solver

Du fortæller muligvis Excel, at en variabel skal være større 200. Når du prøver forskellige variabelværdier, går Excel ikke under 201 med den bestemte variabel.

Klik på knappen for at tilføje en begrænsning Tilføje -knappen ved siden af ​​begrænsningslisten. Du får et nyt vindue. Vælg den celle (eller celler), der skal begrænses i Cellehenvisning vælg derefter en operatør.

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)
  • beholder (skal være enten 1 eller 0)
  • AllDifferent

AllDifferent er lidt forvirrende. Den specificerer, at hver celle i det område, du vælger for Cellehenvisning skal være et andet tal. Men det specificerer også, at de skal være mellem 1 og antallet af celler. Så hvis du har tre celler, ender du med numrene 1, 2 og 3 (men ikke nødvendigvis i den rækkefølge)

Til sidst tilføj værdien for begrænsningen.

Det er vigtigt at huske, at du kan vælg flere celler til cellehenvisning. Hvis du for eksempel ønsker, at seks variabler skal 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 at tilføje en begrænsning for hver celle.

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

Et løsningseksempel

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

I det har vi fem forskellige job, som hver betaler en anden sats. Vi har også antallet af timer, som en teoretisk arbejdstager har arbejdet på hvert af disse job i en given uge. Vi kan bruge Solver-tilføjelsen til at finde ud af, hvordan man maksimerer den samlede lønning, mens vi holder visse variabler inden for nogle begrænsninger.

Her er de begrænsninger, vi bruger:

  • 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.
  • Den samlede arbejdstid skal være lig med 40.

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

Sådan konfigurerer vi det i Solver:

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

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

Lad os ramme Løse og se hvad der sker.

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

For at bevare de nye værdier skal du sørge for Hold Solver Solution kontrolleres og rammer Okay.

Hvis du dog ønsker mere information, 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 information om processen, som Solver-tilføjelsen gennemgik for at få dit svar.

I vores tilfælde er rapporterne ikke særlig spændende, og der er ikke en masse interessante oplysninger der. Men hvis du kører en mere kompliceret Solver-ligning, kan du finde nogle nyttige rapporteringsoplysninger i disse nye regneark. Klik bare på + knappen på siden af ​​enhver rapport for at få mere information:

Løs avancerede indstillinger

Hvis du ikke ved meget om statistikker, kan du ignorere Solvers avancerede indstillinger og bare køre den som den er. Men hvis du kører store, komplekse beregninger, kan du undersøge dem.

Det mest indlysende er løsningsmetoden:

Du kan vælge mellem GRG Ikke-lineær, Simplex LP og Evolutionary. Excel giver en enkel forklaring på, hvornår du skal bruge hver enkelt. En bedre forklaring kræver noget kendskab til statistikker Lær statistik gratis med disse 6 ressourcer Lær statistik gratis med disse 6 ressourcer Statistik har et ry for et emne, der er vanskeligt at forstå. Men at lære af den rigtige ressource vil hjælpe dig med at forstå undersøgelsesresultater, valgrapporter og dine statistikklassetildelinger på kort tid. og regression.

For at justere yderligere indstillinger skal du bare trykke på Muligheder knap. Du kan fortælle Excel om heltaloptimalitet, indstille beregningstidsbegrænsninger (nyttige til massive datasæt) og justere, hvordan GRG- og Evolutionary-metoderne går ud på at udføre 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 indeholder den for dig. Hvis du ønsker 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 næste niveau

Nu hvor du er tilpas 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 foretage nogle beregninger hurtigere, og Solver tilføjer en enorm mængde strøm til Excel's beregningsevne. Sådan beregnes grundlæggende statistikker i Excel: En begynderguide Sådan beregnes basestatistikker i Excel: En begynderguide Microsoft Excel kan gør statistik! Du kan beregne procenter, gennemsnit, standardafvigelse, standardfejl og studerendes T-test. .

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 Seek eller Solver på dine regneark? Hvilke andre tip kan du give for at få de bedste svar ud af dem? Del dine tanker i kommentarerne herunder!




Endnu ingen kommentarer

Om moderne teknologi, enkel og overkommelig.
Din guide i en verden af moderne teknologi. Lær hvordan du bruger de teknologier og gadgets, der omgiver os hver dag, og lær, hvordan du finder interessante ting på Internettet.