Microsoft Excel-da bashorat qilish vositalari

Prognozlash deyarli har qanday faoliyat sohasidagi, iqtisodiyotdan tortib muhandislikka qadar juda muhim elementdir. Ushbu sohada ixtisoslashgan juda ko'p dastur mavjud. Afsuski, hamma foydalanuvchilarga odatiy Excel elektron protsessorida prognozlash uchun arsenal vositalari mavjud bo'lib, ular o'zlarining samaradorligi bo'yicha professional dasturlardan kam emas. Keling, ushbu vositalar nima ekanligini va amalda qanday bashorat qilishni bilib olaylik.

Prognozlash tartibi

Har qanday prognozlashning maqsadi hozirgi tendentsiyani aniqlash va kelajakda ma'lum bir vaqtda o'rganilayotgan ob'ektga nisbatan kutilgan natijani aniqlashdir.

1-usul: trend liniyasi

Excel-da grafik bashorat qilishning eng mashhur turlaridan biri trend chizig'ini qurish orqali ekstrapolyatsiya hisoblanadi.

Keling, ushbu ko'rsatkich bo'yicha oldingi 12 yil uchun ma'lumot asosida 3 yil ichida korxona foydasini taxmin qilishga harakat qilaylik.

1. Biz argumentlar va funktsiya qiymatlaridan iborat jadval ma'lumotlari asosida bog'liqlik grafigini tuzamiz. Buning uchun jadval maydonini tanlang va keyin yorliqda bo'ling Kiritish, blokda joylashgan jadvalning kerakli turini belgisini bosing Grafiklar. Keyin ma'lum bir vaziyatga mos turni tanlaymiz. Tarqaladigan diagrammani tanlash yaxshidir. Siz boshqa ko'rinishni tanlashingiz mumkin, ammo keyin ma'lumotlar to'g'ri ko'rsatilishi uchun siz tahrirlashni amalga oshirishingiz kerak, xususan, argumentlar qatorini olib tashlashingiz va gorizontal o'qning boshqa shkalasini tanlashingiz kerak bo'ladi.



2. Endi biz trend chizig'ini yaratishimiz kerak. Biz diagrammadagi istalgan nuqtani o'ng tugmasini bosing. Faollashtirilgan kontekst menyusida elementni tanlashni to'xtating Trend chizig'ini qo'shish.



3. Trend liniyasini formatlash oynasi ochiladi. Unda taxminan oltita turdan birini tanlashingiz mumkin:
   • Chiziqli;
   • Logarifmik;
   • Eksponensial;
   • Quvvat;
   • Polinom;
   • Chiziqli filtrlash.

   Chiziqli yaqinlashni tanlashdan boshlaylik.

   Sozlamalar blokida "Prognoz" dalada "Oldinga yo'naltirish" raqamni o'rnating "3,0", chunki biz uch yilga oldindan bashorat qilishimiz kerak. Bunga qo'shimcha ravishda, sozlamalar yonidagi katakchani belgilashingiz mumkin. "Diagrammada tenglamani ko'rsating" va "Taxminiy ishonchlilik qiymatini (R ^ 2) diagrammaga qo'ying". Oxirgi ko'rsatkich trend chizig'ining sifatini ko'rsatadi. Sozlamalar amalga oshirilgandan so'ng, tugmachani bosing Yopish.



4. Trend chizig'i qurilgan va undan biz uch yil ichida taxminiy foyda miqdorini aniqlashimiz mumkin. Ko'rib turganimizdek, bu vaqtga kelib u 4500 ming rubldan oshishi kerak. Koeffitsient R2Yuqorida aytib o'tilganidek, trend liniyasining sifati aks etadi. Bizning holatda, qiymat R2 tashkil qiladi 0,89. Koeffitsient qanchalik yuqori bo'lsa, chiziqning ishonchliligi shunchalik yuqori bo'ladi. Uning maksimal qiymati teng bo'lishi mumkin 1. Yuqorida koeffitsient bilan qabul qilinadi 0,85 trend liniyasi ishonchli.



5. Agar ishonch darajasi sizga mos kelmasa, u holda trend liniyasi formati oynasiga qaytishingiz va boshqa yaqinlashuv turini tanlashingiz mumkin. Eng to'g'ri ma'lumotni topish uchun barcha mavjud variantlarni sinab ko'rishingiz mumkin.

   

   Ta'kidlash kerakki, tendentsiya chizig'i orqali ekstrapolyatsiya yordamida prognoz qilish davri tahlil qilingan davrlar bazasining 30% dan oshmasa samarali bo'lishi mumkin. Ya'ni, 12 yillik davrni tahlil qilganda, 3-4 yildan ortiq samarali prognoz qila olmaymiz. Ammo bu holda ham, agar bu vaqt ichida avvalgi davrlarda bo'lmagan engib bo'lmas kuch yoki, aksincha, o'ta qulay holatlar yuzaga kelmasa, bu nisbatan ishonchli bo'ladi.

Dars: Excel-da trend chizig'ini qanday qurish mumkin

2-usul: FORECAST operatori

Jadval ma'lumotlari uchun ekstrapolyatsiya standart Excel funktsiyasi orqali amalga oshirilishi mumkin PREDIKTION. Ushbu argument statistik vositalar toifasiga kiradi va quyidagi sintaksisga ega:

= PREDICT (X; ma'lum_yana qiymatlari; ma'lum_x_vallar)

"X" funktsiya qiymati aniqlanishi kerak bo'lgan argument. Bizning holatda, munozara prognoz qilinadigan yil bo'ladi.

Ma'lum bo'lgan qadriyatlar - ma'lum funktsional qiymatlar bazasi. Bizning holatlarimizda uning roli oldingi davrlar uchun foyda miqdori bilan o'ynaydi.

Ma'lum x qiymatlar funktsiyaning ma'lum qiymatlari mos keladigan dalillar. Ularning rolida bizda o'tgan yillardagi foyda to'g'risida ma'lumot to'plangan yillar soni mavjud.

Tabiiyki, tortishuv vaqt oralig'i bo'lishi shart emas. Masalan, harorat bo'lishi mumkin, va funktsiyaning qiymati qizdirilganda suvning kengayish darajasi bo'lishi mumkin.

Ushbu usulni hisoblashda chiziqli regressiya usuli qo'llaniladi.

Keling, operatorni ishlatish nuanslarini ko'rib chiqaylik PREDIKTION aniq bir misolda. Barcha stolni oling. Biz 2018 yil uchun foyda prognozini bilib olishimiz kerak.

1. Qayta ishlash natijasini ko'rsatmoqchi bo'lgan varaqdagi bo'sh katakchani tanlang. Tugmani bosing "Qo'shish funktsiyasi".



2. Ochadi Xususiyat ustasi. Kategoriya bo'yicha "Statistik" ismni tanlang "PREDICTION"va keyin tugmachani bosing "OK".



3. Dalillar oynasi boshlanadi. Dalada "X" funktsiya qiymatini topmoqchi bo'lgan argument qiymatini ko'rsating. Bizning holatda, bu 2018 yil. Shuning uchun biz yozamiz "2018". Ammo bu ko'rsatkichni varaqdagi va maydonga kiritish yaxshiroqdir "X" shunchaki unga havola bering. Bu kelajakda hisob-kitoblarni avtomatlashtirishga va kerak bo'lganda yilni osongina o'zgartirishga imkon beradi.

   Dalada Ma'lum bo'lgan qadriyatlar ustunning koordinatalarini belgilang "Korxona foydasi". Buni kursorni maydonga qo'yib, so'ng sichqonchaning chap tugmachasini bosib ushlab turib, varaqdagi tegishli ustunni ajratib ko'rsatish mumkin.

   Xuddi shunday sohada Ma'lum x qiymatlar ustun manzilini kiriting "Yil" o'tgan davr uchun ma'lumotlar bilan.

   Barcha ma'lumotlar kiritilgandan so'ng, tugmachani bosing "OK".



4. Operator kiritilgan ma'lumotlar asosida hisoblab chiqadi va natijani ekranda namoyish etadi. 2018 yilda mintaqada 4564,7 ming rubl foyda olish rejalashtirilgan. Olingan jadval asosida biz yuqorida ko'rib chiqilgan sxemalash vositalaridan foydalanib grafik tuzishimiz mumkin.



5. Agar siz argumentni kiritish uchun ishlatilgan uyadagi yilni o'zgartirsangiz, natija shunga qarab o'zgaradi va jadval avtomatik ravishda yangilanadi. Masalan, 2019 yilda prognozlarga ko'ra, foyda miqdori 4637,8 ming rublni tashkil qiladi.

Ammo shuni unutmangki, trend chizig'ini qurishda bo'lgani kabi, prognoz davridan oldingi vaqt ma'lumotlar bazasi to'plangan butun davrning 30% dan oshmasligi kerak.

Dars: Excel-da ekstrapolyatsiya

3-usul: TREND operatori

Prognoz qilish uchun siz boshqa funktsiyadan foydalanishingiz mumkin - TREND. Shuningdek, u statistik operatorlar toifasiga kiradi. Uning sintaksisi vosita sintaksisiga o'xshaydi PREDIKTION va quyidagicha:

= TREND (Ma'lum qiymatlar_y; ma'lum qiymatlar_x; yangi_values_x; [const])

Ko'rib turganingizdek, tortishuvlar Ma'lum bo'lgan qadriyatlar va Ma'lum x qiymatlar operatorning o'xshash elementlariga to'liq mos keladi PREDIKTION, va tortishuv "Yangi x qiymatlar" bahsga mos keladi "X" oldingi vosita. Bundan tashqari, TREND qo'shimcha dalil bor "Doimiy", lekin ixtiyoriy va doimiy omillar mavjud bo'lganda ishlatiladi.

Ushbu operator funktsiyaning chiziqli bog'liqligi mavjud bo'lganda eng samarali qo'llaniladi.

Keling, ushbu vosita bir xil ma'lumotlar qatori bilan qanday ishlashini ko'rib chiqamiz. Natijalarni taqqoslash uchun biz prognoz nuqtasini 2019 yilga belgilaymiz.

1. Biz natijani ko'rsatish va ishga tushirish uchun katakchani belgilaymiz Xususiyat ustasi odatdagi tarzda. Kategoriya bo'yicha "Statistik" ismini tanlang va ta'kidlang "TREND". Tugmani bosing "OK".



2. Operator Argument oynasi ochiladi TREND. Dalada Ma'lum bo'lgan qadriyatlar yuqorida tavsiflangan usul bilan biz ustunning koordinatalarini kiritamiz "Korxona foydasi". Dalada Ma'lum x qiymatlar ustun manzilini kiriting "Yil". Dalada "Yangi x qiymatlar" biz prognoz ko'rsatilishi kerak bo'lgan yil raqami joylashgan uyaga havolani kiritamiz. Bizning holatda, bu 2019 yil. Maydon "Doimiy" bo'sh qoldiring. Tugmani bosing "OK".



3. Operator ma'lumotlarni qayta ishlaydi va natijani ekranda namoyish etadi. Ko'rinib turibdiki, chiziqli qaramlik usuli bilan hisoblangan 2019 yil uchun prognoz qilingan foyda miqdori avvalgi hisoblash usulida bo'lgani kabi 4637,8 ming rublni tashkil etadi.

4-usul: GROWTH operatori

Excel-da prognoz qilish uchun ishlatilishi mumkin bo'lgan yana bir funktsiya GROWTH operatoridir. U shuningdek, vositalarning statistik guruhiga kiradi, ammo avvalgilaridan farqli o'laroq, uni hisoblashda chiziqli qaramlik usulidan emas, balki eksponensial usuldan foydalaniladi. Ushbu vositaning sintaksisi quyidagicha:

= O'SISh (Ma'lum qiymatlar_y; ma'lum qiymatlar_x; yangi_vallar_x; [const])

Ko'rib turganingizdek, ushbu funktsiyaning argumentlari operatorning dalillarini aniq takrorlaydi TREND, shuning uchun biz ularning tavsifiga ikkinchi marta to'xtalmaymiz, ammo darhol ushbu vositani amaliy ravishda qo'llashga o'tamiz.

1. Biz natijani chiqarish uchun katakchani tanlaymiz va odatdagidek chaqiramiz Xususiyat ustasi. Statistik operatorlar ro'yxatida elementni qidiring ROST, uni tanlang va tugmani bosing "OK".



2. Yuqoridagi funktsiyaning argument oynasi faollashtirilgan. Ushbu oynaning maydonlariga ma'lumotlarni operator argumentlari oynasiga kiritganimiz kabi kiriting TREND. Ma'lumot kiritilgandan so'ng, tugmachani bosing "OK".



3. Ma'lumotlarga ishlov berish natijasi monitorda ilgari ko'rsatilgan katakchada ko'rsatiladi. Ko'rib turganingizdek, bu safar natija 4682,1 ming rublni tashkil qiladi. Operator ma'lumotlarini qayta ishlash natijalaridan farqlar TREND ahamiyatsiz, ammo ular mavjud. Buning sababi shundaki, ushbu vositalar turli xil hisoblash usullaridan foydalanadi: chiziqli bog'liqlik usuli va eksponensial qaramlik usuli.

5-usul: LINEAR operatori

Operator LINE hisoblashda chiziqli yaqinlashish usulidan foydalaniladi. Asbob tomonidan ishlatiladigan chiziqli bog'liqlik usuli bilan chalkashtirmaslik kerak. TREND. Uning sintaksisi quyidagicha:

= LINE (Ma'lum qiymatlar_y; ma'lum qiymatlar_x; yangi_vallar_x; [const]; [statistika])

Oxirgi ikkita dalil ixtiyoriydir. Birinchi ikkitasi bilan oldingi usullar bilan tanishamiz. Ammo, ehtimol siz ushbu funktsiyada yangi qiymatlarga ishora qiladigan biron bir argument yo'qligini payqadingiz. Gap shundaki, ushbu vosita faqat davr birligiga to'g'ri keladigan daromadning o'zgarishini aniqlaydi, bu bizning holimizga bir yilga teng, ammo biz umumiy natijani operatorning hisoblash natijasini oxirgi haqiqiy foyda qiymatiga qo'shib alohida hisoblashimiz kerak. LINEyillar sonidan necha marta ko'p.

1. Hisoblash amalga oshiriladigan katakni tanlaymiz va Funktsiya ustasini ishga tushiramiz. Ismni tanlang LINEIN toifasida "Statistik" va tugmachani bosing "OK".



2. Dalada Ma'lum bo'lgan qadriyatlar, ochilgan dalillar oynasi, ustunning koordinatalarini kiriting "Korxona foydasi". Dalada Ma'lum x qiymatlar ustun manzilini kiriting "Yil". Qolgan maydonlar bo'sh qoldiriladi. Keyin tugmachani bosing "OK".



3. Dastur tanlangan katakda chiziqli trend qiymatini hisoblab chiqadi va ko'rsatadi.



4. Endi biz 2019 yilga mo'ljallagan foyda hajmini aniqlashimiz kerak. Belgini o'rnating "=" varaqdagi bo'sh bo'sh joyga. Biz oxirgi o'qilgan yil (2016) uchun haqiqiy foyda miqdorini o'z ichiga olgan katakchani bosamiz. Biz belgi qo'yamiz "+". Keyin, ilgari hisoblangan chiziqli tendentsiyani o'z ichiga olgan katakchani bosing. Biz belgi qo'yamiz "*". O'qish davrining oxirgi yili (2016) va prognoz qilishni xohlagan yil (2019) o'rtasida uch yil davom etganligi sababli, biz kamerada raqamni o'rnatdik. "3". Hisoblash uchun tugmani bosing Kirish.

Ko'rib turganingizdek, 2019 yilda chiziqli yaqinlash usuli bilan hisoblangan prognoz marjasi 4614,9 ming rublni tashkil qiladi.

6-usul: LGRFPPRIBLE operatori

Biz ko'rib chiqadigan oxirgi vosita bo'ladi LGRFPPRIBLE. Ushbu operator hisob-kitoblarni ekspansional yaqinlash usuli asosida amalga oshiradi. Uning sintaksisi quyidagi tuzilishga ega:

= LGRFPRIBLE (Ma'lum qiymatlar_y; ma'lum qiymatlar_x; yangi_vallar_x; [const]; [statistika])

Ko'rib turganingizdek, barcha argumentlar avvalgi funktsiyaning mos keladigan elementlarini to'liq takrorlaydi. Prognozni hisoblash algoritmi biroz o'zgaradi. Funktsiya ekspansional tendentsiyani hisoblab chiqadi, bu daromad miqdori bir davr uchun, ya'ni yil davomida necha marta o'zgarishini ko'rsatadi. So'nggi haqiqiy davr bilan birinchi rejalashtirilgan davr o'rtasidagi foyda farqini topishimiz kerak, uni rejalashtirilgan davrlar soniga ko'paytirishimiz kerak (3) va natijaga oxirgi haqiqiy davrning summasini qo'shing.

1. Funktsiya ustasi operatorlari ro'yxatida ismni tanlang LGRFPPRIBL. Tugmani bosing "OK".



2. Dalillar oynasi boshlanadi. Unda biz funktsiyadan foydalanib ma'lumotlarni xuddi biz qilganimiz kabi kiritamiz LINE. Tugmani bosing "OK".



3. Eksponensial tendentsiya natijasi hisoblab chiqilgan va belgilangan katakda ko'rsatiladi.



4. Biz belgi qo'yamiz "=" bo'sh kameraga joylashtiring. Qavslarni oching va oxirgi haqiqiy davr uchun daromad qiymati bo'lgan uyani tanlang. Biz belgi qo'yamiz "*" va eksponent yo'nalishini o'z ichiga olgan katakni tanlang. Biz minus belgisini qo'yamiz va oxirgi davr uchun daromad qiymati joylashgan elementni yana bosing. Belgilar ichida qavsni va haydovchini yoping "*3+" tirnoqsiz. Yana, oxirgi marta tanlangan bitta katakchani bosing. Hisoblashni amalga oshirish uchun tugmani bosing Kirish.

Ekspansional yaqinlash usuli bilan hisoblab chiqilgan 2019 yilda prognoz qilingan foyda miqdori 4 639,2 ming rublni tashkil qiladi, bu yana oldingi hisob-kitobda olingan natijalardan deyarli farq qilmaydi.

Dars: Excel-dagi boshqa statistik vazifalar

Excel dasturida qanday qilib bashorat qilishni bilib oldik. Buni trend chizig'idan va bir qator o'rnatilgan statistik funktsiyalardan foydalangan holda grafik yordamida amalga oshirish mumkin. Ushbu operatorlar tomonidan bir xil ma'lumotlarni qayta ishlash natijasida boshqacha natijaga erishish mumkin. Ammo bu ajablanarli emas, chunki ularning barchasida har xil hisoblash usullari qo'llaniladi. Agar dalgalanma kichik bo'lsa, unda ma'lum bir vaziyat uchun qo'llaniladigan barcha variantlarni nisbatan ishonchli deb hisoblash mumkin.