Çox vaxt müxtəlif məlumatların birləşməsi üçün son nəticəni hesablamalısınız. Beləliklə, istifadəçi hərəkətlərin bütün mümkün variantlarını qiymətləndirə, qarşılıqlı nəticələr onu qane edənləri və nəhayət, ən optimal variantı seçəcəkdir. Excel-də bu işi yerinə yetirmək üçün xüsusi bir vasitə var - "Məlumat cədvəli" (Əvəzetmə cədvəli) Yuxarıdakı ssenariləri tamamlamaq üçün necə istifadə edəcəyimizi öyrənək.
Həm də oxuyun: Excel-də parametr seçimi
Məlumat cədvəlindən istifadə
Alət "Məlumat cədvəli" Nəticəni bir və ya iki dəyişən dəyişən müxtəlif dəyişikliklər üçün hesablamaq üçün nəzərdə tutulmuşdur. Hesablamadan sonra, bütün mümkün variantlar cədvəl şəklində görünür, buna amil analizinin matrixi deyilir. "Məlumat cədvəli" alətlər qrupuna aiddir "Təhlil olarsa", nişandakı lentə yerləşdirilir "Məlumat" blokda "Məlumatla işləmə". Excel 2007-dən əvvəl bu vasitə çağırıldı Əvəzetmə cədvəli, indiki addan daha da mahiyyətini daha yaxşı əks etdirmişdir.
Axtarış masası bir çox hallarda istifadə edilə bilər. Məsələn, tipik bir seçim, kredit müddəti və kredit məbləği, ya da kredit müddəti və faiz dərəcəsi üçün müxtəlif dəyişikliklər üçün aylıq kredit ödənişinin miqdarını hesablamaq lazım olduğunda. Ayrıca, bu vasitə investisiya layihələri modellərinin təhlilində istifadə edilə bilər.
Bununla yanaşı, bu alətin həddindən artıq istifadəsinin sistemin əyləcinə səbəb ola biləcəyini də bilməlisiniz, çünki məlumatlar davamlı olaraq təkrarlanır. Buna görə də, bu alətdən istifadə etməmək, doldurma markerindən istifadə edərək düsturların surətini çıxarmaq üçün oxşar problemlərin həlli üçün kiçik masa massivlərində tövsiyə olunur.
Əsaslandırılmış tətbiq "Məlumat masaları" yalnız böyük masa aralığında olur, düsturları kopyalamaq çox vaxt tələb edə bilər və prosedur zamanı səhv etmək ehtimalı artır. Ancaq bu vəziyyətdə sistemə həddindən artıq yüklənməməsi üçün əvəz masasının aralığında düsturların avtomatik yenidən hesablanmasını dayandırmaq tövsiyə olunur.
Məlumat cədvəlinin fərqli istifadələri arasındakı əsas fərq hesablamada iştirak edən dəyişənlərin sayıdır: bir dəyişən və ya iki.
Metod 1: aləti bir dəyişən ilə istifadə edin
Verilənlər cədvəli bir dəyişən dəyəri ilə istifadə edildikdə dərhal seçimə baxaq. Ən tipik kredit götürmə nümunəsini götürün.
Beləliklə, hazırda bizə aşağıdakı kredit şərtləri təklif olunur:
- Kredit müddəti - 3 il (36 ay);
- Kreditin məbləği - 900,000 rubl;
- Faiz dərəcəsi - illik 12.5%.
Ödənişlər, ödəmə müddətinin (ayının) sonunda, annuitet sxeminə görə, yəni bərabər hissələrdə baş verir. Eyni zamanda, bütün kredit müddətinin əvvəlində ödənişlərin əhəmiyyətli bir hissəsi faiz ödəmələridir, lakin orqan kiçildikcə faiz ödənişləri azalır və bədənin ödəmə miqdarı da artır. Ümumi ödəniş, yuxarıda qeyd edildiyi kimi, dəyişməz olaraq qalır.
Kredit orqanı və faiz ödənişləri daxil olmaqla aylıq ödənişin nə qədər olacağını hesablamaq lazımdır. Bunun üçün Excel-də bir operator var PMT.
PMT maliyyə funksiyaları qrupuna aiddir və onun vəzifəsi kredit orqanının miqdarı, kredit müddəti və faiz dərəcəsi əsasında aylıq annuitet tipli kredit ödənişini hesablamaqdır. Bu funksiyanın sintaksisi təqdim olunur
= PLT (nisbət; nper; ps; bs; növü)
Təklif - kredit ödənişlərinin faiz dərəcəsini müəyyənləşdirən bir dəlil. Göstərici dövr üçün təyin olunur. Ödəniş müddətimiz bir aya bərabərdir. Buna görə illik 12,5% nisbətini bir ildəki ayların sayına, yəni 12-yə bölmək lazımdır.
"Nper" - bütün kredit müddəti üçün dövrlərin sayını təyin edən bir arqument. Nümunəmizdə, müddət bir ay, kredit müddəti 3 il və ya 36 aydır. Beləliklə, dövrlərin sayı 36-nın əvvəlində olacaqdır.
"PS" - kreditin cari dəyərini təyin edən bir arqument, yəni kredit verildiyi anda kredit orqanının ölçüsüdür. Bizim vəziyyətimizdə bu rəqəm 900.000 rubl təşkil edir.
"BS" - tam ödəmə zamanı kredit orqanının ölçüsünü göstərən bir dəlil. Təbii ki, bu göstərici sıfıra bərabər olacaqdır. Bu mübahisə isteğe bağlıdır. Atlasanız, "0" rəqəminə bərabər olduğu güman edilir.
"Növü" - ayrıca bir arqument. Ödənişin dəqiq nə vaxt ediləcəyini elan edir: dövrün əvvəlində (parametr - "1") və ya dövrün sonunda (parametr - "0") Xatırladığımız kimi, ödənişimiz təqvim ayının sonunda edilir, yəni bu arqumentin dəyəri bərabər olacaq "0". Lakin bu göstəricinin məcburi olmadığını və ümumiyyətlə istifadə edilmədiyi təqdirdə dəyərin bərabər olacağı ehtimal edilir "0", sonra göstərilən nümunədə tamamilə buraxıla bilər.
- Beləliklə, hesablamaya davam edirik. Hesablanmış dəyərin göstəriləcəyi vərəqdə bir hücrə seçin. Düyməni vurun "Funksiya daxil et".
- Başlayır Xüsusiyyət sehrbazdır. Kateqoriyaya keçirik "Maliyyə", siyahıdan ad seçin "PLT" düyməsini basın "OK".
- Bunun ardınca yuxarıdakı funksiyanın dəlillər pəncərəsi işə salınır.
Kursoru sahəyə qoyun Təklif, bundan sonra illik faiz dərəcəsi dəyəri olan vərəqdəki xanaya vururuq. Gördüyünüz kimi, onun koordinatları dərhal sahədə göstərilir. Ancaq xatırladığımız kimi, aylıq dərəcəyə ehtiyacımız var və buna görə nəticəni 12-ə bölürük (/12).
Sahədə "Nper" eyni şəkildə kredit müddəti hüceyrələrinin koordinatlarına daxil oluruq. Bu vəziyyətdə bir şey paylaşmaq lazım deyil.
Sahədə Ps kredit orqanının dəyərini ehtiva edən hüceyrənin koordinatlarını göstərməlisiniz. Biz bunu edirik. Göstərilən koordinatların qarşısında bir işarə də qoyduq "-". Fakt budur ki, funksiya PMT defolt olaraq, aylıq kredit ödəməsi itkisini nəzərə alaraq mənfi işarə ilə son nəticəni verir. Lakin məlumat cədvəlinin aydınlığı üçün bu rəqəmin müsbət olması lazımdır. Buna görə bir işarə qoyduq mənfi funksiya arqumentlərindən birindən əvvəl. Çarpma məlumdur mənfi yandır mənfi sonda verir üstəgəl.
Sahələrə "Bs" və "Növü" məlumat ümumiyyətlə daxil edilmir. Düyməni vurun "OK".
- Bundan sonra, operator ümumi aylıq ödənişin nəticəsini əvvəlcədən təyin edilmiş bir kamerada hesablayır və göstərir - 30108,26 rubl. Ancaq problem borcalanın ayda maksimum 29 min rubl ödəməyi bacarmasıdır, yəni ya daha aşağı faiz dərəcəsi ilə şərt təklif edən bank tapmalı, ya da kredit orqanını azaltmalı, ya da kredit müddətini artırmalıdır. Axtarış masası bizə müxtəlif variantları anlamağa kömək edəcəkdir.
- Əvvəlcə bir dəyişən ilə axtarış masasını istifadə edin. Məcburi aylıq ödənişin həcmindən başlayaraq illik dərəcənin müxtəlif dəyişiklikləri ilə necə dəyişəcəyini görək 9,5% illik və bitən 12,5% illik artımlarla 0,5%. Bütün digər şərtlər dəyişməz qalır. Sütunların adları müxtəlif faiz dərəcələrinə uyğun olacaq bir cədvəl çəkirik. Bu xətt ilə "Aylıq ödənişlər" olduğu kimi buraxın. İlk hüceyrəsində əvvəllər hesabladığımız düstur olmalıdır. Daha çox məlumat üçün xətlər əlavə edə bilərsiniz "Kreditin ümumi məbləği" və "Cəmi faiz". Hesablamanın yerləşdiyi sütun başlıqsız aparılır.
- Sonra, cari şərtlərdə kreditin ümumi məbləğini hesablayırıq. Bunu etmək üçün satırın ilk xanasını seçin "Kreditin ümumi məbləği" və hüceyrələrin tərkibini çoxalt "Aylıq ödəniş" və "Kredit müddəti". Bundan sonra düyməni vurun Daxil edin.
- Mövcud şərtlərdə faizlərin ümumi həcmini hesablamaq üçün eyni dərəcədə kredit orqanının məbləğini kreditin ümumi məbləğindən çıxırıq. Nəticəni ekranda göstərmək üçün düyməni vurun Daxil edin. Beləliklə, krediti ödəyərkən ödədiyimiz məbləği alırıq.
- İndi aləti tətbiq etməyin vaxtı gəldi "Məlumat cədvəli". Sətir adları istisna olmaqla, bütün masa massivini seçirik. Bundan sonra nişana keçin "Məlumat". Şeridəki düyməni vurun "Təhlil olarsa"alətlər qrupunda olan "Məlumatla işləmə" (Excel 2016-da bir qrup alət "Proqnoz") Sonra kiçik bir menyu açılır. Bunun içində bir mövqe seçirik "Məlumat cədvəli ...".
- Kiçik bir pəncərə açılır, buna deyilir "Məlumat cədvəli". Gördüyünüz kimi, bunun iki sahəsi var. Bir dəyişən ilə işlədiyimiz üçün onlardan yalnız birinə ehtiyacımız var. Dəyişən sütunu sütunla dəyişdirdiyimiz üçün sahəni istifadə edəcəyik İçindəki Sütun Dəyərlərini. Orada kursoru seçin və sonra cari faizi olan orijinal verilənlər bazasındakı hücrəni vurun. Sahədə hüceyrə koordinatları göründükdən sonra düyməni vurun "OK".
- Alət, bütün cədvəl aralığını faiz dərəcəsi üçün müxtəlif seçimlərə uyğun dəyərlərlə hesablayır və doldurur. Kursoru bu cədvəl sahəsinin hər hansı bir elementinə yerləşdirsəniz, düsturlar çubuğunda ödənişin hesablanması üçün adi düsturu göstərilmədiyini, lakin keçilməz bir sıra üçün xüsusi bir düsturun olduğunu görə bilərsiniz. Yəni fərdi hücrələrdə dəyərləri dəyişdirmək indi mümkün deyil. Hesablama nəticələrini ayrıca deyil, hamısını birlikdə silə bilərsiniz.
Bundan əlavə, axtarış masasının tətbiqi nəticəsində əldə olunan illik 12.5% aylıq ödənişin funksiyanı tətbiq etməklə aldığımız eyni faiz miqdarı dəyərinə uyğun olduğunu görə bilərsiniz. PMT. Bu, hesablamanın düzgünlüyünü bir daha sübut edir.
Bu cədvəl sırasını təhlil edərək demək lazımdır ki, gördüyünüz kimi, yalnız illik 9.5% dərəcəsi ilə bizdə məqbul bir aylıq ödəniş səviyyəsi (29.000 rubldan az) olur.
Dərs: Excel-də annuitet ödənişinin hesablanması
Metod 2: aləti iki dəyişən ilə istifadə edin
Əlbətdə, hazırda 9.5% ilə kredit verən bankları tapmaq çox çətindir, əgər mümkün deyilsə. Buna görə, digər dəyişənlərin müxtəlif birləşmələri üçün aylıq ödənişin məqbul səviyyəsinə investisiya qoymaq üçün hansı variantların olduğunu görəcəyik: kredit orqanının ölçüsü və kredit müddəti. Bu vəziyyətdə faiz dərəcəsi dəyişməz qalacaq (12,5%). Bu problemi həll edərkən bir vasitə bizə kömək edəcəkdir. "Məlumat cədvəli" iki dəyişən istifadə edərək.
- Yeni bir masa sıra çəkirik. İndi sütun adlarında kredit müddəti göstəriləcəkdir 2 əvvəl 6 aylarla il artımı ilə) və sətirlərdə - kredit orqanının ölçüsü (dən) 850000 əvvəl 950000 rubl artımlarla 10000 rubl). Bu vəziyyətdə, bir şərt, hesablama düsturunun yerləşdiyi hüceyrədir (bizim vəziyyətimizdə) PMT) sətir və sütun adlarının sərhədində yerləşir. Bu şərt olmadan iki dəyişən istifadə edərkən alət işləməyəcəkdir.
- Sonra sütunların, satırların adları və düsturu olan bir hüceyrə də daxil olmaqla nəticələnən bütün masa aralığını seçin PMT. Nişana keçin "Məlumat". Əvvəlki dəfə olduğu kimi düyməni vurun "Təhlil olarsa", alətlər qrupunda "Məlumatla işləmə". Açılan siyahıdan seçin "Məlumat cədvəli ...".
- Alət pəncərəsi başlayır "Məlumat cədvəli". Bu vəziyyətdə hər iki sahəyə ehtiyacımız var. Sahədə İçindəki Sütun Dəyərlərini ilkin məlumatlarda kredit müddəti olan hücrənin koordinatlarını göstərin. Sahədə "Dəyərləri sıra ilə sıra ilə əvəz edin" kredit orqanının dəyərini ehtiva edən ilkin parametrlərin hücrəsinin ünvanını göstərin. Bütün məlumatlar daxil olduqdan sonra. Düyməni vurun "OK".
- Proqram hesablamanı həyata keçirir və cədvəl aralığını məlumatlarla doldurur. Sıralar və sütunların kəsişməsində, illik faizin müvafiq məbləği və göstərilən kredit müddəti ilə aylıq ödənişin dəqiq nə olacağını müşahidə etmək mümkündür.
- Gördüyünüz kimi çox dəyər var. Digər problemləri həll etmək üçün daha da çox ola bilər. Buna görə nəticələrin çıxmasını daha vizual hala gətirmək və verilmiş şəraitə uyğun olmayan dəyərləri dərhal müəyyənləşdirmək üçün vizual vasitələrdən istifadə edə bilərsiniz. Bizim vəziyyətimizdə bu şərti formatlaşdırma olacaq. Sətir və sütun başlıqları xaricində cədvəl aralığının bütün dəyərlərini seçirik.
- Nişana keçin "Ev" və simvolu vurun Şərti biçimlendirme. Alətlər blokunda yerləşir. Üslublar lentdə. Açılan menyuda seçin Hüceyrə seçimi qaydaları. Əlavə siyahıda mövqeyi vurun "Az ...".
- Bunun ardınca şərti formatlama parametrləri pəncərəsi açılır. Sol sahədə hüceyrələrin seçilməsindən daha az dəyəri göstərin. Xatırladığımız kimi, aylıq kredit ödənişinin az olacağı şərtindən məmnunuq 29000 rubl. Bu nömrəni daxil edirik. Doğru sahədə, vurğulamaq rəngini seçə bilərsiniz, baxmayaraq ki, onu standart olaraq tərk edə bilərsiniz. Bütün lazımi parametrlər daxil olduqdan sonra düyməni vurun "OK".
- Bundan sonra, dəyərləri yuxarıdakı vəziyyətə uyğun olan bütün hüceyrələr vurğulanacaqdır.
Cədvəl sırasını təhlil edərək bir sıra nəticələr çıxara bilərik. Gördüyünüz kimi, mövcud kredit müddəti (36 ay) ilə, aylıq ödənişin göstərilən məbləğinə investisiya qoymaq üçün, 860000.00 rubldan çox olmayan bir kredit götürməliyik, yəni əvvəlcədən planlaşdırıldığından 40.000 az.
Əgər hələ də 900.000 rubl kredit götürmək niyyətindəyiksə, onda kredit müddəti 4 il (48 ay) olmalıdır. Yalnız bu vəziyyətdə aylıq ödəniş 29.000 rubl müəyyən edilmiş həddi keçməyəcəkdir.
Beləliklə, bu cədvəl silsiləsindən istifadə edərək və hər seçimin müsbət və eksikliklərini təhlil edərək borcalan, mümkün olanlardan ən uyğun variantı seçərək, kredit şərtləri ilə bağlı müəyyən bir qərar verə bilər.
Əlbəttə ki, axtarış masası yalnız kredit seçimlərini hesablamaq üçün deyil, bir çox digər problemləri həll etmək üçün də istifadə edilə bilər.
Dərs: Excel-də şərti biçimləmə
Ümumiyyətlə, qeyd etmək lazımdır ki, axtarış masası müxtəlif dəyişənlərin birləşməsi üçün nəticəni müəyyənləşdirmək üçün çox faydalı və nisbətən sadə bir vasitədir. Eyni zamanda şərti biçimlendirme istifadə edərək əlavə olaraq alınan məlumatları görüntüləyə bilərsiniz.
