Microsoft Excel бағдарламасындағы мәліметтер кестесі

Жиі, әр түрлі енгізу мәліметтері үшін түпкілікті нәтижені есептеу керек. Осылайша, пайдаланушы іс-әрекеттің барлық ықтимал нұсқаларын бағалай алады, өзара әрекеттесу нәтижелері оны қанағаттандыратындарды таңдайды және ең оңтайлы нұсқаны таңдай алады. Бұл тапсырманы орындау үшін Excel бағдарламасында арнайы құрал бар - «Деректер кестесі» (Ауыстыру кестесі) Жоғарыда көрсетілген сценарийлерді аяқтау үшін оны қалай пайдалануға болатынын білейік.

Сондай-ақ оқыңыз: Excel-де параметрлерді таңдау

Деректер кестесін пайдалану

Аспап «Деректер кестесі» Ол бір немесе екі анықталған айнымалының әртүрлі өзгерістері үшін нәтижені есептеуге арналған. Есептеуден кейін барлық мүмкін нұсқалар кесте түрінде пайда болады, оны факторлық талдау матрицасы деп атайды. «Деректер кестесі» құралдар тобына жатады «Талдау болса не болады», ол қойындыдағы таспаға орналастырылады «Деректер» блокта «Деректермен жұмыс». Excel 2007 бағдарламасына дейін бұл құрал шақырылған Ауыстыру кестесі, қазіргі атауынан гөрі оның мәнін дәлірек көрсетті.

Іздеу кестесін көптеген жағдайларда қолдануға болады. Мысалы, әдеттегі нұсқа - сізге несиелеу мерзімінің әр түрлі өзгерістері мен несие мөлшерін немесе несиелеу мерзімі мен пайыздық мөлшерлемені ай сайынғы төлемнің мөлшерін есептеу қажет болған кезде. Сондай-ақ, бұл құралды инвестициялық жобалардың үлгілерін талдау кезінде қолдануға болады.

Бірақ сіз сонымен қатар бұл құралды шамадан тыс пайдалану жүйенің тежелуіне әкеліп соқтыратынын білуіңіз керек, өйткені деректер үнемі қайта есептеледі. Осыған ұқсас проблемаларды шешу үшін кішкене кестелік массивтерде осы құралды емес, толтырғыш маркердің көмегімен формуланы көшіруді қолдану ұсынылады.

Негізделген өтініш «Деректер кестелері» формулаларды көшіру көп уақытты алуы мүмкін, ал процедураның өзінде қателіктер жібереді. Бірақ бұл жағдайда жүйеге қажетсіз жүктемелердің алдын алу үшін алмастыру кестесінің диапазонындағы формулаларды автоматты түрде қайта санауды өшіру ұсынылады.

Деректер кестесінің әр түрлі қолданылуының негізгі айырмашылығы - есептеуге қатысатын айнымалылар саны: бір айнымалы немесе екі.

1-әдіс: құралды бір айнымалымен қолданыңыз

Мәліметтер кестесі бір айнымалы мәнмен қолданылған кезде бірден қарастырайық. Несиелеудің ең типтік мысалын алайық.

Сонымен, қазіргі кезде бізге келесі несие шарттары ұсынылады:

• Несие мерзімі - 3 жыл (36 ай);
• Несие сомасы - 900 000 рубль;
• Сыйақы мөлшерлемесі - жылдық 12,5%.

Төлемдер аннуитет схемасына сәйкес төлем кезеңінің (айдың) соңында, яғни тең үлестермен жүзеге асырылады. Сонымен бірге, несиенің барлық мерзімінің басында төлемдердің едәуір бөлігі пайыздық төлемдер болып табылады, бірақ орган кішірейген сайын пайыздық төлемдер азаяды және несиенің өтеу сомасы көбейеді. Жалпы төлем, жоғарыда айтылғандай, өзгеріссіз қалады.

Ай сайынғы төлемнің қандай мөлшерде болатынын, оның ішінде несие органының қайтарымы мен пайыздық төлемдерді есептеу керек. Ол үшін Excel бағдарламасында оператор бар PMT.

PMT қаржылық функциялар тобына жатады және оның міндеті несие органының мөлшеріне, несие мерзімі мен сыйақы мөлшерлемесі негізінде ай сайынғы аннуитеттік төлемді есептеу болып табылады. Бұл функцияның синтаксисі ұсынылған

= PLT (жылдамдық; nper; ps; bs; түрі)

Сұраныс - несиелік төлемдердің пайыздық мөлшерлемесін анықтайтын дәлел. Көрсеткіш кезеңге қойылады. Біздің төлем мерзімі бір айға тең. Сондықтан жылдық 12,5% мөлшерлемені бір жылдағы айлар санына, яғни 12-ге бөлу керек.

«Нпер» - несиенің бүкіл мерзімі бойынша кезеңдер санын анықтайтын дәлел. Біздің мысалда, мерзім - бір ай, ал несие мерзімі - 3 жыл немесе 36 ай. Осылайша, кезеңдер саны 36-ға ерте болады.

«PS» - несиенің ағымдағы құнын анықтайтын дәлел, яғни ол берілген кезде несие органының мөлшері. Біздің жағдайда бұл көрсеткіш 900 000 рубльді құрайды.

«BS» - толық төлеу кезіндегі несие органының мөлшерін көрсететін дәлел. Әрине, бұл көрсеткіш нөлге тең болады. Бұл дәлел міндетті емес. Егер сіз оны өткізіп жіберсеңіз, онда ол «0» санына тең деп саналады.

«Түрі» - сонымен қатар қосымша дәлел. Ол төлемнің нақты қашан жасалатынын хабарлайды: кезеңнің басында (параметр - "1") немесе кезеңнің соңында (параметр - "0") Есімізде, біздің төлеміміз күнтізбелік айдың соңында жасалады, яғни бұл дәлелдің мәні тең болады "0". Бірақ бұл индикатор міндетті емес екендігін ескерсек және әдепкі бойынша қолданылмаса, мән тең болуы керек "0", көрсетілген мысалда оны мүлдем жоққа шығаруға болады.

1. Сонымен, есептеуге көшеміз. Парақтағы есептелген мәні көрсетілетін ұяшықты таңдаңыз. Түймесін басыңыз «Кірістіру функциясы».



2. Басталады Мүмкіндік шебері. Санатқа көшеміз «Қаржы», тізімнен атты таңдаңыз «PLT» түймесін басыңыз «Жарайды».



3. Осыдан кейін жоғарыда аталған функцияның аргументтер терезесі іске қосылады.

   Меңзерді өріске салыңыз Сұраныс, содан кейін біз парақтағы ұяшыққа жылдық пайыздық мөлшерлемені нұқамыз. Көріп отырғаныңыздай, оның координаттары бірден өрісте көрінеді. Бірақ біз есімізде, бізге айлық мөлшерлеме керек, сондықтан біз нәтижені 12-ге бөлеміз (/12).

   Алаңда «Нпер» дәл осылай несие мерзімі ұяшықтарының координаттарын енгіземіз. Бұл жағдайда сіз ештеңемен бөлісудің қажеті жоқ.

   Алаңда Ps несие органының құнын қамтитын ұяшықтың координаттарын көрсету керек. Біз мұны жасаймыз. Көрсетілген координаттардың алдына белгі қойдық "-". Функцияның фактісі PMT Әдетте ол ай сайынғы несие бойынша төлемді дұрыс есептегенде теріс белгісімен қорытынды нәтиже береді. Деректер кестесін қолдану айқындылығы үшін оң сан болуы керек. Сондықтан, біз белгі қойдық минус функция аргументтерінің біріне дейін. Көбейту белгілі минус қосулы минус соңында береді плюс.

   Өрістерге «Bs» және «Түрі» деректер мүлдем енгізілмейді. Түймесін басыңыз «Жарайды».



4. Осыдан кейін оператор алдын-ала тағайындалған ұяшықтағы жалпы айлық төлемнің нәтижесін есептейді және көрсетеді - 30108,26 рубль. Бірақ мәселе мынада: қарыз алушы айына ең көбі 29000 рубль төлей алады, яғни ол неғұрлым төмен пайыздық мөлшерлемемен шарттар ұсынатын банкті тауып алуы керек, немесе несие құрылымын қысқартуы керек немесе несие мерзімін ұлғайтуы керек. Іздеу кестесі әртүрлі нұсқаларды анықтауға көмектеседі.



5. Алдымен іздеу кестесін бір айнымалымен қолданыңыз. Міндетті ай сайынғы төлемнің мөлшері жылдық ставканың әр түрлі өзгеруімен қалай өзгеретінін көрейік 9,5% жылдық және аяқталатын 12,5% жылдық өсіммен 0,5%. Барлық қалған жағдайлар өзгеріссіз қалады. Бағандардың атаулары әр түрлі пайыздық мөлшерлемелерге сәйкес келетін кесте ауқымын сызамыз. Осы сызықпен «Ай сайынғы төлемдер» сол күйінде қалдырыңыз. Оның бірінші ұяшығында біз бұрын есептеген формула болуы керек. Қосымша ақпарат алу үшін сіз сызықтар қоса аласыз «Қарыздың жалпы сомасы» және «Жалпы пайыздар». Есептеу орналасқан баған тақырыпсыз жасалады.



6. Әрі қарай, қазіргі жағдайда несиенің жалпы сомасын есептейміз. Ол үшін жолдың бірінші ұяшығын таңдаңыз «Қарыздың жалпы сомасы» жасушалардың құрамын көбейтіңіз «Ай сайынғы төлем» және «Қарыз мерзімі». Осыдан кейін түймесін басыңыз Енгізіңіз.



7. Ағымдағы жағдайда пайыздардың жалпы сомасын есептеу үшін біз несие органының сомасын несиенің жалпы сомасынан аламыз. Нәтижені экранда көрсету үшін батырманы басыңыз Енгізіңіз. Осылайша, біз несиені төлеген кезде артық төлеген соманы аламыз.



8. Енді құралды қолдану уақыты келді «Деректер кестесі». Жол атауларын қоспағанда, біз бүкіл кесте массивін таңдаймыз. Осыдан кейін қойындыға өтіңіз «Деректер». Таспадағы түймені басыңыз «Талдау болса не болады»ол құралдар тобында орналасқан «Деректермен жұмыс» (Excel 2016 бағдарламасында, құралдар тобы «Болжам») Содан кейін шағын мәзір ашылады. Онда біз позицияны таңдаймыз «Деректер кестесі ...».



9. Шағын терезе ашылады, ол деп аталады «Деректер кестесі». Көріп отырғаныңыздай, оның екі өрісі бар. Біз бір айнымалымен жұмыс істейтіндіктен, бізге олардың тек біреуі керек. Айнымалы бағанды ​​баған бойынша өзгерткендіктен, біз өрісті қолданамыз Бағанның мәндерін ауыстырыңыз. Онда жүгіргіні орнатыңыз, содан кейін ағымдағы пайыздық мөлшерлеме бар бастапқы деректер қорындағы ұяшықты нұқыңыз. Өрісте ұяшық координаттары көрсетілгеннен кейін түймесін басыңыз «Жарайды».



10. Құрал барлық кестелік ауқымды есептейді және пайыздық мөлшерлеменің әртүрлі нұсқаларына сәйкес келетін мәндермен толтырады. Егер сіз курсорды осы кесте аймағының кез-келген элементіне орналастырсаңыз, онда формула жолында төлемді есептеу үшін әдеттегі формула көрсетілмейтінін, ал ажыратылмайтын массивтің арнайы формуласын көруге болады. Яғни, қазір жеке ұяшықтардағы мәндерді өзгерту мүмкін емес. Есептеу нәтижелерін бөлек емес, барлығын бірге жоя аласыз.

Сонымен қатар, сіз іздеу кестесін қолдану нәтижесінде алынған жылдық 12,5% мөлшеріндегі ай сайынғы төлем функцияны қолдану арқылы алған пайыздың бірдей мөлшеріне сәйкес келетінін көре аласыз. PMT. Бұл тағы бір рет есептеудің дұрыстығын дәлелдейді.

Осы кесте массивін талдай келе, сіз өзіңіз көріп отырғаныңыздай, тек жылына 9,5% мөлшерінде ай сайынғы төлемнің қолайлы деңгейі (29 000 рубльден аз) алады деп айту керек.

Сабақ: Excel-де аннуитеттік төлемді есептеу

2 әдіс: құралды екі айнымалымен қолданыңыз

Әрине, қазіргі кезде жылдық 9,5% несие беретін банктерді табу өте қиын, егер мүмкін болмаса. Сондықтан, біз басқа айнымалылардың әр түрлі комбинациясы үшін ай сайынғы төлемнің қолайлы деңгейіне инвестициялаудың қандай нұсқалары бар екенін көреміз: несие органының мөлшері мен несие мерзімі. Бұл жағдайда пайыздық мөлшерлеме өзгеріссіз қалады (12,5%). Бұл мәселені шешуде бізге көмекші құрал көмектеседі. «Деректер кестесі» екі айнымалыны қолдана отырып.

1. Жаңа кесте массивін саламыз. Енді бағанда несие мерзімі көрсетіледі (бастап) 2 дейін 6 жылдар айлармен бір жыл өсімімен), ал жолдарда - несие органының мөлшері (бастап) 850000 дейін 950000 рубль өсіммен 10000 рубль). Бұл жағдайда міндетті шарт - есептеу формуласы орналасқан ұяшық (біздің жағдайда) PMT), жолдар мен баған атауларының шекарасында орналасқан. Бұл шартсыз екі айнымалыны қолданған кезде құрал жұмыс істемейді.



2. Содан кейін бағаналардың, жолдардың және формуласы бар ұяшықтардың атауларын қосқандағы барлық кесте ауқымын таңдаңыз PMT. Қойындыға өтіңіз «Деректер». Бұрынғыдай, түймесін басыңыз «Талдау болса не болады», құралдар тобында «Деректермен жұмыс». Ашылған тізімнен таңдаңыз «Деректер кестесі ...».



3. Құрал терезесі басталады «Деректер кестесі». Бұл жағдайда бізге екі өріс қажет. Алаңда Бағанның мәндерін ауыстырыңыз бастапқы мәліметтерде несие мерзімі көрсетілген ұяшықтың координаттарын көрсетіңіз. Алаңда «Мәндерді қатар бойынша қатарға ауыстыру» несие органының құнын қамтитын бастапқы параметрлер ұяшығының мекенжайын көрсетіңіз. Барлық деректер енгізілгеннен кейін. Түймесін басыңыз «Жарайды».



4. Бағдарлама есептеуді орындайды және кесте ауқымын мәліметтермен толтырады. Енді жолдар мен бағандардың қиылысында жылдық пайыздың тиісті мөлшерімен және көрсетілген несие мерзімімен ай сайынғы төлемнің нақты қандай болатындығын байқауға болады.



5. Көріп отырғаныңыздай, құндылықтар өте көп. Басқа мәселелерді шешу үшін бұдан да көп болуы мүмкін. Сондықтан нәтижелердің шығуын көрнекі ету үшін және қандай мәндер берілген шартты қанағаттандырмайтындығын дереу анықтау үшін визуализация құралдарын қолдануға болады. Біздің жағдайда бұл шартты форматтау болады. Жолдар мен баған тақырыптарын қоспағанда, кесте ауқымының барлық мәндерін таңдаймыз.



6. Қойындыға өтіңіз «Үй» белгішесін нұқыңыз Шартты пішімдеу. Ол құралдар блогында орналасқан. Стильдер таспада. Ашылатын мәзірде таңдаңыз Ұяшықтарды таңдау ережелері. Қосымша тізімде позицияны басыңыз «Аз ...».



7. Осыдан кейін шартты пішімдеу параметрлері терезесі ашылады. Сол жақ өрісте ұяшықтар таңдалатын мәннен аз мәнді көрсетіңіз. Естеріңізде болса, біз ай сайынғы несие төлемі аз болатын жағдайға қанағаттанамыз 29000 рубль. Біз бұл нөмірді енгіземіз. Дұрыс өрісте бөлектеу түсін таңдауға болады, бірақ оны әдепкі күйде қалдыра аласыз. Барлық қажетті параметрлер енгізілгеннен кейін түймесін басыңыз «Жарайды».



8. Осыдан кейін мәндері жоғарыда аталған жағдайға сәйкес келетін барлық ұяшықтар бөлектеледі.

Кесте массивін талдай отырып, біз қорытынды жасай аламыз. Көріп отырғаныңыздай, қолданыстағы несие мерзімімен (36 ай) ай сайынғы төлемнің көрсетілген сомасына қаражат салу үшін бізге 860000.00 рубльден аспайтын несие алу керек, яғни бастапқыда жоспарланғаннан 40 000 кем.

Егер біз әлі де 900 000 рубль мөлшерінде несие алғымыз келсе, онда несие мерзімі 4 жыл (48 ай) болуы керек. Тек осы жағдайда ғана ай сайынғы төлем 29000 рубльден белгіленген лимиттен аспайды.

Осылайша, осы кесте массивін қолдана отырып және әр опцияның оң және теріс жақтарын талдай отырып, қарыз алушы мүмкін болатын ең қолайлы нұсқаны таңдап, несие шарттары туралы нақты шешім қабылдай алады.

Әрине, іздеу кестесі тек несиелік опцияларды есептеу үшін ғана емес, сонымен қатар басқа да көптеген мәселелерді шешуге арналған.

Сабақ: Excel-де шартты форматтау

Жалпы, іздеу кестесі әртүрлі ауыспалы комбинациялар үшін нәтижені анықтайтын өте пайдалы және салыстырмалы қарапайым құрал екенін атап өткен жөн. Шартты форматтауды бір уақытта қолдана отырып, алынған ақпаратты визуализациялауға болады.