Beräkna flera resultat genom att använda en datatabell

I Microsoft Excel ingår datatabeller i en uppsättning kommandon som kallas What-If analysverktyg. När du skapar och analyserar datatabeller gör du konsekvensanalyser.

Konsekvensanalys är processen att ändra värdena i celler för att se hur ändringarna påverkar resultatet av formler i kalkylbladet. Du kan till exempel använda en datatabell för att variera räntesatsen och löptiden för ett lån – för att utvärdera potentiella månatliga betalningsbelopp.

Typer av konsekvensanalys    

Det finns tre typer av konsekvensanalysverktyg i Excel: scenarier, datatabeller och målsökning. Scenarier och datatabeller använder uppsättningar med indatavärden för att beräkna möjliga resultat. Målsökning är helt annorlunda, den använder ett enda resultat och beräknar möjliga indatavärden som skulle ge det resultatet.

Liksom scenarier hjälper datatabeller dig att utforska olika möjliga resultat. Till skillnad från scenarier visar datatabeller alla resultat i en tabell i ett kalkylblad. Med hjälp av datatabeller är det enkelt att snabbt undersöka flera olika möjligheter. Eftersom du bara fokuserar på en eller två variabler är resultaten enkla att läsa och dela i tabellform.

En datatabell kan inte tillgodose fler än två variabler. Om du vill analysera fler än två variabler använder du istället scenarier. Även om den bara är begränsad till en eller två variabler (en för radindatacellen och en för kolumnens indatacell), kan en datatabell innehålla så många olika variabelvärden som du vill. Ett scenario kan ha högst 32 olika värden men du kan skapa så många scenarier du vill.

Läs mer i artikeln Introduktion till What-If analys.

Skapa antingen datatabeller med en variabel eller två variabler, beroende på antalet variabler och formler som du behöver testa.

Datatabeller med en variabel    

Använd en datatabell med en variabel om du vill se hur olika värden för en variabel i en eller flera formler ändrar resultatet för de formlerna. Du kan till exempel använda en datatabell med en variabel för att se hur olika räntor påverkar en månatlig amortering med hjälp av funktionen BETALNING. Du anger variabelvärdena i en kolumn eller på en rad och resultaten visas i en kolumn eller på en rad intill.

I följande bild innehåller cell D2 betalningsformeln = BETALNING(B3/12,B4,-B5) som refererar till indatacellen B3.

Datatabeller med två variabler    

Använd en datatabell med två variabler om du vill se hur olika värden av två variabler i en formel ändrar resultaten för den formeln. Du kan till exempel använda en datatabell med två variabler om du vill se hur olika kombinationer av räntor och lånevillkor påverkar de månatliga amorteringarna.

I följande bild innehåller cell C2 betalningsformeln = BETALNING(B3/12,B4,-B5), som använder två indataceller, B3 och B4.

 

Datatabellberäkningar    

När ett kalkylblad beräknas om beräknas även alla datatabeller om, även om inga data har ändrats. Om du vill göra en snabbare beräkning av ett kalkylblad som innehåller en datatabell kan du ändra beräkningsalternativen så att kalkylbladet beräknas om automatiskt, men inte datatabellerna. Mer information finns i avsnittet Snabba upp beräkningen i ett kalkylblad som innehåller datatabeller.

En datatabell med en variabel innehåller indatavärdena antingen i en enda kolumn (kolumnorienterad) eller över en rad (radorienterad). Alla formler i en datatabell med en variabel får bara referera till en indatacell.

Följ anvisningarna nedan:

1. Skriv listan med värden som du vill ersätta i indatacellen – antingen en kolumn eller över en rad. Lämna några tomma rader och kolumner på vardera sidan av värdena.

2. Gör något av följande:

   • Om datatabellen är kolumnorienterad (variabelvärdena finns i en kolumn) skriver du formeln i cellen en rad ovanför och en cell till höger om kolumnen med värden. Den här datatabellen med en variabel är kolumnorienterad och formeln finns i cell D2.
     
     
     
     Om du vill undersöka effekterna av olika värden på andra formler anger du de ytterligare formlerna i cellerna till höger om den första formeln.

   • Om datatabellen är radorienterad (variabelvärdena finns på en rad) skriver du formeln i cellen en kolumn till vänster om det första värdet och en cell under raden med värden.
     
     Om du vill undersöka effekterna av olika värden på andra formler anger du de ytterligare formlerna i cellerna under den första formeln.

3. Markera cellområdet som innehåller de formler och värden som du vill ersätta. I exemplet ovan är området C2:D5.

4. På fliken Data klickar du på Konsekvensanalys > datatabell (i gruppen Dataverktyg eller gruppen Prognos i Excel 2016 ).

5. Gör något av följande:

   • Om datatabellen är kolumnorienterad anger du cellreferens för indatacellen i fältet Kolumnindatacell . I bilden ovan är indatacellen B3.

   • Om datatabellen är radorienterad anger du cellreferensen för indatacellen i fältet Radindatacell .

     Obs!: När du har skapat datatabellen kan du vilja ändra på resultatcellernas format. I bilden formateras resultatcellerna som valuta.

Formler som används i en datatabell med en variabel måste referera till samma indatacell.

Gör följande

1. Gör något av följande:

   • Om datatabellen är kolumnorienterad anger du den nya formeln i en tom cell till höger om en befintlig formel på den översta raden i datatabellen.

   • Om datatabellen är radorienterad anger du den nya formeln i en tom cell under en befintlig formel i den första kolumnen i datatabellen.

2. Markera det cellområde som innefattar datatabellen och den nya formeln.

3. På fliken Data klickar du på Konsekvensanalys > datatabell (i gruppen Dataverktyg eller i gruppen Prognos för Excel 2016 ).

4. Gör något av följande:

   • Om datatabellen är kolumnorienterad anger du cellreferensen för indatacellen i rutan Kolumnindatacell .

   • Om datatabellen är radorienterad anger du cellreferensen för indatacellen i rutan Radindatacell .

Datatabeller med två variabler använder en formel som innefattar två listor med indatavärden. Formeln måste referera till två olika indataceller.

Följ anvisningarna nedan:

1. I en cell i kalkylbladet anger du formeln som refererar till de två indatacellerna.

   I följande exempel , där formelns startvärden anges i cellerna B3, B4 och B5, skriver du formeln =BETALNING(B3/12,B4,-B5) i cell C2.

2. Skriv en lista med indatavärden i samma kolumn, under formeln.

   I det här fallet skriver du olika räntesatser i cellerna C3, C4 och C5.

3. Ange den andra listan på samma rad som formeln – till höger om den.

   Ange lånevillkoren (i månader) i cell D2 och E2.

4. Marker cellområdet som innehåller formeln (C2), både raden och kolumnen med värden (C3:C5 och D2:E2), och de celler där du vill ha de beräknade värdena (D3:E5).

   I det här fallet markerar du C2:E5.

5. Klicka på Konsekvensanalys >datatabell i gruppen Dataverktyg eller prognosgruppen (i Excel 2016 ) på fliken Data (i gruppen Dataverktyg eller prognosgruppen Excel 2016 ). 

6. I fältet Radindatacell anger du referensen till indatacellen för indatavärdena på raden.
   Skriv cell B4 i rutan Radindatacell .

7. I fältet Kolumnindatacell anger du referensen till indatacellen för indatavärdena i kolumnen.
   Skriv B3 i rutan Kolumnindatacell .

8. Klicka på OK.

Exempel på en datatabell med två variabler

En datatabell med två variabler kan visa hur olika kombinationer av räntesatser och lånevillkor påverkar månatliga amorteringar. I bilden här innehåller cell C2 betalningsformeln, =BETALNING(B3/12,B4,-B5), som använder två indataceller, B3 och B4.

När du anger det här beräkningsalternativet sker inga datatabellberäkningar när en beräkning görs i hela arbetsboken. Om du vill beräkna datatabellen manuellt markerar du dess formler och trycker på F9.

Följ de här stegen för att förbättra beräkningsprestandan:

1. Klicka på Arkiv > Alternativ > formler.

2. I avsnittet Beräkningsalternativ , under Beräkna, klickar du på Automatisk utom för datatabeller.

   Tips: Du kan också klicka på pilen på Beräkningsalternativ på fliken Formler och sedan klicka på Automatiska utom datatabeller (i gruppen Beräkning).

Du kan använda några andra Excel-verktyg för att utföra konsekvensanalyser om du har specifika mål eller större uppsättningar med variabla data.

Målsökning

Om du vet vilket resultat du kan förvänta dig av en formel, men inte vet exakt vilket indatavärde formeln behöver för att få det resultatet, använder du funktionen Goal-Seek. Se artikeln Använd målsökning för att hitta det resultat du vill ha genom att justera ett indatavärde.

Problemlösaren i Excel

Du kan använda Excel-tillägget Problemlösaren för att hitta det optimala värdet för en uppsättning indatavariabler. Problemlösaren fungerar med en grupp celler (så kallade beslutsvariabler eller helt enkelt variabla celler) som används för att beräkna formlerna i målsättnings- och begränsningscellerna. Problemlösaren justerar värdena i cellerna med beslutsvariabler för att uppfylla begränsningarna för begränsningsceller och ge det önskade resultatet för målsättningscellen. Läs mer i den här artikeln: Definiera och lösa ett problem med hjälp av Problemlösaren.