Çox vaxt, giriş məlumatlarının müxtəlif kombinasiyaları üçün yekun nəticəni hesablamaq lazımdır. Beləliklə, istifadəçi hərəkət üçün mümkün olan bütün variantları qiymətləndirə, qarşılıqlı təsir nəticəsində ona cavab verənləri seçə və nəhayət, ən optimal seçimi seçə biləcək. Excel-də bu tapşırıq üçün xüsusi bir vasitə var - "Məlumat Cədvəli" ("Axtarış masası"). Yuxarıda göstərilən ssenarilərin yerinə yetirilməsi üçün necə istifadə edəcəyini öyrənək.
Həmçinin baxın: Exceldə parametr seçimi
Məlumatların cədvəlindən istifadə
Tool "Məlumat Cədvəli" bir və ya iki təyin edilmiş dəyişənin fərqli varyasyonları ilə nəticə hesablamaq üçün nəzərdə tutulmuşdur. Hesablamadan sonra mümkün olan bütün variantlar faktor təhlili matrisi adlanan bir masa şəklində görünəcəkdir. "Məlumat Cədvəli" bir qrup vasitəyə aiddir "Nədir" təhlilinişanın üzərindəki lentə yerləşdirilir "Məlumat" blokda "Məlumatlarla işləmə". Excel 2007-ə qədər bu alət bir ad idi. "Axtarış masası"cari addan daha mahiyyətini əks etdirir.
Axtarış masası bir çox hallarda istifadə edilə bilər. Məsələn, tipik bir seçim, kreditin müddətinin müxtəlif variantları və kredit məbləğinin, yaxud kreditləşmə dövrü və faiz dərəcəsi ilə aylıq kredit ödənişinin məbləğini hesablamaq lazımdır. Bu alət həmçinin investisiya layihələri modellərini təhlil edərkən də istifadə edilə bilər.
Lakin bu alətdən həddindən artıq istifadənin sistem frenlemesine gətirib çıxara biləcəyini bilmək lazımdır, çünki məlumat daim yenidən hesablanır. Buna görə də, bu aləti oxşar problemləri həll etmək üçün kiçik sütun arralarında istifadə etmir, lakin doldurma markerini istifadə edərək formulaların kopyalanmasını tətbiq etmək məsləhətdir.
Təqdim edilmiş ərizə "Data Masaları" yalnız böyük bir cədvəl aralığına malikdir, formulun kopyalanması çox vaxt keçirə bilər, prosedurun özü isə səhvlərin ehtimalı artır. Amma bu vəziyyətdə belə sistemdə lazımsız yükün qarşısını almaq üçün, axtarış masasının aralığında avtomatik olaraq yenidən hesablanmasını tövsiyə etmək məsləhətdir.
Bir verilənlər cədvəlinin müxtəlif 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
Bir verilənlər cədvəli bir dəyişən dəyərlə istifadə edildikdə dərhal seçimi nəzərdən keçirək. Borc vermənin ən tipik nümunəsini götürün.
Beləliklə, hazırda biz aşağıdakı kredit şərtləri təklif edirik:
- Kredit müddəti - 3 il (36 ay);
- Kreditin məbləği - 900000 rubl;
- Faiz dərəcəsi - illik 12.5%.
Ödəmələr, ödəniş dövrünün (ayı) sonunda annuitet sxemi, yəni bərabər paylarla istifadə edilir. Eyni zamanda, bütün kredit dövrünün başlanğıcında faiz ödənişləri ödənişlərin əhəmiyyətli bir hissəsini təşkil edir, lakin bədənin azaldılması, faiz ödənişlərinin azaldılması və bədənin özünün geri qaytarılma məbləği artır. Yuxarıda göstərildiyi kimi, ümumi ödəniş dəyişməz olaraq qalır.
Kredit təşkilatının ödənişini və faiz ödənişlərini ehtiva edən aylıq ödənişin nə olacağını hesablamaq lazımdır. Bunun üçün, Excel operatoru var PMT.
PMT Bu, bir sıra maliyyə funksiyalarına aiddir və onun vəzifəsi kredit təşkilatının məbləğinə, kredit müddəti və faiz dərəcəsinə əsasən annuitet növünün aylıq kredit ödəməsini hesablamaqdır. Bu funksiyanın sintaksisi aşağıdakı kimidir.
= PMT (dərəcəsi; nper; ps; bs; növü)
"Bahis" - Kredit ödənişlərinin faiz dərəcəsini müəyyən edən arqument. Göstərici dövr üçün müəyyən edilir. Bizim ödəmə müddəti bir aydır. Buna görə, 12.5% illik dərəcəsi bir ildə ayın sayına bölünməlidir, yəni 12.
"Kper" - Kreditin bütün müddəti üçün müddətlərin sayını müəyyən edən arqument. Məsələn, dövr bir aydır və kredit müddəti 3 il, ya da 36 aydır. Beləliklə, dövrlərin sayı erkən 36 olacaq.
"PS" - kreditin indiki dəyərini müəyyən edən arqument, yəni onun verilməsi zamanı kredit orqanının ölçüsü. Bizim vəziyyətimizdə bu rəqəm 900 min rubldür.
"BS" - tam ödəmə müddətində kredit orqanının ölçüsünü göstərən arqument. Təbii ki, bu göstərici sıfıra bərabər olacaq. Bu arqument isteğe bağlıdır. Əgər onu atlasanız, bu "0" sayına bərabərdir.
"Tipi" - həmçinin isteğe bağlı arqument. O, ödənişin ediləcəyi barədə məlumat verir: dövrün əvvəlində (parametr - "1") və ya dövrün sonunda (parametr - "0"). Xatırladığımız kimi, ödəniş təqvim ayının sonunda, yəni bu arqumentin dəyəri bərabər olacaq "0". Lakin, bu göstəricinin məcburi olmadığını nəzərə alsaq, və istifadə edilmədiyi təqdirdə ucaldılmaqla dəyəri dəyərləndirilir "0"sonra göstərilən nümunədə istifadə edilə bilməz.
- Beləliklə, hesablamaya davam edirik. Hesablanmış dəyərin göstəriləcəyi hesabatındakı bölməni seçin. Düyməni tıkladık "Əlavə funksiyası".
- Başlayır Function Sihirbazı. Kataloqa keçid edin "Maliyyə", siyahıdan adı seçin "PLT" və düyməni basın "OK".
- Bundan sonra yuxarıda göstərilən funksiyanın argumentləri pəncərəsinin aktivləşdirilməsi var.
Kursoru sahəyə qoyun "Bahis"sonra illik faiz dərəcəsi ilə hesabatdakı hüceyrəni basın. Gördüyünüz kimi, koordinatları dərhal sahəsində göstərilir. Ancaq xatırladığımız kimi, aylıq dərəcəyə ehtiyacımız var və buna görə də nəticəni 12/12).
Sahədə "Kper" Eyni şəkildə, kredit müddətli hüceyrələrin koordinatlarını da daxil edirik. Bu halda heç bir şey bölünməməlidir.
Sahədə "Ps" kredit bürosunun dəyərini ehtiva edən hüceyrənin koordinatlarını təyin etməlisiniz. Bunu edirik. Göstərilən koordinatların qarşısında da bir əlamət qoyduq. "-". İşarə funksiyasıdır PMT default olaraq, yekun nəticəni aylıq kredit ödənişini itirən ədalətli bir mənfi işarə ilə verir. Ancaq aydınlıq üçün, məlumatların cədvəlinə müsbət olması lazımdır. Buna görə də bir işarə qoyduq "mənfi" funksiya argümanlarından birinə qədər. Məlum olduğu kimi, çarpma "mənfi" on "mənfi" nəticədə verir üstəgəl.
Sahələrdə "Bs" və "Tipi" Biz məlumatları heç girmirik. Düyməni tıkladık "OK".
- Bundan sonra operator əvvəlcədən təyin edilmiş hüceyrədə hesablayır və göstərir ki, ümumi aylıq ödənişin nəticəsi - 30108,26 rubl. Ancaq problem borcalanın ayda maksimum 29,000 rubl ödəməsi, yəni bankın daha aşağı faiz dərəcəsi olan şərtləri təklif etməsi və ya kredit təşkilatının azaldılması və ya kredit müddəti uzadılmasıdır. Hərəkət üçün müxtəlif variantları hesablayın, bizə axtarış masasına kömək edəcək.
- Başlamaq üçün, bir dəyişən ilə axtarış masa istifadə edin. Dəyişən məcburi aylıq ödənişin dəyərinin illik dərəcədə fərqli dəyişikliklərlə necə dəyişdiyini görək 9,5% illik və bitmə 12,5% addım ilə pa 0,5%. Bütün digər şərtlər dəyişməz qalır. Bir masa aralığını çəkin, sütunların adları faiz dərəcəsinin müxtəlif dəyişikliklərinə cavab verəcəkdir. Bu xəttlə "Aylıq ödənişlər" olduğu kimi buraxın. Onun ilk hüceyrəsində daha əvvəl hesabladığımız formula də olmalıdır. Daha ətraflı məlumat üçün xətləri əlavə edə bilərsiniz "Ümumi kredit məbləği" və "Ümumi faiz". Hesablamanın yerləşdiyi sütun başlıqsız həyata keçirilir.
- Bundan sonra, mövcud şərtlər altında kreditin ümumi məbləğini hesablayırıq. Bunu etmək üçün, satırın ilk hüceyrəsini seçin. "Ümumi kredit məbləği" və hüceyrənin məzmununu çoxaltmaq "Aylıq ödəniş" və "Kredit müddəti". Bu klikdən sonra Daxil edin.
- Mövcud şəraitdə faizlərin ümumi məbləğini hesablamaq üçün oxşar şəkildə kredit təşkilatının dəyərini kreditin ümumi məbləğindən çıxarırıq. Nəticəni ekranda göstərmək üçün düyməni basın. Daxil edin. Beləliklə, biz krediti qaytararkən artıq ödəmədiyimiz məbləği alırıq.
- İndi aləti tətbiq etmək vaxtı gəldi. "Məlumat Cədvəli". Satır adları istisna olmaqla, bütün masa arrayini seçin. Bundan sonra sekməyə keçin "Məlumat". Şerid üzərində düyməni basın "Nədir" təhlilibir qrup vasitəyə yerləşdirilir "Məlumatlarla işləmə" (Excel 2016'da, bir qrup vasitədir "Proqnoz"). Sonra kiçik bir menyu açılır. Burada biz mövqeyi seçirik "Məlumat Cədvəli ...".
- Çağırılan kiçik bir pəncərə açılır "Məlumat Cədvəli". Gördüyünüz kimi, iki sahə var. Bir dəyişənlə işlədiyimiz üçün onlardan yalnız birinə ehtiyacımız var. Dəyişən dəyişikliklərimiz sütunlarda olduğundan, sahəni istifadə edəcəyik "Sütunlardakı əvəz dəyərləri". Kursoru orada yerləşdirdik və faizin cari dəyərini ehtiva edən ilkin məlumatlar dəstində hüceyrəni basın. Hüceyrənin koordinatları sahəsində göstərildikdən sonra düyməni basın "OK".
- Alət bütün masa aralığını müxtəlif faiz dərəcələri seçimlərinə uyğun dəyərlər hesablayır və doldurur. İmleci bu masanın hər hansı bir elementinə yerləşdirirsinizsə, formula barında müntəzəm ödəniş hesablama formulu deyil, qeyri-pozulmayan bir sıra xüsusi forması göstərilir. Yəni fərdi hüceyrələrdə dəyərləri dəyişdirmək artıq mümkün deyil. Hesablama nəticələrini silmək yalnız bir-birilə deyil, hamısı ola bilər.
Əlavə olaraq qeyd etmək olar ki, axtarış masasını tətbiq etməklə əldə olunan illik 12.5% aylıq ödənişin dəyəri funksiyanı tətbiq edərək aldığımız eyni faiz dərəcəsinə uyğun gəlir PMT. Bu bir daha hesablama düzgünlüyünü sübut edir.
Bu cədvəlli array analiz edildikdən sonra, görüldüyü kimi, yalnız illik 9.5% dərəcəsi ilə qəbul edilən aylıq ödəniş səviyyəsi (29 min rubldan az) əldə edilir.
Dərs: Excel-də annuitet ödənişinin hesablanması
Metod 2: iki dəyişən ilə bir vasitə istifadə edin
Əlbəttə ki, bütün hallarda, kreditlər verən bankları ildə 9,5% səviyyəsində tapmaq olduqca çətindir. Buna görə, hər hansı digər dəyişənlərin müxtəlif birləşmələri üçün aylıq ödənişlərin məqbul səviyyəsinə investisiya etmək üçün hansı variantları nəzərdən keçirək: kredit təşkilatının həcmi və kredit müddəti. Eyni zamanda, faiz dərəcəsi dəyişməz olaraq qalacaq (12,5%). Vasitə bu işi bizə kömək edəcək. "Məlumat Cədvəli" iki dəyişəndən istifadə edir.
- Yeni bir masa dizisini çəkin. İndi kredit müddəti sütun adlarında göstəriləcəkdir 2 qədər 6 bir il müddətində aylar) və satırlarda - kredit təşkilatının həcmi ( 850000 qədər 950000 artımlarla rubl 10000 rubl). Bu halda hesablama formulunun yerləşdiyi hüceyrənin (bizim vəziyyətimizdə) vacib olması vacibdir PMT), satır və sütun adlarının sərhədində yerləşir. Bu şərt olmadan, iki dəyişəndən istifadə edərkən alət işləməyəcəkdir.
- Sonra sütunların adlarını, satırlarını və formülü olan hüceyrəni daxil olmaqla, bütün yaranan masa aralığını seçin PMT. Nişana keçin "Məlumat". Əvvəlki vaxtdakı kimi, düyməni basın. "Nədir" təhlilibir qrup alətdə "Məlumatlarla işləmə". Açılan siyahıda maddəni 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ə "Sütunlardakı əvəz dəyərləri" birincil məlumatlarda kredit müddəti olan hüceyrənin koordinatlarını təyin edirik. Sahədə "Satırlara görə dəyişən dəyərləri" Kreditin bədəninin dəyərini əks etdirən ilk parametrlər hüceyrəsinin ünvanını müəyyənləşdirin Bütün məlumatlar girildikdən sonra. Düyməni tıkladık "OK".
- Proqram hesablamanı yerinə yetirir və verilənlər bazası ilə masa aralığını doldurur. Satırlar və sütunların kəsişməsində, aylıq ödənişin müvafiq faiz dərəcəsi və dəqiqləşdirilmiş kreditləşmə dövrü ilə tam olaraq necə olacağını müşahidə etmək mümkündür.
- Gördüyünüz kimi, çox dəyərlər. Digər problemləri həll etmək üçün daha çox ola bilər. Buna görə nəticələrin daha çox görselleştirilmesini və hansı dəyərlərin müəyyən şərtlərə uyğun gəlmədiyini dərhal müəyyən etmək üçün, görselleştirme vasitələrindən istifadə edə bilərsiniz. Bizim vəziyyətimiz şərti formatlaşdırılacaq. Satır və sütun başlıqlarını istisna olmaqla, masa aralığının bütün dəyərlərini seçin.
- Nişana keçin "Ev" və simgesini basın "Şərti formatlaşdırma". Toolbox-da yerləşdirilib. "Styles" lentdə. Açılan menyuda maddə seçin "Hüceyrə seçim qaydaları". Əlavə siyahıda mövqeyini basın "Az ...".
- Bundan sonra şərti formatlama qəbulu pəncərəsi açılır. Sol sahədə biz hüceyrələrin seçilməsindən az olan dəyəri göstəririk. Xatırladığımız kimi, biz kreditin aylıq ödənişinin az olacağı şərtlərdən razıyıq 29000 rubl. Bu nömrəni daxil edin. Sağ sahəyə seçki rəngini seçmək mümkündür, baxmayaraq ki, onu default olaraq tərk edə bilərsiniz. Lazım olan bütün parametrlər girildikdən sonra düyməni basın. "OK".
- Bundan sonra, qiymətləri yuxarıdakı vəziyyətə uyğun olan bütün hüceyrələr rənglərlə vurğulanacaq.
Masa arrayini təhlil etdikdən sonra bəzi nəticələr əldə edə bilərsiniz. Gördüyünüz kimi, kreditin müddətini (36 ay) yuxarıda göstərilən aylıq ödənişlərə sərf etmək üçün biz 8,600,000,00 rubldan çox olmayan kredit götürməliyik, yəni ilk olaraq planlaşdırılandan 40 min az.
Əgər hələ də 900 min rubl məbləğində kredit almaq niyyətindəyiksə, kreditin müddəti 4 il (48 ay) olmalıdır. Yalnız bu halda, aylıq ödənişin məbləği müəyyən edilmiş 29,000 rubl limitdən artıq olmayacaqdır.
Beləliklə, bu cədvəlli serialdan faydalanaraq hər bir seçimin mənfəətini və mənfi cəhətlərini təhlil edərək borcalan kreditin şərtləri ilə bağlı xüsusi qərar qəbul edə bilər, onun ehtiyaclarına ən uyğun şəkildə seçim edir.
Əlbəttə, axtarış masası yalnız kredit variantlarını hesablamaq üçün deyil, bir çox digər problemləri də həll etmək üçün istifadə edilə bilər.
Dərs: Excel-də şərti formatlaşdırma
Ümumiyyətlə, axtarış masası dəyişənlərin müxtəlif kombinasiyalarının nəticəsini təyin etmək üçün çox faydalı və nisbətən sadə bir vasitədir. Bununla birlikdə şərti formatlaşdırma tətbiq edərək, alınan məlumatları görselleştirə bilərsiniz.
