Доста често, треба да го пресметате конечниот резултат за различни комбинации на влезни податоци. Така, корисникот ќе може да ги оцени сите можни опции за активности, да ги избере оние чии резултати од интеракција го задоволуваат и, конечно, да ја избере најоптималната опција. Во Excel, за извршување на оваа задача, постои специјална алатка - "Табела со податоци" (Табела за замена) Ајде да откриеме како да го искористиме за да ги завршиме горенаведените сценарија.
Прочитајте исто така: Избор на параметар во Excel
Користење на табела со податоци
Инструмент "Табела со податоци" Наменет е да се пресмета резултатот за различни варијации на една или две дефинирани променливи. По пресметката, сите можни опции се појавуваат во форма на табела, која се нарекува матрица на анализа на фактори. "Табела со податоци" се однесува на група на алатки „Што ако анализа“, која е поставена на лентата во јазичето „Податоци“ во блок "Работа со податоци". Пред Excel 2007, оваа алатка беше наречена Табела за замена, што дури и попрецизно ја одразуваше нејзината суштина од сегашното име.
Табелата за пребарување може да се користи во многу случаи. На пример, типична опција е кога треба да се пресмета износот на месечната исплата на заем за различни варијации на периодот на кредитирање и износот на заемот, или периодот на кредитирање и каматната стапка. Исто така, оваа алатка може да се користи во анализата на моделите на инвестициските проекти.
Но, исто така треба да бидете свесни дека прекумерната употреба на оваа алатка може да доведе до системско сопирање, бидејќи податоците се раскажуваат постојано. Затоа, се препорачува во малите табели да се решат слични проблеми да не се користи оваа алатка, туку да се користи копирање со формула со помош на маркерот за пополнување.
Оправдана апликација "Табели со податоци" е само во големи опсези на табели, кога копирањето на формулите може да потрае многу време, а за време на самата постапка веројатноста за грешки се зголемува. Но, во овој случај, се препорачува да се оневозможи автоматската пресметка на формулите во опсегот на табелата за супституција за да се избегне непотребно оптоварување на системот.
Главната разлика помеѓу различните намени на табелата со податоци е бројот на варијабли вклучени во пресметката: една променлива или две.
Метод 1: користете ја алатката со една променлива
Веднаш да ја разгледаме опцијата кога се користи табелата со податоци со една варијабилна вредност. Земете го најтипичниот пример за позајмување.
Значи, во моментов ни се нудат следниве услови за заем:
- Мандат на заем - 3 години (36 месеци);
- Износ на заем - 900.000 рубли;
- Каматна стапка - 12,5% на годишно ниво.
Плаќањата се случуваат на крајот на периодот на плаќање (месец) според шемата за ануитет, односно во еднакви акции. Во исто време, на почетокот на целиот рок на заем, значаен дел од плаќањата се плаќањата на камати, но како што телото се намалува, каматните плаќања се намалуваат, а се зголемува и износот на отплата на самото тело. Вкупната исплата, како што споменавме погоре, останува непроменета.
Неопходно е да се пресмета колкава ќе биде износот на месечната исплата, вклучително и отплатата на заемот и каматата. За ова, Excel има оператор PMT.
PMT спаѓа во групата на финансиски функции и нејзината задача е да ја пресмета месечната исплата на заемот на ануитет, заснована врз износот на телата на заемот, терминот на заемот и каматната стапка. Синтаксата на оваа функција е претставена како
= PLT (стапка; nper; ps; bs; тип)
Понуда - аргумент што ја одредува каматната стапка на кредитните плаќања. Индикаторот е поставен за периодот. Периодот на исплата е еднаков на еден месец. Затоа, годишната стапка од 12,5% треба да се подели со бројот на месеци за една година, односно 12.
„Нпер“ - аргумент што го одредува бројот на периоди за целиот рок на заем. Според нашиот пример, периодот е еден месец, а терминот на заемот е 3 години или 36 месеци. Така, бројот на периоди ќе биде рано 36.
„ПС“ - аргумент што ја одредува сегашната вредност на заемот, односно тоа е големината на телото на заемот во моментот на издавањето. Во нашиот случај, оваа бројка е 900.000 рубли.
„БС“ - аргумент што ја означува големината на телото на заемот во моментот на целосна исплата. Секако, овој индикатор ќе биде еднаков на нула. Овој аргумент е по избор. Ако го прескокнете, се претпоставува дека е еднакво на бројот „0“.
„Тип“ - исто така и изборен аргумент. Тој објавува кога точно ќе се изврши плаќањето: на почетокот на периодот (параметар - "1") или на крајот на периодот (параметар - "0") Како што се сеќаваме, нашата исплата е направена на крајот на календарскиот месец, односно вредноста на овој аргумент ќе биде еднаква на "0". Но, со оглед на фактот дека овој индикатор не е задолжителен, и по дифолт, ако не се користи, вредноста се подразбира дека е еднаква "0", тогаш во наведениот пример може да се испушти целосно.
- Значи, продолжуваме со пресметката. Изберете ќелија на листот каде ќе се прикаже пресметаната вредност. Кликнете на копчето "Вметни функција".
- Започнува Волшебник на карактеристики. Се движиме во категоријата „Финансиски“изберете го името од списокот „ПЛТ“ и кликнете на копчето „ОК“.
- После ова, активиран е прозорецот со аргументи од горенаведената функција.
Ставете го курсорот на полето Понуда, по што ќе кликнеме на ќелијата на листот со вредноста на годишната каматна стапка. Како што можете да видите, неговите координати веднаш се прикажани во полето. Но, како што се сеќаваме, ни треба месечна стапка, и затоа резултатот го делиме на 12 (/12).
На полето „Нпер“ на ист начин ги внесуваме координатите на ќелиите на заемот. Во овој случај, не треба да споделите ништо.
На полето Пс мора да ги наведете координатите на ќелијата што ја содржат вредноста на заемот. Ние го правиме тоа Исто така, ставивме знак пред прикажаните координати "-". Факт е дека функцијата PMT по дифолт го дава конечниот резултат со негативен знак, со право размислувајќи ја месечната загуба на исплата на заем. Но, за јасност во примената на табелата со податоци, потребен ни е овој број да биде позитивен. Затоа, ставаме знак минус пред еден од аргументите на функцијата. Познато е множење минус на минус на крајот дава плус.
Во полињата „БС“ и „Тип“ податоците не се внесуваат воопшто. Кликнете на копчето „ОК“.
- После тоа, операторот го пресметува и прикажува резултатот од вкупната месечна исплата во однапред одредена ќелија - 30108,26 рубли. Но, проблемот е во тоа што заемопримачот е во можност да плати максимум 29,000 рубли месечно, односно тој треба да најде банка што нуди услови со пониска каматна стапка, или да го намали телото на заемот, или да го зголеми терминот на заемот. Табелата за пребарување ќе ни помогне да ги разбереме различните опции.
- Прво, користете ја табелата за пребарување со една променлива. Ајде да видиме како ќе се промени износот на задолжителната месечна исплата со различни варијации на годишната стапка, почнувајќи од 9,5% годишно и завршува 12,5% на годишно ниво во прираст 0,5%. Сите други услови остануваат непроменети. Ние цртаме опсег на табели, имињата на колоните од кои ќе одговараат на различни варијации на каматната стапка. Со оваа линија "Месечни плаќања" остави како што е. Неговата прва ќелија треба да ја содржи формулата што ја пресметавме порано. За повеќе информации, можете да додадете линии "Вкупен износ на заем" и „Вкупен интерес“. Колоната во која се наоѓа пресметката е направена без заглавие.
- Следно, ние ја пресметуваме вкупната сума на заемот под тековни услови. За да го направите ова, изберете ја првата ќелија од редот "Вкупен износ на заем" и помножете ја содржината на клетките "Месечна исплата" и „Термин на заемот“. После тоа, кликнете на копчето Внесете.
- За да се пресмета вкупниот износ на камата според тековните услови, ние на сличен начин го одземаме износот на телото на заемот од вкупниот износ на заемот. За да го прикажете резултатот на екранот, кликнете на копчето Внесете. Така, добиваме сума што ја плаќаме кога го враќаме заемот.
- Сега е време да ја примените алатката "Табела со податоци". Ја избираме целата низа на табелата, освен имињата на редови. После тоа, одете на јазичето „Податоци“. Кликнете на копчето на лентата „Што ако анализа“која се наоѓа во групата алатки "Работа со податоци" (во Excel 2016, група на алатки „Прогноза“) Потоа се отвора мало мени. Во него избираме позиција "Табела со податоци ...".
- Се отвора мал прозорец, кој се нарекува "Табела со податоци". Како што можете да видите, има две полиња. Бидејќи работиме со една променлива, потребна ни е само една од нив. Бидејќи ја менуваме променливата колона по колона, ќе го користиме полето Замени вредности на колоните во. Поставете го покажувачот таму и потоа кликнете на ќелијата во оригиналната база на податоци што содржи тековен процент. Откако ќелиите ќе се прикажат во полето, кликнете на копчето „ОК“.
- Алатката го пресметува и пополнува целиот табеларен опсег со вредности што одговараат на различни опции за каматната стапка. Ако го поставите курсорот во кој било елемент од оваа област на табелата, можете да видите дека лентата со формула не ја прикажува вообичаената формула за пресметување на плаќањето, туку посебна формула за нераскинлива низа. Тоа е, сега е невозможно да се променат вредностите во одделни клетки. Можете да ги избришете резултатите од пресметката само сите заедно, а не одделно.
Покрај тоа, може да се види дека месечната исплата на 12,5% на годишно ниво добиена како резултат на примена на табелата за пребарување одговара на вредноста за истата сума на камата што ја добивме со примена на функцијата PMT. Ова уште еднаш ја докажува исправноста на пресметката.
По анализирањето на оваа низа на табели, треба да се каже дека, како што можете да видите, само со стапка од 9,5% на годишно ниво добиваме прифатливо ниво на месечно плаќање (помалку од 29.000 рубли).
Лекција: Пресметка на ануитет плаќање во Excel
Метод 2: користете ја алатката со две променливи
Се разбира, да се најде во моментов банките кои издаваат заеми од 9,5% на годишно ниво е многу тешко, ако не и невозможно. Затоа, ќе видиме кои опции постојат за да се инвестира во прифатливо ниво на месечна исплата за различни комбинации на други варијабли: големината на телото на заемот и терминот на заемот. Во овој случај, каматната стапка ќе остане непроменета (12,5%). Во решавањето на овој проблем, алатка ќе ни помогне. "Табела со податоци" користејќи две варијабли.
- Ние цртаме нова низа на табели. Сега во имињата на колоните ќе биде наведен терминот за заем (од 2 порано 6 години по месеци во пораст од една година), и во редови - големината на заемот (од 850000 порано 950000 рубли во прираст 10000 рубли). Во овој случај, предуслов е дека ќелијата во која се наоѓа формулата за пресметка (во нашиот случај PMT), сместена на границата на имињата на редови и колони. Без оваа состојба, алатката нема да работи кога користите две променливи.
- Потоа изберете го целиот резултат на опсег на табели, вклучувајќи ги имињата на колоните, редовите и ќелијата со формулата PMT. Одете на јазичето „Податоци“. Како и претходниот пат, кликнете на копчето „Што ако анализа“, во групата алатки "Работа со податоци". Во списокот што се отвора, изберете "Табела со податоци ...".
- Прозорецот со алатки започнува "Табела со податоци". Во овој случај, ни требаат и двете полиња. На полето Замени вредности на колоните во наведете ги координатите на ќелијата што содржат рок на заем во примарните податоци. На полето "Замени вредности ред по ред во" наведете ја адресата на ќелијата на почетните параметри што ја содржи вредноста на заемот. По внесувањето на сите податоци. Кликнете на копчето „ОК“.
- Програмата ја извршува пресметката и го пополнува опсегот на табелата со податоци. На пресекот на редови и колони сега е можно да се набудува што точно ќе биде месечната исплата, со соодветниот износ на годишна камата и наведениот термин за заем.
- Како што можете да видите, има многу вредности. За решавање на други проблеми, може да има уште повеќе. Затоа, за да го направите резултатот визуелен и веднаш да одредите кои вредности не ја задоволуваат дадената состојба, можете да користите алатки за визуелизација. Во нашиот случај, ова ќе биде условно форматирање. Ние ги избираме сите вредности на опсегот на табелата, со исклучок на насловите на редот и колоната.
- Преместете на јазичето „Дома“ и кликнете на иконата Условно форматирање. Се наоѓа во блокот со алатки. Стилови на снимката. Во менито што се отвора, изберете Правила за избор на ќелии. Во дополнителниот список, кликнете на позицијата "Помалку ...".
- По ова, се отвора прозорецот за поставки за условно форматирање. Во левото поле означете ја вредноста помалку од која ќе бидат избрани клетките. Како што се сеќаваме, задоволни сме со услов месечната исплата на заем да биде помала од 29000 рубли. Ние го внесуваме овој број. Во вистинското поле, можете да ја изберете бојата за истакнување, иако може да ја оставите стандардно. Откако ќе ги внесете сите потребни поставки, кликнете на копчето „ОК“.
- После тоа, ќе бидат обележани сите клетки чии вредности одговараат на горенаведената состојба.
Откако ја анализиравме низата на табели, можеме да извлечеме некои заклучоци. Како што можете да видите, со постојниот рок на заем (36 месеци), за да инвестираме во наведениот износ на месечна исплата, треба да земеме заем не поголем од 860000,00 рубли, односно 40,000 помалку од првично планираниот.
Ако сè уште имаме намера да земеме заем од 900.000 рубли, тогаш терминот на заемот треба да биде 4 години (48 месеци). Само во овој случај, месечната исплата нема да го надмине утврдениот лимит од 29.000 рубли.
Така, користејќи ја оваа низа на табели и анализирајќи ги добрите и лошите страни на секоја опција, заемопримачот може да донесе одредена одлука за условите на заемот, избирајќи ја најсоодветната опција од сите можни.
Се разбира, табелата за пребарување може да се користи не само за да се пресметаат опциите за кредитирање, туку и да се решат многу други проблеми.
Лекција: Условно форматирање во Excel
Општо, треба да се напомене дека табелата за пребарување е многу корисна и релативно едноставна алатка за утврдување на резултатот за различни комбинации на променливи. Со користење на условно форматирање во исто време, покрај тоа, можете да ги визуелизирате добиените информации.
