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

Annons

Excel är mycket skickligt när du har all den information du behöver för dina beräkningar.

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

Med Målsök och Solver-tillägget kan det. Och vi ska visa dig hur. Läs vidare för en fullständig guide om hur man löser en enda cell med målsökning eller en mer komplicerad ekvation med solver.

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

Målsökningen är redan inbyggd i Excel. Det finns under fliken Data i menyn What-If Analysis :

För det här exemplet använder vi en mycket enkel uppsättning tal. Vi har tre fjärdedelar av försäljningsnummer och ett årligt mål. Vi kan använda målsökningen för att ta reda på vad siffrorna behöver vara i fjärde kvartalet för att nå målet.

Som du kan se är det nuvarande försäljningsantalet 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? Excels målsökning kommer att berätta för oss.

Så här använder du målsökningen steg för steg:

1. Klicka på Data> What-If Analysis> Målsökning . Du får se detta fönster:
   
2. Ställ in "lika" delen av din ekvation i fältet Set Cell . Detta är det nummer som Excel kommer att försöka optimera. I vårt fall är det totala antalet försäljningsnummer i cell B5.
3. Skriv ditt målvärde i fältet Till-värde . Vi letar efter totalt 250 000 enheter sålda, så lägger vi "250 000" i det här fältet.
4. Berätta vilken variabel som ska lösas i fältet Genom att byta cellfält . Vi vill se vad vår försäljning under fjärde kvartalet behöver vara. Så vi ska berätta för Excel att lösa för cell D2. Det kommer att se ut så här när det är klart att gå:
   
5. Hit OK för att lösa ditt mål. När det ser bra ut, tryck bara på OK . Excel kommer att meddela dig när Goal Seek har hittat en lösning.
   
6. Klicka på OK igen och du får se det värde som löser din ekvation i cellen du valde för genom att ändra cell .
   

I vårt fall är lösningen 135 294 enheter. Självfallet kunde vi bara ha hittat det genom att subtrahera löpande summan från det årliga målet. Men mål sök kan också användas på en cell som redan har 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öra mål sök på en kopia av dina data . Det är också en bra idé att göra en notering på din kopierade data som den genererades med hjälp av målsökningen. Du vill inte förvirra den för aktuella, korrekta uppgifter.

Så Goal Seek är en användbar Excel-funktion 16 Excel-formulär som hjälper dig att lösa verkliga problem 16 Excel-formulär som hjälper dig att lösa verkliga problem Det rätta verktyget är hälften av arbetet. Excel kan lösa beräkningar och bearbeta data snabbare än du kan hitta din räknare. Vi visar dig viktiga Excel-formulär och visar hur du använder dem. Läs mer, men det är inte så imponerande. Låt oss titta på ett verktyg som är mycket mer intressant: Solver-tillägget.

Vad gör Excels Solver?

Kort sagt, Solver är som en multivariad version av målsökningen . Det tar en målvariabel och justerar ett antal andra variabler tills det får svaret du vill ha.

Det kan lösa upp ett maximalt värde av ett tal, ett lägsta värde för ett tal eller ett exakt nummer.

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

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å att ladda in tillägget Solver och hoppa sedan till hur du använder Solver i Excel 2016.

Så här laddar du in lösningsuppdateringen

Excel har inte Solver som standard. Det är ett tillägg, liksom andra kraftfulla Excel-funktioner, Ström upp Excel med 10 tillägg för att bearbeta, analysera och visualisera data som en Pro Power Up Excel med 10 tillägg för att bearbeta, analysera och visualisera data som en Pro Vanilla Excel Det är fantastiskt, men du kan göra det ännu kraftfullare med tillägg. Oavsett vilken data du behöver bearbeta är chansen att någon skapade en Excel-app för den. Här är ett urval. Läs mer, du måste ladda det först. Lyckligtvis finns det redan på din dator.

Gå till Arkiv> Alternativ> Add-Ins . Klicka sedan på Gå 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 får du se några alternativ. Kontrollera att rutan bredvid Solver Add-In är markerad och tryck OK .

Nu ser du knappen Solver i analysgruppen på fliken Data :

Om du redan har använt Data Analysis Toolpak Så här gör du Basic Data Analysis i Excel Så här gör du Basic Data Analysis i Excel Excel är inte avsett för dataanalys, men det kan fortfarande hantera statistik. Vi visar hur du använder verktyget Data Analysis Toolpak för att köra Excel-statistik. Läs mer, du hittar knappen Data Analysis. Om inte, visas Solver själv.

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

Så här använder du Solver i Excel

Det finns tre delar till någon Solver-åtgärd: målet, de variabla cellerna och begränsningarna. Vi går igenom varje steg.

1. Klicka på Data> Solver . Du får se fönstret Solver Parameters nedan. (Om du inte ser solverknappen, se föregående avsnitt om hur du laddar in Solver-tillägget.)
   
2. Ange ditt cellmål och berätta för Excel ditt mål. Målet är högst upp i Solver-fönstret, och det har två delar: objektcellen och ett val av maximera, minimera eller ett specifikt värde.
   
   Om du väljer Max, justerar Excel dina variabler för att få det största antalet möjliga i din målcell. Min är motsatsen: Solver minimerar målnumret. Värde av kan du ange ett visst nummer för Solver att leta efter.
3. Välj de variabla cellerna som Excel kan ändra. De variabla cellerna ställs in med fältet By Changing Variable Cells . Klicka på pilen bredvid fältet, klicka sedan och dra för att välja de celler som Solver ska fungera med. Observera att det här ä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 är verkligen kraftfull. Istället för att ändra någon av de variabla cellerna till valfritt antal som det vill, kan du ange begränsningar som måste uppfyllas. För detaljer, se 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 kommer att uppdatera dina data för att inkludera de nya variablerna (det är därför vi rekommenderar att du skapar en kopia av dina data först).

Du kan också skapa rapporter, som vi kommer att titta på kort i vårt Solver-exempel nedan.

Så här ställer du in begränsningar i lösaren

Du kanske säger Excel att en variabel måste vara större 200. När du försöker med olika variabla värden går Excel inte under 201 med den specifika variabeln.

För att lägga till en begränsning, klicka på knappen Lägg till bredvid begränsningslistan. Du får ett nytt fönster. Markera cellen (eller cellerna) som ska begränsas i fältet 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)
• bin (måste vara antingen 1 eller 0)
• AllDifferent

AllDifferent är lite förvirrande. Det specificerar att varje cell i det intervall som du väljer för cellreferens måste vara ett annat tal. Men det anger 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ägg till värdet för begränsningen.

Det är viktigt att komma ihåg att du kan välja flera celler för cellreferens. Om du vill ha sex variabler att ha värden över 10, kan du till exempel välja alla och berätta 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 att se till att alla värden som du inte angav begränsningar för är icke-negativa. Om du vill att dina variabler ska gå negativa, avmarkera den här rutan.

Ett lösningsexempel

För att se hur allt detta fungerar kommer vi att använda Solver-tillägget för att göra en snabbberäkning. Här är de data vi börjar med:

I det har vi fem olika jobb, som alla betalar en annan takt. Vi har också det antal timmar en teoretisk arbetare har arbetat på vid varje jobb under en viss vecka. Vi kan använda Solver-tillägget för att få 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 ska använda:

• Inga jobb kan falla under fyra timmar.
• Jobb 2 måste vara större än åtta timmar .
• Jobb 5 måste vara mindre än elva timmar .
• De totala arbetade timmarna måste vara lika med 40 .

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

Så här ställer vi in ​​det här i Solver:

Först noterar jag att jag har skapat en kopia av tabellen så att vi inte skriver över den ursprungliga, som innehåller vår nuvarande arbetstid.

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

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

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

För att behålla de nya värdena, se till att Keep Solver Solution är markerad och tryck OK .

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

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

I vårt fall är rapporterna inte så 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 några användbara rapporteringsuppgifter i dessa nya kalkylblad. Klicka bara på knappen + på sidan av varje rapport för att få mer information:

Lös avancerade alternativ

Om du inte vet mycket om statistik kan du ignorera Solver avancerade alternativ och bara köra den som-ä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 Lär statistik gratis med dessa 6 resurser Lär statistik gratis med dessa 6 resurser Statistik har ett rykte för ett ämne som är svårt att förstå. Men att lära av rätt resurs hjälper dig att förstå undersökningsresultat, valrapporter och dina statistikuppgifter på nolltid. Läs mer och regression.

För att justera ytterligare inställningar, klicka bara på alternativknappen . Du kan berätta för Excel om heltaloptimalitet, ställa in beräkningstidsbegränsningar (användbar för massiva dataset), och justera hur GRG och Evolutionary 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 om det. Om du vill veta mer om vilken lösningsmetod som ska användas har Engineer Excel en bra artikel som lägger ut det för dig. Om du vill ha maximal noggrannhet är Evolutionary troligen ett bra sätt att gå. Var bara medveten om att det tar lång tid.

Mål sök och lösare: Tar Excel till nästa nivå

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

Målsökningen kan hjälpa dig att spara tid genom att göra några beräkningar snabbare och Solver lägger stor kraft i Excels beräkningsförmåga. Hur man beräknar grundstatistik i Excel: En nybörjarhandledning Hur man beräknar grundstatistik i Excel: En nybörjarhandbok Microsoft Excel kan gör statistik! Du kan beräkna procentsatser, medelvärden, standardavvikelse, standardfel och studentens T-tester. Läs mer .

Det handlar bara om att bli bekväm med dem. Ju mer du använder dem, desto mer användbar blir de.

Användar du målsökande eller lösare i dina kalkylblad? Vilka andra tips kan du göra för att få de bästa svaren ut av dem? Dela dina tankar i kommentarerna nedan!