När du har en komplex formel som är beroende av flera variabler och vill förstå hur förändringar av dessa indata påverkar resultaten effektivt, är en What-If-analysdatatabell i Excel ett kraftfullt verktyg. Det låter dig se alla möjliga resultat med en snabb blick. Här är en steg-för-steg-guide om hur du skapar en datatabell i Excel. Dessutom kommer vi att diskutera några nyckelpunkter för att använda datatabeller effektivt och demonstrera andra operationer som att ta bort, redigera och räkna om datatabeller.

Vad är datatabell i Excel?

Skapa datatabell med en variabel

• Kolumnorienterad datatabell
• Radorienterad datatabell
• Flera formler för en-variabel datatabell

Skapa datatabell med två variabler

Nyckelpunkter med hjälp av datatabellerna

Andra operationer för att använda datatabeller

• Ta bort en datatabell
• Redigera datatabellresultat
• Beräkna om datatabellen manuellt

Vad är datatabell i Excel?

I Excel är en datatabell ett av verktygen What-If Analysis som låter dig experimentera med olika inmatningsvärden för formler och observera ändringarna i formelns utdata. Det här verktyget är otroligt användbart för att utforska olika scenarier och utföra känslighetsanalyser, särskilt när en formel är beroende av flera variabler.

Det finns två typer av datatabeller i Excel:

Datatabell med en variabel: Detta låter dig analysera hur olika värden för en variabel kommer att påverka en formel. Du kan ställa in en datatabell med en variabel i antingen ett radorienterat eller kolumnorienterat format, beroende på om du vill variera din inmatning över en rad eller nedåt i en kolumn.

Datatabell med två variabler: Den här typen låter dig se effekten av att ändra två olika variabler på en formels resultat. I en datatabell med två variabler varierar du värdena längs både en rad och en kolumn.

Skapa datatabell med en variabel

Att skapa en datatabell med en variabel i Excel är en värdefull färdighet för att analysera hur förändringar i en enskild indata kan påverka olika resultat. Det här avsnittet guidar dig genom processen att skapa kolumnorienterade, radorienterade och datatabeller med flera formel 1-variabler.

Fantastisk! Använda effektiva flikar i Excel som Chrome, Edge, Firefox och Safari!
Spara 50% av din tid och minska tusentals musklick åt dig varje dag!

---

Kolumnorienterad datatabell

En kolumnorienterad datatabell är användbar när du vill testa hur olika värden för en enskild variabel påverkar en formels utdata, med variabelvärdena listade i en kolumn. Låt oss överväga ett enkelt ekonomiskt exempel:

Anta att du överväger ett lån på 50,000 3 $, som du planerar att återbetala under en period av 36 år (motsvarande XNUMX månader). För att kunna utvärdera vilken månadsbetalning som skulle vara överkomlig baserat på din lön, är du intresserad av att utforska hur olika räntor skulle påverka det belopp du behöver betala varje månad.

Steg 1: Ställ in basformeln

För att beräkna betalningen kommer jag här att sätta räntan till 5%. I en cell B4, ange PMT-formeln, den beräknar den månatliga betalningen baserat på ränta, antal perioder och lånebelopp. Se skärmdump:

= -PMT($B$1/12, $B$2*12, $B$3)

Steg 2: Lista räntorna i en kolumn

I en kolumn listar du de olika räntorna du vill testa. Till exempel, lista värden från 4 % till 11 % i steg om 1 % i en kolumn, och lämna minst en tom kolumn till höger för resultaten. Se skärmdump:

Steg 3: Skapa datatabellen

1. Ange denna formel i cellen E2: = B4.
   
   Anmärkningar: B4 är cellen där din huvudformel finns, detta är formeln vars resultat du vill se förändras när du varierar räntan.
   
2. Välj intervallet som innehåller din formel, lista över räntor och angränsande celler för resultaten (välj t.ex. D2 till E10). Se skärmdump:
   
3. Gå till menyfliksområdet, klicka på Data fliken och klicka sedan på Vad-om-analys > data~~POS=TRUNC, se skärmdump:
   
4. I data~~POS=TRUNC i dialogrutan, klicka i Kolumninmatningscell box (eftersom vi använder en kolumn för inmatningsvärden) och välj variabelcellen som refereras till i din formel. I det här exemplet väljer vi B1 som innehåller räntan. Äntligen, klicka OK knapp, se skärmdump:
   
5. Excel kommer automatiskt att fylla de tomma cellerna bredvid var och en av dina variabelvärden (olika räntor) med motsvarande resultat. Se skärmdump:
   
6. Använd önskat talformat på resultaten (valuta) efter ditt behov. Nu skapas den kolumnorienterade datatabellen framgångsrikt, och du kan snabbt granska resultaten för att utvärdera vilka månatliga betalningsbelopp som skulle vara överkomliga för dig när räntan ändras. Se skärmdump:
   

Radorienterad datatabell

Att skapa en radorienterad datatabell i Excel innebär att du ordnar dina data så att variabelvärdena listas över en rad snarare än ner i en kolumn. Med exemplet ovan som referens, låt oss fortsätta med stegen för att slutföra skapandet av en radorienterad datatabell i Excel.

Steg 1: Lista räntorna i rad

Placera variabelvärdena (räntor) i en rad och se till att det finns minst en tom kolumn till vänster för formeln och en tom rad nedan för resultaten, se skärmdump:

Steg 2: Skapa datatabellen

1. Ange denna formel i cellen A9: = B4.
   
2. Välj intervallet som innehåller din formel, lista över räntor och angränsande celler för resultaten (välj t.ex. A8 till I9). Klicka sedan Data > Vad-om-analys > data~~POS=TRUNC.
3. I data~~POS=TRUNC i dialogrutan, klicka i Radinmatningscell box (eftersom vi använder en rad för inmatningsvärden) och välj variabelcellen som refereras till i din formel. I det här exemplet väljer vi B1 som innehåller räntan. Äntligen, klicka OK knapp, se skärmdump:
   
4. Använd önskat talformat på resultaten (valuta) efter ditt behov. Nu skapas den radorienterade datatabellen, se skärmdump:
   

Flera formler för en-variabel datatabell

Genom att skapa en datatabell med en variabel med flera formler i Excel kan du se hur en ändring av en enskild indata påverkar flera olika formler samtidigt. I exemplet ovan, vad händer om du vill se förändringen i räntesatserna på både amorteringar och total ränta? Så här ställer du in det:

Steg 1: Lägg till en ny formel för att beräkna den totala räntan

I cell B5 anger du följande formel för att beräkna den totala räntan:

=B4*B2*12-B3

Steg 2: Ordna datatabellens källdata

I en kolumn, lista de olika räntorna du vill testa, och lämna minst två tomma kolumner till höger för utfallen. Se skärmdump:

Steg 3: Skapa datatabellen:

1. Ange denna formel i cell E2: = B4 för att skapa en referens till återbetalningsberäkningen i de ursprungliga uppgifterna.
2. Ange denna formel i cell F2: = B5 för att skapa en referens till det totala intresset för originaldata.
   
3. Välj intervallet som innehåller dina formler, lista över räntor och angränsande celler för resultatet (välj t.ex. D2 till F10). Klicka sedan Data > Vad-om-analys > data~~POS=TRUNC.
4. I data~~POS=TRUNC i dialogrutan, klicka i Kolumninmatningscell box (eftersom vi använder en kolumn för inmatningsvärden) och välj variabelcellen som refereras till i din formel. I det här exemplet väljer vi B1 som innehåller räntan. Äntligen, klicka OK knapp, se skärmdump:
   
5. Använd önskat talformat på resultaten (valuta) efter ditt behov. Och du kan se resultaten för varje formel baserat på de varierande variabelvärdena.
   

Skapa datatabell med två variabler

En datatabell med två variabler i Excel visar effekten av olika kombinationer av två uppsättningar av variabelvärden på en formels resultat, och visar hur ändringar i två inmatningar av en formel samtidigt påverkar dess resultat.

Här har jag ritat en enkel skiss för att hjälpa dig att bättre förstå utseendet och strukturen hos en datatabell med två variabler.

Bygg på exemplet för att skapa en datatabell med en variabel, låt oss nu fortsätta att lära oss hur man skapar en datatabell med två variabler i Excel.

I datauppsättningen nedan har vi räntan, lånetiden och lånebeloppet, och vi har beräknat den månatliga betalningen med hjälp av denna formel: =-PMT($B$1/12, $B$2*12, $B$3) också. Här kommer vi att fokusera på två nyckelvariabler från vår data: ränta och lånebelopp, för att observera hur förändringar i båda dessa faktorer samtidigt påverkar återbetalningsbeloppen.

Steg 1: Ställ in de två växlande variablerna

1. I en kolumn listar du de olika räntorna du vill testa. se skärmdump:
   
2. I en rad anger du de olika lånebeloppsvärdena precis ovanför kolumnvärdena (med början från en cell till höger om formelcellen), se skärmbild:
   

Steg 2: Skapa datatabellen

1. Placera din formel i skärningspunkten mellan raden och kolumnen där du listade variabelvärdena. I det här fallet kommer jag att ange denna formel: = B4 in i cell E2. Se skärmdump:
   
2. Välj intervallet som inkluderar dina lånebelopp, räntor, formelcellen och cellerna där resultaten kommer att visas.
   
3. Klicka sedan Data > Vad-om-analys > data~~POS=TRUNC. I dialogrutan Datatabell:
   • I Radinmatningscell rutan, välj cellreferensen till indatacellen för variabelvärdena i raden (i det här exemplet innehåller B3 lånebeloppet).
   • I Kolumninmatningscelll ruta, välj cellreferens till indatacellen för variabelvärdena i kolumnen (B1 innehåller räntan).
   • Och klicka sedan på OK Knapp.
     
4. Nu kommer Excel att fylla i datatabellen med resultaten för varje kombination av lånebelopp och ränta. Det ger en direkt bild av hur olika kombinationer av lånebelopp och räntor påverkar den månatliga betalningen, vilket gör den till ett värdefullt verktyg för ekonomisk planering och analys.
   
5. Slutligen bör du tillämpa önskat talformat på resultaten (valuta) efter ditt behov.
   

Nyckelpunkter med hjälp av datatabellerna

• Den nyskapade datatabellen måste finnas i samma kalkylblad som originaldata.
• Utdata från datatabellen beror på formelcellen i källdatauppsättningen, och alla ändringar av denna formelcell kommer automatiskt att uppdatera utdata.
• När värden väl har beräknats med hjälp av datatabellen kan de inte ångras Ctrl + Z. Du kan dock manuellt välja alla värden och ta bort dem.
• Datatabellen genererar matrisformler, så enskilda celler i tabellen kan inte ändras eller raderas.

Andra operationer för att använda datatabeller

När du har skapat en datatabell kan du behöva ytterligare åtgärder för att hantera den effektivt, inklusive att ta bort datatabellen, ändra dess resultat och utföra manuella omräkningar.

Ta bort en datatabell

Eftersom datatabellresultaten beräknas med en matrisformel kan du inte ta bort ett enskilt värde från datatabellen. Du kan dock bara ta bort hela datatabellen.

Markera alla datatabellceller eller bara cellerna med resultaten och tryck sedan på Radera tangenten på tangentbordet.

Redigera datatabellresultat

Det är faktiskt inte möjligt att redigera enskilda celler i en datatabell direkt, eftersom de innehåller matrisformler som Excel genererar automatiskt.

För att göra ändringar måste du vanligtvis ta bort den befintliga datatabellen och sedan skapa en ny med önskade ändringar. Detta innebär att justera din basformel eller inmatningsvärdena och sedan ställa in datatabellen igen för att återspegla dessa ändringar.

Beräkna om datatabellen manuellt

Normalt räknar Excel om alla formler i alla öppna arbetsböcker varje gång en ändring görs. Om en stor datatabell som innehåller många variabelvärden och komplexa formler får din Excel-arbetsbok att sakta ner.

För att undvika att Excel automatiskt utför beräkningar för alla datatabeller varje gång en ändring görs i arbetsboken, kan du byta beräkningsläge från Automatisk till Manuell. Gör så här:

Gå till Formler fliken och klicka sedan på Beräkningsalternativ > Automatisk Utom för datatabeller, se skärmdump:

När du har ändrat den här inställningen, när du räknar om hela din arbetsbok, uppdaterar Excel inte längre automatiskt beräkningarna i dina datatabeller.

Om du behöver uppdatera din datatabell manuellt, välj bara cellerna där resultaten (celler som innehåller TABLE()-formlerna) visas och tryck sedan på F9 nyckel.

Genom att följa stegen som beskrivs i den här guiden och ha huvudpunkterna i åtanke kan du effektivt använda datatabeller för dina dataanalysbehov. Om du är intresserad av att utforska fler Excel-tips och tricks, erbjuder vår webbplats tusentals självstudier, klicka här för att komma åt dem. Tack för att du läser, och vi ser fram emot att ge dig mer användbar information i framtiden!