Proqnozlaşdırma iqtisadiyyatdan mühəndisliyə qədər dəyişən demək olar ki, hər hansı fəaliyyət sahəsində çox vacib elementdir. Bu sahədə ixtisaslaşan çox sayda proqram var. Təəssüf ki, bütün istifadəçilər adi Excel elektron cədvəl prosessorunun proqnozlaşdırma aparmaq üçün arsenal vasitələrində olduğunu bilirlər ki, onların effektivliyi peşəkar proqramlardan daha aşağı deyil. Bu alətlərin nə olduğunu və praktikada necə bir proqnoz hazırlamaq edək.
Proqnozlaşdırma proseduru
Hər hansı bir proqnozun məqsədi mövcud tendensiyanı müəyyən etmək və gələcəkdə müəyyən bir nöqtədə tədqiq olunan obyektlə bağlı gözlənilən nəticəni müəyyən etməkdir.
Metod 1: trend xətti
Excel-də qrafik proqnozların ən populyar növlərindən biri trend xəttini qurmaqla həyata keçirilən bir ekstrapolyasiyadır.
Əvvəlki 12 il ərzində bu göstərici üzrə məlumatlar əsasında müəssisənin mənfəətinin 3 il ərzində proqnozlaşdırılmasına çalışaq.
- Funksiyanın dəlilləri və dəyərlərindən ibarət olan cədvəlli məlumatlara əsaslanan asılılıq grafiğini yaradır. Bunu etmək üçün, masa aralığını seçin və sonra sekmede ol "Əlavə et", blokda olan istənilən diaqramın simgesini basın "Şəkillər". Sonra xüsusi vəziyyət üçün müvafiq növ seçirik. Dağıdıcı bir chart seçmək ən yaxşısıdır. Fərqli bir görünüş seçə bilərsiniz, ancaq sonra məlumatın düzgün göstərilməsini təmin etmək üçün, xüsusən, dəlilləndirmə xəttini düzəltməlisiniz və üfqi oxun fərqli bir ölçüsünü seçə bilərsiniz.
- İndi bir trend xətti qurmalıyıq. Diaqramdakı hər hansı bir nöqtəyə sağ tıklayaq. Aktivləşdirilmiş kontekst menyusundan maddə üzrə seçim dayandırın "Trend xəttinə əlavə et".
- Trend xəttini formatlaşdırma pəncərəsi açılır. Altı növü uyğunlaşdırmanın birini seçmək mümkündür:
- Xətti;
- Logarithmic;
- Exponential;
- Güc;
- Polinomial;
- Lineer filtrasiya.
Doğrusal bir təxminlə başlayaq.
Ayarlar qutusunda "Proqnoz" sahəsində "İrəli" nömrə təyin edin "3,0"Çünki önümüzdəki üç il üçün proqnoz vermək lazımdır. Ayrıca, onay kutularını da kontrol edə bilərsiniz "Şemada bərabərliyi göstər" və "Dəyişmənin düzgünlüyünün dəyərini (R ^ 2) qoyun". Son göstərici trend xəttinin keyfiyyətini göstərir. Ayarlar edildikdən sonra düyməni basın. "Yaxın".
- Trend xətti tikilir və üç ildən sonra mənfəətin təxminən məbləğini müəyyən etmək üçün istifadə edə bilərik. Gördüyünüz kimi, həmin vaxt 4,500 min rubl keçməlidir. Katsayısı R2, yuxarıda göstərildiyi kimi, trend xəttinin keyfiyyətini əks etdirir. Bizim vəziyyətdə dəyər R2 qədərdir 0,89. Katsayının nə qədər yüksək olduğu, xəttin etibarlılığı nə qədər yüksəkdir. Onun maksimum dəyəri bərabər ola bilər 1. Bu nisbət başa çatdıqda hesab olunur 0,85 trend xətti etibarlıdır.
- Etibarlılıq səviyyəsindən razı qalmazsanız, trend xətti formatı pəncərəsinə qayıda və hər hansı digər uyğunluq tipini seçə bilərsiniz. Ən doğru tapmaq üçün bütün mövcud variantları sınayabilirsiniz.
Qeyd edək ki, proqnoz dövrü təhlil olunan dövr bazasının 30% -dən artıq olmamaqla, trend xəttindən keçməklə ekstrapolyasiya istifadə edərək effektiv proqnoz ola bilər. Yəni, 12 illik dövrün təhlilində, 3-4 ildən çox müddətə effektiv proqnoz vermirik. Hətta bu vəziyyətdə belə bir dövrün fəlsəfəsi və ya əvvəlki dövrlərdə olmayan olduqca əlverişli hallar olmayacağı təqdirdə nisbətən etibarlı olacaq.
Dərs: Excel-də bir trend xəttinin qurulması
Metod 2: operatorun qiyməti
Cədvəlli məlumatlar üçün ekstrapolyasiya standart Excel funksiyası vasitəsilə edilə bilər. YAXIN. Bu arqument statistik vasitələr kateqoriyasına aiddir və aşağıdakı sözdizimə malikdir:
= PREDICT (x; tanınmış_y_y; bilinən dəyərlər_x)
"X" bir arqument, təyin etmək istədiyinin funksiyasının dəyəri. Bizim halda, mübahisə proqnozun verilməsi üçün olan il olacaqdır.
"Bilinən Y dəyərləri" - funksiyanın məlum dəyərlərinin bazası. Bizim halda, onun rolu əvvəlki dövrlər üçün mənfəət məbləğidir.
"Məlum x" - Bunlar funksiyanın məlum dəyərlərinə uyğun olan arqumentlərdir. Öz rollarında biz əvvəlki illərdə əldə edilən məlumatlar haqqında məlumatların yığılmasına dair illərdir.
Təbii ki, mübahisə mütləq bir zaman dövrü olmamalıdır. Məsələn, istilik ola bilər və funksiyanın dəyəri qızdırıldığı zaman suyun genişlənmə səviyyəsidir.
Bu metodu hesablayarkən doğrusal regression üsulunu istifadə edir.
Operatorun nüanslarına baxaq YAXIN xüsusi bir nümunə. Bütün eyni masa keçir. 2018-ci il üçün mənfəət proqnozunu bilməliyik.
- İşlənmənin nəticəsini göstərmək istədiyiniz cədvəldə boş hüceyrəni seçin. Düyməni basırıq "Əlavə funksiyası".
- Açılıyor Function Sihirbazı. Kateqoriyada "Statistik" adını seçin "XALQ"sonra düyməni basın "OK".
- Mübahisəli pəncərə başlayır. Sahədə "X" funksiyanın dəyərini tapmaq istədiyiniz mübahisənin dəyərini müəyyənləşdirin. Bizim vəziyyətimizdə bu 2018-dir. Buna görə də bir qeyd edirik "2018". Ancaq bu göstəricini təbəqənin və sahənin hüceyrəsində göstərmək daha xeyirlidir "X" yalnız bir link verin. Bu gələcəkdə hesablamaları avtomatlaşdırmağa imkan verir və lazım olduqda ilin asanlıqla dəyişir.
Sahədə "Bilinən Y dəyərləri" sütunun koordinatlarını təyin "Müəssisənin mənfəəti". Bu, kursorun sahəyə yerləşdirilməsi və sonra sol siçan düyməsini basıb və hesabatın müvafiq sütunu seçilərək edilə bilər.
Eləcə də sahədə "Məlum x" sütun ünvanı daxil edəcəyik "İl" keçmiş dövrə aid məlumatlarla.
Bütün məlumatlar girildikdən sonra düyməni basın. "OK".
- Operator daxil olan məlumatlar əsasında hesablayır və nəticəni ekranda göstərir. 2018-ci ildə gəlir 4564,7 min rubl təşkil edir. Nəticədə cədvəl əsasında, biz yuxarıda müzakirə edilmiş chart yaradılması vasitələrini istifadə edərək, bir grafik qura bilərik.
- Dəyişikliyə daxil olmaq üçün istifadə edilən hüdudda il dəyişirsinizsə, nəticənin nəticəsi dəyişəcək və grafiğin avtomatik olaraq yenilənməsi gözlənilir. Məsələn, 2019-cu ildə proqnozlara görə, mənfəətin məbləği 4637,8 min rubl olacaq.
Lakin unutmayın ki, trend xəttinin tikintisində olduğu kimi, proqnoz müddətindən əvvəlki müddət məlumat bazasının yığılmasının bütün müddətinin 30% -dən artıq olmamalıdır.
Dərs: Excel Extrapolation
Metod 3: operator TENDENCY
Proqnoz üçün başqa bir funksiyadan istifadə edə bilərsiniz - TREND. Statistika operatorları kateqoriyasına aiddir. Onun sözdizimi alətin sintaksisinə çox oxşardır. YAXIN və belə görünür:
= TREND (Bilinen dəyərlər_y; bilinən dəyərlər_x; yeni_values_x; [const])
Gördüyünüz kimi, arqumentlər "Bilinən Y dəyərləri" və "Məlum x" operatorun eyni elementlərinə tamamilə cavab verir YAXINvə arqument "Yeni x dəyərlər" ardıcıllıqla uyğunlaşır "X" əvvəlki alət. Bundan əlavə, TREND Əlavə bir arqument var "Sabit"lakin məcburi deyil və yalnız daimi amillər olduğu halda istifadə olunur.
Bu operator funksiyanın xətti asılılığının mövcud olması ilə ən effektiv istifadə olunur.
Bu alətin eyni məlumat array ilə necə işlədiyini görək. Əldə edilmiş nəticələri müqayisə etmək üçün 2019-cu ildə proqnozlaşdırma nöqtəsini təyin edirik.
- Nəticəni göstərmək və işləmək üçün bir hüceyrə təyin edirik Function Sihirbazı adi şəkildə. Kateqoriyada "Statistik" adı tapmaq və seçin "TREND". Düyməni basırıq "OK".
- Operator arqumenti pəncərəsi açılır TREND. Sahədə "Bilinən Y dəyərləri" artıq yuxarıda təsvir edilən sütunun koordinatlarını daxil edin "Müəssisənin mənfəəti". Sahədə "Məlum x" sütunun ünvanını daxil edin "İl". Sahədə "Yeni x dəyərlər" proqnoz göstərilmək istənilən ilin nömrəsinin olduğu yerə istinad edin. Bizim vəziyyətimizdə 2019-cu ildir. Alan "Sabit" boş buraxın. Düyməni basın "OK".
- Operator məlumatları işləyir və nəticəni ekranda göstərir. Gördüyünüz kimi, proqnozlaşdırılan mənfəət 2019-cu ildə xətti asılılıq metodu ilə hesablanır, əvvəlki hesablama metodunda olduğu kimi, 4637,8 min rubl olacaq.
4-cü üsul: GROWTH operatoru
Excel-də proqnozlaşdırmaq üçün istifadə oluna biləcək bir funksiya BÜTÜN operatorudur. Həm də alətlərin statistik qrupuna aiddir, lakin, əvvəlki kimi fərqli olaraq, xətti asılılıq metodunu deyil, hesablama üçün exponential üsulunu istifadə etmir. Bu alətin sintaksisi bu kimi görünür:
= GROWTH (Məlum Dəyərlər_y; Məlum Dəyərlər_x; New_values_x; [const])
Gördüyünüz kimi, bu funksiyanın arqumentləri operatorun dəlillərini təkrarlayır TRENDbelə ki, onların təsvirlərinə ikinci dəfə toxunmayacağıq, ancaq dərhal bu vasitənin tətbiqinə müraciət edəcəyik.
- Nəticə çıxışı hüceyrəsini seçin və adi şəkildə çağırın. Function Sihirbazı. Statistika operatorları siyahısında maddə axtarır "BÖYÜK"seçin və düyməsini basın "OK".
- Yuxarıda göstərilən funksiyanın argüman pəncərəsinin aktivləşdirilməsi baş verir. Bu pəncərənin sahələrinə daxil olan məlumatları operatorun arqument pəncərəsinə daxil etdiklərimizlə tamamilə eynidır TREND. Məlumat girildikdən sonra düyməni basın "OK".
- Məlumatların işlənməsinin nəticəsi əvvəlcədən təyin edilmiş hüceyrədə olan monitorda göstərilir. Gördüyünüz kimi, bu dəfə nəticə 4682,1 min rubl. Operator məlumatlarının emalından fərqlər TREND əhəmiyyətsiz, ancaq mövcuddur. Bu, bu vasitələrin müxtəlif hesablama metodlarından istifadə etməsi ilə bağlıdır: lineer asılılıq üsulu və eksponentlərin asılılığı metodu.
Metod 5: DƏSTƏK operatoru
Operator LINE hesablama zamanı lineer yaxınlaşdırma metodundan istifadə edir. Alət tərəfindən istifadə edilən xətti üsulla qarışdırılmamalıdır. TREND. Onun sözdizimi:
= DİZAYN (Məlum Dəyərlər_y; Məlum Dəyərlər_x; New_values_x; [const]; [Statistika])
Son iki arqument opsiyoneldir. Əvvəlki üsullarla əvvəlcədən tanış olduq. Amma ehtimal ki, bu funksiyada yeni dəyərlərə işarə edən heç bir mübahisə yoxdur. Əslində, bu alət yalnız bizim dövrümüzdə bir il olduğu dövr vahidi üçün gəlir dəyişikliyini müəyyənləşdirir, lakin sonuncu faktiki mənfəət dəyərinə operatorun hesablanması nəticəsində əlavə olunaraq ümumi nəticəni ayrı-ayrı hesablamalıyıq LINEillər sayına çarpdı.
- Hesablamanın həyata keçiriləcəyi və Master funksiyalarını işə saldığı hüceyrənin bir seçimini edin. Adı seçin "LİNEYN" kateqoriyasında "Statistik" və düyməni basın "OK".
- Sahədə "Bilinən Y dəyərləri"açılan argüman penceresinin sütunun koordinatlarını daxil edin "Müəssisənin mənfəəti". Sahədə "Məlum x" sütunun ünvanını daxil edin "İl". Qalan sahələr boş qalır. Sonra düyməni basın "OK".
- Proqram seçilmiş hüceyrədə lineer trendin dəyərini hesablayır və göstərir.
- İndi proqnozlaşdırılan mənfəətin 2019-cu il üçün dəyərini öyrənməliyik. İşarəni seçin "=" hesabatı hər hansı bir boş hüceyrə. Öyrənilən keçən il (2016) faktiki mənfəət məbləği olan hüceyrəni basın. Bir işarə qoyduq "+". Daha sonra, daha əvvəl hesablanmış doğrusal trend ehtiva edən hüceyrəni basın. Bir işarə qoyduq "*". Tədqiqatın son dövrü (2016) və proqnozun (2019) hazırlanacağı ildən etibarən, üç il müddətinə çatdıqdan sonra, "3". Hesablamaq üçün düyməni basın. Daxil edin.
Gördüyünüz kimi, 2019-cu ildə xətti uyğunlaşdırma üsulu ilə hesablanan mənfəətin proqnozlaşdırılan dəyəri 4614,9 min rubl olacaq.
Metod 6: LOST operator
Biz hesab edəcəyimiz son vasitədir LGGRPRIBL. Bu operator exponential approximation metoduna əsasən hesablamalar aparır. Onun sintaksisində aşağıdakı struktur var:
= LOGPLPR (Bilinir dəyərlər_y; tanınmış dəyərlər_x; new_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. Proqnozlaşdırma üçün alqoritm bir qədər dəyişəcək. Bu funksiya bir dövrdə, yəni bir ildə bir neçə dəfə gəlir məbləğinin necə dəyişəcəyini göstərəcək sərhəd trendini hesablayır. Sonuncu faktiki dövrlə ilk planlaşdırılan arasındakı mənfəət fərqini tapmaq lazımdır, onu planlaşdırılan dövrlər sayına görə çoxaldacağam. (3) nəticəyə son faktiki dövrün cəmini əlavə edin.
- Function Sihirbazı operatorlarının siyahısında adı seçin LGRFPRIBL. Düyməni basın. "OK".
- Mübahisəli pəncərə başlayır. Burada funksiyanı istifadə edərək, məlumatları tam olaraq daxil edəcəyik LINE. Düyməni basın "OK".
- Üslub trendinin nəticəsi göstərilən hüceyrədə hesablanır və göstərilir.
- Bir işarə qoyduq "=" boş bir hüceyrədə. Mötərizəni açın və son faktiki dövr üçün gəlir dəyərini ehtiva edən hüceyrəni seçin. Bir işarə qoyduq "*" və üstel trendi olan hüceyrəni seçin. Bir ekssus əlaməti qoyduq və son dövrdə gəlir məbləği olan elementə yenidən basın. Braketi bağlayın və simvolları sürüşdürün. "*3+" quotes olmadan. Yenə son dəfə seçilmiş eyni hüceyrəni basın. Hesablama üçün düyməni basın Daxil edin.
Eksponensial uyğunlaşdırma metodu ilə hesablanan 2019-cu ildə gözlənilən mənfəətin məbləği 4,639,2 min rubl olacaqdır ki, bu da əvvəlki üsullarla hesablamada əldə edilən nəticələrdən çox fərqlənmir.
Dərs: Excel-də digər statistik funksiyalar
Excel proqramında bir proqnoz necə ediləcəyini öyrənirik. Qrafik olaraq, bu, trend xəttinin tətbiqi və analitik olaraq bir sıra daxili statistik funksiyaları istifadə etməklə həyata keçirilə bilər. Bu operatorların eyni məlumatların emalı nəticəsində fərqli bir nəticə ola bilər. Amma bu, təəccüblü deyil, çünki hamısı hesablaşmanın müxtəlif üsullarından istifadə edirlər. Dəyişiklik kiçik olduqda, müəyyən bir vəziyyətə tətbiq olunan bütün bu variantlar nisbətən etibarlı hesab edilə bilər.
