Доста често, потребно е да се пресмета конечниот резултат за различни комбинации на влезни податоци. Така, корисникот ќе може да ги процени сите можни опции за акција, да ги избере оние чии резултати од интеракцијата го задоволуваат, и, конечно, да изберат најоптимална опција. Во Excel, постои специјална алатка за оваа задача - "Табела на податоци" ("Пребарување табела"). Ајде да дознаеме како да го искористиме за да ги реализираме погоре наведените сценарија.
Видете исто така: Избор на параметри во Excel
Користење на табелата со податоци
Алатка "Табела на податоци" тој е дизајниран да го пресмета резултатот со различни варијации на една или две дефинирани варијабли. По пресметувањето, сите можни опции ќе се појават во форма на табела, која се нарекува матрица на факторска анализа. "Табела на податоци" се однесува на група алатки "Што-ако" анализакоја е поставена на панделка во јазичето "Податоци" во блок "Работа со податоци". Пред Excel 2007, оваа алатка носеше име. "Пребарување табела"што уште попрецизно ја одразува својата суштина од сегашното име.
Табелата за пребарување може да се користи во многу случаи. На пример, типична опција е кога треба да се пресмета износот на месечна исплата на заем со различни варијации на периодот на кредитирање и износот на кредитот, или периодот на кредитирање и каматна стапка. Оваа алатка исто така може да се користи кога се анализираат моделите на инвестициски проекти.
Но, исто така, треба да бидете свесни дека прекумерната употреба на оваа алатка може да доведе до системско сопирање, бидејќи податоците постојано се пресметуваат. Затоа, се препорачува да не се користи оваа алатка во мали табеларни низи за решавање на слични проблеми, туку да се примени копирањето на формулите со помош на маркерот за пополнување.
Оправдана апликација "Податоци табели" е само во големи табеларни опсези, кога копирањето на формулите може да потрае многу време, а за време на самата постапка веројатноста за појава на грешки е зголемена. Но, дури и во овој случај, се препорачува да се оневозможи автоматското пресметување на формулите во опсегот на табелата за пребарување, со цел да се избегне непотребно оптоварување на системот.
Главната разлика помеѓу различните употреби на табелата на податоци е бројот на варијабли вклучени во пресметката: една променлива или две.
Метод 1: користете ја алатката со една променлива
Веднаш да ја разгледаме опцијата кога табелата со податоци се користи со една променлива вредност. Земете најкарактеристичен пример за кредитирање.
Значи, во моментов ни се нудат следните кредитни услови:
- Кредитен период - 3 години (36 месеци);
- Износ на заем - 900.000 рубли;
- Каматна стапка - 12,5% на годишно ниво.
Плаќањата се вршат на крајот од периодот на плаќање (месец) со користење на ануитетната шема, односно во еднакви акции. Во исто време, на почетокот на целиот период на заем, каматните исплати сочинуваат значителен дел од плаќањата, но додека телото се намалува, каматните плаќања се намалуваат, а износот на отплата на самиот орган се зголемува. Вкупното плаќање, како што е споменато погоре, останува непроменето.
Неопходно е да се пресмета колкав е износот на месечната исплата, што вклучува отплата на телото за заем и каматни исплати. За ова, Excel има оператор PMT.
PMT Таа припаѓа на група финансиски функции и нејзината задача е да ја пресмета месечната исплата на заемот на ануитетниот тип врз основа на износот на телото за заем, износот на кредитот и каматната стапка. Синтаксата за оваа функција е како што следува.
= PMT (стапка; nper; ps; bs; тип)
"Bet" - Аргументот за утврдување на каматната стапка на кредитни плаќања. Индикаторот е поставен за периодот. Нашиот период на исплата е еден месец. Затоа, годишната стапка од 12,5% треба да се подели на бројот на месеци во една година, односно 12.
"Кпер" - Аргументот кој го одредува бројот на периоди за целиот период на заемот. Во нашиот пример, периодот е еден месец, а периодот на заем е 3 години или 36 месеци. Така, бројот на периоди ќе биде рано 36.
"ПС" - аргументот што ја одредува сегашната вредност на заемот, односно големината на телото за заем во моментот на нејзиното издавање. Во нашиот случај, оваа бројка изнесува 900.000 рубли.
"БС" - аргумент што укажува на големината на телото за заем во моментот на негово целосно плаќање. Се разбира, овој индикатор ќе биде еднаков на нула. Овој аргумент е опционален. Ако го прескокнете, се претпоставува дека е еднакво на бројот "0".
"Тип" - исто така опционален аргумент. Тој известува кога ќе се изврши исплата: на почетокот на периодот (параметар - "1") или на крајот на периодот (параметар - "0"). Како што се сеќаваме, нашата исплата се врши на крајот на календарскиот месец, односно вредноста на овој аргумент ќе биде еднаква на "0". Но, со оглед на тоа што овој индикатор не е задолжителен, и по дифолт, ако не се користи, се претпоставува дека вредноста е "0", тогаш во наведениот пример воопшто не може да се користи.
- Значи, продолжуваме со пресметката. Изберете ја ќелијата на листот каде ќе се прикаже пресметаната вредност. Ние кликнуваме на копчето "Вметни ја функцијата".
- Почнува Функционален волшебник. Направете преминување во категоријата "Финансиски", изберете од листата името "ПЛТ" и кликнете на копчето "Добро".
- По ова, се активира прозорецот на аргументите на горенаведената функција.
Ставете го курсорот во полето "Bet"потоа кликнете на ќелијата на листот со вредноста на годишната каматна стапка. Како што можете да видите, неговите координати веднаш се прикажуваат во полето. Но, како што се сеќаваме, потребна е месечна стапка, и затоа го делиме резултатот со 12 (/12).
Во полето "Кпер" на ист начин, ние внесуваме координати на кредитни термини клетки. Во овој случај, ништо не треба да се подели.
Во полето "Ps" мора да ги наведете координатите на ќелијата што ја содржат вредноста на кредитното тело. Ние го правиме тоа. Ние исто така ставиме знак пред прикажаните координати. "-". Поентата е дека функцијата PMT по дифолт, го дава конечниот резултат со негативен знак, праведно со оглед на месечната исплата на заемот со загуба. Но, за јасност, потребна ни е табелата за податоци да биде позитивна. Затоа, ние ставивме знак "минус" пред една од аргументите на функцијата. Како што е познато, множење "минус" на "минус" на крајот дава плус.
Во полињата "Bs" и "Тип" Воопшто не внесуваме податоци. Ние кликнуваме на копчето "Добро".
- После тоа операторот пресметува и прикажува во претходно назначената ќелија резултат на вкупната месечна исплата - 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 месеци), со цел да инвестираме во горенаведениот износ на месечна исплата, треба да земеме заем кој не надминува 8.600.000 рубли, што е 40.000 помалку од првично планираното.
Ако сеуште имаме намера да земеме заем во износ од 900.000 рубли, тогаш износот на кредитот треба да биде 4 години (48 месеци). Само во овој случај, износот на месечната исплата нема да ја надмине утврдената граница од 29.000 рубли.
Така, искористувајќи ја оваа табеларна низа и анализирајќи ги добрите и лошите страни на секоја опција, заемопримачот може да донесе конкретна одлука за условите за кредитирање, избирајќи ја опцијата што најмногу одговара на неговите потреби.
Се разбира, lookup табелата може да се користи не само за да се пресметаат кредитни опции, туку и за решавање на многу други проблеми.
Лекција: условно форматирање во Excel
Во принцип, треба да се забележи дека табелата за пребарување е многу корисна и релативно едноставна алатка за одредување на резултатот од различни комбинации на променливи. Со примена на условно форматирање заедно со него, исто така, можете да ги визуелизирате добиените информации.
