Табела података у програму Мицрософт Екцел

Често је неопходно израчунати коначни резултат за различите комбинације улазних података. Стога, корисник ће моћи да процени све могуће опције за радње, одаберу оне чије резултате интеракције задовољавају и, коначно, изаберите оптималну опцију. У Екцелу, за извршавање овог задатка постоји посебан алат - "Таблица података" ( "Таблица супституције" ). Хајде да сазнамо како да је користимо за извођење горе наведених сценарија.

Прочитајте такође: Избор параметара у Екцелу

Употреба табеле података

Алатка "Дата Табле" је дизајнирана да израчунава резултат за различите варијације једне или две дефинисане варијабле. Након израчунавања, све могуће варијанте ће се појавити у облику табеле, која се зове матрица факторске анализе. "Таблица података" односи се на групу алата за анализу "Шта ако", која се налази на траци на картици "Подаци" у оквиру "Рад са подацима" . Пре Екцела 2007, овај алат је назван "Замена Табела" , која још тачније одражава његову суштину од тренутног имена.

Таблица супституције може се користити у многим случајевима. На пример, типична опција је када морате да израчунате износ месечне отплате кредита за различите варијације у кредитном периоду и износу кредита, или периоду кредитирања и каматне стопе. Такође, овај алат може се користити у анализи модела инвестиционих пројеката.

Али морате такође знати да прекомерна употреба овог алата може довести до кочења система, јер се подаци непрекидно израчунавају. Због тога се препоручује да у малим табеларним низовима, за решавање сличних задатака, не користите овај алат, већ користите копирање формулара помоћу маркера за пуњење.

Праведна примена "Табеле података" је само у великим распонима таблица, када копирање формулума може потрајати пуно времена, а током поступка повећава вероватноћу грешака. Али у овом случају препоручује се и онемогућити аутоматско поновно израчунавање формула у опсегу табеле замене, како би се избјегло непотребно оптерећење система.

Главна разлика између различитих употреба табеле података је број варијабли укључених у израчунавање: једна варијабла или двије.

1. метод: применити алат са једном променљивом

Одмах размислимо о опцији када се табела података користи са једном променљивом вриједношћу. Узмимо најквалитетнији пример позајмљивања.

Дакле, тренутно нам се нуди следећи кредитни услови:

• Трајање кредита - 3 године (36 мјесеци);
• Износ кредита износи 900.000 рубаља;
• Каматна стопа је 12,5% годишње.

Плаћања се јављају на крају периода плаћања (месец) у оквиру ануитета, односно у једнаким дионицама. Истовремено, на почетку читавог периода зајма, значајан дио плаћања су исплате камата, али како се тијело смањује, плаћање камата се смањује и износ поврата самог тијела повећава. Укупна исплата, као што је горе поменуто, остаје непромењена.

Неопходно је израчунати који ће износ месечне исплате, који укључује отплату тијела зајма и плаћања камата, бити. Да бисте то урадили, у програму Екцел постоји ПЛТ оператор.

ПЛТ припада групи финансијских функција и његов задатак је да израчуна месечну исплату ануитета на основу износа кредита, рока кредита и каматне стопе. Синтакса ове функције је представљена у овом облику

=ПЛТ(ставка;кпер;пс;бс;тип)

"Стопа" је аргумент који одређује каматну стопу плаћања кредита. Индикатор је постављен за период. Имамо период исплате од једног месеца. Стога, годишња стопа од 12,5% треба разврстати у броју месеци у години, односно 12.

"Кпер" - аргумент који одређује број периода за цео период кредита. У нашем примеру, период је један месец, а период кредитирања је 3 године или 36 месеци. Дакле, број периода ће бити рано 36.

"ПС" је аргумент који одређује садашњу вредност кредита, односно величину тијела зајма у тренутку њеног издавања. У нашем случају, ова цифра је 900.000 рубаља.

"БС" је аргумент који указује на износ тијела зајма у тренутку њеног пуног плаћања. Наравно, овај индикатор ће бити нула. Овај аргумент није потребан параметар. Ако је изоставите, претпоставља се да је једнак броју "0".

"Тип" је такође опциони аргумент. Он пријави када се врши уплата: на почетку периода (параметар - "1" ) или на крају периода (параметар - "0" ). Као што се сећамо, плаћамо на крају календарског месеца, односно вредност ове аргументације ће бити једнака "0" . Међутим, имајући у виду да овај индикатор није обавезан и подразумевано, ако га не користите, вриједност и тако значе "0" , онда се у наведеном примјеру генерално не може користити.

1. Дакле, покренемо рачун. Изаберите ћелију на листу, где ће се приказати израчуната вредност. Кликом на дугме "Убаци функцију" .
2. Стартује функционални чаробњак . Прелазимо на категорију "Финансијски" , одаберите име "ПЛТ" са листе и кликните на дугме "ОК" .
3. Након тога, активира се прозор аргумената горе наведене функције.

   Поставили смо курсор у поље "Бет" , а затим кликните на ћелију на листу са вриједношћу годишње каматне стопе. Као што видите, поље одмах приказује своје координате. Али, како се сећамо, потребна нам је месечна стопа, и стога подељујемо резултат за 12 ( / 12 ).

   На пољу "Кпер" на исти начин улазимо у координате ћелија термина кредита. У овом случају, не морате подијелити.

   У пољу "Пс" морате навести координате ћелије које садрже износ тијела зајма. Ми то радимо. Такође поставите знак "-" прије приказаних координата. Чињеница је да функција ПЛТ-а по дефаулту даје коначни резултат са негативним знаком, с правом узимајући у обзир месечно плаћање кредита као губитак. Али ради јасноће у употреби табеле података, овај број мора бити позитиван. Зато смо ставили минус знак прије једног од аргумената функције. Као што знате, помножење "минус" према "минус" на крају даје "плус" .

   У пољу "Бц" и "Тип" нећемо ништа да правимо. Кликните на дугме "ОК" .

4. Након тога, оператер израчунава и даје резултате укупне месечне исплате - 30108,26 рубаља у претходно одређену ћелију. Али проблем је што је зајмопримац у могућности да плати максимално 29.000 рубаља месечно, односно да нађе банку која нуди услове с нижим каматним стопама, или да смањи кредитно тијело, или да повећа период зајма. Табела замена ће нам помоћи да израчунамо различите опције за акцију.
5. Прво, користимо табеле замена са једном променљивом. Да видимо како се вредност обавезног месечног плаћања разликује са различитим варијацијама годишње стопе, почевши од 9,5% годишње и завршава на 12,5% годишње у корацима од 0,5% . Сви остали услови остају непромењени. Цртамо табеларни опсег чије су имена колона одговарају различитим варијацијама каматне стопе. Истовремено, линија "Месечна плаћања" остављена је као што јесте. Прва ћелија мора садржати формулу коју смо раније израчунали. За више информација, можете додати линије "Укупан износ кредита" и "Укупна каматна сума" . Колона у којој се налази прорачун се врши без наслова.
6. Затим, израчунамо укупни износ кредита под тренутним условима. Да бисте то урадили, изаберите прву ћелију линије "Укупни износ кредита" и помножите садржај ћелија "Месечна плаћања" и "Кредитни период" . Након тога, кликните на тастер Ентер .
7. За израчунавање укупног износа камате по тренутним условима, на исти начин одузимамо износ кредита од укупног износа кредита. Да бисте приказали резултат на екрану, кликните на дугме Ентер . Тако добијамо износ који преплаћујемо приликом отплате кредита.
8. Сада је вријеме да примените алатку Дата Табле . Изаберите читав низ таблица, осим имена редова. После тога идите на картицу "Подаци" . Кликом на дугме на траци "Анализа шта ако" , која се налази у групи алатки "Рад са подацима" (у програму Екцел 2016, група "Прогноза" ), кликните на дугме. Затим се отвори мали мени. У њој одаберите ставку "Табела података ..." .
9. Отвара се мали прозор, који се зове "Табела података" . Као што видите, има два поља. Пошто радимо са једном променљивом, потребан је само један од њих. Пошто променимо променљиву за ступце, користићемо поље "Замијенити вриједности по ступцима у" . Поставите курсор тамо, а затим кликните на ћелију у изворном скупу података који садржи тренутну процентуалну вредност. Након приказа координата ћелија на пољу, кликните на дугме "ОК" .
10. Алат израчунава и попуњава цијели распон таблица с вриједностима које одговарају различитим опцијама за каматну стопу. Ако поставите курсор у било који елемент датог подручја таблице, можете видети да формула формуле не приказује уобичајену формулу за израчунавање плаћања, већ посебну формулу за нерастворљив низ. То јест, сада не можете промијенити вриједности у појединачним ћелијама. Резултате рачунања можете обрисати само заједно, а не одвојено.

Осим тога, можете видети да је вредност месечног плаћања од 12,5% годишње, добијене као резултат примене табеле пермутација, одговара вредности за исти износ камате коју смо добили применом функције ПЛТ-а . Ово још једном доказује тачност израчунавања.

Анализирајући овај низ таблица, требало би рећи да, као што видимо, само по стопи од 9,5% годишње је прихватљиво за нас ниво месечног плаћања (мање од 29.000 рубаља).

Лекција: Израчунавање ануитета у Екцелу

Метод 2: користите алат са две варијабле

Наравно, врло је тешко, уопште, да пронађу банке које издају кредите на годишњем нивоу од 9,5%. Дакле, да видимо које опције постоје за улагање у прихватљивом нивоу месечног плаћања за различите комбинације других варијабли: величина тијела кредита и рок трајања кредита. Истовремено, задржавамо каматну стопу непромењену (12,5%). У решавању овог задатка, алатка "Дата Табле" ће нам помоћи да користимо две варијабле.

1. Цртање новог низа табле. Сада ће се у колонама назначити кредитни период (од 2 до 6 година у месецима у корацима од једне године), а у редовима - износ кредита (од 850000 до 950000 рубаља са кораком од 10.000 рубаља). Обавезно стање је да ћелија у којој се налази формула за израчунавање (у нашем случају, ПЛТ ) налази се на граници имена редова и колона. Без овог стања, алат не ради када користите две варијабле.
2. Затим изаберите читав распон стола, укључујући имена колона, редова и ћелије са ПЛТ формулом. Идите на картицу "Подаци" . Као и претходни пут, кликните на дугме "Анализирај шта ако" , у групи алатки "Рад са подацима" . У отвореној листи изаберите ставку "Табела података ..." .
3. Покренут је прозор алата "Дата Табле" . У овом случају, потребна су обе области. У пољу "Замењују вриједности по колонама у", индицирамо координате ћелије које садрже кредитни период у примарним подацима. У пољу "Замена вредности по редовима у" упишемо адресу ћелије почетних параметара који садрже вредност тијела кредита. Након што унесете све податке. Кликните на дугме "ОК" .
4. Програм израчунава и попуњава распон таблица са подацима. На раскрсници редова и ступаца, сада можете видети тачно шта ће бити месечно плаћање, са одговарајућом вредношћу годишње камате и наведеним роком кредита.
5. Као што видите, има доста вриједности. Да би решили друге проблеме, можда их има још више. Стога, како би учинак резултата постао видљивији и одмах одредити које вриједности не задовољавају задате услове, можете користити алате за визуелизацију. У нашем случају ово ће бити условно форматирање. Изаберите све вредности у опсегу таблица, искључујући заглавља редова и колона.
6. Прелазак на картицу "Почетна" и клик на икону "Условно обликовање" . Налази се у кутији за алат "Стилес" на траци. У падајућем менију изаберите "Правила за избор ћелија" . На додатној листи кликните на ставку "Мање ..." .
7. Након тога се отвара прозор условног обликовања. У левом пољу одредите вредност, мање од којих ће ћелија бити додељено. Као што се сећамо, задовољни смо условом под којим ће месечна уплата по зајму бити мања од 29.000 рубаља. Уносимо овај број. У десном пољу можете одабрати боју означавања, иако га можете оставити подразумевано. Након што унесете сва потребна подешавања, кликните на дугме "ОК" .
8. Након тога, све ћелије, вредности у којима одговара горе наведеном стању, биће означене бојом.

Анализирајући низ таблица, можете извући неке закључке. Као што видимо, уз постојећи кредитни рок (36 месеци), да би се инвестирало у горенаведени износ месечног уплата, потребно је да узмемо кредит који не прелази 860000,00 рубаља, односно 40.000 мање него што је првобитно планирано.

Ако још увек намеравамо да извучемо кредит од 900.000 рубаља, рок кредитирања треба да буде 4 године (48 месеци). Само у овом случају месечна уплата неће бити већа од утврђеног лимита од 29.000 рубаља.

Стога, коришћењем овог табеларног поља и анализом предности и слабости сваке опције, зајмопримац може да донесе конкретну одлуку о условима зајма, одабиром најповољније опције из свих могућих опција.

Наравно, табела за преглед може се користити не само за израчунавање опција за кредит, већ и за решавање многих других проблема.

Лекција: Условно обликовање у Екцелу

Уопштено говорећи, потребно је напоменути да је табела за преглед веома корисна и релативно једноставна алатка за одређивање резултата за различите комбинације варијабли. Уз истовремено примену условног обликовања, можете видјети и примљене информације.

• ← Отварање табела ОДС у програму Мицрософт Екцел
• СКЛ упити у Мицрософт Екцелу →