Excels What-If Analysis låter dig se hur en ändring av en cell kan påverka en formels utdata. Du kan använda Excels verktyg för detta ändamål för att beräkna effekten av att ändra värdet på en cell i en formel.
Excel har tre typer av What-If-analysverktyg: Scenariohanterare, Målsökning och Datatabell. Med Goal Seek kan du bestämma vilken input du behöver för att konvertera formeln bakåt till en viss utdata.
Målsökningsfunktionen i Excel är trial and error, så om den inte ger det du vill, så arbetar Excel med att förbättra det värdet tills det gör det.
Vad är Excel-målsökningsformler?
Målsökning i Excel delas upp i tre huvudkomponenter:
- Ställ in cell: Cellen du vill använda som ditt mål. Att värdesätta: Värdet du vill ha som mål. Genom att byta cell: Cellen du vill ändra för att nå ditt målvärde.
Med dessa tre inställningar kommer Excel att försöka förbättra värdet i cellen du ställer in Genom att byta cell tills cellen du satt i Ställ in cell når det värde du bestämt i Att värdesätta.
Om allt det låter förvirrande kommer exemplen nedan att ge dig en bra uppfattning om hur målsökning fungerar.
Målsökningsexempel 1
Låt oss till exempel säga att du har två celler (A & B) och en tredje cell som beräknar medelvärdet av dessa två.
Anta nu att du har ett statiskt värde för A och att du vill höja medelnivån genom att ändra värdet för B. Genom att använda Målsökning kan du beräkna vilket värde av B som ger det genomsnittliga du vill ha.
Hur man använder målsökning i Excel
- Klicka på cell i Excel C1. I formelfältet anger du följande kod:
=AVERAGE(A1:B1)
Denna formel kommer att få Excel att beräkna medelvärdet av värdena i celler A1 och B1 och mata ut det i cellen C1. Du kan se till att din formel fungerar genom att mata in siffror i A1 och B1. C1 bör visa sitt genomsnitt. För det här exemplet ändrar du värdet på A1 cell till 16. Välj cell C1, och sedan från bandet, gå till Data flik. Under Fliken Data, Välj Vad-om-analys och då Mål Sök. Detta kommer att ta upp fönstret Målsökning. I fönstret Målsökning ställer du in Ställ in cell till C1. Detta kommer att vara din målcell. Om du har markerat C1 innan du öppnar fönstret Goal Seek kommer det att ställas in automatiskt Ställ in cell. I Att värdesätta, infoga det målvärde du vill ha. För det här exemplet vill vi att vårt genomsnitt ska vara 26, så målvärdet blir också 26. Slutligen, i Genom att byta cell, välj den cell du vill ändra för att nå målet. I det här exemplet kommer detta att vara cell B2. Klick OK att få Goal Seek att fungera sin magi.
När du klickar på OK visas en dialogruta som informerar dig om att Goal Seek har hittat en lösning.
Värdet på cell A1 kommer att ändras till lösningen, och detta kommer att skriva över tidigare data. Det är en bra idé att köra Goal Seek på en kopia av ditt datablad för att undvika att förlora din ursprungliga data.
Målsökningsexempel 2
Målsökning kan vara användbart när du använder det i verkliga scenarier. Men för att kunna använda Goal Seek till sin fulla potential måste du ha en ordentlig formel på plats, till att börja med.
För det här exemplet, låt oss ta ett litet bankärende. Anta att du har ett bankkonto som ger dig 4% årlig ränta på pengarna du har på ditt konto.
Med hjälp av What-If Analysis och Goal Seek kan du räkna ut hur mycket pengar du behöver ha på ditt konto för att få en viss månatlig räntebetalning.
För det här exemplet, låt oss säga att vi vill få $350 varje månad från räntebetalningar. Så här kan du räkna ut det:
- I cell A1 typ Balans. I cell A2 typ Årlig ränta. I cell A4 typ Månadsvinst. I cell B2 typ 4 %. Välj cell B4 och i formelfältet anger du formeln nedan:
=B1*B2/12
Den månatliga vinsten kommer att vara lika med kontosaldot multiplicerat med årskursen och sedan dividerat med antalet månader på ett år, 12 månader. Gå till Data fliken, klicka på Vad-om-analys, och välj sedan Mål Sök. I den Mål Sök fönster, typ B4 i Ställ in cell. Skriv 350 i Att värdesätta cell. (Detta är den månatliga vinsten du vill få) Typ B1 i Genom att byta cell. (Detta kommer att ändra saldot till ett värde som ger 350 USD per månad) Klicka OK. De Status för målsökning dialog kommer att dyka upp.
I dialogrutan Goal Seeking Status ser du att Goal Seeking har hittat en lösning i cell B4. I cell B1 kan du se lösningen, som ska vara 105 000.
Målsökningskrav
Med Goal Seek kan du se att den bara kan ändra en cell för att nå målvärdet. Det betyder att Goal Seek inte kan lösa och nå en lösning om den har mer än en variabel.
I Excel kan du fortfarande lösa mer än en variabel, men du måste använda ett annat verktyg som heter Solver. Du kan läsa mer om Excels lösare genom att läsa vår artikel om hur du använder Excels lösare.
Begränsningar för målsökning
En målsökningsformel använder test-och-förbättringsprocessen för att nå det optimala värdet, vilket kan ställa till problem när formeln ger ett odefinierat värde. Så här kan du testa det själv:
- I cell A1, typ 3. Välj cell C1, skriv sedan in formeln nedan i formelfältet:
=1/(A1-5)
Den här formeln delar en med A1-5, och om A1-5 råkar vara 0 blir resultatet ett odefinierat värde. Gå till Data fliken, klicka Vad-om-analys och välj sedan Mål Sök. I Ställ in cell, typ C1. I Att värdesätta, typ 1. Äntligen, in Genom att byta cell typ A1. (Detta ändrar A1-värdet till 1 i C1) Klicka OK.
Dialogrutan Målsökningsstatus kommer att berätta att den kanske inte har hittat en lösning, och du kan dubbelkolla att A1-värdet är långt borta.
Nu skulle lösningen på denna målsökning vara att helt enkelt ha 6 i A1. Detta kommer att ge 1/1, vilket är lika med 1. Men vid ett tillfälle under test- och förbättringsprocessen, försöker Excel 5 i A1, vilket ger 1/0, vilket är odefinierat, vilket dödar processen.
Ett sätt att komma runt det här Goal Seek-problemet är att ha ett annat startvärde, ett som kan undvika det odefinierade i test- och förbättringsprocessen.
Som ett exempel, om du ändrar A1 till valfritt tal som är större än 5 och sedan upprepar samma steg till Målsökning för 1, bör du få rätt resultat.
Gör beräkningarna mer effektiva med målsökning
What-If Analysis och Goal Seek kan påskynda dina beräkningar med dina formler med ett stort mått. Att lära sig rätt formler är ett annat viktigt steg mot att bli en Excel-mästare.
Om författaren
Amir M. Bohlooli (65 artiklar publicerade)
Amir är en farmacistudent med en passion för teknik och spel. Han gillar att spela musik, köra bil och skriva ord.
Mer från Amir M. Bohlooli
Prenumerera på vårt nyhetsbrev
Gå med i vårt nyhetsbrev för tekniska tips, recensioner, free e-böcker och exklusiva erbjudanden!
Klicka här för att prenumerera
