detta är den första artikeln i den femdelade serien om dataanalys i Excel. I det här avsnittet visar jag hur du skapar och använder en variabel Datatabell i Excel.

ladda ner exempelfil

titta på video – en-variabel Datatabell i Excel

en variabel Datatabell i Excel passar bäst i situationer när du vill se hur slutresultatet ändras när du ändrar en av inmatningsvariablerna. Om du vill ändra två variabler använder du två variabler datatabell eller Scenario Manager.

när du ska använda en variabel Datatabell i Excel

anta att du har en datauppsättning som visas nedan:

i ovanstående dataset har vi lånebeloppet, räntan och antalet månatliga betalningar.

det månatliga betalningsvärdet beräknas baserat på dessa tre variabler (Det är i rött eftersom det är ett utflöde av pengar). Följande formel används för att beräkna den månatliga betalningen:

=PMT(B2/12,B3,B1)

Nu kanske du vill göra en analys för att se vilket antal månatliga betalningar som passar ditt tillstånd och hur skulle den månatliga betalningen variera beroende på den. Till exempel kanske du vill behålla ditt lån (antal månatliga betalningar) så att ditt månatliga betalningsbelopp inte är mer än $1,000.

ett snabbt sätt är att ändra data i denna tabell och formeln uppdateras automatiskt. Men om du vill testa om för många olika antal månatliga betalningar, skulle en variabel Datatabell i Excel vara vägen att gå.

ställa in en variabel Datatabell i Excel

här är stegen för att ställa in en variabel datatabell i Excel:

• i en kolumn har du alla olika värden på antalet månatliga betalningar som du vill testa. I det här exemplet testar vi för 72, 84, 96… 240.
• Type =B4 i cell E1, som är en rad ovanför värdena i den intilliggande kolumnen till höger. Detta är en konstruktion som måste följas när du arbetar med en variabel datatabell i Excel. Se också till att värdet i cell E1 är beroende av antalet månatliga betalningar. Det fungerar inte om du manuellt anger värdet i cell E1.
  i detta fall hänvisar cell E1 till cell B4, som har ett värde beräknat med en formel som använder cellerna B1, B2 och B3.
• nu är data alla inställda för att användas i en variabel datatabell.
• Välj data (D1:E16). Gå till fliken Data – >dataverktyg –>vad händer om analys –> Datatabell.
• i dialogrutan Datatabell, se cell B3 i Kolumninmatningscellfält. Eftersom vi använder en datatabell med en variabel, lämna Radinmatningscellen tom. Vi valde också Kolumninmatningscellalternativ eftersom data är strukturerad i en kolumn.
• Klicka på OK. Så snart du klickar på OK fyller den automatiskt cellerna intill de variabla datapunkterna (antal månatliga betalningar).

Om du till exempel vill beräkna vilket antal månatliga betalningar du ska använda för att få månadsbetalningen mindre än $1000. Du kan enkelt välja 144 månader (12 år).

Obs:

• när du har beräknat värdena med hjälp av datatabellen kan den inte ångras med Control + Z. Du kan dock manuellt välja alla värden och ta bort den.
• Du kan inte ta bort / ändra någon cell i hela uppsättningen beräknade värden. Eftersom det här är en array måste du radera alla värden.
• Du kan göra samma beräkning genom att ordna data horisontellt. Den enda skillnaden är att du måste använda Radinmatningscell i dialogrutan Datatabell (istället för kolumninmatningscell).

ladda ner filen.. Prova själv

du kanske också gillar följande Excel-handledning:

• beräkning av standardavvikelse i Excel.
• göra Histogram i Excel.
• beräkning av vägt genomsnitt i Excel.
• beräkning av CAGR i Excel.