Microsoft Excel-dagi ma'lumotlar jadvali

Ko'pincha, siz turli xil kirish ma'lumotlari kombinatsiyasi uchun yakuniy natijani hisoblashingiz kerak. Shunday qilib, foydalanuvchi xatti-harakatlarning barcha mumkin bo'lgan variantlarini baholay oladi, o'zaro ta'sir natijalari uni qondiradiganlarni tanlaydi va nihoyat eng maqbul variantni tanlaydi. Ushbu vazifani bajarish uchun Excel-da maxsus vosita mavjud - "Ma'lumotlar jadvali" (Almashtirish jadvali) Yuqoridagi stsenariylarni bajarish uchun uni qanday ishlatishni bilib olaylik.

Shuningdek o'qing: Excelda parametrlarni tanlash

Ma'lumotlar jadvalidan foydalanish

Asbob "Ma'lumotlar jadvali" Bir yoki ikkita aniqlangan o'zgaruvchilarning turli xil o'zgarishlari uchun natijani hisoblash uchun mo'ljallangan. Hisoblashdan keyin barcha mumkin bo'lgan variantlar jadval ko'rinishida paydo bo'ladi, bu omil tahlilining matritsasi deb ataladi. "Ma'lumotlar jadvali" vositalar guruhiga tegishli "Agar tahlil qilsangiz nima bo'ladi?", yorliqdagi lentaga joylashtirilgan "Ma'lumotlar" blokda "Ma'lumotlar bilan ishlash". Excel 2007-dan oldin ushbu vosita chaqirilgan Almashtirish jadvali, bu hozirgi nomdan ham mohiyatini aniqroq aks ettirdi.

Ko'rish jadvalidan ko'p hollarda foydalanish mumkin. Masalan, odatiy variant - bu kredit berish davri va kredit summasi yoki kredit berish davri va foiz stavkalari uchun oylik kredit to'lovi miqdorini hisoblash kerak bo'lganda. Shuningdek, ushbu vositadan investitsiya loyihalari modellarini tahlil qilishda foydalanish mumkin.

Ammo, shuningdek, ushbu vositadan haddan tashqari foydalanish tizimning tormozlanishiga olib kelishi mumkinligini bilishingiz kerak, chunki ma'lumotlar doimiy ravishda qayta sanab chiqiladi. Shu sababli, ushbu jadvalni ishlatishda emas, balki to'ldirish markeridan foydalanib formuladan nusxa olishda o'xshash muammolarni hal qilish uchun kichik jadval massivlarida tavsiya etiladi.

Asosli ariza "Ma'lumotlar jadvallari" Formulalarni nusxalash ko'p vaqtni talab qilishi mumkin va protsedura davomida xato qilish ehtimoli oshadi, faqat katta jadvallar oralig'ida bo'ladi. Ammo bu holda, tizimga ortiqcha yuk tushmasligi uchun formulalarni almashtirish jadvali oralig'ida avtomatik ravishda qayta hisoblashni o'chirish tavsiya etiladi.

Ma'lumotlar jadvalidan har xil foydalanish o'rtasidagi asosiy farq bu hisoblashda qatnashadigan o'zgaruvchilar soni: bitta o'zgaruvchi yoki ikkita.

1-usul: bitta o'zgaruvchili vositadan foydalaning

Ma'lumotlar jadvali bitta o'zgaruvchan qiymatdan foydalanilganda darhol variantni ko'rib chiqamiz. Eng tipik qarz berish misolini oling.

Shunday qilib, hozirgi vaqtda sizga quyidagi kredit shartlari taklif etiladi:

• Kredit muddati - 3 yil (36 oy);
• Kredit summasi - 900 000 rubl;
• Foiz stavkasi - yillik 12,5%.

To'lovlar to'lov davri (oy) oxirida, annuitet sxemasiga muvofiq amalga oshiriladi, ya'ni teng ulushlarda. Shu bilan birga, butun kredit muddati boshida to'lovlarning sezilarli qismi foizlar bo'yicha to'lovlardir, ammo tananing qisqarishi bilan foiz to'lovlari kamayadi va tananing to'lash hajmi ham oshadi. Umumiy to'lov, yuqorida aytib o'tilganidek, o'zgarishsiz qoladi.

Oylik to'lov miqdorini, shu jumladan kredit organini to'lash va foizlarni to'lashni hisoblash kerak. Buning uchun Excel-da operator mavjud PMT.

PMT moliyaviy funktsiyalar guruhiga tegishli bo'lib, uning vazifasi yillik qarz miqdorini, kredit berish muddati va foiz stavkasi asosida yillik yillik yillik to'lovni hisoblash hisoblanadi. Ushbu funktsiya sintaksisi sifatida taqdim etiladi

= PLT (darajasi; nper; ps; bs; turi)

Taklif - kredit to'lovlarining foiz stavkasini belgilaydigan dalil. Ko'rsatkich davr uchun belgilanadi. To'lov muddati bir oyga teng. Shuning uchun yillik 12,5 foiz stavkasini yil ichidagi oylar soniga, ya'ni 12 ga bo'lish kerak.

"Nper" - butun kredit muddati uchun davrlar sonini aniqlaydigan argument. Bizning misolimizda, muddat bir oy, kredit muddati - 3 yil yoki 36 oy. Shunday qilib, davrlar soni 36 ga erta bo'ladi.

"PS" - kreditning hozirgi qiymatini aniqlaydigan dalil, ya'ni u kredit berish paytidagi ssudaning hajmidir. Bizning holatlarimizda bu raqam 900 000 rublni tashkil qiladi.

"BS" - to'liq to'lash paytidagi ssudaning hajmini ko'rsatuvchi dalil. Tabiiyki, bu ko'rsatkich nolga teng bo'ladi. Ushbu dalil ixtiyoriydir. Agar siz uni o'tkazib yuborsangiz, u "0" raqamiga teng deb taxmin qilinadi.

"Turi" - shuningdek ixtiyoriy dalil. U to'lov qachon aniq amalga oshirilishini ma'lum qiladi: davr boshida (parametr - "1") yoki davr oxirida (parametr - "0") Esimizda bo'lsa, bizning to'lovimiz kalendar oyining oxirida amalga oshiriladi, ya'ni ushbu dalilning qiymati teng bo'ladi "0". Ammo, bu ko'rsatkich majburiy emasligini va agar ishlatilmasa, qiymat teng bo'lishi kerakligini hisobga olsak "0", keyin ko'rsatilgan misolda uni butunlay tark etish mumkin.

1. Shunday qilib, biz hisob-kitobga o'tamiz. Hisoblangan qiymat ko'rsatiladigan varaqdagi uyani tanlang. Tugmani bosing "Qo'shish funktsiyasi".



2. Boshlaydi Xususiyat ustasi. Biz toifaga o'tamiz "Moliyaviy", ro'yxatdan ismni tanlang "PLT" va tugmachani bosing "OK".



3. Shundan so'ng, yuqoridagi funktsiyaning argumentlari oynasi faollashadi.

   Kursorni maydonga qo'ying Taklif, shundan so'ng biz yillik foiz stavkasi qiymati bilan varaqdagi katakchani bosamiz. Ko'rib turganingizdek, uning koordinatalari darhol maydonda ko'rsatiladi. Ammo, esimizda bo'lsa, bizga oylik stavka kerak va shuning uchun biz natijani 12 ga bo'lamiz (/12).

   Dalada "Nper" xuddi shu tarzda, biz kredit muddati kataklarining koordinatalarini kiritamiz. Bunday holda, siz hech narsa bilan bo'lishishingiz shart emas.

   Dalada Ps kredit tanasi qiymatini o'z ichiga olgan katakning koordinatalarini ko'rsatishingiz kerak. Biz buni qilamiz. Ko'rsatilgan koordinatalar oldida biz ham belgi qo'yamiz "-". Gap shundaki, funktsiya PMT sukut bo'yicha oylik ssudani to'lash yo'qolishini hisobga olgan holda salbiy belgi bilan yakuniy natijani beradi. Ammo ma'lumotlar jadvalining aniqligi uchun biz ushbu raqamning ijobiy bo'lishiga muhtojmiz. Shuning uchun biz belgi qo'yamiz minus funktsiya argumentlaridan biri oldida. Ko'paytirish ma'lum minus yoqilgan minus oxirida beradi ortiqcha.

   Dalalarga "Bs" va "Turi" ma'lumotlar umuman kiritilmaydi. Tugmani bosing "OK".



4. Shundan so'ng, operator oldindan belgilangan katakchadagi oylik to'lovning natijasini hisoblab chiqadi va namoyish etadi - 30108,26 rubl. Ammo muammo shundaki, qarz oluvchi oyiga eng ko'pi bilan 29000 rubl to'lashi mumkin, ya'ni u eng past foiz stavkasi bilan shartlarni taklif qiladigan bankni topishi yoki kredit organini qisqartirishi yoki kredit muddatini ko'paytirishi kerak. Qidiruv jadvali turli xil variantlarni aniqlashga yordam beradi.



5. Birinchidan, bitta o'zgaruvchiga ega jadvaldan foydalaning. Keling, majburiy oylik to'lovning miqdori yillik stavkaning turli tafovutlari bilan qanday o'zgarishini ko'rib chiqaylik 9,5% yiliga va oxiri 12,5% yillik o'sish bilan 0,5%. Boshqa barcha shartlar o'zgarishsiz qoldirilgan. Biz jadvallar qatorini chizamiz, ularning nomlari foiz stavkalarining turli xil o'zgarishlariga mos keladi. Ushbu chiziq bilan "Oylik to'lovlar" bo'lgani kabi qoldiring. Uning birinchi hujayrasida biz ilgari hisoblagan formulani o'z ichiga olishi kerak. Qo'shimcha ma'lumot olish uchun siz chiziqlar qo'shishingiz mumkin "Umumiy kredit summasi" va "Umumiy foizlar". Hisoblash joylashgan ustun sarlavhasiz amalga oshiriladi.



6. Keyingi, hozirgi sharoitda biz kreditning umumiy miqdorini hisoblaymiz. Buning uchun qatorning birinchi katakchasini tanlang "Umumiy kredit summasi" va hujayralar tarkibini ko'paytiring "Oylik to'lov" va "Kredit muddati". Shundan so'ng, tugmachani bosing Kirish.



7. Hozirgi sharoitda foizlarning umumiy miqdorini hisoblash uchun biz shunga o'xshash tarzda kreditning umumiy miqdoridan kredit tanasi miqdorini olamiz. Natijani ekranda ko'rsatish uchun tugmani bosing Kirish. Shunday qilib, biz kreditni to'lashda ortiqcha miqdorni olamiz.



8. Endi vositani qo'llash vaqti keldi "Ma'lumotlar jadvali". Qator nomlaridan tashqari biz butun jadval massivini tanlaymiz. Shundan so'ng, yorliqqa o'ting "Ma'lumotlar". Ipdagi tugmachani bosing "Agar tahlil qilsangiz nima bo'ladi?"asboblar guruhida joylashgan "Ma'lumotlar bilan ishlash" (Excel 2016 da bir guruh vositalar "Prognoz") Keyin kichik menyu ochiladi. Unda biz pozitsiyani tanlaymiz "Ma'lumotlar jadvali ...".



9. Kichkina oyna ochiladi, u deyiladi "Ma'lumotlar jadvali". Ko'rib turganingizdek, u ikkita maydonga ega. Bitta o'zgaruvchi bilan ishlaganimiz uchun bizga ulardan bittasi kerak. O'zgaruvchan ustunni ustunga o'zgartirganimiz sababli biz maydondan foydalanamiz Ichidagi ustun qiymatlarini almashtiring. U erda kursorni o'rnating va keyin joriy foizni o'z ichiga olgan ma'lumotlar bazasidagi uyani bosing. Maydonda hujayra koordinatalari ko'rsatilgandan so'ng tugmani bosing "OK".



10. Asbob barcha jadval jadvalini foiz stavkasi uchun turli xil variantlarga mos keladigan qiymatlar bilan hisoblaydi va to'ldiradi. Agar siz kursorni ushbu jadval maydonining istalgan elementiga qo'ysangiz, formulalar satrida to'lovni hisoblash uchun odatiy formulani ko'rsatmasligini, ammo ajratib bo'lmaydigan qator uchun maxsus formulani ko'rishingiz mumkin. Ya'ni, endi alohida hujayralardagi qiymatlarni o'zgartirish mumkin emas. Siz hisoblash natijalarini alohida-alohida emas, barchasini yo'q qilishingiz mumkin.

Bundan tashqari, qidirish jadvalini qo'llash natijasida olingan yillik 12,5% miqdoridagi oylik to'lov ushbu funktsiyani qo'llash orqali biz olgan foizlarning o'sha miqdoriga to'g'ri kelishini ko'rishingiz mumkin. PMT. Bu yana bir bor hisob-kitoblarning to'g'riligini tasdiqlaydi.

Ushbu jadval qatorini tahlil qilgandan so'ng shuni ta'kidlash kerakki, ko'rib turganingizdek, yiliga atigi 9,5% miqdorida biz oylik to'lovning maqbul darajasini olamiz (29000 rubldan kam).

Dars: Excel-da annuitet to'lovini hisoblash

2-usul: vositani ikkita o'zgaruvchidan foydalaning

Albatta, hozirgi paytda yiliga 9,5 foizli kredit beradigan banklarni topish juda qiyin, agar iloji bo'lmasa. Shuning uchun, boshqa parametrlarning turli xil kombinatsiyalari uchun oylik to'lovning maqbul darajasiga sarmoya kiritish uchun qanday imkoniyatlar mavjudligini ko'rib chiqamiz: ssudaning hajmi va kredit muddati. Bu holda foiz stavkasi o'zgarishsiz qoladi (12,5%). Ushbu muammoni hal qilishda sizga bir vosita yordam beradi. "Ma'lumotlar jadvali" ikkita o'zgaruvchini ishlatib.

1. Biz yangi jadval massivini chizamiz. Endi ustun nomlarida kredit muddati ko'rsatilgan (dan boshlab) 2 oldin 6 Yillar oylar bilan bir yil o'sishiga qarab) va satrlarda - kredit organining miqdori (dan.) 850000 oldin 950000 rubl miqdorida 10000 rubl). Bunday holda, zaruriy shart - bu hisoblash formulasi joylashgan katak (bizning holatda) PMT), satr va ustun nomlari chegarasida joylashgan. Ushbu shartsiz, ikkita o'zgaruvchini ishlatganda vosita ishlamaydi.



2. Keyin barcha natijalar jadval oralig'ini tanlang, shu jumladan ustunlar, satrlar nomlari va formulali uyani tanlang PMT. Yorliqqa o'ting "Ma'lumotlar". Avvalgi vaqt kabi, tugmachani bosing "Agar tahlil qilsangiz nima bo'ladi?", asboblar guruhida "Ma'lumotlar bilan ishlash". Ochilgan ro'yxatda tanlang "Ma'lumotlar jadvali ...".



3. Asboblar oynasi boshlanadi "Ma'lumotlar jadvali". Bunday holda, ikkala maydon ham kerak. Dalada Ichidagi ustun qiymatlarini almashtiring dastlabki ma'lumotlarda kredit muddatini o'z ichiga olgan katakning koordinatalarini ko'rsating. Dalada "Qatorlarni qatorga qator bilan almashtirish" Kredit tanasi qiymatini o'z ichiga olgan dastlabki parametrlar katakchasining manzilini ko'rsating. Barcha ma'lumotlar kiritilgandan so'ng. Tugmani bosing "OK".



4. Dastur hisoblashni amalga oshiradi va jadvallar jadvalini ma'lumotlar bilan to'ldiradi. Endi qatorlar va ustunlar kesishmasida, oylik to'lovning aniq miqdorini, yillik foizlarning tegishli miqdori va ko'rsatilgan kredit muddati bilan, kuzatish mumkin.



5. Ko'rib turganingizdek, juda ko'p qadriyatlar mavjud. Boshqa muammolarni hal qilish uchun bundan ham ko'proq narsa bo'lishi mumkin. Shuning uchun, natijalarni yanada vizual qilish uchun va qaysi qiymatlar berilgan shartni qoniqtirmasligini darhol aniqlash uchun siz vizualizatsiya vositalaridan foydalanishingiz mumkin. Bizning holatda, bu shartli formatlash bo'ladi. Qator va ustun sarlavhalaridan tashqari jadval oralig'ining barcha qiymatlarini tanlaymiz.



6. Yorliqqa o'ting "Uy" va belgini bosing Shartli formatlash. U asboblar blokida joylashgan. Uslublar lentada. Ochilgan menyuda tanlang Hujayra tanlash qoidalari. Qo'shimcha ro'yxatda manzilni bosing "Kamroq ...".



7. Shundan so'ng shartli formatlash sozlamalari oynasi ochiladi. Chap maydonda kataklar tanlanadigan qiymatdan kamroq qiymatni ko'rsating. Yodimizda bo'lsa, oylik kredit to'lovi kamroq bo'lish shartidan qoniqish hosil qilamiz 29000 rubl. Biz bu raqamni kiritamiz. To'g'ri maydonda siz ta'kidlash rangini tanlashingiz mumkin, lekin siz uni sukut bo'yicha qoldirishingiz mumkin. Barcha kerakli sozlamalar kiritilgandan so'ng, tugmachani bosing "OK".



8. Shundan so'ng, qiymatlari yuqoridagi shartga mos keladigan barcha kataklar ajratiladi.

Jadvallar qatorini tahlil qilib, ba'zi xulosalar chiqarishimiz mumkin. Ko'rinib turibdiki, mavjud kredit muddati bilan (36 oy) oylik to'lovning ko'rsatilgan miqdoriga mablag 'kiritish uchun biz 860000.00 rubldan oshmaydigan miqdorda kredit olishimiz kerak, ya'ni avval rejalashtirilganidan 40,000 kamroq.

Agar biz hali ham 900000 rubl miqdorida qarz olishga niyat qilsak, unda kredit muddati 4 yil (48 oy) bo'lishi kerak. Faqat bu holatda oylik to'lov 29000 rubl belgilangan chegaradan oshmaydi.

Shunday qilib, ushbu jadval qatoridan foydalanib, har bir variantning ijobiy va salbiy tomonlarini tahlil qilib, qarz oluvchi barcha mumkin bo'lganlardan eng munosibini tanlab, kredit shartlari bo'yicha aniq qaror qabul qilishi mumkin.

Albatta, qidirish jadvalidan nafaqat kredit imkoniyatlarini hisoblashda, balki boshqa ko'plab muammolarni hal qilishda ham foydalanish mumkin.

Dars: Excelda shartli formatlash

Umuman, shuni ta'kidlash kerakki, qidirish jadvali turli xil o'zgaruvchilar kombinatsiyasi uchun natijani aniqlash uchun juda foydali va nisbatan sodda vositadir. Bir vaqtning o'zida shartli formatlashni qo'llagan holda, qo'shimcha ravishda siz olingan ma'lumotlarni ingl.