Förtrolltabell i Excel

Ofta måste du beräkna slutresultatet för olika kombinationer av ingångsdata. Således kommer användaren att kunna utvärdera alla möjliga åtgärdsalternativ, välj de, resultatet av den interaktion som den uppfyller, och slutligen välja det optimala alternativet. I Excel finns det ett specialverktyg för att utföra den här uppgiften - "datatabell" ("substitutionstabell"). Låt oss ta reda på hur du använder dem för att utföra ovanstående scenarier.

Dessutom kan det noteras att mängden månadsbetalning på 12,5% per år som erhållits till följd av tillämpningen av substitutionstabellen motsvarar värdet på samma antal intressen som vi mottog genom att tillämpa PL-funktionen. Detta visar återigen korrektheten av beräkningen.

Efter att ha analyserat den här tabellen, bör det sägas att, som vi ser, bara med en hastighet på 9,5% per år, visar det sig vara en acceptabel nivå av månadsbetalning (mindre än 29 000 rubel).

Lektion: Beräkning av livränta betalar i Excel

Metod 2: Använd ett verktyg med två variabler

För att hitta banker för närvarande, som ger ett lån under 9,5% per år, är det mycket svårt, om alls verkligen. Låt oss därför se vilka alternativ som finns på en acceptabel nivå av den månatliga betalningen vid olika kombinationer av andra variabler: omfattningen av lånets och kreditperiodens storlek. Samtidigt lämnas räntan oförändrad (12,5%). För att lösa denna uppgift hjälper vi verktyget "datatabell" med två variabler.

1. Blacksmith Ny Tabular Array. Nu i namnen på kolumnerna kommer en kreditperiod (från 2 till 6 år i månader i ett steg på ett år), och i linjer - Lånets storlek (från 850 000 till 950000 rubel med ett steg av 10 000 rubel). I det här fallet är det obligatoriska tillståndet att cellen, i vilken beräkningsformeln är belägen (i vårt fall, PLT) var belägen på gränsen av namnen på rader och kolumner. Utan att utföra detta tillstånd fungerar inte verktyget när du använder två variabler.



2. Därefter tilldelar vi hela tabellintervallet, inklusive namnet kolumner, rader och celler med PLT-formeln. Gå till fliken "Data". Som tidigare klickar du på "analysen" vad om "", i verktygsfältet "arbetar med data". I listan som öppnas väljer du "Databordet ...".



3. Toolfönstret "datatabell" startar. I det här fallet behöver vi båda fälten. I fältet "Substitutionsvärden på kolumner i", ange koordinaterna för cellen som innehåller låneperioden i primärdata. I fältet "Substitutionsvärden via" anger du adressen till cellen på källparametrarna som innehåller värdet av lånets kropp. Efter att alla data anges. Lera på "OK" -knappen.



4. Programmet utför beräkningen och fyller tabellens dataintervall. Vid korsningen av rader och kolumner kan du nu observera vad som är den månatliga betalningen, med motsvarande värde av årlig procent och den angivna låneperioden.



5. Som du kan se, är det ganska många värden. För att lösa andra uppgifter kan det vara ännu mer. För att göra emissionen av resultaten mer visuell och omedelbart bestämma vilka värden som inte uppfyller det angivna tillståndet, kan du använda visualiseringsverktyg. I vårt fall blir det villkorligt formatering. Vi markerar alla värden på tabellområdet, med undantag av raderna i raderna och kolumnerna.



6. Vi flyttar till fliken "Hem" och ler på ikonen "Villkorlig formatering". Det ligger i "stilar" -verktygsblocket på bandet. I den avbrytande menyn väljer du objektet "Regler för fördelning av celler". I den extra listan över att klicka på positionen "mindre ...".



7. Efter detta öppnas fönstret för villkorliga formateringsinställningar. I det vänstra fältet anger vi det belopp som är mindre än vilka celler som kommer att markeras. När du kommer ihåg, uppfyller vi oss där den månatliga lånebetalningen blir mindre än 29 000 rubel. Ange det här numret. På rätt fält är det möjligt att välja urvalsfärgen, även om du kan lämna den som standard. När alla nödvändiga inställningar är inmatat, ler på "OK" -knappen.



8. Därefter kommer alla celler, de värden som motsvarar det ovan beskrivna tillståndet att markeras med färg.

Efter att ha analyserat tabellmatrisen kan du göra några slutsatser. Som du kan se, med den nuvarande utlåningstiden (36 månader) för att investera i ovannämnda belopp av den månatliga betalningen, måste vi ta ett lån som inte överstiger 80 000.00 rubel, det vill säga 40 000 är mindre än ursprungligen planerat.

Om vi ​​fortfarande har för avsikt att ta ett lån på 900 000 rubel, bör kreditperioden vara 4 år (48 månader). Endast i det här fallet kommer storleken på den månatliga betalningen inte att överstiga den etablerade gränsen för 29 000 rubel.

Således, med hjälp av denna tabellmatris och analysera "för" och "mot" varje alternativ, kan låntagaren ta ett visst beslut om utlåningsvillkor genom att välja det mest svaralternativ som möjligt.

Naturligtvis kan substitutionsbordet inte bara användas för att beräkna kreditalternativ, utan också för att lösa ett flertal andra uppgifter.

Lektion: Villkorlig formatering i Excel

I allmänhet bör det noteras att substitutionsbordet är ett mycket användbart och relativt enkelt verktyg för att bestämma resultatet med olika kombinationer av variabler. Applicera både villkorad formatering samtidigt med den, dessutom kan du visualisera den mottagna informationen.