Llogaritja e koeficientit të përcaktimit në Microsoft Excel

Një nga treguesit që përshkruajnë cilësinë e modelit të ndërtuar në statistika është koeficienti i përcaktimit (R ^ 2), i cili gjithashtu quhet vlera e besimit të përafrimit. Me të, ju mund të përcaktoni nivelin e saktësisë së parashikimit. Le të zbulojmë se si mund të llogarisni këtë tregues duke përdorur mjete të ndryshme në Excel.

Llogaritja e koeficientit të përcaktimit

Në varësi të nivelit të koeficientit të përcaktimit, është e zakonshme që modelet të ndahen në tri grupe:

• 0.8 - 1 - një model i cilësisë së mirë;
• 0.5 - 0.8 - një model i cilësisë së pranueshme;
• 0 - 0,5 - një model me cilësi të dobët.

Në rastin e fundit, cilësia e modelit tregon pamundësinë e përdorimit të saj për parashikimin.

Zgjedhja se si të llogaritet vlera e specifikuar në Excel varet nëse regresioni është linear apo jo. Në rastin e parë, mund ta përdorni funksionin RSQ, dhe në të dytën do të duhet të përdorni një mjet të veçantë nga paketa e analizës.

Metoda 1: llogaritja e koeficientit të përcaktimit me një funksion linear

Para së gjithash, gjeni se si të gjesh koeficientin e përcaktimit për një funksion linear. Në këtë rast, ky tregues do të jetë i barabartë me katrorin e koeficientit të korrelacionit. Ne do ta llogarisim duke përdorur funksionin e ndërtuar në Excel duke përdorur shembullin e një tabele specifike, e cila është treguar më poshtë.

1. Zgjidhni qelizën ku koeficienti i përcaktimit do të shfaqet pas llogaritjes së tij dhe klikoni mbi ikonën "Funksioni i futjes".



2. fillon Funksion magjistar. Lëviz në kategorinë e saj "Statistikore" dhe shënoni emrin "RSQ". Tjetra, kliko mbi butonin "OK".



3. Fillon funksioni i argumenteve të argumenteve. RSQ. Ky operator nga grupi statistikor është projektuar për të llogaritur katrorin e koeficientit të korrelacionit të funksionit Pearson, që është një funksion linear. Dhe, siç kujtojmë, me një funksion linear, koeficienti i përcaktimit është i barabartë me sheshin e koeficientit të korrelacionit.

   Sintaksa për këtë deklaratë është:

   = KVPIRSON (known_y; well-known_x)

   Kështu, një funksion ka dy operatorë, njëra prej të cilave është një listë e vlerave të funksionit dhe e dyta është një argument. Operatorët mund të përfaqësohen direkt si vlera të renditura përmes një pikëpresje (;) dhe në formën e lidhjeve me vargjet ku ndodhen. Ky është alternativa e fundit që do të përdoret nga ne në këtë shembull.

   Vendosni kursorin në fushë "Vlerat e Njohura Y". Ne kryejmë fiksimin e butonit të majtë të miut dhe zgjedhim përmbajtjen e kolonës. "Y" tabela. Siç mund ta shihni, adresa e grupit të të dhënave të specifikuar shfaqet menjëherë në dritare.

   Në mënyrë të ngjashme mbushni fushën "Njohur x". Vendosni kursorin në këtë fushë, por këtë herë zgjidhni vlerat e kolonës "X".

   Pasi të gjitha të dhënat janë shfaqur në dritaren e argumenteve RSQklikoni butonin "OK"që ndodhet në fund të saj.



4. Siç mund ta shikoni, pas kësaj, programi llogarit koeficientin e përcaktimit dhe e kthen rezultatin në qelizën e përzgjedhur përpara thirrjes Zotëruesit e funksioneve. Në shembullin tonë, vlera e treguesit të llogaritur rezulton të jetë 1. Kjo do të thotë se modeli i paraqitur është absolutisht i besueshëm, domethënë përjashton gabimin.

Mësimi: Funksioni Wizard në Microsoft Excel

Metoda 2: Llogaritja e koeficientit të përcaktimit në funksionet jolineare

Por opsioni i mësipërm i llogaritjes së vlerës së dëshiruar mund të zbatohet vetëm për funksionet lineare. Çfarë duhet të bëni për të prodhuar llogaritjen e tij në një funksion jolinear? Në Excel ekziston një mundësi e tillë. Mund të bëhet me një mjet. "Regresionit"e cila është pjesë e paketës "Analiza e të dhënave".

1. Por, para se të përdorni këtë mjet, duhet ta aktivizoni atë vetë. "Paketa e Analizës"i cili me parazgjedhje është i çaktivizuar në Excel. Lëviz në tab "File"dhe pastaj kalojnë nëpër artikullin "Parametrat".



2. Në dritaren e hapur ne lëvizim në seksionin. "Shtesa" duke lundruar nëpër menunë e majtë vertikale. Në fund të panelit të djathtë është një fushë "Menaxhimi". Nga lista e nënseksioneve në dispozicion zgjedhni emrin "Shtesa shtesë në Excel ..."dhe pastaj klikoni mbi butonin "Shko ..."që ndodhet në të djathtë të fushës.



3. Dritarja e shtesave fillon. Në pjesën qendrore është një listë e shtesave shtesë në dispozicion. Kontrollo kutinë pranë pozicionit "Paketa e Analizës". Pas kësaj, klikoni mbi butonin. "OK" në anën e djathtë të dritares së ndërfaqes.



4. Paketa e veglës "Analiza e të dhënave" në instancën aktuale të Excel do të aktivizohet. Qasja në të ndodhet në shiritin në tab "Data". Lëviz në skedën e specifikuar dhe kliko butonin. "Analiza e të dhënave" në grupin e cilësimeve "Analiza".



5. Dritarja e aktivizuar "Analiza e të dhënave" me një listë të mjeteve të specializuara të përpunimit të informacionit. Zgjidh nga ky artikull listës "Regresionit" dhe klikoni mbi butonin "OK".



6. Pastaj hapet dritarja e veglave. "Regresionit". Blloku i parë i cilësimeve - "Input". Këtu në dy fushat ju duhet të specifikoni adresat e vargjeve ku janë vendosur vlerat dhe funksionet e argumentit. Vendos kursorin në fushë "Intervali i hyrjes Y" dhe zgjidhni përmbajtjen e kolonës në fletë "Y". Pasi adresa e grupit shfaqet në dritare "Regresionit"vendosni kursorin në fushë "Intervali i hyrjes Y" dhe në të njëjtën mënyrë zgjidhni qelizat e kolonës "X".

   Rreth parametrave "Label" dhe "E vazhdueshme, zero" kutitë e zgjedhjes nuk janë vendosur. Kutia e zgjedhjes mund të vendoset afër parametrit "Niveli i besueshmërisë" dhe në fushën e kundërt, tregoni vlerën e dëshiruar të treguesit përkatës (për 95% default).

   Në grup "Opsionet e daljes" ju duhet të specifikoni në cilën zonë rezultati i llogaritjes do të shfaqet. Ekzistojnë tri opsione:

   • Zona në fletën aktuale;
   • Një fletë tjetër;
   • Një libër tjetër (skedar i ri).

   Le të ndalojmë zgjedhjen në opsionin e parë që të dhënat fillestare dhe rezultati u vendosën në një fletë pune. Vendoseni kaloni afër parametrit "Ndërrimi i Produktit". Në fushën e kundërt me këtë artikull vendosni kursorin. Ne klikojmë butonin e majtë të miut mbi elementin bosh në fletë, i cili ka për qëllim të bëhet qeliza e sipërme e majtë e tabelës së rezultateve të llogaritjes. Adresa e këtij elementi duhet të shfaqet në dritare "Regresionit".

   Grupet e parametrave "mbetet" dhe "Mundësia normale" injorojnë, pasi ato nuk janë të rëndësishme për zgjidhjen e problemit. Pas kësaj ne klikoni në butonin. "OK"i cili ndodhet në cepin e sipërm të djathtë të dritares "Regresionit".



7. Programi llogarit në bazë të të dhënave të dhëna më parë dhe tregon rezultatin në rangun e specifikuar. Siç mund ta shihni, ky mjet tregon në fletë një numër mjaft të madh të rezultateve në parametra të ndryshëm. Por në kontekstin e mësimit aktual ne jemi të interesuar për treguesin "R-katror". Në këtë rast, ajo është e barabartë me 0.947664, e cila karakterizon modelin e përzgjedhur si një model me cilësi të mirë.

Metoda 3: koeficienti i përcaktimit për vijën e prirjes

Përveç opsioneve të mësipërme, koeficienti i përcaktimit mund të shfaqet drejtpërsëdrejti për linjën e trendit në një grafik të ndërtuar në një fletë Excel. Do të zbulojmë se si mund të bëhet kjo me një shembull konkret.

1. Ne kemi një grafik të bazuar në tabelën e argumenteve dhe vlerave të funksionit që është përdorur për shembullin e mëparshëm. Le të bëjmë një linjë prirjeje për të. Ne klikoni në çdo vend në zonën e ndërtimit në të cilën grafiku është vendosur me butonin e majtë të miut. Në të njëjtën kohë, një grup shtesë tabelash shfaqet në shiritin - "Duke punuar me grafikët". Shko te tab "Layout". Ne klikoni mbi butonin "Linja e Trendit"e cila gjendet në bllokun e mjeteve "Analiza". Shfaqet një menu me një zgjedhje të tipit të linjës së trendit. Ne e ndalojmë zgjedhjen në llojin që korrespondon me një detyrë specifike. Për shembullin tonë, le të zgjedhim "Përafrim eksponencial".



2. Excel po ndërton një linjë prirjeje në formën e një kurbeje të zezë shtesë në planin e skicimit.



3. Tani detyra jonë është që të shfaqim koeficientin e vendosjes vetë. Ne klikojmë me të djathtën në vijën e prirjes. Menuja e kontekstit është aktivizuar. Ndalo zgjedhjen në atë në artikull "Format i linjës së trendit ...".

   

   Për të bërë një kalim në dritaren e formatit të linjës së trendit, mund të kryeni një veprim alternativ. Zgjidhni linjen e trendit duke klikuar mbi të me butonin e majtë të miut. Lëviz në tab "Layout". Ne klikoni mbi butonin "Linja e Trendit" në bllok "Analiza". Në listën që hapet, ne klikojmë në artikullin e fundit në listën e veprimeve - "Opsionet e avancuara të linjës së trendit ...".



4. Pas secilit prej dy veprimeve të mësipërme, hapet një dritare e formatit në të cilin mund të vendosni parametrat shtesë. Në veçanti, për të kryer detyrën tonë, është e nevojshme të kontrolloni kutinë pranë "Vendosni në tabelë vlerën e saktësisë së përafrimit (R ^ 2)". Ajo është e vendosur në pjesën e poshtme të dritares. Kjo është, në këtë mënyrë ne përfshijmë shfaqjen e koeficientit të përcaktimit në zonën e ndërtimit. Pastaj mos harroni të shtypni butonin "Close" në pjesën e poshtme të dritares aktuale.



5. Vlera e besimit të përafrimit, që është, vlera e koeficientit të përcaktimit, do të shfaqet në fletë në zonën e komplotit. Në këtë rast, kjo vlerë, siç e shohim, është 0.9242, e cila karakterizon përafrimin, si një model i cilësisë së mirë.



6. Absolutisht saktësisht kështu që ju mund të vendosni shfaqjen e koeficientit të përcaktimit për çdo lloj tjetër të vijës së prirjes. Ju mund të ndryshoni llojin e linjës së trendit duke bërë një kalim përmes butonit në shirit ose menunë kontekstuale në dritaren e parametrave të saj, siç tregohet më sipër. Pastaj tashmë në dritaren e grupit "Ndërtimi i linjës së trendit" mund të kalojë në një lloj tjetër. Mos harroni të kontrollit në mënyrë që afër pikë "Vendosni në tabelë vlerën e saktësisë së përafrimit" u kontrollua. Pas përfundimit të hapave të mësipërm, klikoni mbi butonin. "Close" në këndin e poshtëm të djathtë të dritares.



7. Në rastin e një tipi linear, linja e trendit tashmë ka një vlerë të besimit të përafrimit prej 0.9477, e cila karakterizon këtë model si edhe më të besueshëm se linja e prirjes eksponenciale e tipit që kemi parë më parë.



8. Kështu, kalimi midis llojeve të ndryshme të linjave të trendit dhe krahasimit të vlerave të tyre të besimit të përafrimit (koeficienti i përcaktimit), ju mund të gjeni variantin, modeli i së cilës më saktë përshkruan grafikun e paraqitur. Varianti me indeksin më të lartë të përcaktimit do të jetë më i besueshmi. Në bazë të saj, ju mund të ndërtoni parashikimin më të saktë.

   Për shembull, për rastin tonë, me anë të eksperimentit, arritëm të përcaktojmë se niveli më i lartë i besimit është i tipit polinomi të vijës së prirjes të shkallës së dytë. Koeficienti i përcaktimit në këtë rast është i barabartë me 1. Kjo sugjeron që ky model është absolutisht i besueshëm, që do të thotë eliminimi i plotë i gabimeve.

   

   Por në të njëjtën kohë, kjo nuk do të thotë se ky lloj trendi do të jetë edhe më i besueshëm për një tabelë tjetër. Zgjedhja optimale e llojit të vijës së prirjes varet nga lloji i funksionit në bazë të të cilit është ndërtuar grafiku. Nëse përdoruesi nuk ka njohuri të mjaftueshme për të vlerësuar mundësinë më të lartë të cilësisë, atëherë e vetmja mënyrë për të përcaktuar parashikimin më të mirë është vetëm një krahasim i koeficientëve të përcaktimit, siç është treguar në shembullin e mësipërm.

Shih gjithashtu:
Linjat e trendit të ndërtimit në Excel
Përafrimi i Excel

Në Excel ekzistojnë dy mundësi kryesore për llogaritjen e koeficientit të përcaktimit: duke përdorur operatorin RSQ dhe mjet aplikimi "Regresionit" nga paketa e mjeteve "Analiza e të dhënave". Në këtë rast, i pari nga këto opsione është menduar për përdorim vetëm në përpunimin e një funksioni linear dhe opsioni tjetër mund të përdoret në pothuajse të gjitha situatat. Përveç kësaj, është e mundur të shfaqet koeficienti i përcaktimit për linjën e trendit të grafikëve si një vlerë e besimit të përafrimit. Duke përdorur këtë tregues, është e mundur të përcaktohet lloji i vijës së prirjes që ka nivelin më të lartë të besueshmërisë për një funksion të caktuar.