Så här använder du Excels målsökning och lösare för att lösa för okända variabler

Så här använder du Excels målsökning och lösare för att lösa för okända variabler

Excel är mycket kapabel när du har all data du behöver för dina beräkningar.





Men vore det inte trevligt om det kunde lösa för okända variabler ?





Med Goal Seek och Solver-tillägget kan det. Och vi visar dig hur. Läs vidare för en fullständig guide om hur du löser för en enda cell med Goal Seek eller en mer komplicerad ekvation med Solver.





Hur man använder målsök i Excel

Målsökning är redan inbyggt i Excel. Det är under Data fliken i Vad-om-analys meny:

I det här exemplet använder vi en mycket enkel uppsättning siffror. Vi har tre fjärdedelars försäljningssiffror och ett årligt mål. Vi kan använda Goal Seek för att ta reda på vad siffrorna behöver vara under fjärde kvartalet för att nå målet.



Som du kan se är den nuvarande försäljningssumman 114 706 enheter. Om vi ​​vill sälja 250 000 i slutet av året, hur många behöver vi sälja under fjärde kvartalet? Excel's Goal Seek kommer att berätta för oss.

Så här använder du Målsökning, steg för steg:





  1. Klick Data> Vad-om-analys> Målsökning . Du ser det här fönstret:
  2. Sätt 'lika' delen av din ekvation i Ange cell fält. Detta är det nummer som Excel kommer att försöka optimera. I vårt fall är det den totala summan av våra försäljningsnummer i cell B5.
  3. Skriv in ditt målvärde i Att värdesätta fält. Vi letar efter totalt 250 000 sålda enheter, så vi lägger 250 000 i detta fält.
  4. Berätta för Excel vilken variabel som ska lösas i Genom att byta cell fält. Vi vill se vad vår försäljning under fjärde kvartalet behöver vara. Så vi säger till Excel att lösa för cell D2. Det kommer att se ut så här när det är klart:
  5. Träffa OK att lösa för ditt mål. När det ser bra ut bara slå OK . Excel meddelar dig när Goal Seek har hittat en lösning.
  6. Klick OK igen ser du värdet som löser din ekvation i cellen du valde för Genom att byta cell .

I vårt fall är lösningen 135 294 enheter. Naturligtvis kunde vi precis ha funnit det genom att subtrahera löptotalen från det årliga målet. Men Goal Seek kan också användas på en cell som har redan data i den . Och det är mer användbart.

Observera att Excel skriver över våra tidigare data. Det är en bra idé att kör Målsök på en kopia av din data . Det är också en bra idé att anteckna dina kopierade data att den genererades med hjälp av Målsökning. Du vill inte förvirra det för aktuella, korrekta data.





hur man flyttar program från hdd till ssd

Så Goal Seek är en användbar Excel -funktion , men det är inte så imponerande. Låt oss titta på ett verktyg som är mycket mer intressant: tilläggsprogrammet Solver.

Vad gör Excel's Solver?

Kort sagt, Solver är som en multivariat version av Goal Seek . Den tar en målvariabel och justerar ett antal andra variabler tills den får det svar du vill ha.

Det kan lösa ett maximivärde för ett tal, ett minimivärde för ett nummer eller ett exakt antal.

Och det fungerar inom begränsningar, så om en variabel inte kan ändras, eller bara kan variera inom ett visst intervall, tar Solver hänsyn till det.

Det är ett bra sätt att lösa flera okända variabler i Excel. Men att hitta och använda det är inte enkelt.

Låt oss ta en titt på hur man laddar tilläggsprogrammet Solver och hoppar sedan in hur man använder Solver i Excel 2016.

Hur man laddar tilläggsprogrammet Solver

Excel har inte Solver som standard. Det är ett tillägg, så som andra kraftfulla Excel-funktioner måste du ladda det först. Lyckligtvis finns det redan på din dator.

Bege sig till Arkiv> Alternativ> Tillägg . Klicka sedan på bredvid Hantera: Excel-tillägg .

Om den här rullgardinsmenyn säger något annat än 'Excel-tillägg' måste du ändra det:

I det resulterande fönstret ser du några alternativ. Se till att rutan bredvid Tilläggsprogram för lösare kontrolleras och träffas OK .

Nu ser du Lösare knappen i Analys grupp av Data flik:

Om du redan har använt Data Analysis Toolpak ser du knappen Dataanalys. Om inte, kommer Solver att visas av sig själv.

Nu när du har laddat tillägget, låt oss ta en titt på hur du använder det.

Hur man använder Solver i Excel

Det finns tre delar i alla lösningsåtgärder: målet, de variabla cellerna och begränsningarna. Vi går igenom vart och ett av stegen.

  1. Klick Data> Lösare . Du ser fönstret Lösarparametrar nedan. (Om du inte ser lösningsknappen, se föregående avsnitt om hur du laddar tilläggsprogrammet Solver.)
  2. Ställ in ditt cellmål och berätta ditt mål för Excel. Målet ligger högst upp i Solver -fönstret och det har två delar: målcellen och ett val av maximera, minimera eller ett specifikt värde. Om du väljer Max , Excel kommer att justera dina variabler för att få största möjliga antal i din målcell. Min är motsatsen: Solver minimerar objektivtalet. Värdet av låter dig ange ett specifikt nummer för Solver att leta efter.
  3. Välj de variabelceller som Excel kan ändra. De variabla cellerna ställs in med Genom att ändra variabla celler fält. Klicka på pilen bredvid fältet, klicka sedan och dra för att välja de celler som Solver ska arbeta med. Observera att dessa är alla celler som kan variera. Om du inte vill att en cell ska ändras, välj inte den.
  4. Ange begränsningar för flera eller enskilda variabler. Slutligen kommer vi till begränsningarna. Det är här Solver verkligen är kraftfull. Istället för att ändra någon av variabelcellerna till valfritt tal kan du ange begränsningar som måste uppfyllas. Mer information finns i avsnittet om hur du ställer in begränsningar nedan.
  5. När all denna information är på plats, slå Lösa för att få ditt svar. Excel uppdaterar dina data så att de innehåller de nya variablerna (det är därför vi rekommenderar att du först skapar en kopia av dina data).

Du kan också generera rapporter som vi tittar kort på i vårt lösningsexempel nedan.

Så här ställer du in begränsningar i Solver

Du kan berätta för Excel att en variabel måste vara större 200. När du försöker med olika variabelvärden kommer Excel inte att gå under 201 med just den variabeln.

Om du vill lägga till en begränsning klickar du på Lägg till -knappen bredvid begränsningslistan. Du får ett nytt fönster. Markera cellen (eller cellerna) som ska begränsas i Cellreferens och välj sedan en operatör.

Här är de tillgängliga operatörerna:

  • <= (mindre än eller lika med)
  • = (lika med)
  • => (större än eller lika med)
  • int (måste vara ett heltal)
  • am (måste vara antingen 1 eller 0)
  • Alla olika

Alla olika är lite förvirrande. Den anger att varje cell i det område du väljer för Cellreferens måste vara ett annat nummer. Men det specificerar också att de måste vara mellan 1 och antalet celler. Så om du har tre celler kommer du att sluta med siffrorna 1, 2 och 3 (men inte nödvändigtvis i den ordningen)

Slutligen lägger du till värdet för begränsningen.

Det är viktigt att komma ihåg att du kan markera flera celler för cellreferens. Om du till exempel vill att sex variabler ska ha värden över 10 kan du välja dem alla och berätta för Solver att de måste vara större än eller lika med 11. Du behöver inte lägga till en begränsning för varje cell.

Du kan också använda kryssrutan i huvudfönstret för lösning för att se till att alla värden som du inte angav begränsningar för inte är negativa. Om du vill att dina variabler ska bli negativa avmarkerar du den här rutan.

Ett lösningsexempel

För att se hur allt detta fungerar, använder vi tilläggsprogrammet Solver för att göra en snabb beräkning. Här är data vi börjar med:

I den har vi fem olika jobb, som alla betalar en annan skattesats. Vi har också antalet timmar som en teoretisk arbetare har arbetat på vart och ett av dessa jobb under en viss vecka. Vi kan använda tilläggsprogrammet Solver för att ta reda på hur man maximerar den totala lönen samtidigt som vissa variabler hålls inom vissa begränsningar.

Här är de begränsningar vi kommer att använda:

  • Inga jobb kan falla under fyra timmar.
  • Jobb 2 måste vara mer än åtta timmar .
  • Jobb 5 måste vara mindre än elva timmar .
  • Den totala arbetstiden måste vara lika med 40 .

Det kan vara till hjälp att skriva ut dina begränsningar så här innan du använder Solver.

Så här skulle vi ställa in det i Solver:

Observera först det Jag har skapat en kopia av tabellen så vi skriver inte över den ursprungliga, som innehåller våra nuvarande arbetstider.

Och för det andra, se att värdena i begränsningarna större än och mindre än är en högre eller lägre än vad jag nämnde ovan. Det beror på att det inte finns några alternativ som är större än eller mindre. Det finns bara större än-eller-lika-till och mindre-än-eller-lika-till.

Låt oss slå Lösa och se vad som händer.

Solver hittade en lösning! Som du kan se till vänster i fönstret ovan har våra intäkter ökat med $ 130. Och alla begränsningar har uppfyllts.

hur kan jag spela .mp4 -filer på min tv via en usb -flashenhet?

För att behålla de nya värdena, se till Behåll lösningslösning kontrolleras och träffas OK .

Om du vill ha mer information kan du välja en rapport från höger sida av fönstret. Välj alla de rapporter du vill ha, berätta för Excel om du vill ha dem beskrivna (jag rekommenderar det) och tryck på OK .

Rapporterna genereras på nya blad i din arbetsbok och ger dig information om processen som tilläggsprogrammet Solver gick igenom för att få ditt svar.

I vårt fall är rapporterna inte särskilt spännande, och det finns inte så mycket intressant information där. Men om du kör en mer komplicerad Solver -ekvation kan du hitta användbar rapporteringsinformation i dessa nya kalkylblad. Klicka bara på + knappen på sidan av en rapport för att få mer information:

Solver Avancerade alternativ

Om du inte vet mycket om statistik kan du ignorera Solvers avancerade alternativ och bara köra den som den är. Men om du kör stora, komplexa beräkningar kanske du vill titta på dem.

Det mest uppenbara är lösningsmetoden:

Du kan välja mellan GRG Nonlinear, Simplex LP och Evolutionary. Excel ger en enkel förklaring om när du ska använda var och en. En bättre förklaring kräver viss kunskap om statistik och regression.

För att justera ytterligare inställningar, tryck bara på alternativ knapp. Du kan berätta för Excel om heltalsoptimalitet, ställa in beräkningstidsbegränsningar (användbara för massiva datamängder) och justera hur GRG- och evolutionära lösningsmetoder gör för att göra sina beräkningar.

Återigen, om du inte vet vad något av detta betyder, oroa dig inte för det. Om du vill veta mer om vilken lösningsmetod du ska använda, har Engineer Excel en bra artikel som beskriver det för dig . Om du vill ha maximal noggrannhet är Evolutionary förmodligen en bra väg att gå. Tänk bara på att det kommer att ta lång tid.

Målsökning och lösning: Ta Excel till nästa nivå

Nu när du är bekväm med grunderna för att lösa okända variabler i Excel, är en helt ny värld av kalkylarkberäkning öppen för dig.

Goal Seek kan hjälpa dig att spara tid genom att göra några beräkningar snabbare, och Solver lägger till en enorm mängd kraft Excel beräkningsförmåga .

Det är bara att bli bekväm med dem. Ju mer du använder dem, desto mer användbara blir de.

Använder du Goal Search eller Solver i dina kalkylblad? Vilka andra tips kan du ge för att få ut de bästa svaren av dem? Dela dina tankar i kommentarerna nedan!

Dela med sig Dela med sig Tweet E-post 5 tips för att ladda dina VirtualBox Linux -maskiner

Trött på den dåliga prestanda som erbjuds av virtuella maskiner? Här är vad du bör göra för att öka din VirtualBox -prestanda.

Läs Nästa
Relaterade ämnen
  • Produktivitet
  • Kalkylark
  • Microsoft excel
  • Microsoft Office -tips
Om författaren Sedan Albright(506 artiklar publicerade)

Dann är en innehållsstrategi och marknadskonsult som hjälper företag att skapa efterfrågan och leads. Han bloggar också om strategi och innehållsmarknadsföring på dannalbright.com.

Mer från Dann Albright

Prenumerera på vårt nyhetsbrev

Gå med i vårt nyhetsbrev för tekniska tips, recensioner, gratis e -böcker och exklusiva erbjudanden!

Klicka här för att prenumerera