LSM ორი ცვლადის ფუნქციისთვის. ექსპერიმენტული მონაცემების დაახლოება. მინიმალური კვადრატის მეთოდი. LSM-ის პრაქტიკული განხორციელება არაპროგრამირებად კალკულატორზე წრფივი დამოკიდებულებისთვის

მაგალითი.

ექსპერიმენტული მონაცემები ცვლადების მნიშვნელობებზე Xდა ზემოცემულია ცხრილში.

მათი გასწორების შედეგად ფუნქცია

გამოყენება მინიმალური კვადრატის მეთოდი, მიახლოებით ამ მონაცემებს წრფივი დამოკიდებულებით y=ax+b(იპოვეთ პარამეტრები ადა ბ). გაარკვიეთ, ორი ხაზიდან რომელია უკეთესი (უმცირესი კვადრატების მეთოდის გაგებით) ასწორებს ექსპერიმენტულ მონაცემებს. გააკეთე ნახატი.

უმცირესი კვადრატების მეთოდის არსი (LSM).

პრობლემა არის წრფივი დამოკიდებულების კოეფიციენტების პოვნა, რომლებისთვისაც ორი ცვლადის ფუნქციაა ადა ბ იღებს უმცირეს მნიშვნელობას. ანუ მონაცემების გათვალისწინებით ადა ბნაპოვნი სწორი ხაზიდან ექსპერიმენტული მონაცემების კვადრატული გადახრების ჯამი ყველაზე მცირე იქნება. ეს არის უმცირესი კვადრატების მეთოდის მთელი აზრი.

ამრიგად, მაგალითის ამოხსნა მცირდება ორი ცვლადის ფუნქციის უკიდურესობის პოვნამდე.

კოეფიციენტების მოძიების ფორმულების გამოყვანა.

შედგენილია და ამოხსნილია ორი განტოლების სისტემა ორი უცნობით. ცვლადების მიმართ ფუნქციის ნაწილობრივი წარმოებულების მოძიება ადა ბ, ამ წარმოებულებს ვატოლებთ ნულს.

მიღებულ განტოლებათა სისტემას ვხსნით ნებისმიერი მეთოდით (მაგ ჩანაცვლების მეთოდიან ) და მიიღეთ ფორმულები კოეფიციენტების მოსაძებნად უმცირესი კვადრატების მეთოდის (LSM) გამოყენებით.

მონაცემებით ადა ბფუნქცია იღებს უმცირეს მნიშვნელობას. ამ ფაქტის მტკიცებულება მოცემულია.

ეს არის უმცირესი კვადრატების მთელი მეთოდი. პარამეტრის პოვნის ფორმულა აშეიცავს ჯამებს , , და პარამეტრს ნ- ექსპერიმენტული მონაცემების რაოდენობა. ამ თანხების მნიშვნელობები რეკომენდებულია ცალკე გამოითვალოს. კოეფიციენტი ბნაპოვნია გაანგარიშების შემდეგ ა.

დროა გავიხსენოთ ორიგინალური მაგალითი.

გამოსავალი.

ჩვენს მაგალითში n=5. ჩვენ ვავსებთ ცხრილს იმ თანხების გამოთვლის მოხერხებულობისთვის, რომლებიც შედის საჭირო კოეფიციენტების ფორმულებში.

ცხრილის მეოთხე მწკრივის მნიშვნელობები მიიღება მე -2 რიგის მნიშვნელობების გამრავლებით მე -3 რიგის მნიშვნელობებზე თითოეული ნომრისთვის. მე.

ცხრილის მეხუთე მწკრივის მნიშვნელობები მიიღება მე-2 რიგის მნიშვნელობების კვადრატში თითოეული ნომრისთვის. მე.

ცხრილის ბოლო სვეტის მნიშვნელობები არის მნიშვნელობების ჯამები რიგებში.

კოეფიციენტების საპოვნელად ვიყენებთ უმცირესი კვადრატების მეთოდის ფორმულებს ადა ბ. ჩვენ მათში ვცვლით შესაბამის მნიშვნელობებს ცხრილის ბოლო სვეტიდან:

აქედან გამომდინარე, y=0.165x+2.184არის სასურველი მიახლოებითი სწორი ხაზი.

რჩება იმის გარკვევა, თუ რომელი სტრიქონი y=0.165x+2.184ან უკეთ აახლოებს თავდაპირველ მონაცემებს, ანუ შეაფასოს უმცირესი კვადრატების მეთოდის გამოყენებით.

უმცირესი კვადრატების მეთოდის ცდომილების შეფასება.

ამისათვის თქვენ უნდა გამოთვალოთ თავდაპირველი მონაცემების კვადრატული გადახრების ჯამები ამ ხაზებიდან და , უფრო მცირე მნიშვნელობა შეესაბამება ხაზს, რომელიც უკეთ უახლოვდება თავდაპირველ მონაცემებს უმცირესი კვადრატების მეთოდის მიხედვით.

მას შემდეგ, ხაზი y=0.165x+2.184უკეთ აახლოებს თავდაპირველ მონაცემებს.

უმცირესი კვადრატების მეთოდის გრაფიკული ილუსტრაცია (LSM).

ჩარტებზე ყველაფერი მშვენივრად გამოიყურება. წითელი ხაზი არის ნაპოვნი ხაზი y=0.165x+2.184, ლურჯი ხაზი არის , ვარდისფერი წერტილები ორიგინალური მონაცემებია.

რისთვის არის ეს, რისთვის არის ყველა ეს მიახლოება?

მე პირადად ვიყენებ მონაცემთა გასწორების პრობლემების გადასაჭრელად, ინტერპოლაციისა და ექსტრაპოლაციის ამოცანების გადასაჭრელად (პირველ მაგალითში შეიძლება გთხოვოთ დაკვირვებული მნიშვნელობის მნიშვნელობის პოვნა წზე x=3ან როდის x=6 MNC მეთოდის მიხედვით). მაგრამ ამის შესახებ უფრო მეტს მოგვიანებით ვისაუბრებთ საიტის სხვა განყოფილებაში.

მტკიცებულება.

ისე რომ როცა იპოვეს ადა ბფუნქცია იღებს უმცირეს მნიშვნელობას, აუცილებელია, რომ ამ ეტაპზე ფუნქციისთვის მეორე რიგის დიფერენციალური კვადრატული ფორმის მატრიცა დადებითი იყო გარკვეული. ვაჩვენოთ.

მას აქვს მრავალი პროგრამა, რადგან ის იძლევა მოცემული ფუნქციის მიახლოებით წარმოდგენას სხვა უფრო მარტივი ფუნქციებით. LSM შეიძლება იყოს ძალიან სასარგებლო დაკვირვებების დამუშავებისას და ის აქტიურად გამოიყენება ზოგიერთი სიდიდის შესაფასებლად სხვათა გაზომვის შედეგებიდან, რომლებიც შეიცავს შემთხვევით შეცდომებს. ამ სტატიაში თქვენ შეისწავლით თუ როგორ უნდა განახორციელოთ მინიმალური კვადრატების გამოთვლები Excel-ში.

პრობლემის განცხადება კონკრეტულ მაგალითზე

დავუშვათ, რომ არსებობს ორი ინდიკატორი X და Y. უფრო მეტიც, Y დამოკიდებულია X-ზე. ვინაიდან OLS ჩვენთვის საინტერესოა რეგრესიული ანალიზის თვალსაზრისით (ექსელში მისი მეთოდები დანერგილია ჩაშენებული ფუნქციების გამოყენებით), დაუყოვნებლივ უნდა გავაგრძელოთ განიხილოს კონკრეტული პრობლემა.

ასე რომ, მოდით X იყოს სასურსათო მაღაზიის გასაყიდი ფართობი, რომელიც იზომება კვადრატულ მეტრებში, ხოლო Y იყოს წლიური ბრუნვა, რომელიც განისაზღვრება მილიონ რუბლში.

საჭიროა პროგნოზის გაკეთება, თუ რა ბრუნვა (Y) ექნება მაღაზიას, თუ მას აქვს ამა თუ იმ საცალო ფართი. ცხადია, ფუნქცია Y = f (X) იზრდება, ვინაიდან ჰიპერმარკეტი უფრო მეტ საქონელს ყიდის, ვიდრე სადგომი.

რამდენიმე სიტყვა წინასწარმეტყველებისთვის გამოყენებული საწყისი მონაცემების სისწორის შესახებ

ვთქვათ, გვაქვს n მაღაზიის მონაცემებით აგებული ცხრილი.

მათემატიკური სტატისტიკის მიხედვით, შედეგები მეტ-ნაკლებად სწორი იქნება, თუკი მინიმუმ 5-6 ობიექტის მონაცემები შეისწავლება. ასევე, „ანომალიური“ შედეგების გამოყენება შეუძლებელია. კერძოდ, ელიტარულ პატარა ბუტიკს შეიძლება ჰქონდეს მრავალჯერ მეტი ბრუნვა, ვიდრე "მასმარკეტის" კლასის მსხვილი მაღაზიების ბრუნვა.

მეთოდის არსი

ცხრილის მონაცემები შეიძლება გამოისახოს დეკარტის სიბრტყეზე, როგორც წერტილები M 1 (x 1, y 1), ... M n (x n, y n). ახლა ამოცანის ამოხსნა დაიყვანება y = f (x) მიახლოებითი ფუნქციის არჩევით, რომელსაც აქვს გრაფიკი რაც შეიძლება ახლოს გადის M 1, M 2, .. M n წერტილებთან.

რა თქმა უნდა, შეგიძლიათ გამოიყენოთ მაღალი ხარისხის პოლინომი, მაგრამ ეს ვარიანტი არა მხოლოდ რთული განსახორციელებელია, არამედ უბრალოდ არასწორია, რადგან ის არ ასახავს მთავარ ტენდენციას, რომელიც უნდა გამოვლინდეს. ყველაზე გონივრული გამოსავალი არის სწორი ხაზის ძიება y = ax + b, რომელიც საუკეთესოდ აახლოებს ექსპერიმენტულ მონაცემებს და უფრო ზუსტად, კოეფიციენტებს - a და b.

სიზუსტის ქულა

ნებისმიერი მიახლოებისთვის განსაკუთრებული მნიშვნელობა აქვს მისი სიზუსტის შეფასებას. e i-ით აღნიშნეთ განსხვავება (გადახრა) x i წერტილის ფუნქციურ და ექსპერიმენტულ მნიშვნელობებს შორის, ანუ e i = y i - f (x i).

ცხადია, მიახლოების სიზუსტის შესაფასებლად შეგიძლიათ გამოიყენოთ გადახრების ჯამი, ანუ სწორი ხაზის არჩევისას X-ის დამოკიდებულების მიახლოებითი წარმოდგენისთვის Y-ზე უპირატესობა უნდა მიენიჭოს მას, რომელსაც აქვს ყველაზე მცირე მნიშვნელობა. ჯამი e i ყველა განხილულ პუნქტში. თუმცა, ყველაფერი ასე მარტივი არ არის, რადგან დადებით გადახრებთან ერთად, პრაქტიკულად იქნება უარყოფითიც.

პრობლემის გადაჭრა შეგიძლიათ გადახრის მოდულების ან მათი კვადრატების გამოყენებით. ეს უკანასკნელი მეთოდი ყველაზე ფართოდ გამოიყენება. იგი გამოიყენება ბევრ სფეროში, მათ შორის რეგრესიული ანალიზის ჩათვლით (Excel-ში მისი განხორციელება ხორციელდება ორი ჩაშენებული ფუნქციის გამოყენებით) და დიდი ხანია დადასტურებულია, რომ ეფექტურია.

მინიმალური კვადრატის მეთოდი

Excel-ში, როგორც მოგეხსენებათ, არის ჩაშენებული autosum ფუნქცია, რომელიც საშუალებას გაძლევთ გამოთვალოთ არჩეულ დიაპაზონში მდებარე ყველა მნიშვნელობის მნიშვნელობები. ამგვარად, არაფერი შეგვიშლის ხელს გამოთვალოს გამოხატვის მნიშვნელობა (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

მათემატიკური აღნიშვნით, ეს ასე გამოიყურება:

მას შემდეგ, რაც თავდაპირველად გადაწყვეტილება მიიღეს მიახლოებით სწორი ხაზის გამოყენებით, ჩვენ გვაქვს:

ამრიგად, სწორი ხაზის პოვნის ამოცანა, რომელიც საუკეთესოდ აღწერს კონკრეტულ ურთიერთობას X-სა და Y-ს შორის, შეადგენს ორი ცვლადის ფუნქციის მინიმუმის გამოთვლას:

ეს მოითხოვს ნულოვანი ნაწილობრივი წარმოებულების გათანაბრებას ახალ a და b ცვლადებთან მიმართებაში და პრიმიტიული სისტემის ამოხსნას, რომელიც შედგება ორი განტოლებისგან, ფორმის 2 უცნობით:

მარტივი გარდაქმნების შემდეგ, მათ შორის 2-ზე გაყოფა და ჯამებით მანიპულირება, მივიღებთ:

მისი ამოხსნით, მაგალითად, კრამერის მეთოდით, ვიღებთ სტაციონარულ წერტილს გარკვეული კოეფიციენტებით a * და b * . ეს არის მინიმალური, ანუ იმის პროგნოზირებისთვის, თუ რა ბრუნვა ექნება მაღაზიას გარკვეულ ფართობზე, შესაფერისია სწორი ხაზი y = a * x + b *, რომელიც არის რეგრესიის მოდელი მოცემული მაგალითისთვის. რა თქმა უნდა, ეს არ მოგცემთ საშუალებას იპოვოთ ზუსტი შედეგი, მაგრამ ეს დაგეხმარებათ გაიგოთ, ანაზღაურდება თუ არა მაღაზიის კრედიტით ყიდვა კონკრეტული ზონისთვის.

როგორ განვახორციელოთ ყველაზე მცირე კვადრატების მეთოდი Excel-ში

Excel-ს აქვს ფუნქცია უმცირესი კვადრატების მნიშვნელობის გამოსათვლელად. მას აქვს შემდეგი ფორმა: TREND (ცნობილი Y მნიშვნელობები; ცნობილი X მნიშვნელობები; ახალი X მნიშვნელობები; მუდმივი). მოდით გამოვიყენოთ Excel-ში OLS-ის გამოთვლის ფორმულა ჩვენს ცხრილში.

ამისათვის, იმ უჯრედში, რომელშიც უნდა იყოს ნაჩვენები გაანგარიშების შედეგი Excel-ში უმცირესი კვადრატების მეთოდით, შეიყვანეთ "=" ნიშანი და აირჩიეთ "TREND" ფუნქცია. ფანჯარაში, რომელიც იხსნება, შეავსეთ შესაბამისი ველები, მონიშნეთ:

ცნობილი მნიშვნელობების დიაპაზონი Y-სთვის (ამ შემთხვევაში მონაცემები ბრუნვისთვის);
დიაპაზონი x 1, …x n, ანუ საცალო ფართის ზომა;
და x-ის ცნობილი და უცნობი მნიშვნელობები, რისთვისაც თქვენ უნდა გაარკვიოთ ბრუნვის ზომა (სამუშაო ფურცელზე მათი ადგილმდებარეობის შესახებ ინფორმაციისთვის იხილეთ ქვემოთ).

გარდა ამისა, ფორმულაში არის ლოგიკური ცვლადი "Const". თუ მის შესაბამის ველში შეიყვანთ 1, მაშინ ეს ნიშნავს, რომ გამოთვლები უნდა განხორციელდეს, თუ ვივარაუდებთ, რომ b \u003d 0.

თუ საჭიროა იცოდეთ პროგნოზი ერთზე მეტი x მნიშვნელობისთვის, მაშინ ფორმულის შეყვანის შემდეგ არ უნდა დააჭიროთ "Enter", არამედ უნდა აკრიფოთ კომბინაცია "Shift" + "Control" + "Enter" ("Enter" ) კლავიატურაზე.

ზოგიერთი მახასიათებელი

რეგრესიის ანალიზი შეიძლება ხელმისაწვდომი იყოს დუმებისთვისაც კი. Excel-ის ფორმულა უცნობი ცვლადების მასივის მნიშვნელობის პროგნოზირებისთვის - "TREND" - შეიძლება გამოიყენოს მათაც, ვისაც არასოდეს სმენია უმცირესი კვადრატების მეთოდის შესახებ. საკმარისია მხოლოდ მისი მუშაობის ზოგიერთი მახასიათებლის ცოდნა. Კერძოდ:

თუ თქვენ განათავსებთ y ცვლადის ცნობილი მნიშვნელობების დიაპაზონს ერთ მწკრივში ან სვეტში, მაშინ თითოეული მწკრივი (სვეტი) x-ის ცნობილი მნიშვნელობებით აღიქმება პროგრამის მიერ, როგორც ცალკე ცვლადი.
თუ დიაპაზონი ცნობილი x-ით არ არის მითითებული TREND ფანჯარაში, მაშინ Excel-ში ფუნქციის გამოყენების შემთხვევაში, პროგრამა განიხილავს მას, როგორც მთელი რიცხვებისგან შემდგარ მასივს, რომელთა რაოდენობა შეესაბამება დიაპაზონს მოცემული მნიშვნელობებით. ცვლადის y.
"პროგნოზირებადი" მნიშვნელობების მასივის გამოსატანად, ტენდენციის გამოხატულება უნდა იყოს შეყვანილი მასივის ფორმულის სახით.
თუ არ არის მითითებული ახალი x მნიშვნელობები, მაშინ TREND ფუნქცია მათ ცნობილთა ტოლად მიიჩნევს. თუ ისინი არ არის მითითებული, მაშინ მასივი 1 მიიღება არგუმენტად; 2; 3; 4;…, რომელიც შეესაბამება დიაპაზონს უკვე მოცემული პარამეტრებით y.
დიაპაზონი, რომელიც შეიცავს ახალ x მნიშვნელობებს, უნდა ჰქონდეს იგივე ან მეტი მწკრივი ან სვეტი, როგორც დიაპაზონი მოცემული y მნიშვნელობებით. სხვა სიტყვებით რომ ვთქვათ, ის უნდა იყოს პროპორციული დამოუკიდებელი ცვლადების მიმართ.
მასივი ცნობილი x მნიშვნელობებით შეიძლება შეიცავდეს მრავალ ცვლადს. თუმცა, თუ ჩვენ ვსაუბრობთ მხოლოდ ერთზე, მაშინ საჭიროა, რომ დიაპაზონი x და y მოცემული მნიშვნელობებით იყოს თანაზომიერი. რამდენიმე ცვლადის შემთხვევაში, აუცილებელია, რომ დიაპაზონი მოცემული y მნიშვნელობებით მოთავსდეს ერთ სვეტში ან ერთ რიგში.

FORECAST ფუნქცია

იგი ხორციელდება რამდენიმე ფუნქციის გამოყენებით. ერთ-ერთ მათგანს "პროგნოზირება" ჰქვია. ის TREND-ის მსგავსია, ანუ იძლევა გამოთვლების შედეგს უმცირესი კვადრატების მეთოდით. თუმცა, მხოლოდ ერთი X-ისთვის, რომლისთვისაც Y-ის მნიშვნელობა უცნობია.

ახლა თქვენ იცით ექსელის ფორმულები დუმებისთვის, რომლებიც საშუალებას გაძლევთ იწინასწარმეტყველოთ ინდიკატორის მომავალი მნიშვნელობის მნიშვნელობა ხაზოვანი ტენდენციის მიხედვით.

უმცირესი კვადრატების მეთოდი ერთ-ერთი ყველაზე გავრცელებული და ყველაზე განვითარებულია მისი გამო წრფივი პარამეტრების შეფასების მეთოდების სიმარტივე და ეფექტურობა. ამავდროულად, გარკვეული სიფრთხილე უნდა იქნას გამოყენებული მისი გამოყენებისას, რადგან მის გამოყენებით აშენებული მოდელები შეიძლება არ აკმაყოფილებდეს რიგ მოთხოვნებს მათი პარამეტრების ხარისხის შესახებ და, შედეგად, არ ასახავდეს პროცესის განვითარების ნიმუშებს.

განვიხილოთ უფრო დეტალურად წრფივი ეკონომეტრიული მოდელის პარამეტრების შეფასების პროცედურა უმცირესი კვადრატების მეთოდის გამოყენებით. ასეთი მოდელი ზოგადი ფორმით შეიძლება წარმოდგენილი იყოს განტოლებით (1.2):

y t = a 0 + a 1 x 1 t +...+ a n x nt + ε t .

საწყისი მონაცემები a 0, a 1,..., a n პარამეტრების შეფასებისას არის დამოკიდებული ცვლადის მნიშვნელობების ვექტორი. წ= (y 1 , y 2 , ... , y T)" და დამოუკიდებელი ცვლადების მნიშვნელობების მატრიცა

რომელშიც პირველი სვეტი, რომელიც შედგება ერთებისგან, შეესაბამება მოდელის კოეფიციენტს.

უმცირესი კვადრატების მეთოდმა მიიღო სახელი იმ ძირითადი პრინციპიდან გამომდინარე, რომ მის საფუძველზე მიღებული პარამეტრების შეფასება უნდა აკმაყოფილებდეს: მოდელის შეცდომის კვადრატების ჯამი მინიმალური უნდა იყოს.

უმცირესი კვადრატების მეთოდით ამოცანების ამოხსნის მაგალითები

მაგალითი 2.1.სავაჭრო საწარმოს აქვს 12 მაღაზიისგან შემდგარი ქსელი, რომლის საქმიანობის შესახებ ინფორმაცია მოცემულია ცხრილში. 2.1.

კომპანიის მენეჯმენტს სურს იცოდეს, თუ როგორ არის დამოკიდებული წლიური ზომა მაღაზიის გაყიდვების არეალზე.

ცხრილი 2.1

მაღაზიის ნომერი	წლიური ბრუნვა, მილიონი რუბლი	სავაჭრო ფართი ათასი მ 2

უმცირესი კვადრატების გამოსავალი.მოდით დავასახელოთ - მე-ე მაღაზიის წლიური ბრუნვა, მილიონი რუბლი; - მაღაზიის გასაყიდი ფართი ათასი მ 2.

ნახ.2.1. Scatterplot მაგალითისთვის 2.1

ცვლადებს შორის ფუნქციონალური დამოკიდებულების ფორმის განსაზღვრა და სკატერპლტის აგება (ნახ. 2.1).

სკატერის დიაგრამაზე დაყრდნობით შეგვიძლია დავასკვნათ, რომ წლიური ბრუნვა დადებითად არის დამოკიდებული გაყიდვის არეალზე (ანუ, y გაიზრდება ზრდით). ფუნქციური კავშირის ყველაზე შესაფერისი ფორმაა − ხაზოვანი.

ინფორმაცია შემდგომი გამოთვლებისთვის მოცემულია ცხრილში. 2.2. უმცირესი კვადრატების მეთოდის გამოყენებით ვაფასებთ ხაზოვანი ერთფაქტორიანი ეკონომეტრიული მოდელის პარამეტრებს

ცხრილი 2.2

ამრიგად,

ამრიგად, სავაჭრო ფართობის 1 ათასი მ 2-ით გაზრდით, სხვა თანაბარი პირობებით, საშუალო წლიური ბრუნვა იზრდება 67,8871 მილიონი რუბლით.

მაგალითი 2.2.საწარმოს ხელმძღვანელობამ შენიშნა, რომ წლიური ბრუნვა დამოკიდებულია არა მხოლოდ მაღაზიის გაყიდვის ზონაზე (იხ. მაგალითი 2.1), არამედ ვიზიტორების საშუალო რაოდენობაზეც. შესაბამისი ინფორმაცია მოცემულია ცხრილში. 2.3.

ცხრილი 2.3

გამოსავალი.აღნიშნე - მაღაზიის ვიზიტორთა საშუალო რაოდენობა დღეში, ათასი ადამიანი.

ცვლადებს შორის ფუნქციონალური დამოკიდებულების ფორმის განსაზღვრა და სკატერპლტის აგება (ნახ. 2.2).

სკატერის დიაგრამაზე დაყრდნობით შეგვიძლია დავასკვნათ, რომ წლიური ბრუნვა დადებითად არის დაკავშირებული დღე-ღამეში ვიზიტორთა საშუალო რაოდენობასთან (ანუ y გაიზრდება ზრდით). ფუნქციური დამოკიდებულების ფორმა წრფივია.

ბრინჯი. 2.2. Scatterplot მაგალითად 2.2

ცხრილი 2.4

ზოგადად, აუცილებელია ორფაქტორიანი ეკონომეტრიული მოდელის პარამეტრების დადგენა

y t \u003d a 0 + a 1 x 1 t + a 2 x 2 t + ε t

შემდგომი გამოთვლებისთვის საჭირო ინფორმაცია წარმოდგენილია ცხრილში. 2.4.

მოდით შევაფასოთ წრფივი ორფაქტორიანი ეკონომეტრიული მოდელის პარამეტრები უმცირესი კვადრატების მეთოდის გამოყენებით.

ამრიგად,

კოეფიციენტის შეფასება = 61,6583 გვიჩვენებს, რომ სხვა თანაბარ პირობებში, სავაჭრო ფართობის 1 ათასი მ 2-ით გაზრდით, წლიური ბრუნვა გაიზრდება საშუალოდ 61,6583 მილიონი რუბლით.

მინიმალური კვადრატის მეთოდი

უმცირესი კვადრატის მეთოდი ( MNK, OLS, ჩვეულებრივი უმცირესი კვადრატები) - რეგრესიული ანალიზის ერთ-ერთი ძირითადი მეთოდი რეგრესიის მოდელების უცნობი პარამეტრების შეფასების ნიმუშის მონაცემებიდან. მეთოდი ეფუძნება რეგრესიის ნარჩენების კვადრატების ჯამის მინიმიზაციას.

უნდა აღინიშნოს, რომ უმცირესი კვადრატების მეთოდს თავისთავად შეიძლება ეწოდოს პრობლემის გადაჭრის მეთოდი ნებისმიერ სფეროში, თუ ამოხსნა შედგება ან აკმაყოფილებს უცნობი ცვლადის ზოგიერთი ფუნქციის კვადრატების ჯამის მინიმიზაციის გარკვეულ კრიტერიუმს. მაშასადამე, უმცირესი კვადრატების მეთოდი ასევე შეიძლება გამოყენებულ იქნას მოცემული ფუნქციის მიახლოებითი წარმოდგენისთვის (დაახლოებით) სხვა (უმარტივესი) ფუნქციებით, როდესაც ვიპოვით სიმრავლეებს, რომლებიც აკმაყოფილებს განტოლებებს ან შეზღუდვებს, რომელთა რაოდენობა აღემატება ამ სიდიდეების რაოდენობას. და ა.შ.

MNC-ის არსი

მოდით (ახსნა) ცვლადს შორის ალბათური (რეგრესიის) დამოკიდებულების (პარამეტრული) მოდელი. წდა მრავალი ფაქტორი (ახსნა ცვლადები) x

სად არის უცნობი მოდელის პარამეტრების ვექტორი

- შემთხვევითი მოდელის შეცდომა.

დაე, ასევე იყოს მითითებული ცვლადების მნიშვნელობების ნიმუშის დაკვირვება. მოდით იყოს დაკვირვების ნომერი (). შემდეგ არის ცვლადების მნიშვნელობები --ე დაკვირვებაში. შემდეგ, b პარამეტრების მოცემული მნიშვნელობებისთვის, შესაძლებელია ახსნილი ცვლადის y თეორიული (მოდელური) მნიშვნელობების გამოთვლა:

ნარჩენების მნიშვნელობა დამოკიდებულია b პარამეტრების მნიშვნელობებზე.

LSM-ის (ჩვეულებრივი, კლასიკური) არსი არის ისეთი b პარამეტრების პოვნა, რომლებისთვისაც ნარჩენების კვადრატების ჯამი (ინგლ. კვადრატების ნარჩენი ჯამი) იქნება მინიმალური:

ზოგადად, ამ პრობლემის გადაჭრა შესაძლებელია ოპტიმიზაციის (მინიმიზაციის) რიცხვითი მეთოდებით. ამ შემთხვევაში საუბარია არაწრფივი უმცირესი კვადრატები(NLS ან NLLS - ინგლისური. არაწრფივი უმცირესი კვადრატები). ხშირ შემთხვევაში, შესაძლებელია ანალიტიკური გადაწყვეტის მიღება. მინიმიზაციის პრობლემის გადასაჭრელად საჭიროა ვიპოვოთ ფუნქციის სტაციონარული წერტილები უცნობი პარამეტრების მიხედვით დიფერენცირებით, წარმოებულების ნულამდე გათანაბრებით და მიღებული განტოლებათა სისტემის ამოხსნით:

თუ მოდელის შემთხვევითი შეცდომები ჩვეულებრივ განაწილებულია, აქვთ იგივე ვარიაცია და არ არის დაკავშირებული ერთმანეთთან, მინიმალური კვადრატების პარამეტრების შეფასება იგივეა, რაც მაქსიმალური ალბათობის მეთოდის (MLM) შეფასებები.

LSM ხაზოვანი მოდელის შემთხვევაში

დაე, რეგრესიის დამოკიდებულება იყოს წრფივი:

დაე წ- ახსნილი ცვლადის დაკვირვების სვეტის ვექტორი და - ფაქტორების დაკვირვების მატრიცა (მატრიცის რიგები - ფაქტორების მნიშვნელობების ვექტორები მოცემულ დაკვირვებაში, სვეტების მიხედვით - მოცემული ფაქტორის მნიშვნელობების ვექტორი ყველა დაკვირვებაში) . ხაზოვანი მოდელის მატრიცულ წარმოდგენას აქვს ფორმა:

მაშინ ახსნილი ცვლადის შეფასების ვექტორი და რეგრესიის ნარჩენების ვექტორი ტოლი იქნება

შესაბამისად, რეგრესიის ნარჩენების კვადრატების ჯამი ტოლი იქნება

ამ ფუნქციის დიფერენცირებით პარამეტრის ვექტორთან მიმართებაში და წარმოებულების ნულამდე გათანაბრება, ჩვენ ვიღებთ განტოლებათა სისტემას (მატრიცის სახით):

განტოლებათა ამ სისტემის ამოხსნა იძლევა ხაზოვანი მოდელის უმცირესი კვადრატების შეფასების ზოგად ფორმულას:

ანალიტიკური მიზნებისთვის, ამ ფორმულის ბოლო წარმოდგენა სასარგებლო აღმოჩნდება. თუ მონაცემები რეგრესიის მოდელში ორიენტირებული, მაშინ ამ წარმოდგენაში პირველ მატრიცას აქვს ფაქტორების ნიმუშური კოვარიანტების მატრიცის მნიშვნელობა, ხოლო მეორე არის დამოკიდებული ცვლადთან ფაქტორების კოვარიანტების ვექტორი. თუ გარდა ამისა, მონაცემები ასევე ნორმალიზებული SKO-ში (ეს არის საბოლოო ჯამში სტანდარტიზებული), მაშინ პირველ მატრიცას აქვს ფაქტორების ნიმუშის კორელაციის მატრიცის მნიშვნელობა, მეორე ვექტორს - ფაქტორების ნიმუშის კორელაციის ვექტორს დამოკიდებულ ცვლადთან.

LLS შეფასებების მნიშვნელოვანი თვისება მოდელებისთვის მუდმივთან ერთად- აგებული რეგრესიის ხაზი გადის ნიმუშის მონაცემების სიმძიმის ცენტრში, ანუ სრულდება თანასწორობა:

კერძოდ, უკიდურეს შემთხვევაში, როდესაც ერთადერთი რეგრესორი არის მუდმივი, აღმოვაჩენთ, რომ ერთი პარამეტრის OLS შეფასება (თვითონ მუდმივი) უდრის ახსნილი ცვლადის საშუალო მნიშვნელობას. ანუ არითმეტიკული საშუალო, რომელიც ცნობილია თავისი კარგი თვისებებით დიდი რიცხვების კანონებიდან, ასევე არის უმცირესი კვადრატების შეფასება - ის აკმაყოფილებს მისგან კვადრატული გადახრების მინიმალური ჯამის კრიტერიუმს.

მაგალითი: მარტივი (წყვილი) რეგრესია

დაწყვილებული წრფივი რეგრესიის შემთხვევაში, გაანგარიშების ფორმულები გამარტივებულია (შეგიძლიათ გააკეთოთ მატრიცული ალგებრის გარეშე):

OLS შეფასების თვისებები

უპირველეს ყოვლისა, ჩვენ აღვნიშნავთ, რომ წრფივი მოდელებისთვის, უმცირესი კვადრატების შეფასებები არის წრფივი შეფასებები, როგორც ზემოთ მოყვანილი ფორმულიდან ჩანს. OLS-ის მიუკერძოებელი შეფასებისთვის, აუცილებელია და საკმარისია რეგრესიის ანალიზის ყველაზე მნიშვნელოვანი პირობის შესრულება: ფაქტორებით განპირობებული შემთხვევითი შეცდომის მათემატიკური მოლოდინი უნდა იყოს ნულის ტოლი. ეს პირობა დაკმაყოფილებულია, კერძოდ, თუ

შემთხვევითი შეცდომების მათემატიკური მოლოდინი არის ნული და
ფაქტორები და შემთხვევითი შეცდომები დამოუკიდებელი შემთხვევითი ცვლადებია.

მეორე პირობა - ეგზოგენური ფაქტორების მდგომარეობა - ფუნდამენტურია. თუ ეს თვისება არ არის დაკმაყოფილებული, მაშინ შეგვიძლია ვივარაუდოთ, რომ თითქმის ნებისმიერი შეფასება იქნება უკიდურესად არადამაკმაყოფილებელი: ისინი არც კი იქნება თანმიმდევრული (ანუ, მონაცემთა ძალიან დიდი რაოდენობაც კი არ იძლევა ხარისხობრივი შეფასებების მიღებას ამ შემთხვევაში). კლასიკურ შემთხვევაში, უფრო ძლიერი ვარაუდი კეთდება ფაქტორების დეტერმინიზმის შესახებ, შემთხვევითი შეცდომისგან განსხვავებით, რაც ავტომატურად ნიშნავს, რომ ეგზოგენური მდგომარეობა დაკმაყოფილებულია. ზოგად შემთხვევაში, შეფასებების თანმიმდევრულობისთვის საკმარისია ეგზოგენურობის პირობის შესრულება მატრიცის კონვერგენციასთან ერთად ზოგიერთ არასიგნოლურ მატრიცასთან ერთად ნიმუშის ზომის ზრდით უსასრულობამდე.

იმისთვის, რომ თანმიმდევრულობისა და მიუკერძოებლობის გარდა, (ჩვეულებრივი) უმცირესი კვადრატების შეფასებაც ეფექტური იყოს (საუკეთესო ხაზოვანი მიუკერძოებელი შეფასებების კლასში), აუცილებელია შემთხვევითი შეცდომის დამატებითი თვისებების შესრულება:

ეს დაშვებები შეიძლება ჩამოყალიბდეს შემთხვევითი შეცდომის ვექტორის კოვარიანტული მატრიცისთვის

ხაზოვანი მოდელი, რომელიც აკმაყოფილებს ამ პირობებს ეწოდება კლასიკური. კლასიკური წრფივი რეგრესიის OLS შეფასებები არის მიუკერძოებელი, თანმიმდევრული და ყველაზე ეფექტური შეფასებები ყველა ხაზოვანი მიუკერძოებელი შეფასების კლასში (ინგლისურ ლიტერატურაში, შემოკლება ზოგჯერ გამოიყენება ლურჯი (საუკეთესო ხაზოვანი უსაფუძვლო შემფასებელი) არის საუკეთესო ხაზოვანი მიუკერძოებელი შეფასება; საშინაო ლიტერატურაში უფრო ხშირად ციტირებულია გაუს-მარკოვის თეორემა). როგორც ადვილი საჩვენებელია, კოეფიციენტის შეფასების ვექტორის კოვარიანტული მატრიცა ტოლი იქნება:

განზოგადებული უმცირესი კვადრატები

უმცირესი კვადრატების მეთოდი ფართო განზოგადების საშუალებას იძლევა. ნარჩენების კვადრატების ჯამის მინიმიზაციის ნაცვლად, შეიძლება მინიმუმამდე დავიყვანოთ ნარჩენი ვექტორის გარკვეული დადებითი განსაზღვრული კვადრატული ფორმა, სადაც არის რაღაც სიმეტრიული დადებითი განსაზღვრული წონის მატრიცა. ჩვეულებრივი უმცირესი კვადრატები ამ მიდგომის განსაკუთრებული შემთხვევაა, როდესაც წონის მატრიცა არის იდენტურობის მატრიცის პროპორციული. როგორც ცნობილია სიმეტრიული მატრიცების (ან ოპერატორების) თეორიიდან, ასეთი მატრიცების დაშლა ხდება. მაშასადამე, მითითებული ფუნქციონალი შეიძლება წარმოდგენილი იყოს შემდეგნაირად, ანუ ეს ფუნქციონალი შეიძლება იყოს წარმოდგენილი, როგორც ზოგიერთი გარდაქმნილი „ნარჩენების“ კვადრატების ჯამი. ამრიგად, ჩვენ შეგვიძლია გამოვყოთ უმცირესი კვადრატების მეთოდების კლასი - LS- მეთოდები (უმცირესი კვადრატები).

დადასტურებულია (აიტკენის თეორემა), რომ განზოგადებული წრფივი რეგრესიის მოდელისთვის (რომელშიც არ არის დაწესებული შეზღუდვები შემთხვევითი შეცდომების კოვარიანტულ მატრიცაზე), ყველაზე ეფექტური (წრფივი მიუკერძოებელი შეფასებების კლასში) შეფასებებია ე.წ. განზოგადებული OLS (OMNK, GLS - გენერალიზებული უმცირესი კვადრატები)- LS-მეთოდი წონის მატრიცით, რომელიც ტოლია შემთხვევითი შეცდომების შებრუნებული კოვარიანტული მატრიცის: .

შეიძლება აჩვენოს, რომ ხაზოვანი მოდელის პარამეტრების GLS-შეფასების ფორმულას აქვს ფორმა

ამ შეფასებების კოვარიანტული მატრიცა, შესაბამისად, ტოლი იქნება

სინამდვილეში, OLS-ის არსი მდგომარეობს ორიგინალური მონაცემების გარკვეულ (წრფივ) ტრანსფორმაციაში (P) და ჩვეულებრივ უმცირეს კვადრატების გამოყენებაში ტრანსფორმირებულ მონაცემებზე. ამ ტრანსფორმაციის მიზანია ის, რომ ტრანსფორმირებული მონაცემებისთვის შემთხვევითი შეცდომები უკვე აკმაყოფილებს კლასიკურ დაშვებებს.

შეწონილი უმცირესი კვადრატები

დიაგონალური წონის მატრიცის შემთხვევაში (და, შესაბამისად, შემთხვევითი შეცდომების კოვარიანტული მატრიცა), ჩვენ გვაქვს ეგრეთ წოდებული შეწონილი უმცირესი კვადრატები (WLS - Weighted Least Squares). ამ შემთხვევაში, მოდელის ნარჩენების კვადრატების შეწონილი ჯამი მინიმუმამდეა დაყვანილი, ანუ თითოეული დაკვირვება იღებს „წონას“, რომელიც უკუპროპორციულია ამ დაკვირვებაში შემთხვევითი შეცდომის დისპერსიასთან: . ფაქტობრივად, მონაცემები გარდაიქმნება დაკვირვებების შეწონვით (გაყოფა შემთხვევითი შეცდომების სავარაუდო სტანდარტული გადახრის პროპორციულ რაოდენობაზე) და ნორმალური უმცირესი კვადრატები გამოიყენება შეწონილ მონაცემებზე.

LSM-ის პრაქტიკაში გამოყენების რამდენიმე განსაკუთრებული შემთხვევა

ხაზოვანი მიახლოება

განვიხილოთ შემთხვევა, როდესაც გარკვეული სკალარული რაოდენობის დამოკიდებულების შესწავლის შედეგად გარკვეულ სკალარული სიდიდეზე (ეს შეიძლება იყოს, მაგალითად, ძაბვის დამოკიდებულება მიმდინარე სიძლიერეზე: , სადაც არის მუდმივი მნიშვნელობა, გამტარის წინააღმდეგობა ), გაზომეს ეს რაოდენობები, რის შედეგადაც მნიშვნელობები და მათი შესაბამისი მნიშვნელობები. გაზომვის მონაცემები უნდა ჩაიწეროს ცხრილში.

მაგიდა. გაზომვის შედეგები.

გაზომვა No.
1
2
3
4
5
6

კითხვა ასე ჟღერს: კოეფიციენტის რომელი მნიშვნელობა შეიძლება ავირჩიოთ დამოკიდებულების საუკეთესოდ აღსაწერად? უმცირესი კვადრატების მიხედვით, ეს მნიშვნელობა უნდა იყოს ისეთი, რომ მნიშვნელობების კვადრატული გადახრების ჯამი მნიშვნელობებისგან

მინიმალური იყო

კვადრატული გადახრების ჯამს აქვს ერთი უკიდურესი - მინიმუმი, რაც საშუალებას გვაძლევს გამოვიყენოთ ეს ფორმულა. მოდით ვიპოვოთ კოეფიციენტის მნიშვნელობა ამ ფორმულიდან. ამისათვის ჩვენ ვცვლით მის მარცხენა მხარეს შემდეგნაირად:

ბოლო ფორმულა საშუალებას გვაძლევს ვიპოვოთ კოეფიციენტის მნიშვნელობა, რომელიც საჭირო იყო პრობლემაში.

ამბავი

XIX საუკუნის დასაწყისამდე. მეცნიერებს არ ჰქონდათ გარკვეული წესები განტოლებათა სისტემის ამოხსნისთვის, რომელშიც უცნობის რაოდენობა განტოლებათა რაოდენობაზე ნაკლებია; ამ დრომდე გამოიყენებოდა კონკრეტული მეთოდები, განტოლებების ტიპებიდან და კალკულატორების ჭკუიდან გამომდინარე, და, შესაბამისად, სხვადასხვა კალკულატორები, ერთი და იგივე დაკვირვების მონაცემებიდან დაწყებული, სხვადასხვა დასკვნამდე მივიდნენ. გაუსს (1795) მიეწერება მეთოდის პირველი გამოყენება, ხოლო ლეჟანდრმა (1805) დამოუკიდებლად აღმოაჩინა და გამოაქვეყნა იგი მისი თანამედროვე სახელით (fr. Methode des moindres quarres ) . ლაპლასმა მეთოდი დააკავშირა ალბათობის თეორიას და ამერიკელმა მათემატიკოსმა ადრეინმა (1808) განიხილა მისი ალბათური აპლიკაციები. მეთოდი ფართოდ არის გავრცელებული და გაუმჯობესებულია ენკეს, ბესელის, ჰანსენის და სხვათა შემდგომი კვლევებით.

MNC-ების ალტერნატიული გამოყენება

უმცირესი კვადრატების მეთოდის იდეა ასევე შეიძლება გამოყენებულ იქნას სხვა შემთხვევებში, რომლებიც პირდაპირ არ არის დაკავშირებული რეგრესიის ანალიზთან. ფაქტია, რომ კვადრატების ჯამი არის ერთ-ერთი ყველაზე გავრცელებული სიახლოვის საზომი ვექტორებისთვის (ევკლიდური მეტრიკა სასრულ განზომილებიან სივრცეებში).

ერთ-ერთი პროგრამაა წრფივი განტოლებების სისტემების „გადაწყვეტა“, რომლებშიც განტოლებების რაოდენობა ცვლადების რაოდენობაზე მეტია.

სადაც მატრიცა არ არის კვადრატული, არამედ მართკუთხა.

განტოლებათა ასეთ სისტემას, ზოგად შემთხვევაში, არ აქვს ამონახსნი (თუ რანგი რეალურად მეტია ცვლადების რაოდენობაზე). მაშასადამე, ამ სისტემის „გადაჭრა“ შესაძლებელია მხოლოდ ასეთი ვექტორის არჩევის მნიშვნელობით, რათა მინიმუმამდე დაიყვანოს „მანძილი“ ვექტორებსა და . ამისათვის შეგიძლიათ გამოიყენოთ კრიტერიუმი სისტემის განტოლებების მარცხენა და მარჯვენა ნაწილების კვადრატული განსხვავებების ჯამის მინიმიზაციისთვის, ანუ . ადვილია იმის ჩვენება, რომ ამ მინიმიზაციის პრობლემის ამოხსნა იწვევს განტოლებათა შემდეგი სისტემის ამოხსნას

მაგალითი.

მათი გასწორების შედეგად ფუნქცია

უმცირესი კვადრატების მეთოდის არსი (LSM).

კოეფიციენტების მოძიების ფორმულების გამოყვანა.

შედგენილია და ამოხსნილია ორი განტოლების სისტემა ორი უცნობით. ფუნქციების ნაწილობრივი წარმოებულების მოძიება ცვლადების მიხედვით ადა ბ, ამ წარმოებულებს ვატოლებთ ნულს.

მიღებულ განტოლებათა სისტემას ვხსნით ნებისმიერი მეთოდით (მაგ ჩანაცვლების მეთოდიან კრამერის მეთოდი) და მიიღეთ ფორმულები კოეფიციენტების მოსაძებნად უმცირესი კვადრატების მეთოდის (LSM) გამოყენებით.

მონაცემებით ადა ბფუნქცია იღებს უმცირეს მნიშვნელობას. ამ ფაქტის მტკიცებულება მოცემულია გვერდის ბოლოს ტექსტის ქვემოთ.

ეს არის უმცირესი კვადრატების მთელი მეთოდი. პარამეტრის პოვნის ფორმულა აშეიცავს ჯამებს ,, და პარამეტრს ნ- ექსპერიმენტული მონაცემების რაოდენობა. ამ თანხების მნიშვნელობები რეკომენდებულია ცალკე გამოითვალოს. კოეფიციენტი ბნაპოვნია გაანგარიშების შემდეგ ა.

დროა გავიხსენოთ ორიგინალური მაგალითი.

გამოსავალი.

ცხრილის ბოლო სვეტის მნიშვნელობები არის მნიშვნელობების ჯამები რიგებში.

აქედან გამომდინარე, y=0.165x+2.184არის სასურველი მიახლოებითი სწორი ხაზი.

უმცირესი კვადრატების მეთოდის ცდომილების შეფასება.

მას შემდეგ, ხაზი y=0.165x+2.184უკეთ აახლოებს თავდაპირველ მონაცემებს.

უმცირესი კვადრატების მეთოდის გრაფიკული ილუსტრაცია (LSM).

პრაქტიკაში, სხვადასხვა პროცესის მოდელირებისას - კერძოდ, ეკონომიკური, ფიზიკური, ტექნიკური, სოციალური - ფართოდ გამოიყენება ფუნქციების სავარაუდო მნიშვნელობების გამოთვლის ერთი ან სხვა მეთოდი მათი ცნობილი მნიშვნელობებიდან ზოგიერთ ფიქსირებულ წერტილში.

ამ ტიპის ფუნქციების მიახლოების პრობლემები ხშირად წარმოიქმნება:

ექსპერიმენტის შედეგად მიღებული ცხრილის მონაცემების მიხედვით შესასწავლი პროცესის დამახასიათებელი რაოდენობების მნიშვნელობების გამოთვლის სავარაუდო ფორმულების აგებისას;

რიცხვით ინტეგრაციაში, დიფერენციაციაში, დიფერენციალური განტოლებების ამოხსნაში და სხვ.;

თუ აუცილებელია ფუნქციების მნიშვნელობების გამოთვლა განხილული ინტერვალის შუალედურ წერტილებში;

პროცესის დამახასიათებელი რაოდენობების მნიშვნელობების განსაზღვრისას განსახილველი ინტერვალის გარეთ, კერძოდ, პროგნოზირებისას.

თუ ცხრილით მითითებული გარკვეული პროცესის მოდელირებისთვის, აგებულია ფუნქცია, რომელიც დაახლოებით აღწერს ამ პროცესს უმცირესი კვადრატების მეთოდის საფუძველზე, მას ეწოდება მიახლოებითი ფუნქცია (რეგრესია) და თავად მიახლოებითი ფუნქციების აგების ამოცანა იქნება. იყოს მიახლოების პრობლემა.

ამ სტატიაში განხილულია MS Excel პაკეტის შესაძლებლობები ასეთი პრობლემების გადასაჭრელად, გარდა ამისა, მოცემულია ცხრილად მოცემული ფუნქციებისთვის რეგრესიის აგების (შექმნის) მეთოდები და ტექნიკა (რაც რეგრესიის ანალიზის საფუძველია).

Excel-ში რეგრესიების აგების ორი ვარიანტია.

შერჩეული რეგრესიების (ტენდენციის ხაზების) დამატება შესწავლილი პროცესის მახასიათებლისთვის მონაცემთა ცხრილის საფუძველზე აგებულ დიაგრამაზე (ხელმისაწვდომია მხოლოდ დიაგრამის აგების შემთხვევაში);

Excel-ის სამუშაო ფურცლის ჩაშენებული სტატისტიკური ფუნქციების გამოყენებით, რაც საშუალებას გაძლევთ მიიღოთ რეგრესიები (ტენდენციის ხაზები) პირდაპირ წყაროს მონაცემთა ცხრილიდან.

ტენდენციების დამატება ჩარტში

მონაცემთა ცხრილისთვის, რომელიც აღწერს გარკვეულ პროცესს და წარმოდგენილია დიაგრამით, Excel-ს აქვს ეფექტური რეგრესიის ანალიზის ინსტრუმენტი, რომელიც საშუალებას გაძლევთ:

შექმენით უმცირესი კვადრატების მეთოდის საფუძველზე და დაამატეთ დიაგრამას ხუთი ტიპის რეგრესია, რომლებიც მოდელირებენ შესასწავლ პროცესს სხვადასხვა ხარისხის სიზუსტით;

დიაგრამას დაამატეთ აგებული რეგრესიის განტოლება;

განსაზღვროს შერჩეული რეგრესიის შესაბამისობის ხარისხი სქემაზე გამოტანილ მონაცემებთან.

დიაგრამის მონაცემებზე დაყრდნობით Excel საშუალებას გაძლევთ მიიღოთ ხაზოვანი, პოლინომიური, ლოგარითმული, ექსპონენციალური, ექსპონენციალური ტიპის რეგრესია, რომლებიც მოცემულია განტოლებით:

y = y(x)

სადაც x არის დამოუკიდებელი ცვლადი, რომელიც ხშირად იღებს ნატურალური რიცხვების მიმდევრობის მნიშვნელობებს (1; 2; 3; ...) და აწარმოებს, მაგალითად, შესასწავლი პროცესის დროის ათვლას (მახასიათებლებს) .

1 . ხაზოვანი რეგრესია კარგია ისეთი მახასიათებლების მოდელირებისთვის, რომლებიც იზრდება ან მცირდება მუდმივი ტემპით. ეს არის შესწავლილი პროცესის უმარტივესი მოდელი. იგი აგებულია განტოლების მიხედვით:

y=mx+b

სადაც m არის წრფივი რეგრესიის დახრილობის ტანგენსი x ღერძზე; b - წრფივი რეგრესიის გადაკვეთის წერტილის კოორდინატი y ღერძთან.

2 . პოლინომიური ტენდენციის ხაზი სასარგებლოა მახასიათებლების აღსაწერად, რომლებსაც აქვთ რამდენიმე განსხვავებული უკიდურესობა (მაღალი და დაბალი). მრავალწევრის ხარისხის არჩევანი განისაზღვრება შესასწავლი მახასიათებლის უკიდურესობების რაოდენობით. ამრიგად, მეორე ხარისხის მრავალწევრს შეუძლია კარგად აღწეროს პროცესი, რომელსაც აქვს მხოლოდ ერთი მაქსიმუმი ან მინიმალური; მესამე ხარისხის მრავალწევრი - არაუმეტეს ორი უკიდურესი; მეოთხე ხარისხის მრავალწევრი - არაუმეტეს სამი უკიდურესისა და ა.შ.

ამ შემთხვევაში, ტრენდის ხაზი აგებულია განტოლების შესაბამისად:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

სადაც კოეფიციენტები c0, c1, c2,... c6 არის მუდმივები, რომელთა მნიშვნელობები განისაზღვრება მშენებლობის დროს.

3 . ლოგარითმული ტენდენციის ხაზი წარმატებით გამოიყენება მოდელირების მახასიათებლებში, რომელთა მნიშვნელობები თავდაპირველად სწრაფად იცვლება, შემდეგ კი თანდათან სტაბილიზდება.

y = c ln(x) + b

4 . ელექტრო ტენდენციის ხაზი იძლევა კარგ შედეგებს, თუ შესწავლილი დამოკიდებულების მნიშვნელობები ხასიათდება ზრდის ტემპის მუდმივი ცვლილებით. ასეთი დამოკიდებულების მაგალითი შეიძლება გახდეს მანქანის ერთნაირად აჩქარებული მოძრაობის გრაფიკი. თუ მონაცემებში არის ნულოვანი ან უარყოფითი მნიშვნელობები, თქვენ არ შეგიძლიათ გამოიყენოთ დენის ტრენდული ხაზი.

იგი აგებულია განტოლების შესაბამისად:

y = cxb

სადაც b, c კოეფიციენტები მუდმივებია.

5 . ექსპონენციური ტენდენციის ხაზი უნდა იქნას გამოყენებული, თუ მონაცემების ცვლილების სიჩქარე მუდმივად იზრდება. ნულოვანი ან უარყოფითი მნიშვნელობების შემცველი მონაცემებისთვის, ამ სახის მიახლოება ასევე არ გამოიყენება.

იგი აგებულია განტოლების შესაბამისად:

y=cebx

სადაც b, c კოეფიციენტები მუდმივებია.

ტრენდის ხაზის არჩევისას Excel ავტომატურად ითვლის R2-ის მნიშვნელობას, რაც ახასიათებს მიახლოების სიზუსტეს: რაც უფრო ახლოს არის R2 მნიშვნელობა ერთთან, მით უფრო საიმედოდ აახლოებს ტრენდის ხაზი შესწავლილ პროცესს. საჭიროების შემთხვევაში, R2-ის მნიშვნელობა ყოველთვის შეიძლება იყოს ნაჩვენები დიაგრამაზე.

განისაზღვრება ფორმულით:

ტენდენციის ხაზის დასამატებლად მონაცემთა სერიას:

გააქტიურეთ დიაგრამა, რომელიც აგებულია მონაცემთა სერიის საფუძველზე, ანუ დააწკაპუნეთ დიაგრამის ზონაში. ჩარტის ელემენტი გამოჩნდება მთავარ მენიუში;

ამ პუნქტზე დაწკაპუნების შემდეგ ეკრანზე გამოჩნდება მენიუ, რომელშიც უნდა აირჩიოთ Add trend line ბრძანება.

იგივე ქმედებები ადვილად განხორციელდება, თუ გადაახვევთ დიაგრამას, რომელიც შეესაბამება მონაცემთა ერთ-ერთ სერიას და დააწკაპუნეთ მაუსის მარჯვენა ღილაკით; კონტექსტურ მენიუში, რომელიც გამოჩნდება, აირჩიეთ ბრძანება ტენდენციის ხაზის დამატება. ეკრანზე გამოჩნდება Trendline დიალოგური ფანჯარა, სადაც გახსნილია Type tab (ნახ. 1).

ამის შემდეგ გჭირდებათ:

Type ჩანართზე აირჩიეთ სასურველი ტრენდის ხაზის ტიპი (ხაზოვანი არჩეულია ნაგულისხმევად). Polynomial ტიპისთვის, Degree ველში, მიუთითეთ არჩეული მრავალწევრის ხარისხი.

1 . ჩაშენებული სერიების ველი ჩამოთვლის ყველა მონაცემთა სერიას მოცემულ დიაგრამაში. ტენდენციის ხაზის დასამატებლად მონაცემთა კონკრეტულ სერიაში, აირჩიეთ მისი სახელი ველში ჩაშენებული სერია.

საჭიროების შემთხვევაში, პარამეტრების ჩანართზე გადასვლით (ნახ. 2), შეგიძლიათ დააყენოთ შემდეგი პარამეტრები ტრენდის ხაზისთვის:

შეცვალეთ ტრენდის ხაზის სახელი მიახლოებითი (გათლილი) მრუდის ველის სახელში.

პროგნოზის ველში დააყენეთ პროგნოზის პერიოდების რაოდენობა (წინ ან უკან);

აჩვენეთ ტენდენციის ხაზის განტოლება დიაგრამის ზონაში, რისთვისაც უნდა ჩართოთ ჩამრთველი განტოლების ჩვენება სქემაზე;

აჩვენეთ მიახლოების სანდოობის მნიშვნელობა R2 დიაგრამის ზონაში, რისთვისაც უნდა ჩართოთ ჩამრთველი დიაგრამაზე მიახლოების სანდოობის მნიშვნელობის (R^2) ჩასმა;

დააყენეთ ტრენდის ხაზის გადაკვეთის წერტილი Y-ღერძთან, რისთვისაც უნდა ჩართოთ ჩამრთველი მრუდის გადაკვეთა Y ღერძთან წერტილში;

დააწკაპუნეთ OK ღილაკს დიალოგური ფანჯრის დასახურად.

უკვე აშენებული ტრენდის ხაზის რედაქტირების დასაწყებად სამი გზა არსებობს:

გამოიყენეთ Selected trend line ბრძანება Format მენიუდან ტრენდის ხაზის არჩევის შემდეგ;

აირჩიეთ ბრძანება Format Trendline კონტექსტური მენიუდან, რომელიც გამოიძახება ტრენდის ხაზის მარჯვენა ღილაკით;

ტრენდის ხაზზე ორჯერ დაწკაპუნებით.

ეკრანზე გამოჩნდება Format Trendline დიალოგური ფანჯარა (ნახ. 3), რომელიც შეიცავს სამ ჩანართს: View, Type, Parameters და ბოლო ორის შინაარსი მთლიანად ემთხვევა Trendline დიალოგური ფანჯრის მსგავს ჩანართებს (ნახ. 1-2). ). View ჩანართზე შეგიძლიათ დააყენოთ ხაზის ტიპი, მისი ფერი და სისქე.

უკვე აგებული ტრენდის ხაზის წასაშლელად აირჩიეთ ტენდენციის ხაზი, რომელიც უნდა წაიშალოს და დააჭირეთ ღილაკს Delete.

განხილული რეგრესიული ანალიზის ინსტრუმენტის უპირატესობებია:

დიაგრამებზე ტენდენციის ხაზის გამოსახვის შედარებითი სიმარტივე მონაცემთა ცხრილის შექმნის გარეშე;

შემოთავაზებული ტენდენციის ხაზების ტიპების საკმაოდ ფართო ჩამონათვალი და ეს სია მოიცავს რეგრესიის ყველაზე ხშირად გამოყენებულ ტიპებს;

შესასწავლი პროცესის ქცევის წინასწარმეტყველების შესაძლებლობა თვითნებური (საღი აზრის ფარგლებში) ნაბიჯების წინ, ასევე უკან;

ტრენდის ხაზის განტოლების ანალიტიკური ფორმით მიღების შესაძლებლობა;

საჭიროების შემთხვევაში, მიახლოების სანდოობის შეფასების მიღების შესაძლებლობა.

ნაკლოვანებები მოიცავს შემდეგ პუნქტებს:

ტრენდის ხაზის მშენებლობა ხორციელდება მხოლოდ იმ შემთხვევაში, თუ არსებობს დიაგრამა, რომელიც აგებულია მონაცემთა სერიაზე;

შესასწავლი მახასიათებლისთვის მონაცემთა სერიების გენერირების პროცესი, მისთვის მიღებული ტენდენციის ხაზის განტოლებების საფუძველზე, გარკვეულწილად გადატვირთულია: სასურველი რეგრესიული განტოლებები განახლდება თავდაპირველი მონაცემთა სერიის მნიშვნელობების ყოველი ცვლილებით, მაგრამ მხოლოდ დიაგრამის ზონაში. , ხოლო მონაცემთა სერია, რომელიც ჩამოყალიბდა ძველი ხაზის განტოლების ტენდენციის საფუძველზე, უცვლელი რჩება;

PivotChart ანგარიშებში, როდესაც ცვლით დიაგრამის ხედს ან ასოცირებულ PivotTable ანგარიშს, არსებული ტენდენციების ხაზები არ არის დაცული, რაც ნიშნავს, რომ სანამ დახაზავთ ტენდენციურ ხაზებს ან სხვაგვარად დაფორმატებთ PivotChart ანგარიშის, თქვენ უნდა დარწმუნდეთ, რომ ანგარიშის განლაგება აკმაყოფილებს თქვენს მოთხოვნებს.

ტენდენციის ხაზები შეიძლება დაემატოს სქემებზე წარმოდგენილ მონაცემთა სერიებს, როგორიცაა გრაფიკი, ჰისტოგრამა, ბრტყელი არანორმალიზებული ფართობის დიაგრამები, ზოლები, სკატერი, ბუშტები და მარაგის სქემები.

თქვენ არ შეგიძლიათ დაამატოთ ტენდენციები მონაცემთა სერიებში 3-D, Standard, Radar, Pie და Donut დიაგრამებზე.

ჩაშენებული Excel ფუნქციების გამოყენება

Excel ასევე გთავაზობთ რეგრესიის ანალიზის ხელსაწყოს გრაფიკის არეალის მიღმა ტენდენციების გამოსათვლელად. ამ მიზნით შეიძლება გამოყენებულ იქნას მრავალი სტატისტიკური სამუშაო ფურცლის ფუნქცია, მაგრამ ყველა მათგანი საშუალებას გაძლევთ შექმნათ მხოლოდ წრფივი ან ექსპონენციალური რეგრესია.

Excel-ს აქვს რამდენიმე ფუნქცია ხაზოვანი რეგრესიის შესაქმნელად, კერძოდ:

TREND;

SLOPE და CUT.

ასევე რამდენიმე ფუნქცია ექსპონენციალური ტენდენციის ხაზის ასაგებად, კერძოდ:

LGRFPდაახ.

უნდა აღინიშნოს, რომ რეგრესიების აგების ტექნიკა TREND და GROWTH ფუნქციების გამოყენებით პრაქტიკულად იგივეა. იგივე შეიძლება ითქვას LINEST და LGRFPRIBL ფუნქციების წყვილზე. ამ ოთხი ფუნქციისთვის, მნიშვნელობების ცხრილის შექმნისას გამოიყენება Excel ფუნქციები, როგორიცაა მასივის ფორმულები, რაც გარკვეულწილად აფერხებს რეგრესიების აგების პროცესს. ჩვენ ასევე აღვნიშნავთ, რომ წრფივი რეგრესიის აგება, ჩვენი აზრით, ყველაზე ადვილია განსახორციელებელი SLOPE და INTERCEPT ფუნქციების გამოყენებით, სადაც პირველი განსაზღვრავს წრფივი რეგრესიის დახრილობას, ხოლო მეორე განსაზღვრავს რეგრესიის მიერ მოწყვეტილ სეგმენტს. y-ღერძზე.

რეგრესიის ანალიზისთვის ჩაშენებული ფუნქციების ხელსაწყოს უპირატესობებია:

შესწავლილი მახასიათებლის მონაცემთა სერიის ფორმირების საკმაოდ მარტივი პროცესი ყველა ჩაშენებული სტატისტიკური ფუნქციისთვის, რომელიც განსაზღვრავს ტენდენციის ხაზებს;

გენერირებული მონაცემთა სერიების საფუძველზე ტრენდის ხაზების აგების სტანდარტული ტექნიკა;

შესასწავლი პროცესის ქცევის პროგნოზირების უნარი წინ ან უკან ნაბიჯების საჭირო რაოდენობისთვის.

და უარყოფითი მხარეები მოიცავს იმ ფაქტს, რომ Excel-ს არ აქვს ჩაშენებული ფუნქციები სხვა (ხაზოვანი და ექსპონენციალური) ტიპის ტრენდული ხაზების შესაქმნელად. ეს გარემოება ხშირად არ იძლევა შესასწავლი პროცესის საკმარისად ზუსტი მოდელის არჩევის, ასევე რეალობასთან მიახლოებული პროგნოზების მიღების საშუალებას. გარდა ამისა, TREND და GROW ფუნქციების გამოყენებისას ტენდენციის ხაზების განტოლებები უცნობია.

უნდა აღინიშნოს, რომ ავტორებს არ დაუსახავთ სტატიის მიზანს, წარმოედგინათ რეგრესული ანალიზის კურსი სხვადასხვა ხარისხის სისრულით. მისი მთავარი ამოცანაა აჩვენოს Excel პაკეტის შესაძლებლობები კონკრეტული მაგალითების გამოყენებით დაახლოების ამოცანების გადაჭრაში; დემონსტრირება, თუ რა ეფექტური ინსტრუმენტები აქვს Excel-ს რეგრესიებისა და პროგნოზირების შესაქმნელად; აჩვენეთ, თუ რამდენად მარტივად შეიძლება გადაჭრას ასეთი პრობლემები თუნდაც მომხმარებლის მიერ, რომელსაც არ აქვს ღრმა ცოდნა რეგრესიული ანალიზის შესახებ.

კონკრეტული პრობლემების გადაჭრის მაგალითები

განვიხილოთ კონკრეტული პრობლემების გადაწყვეტა Excel პაკეტის ჩამოთვლილი ხელსაწყოების გამოყენებით.

დავალება 1

საავტომობილო სატრანსპორტო საწარმოს 1995-2002 წლების მოგების მონაცემების ცხრილით. თქვენ უნდა გააკეთოთ შემდეგი.

შექმენით სქემა.

დაამატეთ ხაზოვანი და პოლინომიური (კვადრატული და კუბური) ტენდენციის ხაზები სქემას.

ტრენდის ხაზის განტოლებების გამოყენებით, მიიღეთ ტაბულური მონაცემები საწარმოს მოგების შესახებ თითოეული ტრენდის ხაზისთვის 1995-2004 წლებში.

გააკეთეთ საწარმოს მოგების პროგნოზი 2003 და 2004 წლებში.

პრობლემის გადაწყვეტა

Excel-ის სამუშაო ფურცლის A4:C11 უჯრედების დიაპაზონში შევდივართ ნახ. 4.

B4:C11 უჯრედების დიაპაზონის არჩევის შემდეგ, ჩვენ ვაშენებთ დიაგრამას.

ვააქტიურებთ აგებულ დიაგრამას და ზემოთ აღწერილი მეთოდის გამოყენებით Trend Line დიალოგურ ფანჯარაში ტენდენციის ხაზის ტიპის არჩევის შემდეგ (იხ. სურ. 1), გრაფიკს მონაცვლეობით ვამატებთ ხაზოვან, კვადრატულ და კუბურ ტრენდულ ხაზებს. იმავე დიალოგურ ფანჯარაში გახსენით პარამეტრების ჩანართი (იხ. ნახ. 2), მიახლოებითი (გათლილი) მრუდის ველში შეიყვანეთ დასამატებელი ტენდენციის სახელი, ხოლო ველში Forecast forward for: პერიოდები, მითითებული. ღირებულება 2, რადგან დაგეგმილია მოგების პროგნოზის გაკეთება ორი წლის განმავლობაში. რეგრესიის განტოლებისა და მიახლოებითი სანდოობის R2 მნიშვნელობის გამოსაჩენად დიაგრამის არეში, ჩართეთ მოსანიშნი ველები განტოლების ეკრანზე ჩვენება და დიაგრამაზე მიახლოების სანდოობის (R^2) მნიშვნელობა. უკეთესი ვიზუალური აღქმისთვის ვცვლით გამოსახული ტენდენციის ხაზების ტიპს, ფერს და სისქეს, რისთვისაც ვიყენებთ Trend Line Format დიალოგური ფანჯრის View ჩანართს (იხ. სურ. 3). მიღებული დიაგრამა დამატებული ტენდენციის ხაზებით ნაჩვენებია ნახ. 5.

1995-2004 წლების თითოეული ტრენდის ხაზისთვის საწარმოს მოგების შესახებ ცხრილის მონაცემების მიღება. გამოვიყენოთ ნახატზე წარმოდგენილი ტენდენციის ხაზების განტოლებები. 5. ამისათვის D3:F3 დიაპაზონის უჯრედებში შეიყვანეთ ტექსტური ინფორმაცია შერჩეული ტრენდის ხაზის ტიპის შესახებ: Linear trend, Quadratic trend, Cubic trend. შემდეგი, შეიყვანეთ ხაზოვანი რეგრესიის ფორმულა D4 უჯრედში და შევსების მარკერის გამოყენებით დააკოპირეთ ეს ფორმულა D5:D13 უჯრედების დიაპაზონის შედარებითი მითითებით. უნდა აღინიშნოს, რომ D4:D13 უჯრედების დიაპაზონიდან წრფივი რეგრესიის ფორმულის მქონე თითოეულ უჯრედს არგუმენტად აქვს შესაბამისი უჯრედი A4:A13 დიაპაზონიდან. ანალოგიურად, კვადრატული რეგრესიისთვის უჯრედების დიაპაზონი E4:E13 ივსება, ხოლო კუბური რეგრესიისთვის უჯრედების დიაპაზონი F4:F13 ივსება. ამრიგად, გაკეთდა საწარმოს მოგების პროგნოზი 2003 და 2004 წლებში. სამი ტენდენციით. შედეგად მიღებული მნიშვნელობების ცხრილი ნაჩვენებია ნახ. 6.

დავალება 2

შექმენით სქემა.

ჩარტში დაამატეთ ლოგარითმული, ექსპონენციალური და ექსპონენციალური ტენდენციის ხაზები.

გამოიტანეთ მიღებული ტრენდის ხაზების განტოლებები, ასევე მიახლოების საიმედოობის R2 მნიშვნელობები თითოეული მათგანისთვის.

ტრენდის ხაზის განტოლებების გამოყენებით, მიიღეთ ტაბულური მონაცემები საწარმოს მოგების შესახებ თითოეული ტრენდის ხაზისთვის 1995-2002 წლებში.

გააკეთეთ ბიზნესის მოგების პროგნოზი 2003 და 2004 წლებისთვის ამ ტენდენციის ხაზების გამოყენებით.

პრობლემის გადაწყვეტა

1 პრობლემის გადაჭრისას მოცემული მეთოდოლოგიის მიხედვით ვიღებთ დიაგრამას დამატებული ლოგარითმული, ექსპონენციალური და ექსპონენციალური ტენდენციის ხაზებით (ნახ. 7). გარდა ამისა, მიღებული ტენდენციის ხაზის განტოლებების გამოყენებით, ჩვენ ვავსებთ საწარმოს მოგების მნიშვნელობების ცხრილს, მათ შორის პროგნოზირებულ მნიშვნელობებს 2003 და 2004 წლებში. (ნახ. 8).

ნახ. 5 და ნახ. ჩანს, რომ ლოგარითმული ტენდენციის მქონე მოდელი შეესაბამება მიახლოების სანდოობის ყველაზე დაბალ მნიშვნელობას

R2 = 0.8659

R2-ის უმაღლესი მნიშვნელობები შეესაბამება მოდელებს პოლინომიური ტენდენციით: კვადრატული (R2 = 0,9263) და კუბური (R2 = 0,933).

დავალება 3

1995-2002 წლებში საავტომობილო სატრანსპორტო საწარმოს მოგების შესახებ მონაცემების ცხრილით, რომელიც მოცემულია ამოცანა 1-ში, თქვენ უნდა შეასრულოთ შემდეგი ნაბიჯები.

მიიღეთ მონაცემთა სერიები წრფივი და ექსპონენციალური ტენდენციების ხაზებისთვის TREND და GROW ფუნქციების გამოყენებით.

TREND და GROWTH ფუნქციების გამოყენებით გააკეთეთ საწარმოს მოგების პროგნოზი 2003 და 2004 წლებში.

საწყისი მონაცემებისთვის და მიღებული მონაცემების სერიებისთვის შექმენით დიაგრამა.

პრობლემის გადაწყვეტა

გამოვიყენოთ დავალების 1-ლი სამუშაო ფურცელი (იხ. სურ. 4). დავიწყოთ TREND ფუნქციით:

აირჩიეთ D4:D11 უჯრედების დიაპაზონი, რომელიც უნდა იყოს შევსებული TREND ფუნქციის მნიშვნელობებით, რომელიც შეესაბამება საწარმოს მოგების შესახებ ცნობილ მონაცემებს;

გამოიძახეთ Function ბრძანება Insert მენიუდან. Function Wizard დიალოგურ ფანჯარაში, რომელიც გამოჩნდება, აირჩიეთ TREND ფუნქცია სტატისტიკური კატეგორიიდან და შემდეგ დააჭირეთ ღილაკს OK. იგივე ოპერაცია შეიძლება შესრულდეს სტანდარტული ხელსაწყოთა ზოლის ღილაკზე (Insert function) დაჭერით.

ფუნქციის არგუმენტების დიალოგურ ფანჯარაში, რომელიც გამოჩნდება, შეიყვანეთ C4:C11 უჯრედების დიაპაზონი Known_values_y ველში; Known_values_x ველში - უჯრედების დიაპაზონი B4:B11;

შეყვანილი ფორმულა მასივის ფორმულად რომ გახადოთ, გამოიყენეთ კლავიშთა კომბინაცია + + .

ფორმულა, რომელიც შევიტანეთ ფორმულების ზოლში, ასე გამოიყურება: =(TREND(C4:C11;B4:B11)).

შედეგად, უჯრედების D4:D11 დიაპაზონი ივსება TREND ფუნქციის შესაბამისი მნიშვნელობებით (ნახ. 9).

კომპანიის მოგების პროგნოზის გაკეთება 2003 და 2004 წლებში. საჭირო:

აირჩიეთ D12:D13 უჯრედების დიაპაზონი, სადაც შეიტანება TREND ფუნქციით პროგნოზირებული მნიშვნელობები.

გამოიძახეთ TREND ფუნქცია და ფუნქციის არგუმენტების დიალოგურ ფანჯარაში, რომელიც გამოჩნდება, შეიყვანეთ Known_values_y ველში - უჯრედების დიაპაზონი C4:C11; Known_values_x ველში - უჯრედების დიაპაზონი B4:B11; ხოლო ველში New_values_x - უჯრედების დიაპაზონი B12:B13.

გადააქციეთ ეს ფორმულა მასივის ფორმულად კლავიატურის მალსახმობის Ctrl + Shift + Enter გამოყენებით.

შეყვანილი ფორმულა ასე გამოიყურება: =(TREND(C4:C11;B4:B11;B12:B13)), ხოლო D12:D13 უჯრედების დიაპაზონი შეივსება TREND ფუნქციის პროგნოზირებული მნიშვნელობებით (იხ. 9).

ანალოგიურად, მონაცემთა სერია ივსება GROWTH ფუნქციის გამოყენებით, რომელიც გამოიყენება არაწრფივი დამოკიდებულებების ანალიზში და მუშაობს ზუსტად ისევე, როგორც მისი ხაზოვანი ანალოგი TREND.

სურათი 10 გვიჩვენებს ცხრილს ფორმულის ჩვენების რეჟიმში.

საწყისი მონაცემებისთვის და მიღებული მონაცემების სერიებისთვის ნაჩვენებია დიაგრამა ნახ. თერთმეტი.

დავალება 4

საავტომობილო სატრანსპორტო საწარმოს დისპეტჩერიზაციის სამსახურის მიერ სერვისებზე განაცხადების მიღების შესახებ მონაცემების ცხრილით მიმდინარე თვის 1-დან 11 დღემდე, უნდა შესრულდეს შემდეგი მოქმედებები.

წრფივი რეგრესიის მონაცემების სერიების მიღება: SLOPE და INTERCEPT ფუნქციების გამოყენებით; LINEST ფუნქციის გამოყენებით.

ამოიღეთ მონაცემთა სერია ექსპონენციალური რეგრესიისთვის LYFFPRIB ფუნქციის გამოყენებით.

ზემოაღნიშნული ფუნქციების გამოყენებით, გააკეთეთ პროგნოზი დისპეტჩერიზაციის სამსახურში განაცხადების მიღების შესახებ მიმდინარე თვის 12-დან 14 დღემდე.

ორიგინალური და მიღებული მონაცემების სერიებისთვის შექმენით დიაგრამა.

პრობლემის გადაწყვეტა

გაითვალისწინეთ, რომ TREND და GROW ფუნქციებისგან განსხვავებით, არცერთი ზემოთ ჩამოთვლილი ფუნქცია (SLOPE, INTERCEPTION, LINEST, LGRFPRIB) არ არის რეგრესია. ეს ფუნქციები თამაშობენ მხოლოდ დამხმარე როლს, განსაზღვრავენ აუცილებელ რეგრესიულ პარამეტრებს.

SLOPE, INTERCEPT, LINEST, LGRFPRIB ფუნქციების გამოყენებით აგებული წრფივი და ექსპონენციალური რეგრესიებისთვის, მათი განტოლებების გარეგნობა ყოველთვის ცნობილია, TREND და GROWTH ფუნქციების შესაბამისი წრფივი და ექსპონენციალური რეგრესიებისგან განსხვავებით.

1 . მოდით ავაშენოთ წრფივი რეგრესია, რომელსაც აქვს განტოლება:

y=mx+b

SLOPE და INTERCEPT ფუნქციების გამოყენებით, m რეგრესიის დახრილობა განისაზღვრება SLOPE ფუნქციით, ხოლო მუდმივი წევრი b - INTERCEPT ფუნქციით.

ამისათვის ჩვენ ვასრულებთ შემდეგ მოქმედებებს:

შეიყვანეთ წყაროს ცხრილი A4:B14 უჯრედების დიაპაზონში;

m პარამეტრის მნიშვნელობა განისაზღვრება C19 უჯრედში. სტატისტიკური კატეგორიიდან აირჩიეთ Slope ფუნქცია; შეიყვანეთ უჯრედების B4:B14 დიაპაზონი ცნობილი_მნიშვნელობები_y ველში და A4:A14 უჯრედების დიაპაზონი ცნობილი_მნიშვნელობები_x ველში. ფორმულა შევა C19 უჯრედში: =SLOPE(B4:B14;A4:A14);

მსგავსი მეთოდის გამოყენებით განისაზღვრება b პარამეტრის მნიშვნელობა D19 უჯრედში. და მისი შინაარსი ასე გამოიყურება: = INTERCEPT(B4:B14;A4:A14). ამრიგად, m და b პარამეტრების მნიშვნელობები, რომლებიც აუცილებელია ხაზოვანი რეგრესიის ასაგებად, შეინახება, შესაბამისად, უჯრედებში C19, D19;

შემდეგ C4 უჯრედში შევიყვანთ წრფივი რეგრესიის ფორმულას სახით: = $ C * A4 + $ D. ამ ფორმულაში უჯრედები C19 და D19 იწერება აბსოლუტური მითითებით (უჯრედის მისამართი არ უნდა შეიცვალოს შესაძლო კოპირებით). აბსოლუტური მითითების ნიშანი $ შეიძლება აკრიფოთ კლავიატურიდან ან F4 კლავიშის გამოყენებით, კურსორის უჯრედის მისამართზე განთავსების შემდეგ. შევსების სახელურის გამოყენებით დააკოპირეთ ეს ფორმულა უჯრედების დიაპაზონში C4:C17. ვიღებთ სასურველ მონაცემთა სერიას (სურ. 12). იმის გამო, რომ მოთხოვნის რაოდენობა არის მთელი რიცხვი, თქვენ უნდა დააყენოთ რიცხვის ფორმატი უჯრედის ფორმატის ფანჯრის Number ჩანართზე ათწილადების რაოდენობა 0-მდე.

2 . ახლა ავაშენოთ წრფივი რეგრესია, რომელიც მოცემულია განტოლებით:

y=mx+b

LINEST ფუნქციის გამოყენებით.

Ამისთვის:

შეიყვანეთ LINEST ფუნქცია მასივის ფორმულის სახით უჯრედების დიაპაზონში C20:D20: =(LINEST(B4:B14;A4:A14)). შედეგად ვიღებთ m პარამეტრის მნიშვნელობას C20 უჯრედში, ხოლო b პარამეტრის მნიშვნელობას D20 უჯრედში;

შეიყვანეთ ფორმულა D4 უჯრედში: =$C*A4+$D;

დააკოპირეთ ეს ფორმულა შევსების მარკერის გამოყენებით D4:D17 უჯრედების დიაპაზონში და მიიღეთ სასურველი მონაცემთა სერია.

3 . ჩვენ ვაშენებთ ექსპონენციალურ რეგრესიას, რომელსაც აქვს განტოლება:

LGRFPRIBL ფუნქციის დახმარებით, იგი შესრულებულია ანალოგიურად:

C21:D21 უჯრედების დიაპაზონში შეიყვანეთ ფუნქცია LGRFPRIBL, როგორც მასივის ფორმულა: =( LGRFPRIBL (B4:B14;A4:A14)). ამ შემთხვევაში, m პარამეტრის მნიშვნელობა დადგინდება C21 უჯრაში, ხოლო b პარამეტრის მნიშვნელობა D21 უჯრაში;

ფორმულა შეყვანილია უჯრედში E4: =$D*$C^A4;

შევსების მარკერის გამოყენებით, ეს ფორმულა კოპირდება უჯრედების დიაპაზონში E4:E17, სადაც განთავსდება ექსპონენციალური რეგრესიის მონაცემთა სერია (იხ. სურ. 12).

ნახ. 13 გვიჩვენებს ცხრილს, სადაც ჩვენ ვხედავთ ფუნქციებს, რომლებსაც ვიყენებთ უჯრედების საჭირო დიაპაზონებით, ასევე ფორმულებით.

ღირებულება რ 2 დაურეკა განსაზღვრის კოეფიციენტი.

რეგრესიული დამოკიდებულების აგების ამოცანაა ვიპოვოთ მოდელის (1) კოეფიციენტების m კოეფიციენტების ვექტორი, რომლის დროსაც კოეფიციენტი R იღებს მაქსიმალურ მნიშვნელობას.

R-ის მნიშვნელოვნების შესაფასებლად გამოიყენება ფიშერის F-ტესტი, რომელიც გამოითვლება ფორმულით

სად ნ- ნიმუშის ზომა (ექსპერიმენტების რაოდენობა);

k არის მოდელის კოეფიციენტების რაოდენობა.

თუ F აღემატება მონაცემებისთვის კრიტიკულ მნიშვნელობას ნდა კდა მიღებული ნდობის დონე, მაშინ R-ის მნიშვნელობა მნიშვნელოვნად ითვლება. F-ის კრიტიკული მნიშვნელობების ცხრილები მოცემულია მათემატიკური სტატისტიკის საცნობარო წიგნებში.

ამრიგად, R-ის მნიშვნელობა განისაზღვრება არა მხოლოდ მისი მნიშვნელობით, არამედ ექსპერიმენტების რაოდენობასა და მოდელის კოეფიციენტების (პარამეტრების) რაოდენობას შორის. მართლაც, კორელაციის კოეფიციენტი n=2-ისთვის მარტივი წრფივი მოდელისთვის არის 1 (სიბრტყეზე 2 წერტილიდან ყოველთვის შეგიძლიათ ერთი სწორი ხაზის დახატვა). თუმცა, თუ ექსპერიმენტული მონაცემები შემთხვევითი ცვლადებია, R-ის ასეთ მნიშვნელობას დიდი სიფრთხილით უნდა ვენდოთ. ჩვეულებრივ, მნიშვნელოვანი R და საიმედო რეგრესიის მისაღებად, ის მიზნად ისახავს იმის უზრუნველყოფას, რომ ექსპერიმენტების რაოდენობა მნიშვნელოვნად აღემატება მოდელის კოეფიციენტების რაოდენობას (n>k).

ხაზოვანი რეგრესიის მოდელის შესაქმნელად, თქვენ უნდა:

1) მოამზადეთ n მწკრივისა და m სვეტის სია, რომელიც შეიცავს ექსპერიმენტულ მონაცემებს (სვეტი, რომელიც შეიცავს გამომავალ მნიშვნელობას იუნდა იყოს სიაში პირველი ან ბოლო); მაგალითად, ავიღოთ წინა დავალების მონაცემები, დავამატოთ სვეტი სახელწოდებით „პერიოდული რიცხვი“, ნომრების რიცხვი 1-დან 12-მდე. (ეს იქნება მნიშვნელობები. X)

2) გადადით მენიუში მონაცემთა/მონაცემთა ანალიზი/რეგრესია

თუ მენიუში „მონაცემთა ანალიზი“ აკლია, მაშინ უნდა გადახვიდეთ იმავე მენიუს „დამატებების“ პუნქტში და მონიშნეთ ველი „ანალიზის პაკეტი“.

3) დიალოგურ ფანჯარაში "რეგრესია" დააყენეთ:

შეყვანის ინტერვალი Y;

შეყვანის ინტერვალი X;

გამომავალი ინტერვალი - ინტერვალის ზედა მარცხენა უჯრა, რომელშიც განთავსდება გაანგარიშების შედეგები (რეკომენდებულია მისი განთავსება ახალ სამუშაო ფურცელზე);