Dette er den anden artikel i serien med fem dele om dataanalyse i Excel. I dette afsnit vil jeg vise dig, hvordan du bruger to variabel datatabel i Excel.
Andre artikler i denne serie:
- Én variabel datatabel i Excel.
- Scenario Manager i Excel.
- Målsøgning i Excel.
- Excel -løsning.
Se video - To -variabel datatabel i Excel
Datatabel med to variabler er bedst egnet i situationer, hvor du vil se, hvordan det endelige resultat ændres, når to af inputvariablerne ændres samtidigt (i modsætning til One Variable Data Table, hvor kun en af inputvariablen ændres).
Hvis du vil analysere data, når mere end 2 variabler ændres, er scenariehåndteringen vejen frem.
Hvornår skal du bruge to variabel datatabel i Excel
Antag, at du har et datasæt som vist herunder:
I ovenstående datasæt har vi lånebeløb, rente og antal månedlige betalinger. Baseret på disse 3 inputvariabler beregnes den månedlige betaling (den er i rødt, da det er en pengestrøm). Følgende formel bruges til at beregne den månedlige betaling:
= PMT (B2/12, B3, B1)
Nu vil du måske foretage en analyse for at se, hvad der bør være den ideelle kombination af lånebeløb og antal månedlige betalinger, der passer til dine krav. For eksempel vil du måske beholde den månedlige betaling på $ 500 eller mindre og analysere, hvad kombinationen af lånebeløb og lejemål kan give dig dette.
I en sådan situation bør der bruges en datatabel med to variable.
Opsætning af to variabel datatabel i Excel
Her er trinene til at oprette en tabel med to variable i Excel:
- I en kolonne har du alle de forskellige værdier, du vil teste for Antal månedlige betalinger. I dette eksempel tester vi for 72, 84, 96… 240. Samtidig har de forskellige lånebeløbsværdier i rækken lige over kolonneværdierne (begyndende en celle til højre) som vist på billedet herunder.
- Skriv = B4 i celle D1, som er en række over værdierne i kolonnen. Dette er en konstruktion, der skal følges, når du arbejder med to variable datatabeller. Sørg også for, at værdien i celle D1 er afhængig af begge variablerne (antal månedlige betalinger og lånebeløb). Det virker ikke, hvis du manuelt indtaster værdien i celle D1.
I dette tilfælde refererer celle D1 til celle B4, som har en værdi beregnet ved hjælp af en formel, der bruger celler B1, B2 og B3.
- Nu er dataene alle klar til at blive brugt til en beregning med to variable datatabeller.
- Vælg data (D1: J16). Gå til fanen Data -> Dataværktøjer -> Hvad hvis analyse -> Datatabel
- I dialogboksen Datatabel skal du bruge følgende referencer:
- Rækkeindgangscelle: $ B $ 1
- Kolonneindgangscelle: $ B $ 3
- Klik på OK. Så snart du klikker på OK, udfylder det øjeblikkeligt alle de tomme celler i det valgte dataområde. Det giver dig hurtigt et overblik over månedlige betalinger for forskellige kombinationer af lånebeløb og antal månedlige betalinger.
Hvis du f.eks. Vil identificere de kombinationer af lånebeløb og antal månedlige betalinger, der ville resultere i en månedlig betaling på mindre end $ 500 pr. Måned, kan du blot bruge denne 2 -variabel datatabelmetode.
Bemærk:
- Når du har beregnet værdierne ved hjælp af datatabel, kan den ikke fortrydes ved hjælp af Control + Z. Du kan dog manuelt vælge alle værdierne og slette den.
- Du kan ikke slette/ændre en enkelt celle i hele sættet med beregnede værdier. Da dette er en matrix, skal du slette alle værdierne.
