Detta är den andra artikeln i serien med fem delar om dataanalys i Excel. I det här avsnittet kommer jag att visa dig hur du använder två variabeltabell i Excel.
Andra artiklar i denna serie:
- En tabell med variabel data i Excel.
- Scenariohanterare i Excel.
- Målsök i Excel.
- Excel -lösning.
Titta på video - Datatabell med två variabler i Excel
Tvåvariabel datatabell är bäst lämpad i situationer när du vill se hur det slutliga resultatet förändras när två av inmatningsvariablerna ändras samtidigt (mot en tabell med variabel data där endast en av inmatningsvariabeln ändras).
Om du vill analysera data när mer än 2 variabler ändras, är scenariohanteraren rätt väg att gå.
När ska du använda tabellen med två variabler 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. Baserat på dessa 3 inmatningsvariabler beräknas månadsbetalning (den ä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 vad som bör vara den perfekta kombinationen av lånebelopp och antal månatliga betalningar som passar ditt krav. Till exempel kanske du vill behålla den månatliga betalningen på $ 500 eller mindre och analysera vad lånebelopp och upplåtelsekombination kan ge dig detta.
I en sådan situation bör en tabell med två variabler användas.
Upprätta två tabeller med variabel data i Excel
Här är stegen för att skapa en tabell med två variabler i Excel:
- I en kolumn har du alla olika värden som du vill testa för antal månatliga betalningar. I det här exemplet testar vi för 72, 84, 96… 240. Samtidigt har vi de olika lånebeloppsvärdena i raden strax ovanför kolumnvärdena (börjar en cell till höger) som visas på bilden nedan.
- Skriv = B4 i cell D1, som är en rad ovanför värdena i kolumnen. Detta är en konstruktion som måste följas när du arbetar med två variabla datatabeller. Se också till att värdet i cell D1 är beroende av båda variablerna (antal månatliga betalningar och lånebelopp). Det fungerar inte om du manuellt anger värdet i cell D1.
I detta fall hänvisar cell D1 till cell B4, som har ett värde beräknat med hjälp av en formel som använder cellerna B1, B2 och B3.
- Nu är alla data inställda för att användas för en beräkning av tabeller med två variabler.
- Välj data (D1: J16). Gå till fliken Data -> Dataverktyg -> Vad händer om analys -> Datatabell
- I rutan Datatabell, använd följande referenser:
- Radinmatningscell: $ B $ 1
- Kolumninmatningscell: $ B $ 3
- Klicka på OK. Så snart du klickar på OK fylls det omedelbart i alla tomma celler i det valda dataområdet. Det ger dig snabbt en vy över månatliga betalningar för olika kombinationer av lånebelopp och antal månatliga betalningar.
Om du till exempel vill identifiera kombinationerna av lånebelopp och antal månatliga betalningar som skulle resultera i en månadsbetalning på mindre än $ 500 per månad, kan du helt enkelt använda denna 2 -variabeltabellmetod.
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.
