Ko'pincha, kirish ma'lumotlarining turli xil birikmalari uchun yakuniy natijani hisoblash kerak. Shunday qilib, foydalanuvchi harakatlarning barcha mumkin bo'lgan variantlarini baholashi mumkin, o'zaro ta'sirlar natijasini qondiradiganlarni tanlaydi va nihoyat, eng optimal variantni tanlaydi. Excelda bu vazifa uchun maxsus vosita - "Ma'lumotlar jadvali" ("Qidiruv stoli"). Yuqoridagi stsenariylarni bajarish uchun uni qanday ishlatishni bilib olaylik.
Shuningdek qarang: Excelda parametr tanlash
Ma'lumotlar jadvalidan foydalanish
Asbob "Ma'lumotlar jadvali" natijani bir yoki ikkita belgilangan o'zgaruvchining turli xilligi bilan hisoblash uchun mo'ljallangan. Hisoblashdan so'ng barcha mumkin bo'lgan variantlar faktor tahlili matrisi deyiladi jadval shaklida ko'rinadi. "Ma'lumotlar jadvali" bir guruh vosita degan ma'noni anglatadi "If-if" tahlil qilishyorliqda tasma ustida joylashtirilgan "Ma'lumot" blokida "Ma'lumotlarni ishlash". Excel 2007dan oldin, bu vosita nomga ega edi. "Qidiruv stoli"uning mohiyatini hozirgi nomidan ham aniqroq aks ettirgan.
Qidiruv jadvali ko'p holatlarda ishlatilishi mumkin. Misol uchun, odatda, bir variant - kreditlash muddati va kredit summasi, kreditlash muddati va foiz stavkasi bilan oylik kreditlar miqdorini hisoblashingiz kerak. Ushbu vosita investitsion loyiha modellarini tahlil qilishda ham qo'llanilishi mumkin.
Ammo, bu vositaning haddan tashqari ishlatilishi tizim tormozlashiga olib kelishi mumkinligini bilishingiz kerak, chunki ma'lumotlar doimiy ravishda qayta hisoblab chiqiladi. Shu sababli, ushbu vositani shu kabi muammolarni echish uchun kichik jadvallar majmualarida ishlatmaslik kerak, lekin to'ldirish belgisini ishlatib, formulalarni nusxalashni qo'llash tavsiya etiladi.
Tegishli ariza "Ma'lumotlar jadvallari" faqat katta jadvallar oralig'ida bo'lsa, formulalarni nusxalash ko'p vaqtni talab qilishi mumkin, va protsedurada o'zi xatolar ehtimolligi oshadi. Ammo, bu holatda ham tizimda keraksiz yuklanishni oldini olish uchun, qo'ng'iroq jadvalining oralig'ida formulalarni avtomatik qayta hisoblashni o'chirish tavsiya etiladi.
Ma'lumotlar jadvalining turli xil qo'llanilishi orasidagi asosiy farq hisob-kitobga kiritilgan o'zgaruvchining soni: bitta o'zgaruvchi yoki ikkita.
1-usul: asbobni bir o'zgaruvchidan foydalaning
Ma'lumotlar jadvalining bitta o'zgaruvchan qiymati bilan foydalanilganda darhol variantni ko'rib chiqaylik. Qarz berishning eng oddiy namunasini oling.
Shunday qilib, hozirgi vaqtda quyidagi kredit shartlari taklif etiladi:
- Kredit muddati - 3 yil (36 oy);
- Kredit miqdori - 900000 rubl;
- Foiz stavkasi - yillik 12,5%.
To'lovlar to'lov muddati (oyi) oxirida annuitet sxemasidan foydalanib, ya'ni teng miqdorda amalga oshiriladi. Shu bilan birga, barcha kredit muddatining boshida foizlar to'lovlari to'lovlarning katta qismini tashkil etadi, biroq, tananing kamayishi, foizlar pasayishi va tananing o'zini qoplash miqdori ortadi. Yuqorida aytib o'tilganidek, jami to'lov o'zgarishsiz qolmoqda.
Oylik to'lov miqdori, ya'ni kredit tashkilotining qaytarilishi va foizlarni to'lashni hisobga olgan holda hisoblash zarur. Buning uchun Excelda operator mavjud PMT.
PMT U bir qator moliyaviy funktsiyalarga mansub va uning vazifasi qarzdorning miqdori, kredit muddati va foiz stavkasi bo'yicha annuitet turidagi oylik kreditni to'lashni hisoblash hisoblanadi. Ushbu funktsiyaning sintaksisi quyidagicha.
= PMT (tezligi; nper; ps; bs; turi)
"Tikish" - Kredit to'lovlarining foiz stavkasini belgilovchi dalil. Indikator davr uchun o'rnatiladi. Bizning to'lov muddati - bir oy. Shuning uchun yiliga 12,5% yillik stavka yillardagi oylar soniga, ya'ni 12 ga teng bo'lishi kerak.
"Kper" - Kreditning barcha muddati uchun davrlarning sonini belgilaydigan dalil. Bizning misolimizda muddat bir oy, kredit muddati - 3 yil yoki 36 oy. Shunday qilib, davrlarning soni erta 36 bo'ladi.
"PS" - qarzning hozirgi qiymatini belgilaydigan dalil, ya'ni uni berish vaqtida kredit tashkilotining hajmi. Bizning holatimizda bu ko'rsatkich 900 ming rublni tashkil etadi.
"BS" - To'liq to'lash vaqtida kredit tashkilotining hajmini ko'rsatuvchi dalil. Tabiiyki, bu ko'rsatkich nolga teng bo'ladi. Ushbu dalillar ixtiyoriy. Agar siz uni o'tkazib yuborsangiz, u "0" raqamiga teng deb taxmin qilinadi.
"Toifa" - Shuningdek, ixtiyoriy dalil. U to'lov qachon amalga oshirilishi haqida ma'lumot beradi: davr boshida (parametr - "1") yoki davr oxirida (parametr - "0"). Eslatib o'tamiz, bizning to'lovimiz kalendar oyining oxirida, ya'ni bu bahsning qiymati teng bo'ladi "0". Biroq, bu ko'rsatkich majburiy emas va odatiy bo'lib, agar u foydalanilmasa, qiymat deb hisoblanadi "0", unda ko'rsatilgan misolda u umuman ishlatilmaydi.
- Shunday qilib, hisob-kitoblarga o'tamiz. Hisoblangan qiymat ko'rsatilgan sahifadagi varaqni tanlang. Biz tugmani bosamiz "Funktsiyani kiritish".
- Boshlanadi Funktsiya ustasi. Kategoriyaga o'tishni amalga oshiring "Moliyaviy", ro'yxatdan nomini tanlang "PLT" tugmasini bosing "OK".
- Shundan so'ng, yuqoridagi funktsiyaning argumentlari oynasining faollashuvi mavjud.
Kursorni maydonga qo'ying "Tikish"keyin yillik foiz stavkasi qiymati bilan varaqdagi kameraga bosing. Ko'rib turganingizdek, koordinatalari darhol maydonda ko'rsatiladi. Ammo, biz eslayotganimizdek, oylik ish haqi kerak, shuning uchun biz natijani 12 (/12).
Dalada "Kper" Xuddi shu tarzda, kredit muddati hujayralari koordinatalarini kiritamiz. Bunday holda, hech narsa ajratilmasligi kerak.
Dalada "Ps" kreditlash organining qiymatini o'z ichiga olgan hujayraning koordinatalarini belgilashingiz kerak. Buni qilamiz. Ko'rsatilgan koordinatalar oldida ham bir belgi qo'yamiz. "-". Bu nuqta funksiya PMT sukut bo'yicha, yakuniy xulosani salbiy belgisi bilan, oylik kreditni to'lashni hisobga olgan holda hisobga oladi. Ammo, aniqlik uchun, biz ma'lumot jadvalining ijobiy bo'lishi kerak. Shuning uchun, biz bir belgi qo'yamiz "minus" funktsional argumentlarning biridan oldin. Ma'lumki, ko'paytirish "minus" ochiq "minus" oxirida beradi ortiqcha.
Maydonlarda "Bs" va "Toifa" Biz ma'lumotlarni hech qachon kiritmaymiz. Biz tugmani bosamiz "OK".
- Shundan so'ng, operator hisoblab chiqadi va oldindan belgilangan hujayradagi umumiy oylik to'lov natijasini ko'rsatadi - 30108,26 rubl. Biroq, muammo qarzdorning oyiga maksimal 29 ming rublni to'lashga qodir ekanligi, ya'ni u bankka nisbatan foiz stavkasi kamroq bo'lgan shartlarni taklif qilishi yoki kredit byurosini qisqartirish yoki kredit muddatini uzaytirishi kerak. Harakat qilish uchun turli xil variantlarni hisoblab chiqing, bizga qo'ng'iroq jadvalini beradi.
- Boshlash uchun, qidiruv jadvalini bitta o'zgaruvchidan foydalaning. Keling, majburiy oylik to'lovning qiymati yillik stavka bo'yicha o'zgaruvchan farqlarga qanday o'zgarganini ko'rib chiqaylik 9,5% yillik va tugatish 12,5% qadam bilan pa 0,5%. Boshqa barcha sharoitlar o'zgarishsiz qolgan. Jadval oralig'ini chizish, ustunlar nomlari foiz stavkasining turli xilligiga mos kelishi mumkin. Ushbu yo'nalish bilan "Oylik to'lovlar" qoldiring. Uning birinchi hujayrasi avval biz hisoblagan formulani o'z ichiga olishi kerak. Qo'shimcha ma'lumot uchun qatorlarni qo'shishingiz mumkin "Umumiy kredit summasi" va "Umumiy foiz". Hisob-kitobning joylashgan ustuni sarlavhasiz bajariladi.
- Keyinchalik, hozirgi sharoitda kreditning umumiy miqdorini hisoblaymiz. Buni amalga oshirish uchun qatorning birinchi xonasini tanlang. "Umumiy kredit summasi" va hujayra tarkibini ko'paytiradi "Oylik to'lov" va "Kredit muddati". Keyin bu tugmani bosing Kirish.
- Hozirgi sharoitda foizlarning umumiy miqdorini hisoblash uchun biz shunga o'xshash tarzda kredit tashkilotining qiymatini kreditning umumiy miqdoridan chiqaramiz. Natijani ekranda ko'rsatish uchun tugmani bosing. Kirish. Shunday qilib, biz qarzni qaytarib berganda ortiqcha miqdorni olamiz.
- Endi asbobni qo'llash vaqti keldi. "Ma'lumotlar jadvali". Qator nomlari bundan mustasno, jadvalning butun qatorini tanlang. Keyin tabga o'ting "Ma'lumot". Ipdagi tugmani bosing "If-if" tahlil qilishbir qator vositalarga joylashtirilgan "Ma'lumotlarni ishlash" (Excel 2016 da, asboblar guruhi "Bashoratli"). Keyin kichik menyu ochiladi. Unda biz pozitsiyani tanlaymiz "Ma'lumotlar jadvali ...".
- Chaqirilayotgan kichik oyna ochiladi "Ma'lumotlar jadvali". Ko'rib turganingizdek, ikkita maydon bor. Bir o'zgaruvchimiz bilan ishlaganimiz uchun ulardan faqat bittasi kerak. O'zgaruvchan o'zgarishimiz ustunlarda sodir bo'lgani sababli, biz maydonni ishlatamiz "Sütunlardaki qiymatlarni o'rniga". Kursorni o'sha joyga joylashtiramiz va foizlarning joriy qiymatini o'z ichiga olgan boshlang'ich ma'lumotlar to'plamidagi kamerani bosing. Hujayra koordinatalari maydonda ko'rsatilgandan so'ng, tugmani bosing "OK".
- Ushbu vosita barcha jadvallar oralig'ini turli xil foiz stavkalari mos keladigan qiymatlar bilan hisoblab chiqadi va to'ldiradi. Kursorni ushbu jadvalning biron bir elementiga joylashtirsangiz, formulalar majmuasida oddiy to'lovni hisoblash formulasini emas, balki alohida bo'lmaydigan arrayning maxsus formulasini ko'rasiz. Ya'ni, alohida hujayralardagi qiymatlarni o'zgartirish mumkin emas. Hisoblash natijalarini yo'q qilish faqatgina birga bo'lishi mumkin, va alohida emas.
Bundan tashqari, hisoblash jadvalini qo'llash bilan olingan oylik to'lovning yiliga 12,5% qiymatida funktsiyani qo'llash natijasida olingan foiz stavkasi qiymatiga mos kelishini ta'kidlash mumkin PMT. Bu yana bir bor hisoblashning to'g'riligini isbotlaydi.
Ushbu jadval majmuasini tahlil qilgandan so'ng, biz ko'rib turganimizdek, yiliga 9,5% miqdorida, oylik to'lovlarning maqbul miqdori (29 ming rubldan kam) olinadi.
Kurs: Excelda rag'batlantiruvchi to'lovni hisoblash
2-usul: ikki o'zgaruvchiga ega vositadan foydalaning
Albatta, qarzni to'layotgan banklarni yiliga 9,5% evaziga topish juda qiyin. Shuning uchun keling, qanday variantlar mavjudligini ko'rib chiqamiz, ular boshqa o'zgaruvchilardan turli xil kombinatsiyalar uchun qabul qilinadigan oylik to'lov darajasiga: kredit muassasasining hajmi va kredit muddati. Shu bilan birga, foiz stavkasi o'zgarmaydi (12,5%). Ushbu vosita bizga yordam beradi. "Ma'lumotlar jadvali" ikki o'zgaruvchidan foydalanish.
- Yangi jadvallar qatorini chizish. Keling, kreditlar muddati ustun nomlari ( 2 qadar 6 yillar davomida bir yil mobaynida), va qatorlarda - kredit byurosi (dan 850000 qadar 950000 so'm miqdorida 10000 rubl). Bunday holda, hisoblash formulasi joylashgan hujayra (bizning holimizda PMT), satr va ustun nomlari chegarasida joylashgan. Ushbu shartsiz, vosita ikki o'zgaruvchidan foydalanishda ishlamaydi.
- Keyinchalik, natijada olingan barcha jadvallar oralig'ini, shu jumladan ustunlar, satrlar va formulaga ega bo'lgan hujayra nomlarini tanlang PMT. Yorliqqa o'ting "Ma'lumot". Oldingi vaqtdagi kabi, tugmani bosing. "If-if" tahlil qilishasboblar guruhida "Ma'lumotlarni ishlash". Ochilgan ro'yxatda elementni tanlang "Ma'lumotlar jadvali ...".
- Asbob oynasi boshlanadi. "Ma'lumotlar jadvali". Bunday holda ikkala sohaga ham ehtiyoj bor. Dalada "Sütunlardaki qiymatlarni o'rniga" biz asosiy ma'lumotlarda kredit muddatini o'z ichiga olgan hujayraning koordinatalarini aniqladik. Dalada "Satrlarga ko'ra qiymatlarni almashtirish" qarz tanasining qiymati bo'lgan dastlabki parametrlarning xujayrasining manzilini ko'rsatish. Barcha ma'lumotlar kiritilgandan so'ng. Biz tugmani bosamiz "OK".
- Dastur hisobni amalga oshiradi va jadval oralig'ini ma'lumotlar bilan to'ldiradi. Satrlar va ustunlar kesishmasida, aynan oylik to'lov miqdori mos ravishda yillik foiz va belgilangan kreditlash davri bilan qanchalik aniq bo'lishini kuzatish mumkin.
- Ko'rib turganingizdek, juda ko'p qadriyatlar. Boshqa muammolarni hal qilish uchun yana ko'p narsalar bo'lishi mumkin. Shuning uchun, natijalarni chiqishi yanada ingl. Qilish va qanday qiymatlarni ushbu shartni qondirmasligini darhol aniqlash uchun, siz ingl. Moslama vositalaridan foydalanishingiz mumkin. Bizning holatda shartli formatlash bo'ladi. Jadval oralig'idagi barcha qiymatlarni, satr va ustun sarlavhalarini tashqari tanlang.
- Yorliqqa o'tkazish "Bosh sahifa" va belgini bosing "Shartli formatlash". Asbob qutisida joylashgan. "Uslublar" kasetlarda. Ochilgan menyuda elementni tanlang "Hujayra tanlash qoidalari". Qo'shimcha ro'yxatda pozitsiyani bosing "Yana ...".
- Shundan so'ng, shartli formatlash sozlamalari oynasi ochiladi. Chap sohada xujayralar tanlanadiganidan kamroq qiymatni ko'rsatamiz. Biz eslayotganimizdek, kredit bo'yicha oylik to'lov kamroq bo'ladi 29000 rubl. Ushbu raqamni kiriting. To'g'ri maydonda tanlov rangini tanlash mumkin, garchi uni sukut bo'yicha qoldirish mumkin. Barcha zarur sozlamalar kiritilgandan so'ng, tugmani bosing. "OK".
- Shundan so'ng, qiymatlari yuqoridagi holatga mos keladigan barcha hujayralar rang bilan ajratiladi.
Jadval qatorini tahlil qilgandan so'ng, ba'zi xulosalar chiqarishingiz mumkin. Ko'rib turganingizdek, hozirgi kredit muddati (36 oy) bilan yuqorida ko'rsatilgan oylik to'lov miqdoriga mablag 'sarflash uchun 8,600,000,00 rubldan oshmaydigan kreditni olishimiz kerak.
Agar biz hali ham 900 ming rubl miqdorida kredit olishni rejalashtirmoqchi bo'lsak, kredit muddati 4 yil (48 oy) bo'lishi kerak. Faqat bu holatda, oylik to'lov miqdori belgilangan chegaradan 29000 rubldan oshmaydi.
Shunday qilib, ushbu jadvallar qatoridan foydalanib va har bir variantning afzalliklari va kamchiliklarini tahlil qilsak, qarz oluvchi qarz berish shartlari bo'yicha aniq qarorlar qabul qilib, uning ehtiyojlariga mos keladigan variantni tanlashi mumkin.
Albatta, qo'ng'iroqlar jadvali kredit variantlarini hisoblash uchun emas, balki boshqa ko'pgina muammolarni ham hal qilish uchun ishlatilishi mumkin.
Kurs: Excelda shartli formatlash
Umuman olganda, qidirish jadvalining turli xil kombinatsiyalar natijalarini aniqlash uchun juda foydali va nisbatan oddiy vosita ekanligini ta'kidlash kerak. Shartli formatlashni qo'llash bilan birga, siz olingan ma'lumotni ham ko'rishingiz mumkin.
