KOEFICIENTI I PëRCAKTIMIT Në EXCEL

Send

Një nga treguesit që përshkruan cilësinë e modelit të ndërtuar në statistika është koeficienti i përcaktimit (R ^ 2), i cili quhet edhe 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 ta llogaritni këtë tregues duke përdorur mjete të ndryshme të Excel.

Llogaritja e koeficientit të përcaktimit

Në varësi të nivelit të koeficientit të përcaktimit, është zakon që modelet të ndahen në tre grupe:

0,8 - 1 - model me cilësi të mirë;
0.5 - 0.8 - model me cilësi të pranueshme;
0 - 0.5 - model me cilësi të dobët.

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

Zgjedhja se si të llogarisni vlerën e specifikuar në Excel varet nëse regresioni është linear apo jo. Në rastin e parë, ju mund të përdorni funksionin RSQ, dhe në të dytën 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, ne do të zbulojmë se si të gjejmë 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 atë duke përdorur funksionin e integruar në Excel në shembullin e një tabele specifike, e cila është dhënë më poshtë.

Zgjidhni qelizën ku pas llogaritjes së tij do të shfaqet koeficienti i përcaktimit, dhe klikoni në ikonën "Vendos funksionin".

Fillon lart Wizard Feature. Kalimi në kategorinë e tij "Statistikore" dhe shënoni emrin "RSQ". Klikoni tjetër në butonin "OK".

Fillon dritarja e argumenteve të funksionit. RSQ. Ky operator nga grupi statistikor është krijuar për të llogaritur katrorin e koeficientit të korrelacionit të funksionit Pearson, domethënë një funksion linear. Dhe siç kujtojmë, me një funksion linear, koeficienti i përcaktimit është saktësisht i barabartë me katrorin e koeficientit të korrelacionit.
Sintaksa për këtë deklaratë është:
= KVPIRSON (vlerat e njohura_y_; vlerat e njohura_x)
Kështu, një funksion ka dy operatorë, njëri prej të cilëve është një listë e vlerave të funksionit, dhe i dyti është një argument. Operatorët mund të përfaqësohen drejtpërdrejt si vlerat e numëruara përmes një pikëpresjeje (;), dhe në formën e lidhjeve në rangjet ku janë vendosur. Optionshtë opsioni i fundit që do të përdoret nga ne në këtë shembull.
Vendoseni kursorin në fushë Vlerat e njohura y. Ne mbajmë butonin e 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ë të njëjtën mënyrë, plotësoni fushën Vlerat e njohura x. Vendoseni 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 në butonin "OK"e vendosur në fund të saj.

Siç mund ta shihni, pas kësaj programi llogarit koeficientin e përcaktimit dhe tregon rezultatin në qelizën që ishte zgjedhur edhe para thirrjes Magjistarët e funksionit. Në shembullin tonë, vlera e treguesit të llogaritur doli të jetë 1. Kjo do të thotë që modeli i paraqitur është absolutisht i besueshëm, d.m.th. ai eliminon gabimin.

Mësimi: Wizard Feature në Microsoft Excel

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

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

Por para se të përdorni mjetin e specifikuar, duhet ta aktivizoni vetë Paketa e Analizës, e cila është çaktivizuar si parazgjedhje në Excel. Kaloni në skedë "File"dhe pastaj shkoni tek "Parametrat".

Në dritaren që hapet, zhvendoseni në seksion "Shtesa" duke lundruar menunë e majtë vertikale. Në fund të panelit të djathtë të dritares është një fushë "Menaxhimi". Nga lista e nënseksioneve në dispozicion atje, zgjidhni emrin "Shtesa Excel ..."dhe pastaj kliko në butonin "Shko ..."të vendosura në të djathtë të fushës.

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

Pako mjetesh "Analiza e të dhënave" në shembullin aktual të Excel do të aktivizohet. Qasja në të është e vendosur në shiritin në skedë "Data". Ne lëvizim në skedën e specifikuar dhe klikojmë në butonin "Analiza e të dhënave" në grupin e cilësimeve "Analiza".

Dritarja aktivizohet "Analiza e të dhënave" me një listë të mjeteve të specializuara për përpunimin e informacionit. Zgjidhni artikullin nga kjo listë "Regresionit" dhe klikoni në butonin "OK".

Pastaj hapet dritarja e mjetit "Regresionit". Blloku i parë i cilësimeve është "Input". Këtu në dy fusha ju duhet të specifikoni adresat e rangjeve ku gjenden vlerat e argumentit dhe funksionit. Vendoseni kursorin në fushë "Intervali i hyrjes Y" dhe zgjidhni përmbajtjen e kolonës në fletë "Y". Pasi të shfaqet adresa e grupit në dritare "Regresionit"vendoseni kursorin në fushë "Intervali i hyrjes Y" dhe zgjidhni qelizat e kolonës saktësisht në të njëjtën mënyrë "X".
Rreth parametrave "Label" dhe Zero konstante mos vendos flamuj. Kutia e kontrollit mund të vendoset pranë parametrit. "Niveli i besueshmërisë" dhe në fushën e kundërt, tregoni vlerën e dëshiruar të treguesit përkatës (95% si parazgjedhje).
Në grup Opsionet e rezultatit duhet të specifikoni në cilën zonë do të shfaqet rezultati i llogaritjes. Ekzistojnë tre mundësi:
- Zona në fletën aktuale;
- Një fletë tjetër;
- Një libër tjetër (skedar i ri).
Le të zgjedhim opsionin e parë në mënyrë që të dhënat e burimit dhe rezultati të vendosen në të njëjtën fletë pune. Ne e vendosëm çelësin afër parametrit "Intervali i Prodhimit". Në fushën përballë këtij artikulli, vendoseni kursorin. Klikoni me të majtën në një element bosh në fletë, e cila është krijuar për t'u bërë qeliza e sipërme e majtë e tabelës së rezultatit të llogaritjes. Adresa e këtij elementi duhet të shfaqet në fushën e dritares "Regresionit".
Grupet e parametrave "mbetet" dhe "Probabiliteti normal" Injoroni, sepse ato nuk janë të rëndësishme për zgjidhjen e detyrës. Pas kësaj, klikoni në butonin "OK"të vendosura në këndin e sipërm të djathtë të dritares "Regresionit".

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

Metoda 3: koeficienti i përcaktimit për linjën e trendit

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

Ne kemi një grafik të bazuar në një tabelë të argumenteve dhe vlerave të funksionit që është përdorur për shembullin e mëparshëm. Ne do të ndërtojmë një vijë prirjeje për të. Ne klikojmë në çdo vend të zonës së ndërtimit në të cilën është vendosur grafiku, me butonin e majtë të miut. Në të njëjtën kohë, një grup shtesë i skedave shfaqet në shirit - "Puna me grafikët". Shko te skeda "Layout". Klikoni në butonin Linja e Trenditi cili ndodhet në bllokun e mjeteve "Analiza". Një menu shfaqet me një zgjedhje të llojit të linjës së trendit. Ne ndalojmë zgjedhjen e llojit që korrespondon me një detyrë specifike. Le të zgjedhim një mundësi për shembullin tonë "Përafrim eksponencial".

Excel ndërton një vijë prirjeje në formën e një kthesë shtesë të zezë drejtë në tabelë.

Tani detyra jonë është të shfaqim vetë koeficientin e përcaktimit. Klikoni me të djathtën në vijën e trendit. Menyja e kontekstit është aktivizuar. Ne ndalojmë zgjedhjen në të në "Formati i linjës së trendit ...".
Për të kryer kalimin në dritaren e formatit të linjës së trendit, mund të kryeni një veprim alternative. Zgjidhni linjën e trendit duke klikuar mbi të me butonin e majtë të miut. Kaloni në skedë "Layout". Klikoni në butonin Linja e Trendit në bllok "Analiza". Në listën që hapet, klikoni në artikullin e fundit në listën e veprimeve - "Parametrat shtesë të vijës së trendit ...".

Pas njërës nga dy veprimet e mësipërme, lëshohet një dritare me format në të cilën mund të bëni cilësime shtesë. Në veçanti, për të përfunduar detyrën tonë, është e nevojshme të kontrolloni kutinë pranë "Vendoseni vlerën e besimit të përafrimit (R ^ 2) në diagram". Ndodhet në fundin e dritares. Kjo do të thotë, në këtë mënyrë mundësojmë shfaqjen e koeficientit të përcaktimit në zonën e ndërtimit. Atëherë mos harroni të klikoni në butonin "Close" në fund të dritares aktuale.

Vlera e besueshmërisë së përafrimit, domethënë, vlera e koeficientit të përcaktimit, do të shfaqet në një fletë në zonën e ndërtimit. Në këtë rast, kjo vlerë, siç shohim, është 0.9242, e cila karakterizon përafrimin si një model të një cilësie të mirë.

Absolutisht saktësisht në këtë mënyrë ju mund të vendosni shfaqjen e koeficientit të përcaktimit për çdo lloj tjetër të linjës së trendit. Ju mund të ndryshoni llojin e linjës së trendit duke bërë një kalim përmes butonit në shirit ose menusë së kontekstit në dritaren e parametrave të tij, siç tregohet më sipër. Pastaj në vetë dritaren në grup "Ndërtimi i një linje trendi" Mund të kaloni në një lloj tjetër. Në të njëjtën kohë, mos harroni ta kontrolloni atë rreth pikës "Vendoseni vlerën e besimit të përafrimit në diagram" u kontrollua kutia e kontrollit. Pasi të keni përfunduar hapat e mësipërm, klikoni në butonin "Close" në këndin e poshtëm të djathtë të dritares.

Me llojin linear, linja e trendit tashmë ka një vlerë besimi përafrimi të barabartë me 0.9477, e cila e karakterizon këtë model madje edhe më të besueshëm sesa linja e trendit të tipit eksponencial të konsideruar nga ne më parë.

Kështu, duke kaluar ndërmjet llojeve të ndryshme të linjave të trendit dhe duke krahasuar vlerat e tyre të besimit të përafrimit (koeficienti i përcaktimit), mund të gjesh opsionin, modeli i të cilit përshkruan më saktë grafikun e paraqitur. Opsioni me koeficientin më të lartë të koeficientit të përcaktimit do të jetë më i besueshëm. Bazuar në të, ju mund të ndërtoni parashikimin më të saktë.
Për shembull, për rastin tonë ishte eksperimentale e mundur të vërtetohej se lloji polinom i linjës së trendit të shkallës së dytë ka nivelin më të lartë të besimit. Koeficienti i përcaktimit në këtë rast është 1. Kjo sugjeron që ky model është absolutisht i besueshëm, që do të thotë përjashtim i plotë i gabimeve.
Por, në të njëjtën kohë, kjo nuk do të thotë aspak se për një tabelë tjetër kjo lloj linje prirjeje do të jetë gjithashtu më e besueshme. Zgjedhja optimale e llojit të linjës së trendit varet nga lloji i funksionit në bazë të të cilit është ndërtuar tabela. Nëse përdoruesi nuk ka njohuri të mjaftueshme për të vlerësuar variantin e cilësisë më të mirë me sy, atëherë mënyra e vetme për të përcaktuar parashikimin më të mirë është të krahasoni koeficientët e përcaktimit, siç u tregua në shembullin e mësipërm.

Lexoni gjithashtu:
Ndërtimi i një linje trendi në Excel
Përafrimi në Excel

Ekzistojnë dy mundësi kryesore për llogaritjen e koeficientit të përcaktimit në Excel: përdorimi i operatorit RSQ dhe përdorimi i mjeteve "Regresionit" nga kutia e veglave "Analiza e të dhënave". Për më tepër, e para nga këto mundësi ë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ër më tepër, është e mundur të shfaqet koeficienti i përcaktimit për linjën e trendit të grafikëve si një vlerë e besueshmërisë së përafrimit. Duke përdorur këtë tregues, është e mundur të përcaktohet lloji i linjës së trendit që ka nivelin më të lartë të besimit për një funksion të veçantë.

Send