Даволі часта патрабуецца разлічыць выніковы вынік для розных камбінацый ўводных дадзеных. Такім чынам карыстальнік зможа ацаніць усе магчымыя варыянты дзеянняў, адабраць тыя, вынік ўзаемадзеяння якіх яго задавальняе, і, нарэшце, выбраць самы аптымальны варыянт. У Excel для выканання гэтай задачы існуе спецыяльны інструмент - "Табліца дадзеных" ("Табліца падстаноўкі"). Давайце даведаемся, як ім карыстацца для выканання названых вышэй сцэнарыяў.
Чытайце таксама: Падбор параметру ў Excel
Выкарыстанне табліцы дадзеных
інструмент "Табліца дадзеных" прызначаны для таго, каб разлічваць вынік пры розных варыяцыях адной або двух пэўных зменных. Пасля разліку ўсе магчымыя варыянты паўстануць у выглядзе табліцы, якую называюць матрыцай факторного аналізу. "Табліца дадзеных" адносіцца да групы інструментаў "Аналіз" што калі "", Якая размешчана на стужцы ва ўкладцы "Дадзеныя" ў блоку "Праца з дадзенымі". Да версіі Excel 2007 гэты інструмент насіў найменне "Табліца падстаноўкі", Што нават больш дакладна адлюстроўвала яго сутнасць, чым цяперашняя назва.
Табліцу падстаноўкі можна выкарыстоўваць ў многіх выпадках. Напрыклад, тыповы варыянт, калі трэба разлічыць суму штомесячнага плацяжу па крэдыце пры розных варыяцыях перыяду крэдытавання і сумы пазыкі, альбо перыяду крэдытавання і працэнтнай стаўкі. Таксама гэты інструмент можна выкарыстоўваць пры аналізе мадэляў інвестыцыйных праектаў.
Але таксама варта ведаць, што празмернае прымяненне дадзенага інструмента можа прывесці да тармажэння сістэмы, так як пералік дадзеных вырабляецца пастаянна. Таму рэкамендуецца ў невялікіх таблічных масівах для вырашэння аналагічных задач не выкарыстоўваць гэты інструмент, а ўжываць капіяванне формул з дапамогай маркера запаўнення.
апраўданым прымяненне "Табліцы дадзеных" з'яўляецца толькі ў вялікіх таблічных дыяпазонах, калі капіяванне формул можа адняць шмат часу, а ў час самой працэдуры павялічваецца верагоднасць дапушчэння памылак. Але і ў гэтым выпадку рэкамендуецца ў дыяпазоне табліцы падстаноўкі адключыць аўтаматычны пералік формул, каб пазбегнуць залішняй нагрузкі на сістэму.
Галоўнае адрозненне паміж рознымі варыянтамі прымянення табліцы дадзеных складаецца ў колькасці зменных, якія прымаюць удзел у вылічэнні: адна зменная ці дзве.
Спосаб 1: прымяненне інструмента з адной зменнай
Адразу давайце разгледзім варыянт, калі табліца дадзеных выкарыстоўваецца з адным пераменным значэннем. Возьмем найбольш тыповы прыклад з крэдытаваннем.
Такім чынам, у цяперашні час нам прапануюцца наступныя ўмовы крэдытавання:
- Тэрмін крэдытавання - 3 гады (36 месяцаў);
- Сума пазыкі - 900000 рублёў;
- Працэнтная стаўка - 12,5% гадавых.
Выплаты адбываюцца ў канцы плацёжнага перыяду (месяца) па аннуитетной схеме, гэта значыць, роўнымі долямі. Пры гэтым, спачатку ўсяго тэрміну крэдытавання значную частку выплат складаюць працэнтныя плацяжы, але па меры скарачэння цела працэнтныя плацяжы памяншаюцца, а павялічваецца памер пагашэння самога цела. Агульная ж выплата, як ужо было сказана вышэй, застаецца без зменаў.
Трэба разлічыць, якая будзе сума штомесячнага плацяжу, які ўключае ў сябе пагашэнне цела крэдыту і выплат па працэнтах. Для гэтага ў Эксэля маецца аператар ПЛТ.
ПЛТ адносіцца да групы фінансавых функцый і яго задачай з'яўляецца вылічэнне штомесячнага крэдытнага плацяжу аннуитетного тыпу на падставе сумы цела крэдыту, тэрміну крэдытавання і працэнтнай стаўкі. Сінтаксіс гэтай функцыі прадстаўлены ў такім выглядзе
= ПЛТ (стаўка; кпер; пс; БС; тып)
"Стаўка" - аргумент, які вызначае працэнтную стаўку крэдытных выплат. Паказчык выстаўляецца за перыяд. У нас перыяд выплат роўны месяцу. Таму гадавую стаўку ў 12,5% варта разбіць на колькасць месяцаў у годзе, гэта значыць, 12.
"Кпер" - аргумент, які вызначае колькасць перыядаў за ўвесь тэрмін прадастаўлення крэдыту. У нашым прыкладзе перыяд роўны аднаму месяцу, а тэрмін крэдытавання складае 3 гады або 36 месяцаў. Такім чынам, колькасць перыядаў будзе рана 36.
"ПС" - аргумент, які вызначае прыведзеную кошт крэдыту, гэта значыць, гэта памер цела крэдыту на момант яго выдачы. У нашым выпадку гэты паказчык роўны 900000 рублёў.
"БС" - аргумент, які паказвае на велічыню цела крэдыту на момант яго поўнай выплаты. Натуральна, што гэты паказчык будзе роўны нулю. Гэты аргумент не з'яўляецца абавязковым параметрам. Калі яго прапусціць, то маецца на ўвазе, што ён роўны ліку "0".
"Тып" - таксама неабавязковы аргумент. Ён паведамляе пра тое, калі менавіта будзе праводзіцца плацёж: у пачатку перыяду (параметр - "1") Або ў канцы перыяду (параметр - "0"). Як мы памятаем, у нас плацёж праводзіцца ў канцы каляндарнага месяца, гэта значыць, велічыня гэтага аргументу будзе роўная "0". Але, улічваючы тое, што гэты паказчык не з'яўляецца абавязковым, і па змаўчанні, калі яго не выкарыстоўваць, значэнне і так маецца на ўвазе роўным "0", То ў паказаным прыкладзе яго наогул можна не ўжываць.
- Дык вось, пачынаем да разліку. Вылучаем вочка на лісце, куды будзе выводзіцца разліковае значэнне. Клацает па кнопцы "Уставіць функцыю".
- запускаецца майстар функцый. Вырабляем пераход у катэгорыю "Фінансавыя", Выбіраем з пераліку найменне "ПЛТ" і клацает па кнопцы "OK".
- Услед за гэтым адбываецца актывацыя акенца аргументаў вышэйпаказанай функцыі.
Ставім курсор у полі "Стаўка", Пасля чаго клікаем па вочку на лісце са значэннем гадавы працэнтнай стаўкі. Як бачым, у поле тут жа адлюстроўваюцца яе каардынаты. Але, як мы памятаем, нам патрэбна месячная стаўка, а таму вырабляем дзяленне атрыманага выніку на 12 (/12).
У полі "Кпер" такім жа чынам ўносім каардынаты вочак тэрміну крэдыту. У гэтым выпадку дзяліць нічога не трэба.
У полі "Пс" трэба паказаць каардынаты ячэйкі, якая змяшчае велічыню цела крэдыту. Выконваем гэта. Таксама ставім перад адлюстраваў каардынатамі знак "-". Справа ў тым, што функцыя ПЛТ па змаўчанні выдае выніковы вынік менавіта з адмоўным знакам, справядліва лічачы штомесячны крэдытны плацёж стратай. Але нам для нагляднасці прымянення табліцы дадзеных трэба, каб дадзены лік было станоўчым. Таму мы і ставім знак "Мінус" перад адным з аргументаў функцыі. Як вядома, множанне "Мінус" на "Мінус" у выніку дае "Плюс".
У палі "БС" і "Тып" дадзеныя наогул не ўносім. Клацает па кнопцы "OK".
- Пасля гэтага аператар вырабляе падлік і выводзіць у загадзя пазначаную вочка вынік агульнага штомесячнага плацяжу - 30108,26 рублёў. Але праблема складаецца ў тым, што пазычальнік ў стане плаціць максімум 29000 рублёў у месяц, гэта значыць, яму варта альбо знайсці банк, які прапануе ўмовы з больш нізкай працэнтнай стаўкай, альбо зменшыць цела пазыкі, альбо павялічыць тэрмін крэдытавання. Пралічыць розныя варыянты дзеянняў нам дапаможа табліца падстановак.
- Для пачатку выкарыстоўваем табліцу падстановак з адной зменнай. Паглядзім, як будзе змяняцца велічыня абавязковага месячнага плацяжу пры розных варыяцыях гадавы стаўкі, пачынаючы ад 9,5% гадавых і заканчваючы 12,5% гадавых з крокам 0,5%. Усе астатнія ўмовы пакідаем нязменнымі. Чэрці таблічны дыяпазон, найменні калонак якога будуць адпавядаць розным варыяцыям працэнтнай стаўкі. Пры гэтым радок "Штомесячныя выплаты" пакідаем так, як ёсць. У першай яе вочку павінна ўтрымлівацца формула, якую мы разлічылі раней. Для большай інфарматыўнасці можна дадаць радкі "Агульная сума крэдыту" і "Агульная сума працэнтаў". Слупок, у якім знаходзіцца разлік, робім без загалоўка.
- Далей разлічым агульную суму пазыкі пры бягучых умовах. Для гэтага вылучаем першую вочка радкі "Агульная сума крэдыту" і памнажаем змесціва вочак "Штомесячны плацёж" і "Тэрмін крэдыту". Пасля гэтага пстрыкаем па клавішы Enter.
- Для разліку агульнай сумы працэнтаў пры бягучых умовах аналагічным чынам адымаем ад агульнай сумы пазыкі велічыню цела крэдыту. Для вываду выніку на экран пстрыкаем па кнопцы Enter. Такім чынам мы атрымліваем суму, якую пераплачваем пры вяртанні пазыкі.
- Цяпер настаў час прымяніць інструмент "Табліца дадзеных". Вылучаем ўвесь таблічны масіў, акрамя найменняў радкоў. Пасля гэтага пераходзім ва ўкладку "Дадзеныя". Пстрыкаем па кнопцы на стужцы "Аналіз" што калі "", Якая размешчана ў групе інструментаў "Праца з дадзенымі" (У Excel 2016 група інструментаў "Прагноз"). Затым адкрываецца невялікае меню. У ім выбіраем пазіцыю "Табліца дадзеных ...".
- Адкрываецца невялікае акенца, якое так і называецца "Табліца дадзеных". Як бачым, у яго маецца два поля. Так як мы працуем з адной зменнай, то нам спатрэбіцца толькі адно з іх. Так як у нас змены зменнай адбываецца па слупках, то мы будзем выкарыстоўваць поле "Падставіць значэння па слупках ў". Усталёўваем туды курсор, а затым клікаем па вочку ў зыходным наборы дадзеных, якая ўтрымлівае бягучую велічыню адсоткаў. Пасля таго, як каардынаты ячэйкі адлюстраваліся ў поле, ціснем на кнопку "OK".
- Інструмент вырабляе разлік і запаўняе ўвесь таблічны дыяпазон значэннямі, якія адпавядаюць розным варыянтам працэнтнай стаўкі. Калі ўсталяваць курсор у любы элемент дадзенай таблічнай вобласці, то можна ўбачыць, што ў радку формул адлюстроўваецца не звычайная формула разліку плацяжу, а спецыяльная формула непарыўнага масіва. Гэта значыць, змяняць значэння ў асобных вочках цяпер нельга. Выдаляць вынікі разліку можна толькі ўсе разам, а не паасобку.
Акрамя таго, можна заўважыць, што велічыня штомесячнага плацяжу пры 12.5% гадавых, атрыманая ў выніку прымянення табліцы падстановак, адпавядае велічыні пры тым жа памеры працэнтаў, якую мы атрымалі шляхам прымянення функцыі ПЛТ. Гэта лішні раз даказвае правільнасць разліку.
Прааналізаваўшы дадзены таблічны масіў, варта сказаць, што, як бачым, толькі пры стаўцы 9,5% гадавых атрымліваецца прымальны для нас узровень штомесячнага плацяжу (менш за 29000 рублёў).
Урок: Разлік аннуитетного плацяжу ў Эксэля
Спосаб 2: выкарыстанне інструмента з дзвюма зменнымі
Вядома, адшукаць у цяперашні час банкі, якія выдаюць крэдыт пад 9,5% гадавых, вельмі складана, калі наогул рэальна. Таму паглядзім, якія варыянты існуюць ўкласціся ў прымальны ўзровень штомесячнага плацяжу пры розных камбінацыях іншых зменных: велічыні цела пазыкі і тэрміну крэдытавання. Пры гэтым працэнтную стаўку пакінем нязменнай (12,5%). У рашэнні дадзенай задачы нам дапаможа інструмент "Табліца дадзеных" з выкарыстаннем двух зменных.
- Чэрці новы таблічны масіў. Цяпер у назвах слупкоў будзе паказвацца тэрмін крэдытавання (ад 2 да 6 гадоў у месяцах з крокам у адзін год), а ў радках - велічыня цела крэдыту (ад 850000 да 950000 рублёў з крокам 10000 рублёў). Пры гэтым абавязковай умовай з'яўляецца тое, каб вочка, у якой знаходзіцца формула разліку (у нашым выпадку ПЛТ), Размяшчалася на мяжы найменняў радкоў і слупкоў. Без выканання гэтай умовы інструмент пры выкарыстанні двух зменных працаваць не будзе.
- Затым вылучаем ўвесь атрыманы таблічны дыяпазон, уключаючы найменне слупкоў, радкоў і вочка з формулай ПЛТ. Пераходзім ва ўкладку "Дадзеныя". Як і ў папярэдні раз, пстрыкаем па кнопцы "Аналіз" што калі "", У групе інструментаў "Праца з дадзенымі". У якое адкрылася спісе выбіраем пункт "Табліца дадзеных ...".
- Запускаецца акно інструмента "Табліца дадзеных". У дадзеным выпадку нам спатрэбяцца абодва поля. У полі "Падстаўляць значэння па слупках ў" паказваем каардынаты ячэйкі, якая змяшчае тэрмін крэдыту ў першасных дадзеных. У полі "Падстаўляць значэння па радках ў" паказваем адрас ячэйкі зыходных параметраў, якая змяшчае велічыню цела крэдыту. Пасля таго, як усе дадзеныя ўведзеныя. Клацает па кнопцы "OK".
- Праграма выконвае разлік і запаўняе таблічны дыяпазон дадзенымі. На скрыжаванні радкоў і слупкоў зараз можна назіраць, якім менавіта будзе штомесячны плацёж, пры адпаведнай велічыні гадавых працэнтаў і паказаным тэрміне крэдытавання.
- Як бачым, значэнняў даволі шмат. Для вырашэння іншых задач іх можа быць яшчэ больш. Таму, каб зрабіць выдачу вынікаў больш нагляднай і адразу вызначыць, якія значэння не задавальняюць зададзеным умове, можна выкарыстоўваць інструменты візуалізацыі. У нашым выпадку гэта будзе ўмоўнае фарматаванне. Вылучаем усе значэння таблічнага дыяпазону, выключаючы загалоўкі радкоў і слупкоў.
- Перамяшчаемся ва ўкладку "Галоўная" і клацает па значку "Умоўнае фарматаванне". Ён размешчаны ў блоку інструментаў "Стылі" на стужцы. У раскрыць меню выбіраем пункт "Правілы выдзялення вочак". У дадатковым спісе клікаем па пазіцыі "Менш ...".
- Услед за гэтым адкрываецца акно налады ўмоўнага фарматавання. У левым полі паказваем велічыню, менш якой вочкі будуць вылучаныя. Як памятаем, нас задавальняе ўмова, пры якім штомесячны плацёж па крэдыце будзе складаць менш 29000 рублёў. Ўпісваем дадзены лік. У правым поле існуе магчымасць выбару колеру вылучэнні, хоць можна пакінуць яго і па змаўчанні. Пасля таго, як усе патрабаваныя налады ўведзеныя, клацает па кнопцы "OK".
- Пасля гэтага ўсе вочкі, значэння ў якіх адпавядаюць вышэйапісаным умове, будуць вылучаны колерам.
Прааналізаваўшы таблічны масіў, можна зрабіць некаторыя высновы. Як бачым, пры існуючым тэрміне крэдытавання (36 месяцаў), каб ўкласціся ў вышэй пазначаную суму штомесячнага плацяжу, нам трэба ўзяць пазыку якi не перавышае 860000,00 рублёў, гэта значыць, на 40000 менш першапачаткова запланаванага.
Калі ж мы ўсё ж такі маюць намер браць крэдыт памерам 900000 рублёў, то тэрмін крэдытавання павінен складаць 4 гады (48 месяцаў). Толькі ў такім выпадку памер штомесячнага плацяжу не перавысіць усталяваную мяжу ў 29000 рублёў.
Такім чынам, скарыстаўшыся дадзеных таблічнага масівам і прааналізаваўшы "за" і "супраць" кожнага варыянту, пазычальнік можа прыняць канкрэтнае рашэнне аб умовах крэдытавання, выбраўшы найбольш адказвае яго пажаданням варыянт з усіх магчымых.
Вядома, табліцу падстановак можна выкарыстоўваць не толькі для разліку крэдытных варыянтаў, але і для вырашэння мноства іншых задач.
Урок: Умоўнае фарматаванне ў Эксэля
Увогуле, трэба адзначыць, што табліца падстановак з'яўляецца вельмі карысным і параўнальна простым інструментам для вызначэння выніку пры розных камбінацыях зменных. Ужыўшы адначасова з ім ўмоўнае фарматаванне, акрамя таго, можна візуалізаваць атрыманую інфармацыю.
