Dette er den andre artikkelen i serien med fem deler om dataanalyse i Excel. I denne delen vil jeg vise deg hvordan du bruker to variabel datatabell i Excel.
Andre artikler i denne serien:
- Én variabel datatabell i Excel.
- Scenariobehandling i Excel.
- Målsøk i Excel.
- Excel -løsning.
Se video - Datatabell med to variabler i Excel
To-variabel datatabell er best egnet i situasjoner når du vil se hvordan det endelige resultatet endres når to av inndatavariablene endres samtidig (i motsetning til en variabel datatabell der bare en av inndatavariabelen endres).
Hvis du vil analysere data når mer enn 2 variabler endres, er scenariobehandling veien å gå.
Når skal jeg bruke to variabel datatabell i Excel
Anta at du har et datasett som vist nedenfor:
I datasettet ovenfor har vi lånebeløp, rente og antall månedlige betalinger. Basert på disse 3 inndatavariablene beregnes månedlig betaling (den er i rødt, da det er en utstrømning av penger). Følgende formel brukes til å beregne den månedlige betalingen:
= PMT (B2/12, B3, B1)
Nå kan det være lurt å gjøre en analyse for å se hva som bør være den ideelle kombinasjonen av lånebeløp og antall månedlige betalinger som passer ditt behov. For eksempel kan det være lurt å beholde den månedlige betalingen på $ 500 eller mindre, og analysere hva lånebeløp og leiekontrakt kan gi deg dette.
I en slik situasjon bør en to variabel datatabell brukes.
Sette opp to variabel datatabell i Excel
Her er trinnene for å sette opp en tabell med to variable i Excel:
- I en kolonne har du alle de forskjellige verdiene du vil teste for antall månedlige betalinger. I dette eksemplet tester vi for 72, 84, 96… 240. Samtidig har du de forskjellige lånebeløpsverdiene i raden like over kolonneverdiene (begynner en celle til høyre) som vist på bildet nedenfor.
- Skriv = B4 i celle D1, som er en rad over verdiene i kolonnen. Dette er en konstruksjon som må følges når du arbeider med to variable tabeller. Sørg også for at verdien i celle D1 er avhengig av begge variablene (Antall månedlige betalinger og lånebeløp). Det vil ikke fungere hvis du manuelt angir verdien i celle D1.
I dette tilfellet refererer celle D1 til celle B4, som har en verdi beregnet ved hjelp av en formel som bruker cellene B1, B2 og B3.
- Nå er dataene klare til å bli brukt til en beregning av datatabeller med to variabler.
- Velg dataene (D1: J16). Gå til Data -fanen -> Dataverktøy -> Hva om analyse -> Datatabell
- I dialogboksen Datatabell bruker du følgende referanser:
- Inndatacelle for rad: $ B $ 1
- Inngangscelle for kolonne: $ B $ 3
- Klikk OK. Så snart du klikker OK, fyller den alle de tomme cellene i det valgte dataområdet umiddelbart. Det gir deg raskt en oversikt over månedlige betalinger for en rekke kombinasjoner av lånebeløp og antall månedlige betalinger.
For eksempel, hvis du vil identifisere kombinasjonene av lånebeløp og antall månedlige betalinger som ville resultere i en månedlig betaling på mindre enn $ 500 per måned, kan du ganske enkelt bruke denne metoden med 2 variabelt datatabeller.
Merk:
- Når du har beregnet verdiene ved hjelp av datatabell, kan det ikke angres med Control + Z. Du kan imidlertid velge alle verdiene manuelt og slette det.
- Du kan ikke slette/endre én celle i hele settet med beregnede verdier. Siden dette er en matrise, må du slette alle verdiene.
