Жиі жиі енгізу деректерінің әр түрлі комбинациясы үшін соңғы нәтижені есептеу қажет. Осылайша, қолданушы әрекет етудің барлық ықтимал нұсқаларын бағалауға, өзара әрекеттесу нәтижесі қанағаттандыратындарды таңдауға және, ақырында, оңтайлы опцияны таңдай алады. Excel бағдарламасында бұл тапсырма үшін арнайы құрал бар - «Деректер кестесі» («Іздеу кестесі»). Жоғарыда келтірілген сценарийлерді орындау үшін оны қалай пайдалану керектігін білейік.
Сондай-ақ қараңыз: Excel параметріндегі параметрді таңдау
Деректер кестесін пайдалану
Құрал «Деректер кестесі» нәтижені бір немесе екі анықталған айнымалылардың әр түрлі вариацияларымен есептеуге арналған. Есептеуден кейін барлық ықтимал нұсқалар кесте түрінде пайда болады, ол факторлық талдаудың матрицасы деп аталады. «Деректер кестесі» құралдардың тобына жатады «Не-не» талдауқойындыдағы таспаға орналастырылған «Деректер» блокта «Деректермен жұмыс істеу». Excel 2007 нұсқасына дейін бұл құрал атауды қолданды. «Іздеу кестесі»бұл оның атауын қазіргі атаудан да дәлірек көрсетеді.
Іздеу кестесін көптеген жағдайларда пайдалануға болады. Мысалы, әдеттегі опция несие беру кезеңінің әртүрлі нұсқалары мен несие сомасын немесе кредиттеу кезеңін және пайыздық мөлшерлемен ай сайынғы несие төлемін есептеу қажет болғанда. Бұл құралды инвестициялық жобаның модельдерін талдау кезінде де пайдалануға болады.
Бірақ бұл құралдың шамадан тыс пайдаланылуы жүйелі тежеуді тудыруы мүмкін екенін білу керек, өйткені деректер үнемі қайта есептеледі. Сондықтан, осы құралды ұқсас кестелердегі шағын кестелік массивтерде қолдануға болмайды, бірақ толтыру белгісін қолдану арқылы формулаларды көшіруді қолдану ұсынылады.
Негізделген өтініш «Деректер кестелері» Үлкен кестеліктер ауқымында ғана, формулаларды көшіру үлкен уақытты талап ете алады, және процедура кезінде қателердің ықтималдығы артады. Бірақ тіпті бұл жағдайда жүйеде артық жүктемені болдырмау үшін, іздеу кестесінің ауқымында формулаларды автоматты түрде қайта санауды өшіру ұсынылады.
Деректер кестесінің әр түрлі пайдаланулары арасындағы негізгі айырмашылық есептеуге қатысатын айнымалы мәндердің саны: бір айнымалы немесе екі.
1-әдіс: құралды бір айнымалы мәнмен пайдаланыңыз
Деректер кестесінің бір айнымалы мәнмен пайдаланылған кезде дереу опцияны қарастырайық. Кредиттеудің ең қарапайым үлгісін алыңыз.
Сонымен, қазіргі уақытта бізге несие шарттары ұсынылады:
- Несие мерзімі - 3 жыл (36 ай);
- Кредит сомасы - 900000 рубль;
- Пайыздық мөлшерлеме - жылдық 12,5%.
Төлемдер төлем мерзімінің (айдың) соңында аннуитеттік схеманы, яғни тең үлестермен, жүргізіледі. Сонымен қатар, барлық несие кезеңінің басында пайыздық төлемдер төлемдердің елеулі бөлігін құрайды, алайда органның қысқаруы, пайыздық төлемдердің төмендеуі және органның өзі өтеу мөлшері көбейеді. Жоғарыда айтылғандай, жалпы төлем өзгеріссіз қалады.
Ай сайынғы төлем сомасының несие органы мен пайыздық төлемдерді өтеуін қамтитын соманы есептеу қажет. Ол үшін Excel-те оператор бар PMT.
PMT Ол қаржы функцияларының тобына жатады және оның міндеті - несие органының сомасына, несие мерзімі мен пайыздық мөлшерлеменің негізінде аннуитетті ай сайынғы несие бойынша төлемдерді есептеу. Бұл функцияның синтаксисі келесідей.
= PMT (жылдамдығы; nper; ps; bs; түрі)
«Бета» - Кредиттік төлемдердің пайыздық мөлшерлемесін анықтайтын дәлел. Көрсеткіш кезең үшін орнатылады. Біздің төлем мерзіміміз - бір ай. Сондықтан жыл сайынғы 12,5% мөлшерлеме жылдағы айлар санына, яғни 12-ге дейін бөлінуі керек.
«Кпер» - Қарыздың бүкіл кезеңі үшін кезеңдердің санын анықтайтын дәлел. Біздің мысалда бұл кезең бір ай, ал несие мерзімі - 3 жыл немесе 36 ай. Осылайша, кезеңдердің саны ерте 36 болады.
«PS» - қарыздың дисконтталған құнын айқындайтын дәлел, яғни ол шығарылған сәтте несие органының мөлшері. Біздің жағдайда бұл көрсеткіш 900 мың рубль.
«BS» - толық көлемде қарыз органының мөлшерін көрсететін дәлел. Әрине, бұл көрсеткіш нөлге тең болады. Бұл аргумент міндетті емес. Егер сіз оны өткізіп жібермесеңіз, ол «0» санына тең деп есептеледі.
«Түрі» - Факультативті дәлел. Ол төлем жүргізілетіні туралы хабарлайды: кезеңнің басында (параметр - "1") немесе кезең соңында (параметр - "0"). Естеріңізде болса, біздің төлем күнтізбелік айдың соңында жасалады, яғни бұл дәлелдің мәні тең болады "0". Бірақ, бұл көрсеткіш міндетті емес және әдепкі бойынша, ол пайдаланылмаса, мән деп саналады "0", Онда көрсетілген мысалда оны мүлдем қолдануға болмайды.
- Сондықтан есептеуді жалғастырамыз. Есептелген мән көрсетілетін парақтағы ұяшықты таңдаңыз. Біз батырманы басамыз «Функцияны кірістіру».
- Басталады Функция шебері. Санатқа көшуді жүзеге асырыңыз «Қаржы», тізімнен атауды таңдаңыз «PLT» батырмасын басыңыз «ОК».
- Осыдан кейін, жоғарыда аталған функцияның дәлел терезесін белсендіру бар.
Курсорды өріске қойыңыз «Бета»содан кейін парақтағы ұяшыққа жылдық пайыздық мөлшерлемені басыңыз. Көріп отырғандай, оның координаттары бірден өрісте көрсетіледі. Бірақ, есімізде, ай сайынғы мөлшерлеме қажет, сондықтан біз нәтижені 12/12).
Алаңда «Кпер» Сонымен қатар, біз кредиттік термин клеткаларының координаттарын енгіземіз. Бұл жағдайда ештеңені бөлуге болмайды.
Алаңда «Пс» сіз несие денесінің мәнін қамтитын ұяшықтың координаттарын көрсетуіңіз керек. Біз мұны істейміз. Сондай-ақ көрсетілген координаттардың алдында белгі қойдық. "-". Мәселе мынада, бұл функция PMT әдепкі бойынша, соңғы нəтижені несие бойынша ай сайынғы төлемді əділ түрде ескере отырып, теріс белгіпен береді. Бірақ түсінікті болу үшін деректер кестесін оң ету қажет. Сондықтан біз белгі қойдық «минус» функция дәлелдерінің біреуіне дейін. Белгілі болғандай, көбейту «минус» қосу «минус» ақыр соңында береді плюс.
Өрістерде «Bs» және «Түрі» Деректерді мүлдем енгізбейміз. Біз батырманы басамыз «ОК».
- Осыдан кейін, оператор алдын ала белгіленген ұяшықта есептеп шығарады және жалпы ай сайынғы төлем нәтижесін көрсетеді - 30108,26 рубль. Бірақ мәселе қарыз алушы айына ең көп дегенде 29 мың рубль төлеуге қабілетті екенін, яғни банкке неғұрлым төмен пайыздық мөлшерлеме ұсынатын немесе несие органын қысқартуға немесе несие мерзімін ұзартуға жағдай жасауы керек. Әрекет етудің әртүрлі нұсқаларын есептеу бізге іздеу кестесіне көмектеседі.
- Бастау үшін іздеу кестесін бір айнымалы мәнмен пайдаланыңыз. Міндетті ай сайынғы төлемнің құнын жылдық мөлшерлеменің өзгеруімен қалай өзгеретінін көрейік 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,00 рубльден аспайтын қарыз алуымыз керек, яғни бастапқыда жоспарланғаннан 40 мыңға аз.
Егер біз әлі де 900 мың рубль мөлшерінде несие алуға ниет білдірсек, несие мерзімі 4 жыл (48 ай) болуы керек. Тек осы жағдайда ғана айлық төлем мөлшері 29 000 рубльден аспайды.
Осылайша, осы кестелік массивтің артықшылығын пайдаланып, әрбір опцияның артықшылығы мен жағымсыздығын талдай отырып, қарыз алушы несиелендіру шарттарына қатысты нақты шешім қабылдай алады, ол өз қажеттіліктерін қанағаттандыратын нұсқаны таңдайды.
Әрине, іздеу таблицасы кредиттік опцияларды есептеу үшін ғана емес, көптеген басқа мәселелерді шешу үшін де қолданыла алады.
Сабақ: Excel бағдарламасында шартты пішімдеу
Тұтастай алғанда, іздеу кесте айнымалылардың түрлі комбинацияларының нәтижесін анықтау үшін өте пайдалы және салыстырмалы қарапайым құралы екенін атап өткен жөн. Шартты пішімдеуді қолдану арқылы, сонымен бірге алынған ақпаратты визуализациялауға болады.
