Көбүнчө, ар кандай маалыматтарды айкалыштыруу үчүн акыркы натыйжаны эсептеп чыгарышыңыз керек. Ошентип, колдонуучу иш-аракеттердин бардык мүмкүн болгон варианттарын баалоого, өз ара аракеттенүүнүн натыйжалары ага канааттанган адамдарды тандап, акыры, эң оптималдуу вариантты тандап алат. Бул тапшырманы аткаруу үчүн Excelде атайын шайман бар - "Маалыматтар таблицасы" (Таблица). Жогорудагы сценарийлерди аягына чыгаруу үчүн аны кантип колдонууну билип алалы.
Ошондой эле окуңуз: Excelде параметрлерди тандоо
Маалыматтар таблицасын колдонуу
курал "Маалыматтар таблицасы" Бир же эки аныкталган өзгөрмөлөрдүн ар кандай вариациялары үчүн натыйжаны эсептөө үчүн арналган. Эсептеп бүткөндөн кийин, бардык мүмкүн болгон варианттар фактордук анализдин матрицасы деп аталган таблица түрүндө пайда болот. "Маалыматтар таблицасы" куралдар тобуна кирет "Анализ болсочу?"кыстырмадагы лентага жайгаштырылган "Маалымат" блокто "Маалыматтар менен иштөө". Excel 2007гө чейин бул шайман деп аталган Таблицаазыркы атка караганда анын маңызын дагы даана чагылдырган.
Издөө столу көптөгөн учурларда колдонулушу мүмкүн. Мисалы, типтүү вариант - бул насыянын мезгилине жана насыянын суммасына же насыянын мөөнөтүнө жана пайыздык чени боюнча ар айлык насыянын суммасын эсептешиңиз керек. Ошондой эле, бул куралды инвестициялык долбоорлордун моделдерин талдоодо колдонсо болот.
Бирок сиз бул куралды ашыкча колдонуу тутумдун тежелишине алып келиши мүмкүн экендигин билишиңиз керек, анткени маалыматтар үзгүлтүксүз кайра саналып турат. Андыктан ушул сыяктуу көйгөйлөрдү чечүү үчүн кичинекей таблицалык массивдерде ушул куралды колдонбой, толтуруу маркеринин жардамы менен формуланы көчүрүү сунушталат.
Негизделген арыз "Маалыматтар таблицалары" формулаларды көчүрүү бир топ убакытты талап кылат жана процедуранын жүрүшүндө ката кетирүү ыктымалдыгы жогорулайт. Бирок бул учурда, системага ашыкча жүктөрдү түшүрбөө үчүн, алмаштыруу столунун диапазонунда формулаларды автоматтык түрдө кайра эсептөөнү токтотуу сунушталат.
Маалыматтар таблицасын ар кандай колдонуудагы негизги айырма эсептөөдө катышкан өзгөрмөлөрдүн саны: бир өзгөрүлмө же эки.
1-ыкма: бир өзгөрмөлүү куралды колдонуңуз
Маалыматтар таблицасы бир өзгөрмө мааниси менен колдонулганда, ошол замат вариантты карап көрөлү. Насыялоонун эң типтүү мисалын ал.
Ошентип, учурда насыялоонун төмөнкү шарттары сунушталат:
- Насыянын мөөнөтү - 3 жыл (36 ай);
- Насыянын суммасы - 900,000 рубль;
- Пайыздык чен - жылдык 12,5%.
Төлөмдөр аннуитеттик схемага ылайык төлөө мезгилинин (айдын) аягында, б.а. тең үлүштөрдө жүргүзүлөт. Ошол эле учурда, насыянын бүткүл мөөнөтүнүн башталышында, төлөмдөрдүн олуттуу бөлүгү пайыздык төлөмдөр болуп саналат, бирок орган кыскарган сайын, пайыздык төлөмдөр азайып, ал эми кайтарып берүү суммасы көбөйөт. Жалпы төлөө, жогоруда айтылгандай, өзгөрүүсүз калат.
Насыянын ордун толтурууну жана пайыздарды төлөөнү кошо алганда, ай сайын төлөнө турган сумманын суммасын эсептөө керек. Бул үчүн, Excelдин оператору бар PMT.
PMT каржылык функциялар тобуна кирет жана анын милдети насыя органынын суммасына, насыянын мөөнөтүнө жана пайыздык ставкасына жараша ай сайын аннуитеттик насыя түрүн эсептөө болуп саналат. Бул функциянын синтаксиси төмөнкүдөй келтирилген
= PLT (чен; nper; ps; bs; тиби)
"Bet" - кредиттик төлөмдөрдүн пайыздык чендерин аныктоочу жүйө. Көрсөткүч мезгилге белгиленген. Төлөө мөөнөтү бир айга барабар. Демек, жылдык 12,5% чен жылдык айлардын санына, башкача айтканда, 12ге бөлүнүшү керек.
"NPER" - насыянын бүткүл мөөнөткө чейинки мөөнөттөрүнүн санын аныктоочу аргумент. Биздин мисалда, мөөнөт бир ай, насыянын мөөнөтү - 3 жыл же 36 ай. Ошентип, мезгилдердин саны 36 эрте болот.
"PS" - насыянын учурдагы наркын аныктоочу жүйө, б.а. аны берүү учурундагы насыянын органы. Биздин учурда, бул көрсөткүч 900,000 рублди түзөт.
"BS" - толук төлөө учурунда насыя органынын өлчөмүн көрсөткөн аргумент. Албетте, бул көрсөткүч нөлгө барабар болот. Бул аргумент милдеттүү эмес. Эгер сиз аны өткөрүп жиберсеңиз, ал "0" санына барабар деп божомолдонот.
"Түрү" - кошумча аргумент. Ал төлөм качан төлөнө тургандыгын жарыялайт: мезгилдин башында (параметр - "1") же мезгилдин аягында (параметр - "0"). Эсиңизде болсун, биздин төлөм календардык айдын аягында жүргүзүлөт, башкача айтканда, бул аргументтин мааниси барабар болот "0". Бирок, бул көрсөткүч милдеттүү эмес экендигин жана демейки мааниде колдонулбаса, мааниси барабар деп божомолдонот "0"анда көрсөтүлгөн мисалда аны таптакыр жокко чыгарса болот.
- Ошентип, эсептөөнү улантабыз. Баракта эсептелген маани көрсөтүлө турган уячаны тандаңыз. Баскычты чыкылдатыңыз "Кыстыруу функциясы".
- Башталат Мүмкүнчүлүк чебери. Биз категорияга өтөбүз "Financial", тизмеден ат тандаңыз "PMT" жана баскычты чыкылдатыңыз "OK".
- Андан кийин, жогорудагы функциянын аргументтер терезеси жандырылды.
Курсорду талаага кой "Bet", андан кийин жыл сайын пайыздык чендин мааниси бар баракчаны басабыз. Көрүнүп тургандай, анын координаттары ошол замат талаага көрүнүп турат. Бирок, биз эсибизде болгондой, бизге айлык ставка керек, ошондуктан натыйжаны 12ге бөлөбүз (/12).
Талаада "NPER" Ошол сыяктуу эле, насыянын мөөнөтүнүн ячейкаларынын координаттарын киргизебиз. Бул учурда эч нерсе бөлүшүүнүн кажети жок.
Талаада "Заб" насыя органынын наркын камтыган уячанын координаттарын көрсөтүшүңүз керек. Биз муну жасайбыз. Көрсөтүлгөн координаттардын алдына белги койдук "-". Чынында, функция PMT демейки шартта, ал насыянын ай сайын төлөнгөн жоготуусун эске алып, терс белгиси менен акыркы жыйынтыкты берет. Бирок маалымат таблицасын колдонуунун тактыгы үчүн оң сандын болушу керек. Ошондуктан биз белги койдук "Минус" функция аргументтеринин бирине чейин. Көбөйтүү белгилүү "Минус" боюнча "Минус" акыры берет "Plus".
Талааларга киришти "BS" жана "Түрү" маалыматтар такыр киргизилген эмес. Баскычты чыкылдатыңыз "OK".
- Андан кийин, оператор алдын-ала дайындалган уячадагы айлык төлөмдүн жыйынтыгын эсептейт жана көрсөтөт - 30108,26 рубл. Бирок маселе, карыз алуучу айына эң көп дегенде 29000 рубль төлөй алат, башкача айтканда, ал төмөн пайыздык ставка менен банк сунуштаган шарттарды табышы керек, же насыя берүүчү органды кыскартышы керек, же насыянын мөөнөтүн көбөйтүшү керек. Издөө таблицасы ар кандай варианттарды билүүгө жардам берет.
- Биринчиден, издөө столун бир өзгөрмө менен колдонуңуз. Келгиле, ай сайын төлөнө турган милдеттүү төлөмдүн суммасы ар кандай өзгөрүүдөн баштап кандайча өзгөрөрүн карап көрөлү 9,5% жылдык жана аяктоочу 12,5% жылдык өсүш менен 0,5%. Бардык калган шарттар өзгөрүүсүз калтырылган. Биз үстөлдөрдүн аталыштары пайыздык чендердин ар кандай вариацияларына туура келген столдорду тартам. Бул сызык менен "Бир айлык төлөмдөр" болгондой калтырыңыз. Анын биринчи клеткасында биз мурда эсептеген формула камтылышы керек. Көбүрөөк маалымат алуу үчүн сиз саптарды кошо аласыз "Насыянын жалпы суммасы" жана "Жалпы пайыздар". Эсептөө жайгашкан тилкенин аталышы жок жасалат.
- Андан кийин, учурдагы шарттарда насыянын жалпы суммасын эсептейбиз. Бул үчүн, катардын биринчи уясын тандаңыз "Насыянын жалпы суммасы" жана клеткалардын мазмунун көбөйт "Ай сайын төлөө" жана "Насыянын мөөнөтү". Андан кийин, баскычты чыкылдатыңыз кирүү.
- Учурдагы шарттарда пайыздардын жалпы суммасын эсептөө үчүн, биз да насыянын суммасынан насыянын ордун толтуруп алабыз. Экранда натыйжаны көрсөтүү үчүн, баскычты чыкылдатыңыз кирүү. Ошентип, насыяны төлөп жатканда ашыкча төлөп берген сумманы алабыз.
- Эми бул куралды колдонууга убакыт келди "Маалыматтар таблицасы". Жол аттарынан башка бардык таблицалар массивин тандайбыз. Андан кийин, өтмөккө өтүңүз "Маалымат". Тасмадагы баскычты чыкылдатыңыз "Анализ болсочу?"курал тобунда жайгашкан "Маалыматтар менен иштөө" (Excel 2016 куралдар тобу "Болжол"). Андан кийин чакан меню ачылат. Анда биз позицияны тандайбыз "Маалыматтар таблицасы ...".
- Кичинекей терезе ачылат, ал деп аталат "Маалыматтар таблицасы". Көрүнүп тургандай, анын эки талаасы бар. Бир өзгөрмө менен иштегендиктен, алардын бирөөсү гана керек. Өзгөрмө тилкесин тилке менен алмаштыргандыктан, биз талааны колдонобуз Тилке маанилерин алмаштырыңыз. Ал жерде курсорду орнотуп, учурдагы пайызы камтылган баштапкы маалымат базасындагы уячага чыкылдатыңыз. Талаада уяча координаттары көрсөтүлгөндөн кийин, баскычты чыкылдатыңыз "OK".
- Курал бардык таблицалык диапазонду эсептейт жана пайыздык чендин ар кандай варианттарына дал келген маанилер менен толтурат. Эгерде сиз курсорду ушул таблицанын аймагынын каалаган элементине жайгаштырсаңыз, анда формула тилкесинде төлөмдү эсептөөнүн кадимки формуласы эмес, ажыратылгыс массивдин атайын формуласы көрүнбөйт. Башкача айтканда, азыр жеке клеткалардагы маанилерди өзгөртүү мүмкүн эмес. Сиз эсептөө натыйжаларын өзүнчө эмес, бардыгын чогуу жок кыла аласыз.
Мындан тышкары, издөө столун колдонуунун натыйжасында алынган жылдык 12.5% өлчөмүндөгү айлык төлөм функцияны колдонуу менен алынган пайыздардын ошол суммасынын маанисине дал келээрин белгилей кетсек болот. PMT. Бул дагы бир жолу эсептөөнүн туура экендигин тастыктайт.
Бул таблица массивин талдап чыгып, айта турган нерсе, жыл сайын 9.5% өлчөмүндө биз айлык төлөмдөрдүн алгылыктуу деңгээлин алабыз (29,000 рубльдан аз).
Сабак: Excel'де аннуитеттик төлөмдү эсептөө
2-ыкма: куралды эки өзгөрмө менен колдонуңуз
Албетте, учурда 9,5% жылдык насыя бере турган банктарды табуу өтө кыйын, эгер мүмкүн болбосо. Демек, башка өзгөрүлмөлөрдүн ар кандай айкалыштары үчүн айлык төлөмдүн алгылыктуу деңгээлине инвестициялоонун кандай жолдору бар экендигин көрөбүз: насыянын органы жана насыянын мөөнөтү. Бул учурда, пайыздык чен өзгөрүүсүз калат (12,5%). Бул көйгөйдү чечүүдө бир курал жардам берет. "Маалыматтар таблицасы" эки өзгөрмөнү колдонуп.
- Жаңы таблица массивин тартам. Эми тилкеде насыянын мөөнөтү көрсөтүлөт (баштап) 2 үчүн 6 жылдар менен айлар менен, бир жылга көбөйтүү менен) жана саптарда - насыя органынын өлчөмү (тартып) 850000 үчүн 950000 рублга өскөн 10000 сом). Бул учурда, эсептөө формуласы жайгашкан уюлдук (биздин шартта) болушу шарт PMT) сап жана тилке аттарынын чектеринде жайгашкан. Бул шартсыз, эки өзгөрмөнү колдонууда курал иштебейт.
- Андан кийин пайда болгон таблицанын бардык диапазонун, анын ичинде формулалары бар тилкелердин, саптардын жана уячалардын аттарын тандаңыз PMT. Өтмөккө өтүңүз "Маалымат". Мурунку мезгилдегидай эле, баскычты чыкылдатыңыз "Анализ болсочу?", курал тобунда "Маалыматтар менен иштөө". Ачкан тизмеде, тандаңыз "Маалыматтар таблицасы ...".
- Курал терезеси башталат "Маалыматтар таблицасы". Бул учурда бизге эки талаа керек. Талаада Тилке маанилерин алмаштырыңыз Насыя мөөнөтүн камтыган уячанын координаттарын баштапкы маалыматтарда көрсөтүңүз. Талаада "Маанилерди катардан катарга алмаштырыңыз" Насыя органынын маанисин камтыган баштапкы параметрлердин уячасынын дарегин көрсөтүңүз. Бардык маалыматтар киргизилгенден кийин. Баскычты чыкылдатыңыз "OK".
- Программа эсептөөнү жүргүзөт жана жадыбал аралыгын маалыматтар менен толтурат. Азыр катарлар жана тилкелердин кесилишинде ай сайын төлөнө турган сумманы, жылдык пайыздын тиешелүү суммасын жана көрсөтүлгөн насыянын мөөнөтүн көрө аласыз.
- Көрүнүп тургандай, көптөгөн баалуулуктар бар. Башка көйгөйлөрдү чечүү үчүн андан да көп болушу мүмкүн. Андыктан, натыйжаларды көбүрөөк визуализациялоо үчүн жана кайсы маанилер көрсөтүлгөн шартка жооп бербестигин тез арада аныктоо үчүн визуализация куралдарын колдонсоңуз болот. Биздин учурда, бул шарттуу форматтоо болот. Катар жана тилкенин аталыштарын эске албаганда, таблицанын диапазонундагы бардык маанилерди тандап алабыз.
- Өтмөккө жылуу "Башкы бет" жана сөлөкөтүн чыкылдатыңыз Шарттуу форматтоо. Ал куралдар блогунда жайгашкан. "Стилдери" лентада. Ачылган менюдан, тандаңыз Уюкту тандоо эрежелери. Кошумча тизмеде жайгашкан жерди чыкылдатыңыз "Азыраак ...".
- Андан кийин шарттуу форматтоо жөндөөлөрү терезеси ачылат. Сол жакта уячалар тандалып алынгандан аз маанини көрсөтүңүз. Эсиңизде болсун, насыянын ай сайынкы төлөмү азыраак болушу шарт 29000 рубл. Бул номерди киргизебиз. Туура талаасында, сиз түстү тандай аласыз, бирок аны демейки бойдон калтырсаңыз болот. Бардык керектүү орнотуулар киргизилгенден кийин, баскычты чыкылдатыңыз "OK".
- Андан кийин, маанилери жогорудагы шартка туура келген бардык клеткалар бөлүнүп чыгат.
Таблица массивин талдап чыгып, бир нече тыянак чыгарууга болот. Көрүнүп тургандай, насыянын колдонуудагы мөөнөтү менен (36 ай) айлык төлөмдүн көрсөтүлгөн суммасын инвестициялоо үчүн, биз насыяны 860000.00 рубльдан ашпашыбыз керек, башкача айтканда, пландагыдан 40,000 аз.
Эгерде дагы эле 900,000 рубль насыя алууну ниет кылсак, анда насыянын мөөнөтү 4 жыл (48 ай) болушу керек. Бул учурда гана, ай сайынкы төлөм 29000 рублдин чегинен ашпайт.
Ошентип, ушул таблицалык массивди колдонуп, ар бир тандоонун оң жана терс жактарын анализдеп, насыя алуучу насыянын шарттары боюнча конкреттүү чечим кабыл алып, эң ылайыктуу вариантты тандап алат.
Албетте, издөө столун насыя варианттарын эсептөө үчүн гана эмес, башка көптөгөн көйгөйлөрдү чечүү үчүн да колдонсо болот.
Сабак: Excel программасында шарттуу форматтоо
Жалпысынан, издөөнүн үстөлү ар кандай өзгөрмөлөрдүн айкалышы үчүн натыйжаны аныктоодо өтө пайдалуу жана салыштырмалуу жөнөкөй курал экендигин белгилей кетүү керек. Шарттуу форматтоону бир эле учурда колдонуп, алынган маалыматты элестете аласыз.
