სტატისტიკებში მშენებარე მოდელის ხარისხის აღწერის ერთ-ერთი მაჩვენებელი არის განსაზღვრის კოეფიციენტი (R ^ 2), რომელსაც ასევე უწოდებენ ნდობის ღირებულებას. მასთან ერთად შეგიძლიათ განსაზღვროთ პროგნოზის სიზუსტის დონე. მოდით გაირკვეს, თუ როგორ შეგიძლიათ გამოთვალოთ ეს მაჩვენებელი სხვადასხვა Excel- ის გამოყენებით.
განსაზღვრის კოეფიციენტის გაანგარიშება
განსაზღვრის კოეფიციენტის დონის მიხედვით, ჩვეულებრივია მოდელების გაყოფა სამ ჯგუფად:
- 0.8 - 1 - კარგი ხარისხის მოდელი;
- 0.5 - 0.8 - მისაღები ხარისხის მოდელი;
- 0 - 0,5 - მოდელი დაბალი ხარისხის.
ამ უკანასკნელის შემთხვევაში, მოდელის ხარისხი მიუთითებს პროგნოზისთვის მისი გამოყენების შეუძლებლობაზე.
არჩევანი, თუ როგორ უნდა გამოვთვალოთ განსაზღვრული მნიშვნელობა Excel- ში, დამოკიდებულია იმაზე, თუ არა რეგრესია ხაზოვანი თუ არა. პირველ შემთხვევაში, შეგიძლიათ გამოიყენოთ ფუნქცია KVPIRSONდა მეორეში თქვენ უნდა გამოვიყენოთ სპეციალური ინსტრუმენტი ანალიზის პაკეტიდან.
მეთოდი 1: ხაზოვანი ფუნქციით განსაზღვრული კოეფიციენტის გაანგარიშება
უპირველეს ყოვლისა, გაირკვეს, თუ როგორ მოვძებნოთ კოეფიციენტი განსაზღვრავს წრფივი ფუნქციისთვის. ამ შემთხვევაში, ეს მაჩვენებელი კორელაციის კოეფიციენტის კვადრატის ტოლფასი იქნება. ჩვენ გამოვთვალოთ იგი ჩაშენებული Excel- ის ფუნქციის გამოყენებით კონკრეტული ცხრილის მაგალითზე, რომელიც ნაჩვენებია ქვემოთ.
- აირჩიეთ საკანში, სადაც განსაზღვრავს კოეფიციენტი მისი გაანგარიშების შემდეგ და დააჭირეთ ხატულას "ჩასმა ფუნქცია".
- იწყება ფუნქციის ოსტატი. გადატანა მის კატეგორიაში "სტატისტიკური" და დაასახელეთ სახელი KVPIRSON. შემდეგ დააჭირეთ ღილაკს "OK".
- ფუნქციის არგუმენტების ფანჯარა იწყება. KVPIRSON. სტატისტიკური ჯგუფის ეს ოპერატორი განკუთვნილია Pearson ფუნქციის კორელაციის კოეფიციენტის კვადრატის გამოთვლაზე, ანუ ხაზოვანი ფუნქცია. და როგორც ჩვენ გვახსოვს, წრფივი ფუნქციით, განსაზღვრის კოეფიციენტი მხოლოდ კორელაციის კოეფიციენტის კვადრატის ტოლია.
ამ განცხადების სინტაქსია:
= KVPIRSON (known_y; well-known_x)ამრიგად, ფუნქციას ორი ოპერატორი აქვს, რომელთაგან ერთ-ერთია ფუნქციის ღირებულებები და მეორე არის არგუმენტი. ოპერატორები შეიძლება წარმოდგენილი იყოს როგორც უშუალოდ სიდიდეზე,;), და ფორმების კავშირების მერყეობს, სადაც ისინი მდებარეობს. ეს არის ბოლო ვარიანტი, რომელიც გამოყენებული იქნება ამ მაგალითში.
კურსორის დაყენება ამ სფეროში "ცნობილი Y ღირებულებები". ჩვენ ვვარჯიშობთ მაუსის მარცხენა ღილაკს და აირჩიეთ სვეტის შინაარსი. "Y" მაგიდები. როგორც ხედავთ, მითითებული მონაცემების მასივის მისამართი მაშინვე გამოჩნდება ფანჯარაში.
ანალოგიურად შეავსეთ ველი "ცნობილი x". ამ სფეროში კურსორის დაყენება, მაგრამ ამ დროს აირჩიეთ სვეტის მნიშვნელობები "X".
მას შემდეგ, რაც ყველა მონაცემები ნაჩვენები არგუმენტები ფანჯარაში KVPIRSONდააჭირეთ ღილაკს "OK"მდებარეობს მის ძალიან ბოლოში.
- როგორც ხედავთ, ამის შემდეგ პროგრამა განსაზღვრავს განსაზღვრის კოეფიციენტს და დააბრუნებს უჯრედის შედეგს ფუნქციის ოსტატები. ჩვენს მაგალითში გაანგარიშებული ინდიკატორის ღირებულება აღმოჩნდა 1. ეს ნიშნავს, რომ წარმოდგენილი მოდელი აბსოლუტურად საიმედოა, ანუ ის შეცდომას გამორიცხავს.
გაკვეთილი: ფუნქციის ოსტატი Microsoft Excel- ში
მეთოდი 2: განსაზღვრების კოეფიციენტის გაანგარიშება არაწრფივი ფუნქციებით
მაგრამ სასურველი ღირებულების გაანგარიშების ზემოთ გამოყენებული ვარიანტი შეიძლება გამოყენებულ იქნას მხოლოდ წრფივი ფუნქციებისათვის. რა უნდა გავაკეთოთ იმისათვის, რომ წარმოადგინოს მისი გაანგარიშება არაწრფივი ფუნქციით? Excel- ში ასეთი შესაძლებლობა არსებობს. ეს შეიძლება გაკეთდეს ინსტრუმენტი. "რეგრესია"რომელიც არის პაკეტის ნაწილი "მონაცემთა ანალიზი".
- მაგრამ ამ ინსტრუმენტის გამოყენებამდე უნდა გააქტიუროთ იგი. "ანალიზის პაკეტი"რომელიც ჩვეულებრივ გამორთულია Excel- ში. გადატანა tab- ზე "ფაილი"და შემდეგ გაიაროს ნივთი "პარამეტრები".
- გახსნილი ფანჯარაში გადავედით განყოფილებაში. მოდულების დამატება ნავიგაციის მეშვეობით მარცხენა ვერტიკალური მენიუდან. ქვედა მარჯვენა სარკმელზე არის ველი "მენეჯმენტი". არსებული ქვედანაყოფების სიიდან აირჩიეთ სახელი "Excel add-ins ..."და შემდეგ დააჭირეთ ღილაკს "წადი ..."რომელიც მდებარეობს მინდვრის მარჯვენა მხარეს.
- Add-ons ფანჯარა იწყება. ცენტრალურ ნაწილში არის დამატებითი მოდულების სია. შეამოწმეთ ყუთი პოზიციის შემდეგ "ანალიზის პაკეტი". ამის შემდეგ დააჭირეთ ღილაკს. "OK" ინტერფეისის ფანჯრის მარჯვენა მხარეს.
- Tool პაკეტი "მონაცემთა ანალიზი" მიმდინარე რეჟიმში Excel- ის გააქტიურება. მასზე წვდომა მდებარეობს ლენტიდან ლენტით "მონაცემები". გადატანა მითითებულ ჩანართზე და დააჭირეთ ღილაკს. "მონაცემთა ანალიზი" პარამეტრების ჯგუფში "ანალიზი".
- გააქტიურებული ფანჯარა "მონაცემთა ანალიზი" სპეციალური ინფორმაციის დამუშავების ინსტრუმენტების სიაში. აირჩიეთ ამ სიიდან "რეგრესია" და დააჭირეთ ღილაკს "OK".
- შემდეგ ფანჯრის ფანჯარა იხსნება. "რეგრესია". პარამეტრების პირველი ბლოკი - "შეყვანა". აქ ორ სფეროში თქვენ უნდა მიუთითოთ მერყეობის მისამართები, სადაც არგუმენტი ღირებულებები და ფუნქციები მდებარეობს. განათავსეთ კურსორი სფეროში "შეყვანის ინტერვალი Y" და აირჩიეთ სვეტის შინაარსი ფურცელზე "Y". მას შემდეგ, რაც მასივის მისამართი გამოჩნდება ფანჯარაში "რეგრესია"დააყენა კურსორი სფეროში "შეყვანის ინტერვალი Y" და ზუსტად იგივე გზით აირჩიეთ სვეტის უჯრედები "X".
პარამეტრების შესახებ "Tag" და "მუდმივი ნულოვანი" ჩანართები არ არის მითითებული. ჩამრთველი შეიძლება დააყენოთ პარამეტრის მახლობლად "სანდოობის დონე" და საველე საპირისპიროდ, მიუთითეთ შესაბამისი მაჩვენებლის სასურველი ღირებულება (შესაბამისად 95%).
ჯგუფში "გამოყვანის პარამეტრები" თქვენ უნდა მიუთითოთ, თუ რომელი ფართობი იქნება გაანგარიშების შედეგი. არსებობს სამი ვარიანტი:
- ფართობი მიმდინარე ფურცელზე;
- სხვა ფურცელი;
- კიდევ ერთი წიგნი (ახალი ფაილი).
მოდით შეწყვიტოს არჩევანი პირველ ვარიანტზე, რომ პირველადი მონაცემები და შედეგი ერთ სამუშაოზე განთავსდა. განათავსეთ შეცვლა პარამეტრის მახლობლად "გამოყვანის ინტერვალი". სფეროში საპირისპირო ამ პუნქტის დააყენა კურსორი. ჩვენ დააჭირეთ მაუსის მარცხენა ღილაკს ცარიელი ელემენტის ფურცელზე, რომელიც განკუთვნილია გაანგარიშების შედეგების ცხრილის მარცხენა ზედა საკანში. ამ ელემენტის მისამართი უნდა გამოჩნდეს ფანჯარაში "რეგრესია".
პარამეტრების ჯგუფები "რჩება" და "ჩვეულებრივი ალბათობა" იგნორირება, რადგან ისინი პრობლემის გადაჭრისთვის მნიშვნელოვანი არ არის. ამის შემდეგ დააჭირეთ ღილაკს. "OK"რომელიც მდებარეობს ფანჯრის ზედა მარჯვენა კუთხეში "რეგრესია".
- პროგრამა ითვლის ადრე შესული მონაცემების საფუძველზე და აჩვენებს შედეგს განსაზღვრულ დიაპაზონში. როგორც ხედავთ, ეს ინსტრუმენტი აჩვენებს ფურცელზე საკმაოდ დიდ რაოდენობას შედეგებზე სხვადასხვა პარამეტრებზე. მაგრამ მიმდინარე გაკვეთილის კონტექსტში ჩვენ დაინტერესებული ვართ ინდიკატორისთვის "რ-კვადრატი". ამ შემთხვევაში, ეს არის 0.947664, რომელიც ახასიათებს შერჩეულ მოდელს კარგი ხარისხის მოდელად.
მეთოდი 3: განსაზღვრა კოეფიციენტი ტენდენციის ხაზისთვის
გარდა ზემოაღნიშნული ვარიანტებისა, განსაზღვრის კოეფიციენტი შეიძლება გამოიხატოს პირდაპირ ტენდენციის ხაზში Excel ცხრილზე აგებული გრაფაში. ჩვენ დავინახავთ, როგორ შეიძლება გაკეთდეს კონკრეტული მაგალითი.
- ჩვენ გვყავს გრაფა დაყრდნობით იმ ფუნქციის არგუმენტების და ღირებულებების ცხრილზე, რომლებიც გამოყენებულ იქნა წინა მაგალითზე. მოდით გავაკეთოთ ტენდენცია ხაზი. ჩვენ დააკლიკეთ ნებისმიერ ადგილას სამშენებლო ზონაში, რომელზეც გრაფა მოთავსებულია მაუსის მარცხენა ღილაკით. ამავე დროს, ჩანართების დამატებითი კომპლექტი გამოჩნდება ლენტი - "შარტებთან მუშაობა". გადადით tab- ზე "განლაგება". ღილაკზე დაჭერით "Trend line"რომელიც მდებარეობს ინსტრუმენტების ბლოკში "ანალიზი". მენიუ გამოჩნდება ტენდენციის ხაზის ტიპის არჩევასთან ერთად. ჩვენ შეწყვიტოს არჩევანი ტიპის, რომელიც შეესაბამება კონკრეტული ამოცანა. ჩვენი მაგალითისთვის, მოდით აირჩიოთ "გადაჭარბებული დაახლოება".
- Excel აშენებს ტენდენციის ხაზს დამატებითი შავი მრუდის სახით charting plane- ზე.
- ახლა ჩვენი ამოცანაა თავად განსაზღვროს კოეფიციენტი. ჩვენ უფლება დააწკაპუნეთ ტენდენციის ხაზზე. კონტექსტური მენიუ გააქტიურებულია. შეაჩერე შერჩევა მასზე "ტენდენციის ხაზის ფორმატი ...".
ტენდენციის ხაზის ფორმატის ფანჯრის გადასვლისთვის ალტერნატიული მოქმედების შესრულება შეგიძლიათ. აირჩიეთ ტენდენციის ხაზი დაწკაპვით მასზე მაუსის მარცხენა ღილაკს. გადატანა tab- ზე "განლაგება". ღილაკზე დაჭერით "Trend line" ბლოკში "ანალიზი". სიაში, რომელიც ხსნის, ჩვენ დააჭირეთ ბოლო პუნქტში სიაში ქმედებები - "Advanced Trend Line პარამეტრები ...".
- ზემოაღნიშნული ორი ქმედების შემდეგ, შეიქმნა ფორმატის ფანჯარა, რომელშიც შეგიძლიათ დამატებითი პარამეტრები გააკეთოთ. კერძოდ, ჩვენი დავალების შესასრულებლად, აუცილებელია, შეამოწმოთ ყუთი "განათავსეთ სქემა ღირებულება სიზუსტის დაახლოება (R ^ 2)". იგი მდებარეობს ფანჯრის ძალიან ბოლოში. ანუ ამ გზით ჩვენ მოიცავს მშენებლობის არეალის განსაზღვრის კოეფიციენტის ჩვენებას. მაშინ არ უნდა დაგვავიწყდეს, დააჭირეთ ღილაკს "დახურვა" მიმდინარე ფანჯრის ბოლოში.
- სიახლოვის ნდობის ღირებულება, რომელიც განსაზღვრავს კოეფიციენტის განსაზღვრის ღირებულებას, ნაჩვენები იქნება ფურცლის ტერიტორიაზე. ამ შემთხვევაში, ეს ღირებულება, როგორც ვხედავთ, არის 0.9242, რომელიც ახასიათებს დაახლოებას, როგორც კარგი ხარისხის მოდელი.
- აბსოლუტურად ზუსტად ისე, რომ თქვენ შეგიძლიათ დააყენოთ ჩვენება კოეფიციენტი განსაზღვრის ნებისმიერი სხვა ტიპის ტენდენცია. თქვენ შეგიძლიათ შეცვალოთ ტიპის ტენდენციის ხაზის მეშვეობით გადატანა ღილაკზე ლენტი ან კონტექსტური მენიუში პარამეტრების ფანჯარაში, როგორც ეს ნაჩვენებია ზემოთ. შემდეგ უკვე ფანჯარაში "ტენდენციის ხაზის შექმნა" შეიძლება სხვა ტიპის გადავიდეს. ნუ დაგავიწყდებათ, რომ კონტროლი ისე, რომ ახლოს წერტილი "მოათავსეთ სქემა ღირებულება დაახლოების სიზუსტით" შემოწმდა. ზემოთ აღნიშნული ნაბიჯის დასრულების შემდეგ დააჭირეთ ღილაკს. "დახურვა" ფანჯრის ქვედა მარჯვენა კუთხეში.
- ხაზოვანი ტიპის შემთხვევაში, ტენდენციურ ხაზს უკვე აქვს დაახლოების ნდობა 0.9477, რომელიც ახასიათებს ამ მოდელს უფრო საიმედოდ, ვიდრე ექსპონენციალური ტიპის ტენდენციის შედარება.
- ამრიგად, გადართვა სხვადასხვა ტიპის ტენდენციის ხაზები და შედარების მათი ღირებულებების დაახლოების (განსაზღვრა კოეფიციენტი), შეგიძლიათ იპოვოთ ვარიანტი, რომლის მოდელიც ზუსტად აღწერს წარმოდგენილი გრაფიკს. ყველაზე საიმედოობის ვარიანტის განსაზღვრა ყველაზე მაღალია. ამის საფუძველზე შეგიძლიათ აშენდეს ყველაზე ზუსტი პროგნოზი.
მაგალითად, ჩვენი საქმე ექსპერიმენტის საშუალებით შევძელით დავამყაროთ, რომ უმაღლესი დონის ნდობა არის მეორე დონის ტენდენციის ხაზის პოლინომური ტიპი. ამ შემთხვევაში განსაზღვრული კოეფიციენტი უდრის 1. ეს ნიშნავს, რომ ეს მოდელი აბსოლუტურად საიმედოა, რაც ნიშნავს შეცდომების სრულ აღმოფხვრას.
მაგრამ ამავე დროს, ეს არ ნიშნავს იმას, რომ ამ ტიპის ტენდენცია კიდევ უფრო საიმედო იქნება სხვა ჩარტში. ტენდენციის ტიპის ოპტიმალური არჩევანი დამოკიდებულია ფუნქციის ტიპზე, რომლის საფუძველზეც გრაფა აშენდა. თუ მომხმარებელი არ არის საკმარისი ცოდნა, რათა შეაფასოს უმაღლესი ხარისხის ვარიანტი, მაშინ ერთადერთი გზა, რომელიც განსაზღვრავს საუკეთესო პროგნოზს, არის შედარება კოეფიციენტების განსაზღვრა, როგორც ნაჩვენებია ზემოთ მოყვანილ მაგალითში.
იხილეთ ასევე:
შენობის ტენდენციის ხაზები Excel- ში
Excel დაახლოება
Excel- ში ორ ძირითად ვარიანტს განსაზღვრავს კოეფიციენტის განსაზღვრა: ოპერატორის გამოყენებით KVPIRSON და აპლიკაციის ინსტრუმენტი "რეგრესია" საწყისი პაკეტის ინსტრუმენტები "მონაცემთა ანალიზი". ამ შემთხვევაში, ამ ვარიანტების პირველი ვარიანტი განკუთვნილია მხოლოდ წრფივი ფუნქციის დამუშავებაში და სხვა ვარიანტი შეიძლება გამოყენებულ იქნას თითქმის ყველა სიტუაციაში. ამასთან, შესაძლებელია გრაფიკების ტენდენციის ხაზის განსაზღვრის კოეფიციენტი, როგორც ნდობის ღირებულების დაახლოება. ამ ინდიკატორის გამოყენებით, შესაძლებელია განსაზღვროს ტენდენციის ტიპის ტიპი, რომელსაც აქვს განსაკუთრებული ფუნქციის მაღალი ნდობის დონე.
