Excelda sezgirlik tahlili (namuna ma'lumotlar jadvali)

Moliya sohasidagi jarayonlar doimo o'zaro bog'liq - bir omil boshqasiga bog'liq va u bilan o'zgaradi. Ushbu o'zgarishlarni kuzatib boring va kelajakda nima kutish mumkinligini tushunib oling, ehtimol Excel funktsiyalari va elektron jadval usullaridan foydalaning.

Ma'lumotlar jadvali yordamida bir nechta natijalarni olish

Ma'lumotlar jadvali imkoniyatlari - bu nima bo'lsa-da tahlil qilishning elementlari - ko'pincha Microsoft Excel orqali amalga oshiriladi. Bu sezuvchanlik tahlilining ikkinchi nomi.

haqida umumiy ma'lumot

Ma'lumotlar jadvali - bu ba'zi hujayralardagi qiymatlarni o'zgartirish orqali muammolarni hal qilish uchun ishlatilishi mumkin bo'lgan hujayralar diapazoni. U formulaning tarkibiy qismlaridagi o'zgarishlarni kuzatib borish va ushbu o'zgarishlarga muvofiq natijalar bo'yicha yangilanishlarni olish zarur bo'lganda yaratiladi. Keling, tadqiqotda ma'lumotlar jadvallarini qanday ishlatishni va ularning qanday turlarini bilib olaylik.

Ma'lumotlar jadvallari haqida asosiy ma'lumotlar

Ma'lumotlar jadvallarining ikki turi mavjud bo'lib, ular komponentlar soni bo'yicha farqlanadi. Siz uXNUMXbuXNUMXb qiymatlar soniga e'tibor qaratgan holda jadvalni tuzishingiz kerak, u bilan tekshirishingiz kerak.

Bir yoki bir nechta ifodalarda faqat bitta o'zgaruvchi bo'lsa, ularning natijasini o'zgartirishi mumkin bo'lsa, statistiklar bitta o'zgaruvchan jadvaldan foydalanadilar. Masalan, u ko'pincha PMT funktsiyasi bilan birgalikda ishlatiladi. Formula muntazam to'lov miqdorini hisoblash uchun mo'ljallangan va shartnomada ko'rsatilgan foiz stavkasini hisobga oladi. Bunday hisob-kitoblarda o'zgaruvchilar bir ustunga, hisob-kitoblarning natijalari esa boshqa ustunga yoziladi. 1 o'zgaruvchiga ega bo'lgan ma'lumotlar plastinkasiga misol:

Keyinchalik, 2 o'zgaruvchiga ega bo'lgan plitalarni ko'rib chiqing. Ular har qanday ko'rsatkichning o'zgarishiga ikkita omil ta'sir qiladigan hollarda qo'llaniladi. Ikki o'zgaruvchi kredit bilan bog'liq bo'lgan boshqa jadvalda yakunlanishi mumkin, bu esa optimal to'lov muddatini va oylik to'lov miqdorini aniqlash uchun ishlatilishi mumkin. Ushbu hisob-kitobda siz PMT funksiyasidan ham foydalanishingiz kerak. 2 ta o'zgaruvchiga ega jadvalga misol:

Bitta o'zgaruvchi bilan ma'lumotlar jadvalini yaratish

Stokda atigi 100 ta kitob bo'lgan kichik kitob do'koni misolida tahlil qilish usulini ko'rib chiqing. Ulardan ba'zilari qimmatroq sotilishi mumkin (50 dollar), qolganlari xaridorlarga arzonroq (20 dollar) tushadi. Barcha tovarlarni sotishdan olingan umumiy daromad hisoblab chiqiladi - egasi kitoblarning 60 foizini yuqori narxda sotishga qaror qildi. Agar siz katta hajmdagi tovarlar narxini oshirsangiz - 70% va hokazo, daromad qanday oshishini bilib olishingiz kerak.

E'tibor bering! Umumiy daromad formula yordamida hisoblanishi kerak, aks holda ma'lumotlar jadvalini tuzish mumkin bo'lmaydi.

1. Varaq chetidan uzoqda bo'sh katakchani tanlang va unga formulani yozing: =Jami daromad xujayrasi. Masalan, agar daromad C14 katakchasida yozilgan bo'lsa (tasodifiy belgi ko'rsatilgan), siz buni yozishingiz kerak: =S14.
2. Ushbu katakchaning chap tomonidagi ustundagi tovarlar hajmining foizini yozamiz - uning ostida emas, bu juda muhim.
3. Biz foiz ustuni va umumiy daromadga havola joylashgan hujayralar oralig'ini tanlaymiz.

4. Biz "Ma'lumotlar" yorlig'ida "Tahlil bo'lsa nima bo'ladi" bandini topamiz va ustiga bosing - ochilgan menyuda "Ma'lumotlar jadvali" variantini tanlang.

5. Kichkina oyna ochiladi, unda "Qiymatlarni satrlar bo'yicha almashtirish ..." ustunida dastlab yuqori narxda sotilgan kitoblar foizini ko'rsatish kerak bo'ladi. Ushbu qadam ortib borayotgan foizni hisobga olgan holda umumiy daromadni qayta hisoblash uchun amalga oshiriladi.

Jadvalni tuzish uchun ma'lumotlar kiritilgan oynada "OK" tugmasini bosgandan so'ng, hisob-kitoblar natijalari satrlarda paydo bo'ladi.

Yagona o'zgaruvchan ma'lumotlar jadvaliga formula qo'shish

Harakatni faqat bitta o'zgaruvchi bilan hisoblashda yordam bergan jadvaldan siz qo'shimcha formula qo'shish orqali murakkab tahlil vositasini yaratishingiz mumkin. U allaqachon mavjud formulaning yoniga kiritilishi kerak - masalan, agar jadval qatorga yo'naltirilgan bo'lsa, biz mavjud bo'lganning o'ng tomonidagi katakchaga ifoda kiritamiz. Ustun yo'nalishi o'rnatilganda, biz eski formulaning ostiga yangi formulani yozamiz. Keyinchalik, algoritmga amal qiling:

1. Hujayralar oralig'ini yana tanlang, lekin endi u yangi formulani o'z ichiga olishi kerak.
2. "Nima bo'lsa" tahlil menyusini oching va "Ma'lumotlar jadvali" ni tanlang.
3. Plitaning yo'nalishiga qarab, satrlar yoki ustunlardagi mos keladigan maydonga yangi formula qo'shamiz.

Ikki o'zgaruvchiga ega ma'lumotlar jadvalini yarating

Bunday jadvalning boshlanishi biroz farq qiladi - foiz qiymatlaridan yuqori bo'lgan umumiy daromadga havolani qo'yish kerak. Keyinchalik, biz quyidagi amallarni bajaramiz:

1. Narx variantlarini daromadga havola bilan bitta qatorga yozing - har bir narx uchun bitta katak.
2. Bir qator katakchalarni tanlang.

3. Bir o'zgaruvchiga ega jadvalni tuzishda bo'lgani kabi, asboblar panelidagi "Ma'lumotlar" yorlig'i orqali ma'lumotlar jadvali oynasini oching.
4. "Qiymatlarni ustunlar bilan almashtirish ..." ustunida boshlang'ich yuqori narxga ega katakchani almashtiring.
5. "Qiymatlarni satrlar bo'yicha almashtirish ..." ustuniga qimmatbaho kitoblarni sotishning boshlang'ich foizini ko'rsatadigan katakchani qo'shing va "OK" tugmasini bosing.

Natijada, butun jadval tovarlarni sotish uchun turli shartlar bilan mumkin bo'lgan daromad miqdori bilan to'ldiriladi.

Ma'lumotlar jadvallarini o'z ichiga olgan ish varaqlari uchun hisob-kitoblarni tezlashtiring

Agar sizga butun ish kitobini qayta hisoblashni qo'zg'atmaydigan ma'lumotlar jadvalida tezkor hisob-kitoblar kerak bo'lsa, jarayonni tezlashtirish uchun bir nechta narsalarni qilishingiz mumkin.

1. Variantlar oynasini oching, o'ngdagi menyuda "Formulalar" bandini tanlang.
2. "Ishchi kitobidagi hisoblar" bo'limida "Avtomatik, ma'lumotlar jadvallaridan tashqari" bandini tanlang.

3. Keling, jadvaldagi natijalarni qo'lda qayta hisoblaymiz. Buning uchun formulalarni tanlang va F tugmasini bosing.

Sezuvchanlik tahlilini amalga oshirish uchun boshqa vositalar

Dasturda sezgirlikni tahlil qilishda yordam beradigan boshqa vositalar mavjud. Ular qo'lda bajarilishi kerak bo'lgan ba'zi harakatlarni avtomatlashtiradi.

1. Agar kerakli natija ma'lum bo'lsa, "Parametr tanlash" funktsiyasi mos keladi va bunday natijani olish uchun siz o'zgaruvchining kirish qiymatini bilishingiz kerak..
2. "Yechim izlash" - muammolarni hal qilish uchun qo'shimcha. Cheklovlarni belgilash va ularga ishora qilish kerak, shundan so'ng tizim javobni topadi. Yechim qiymatlarni o'zgartirish orqali aniqlanadi.
3. Sezuvchanlik tahlili Ssenariy menejeri yordamida amalga oshirilishi mumkin. Ushbu vosita Ma'lumotlar yorlig'i ostidagi nima bo'lsa tahlil menyusida joylashgan. U bir nechta hujayralardagi qiymatlarni almashtiradi - ularning soni 32 ga yetishi mumkin. Dispetcher bu qiymatlarni foydalanuvchi ularni qo'lda o'zgartirmasligi uchun solishtiradi. Skript menejeridan foydalanishga misol:

Excelda investitsiya loyihasining sezgirlik tahlili

Agar nima bo'lsa tahlili, ayniqsa, investitsiya kabi prognozlash zarur bo'lgan holatlarda foydalidir. Tahlilchilar ushbu usuldan ba'zi omillarning o'zgarishi natijasida kompaniya aktsiyalarining qiymati qanday o'zgarishini bilish uchun foydalanadilar.

Investitsion sezgirlikni tahlil qilish usuli

"Nima bo'lsa" ni tahlil qilishda ro'yxatga olishdan foydalaning - qo'lda yoki avtomatik. Qiymatlar diapazoni ma'lum va ular formulaga birma-bir almashtiriladi. Natijada qiymatlar to'plami. Ulardan mos raqamni tanlang. Moliya sohasida sezgirlik tahlili o'tkaziladigan to'rtta ko'rsatkichni ko'rib chiqaylik:

1. Sof hozirgi qiymat - daromad miqdoridan investitsiya miqdorini ayirish yo'li bilan hisoblanadi.
2. Ichki daromad / foyda darajasi - bir yilda investitsiyadan qancha foyda olish kerakligini ko'rsatadi.
3. Qaytarilish koeffitsienti - bu barcha daromadlarning dastlabki investitsiyalarga nisbati.
4. Diskontlangan foyda indeksi - investitsiya samaradorligini ko'rsatadi.

formula

O'rnatish sezgirligini quyidagi formula yordamida hisoblash mumkin: Chiqish parametrining o'zgarishi% / Kirish parametrining o'zgarishi%.

Chiqish va kirish parametrlari yuqorida tavsiflangan qiymatlar bo'lishi mumkin.

1. Natijani standart sharoitlarda bilishingiz kerak.
2. Biz o'zgaruvchilardan birini almashtiramiz va natijadagi o'zgarishlarni kuzatamiz.
3. Belgilangan shartlarga nisbatan ikkala parametrning foiz o'zgarishini hisoblaymiz.
4. Olingan foizlarni formulaga kiritamiz va sezgirlikni aniqlaymiz.

Excelda investitsiya loyihasining sezgirlik tahliliga misol

Tahlil metodologiyasini yaxshiroq tushunish uchun misol kerak. Keling, loyihani quyidagi ma'lum ma'lumotlar bilan tahlil qilaylik:

1. Loyihani tahlil qilish uchun jadvalni to'ldiring.

2. OFFSET funksiyasidan foydalanib pul oqimini hisoblaymiz. Dastlabki bosqichda oqim investitsiyalarga teng. Keyin formulani qo'llaymiz: =AGAR(OFFSET(Raqam,1;)=2;SUM(Kirish 1:Chiqish 1); SUM(Kirish 1:Chiqish 1)+$B$ 5)
   Jadvalning tartibiga qarab formuladagi hujayra belgilari boshqacha bo'lishi mumkin. Oxirida dastlabki ma'lumotlardan olingan qiymat qo'shiladi - qutqarish qiymati.

3. Biz loyihani to'lash muddatini aniqlaymiz. Dastlabki davr uchun biz ushbu formuladan foydalanamiz: =QISQA(G7: G17;»<0″). Hujayra diapazoni pul oqimi ustunidir. Keyingi davrlar uchun biz ushbu formulani qo'llaymiz: =Boshlang'ich davr+AGAR(Birinchi e.oqim>0; Birinchi e.oqim;0). Loyiha 4 yil ichida zararni yo'qotish nuqtasida.

4. Loyiha o'z samarasini beradigan davrlarning raqamlari uchun ustun yaratamiz.

5. Biz investitsiya daromadini hisoblaymiz. Muayyan vaqtdagi foyda dastlabki investitsiyaga bo'lingan holda ifodalash kerak.

6. Ushbu formuladan foydalanib chegirma omilini aniqlaymiz: =1/(1+Disk.%) ^ Raqam.

7. Hozirgi qiymatni ko'paytirish yordamida hisoblaymiz - pul oqimi chegirma omili bilan ko'paytiriladi.

8. Keling, PI (rentabellik indeksi) ni hisoblaymiz. Vaqt o'tishi bilan joriy qiymat loyihaning boshlanishidagi investitsiyalarga bo'linadi.

9. IRR funksiyasidan foydalanib, ichki daromad stavkasini aniqlaymiz: =IRR(pul oqimi diapazoni).

Ma'lumotlar jadvalidan foydalangan holda investitsiya sezgirligini tahlil qilish

Investitsiyalar sohasidagi loyihalarni tahlil qilish uchun ma'lumotlar jadvalidan ko'ra boshqa usullar mos keladi. Ko'pgina foydalanuvchilar formulani tuzishda chalkashliklarga duch kelishadi. Bir omilning boshqalardagi o'zgarishlarga bog'liqligini bilish uchun siz hisob-kitoblarni kiritish va ma'lumotlarni o'qish uchun to'g'ri hujayralarni tanlashingiz kerak.

Hisoblashni avtomatlashtirish bilan Excelda faktor va dispersiya tahlili

Sezuvchanlik tahlilining yana bir tipologiyasi omilli tahlil va dispersiya tahlilidir. Birinchi tur raqamlar orasidagi munosabatni belgilaydi, ikkinchisi bir o'zgaruvchining boshqalarga bog'liqligini ochib beradi.

Excelda ANOVA

Bunday tahlilning maqsadi qiymatning o'zgaruvchanligini uchta komponentga bo'lishdir:

1. Boshqa qiymatlarning ta'siri natijasida o'zgaruvchanlik.
2. Unga ta'sir qiluvchi qadriyatlarning o'zaro bog'liqligi tufayli o'zgarishlar.
3. Tasodifiy o'zgarishlar.

Keling, Excelning "Ma'lumotlar tahlili" qo'shimchasi orqali dispersiyani tahlil qilaylik. Agar u yoqilmagan bo'lsa, uni sozlamalarda yoqish mumkin.

Dastlabki jadval ikkita qoidaga amal qilishi kerak: har bir qiymat uchun bitta ustun mavjud va undagi ma'lumotlar o'sish yoki kamayish tartibida joylashtirilgan. Ta'lim darajasining konfliktdagi xatti-harakatlarga ta'sirini tekshirish kerak.

1. Ma'lumotlar yorlig'ida Ma'lumotlarni tahlil qilish vositasini toping va uning oynasini oching. Ro'yxatda siz dispersiyaning bir tomonlama tahlilini tanlashingiz kerak.

2. Muloqot oynasining qatorlarini to'ldiring. Kirish oralig'i sarlavhalar va raqamlardan tashqari barcha hujayralardir. Ustunlar bo'yicha guruhlash. Natijalarni yangi varaqda ko'rsatamiz.

Sariq hujayradagi qiymat birdan katta bo'lganligi sababli, taxminni noto'g'ri deb hisoblash mumkin - ta'lim va mojarodagi xatti-harakatlar o'rtasida hech qanday bog'liqlik yo'q.

Excelda faktor tahlili: misol

Keling, savdo sohasidagi ma'lumotlarning o'zaro bog'liqligini tahlil qilaylik - mashhur va mashhur bo'lmagan mahsulotlarni aniqlash kerak. Dastlabki ma'lumotlar:

1. Ikkinchi oyda qaysi tovarlarga talab ko'proq oshganini aniqlashimiz kerak. Biz talabning o'sishi va pasayishini aniqlash uchun yangi jadval tuzmoqdamiz. O'sish quyidagi formula bo'yicha hisoblanadi: =IF((Talab 2-Talab 1)>0; Talab 2- Talab 1;0). Formulani kamaytirish: =AGAR(O'sish=0; Talab 1- Talab 2;0).

2. Tovarlarga bo'lgan talabning o'sishini foiz sifatida hisoblang: =AGAR(Oʻsish/Natija 2 =0; Kamaytirish/Natija 2; Oʻsish/Natija 2).

3. Aniqlik uchun diagramma tuzamiz - hujayralar oralig'ini tanlang va "Qo'shish" yorlig'i orqali gistogramma yarating. Sozlamalarda siz to'ldirishni olib tashlashingiz kerak, bu ma'lumotlar seriyasini formatlash vositasi orqali amalga oshirilishi mumkin.

Excelda dispersiyani ikki tomonlama tahlil qilish

Dispersiyani tahlil qilish bir nechta o'zgaruvchilar bilan amalga oshiriladi. Buni misol bilan ko'rib chiqing: erkaklar va ayollarda turli xil tovush tovushiga reaktsiya qanchalik tez namoyon bo'lishini bilib olishingiz kerak.

1. Biz "Ma'lumotlar tahlili" ni ochamiz, ro'yxatda siz takrorlashsiz ikki tomonlama dispersiya tahlilini topishingiz kerak.
2. Kirish oralig'i - ma'lumotlarni o'z ichiga olgan hujayralar (sarlavhasiz). Natijalarni yangi varaqda ko'rsatamiz va "OK" tugmasini bosing.

F qiymati F-kritikdan kattaroqdir, ya'ni zamin tovushga reaktsiya tezligiga ta'sir qiladi.

Xulosa

Ushbu maqolada Excel elektron jadvalidagi sezgirlikni tahlil qilish har bir foydalanuvchi uni qo'llash usullarini tushunishi uchun batafsil muhokama qilindi.