პროგნოზირება ძალიან მნიშვნელოვანი ელემენტია საქმიანობის ნებისმიერ სფეროში, როგორიცაა ეკონომიკისა და საინჟინრო. ამ სფეროში სპეციფიკური პროგრამული უზრუნველყოფა არსებობს. სამწუხაროდ, ყველა მომხმარებელმა არ იცის, რომ ჩვეულებრივი Excel- ის პროცესორი თავის არსენალში მუშაობს პროგნოზირებისთვის, რაც მათი ეფექტურობაში პროფესიული პროგრამების უმრავლესობა არ არის. მოდით გაირკვეს, თუ რა არის ეს ინსტრუმენტები და როგორ გააკეთოთ პროგნოზი პრაქტიკაში.
პროგნოზირების პროცედურა
ნებისმიერი პროგნოზის მიზანი არის არსებული ტენდენციის იდენტიფიცირება და მოსალოდნელი შედეგის განსაზღვრა მომავალში გარკვეული დროის განმავლობაში შესწავლილი ობიექტის მიმართ.
მეთოდი 1: ტენდენციის ხაზი
ერთ-ერთი ყველაზე პოპულარული ტიპის გრაფიკული პროგნოზირება Excel- ში არის ექსტრაპოლაცია, რომელიც შესრულებულია ტენდენციის ხაზის მშენებლობით.
მოდით ვცადოთ, რომ საწარმოს მოგების პროგნოზირება 3 წლის განმავლობაში, წინა 12 წლის განმავლობაში ამ ინდიკატორის მონაცემებზე დაყრდნობით.
- ფუნქციონალის არგუმენტები და ღირებულებები, რომლებიც შედგება ცხრილის მონაცემებზე დაყრდნობით. ამისათვის აირჩიეთ tablespace, და შემდეგ, tab "ჩასმა"დააკლიკეთ სასურველი ტიპის დიაგრამის ხატი, რომელიც მდებარეობს ბლოკში "დიაგრამები". შემდეგ ჩვენ ვარჩევთ კონკრეტულ სიტუაციას. უმჯობესია აირჩიოთ scatter ჩარტში. თქვენ შეგიძლიათ აირჩიოთ განსხვავებული ხედი, მაგრამ შემდეგ, იმისათვის, რომ მონაცემები სწორად იქნას ნაჩვენები, თქვენ უნდა შეცვალოთ არგუმენტი ხაზი და აირჩიეთ ჰორიზონტალური ღერძის განსხვავებული მასშტაბი.
- ახლა ჩვენ უნდა ავაშენოთ ტენდენცია. ჩვენ სწორი დააწკაპუნეთ დიაგრამის ნებისმიერ წერტილში. გააქტიურებული კონტექსტური მენიუში, შეაჩერე შერჩევა პუნქტში "დამატება ტენდენციის ხაზი".
- ტენდენციის ხაზის ფორმატირების ფანჯარა იხსნება. შესაძლებელია ექვსი ტიპის დაახლოების არჩევის შესაძლებლობა:
- ხაზოვანი;
- ლოგარითმული;
- ექსპონენციალური;
- ძალაუფლება;
- პოლინომიული;
- ხაზოვანი ფილტრაცია.
დავიწყოთ წრფივი დაახლოება.
პარამეტრების ყუთში "პროგნოზი" სფეროში "თავდამსხმელი" მითითებული ნომერი "3,0", ვინაიდან სამი წლის წინ უნდა მივიღოთ პროგნოზი. გარდა ამისა, შეგიძლიათ შეამოწმოთ ჩანართები "დიაგრამის ჩვენება განტოლება" და "განათავსეთ სქემა ღირებულება სიზუსტის დაახლოება (R ^ 2)". ბოლო მაჩვენებელი გვიჩვენებს ხარისხის ხაზის ხარისხს. პარამეტრების გაკეთების შემდეგ დააჭირეთ ღილაკს. "დახურვა".
- ტენდენციის ხაზი აგებულია და ჩვენ შეგვიძლია გამოვიყენოთ ის სამი წლის შემდეგ მოგების სავარაუდო რაოდენობა. როგორც ხედავთ, იმ დროს უნდა გაიაროს 4,500 ათასი რუბლი. კოეფიციენტი R2, როგორც ზემოთ აღინიშნა, აჩვენებს ტენდენციის ხაზის ხარისხს. ჩვენს შემთხვევაში, ღირებულება R2 არის 0,89. უმაღლესი კოეფიციენტი, უფრო მაღალი საიმედოობა ხაზი. მისი მაქსიმალური მნიშვნელობა შეიძლება იყოს თანაბარი 1. ითვლება, რომ როდესაც თანაფარდობა დასრულდა 0,85 ტენდენციის ხაზი საიმედოა.
- თუ არ ხართ კმაყოფილი ნდობის დონეზე, მაშინ შეგიძლიათ დაბრუნდეთ ტენდენციის ხაზის ფორმატის ფანჯარაში და შეარჩიოთ ნებისმიერი სხვა ტიპის დაახლოება. თქვენ შეგიძლიათ სცადოთ ყველა შესაძლო ვარიანტის მოძებნა ყველაზე ზუსტი.
უნდა აღინიშნოს, რომ ეფექტური პროგნოზი ექსტრაპოლაციის გამოყენებით ტენდენციის ხაზის მეშვეობით შეიძლება იყოს თუ პროგნოზირების პერიოდი არ აღემატება გაანალიზებულ პერიოდის 30% -ს. ანუ 12 წლის განმავლობაში ანალიზში 3-4 წელიწადზე მეტი ეფექტიანი პროგნოზირება არ შეგვიძლია. მაგრამ ამ შემთხვევაშიც კი, შედარებით საიმედო იქნება, თუ ამ დროს არ იქნება ფორსმაჟორული ან, პირიქით, უკიდურესად ხელსაყრელი გარემოებები, რომლებიც წინა პერიოდებში არ ყოფილა.
გაკვეთილი: როგორ ავაშენოთ ტენდენცია ხაზი Excel- ში
მეთოდი 2: ოპერატორი FORECAST
ცხრილის მონაცემების ექსტრაპოლაცია შეიძლება გაკეთდეს სტანდარტული Excel ფუნქციით. FORECAST. ეს არგუმენტი ეკუთვნის სტატისტიკური ინსტრუმენტების კატეგორიას და აქვს შემდეგი სინტაქსი:
= PREDICT (x; known_y_y; ცნობილი values_x)
"X" არის არგუმენტი, ფუნქციის ღირებულება, რომლისთვისაც გსურთ განსაზღვრა. ჩვენს შემთხვევაში, არგუმენტი იქნება წელი, რომლისთვისაც უნდა გაკეთდეს პროგნოზი.
"ცნობილი Y ღირებულებები" ფუნქციის ცნობილი ღირებულებების ბაზა. ჩვენს შემთხვევაში, მისი როლი წინა წლების მოგებაა.
"ცნობილი x" - ეს არის არგუმენტები, რომლებიც შეესაბამება ფუნქციის ცნობილი ღირებულებებს. მათი როლი გვაქვს წლების ნუმერაცია, რისთვისაც ინფორმაცია შეგროვდა წინა წლების მოგებაზე.
ბუნებრივია, არგუმენტი აუცილებლად არ უნდა იყოს დრო. მაგალითად, ტემპერატურა შეიძლება იყოს და ფუნქციის ღირებულება შეიძლება იყოს წყლის გაფართოების დონე, როდესაც გაცხელებულია.
ამ მეთოდის გაანგარიშებისას ხაზოვანი რეგრესიის მეთოდი იყენებს.
მოდით შევხედოთ ოპერაციის ნიუანსებს FORECAST კონკრეტულ მაგალითზე. ერთი და იგივე მაგიდა. მოგვიწევს 2018 წლის მოგების პროგნოზი.
- აირჩიეთ ცარიელი უჯრა ფურცელზე, სადაც გსურთ გადამუშავების შედეგი. ჩვენ დააჭირეთ ღილაკს "ჩასმა ფუნქცია".
- ხსნის ფუნქციის ოსტატი. ამ კატეგორიაში "სტატისტიკური" აირჩიეთ სახელი "გაკვეთილი"და შემდეგ დააჭირეთ ღილაკს "OK".
- არგუმენტის ფანჯარა იწყება. სფეროში "X" განსაზღვროთ არგუმენტის მნიშვნელობა, რომელსაც გსურთ იპოვოთ ფუნქციის მნიშვნელობა. ჩვენს შემთხვევაში, ეს არის 2018. ამიტომ, ჩვენ ჩანაწერი "2018". მაგრამ უმჯობესია მიუთითოს ეს მაჩვენებელი საკანში ფურცელზე და სფეროში "X" უბრალოდ მისცეს ბმული მას. ეს საშუალებას მოგვცემს ავტომატურად გაანგარიშება კალკულაციები და საჭიროების შემთხვევაში მარტივად შეიცვალოს წელი.
სფეროში "ცნობილი Y ღირებულებები" დააკონკრეტეთ სვეტის კოორდინატები "საწარმოს მოგება". ეს შეიძლება გაკეთდეს კურსორის დარგში მინდორზე და შემდეგ მაუსის მარცხენა ღილაკზე დაჭერით და ფურცელზე შესაბამისი სვეტის შერჩევა.
ანალოგიურად სფეროში "ცნობილი x" ჩვენ მიუთითეთ სვეტის მისამართი "წელი" გასული პერიოდის მონაცემები.
მას შემდეგ, რაც ყველა ინფორმაცია შევიდა, დააჭირეთ ღილაკს. "OK".
- ოპერატორი ითვლის მონაცემების საფუძველზე და აჩვენებს შედეგს ეკრანზე. 2018 წლისთვის დაგეგმილია მოგება 4564.7 ათასი რუბლი. ცხრილის საფუძველზე, ჩვენ შეგვიძლია შევქმნათ გრაფა ჩარტის შექმნის საშუალებებით, რომლებიც ზემოთ განხილული იყო.
- თუ შეიცვალა წელიწადის საკანში, რომელიც გამოყენებულ იქნა არგუმენტში, შედეგი შეიცვლება შესაბამისად და გრაფა ავტომატურად განახლდება. მაგალითად, პროგნოზების მიხედვით 2019 წელს, მოგების ოდენობა 4637.8 ათასი რუბლი იქნება.
მაგრამ არ უნდა დაგვავიწყდეს, რომ, როგორც ტენდენციის ხაზის მშენებლობაში, პროგნოზირების დაწყებამდე დროის ხანგრძლივობა არ უნდა აღემატებოდეს მთელი პერიოდის 30% -ს, რისთვისაც მონაცემთა ბაზა დაგროვდა.
გაკვეთილი: Excel Extrapolation
მეთოდი 3: ოპერატორი TENDENCY
პროგნოზისთვის შეგიძლიათ გამოიყენოთ სხვა ფუნქცია - TREND. ის ასევე ეკუთვნის სტატისტიკურ ოპერატორთა კატეგორიას. მისი სინტაქსი ჰგავს ინსტრუმენტის სინტაქსს. FORECAST და ასე გამოიყურება:
= TREND (ცნობილი values_y; ცნობილი values_x; new_values_x; [const)]
როგორც ხედავთ, არგუმენტები "ცნობილი Y ღირებულებები" და "ცნობილი x" მთლიანად შეესაბამება ოპერატორის იგივე ელემენტებს FORECASTდა არგუმენტი "ახალი x ფასეულობები" შეესაბამება არგუმენტს "X" წინა ინსტრუმენტი. გარდა ამისა, TREND არსებობს დამატებითი არგუმენტი "მუდმივი"მაგრამ ეს არ არის სავალდებულო და გამოიყენება მხოლოდ იმ შემთხვევაში, თუ არსებობს მუდმივი ფაქტორები.
ეს ოპერატორი ყველაზე ეფექტურად გამოიყენება ფუნქციის ხაზოვანი დამოკიდებულებით.
ვნახოთ, როგორ მუშაობს ეს ინსტრუმენტი იგივე მონაცემების მასივთან ერთად. მიღებული შედეგების შედარების მიზნით, 2019 წელს განსაზღვრავს პროგნოზირების წერტილი.
- ჩვენ გავაკეთებთ საკანში დანიშნულება გამოხატოს შედეგი და აწარმოებს ფუნქციის ოსტატი ჩვეულ რეჟიმში. ამ კატეგორიაში "სტატისტიკური" იპოვეთ და აირჩიეთ სახელი "TREND". ჩვენ დააჭირეთ ღილაკს "OK".
- ოპერატორის არგუმენტი ფანჯარა იხსნება TREND. სფეროში "ცნობილი Y ღირებულებები" უკვე აღწერილია ზემოთ, შეიყვანეთ სვეტის კოორდინატები "საწარმოს მოგება". სფეროში "ცნობილი x" შეიტანეთ სვეტის მისამართი "წელი". სფეროში "ახალი x ფასეულობები" შეიტანეთ მითითება იმ საკანში, სადაც მითითებულია წელიწადის რაოდენობა, რომლის პროგნოზირებაც უნდა აღინიშნოს. ჩვენს შემთხვევაში, ეს არის 2019. საველე "მუდმივი" დატოვეთ ცარიელი. დააჭირეთ ღილაკს "OK".
- ოპერატორი ამუშავებს მონაცემებს და აჩვენებს შედეგს ეკრანზე. როგორც ხედავთ, საპროგნოზო მოგების ოდენობა 2019 წლისათვის, გათვლილია ხაზოვანი დამოკიდებულების მეთოდით, ისევე როგორც გაანგარიშების წინა მეთოდით 4637.8 ათასი რუბლი.
მეთოდი 4: GROWTH ოპერატორი
კიდევ ერთი ფუნქცია, რომელიც შეიძლება გამოყენებულ იქნას Excel- ში, არის GROWTH ოპერატორი. იგი ასევე მიეკუთვნება ინსტრუმენტების სტატისტიკურ ჯგუფს, მაგრამ განსხვავებით წინა პიროვნებებისგან, არ იყენებს ხაზოვანი დამოკიდებულების მეთოდს, მაგრამ გაანგარიშების საჩვენებელი მეთოდია. ამ ინსტრუმენტის სინტაქსი ასე გამოიყურება:
= GROWTH (ცნობილი Values_y; ცნობილი Values_x; New_values_x; [const])
როგორც ხედავთ, ამ ფუნქციის არგუმენტები ზუსტად განმეორდება ოპერატორის არგუმენტებს TRENDისე, რომ ჩვენ არ დავყოფთ მათი აღწერილობა მეორედ, მაგრამ დაუყოვნებლივ მივმართოთ გამოყენების ამ ინსტრუმენტში პრაქტიკაში.
- აირჩიეთ შედეგი გამომავალი უჯრედის და დაარქვით ჩვეულ რეჟიმში. ფუნქციის ოსტატი. სიაში სტატისტიკური ოპერატორები ეძებენ ნივთი "GROWTH"აირჩიეთ ეს და დააჭირეთ ღილაკს "OK".
- აღნიშნული ფუნქციის არგუმენტის ფანჯრის გააქტიურება ხდება. შეიყვანეთ მონაცემები ამ ფანჯრის ველში, ისევე როგორც ოპერატორთა არგუმენტში TREND. ინფორმაციის შესვლის შემდეგ დააჭირეთ ღილაკს "OK".
- მონაცემთა დამუშავების შედეგი გამოჩნდება მონიტორზე ადრე მითითებული საკანში. როგორც ხედავთ, ამ დროის შედეგია 4682.1 ათასი რუბლი. განსხვავებები ოპერატორის მონაცემების დამუშავება TREND უმნიშვნელო, მაგრამ ისინი ხელმისაწვდომია. ეს იმის გამო ხდება, რომ ეს ინსტრუმენტები გამოიყენებს გაანგარიშების სხვადასხვა მეთოდებს: ხაზოვანი დამოკიდებულების მეთოდი და გაფართოებაზე დამოკიდებულების მეთოდი.
მეთოდი 5: LINEST ოპერატორი
ოპერატორი LINE როდესაც გაანგარიშება იყენებს ხაზოვანი დაახლოების მეთოდის გამოყენებას. ეს არ უნდა იყოს დაბნეული ინსტრუმენტის მიერ გამოყენებული წრფივი მეთოდით. TREND. მისი სინტაქსია:
= LINEST (ცნობილი Values_y; ცნობილი Values_x; New_values_x; [სტატისტიკა]; [სტატისტიკა])
ბოლო ორი არგუმენტი არის სურვილისამებრ. ჩვენ პირველ ორ მეთოდს ვიცნობთ. მაგრამ ალბათ შენიშნა, რომ ამ ფუნქციაში არ არსებობს არგუმენტი ახალი ღირებულებების მითითებით. ფაქტია, რომ ეს ინსტრუმენტი მხოლოდ განსაზღვრავს შემოსავლების ცვლილებას პერიოდის ერთეულში, რომელიც ჩვენს შემთხვევაში ერთი წელია, მაგრამ ჩვენ უნდა გამოვთვალოთ საერთო შედეგი ცალ-ცალკე და ბოლო ფაქტობრივი მოგების ღირებულების დამატება ოპერატორის გაანგარიშების შედეგის შესახებ LINEგამრავლებული წლების მიხედვით.
- გააკეთეთ შერჩევა საკანში, რომელშიც გაანგარიშება შესრულდება და დაიწყება ფუნქციების მაგისტრი. აირჩიეთ სახელი "LINEYN" კატეგორიაში "სტატისტიკური" და დააჭირეთ ღილაკს "OK".
- სფეროში "ცნობილი Y ღირებულებები"არგუმენტის ფანჯრის გახსნისას, შეიტანეთ სვეტის კოორდინატები "საწარმოს მოგება". სფეროში "ცნობილი x" შეიტანეთ სვეტის მისამართი "წელი". დარჩენილი ველი ცარიელია. შემდეგ დააჭირეთ ღილაკს "OK".
- პროგრამა განსაზღვრავს და აჩვენებს შერჩეულ საკანში ხაზგარეშე ტენდენციის მნიშვნელობას.
- ახლა, 2019 წლის მოგების ღირებულების გასარკვევად უნდა გავიგოთ. დააყენეთ ნიშანი "=" ნებისმიერი ცარიელი საკანში ფურცელზე. დააჭირეთ საკანში, რომელიც შეიცავს გასული წლის მოგების ფაქტობრივ მოგებას (2016). ჩვენ ვახსენით ნიშანი "+". შემდეგი, დააჭირეთ საკანში, რომელიც შეიცავს ადრე გამოითვლება ხაზოვანი ტენდენცია. ჩვენ ვახსენით ნიშანი "*". შესწავლილი პერიოდის (2016 წ) და გასული წლის (2019 წ) პროგნოზს შორის, მას შემდეგ, რაც სამი წელია მდგომარეობს, "3". გამოთვლა, დააჭირეთ ღილაკს. შეიყვანეთ.
მოგეხსენებათ, მოგების პროგნოზირებული ღირებულება 2019 წელს, წრფივი დაახლოების მეთოდით გამოითვლება 4614.9 ათასი რუბლი.
მეთოდი 6: ლოგოს ოპერატორი
უკანასკნელი ინსტრუმენტი, რომელსაც ჩვენ განვიხილავთ, არის LGGRPRIBL. ეს ოპერატორი ასრულებს კალკულაციებს ეფექტურობის დაახლოების მეთოდის საფუძველზე. მისი სინტაქსი აქვს შემდეგი სტრუქტურა:
= LOGPLPR (ცნობილი values_y; ცნობილი values_x; new_values_x; [const)]; [სტატისტიკა])
როგორც ხედავთ, ყველა არგუმენტი მთლიანად განმეორდება წინა ფუნქციის შესაბამის ელემენტებს. პროგნოზის გაანგარიშების ალგორითმი ოდნავ შეცვლის. ფუნქცია აანგარიშებს ექსპონენციურ ტენდენციას, რომელიც აჩვენებს რამდენჯერ რამდენჯერმე შემოსავლის ოდენობა შეიცვლება ერთ პერიოდში, ანუ ერთი წლის განმავლობაში. ჩვენ გვჭირდება მოგება სხვაობა მოგება-გასული პერიოდისა და პირველი დაგეგმილი პერიოდის განმავლობაში, გამრავლებისთვის დაგეგმილი პერიოდების რაოდენობით. (3) და დაამატოთ შედეგი საბოლოო პერიოდის თანხაზე.
- ფუნქციის ოსტატის ოპერატორთა სიაში შეარჩიეთ სახელი LGRFPRIBL. დააჭირეთ ღილაკს. "OK".
- არგუმენტის ფანჯარა იწყება. მასში შედის მონაცემები ზუსტად ისე, როგორც ეს გააკეთა გამოყენებით ფუნქცია LINE. დააჭირეთ ღილაკს "OK".
- განზომილებიანი ტენდენციის შედეგი გამოითვლება და ნაჩვენებია ნაჩვენები საკანში.
- ჩვენ ვახსენით ნიშანი "=" ცარიელი საკანში. გახსენით ფრჩხილები და აირჩიეთ საკანში, რომელიც შეიცავს რეალურ ღირებულებას უკანასკნელ ფაქტობრივ პერიოდში. ჩვენ ვახსენით ნიშანი "*" და აირჩიეთ დიაპაზონის შემცველი საკანში. ჩვენ დავამატებთ მინუს სიმბოლოს და კვლავ დააჭირეთ იმ ელემენტებს, რომელშიც არის ბოლო პერიოდის შემოსავლის ოდენობა. დახურვა bracket და მართოს სიმბოლო. "*3+" გარეშე შეთავაზებები. კვლავ დააჭირეთ ერთსა და იმავე საკანში, რომელიც ბოლო დროს შეირჩა. გაანგარიშების ღილაკზე დაჭერით შეიყვანეთ.
2019 წელს მოგების წინასწარ განსაზღვრული ოდენობა, რომელიც გამოითვლება ექსპანსიონალური დაახლოების მეთოდით, იქნება 4,639,2 ათასი რუბლი, რომელიც კვლავ არ განსხვავდება წინა მეთოდებით გაანგარიშებული შედეგებისგან.
გაკვეთილი: სხვა სტატისტიკური ფუნქციები Excel- ში
გაირკვა, თუ როგორ უნდა გამოვიყენოთ Excel- ის პროგნოზირება. გრაფიკულად, ეს შეიძლება გაკეთდეს გამოყენებით ტენდენციის ხაზი, და ანალიზური გამოყენებით რაოდენობის ინტეგრირებული სტატისტიკური ფუნქციები. ამ ოპერატორების მიერ იდენტური მონაცემების დამუშავების შედეგად შეიძლება განსხვავებული შედეგი იყოს. მაგრამ ეს გასაკვირი არ არის, რადგან ყველა მათგანს გაანგარიშების სხვადასხვა მეთოდები იყენებს. თუ მერყეობა მცირეა, მაშინ კონკრეტული საქმისთვის ყველა ეს ვარიანტი შეიძლება შედარებით სანდო იყოს.
