Detta är den första artikeln i den femdelade serien om dataanalys i Excel. I det här avsnittet kommer jag att visa dig hur du skapar och använder en tabell med en variabel i Excel.
Andra artiklar i dataanalysserien:
- Två variabla datatabeller i Excel.
- Scenariohanterare i Excel.
- Målsök i Excel.
- Excel -lösning.
Titta på video - tabell med en variabel i Excel
En variabel datatabell i Excel är mest lämpad i situationer när du vill se hur det slutliga resultatet ändras när du ändrar en av inmatningsvariablerna. Om du vill ändra två variabler använder du tabellen med två variabler eller Scenariohanteraren.
När ska du använda en tabell med variabel data i Excel
Anta att du har en datamängd som visas nedan:
I ovanstående datamängd har vi lånebelopp, ränta och antal månatliga betalningar.
Värdet för månadsbetalning beräknas utifrån 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 månadsbetalningen variera utifrån det. Till exempel kanske du vill behålla lånetiden (antal månatliga betalningar) så att ditt månatliga betalningsbelopp inte är mer än $ 1000.
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 är en tabell med en variabel data i Excel rätt väg att gå.
Upprätta en tabell med en variabel i Excel
Här är stegen för att ställa in en tabell med en variabel i Excel:
- I en kolumn har du alla olika värden för antalet månatliga betalningar som du vill testa. I det här exemplet testar vi för 72, 84, 96 … 240.
- Skriv = 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 det här fallet 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 datan klar för användning i tabellen En variabel.
- Välj data (D1: E16). Gå till fliken Data -> Dataverktyg -> Vad händer om analys -> Datatabell.
- I dialogrutan Datatabell hänvisar du till cell B3 i fältet kolumninmatningscell. Eftersom vi använder en datatabell med en variabel, lämnar du radinmatningscellen tom. Vi valde också kolumninmatningscellalternativ eftersom data är strukturerade i en kolumn.
- Klicka på OK. Så snart du klickar på OK fylls det automatiskt i 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 ha den månatliga betalningen mindre än $ 1000. Du kan enkelt välja 144 månader (12 år).
Notera:
- När du har beräknat värdena med datatabell kan det inte ångras med hjälp av Control + Z. Du kan dock manuellt välja alla värden och radera det.
- Du kan inte radera/ändra någon cell i hela uppsättningen beräknade värden. Eftersom detta är en matris måste du ta bort 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 radinmatningscellen i datatabellens dialogruta (istället för kolumninmatningscellen).
