Proqnozlaşdırma, iqtisadiyyatdan mühəndisliyə qədər demək olar ki, hər hansı bir fəaliyyət sahəsinin çox vacib bir elementidir. Bu sahədə ixtisaslaşan çox sayda proqram var. Təəssüf ki, bütün istifadəçilər adi Excel elektron tablo prosessorunun proqnozlaşdırma üçün öz arsenal vasitələrində olduğunu və səmərəliliyində peşəkar proqramlardan heç də aşağı olmayan bir şey olduğunu bilmirlər. Bu vasitələrin nə olduğunu və praktikada necə bir proqnoz hazırlayacağımızı öyrənək.
Proqnozlaşdırma qaydası
İstənilən proqnozlaşdırmada məqsəd cari tendensiyanı müəyyənləşdirmək və gələcəkdə müəyyən bir vaxtda öyrənilən obyektlə əlaqəli gözlənilən nəticəni müəyyən etməkdir.
Metod 1: trend xətti
Excel-də ən populyar qrafik proqnozlaşdırma növlərindən biri trend xətti quraraq ekstrapolyasiyadır.
Gəlin bu 12 il üçün bu göstərici barədə məlumatlar əsasında 3 ildə müəssisənin mənfəətinin həcmini proqnozlaşdırmağa çalışaq.
- Arqumentlər və funksiya dəyərlərindən ibarət cədvəl məlumatları əsasında bir asılılıq qrafiki qururuq. Bunu etmək üçün, masa sahəsini seçin və sonra nişan içində Daxil edin, blokda yerləşən istədiyiniz tip tipi nişanın üzərinə vurun Diaqramlar. Sonra müəyyən bir vəziyyətə uyğun olan növü seçirik. Səpələnmə sxemini seçmək ən yaxşısıdır. Başqa bir mənzərəni seçə bilərsiniz, ancaq sonra, məlumatların düzgün göstərildiyi üçün, tənzimləməni yerinə yetirməli, xüsusən də arqumentin xəttini çıxartmalı və üfüqi oxun başqa miqyasını seçməlisiniz.
- İndi bir trend xətti qurmalıyıq. Diaqramdakı nöqtələrdən birini sağa vururuq. Aktivləşdirilmiş kontekst menyusunda, seçimi dayandırın Trend Xətti əlavə edin.
- Trend xətti formatlama pəncərəsi açılır. Bu yaxınlaşmanın altı növündən birini seçə bilərsiniz:
- Xətti;
- Loqarifmik;
- Eksponentli;
- Güc;
- Polinom;
- Xətti süzgəc.
Xətti yaxınlaşma seçərək başlayaq.
Parametrlər blokunda "Proqnoz" sahəsində "Ötür" nömrəni təyin et "3,0", üç il əvvəlcədən proqnoz verməliyik. Bundan əlavə, parametrlərin yanında qutuyu yoxlaya bilərsiniz. "Bərabərliyi diaqramda göstər" və "Diaqramda yaxınlaşma inamının dəyərini (R ^ 2) qoyun". Son göstərici trend xəttinin keyfiyyətini göstərir. Parametrlər edildikdən sonra düyməni vurun Bağlayın.
- Trend xətti qurulur və ondan üç ildə təqribi mənfəət miqdarını təyin edə bilərik. Gördüyümüz kimi, bu vaxta qədər 4500 min rubldan çox olmalıdır. Əmsalı R2Yuxarıda qeyd edildiyi kimi, trend xəttinin keyfiyyətini göstərir. Bizim vəziyyətimizdə, dəyəri R2 təşkil edir 0,89. Bu əmsal nə qədər yüksəkdirsə, xəttin etibarlılığı da o qədər yüksəkdir. Onun maksimum dəyəri bərabər ola bilər 1. Yuxarıda bir əmsal ilə qəbul edilir 0,85 trend xətti etibarlıdır.
- Etibar səviyyəsi sizə uyğun deyilsə, onda trend xətti formatı pəncərəsinə qayıdıb yaxınlaşmanın başqa növünü seçə bilərsiniz. Ən doğrusını tapmaq üçün bütün mövcud variantları sınaya bilərsiniz.
Qeyd etmək lazımdır ki, trend xətti ilə ekstrapolyasiyadan istifadə edərək proqnoz müddəti təhlil olunmuş dövrlərin bazasının 30% -dən çox olmadıqda təsirli ola bilər. Yəni 12 illik bir dövrü təhlil edərkən 3-4 ildən çox müddətə təsirli bir proqnoz verə bilmərik. Ancaq bu vəziyyətdə də, bu müddət ərzində əvvəlki dövrlərdə olmayan bir fors-major və ya əksinə son dərəcə əlverişli hal olmayacağı təqdirdə nisbətən etibarlı olacaqdır.
Dərs: Excel-də bir trend xəttini necə qurmaq olar
Metod 2: FORECAST operatoru
Cədvəl məlumatları üçün ekstrapolyasiya standart Excel funksiyası vasitəsilə edilə bilər ÖDƏNİŞSİZ. Bu dəlil statistik alətlər kateqoriyasına aiddir və aşağıdakı sintaksisə malikdir:
= PREDICT (X; məlum_y_ dəyərlər; məlum_x_vallar)
"X" funksiya dəyərinin təyin edilməsi lazım olduğu bir dəlildir. Bizim vəziyyətimizdə, mübahisə proqnozlaşdırılmanın aparılacağı il olacaq.
Bilinən dəyərlər - məlum funksiya dəyərlərinin əsası. Bizim vəziyyətimizdə onun rolu əvvəlki dövrlər üçün mənfəətin həcmi ilə oynayır.
Məlum x Dəyərlər funksiyanın məlum dəyərlərinin uyğun gəldiyi dəlillərdir. Onların rollarında, əvvəlki illərin mənfəətinə dair məlumatların toplandığı illərin nömrələnməsi var.
Təbii ki, mübahisənin bir müddətlik olması lazım deyil. Məsələn, temperatur ola bilər və funksiyanın dəyəri qızdırıldığı zaman suyun genişlənməsi səviyyəsi ola bilər.
Bu metodu hesablayarkən xətti reqressiya metodu istifadə olunur.
Operatordan istifadə nüanslarına baxaq ÖDƏNİŞSİZ konkret bir misalda. Bütün masanı götürün. 2018-ci il üçün qazanc proqnozunu bilməliyik.
- İşləmə nəticəsini göstərməyi planlaşdırdığınız vərəqdə boş bir hücrə seçin. Düyməni vurun "Funksiya daxil et".
- Açır Xüsusiyyət sehrbazdır. Kateqoriyada "Statistik" ad seçin "TƏQDİMAT"sonra düyməni vurun "OK".
- Mübahisə pəncərəsi başlayır. Sahədə "X" funksiyanın dəyərini tapmaq istədiyiniz dəlilin dəyərini göstərin. Bizim vəziyyətimizdə bu 2018-ci ildir. Buna görə də yazırıq "2018". Bu göstəricini vərəqdəki bir hücrəyə və sahəyə göstərmək daha yaxşıdır "X" sadəcə ona bir keçid verin. Bu, gələcəkdə hesablamaları avtomatlaşdırmağa və zəruri hallarda ili asanlıqla dəyişdirməyə imkan verəcəkdir.
Sahədə Bilinən dəyərlər sütunun koordinatlarını göstərin "Müəssisənin qazancı". Bu, kursoru sahəyə qoyaraq, sol siçan düyməsini basıb səhifədəki müvafiq sütunu vurğulamaqla edilə bilər.
Eynilə sahədə Məlum x Dəyərlər sütun ünvanını daxil edin "İl" ötən dövr üçün məlumatlarla.
Bütün məlumatlar daxil olduqdan sonra düyməni vurun "OK".
- Operator daxil edilmiş məlumatlar əsasında hesablayır və nəticəni ekranda göstərir. 2018-ci il üçün bölgədə 4564,7 min rubl qazanc əldə etmək planlaşdırılır. Yaranan cədvələ əsasən yuxarıda müzakirə edilən charting vasitələrindən istifadə edərək bir qrafik qura bilərik.
- Arqumenti daxil etmək üçün istifadə olunan xanada ili dəyişdirsəniz, nəticə müvafiq olaraq dəyişəcək və cədvəl avtomatik olaraq yenilənəcəkdir. Məsələn, 2019-cu ildəki proqnoza görə, qazancın miqdarı 4637.8 min rubl olacaqdır.
Ancaq unutmayın ki, trend xəttinin qurulmasında olduğu kimi, proqnoz dövrünə qədər olan müddət də verilənlər bazasının toplandığı bütün dövrün 30% -dən çox olmamalıdır.
Dərs: Excel-də ekstrapolyasiya
Metod 3: TREND operatoru
Proqnoz vermək üçün başqa bir funksiyadan istifadə edə bilərsiniz - TREND. Həm də statistik operatorlar kateqoriyasına aiddir. Onun sintaksisi alət sintaksisinə çox bənzəyir TƏQDİMAT və bu kimi görünür:
= TREND (Məlum dəyərlər_y; məlum dəyərlər_x; yeni_values_x; [const])
Gördüyünüz kimi, mübahisələr Bilinən dəyərlər və Məlum x Dəyərlər tamamilə operatorun oxşar elementlərinə uyğundur ÖDƏNİŞSİZ, və mübahisə "Yeni x dəyərlər" mübahisəyə uyğundur "X" əvvəlki alət. Bundan əlavə TREND əlavə bir arqument var "Daimi", lakin bu isteğe bağlıdır və yalnız sabit amillər olduqda istifadə olunur.
Bu operator funksiyanın xətti bir asılılığının mövcudluğunda ən effektiv istifadə olunur.
Bu alətin eyni məlumat silsiləsi ilə necə işləyəcəyini görək. Nəticələri müqayisə etmək üçün proqnoz nöqtəsini 2019-cu il kimi təyin edirik.
- Nəticəni göstərmək və işlətmək üçün hücrəni təyin edirik Xüsusiyyət sehrbazdır adi qaydada. Kateqoriyada "Statistik" adı tapın və vurğulayın "TREND". Düyməni vurun "OK".
- Operator Arqument Pəncərəsi Açılır TREND. Sahədə Bilinən dəyərlər yuxarıda təsvir olunan üsulla sütunun koordinatlarını daxil edirik "Müəssisənin qazancı". Sahədə Məlum x Dəyərlər sütun ünvanını daxil edin "İl". Sahədə "Yeni x dəyərlər" proqnozun göstərilməli olduğu il nömrəsinin yerləşdiyi xanaya keçid daxil edirik. Bizim vəziyyətimizdə bu, 2019-cu ildir. Sahə "Daimi" boş qoyun. Düyməni vurun "OK".
- Operator məlumatları emal edir və nəticəni ekranda göstərir. Gördüyünüz kimi, xətti asılılıq metodu ilə hesablanan 2019-cu il üçün proqnozlaşdırılan mənfəətin miqdarı əvvəlki hesablama metodunda olduğu kimi 4637,8 min rubl olacaqdır.
Metod 4: Böyümə operatoru
Excel-də proqnoz vermək üçün istifadə edilə bilən başqa bir funksiya BÖYÜK operatorudur. Həm də statistik alətlər qrupuna aiddir, lakin əvvəlkilərdən fərqli olaraq, onu hesablayarkən xətti asılılıq metodundan deyil, eksponensial metoddan istifadə edir. Bu vasitənin sintaksisi aşağıdakı kimidir:
= Böyüdük (Bilinən dəyərlər_y; Məlum dəyərlər_x; Yeni_values_x; [const])
Gördüyünüz kimi, bu funksiyanın dəlilləri operatorun arqumentlərini tam olaraq təkrarlayır TREND, buna görə də onların təsvirinə ikinci dəfə toxunmayacağıq, dərhal bu vasitənin praktik tətbiqinə davam edirik.
- Nəticəni çıxarmaq üçün hücrəni seçirik və adi şəkildə çağırırıq Xüsusiyyət sehrbazdır. Statistik operatorların siyahısında, maddəyə baxın ROST, onu seçin və düyməni vurun "OK".
- Yuxarıdakı funksiyanın arqument pəncərəsi aktivdir. Bu pəncərənin sahələrində məlumatları operator arqumentləri pəncərəsinə daxil etdiyimiz şəkildə eyni şəkildə daxil edin TREND. Məlumat daxil olduqdan sonra düyməni vurun "OK".
- Məlumatların işlənməsi nəticəsi əvvəllər göstərilən hücrədə monitorda göstərilir. Gördüyünüz kimi, bu dəfə nəticə 4682,1 min rubl təşkil edir. Operator məlumatlarının işlənməsi nəticələrindən fərqlər TREND əhəmiyyətsiz, lakin onlar mövcuddur. Bu, bu vasitələrin fərqli hesablama metodlarından istifadə etməsi ilə əlaqədardır: xətti asılılıq metodu və eksponensial asılılıq metodu.
5-ci metod: LINEAR operatoru
Operator Xətt hesablamada xətti yaxınlaşma metodundan istifadə edir. Alətin istifadə etdiyi xətti asılılıq metodu ilə qarışdırılmamalıdır. TREND. Onun sintaksisi aşağıdakı kimidir:
= LINE (Məlum dəyərlər_y; məlum dəyərlər_x; yeni_values_x; [const]; [statistika])
Son iki arqument isteğe bağlıdır. İlk ikisi ilə əvvəlki üsullarla tanışıq. Ancaq yəqin ki, bu funksiyada yeni dəyərlərə işarə edən bir arqumentin olmadığını gördünüz. Fakt budur ki, bu vasitə yalnız dövr vahidi üzrə gəlirin dəyişməsini müəyyənləşdirir, bu da bizim vəziyyətimizə bir il bərabərdir, lakin operatorun hesablamasının nəticəsini son həqiqi mənfəət dəyərinə əlavə edərək ümumi nəticəni ayrıca hesablamalıyıq. Xəttil sayından dəfələrlə çoxdur.
- Hesablamanın aparılacağı hücrəni seçirik və Sihirbazı işlədirik. Ad seçin LINEIN kateqoriyasında "Statistik" düyməsini basın "OK".
- Sahədə Bilinən dəyərlər, açılmış dəlillər pəncərəsi, sütunun koordinatlarını daxil edin "Müəssisənin qazancı". Sahədə Məlum x Dəyərlər sütun ünvanını daxil edin "İl". Qalan sahələr boş qalır. Sonra düyməni vurun "OK".
- Proqram seçilmiş xanada xətti trend dəyərini hesablayır və göstərir.
- İndi 2019-cu il üçün proqnozlaşdırılan mənfəətin həcmini tapmalıyıq. İşarəni qoyun "=" vərəqdəki boş bir hücrəyə. Son tədris ili (2016) üçün faktiki mənfəət miqdarı olan hücrəni tıklayırıq. Bir işarə qoyduq "+". Sonra, əvvəlcədən hesablanmış xətti meyl olan hüceyrəni vurun. Bir işarə qoyduq "*". Tədqiqat dövrünün sonuncu ili (2016) ilə proqnoz vermək istədiyiniz il (2019) arasında üç ildən bir müddət qaldığından, hücrədə sayını təyin etdik "3". Hesablama etmək üçün düyməni vurun Daxil edin.
Gördüyünüz kimi, 2019-cu ildə xətti yaxınlaşma metodu ilə hesablanan proqnozlaşdırılan mənfəət həddi 4.614.9 min rubl təşkil edəcəkdir.
Metod 6: LGRFPPRIBLE operatoru
Baxacağımız son vasitə olacaq LGRFPPRIBLE. Bu operator hesablamaları eksponensial yaxınlaşma metodu əsasında aparır. Onun sintaksisi aşağıdakı quruluşa malikdir:
= LGRFPRIBLE (Məlum dəyərlər_y; məlum dəyərlər_x; yeni_values_x; [const]; [statistika])
Gördüyünüz kimi, bütün arqumentlər əvvəlki funksiyanın müvafiq elementlərini tamamilə təkrarlayır. Proqnoz hesablama alqoritmi biraz dəyişəcək. Funksiya bir dövr üçün, yəni bir il ərzində neçə dəfə gəlir həcminin dəyişəcəyini göstərən eksponent meylini hesablayır. Son həqiqi dövr və ilk planlaşdırılan dövr arasındakı mənfəət fərqini tapmalı, onu planlaşdırılmış dövrlərin sayına artırmalıyıq. (3) nəticəyə son həqiqi dövrün cəmini əlavə edin.
- Funksiya Sihirbazının operatorlarının siyahısında ad seçin LGRFPPRIBL. Düyməni vurun "OK".
- Mübahisə pəncərəsi başlayır. Bu funksiyanı istifadə edərək məlumatları tam olaraq etdiyimiz kimi daxil edirik Xətt. Düyməni vurun "OK".
- Eksponent meylinin nəticəsi hesablanır və təyin olunmuş hücrədə göstərilir.
- Bir işarə qoyduq "=" boş bir hücrəyə. Mötərizəni açın və son həqiqi dövr üçün gəlir dəyəri olan hücrəni seçin. Bir işarə qoyduq "*" və eksponent meyli olan hücrəni seçin. Bir mənfi işarəni qoyduq və son dövr üçün gəlir dəyərinin yerləşdiyi elementə yenidən vurun. Mötərizəni bağlayın və işarələrdə sürün "*3+" kotirovkalar olmadan. Yenə də son dəfə seçilmiş eyni hücrəni vurun. Hesablamanı həyata keçirmək üçün düyməni vurun Daxil edin.
Eksponensial yaxınlaşma üsulu ilə hesablanmış 2019-cu ildə proqnozlaşdırılan mənfəət miqdarı 4639,2 min rubl təşkil edəcəkdir ki, bu da əvvəlki hesablamada əldə olunan nəticələrdən bir o qədər də fərqlənmir.
Dərs: Excel-də digər statistik funksiyalar
Excel proqramında proqnozların necə veriləcəyini öyrəndik. Bu bir trend xəttinin köməyi ilə və bir sıra daxili statistik funksiyalardan istifadə etməklə qrafik olaraq edilə bilər. Bu operatorların eyni məlumatları emal etməsi nəticəsində fərqli bir nəticə əldə edilə bilər. Ancaq bu təəccüblü deyil, çünki hamısı fərqli hesablama metodlarından istifadə edirlər. Dalğalanma kiçik olsa, müəyyən bir işə tətbiq olunan bütün bu seçimlər nisbətən etibarlı hesab edilə bilər.
