სტატისტიკაში აგებული მოდელის ხარისხის აღწერის ერთ – ერთი მაჩვენებელი არის განსაზღვრულობის კოეფიციენტი (R ^ 2), რომელსაც ასევე უწოდებენ მიახლოებითი ნდობის მნიშვნელობას. მასთან ერთად შეგიძლიათ განსაზღვროთ პროგნოზის სიზუსტის დონე. მოდით გავეცნოთ როგორ შეგიძლიათ გამოთვალოთ ეს მაჩვენებელი სხვადასხვა Excel ინსტრუმენტების გამოყენებით.
განსაზღვრის კოეფიციენტის გაანგარიშება
განსაზღვრის კოეფიციენტის დონიდან გამომდინარე, ჩვეულებრივია მოდელების დაყოფა სამ ჯგუფად:
- 0.8 - 1 - კარგი ხარისხის მოდელი;
- 0.5 - 0.8 - მისაღები ხარისხის მოდელი;
- 0 - 0.5 - უხარისხო მოდელი.
ამ უკანასკნელ შემთხვევაში, მოდელის ხარისხი მიუთითებს პროგნოზისთვის მისი გამოყენების შეუძლებლობაზე.
არჩევანი, თუ როგორ უნდა გამოვთვალოთ მითითებული მნიშვნელობა Excel- ზე, დამოკიდებულია იმაზე, რეგრესია არის ხაზოვანი თუ არა. პირველ შემთხვევაში, თქვენ შეგიძლიათ გამოიყენოთ ფუნქცია KVPIRSON, ხოლო მეორეში თქვენ უნდა გამოიყენოთ სპეციალური ინსტრუმენტი ანალიზის პაკეტიდან.
მეთოდი 1: განსაზღვრის კოეფიციენტის გამოთვლა ხაზოვანი ფუნქციით
უპირველეს ყოვლისა, ჩვენ გაირკვევა, თუ როგორ უნდა მოვძებნოთ ხაზოვანი ფუნქციის განსაზღვრის კოეფიციენტი. ამ შემთხვევაში, ეს მაჩვენებელი ტოლი იქნება კორელაციის კოეფიციენტის კვადრატში. ჩვენ გამოვთვლით მასში ჩაშენებული Excel ფუნქციის გამოყენებით კონკრეტული ცხრილის მაგალითზე, რომელიც მოცემულია ქვემოთ.
- შეარჩიეთ უჯრედი, სადაც მისი გამოანგარიშების შემდეგ გამოჩნდება განსაზღვრის კოეფიციენტი და დააჭირეთ ხატულას "ფუნქციის ჩასმა".
- იწყებს მხატვრული ოსტატი. გადადის მის კატეგორიაში "სტატისტიკური" და მონიშნეთ სახელი KVPIRSON. შემდეგ დააჭირეთ ღილაკს "კარგი".
- ფუნქციის არგუმენტების ფანჯარა იწყება. KVPIRSON. სტატისტიკური ჯგუფის ეს ოპერატორი შექმნილია პერსონის ფუნქციის კორელაციის კოეფიციენტის კვადრატის გამოსაანგარიშებლად, ანუ წრფივი ფუნქციით. და როგორც მახსოვს, წრფივი ფუნქციით, განსაზღვრის კოეფიციენტი ზუსტად ტოლია კორელაციის კოეფიციენტის კვადრატში.
ამ განცხადების სინტაქსია:
= KVPIRSON (ცნობილია_ი მნიშვნელობები_; ცნობილი_x მნიშვნელობები)ამრიგად, ფუნქციას ორი ოპერატორი ჰყავს, რომელთაგან ერთი ფუნქციების მნიშვნელობების ჩამონათვალია, მეორე კი არგუმენტი. ოპერატორები შეიძლება წარმოდგენილი იყოს პირდაპირ, როგორც მნიშვნელობებით, რომლებიც ჩამოთვლილია ნახევარწრის საშუალებით (;) და ბმულების სახით იმ დიაპაზონებთან, სადაც ისინი მდებარეობს. ეს უკანასკნელი ვარიანტია, რომელსაც ჩვენს მაგალითში გამოიყენებენ.
დააყენეთ კურსორი ველში ცნობილი y ღირებულებები. ჩვენ მაუსის მარცხენა ღილაკს ვატარებთ და ვარჩევთ სვეტის შინაარსს "Y" მაგიდები. როგორც ხედავთ, მითითებული მონაცემთა მასივის მისამართი დაუყოვნებლივ ნაჩვენებია ფანჯარაში.
ანალოგიურად, შეავსეთ ველი ცნობილი x ღირებულებები. განათავსეთ კურსორი ამ ველში, მაგრამ ამჯერად აირჩიეთ სვეტის მნიშვნელობები "X".
მას შემდეგ, რაც ყველა მონაცემი ნაჩვენებია არგუმენტების ფანჯარაში KVPIRSONდააჭირეთ ღილაკს "კარგი"მდებარეობს მის ბოლოში.
- როგორც ხედავთ, ამის შემდეგ პროგრამა ითვლის განსაზღვრის კოეფიციენტს და აჩვენებს შედეგს უჯრედში, რომელიც შერჩეულ იქნა ზარის მიღებამდეც კი ფუნქციის ოსტატები. ჩვენს მაგალითში, გამოთვლილი ინდიკატორის მნიშვნელობა აღმოჩნდა 1. ეს ნიშნავს, რომ წარმოდგენილი მოდელი აბსოლუტურად საიმედოა, ანუ ის გამორიცხავს შეცდომას.
გაკვეთილი: მხატვრული ოსტატი Microsoft Excel- ში
მეთოდი 2: განსაზღვრა კოეფიციენტის გამოთვლა არაწრფივი ფუნქციებით
მაგრამ სასურველი მნიშვნელობის გამოსათვლელად ზემოთ მოცემული ვარიანტი შეიძლება გამოყენებულ იქნას მხოლოდ ხაზოვან ფუნქციებზე. რა უნდა გავაკეთოთ, რომ გამოვთვალოთ ის არაწრფივი ფუნქციით? Excel- ში ასეთი შესაძლებლობა არსებობს. ეს შეიძლება გაკეთდეს ხელსაწყოთი. "რეგრესი"რომელიც პაკეტის ნაწილია "მონაცემთა ანალიზი".
- მაგრამ მითითებული ხელსაწყოს გამოყენებამდე თქვენ თავად უნდა გააქტიუროთ ანალიზის პაკეტი, რომელიც გამორთულია Excel- ში. ჩანართზე გადასვლა ფაილიდა შემდეგ წასვლა "პარამეტრები".
- ფანჯარაში, რომელიც იხსნება, გადადით განყოფილებაში "დამატებები" მარცხენა ვერტიკალური მენიუს ნავიგაციით. ფანჯრის მარჯვენა პანელის ბოლოში არის ველი "მენეჯმენტი". აქ არსებული ქვესექციების ჩამონათვლიდან აირჩიეთ სახელი "Excel დანამატები ..."და შემდეგ დააჭირეთ ღილაკს "წადი ..."მდებარეობს მარჯვნივ.
- დანამატების ფანჯარა ამოქმედდა. მის ცენტრალურ ნაწილში არის ხელმისაწვდომი დანამატების ჩამონათვალი. დააყენეთ ყუთი პოზიციის გვერდით ანალიზის პაკეტი. ამის შემდეგ დააჭირეთ ღილაკს "კარგი" ფანჯრის ინტერფეისის მარჯვენა მხარეს.
- ხელსაწყოს პაკეტი "მონაცემთა ანალიზი" Excel- ის მიმდინარე მაგალითში გააქტიურდება. მასზე დაშვება განლაგებულია ჩანართის ფირზე "მონაცემები". ჩვენ გადავდივართ მითითებულ ჩანართზე და ღილაკს ვაწვებით "მონაცემთა ანალიზი" პარამეტრების ჯგუფში "ანალიზი".
- ფანჯარა გააქტიურებულია "მონაცემთა ანალიზი" ინფორმაციის დამუშავების სპეციალიზებული ინსტრუმენტების ჩამონათვალში. აირჩიეთ ამ სიიდან "რეგრესი" და დააჭირეთ ღილაკს "კარგი".
- შემდეგ იხსნება ხელსაწყოს ფანჯარა "რეგრესი". პარამეტრების პირველი ბლოკი არის "შეყვანა". აქ ორ ველში უნდა მიუთითოთ დიაპაზონის მისამართები, სადაც განთავსებულია არგუმენტის და ფუნქციის მნიშვნელობები. კურსორი განათავსეთ მინდორში "შეყვანის ინტერვალი Y" შეარჩიეთ სვეტის შინაარსი ფურცელზე "Y". მასივის მისამართის ფანჯრის ჩვენების შემდეგ "რეგრესი"მოათავსეთ კურსორი მინდორში "შეყვანის ინტერვალი Y" და შეარჩიეთ სვეტის უჯრედები ზუსტად იმავე გზით "X".
პარამეტრების შესახებ "ეტიკეტი" და მუდმივი ნულოვანი არ დააყენოთ დროშები. პანელის პარამეტრის გვერდი შეიძლება დააყენოთ. "საიმედოობის დონე" და საპირისპირო ველში მიუთითეთ შესაბამისი ინდიკატორის სასურველი მნიშვნელობა (სტანდარტულად 95%).
ჯგუფში გამოყვანის პარამეტრები თქვენ უნდა მიუთითოთ რომელ სფეროში გამოჩნდება გამოთვლების შედეგი. არსებობს სამი ვარიანტი:
- ფართობი მიმდინარე ფურცელზე;
- კიდევ ერთი ფურცელი;
- კიდევ ერთი წიგნი (ახალი ფაილი).
მოდით ავირჩიოთ პირველი ვარიანტი ისე, რომ წყარო მონაცემები და შედეგი განთავსდეს იმავე სამუშაო ფურცელზე. ჩვენ გადართეთ შეცვლა პარამეტრის მახლობლად "გამოყვანის ინტერვალი". ამ ნივთის მოპირდაპირე მხარეს, განათავსეთ კურსორი. მარცხენა ღილაკით დადეთ ფურცელზე ცარიელი ელემენტი, რომელიც შექმნილია იმისთვის, რომ გახდეს საანგარიშო გამომავალი ცხრილის ზედა მარცხენა უჯრედი. ამ ელემენტის მისამართი უნდა იყოს ნაჩვენები ფანჯრის ველში "რეგრესი".
პარამეტრების ჯგუფები "დარჩენილი" და "ნორმალური ალბათობა" უგულებელყოფა, რადგან ისინი არ არიან მნიშვნელოვანი ამოცანის გადასაჭრელად. ამის შემდეგ დააჭირეთ ღილაკს "კარგი"მდებარეობს ფანჯრის ზედა მარჯვენა კუთხეში "რეგრესი".
- პროგრამა ითვლის ადრე შეყვანილ მონაცემებზე დაყრდნობით და აჩვენებს შედეგს მითითებულ დიაპაზონში. როგორც ხედავთ, ეს ინსტრუმენტი აჩვენებს შედეგების საკმაოდ დიდ რაოდენობას სხვადასხვა პარამეტრებზე ფურცელზე. მაგრამ მიმდინარე გაკვეთილის თვალსაზრისით, ჩვენ გვაინტერესებს ეს მაჩვენებელი რ-მოედანი. ამ შემთხვევაში, ეს ტოლია 0.947664, რაც ახასიათებს არჩეულ მოდელს, როგორც კარგი ხარისხის მოდელს.
მეთოდი 3: ტენდენციის ხაზის განსაზღვრის კოეფიციენტი
ზემოთ ჩამოთვლილი ვარიანტების გარდა, განსაზღვრის კოეფიციენტი შეიძლება პირდაპირ აჩვენოთ ტენდენციის ხაზისთვის Excel სამუშაო ფურცელზე აგებულ გრაფიკზე. ჩვენ გაირკვეს, თუ როგორ შეიძლება ეს გაკეთდეს კონკრეტული მაგალითით.
- ჩვენ გვაქვს გრაფიკი, რომელიც მოცემულია არგუმენტების ცხრილისა და ფუნქციის მნიშვნელობების საფუძველზე, რომელიც გამოყენებული იყო წინა მაგალითისთვის. ჩვენ ავაშენებთ მას ტრენდულ ხაზს. ჩვენ დააჭირეთ სამშენებლო ტერიტორიის ნებისმიერ ადგილს, რომელზეც ჩარტშია განთავსებული, თაგვის მარცხენა ღილაკით. ამავე დროს, ჩანართების დამატებითი ნაკრები გამოჩნდება ლენტზე - "გრაფიკებთან მუშაობა". ჩანართზე გადასვლა "განლაგება". დააჭირეთ ღილაკს ტენდენციის ხაზირომელიც მდებარეობს ხელსაწყოს ბლოკში "ანალიზი". მენიუში ჩანს ტრენდული ხაზის ტიპის არჩევანი. ჩვენ ვჩერდებით იმ ტიპის არჩევანს, რომელიც შეესაბამება კონკრეტულ დავალებას. მოდით აირჩიოთ ვარიანტი ჩვენი მაგალითისთვის "ექსპონენციური მიახლოება".
- Excel აშენებს ტენდენციის ხაზს დამატებით შავი მრუდი სახით ჩარტში.
- ახლა ჩვენი ამოცანაა თავად განსაზღვროს კოეფიციენტის კოეფიციენტი. დააწკაპუნეთ მარჯვნივ, ტენდენციის ხაზზე. კონტექსტური მენიუ გააქტიურებულია. ჩვენ ვწყვეტთ არჩევანს მასში "ტრენდის ხაზის ფორმატი ...".
ტრენდის ხაზის ფორმატის ფანჯარაში გადასვლის შესასრულებლად შეგიძლიათ შეასრულოთ ალტერნატიული მოქმედება. აირჩიეთ ტენდენციის ხაზი, დააჭირეთ მასზე მაუსის მარცხენა ღილაკზე. ჩანართზე გადასვლა "განლაგება". დააჭირეთ ღილაკს ტენდენციის ხაზი ბლოკში "ანალიზი". სიაში, რომელიც იხსნება, დააჭირეთ მოქმედებების ჩამონათვალს ბოლო პუნქტზე - "დამატებითი ტენდენციის ხაზის პარამეტრი ...".
- რომელიმე ზემოთ ჩამოთვლილი ორი მოქმედების შემდეგ, შეიქმნა ფორმატის ფანჯარა, რომელშიც შეგიძლიათ დამატებითი პარამეტრები. კერძოდ, ჩვენი დავალების შესასრულებლად, საჭიროა შემდეგი ყუთის შემოწმება "დიაგრამაზე მიიტანეთ მიახლოებითი ნდობის მნიშვნელობა (R ^ 2)". იგი მდებარეობს ფანჯრის ბოლოში. ანუ, ამ გზით ჩვენ შესაძლებლობას ვცემთ განსაზღვროს კოეფიციენტის კოეფიციენტი სამშენებლო ფართობზე. შემდეგ არ უნდა დაგვავიწყდეს, რომ დააჭიროთ ღილაკს დახურვა მიმდინარე ფანჯრის ბოლოში.
- მიახლოებითი საიმედოობის მნიშვნელობა, ანუ განსაზღვრის კოეფიციენტის მნიშვნელობა, ნაჩვენები იქნება ფურცელზე სამშენებლო ზონაში. ამ შემთხვევაში, ეს მნიშვნელობა, როგორც ვხედავთ, არის 0.9242, რაც ახასიათებს მიახლოებას, როგორც კარგი ხარისხის მოდელს.
- აბსოლუტურად ზუსტად ამ გზით შეგიძლიათ დააყენოთ განსაზღვრის კოეფიციენტის ჩვენება ნებისმიერი სხვა ტიპის ტრენდული ხაზისთვის. თქვენ შეგიძლიათ შეცვალოთ ტენდენციის ხაზის სახეობა ღილაკზე გადასვლის გზით ლენტიზე ან კონტექსტურ მენიუში მისი პარამეტრების ფანჯარაში, როგორც ეს მოცემულია ზემოთ. შემდეგ თვითონ ფანჯარაში ჯგუფში "ტრენდის ხაზის მშენებლობა" შეგიძლიათ გადახვიდეთ სხვა ტიპზე. ამავე დროს, არ უნდა დაგვავიწყდეს, რომ აკონტროლოთ ეს წერტილი "მიიტანეთ მიახლოებითი ნდობის მნიშვნელობა დიაგრამაზე" შემოწმდა ყუთი. ზემოაღნიშნული ნაბიჯების დასრულების შემდეგ დააჭირეთ ღილაკს დახურვა ფანჯრის ქვედა მარჯვენა კუთხეში.
- ხაზოვანი ტიპით, ტენდენციის ხაზს უკვე აქვს ნდობის მნიშვნელობა, რომელიც ტოლია 0.9477, რაც ამ მოდელს ახასიათებს, როგორც უფრო საიმედო, ვიდრე ჩვენს მიერ ადრე განხილული ექსპონენტური ტიპის ტენდენციურ ხაზს.
- ამრიგად, სხვადასხვა ტიპის ტენდენციური ხაზების ერთმანეთთან გადართვა და მათი მიახლოებითი ნდობის მნიშვნელობების შედარება (განსაზღვრის კოეფიციენტი), ჩვენ შეგვიძლია ვიპოვოთ ვარიანტი, რომლის მოდელი ყველაზე ზუსტად აღწერს წარმოდგენილ გრაფიკს. ვარიანტი, რომელსაც განსაზღვრავს ყველაზე მაღალი კოეფიციენტი, ყველაზე საიმედო იქნება. ამის საფუძველზე შეგიძლიათ შექმნათ ყველაზე ზუსტი პროგნოზი.
მაგალითად, ჩვენი საქმისთვის ექსპერიმენტულად შესაძლებელი გახდა იმის დადგენა, რომ მეორე ხარისხის პოლინომიურ ტიპს აქვს უმაღლესი დონის ნდობა. განმსაზღვრელი კოეფიციენტი ამ შემთხვევაში არის 1. ეს მიგვითითებს, რომ ეს მოდელი არის აბსოლუტურად საიმედო, რაც ნიშნავს შეცდომების სრულ გამორიცხვას.
მაგრამ, ამავე დროს, ეს საერთოდ არ ნიშნავს იმას, რომ სხვა სქემისთვის, ამ ტიპის ტრენდული ხაზი ასევე ყველაზე საიმედო იქნება. ტრენდული ხაზის ტიპის ოპტიმალური არჩევანი დამოკიდებულია ფუნქციის ტიპზე, რომლის საფუძველზეც შეიქმნა სქემა. თუ მომხმარებელს არ აქვს საკმარისი ცოდნა, შეაფასოს საუკეთესო ხარისხის ვარიანტი თვალის მიხედვით, მაშინ საუკეთესო პროგნოზის დასადგენად ერთადერთი გზა არის განსაზღვრის კოეფიციენტების შედარება, როგორც ეს მოცემულია ზემოთ მოცემულ მაგალითში.
წაიკითხეთ აგრეთვე:
ტრენდის ხაზის მშენებლობა Excel- ში
მიახლოება Excel- ში
Excel– ში განსაზღვრის კოეფიციენტის გაანგარიშების ორი ძირითადი ვარიანტი არსებობს: ოპერატორის გამოყენებით KVPIRSON და ხელსაწყოს გამოყენება "რეგრესი" ინსტრუმენტის ყუთიდან "მონაცემთა ანალიზი". უფრო მეტიც, ამ ვარიანტების პირველი გამოყენება გამიზნულია მხოლოდ ხაზოვანი ფუნქციის დამუშავებისას, ხოლო მეორე ვარიანტი შეიძლება გამოყენებულ იქნას თითქმის ყველა სიტუაციაში. ამასთან, შესაძლებელია სქემების ტენდენციური ხაზისთვის განსაზღვრის კოეფიციენტის ჩვენება, როგორც მიახლოებითი საიმედოობის ღირებულება. ამ ინდიკატორის გამოყენებით, შესაძლებელია განსაზღვროთ ტენდენციური ხაზის ტიპი, რომელსაც აქვს მაღალი ფუნქციის ნდობა კონკრეტული ფუნქციისთვის.
