Jedan od indikatora koji opisuju kvalitet konstruisanog modela u statistici je koeficijent determinacije (R ^ 2), koji se naziva i vrijednost pouzdanosti aproksimacije. Pomoću njega možete odrediti nivo tačnosti prognoze. Hajde da otkrijemo kako možete izračunati ovaj indikator koristeći razne Excelove alate.
Izračunavanje koeficijenta određivanja
U zavisnosti od nivoa koeficijenta determinacije, uobičajeno je da se modeli dele na tri grupe:
- 0,8 - 1 - model dobre kvalitete;
- 0,5 - 0,8 - model prihvatljivog kvaliteta;
- 0 - 0,5 - model loše kvalitete.
U drugom slučaju, kvalitet modela ukazuje na nemogućnost njegove upotrebe za prognozu.
Izbor načina izračunavanja specificirane vrijednosti u Excel-u zavisi od toga da li je regresija linearna ili ne. U prvom slučaju možete koristiti funkciju KVPIRSON, au drugom ćete morati koristiti poseban alat iz paketa za analizu.
Metoda 1: izračunavanje koeficijenta određivanja sa linearnom funkcijom
Pre svega, saznajte kako pronaći koeficijent određivanja za linearnu funkciju. U ovom slučaju, ovaj indikator će biti jednak kvadratu koeficijenta korelacije. Izračunat ćemo ga koristeći ugrađenu Excel funkciju na primjeru specifične tabele, koja je prikazana ispod.
- Izaberite ćeliju u kojoj će se nakon izračunavanja prikazati koeficijent determinacije i kliknite na ikonu "Umetni funkciju".
- Počinje Čarobnjak za funkcije. Pređite na kategoriju "Statistical" i označite ime KVPIRSON. Zatim kliknite na dugme "OK".
- Počinje prozor argumenata funkcije. KVPIRSON. Ovaj operator iz statističke grupe je dizajniran za izračun kvadrata koeficijenta korelacije Pearsonove funkcije, odnosno linearne funkcije. I kao što se sećamo, sa linearnom funkcijom, koeficijent determinacije je jednak kvadratu koeficijenta korelacije.
Sintaksa za ovaj izraz je:
= KVPIRSON (known_y; well-known_x)Dakle, funkcija ima dva operatora, od kojih je jedan popis vrijednosti funkcije, a drugi je argument. Operateri se mogu predstaviti kao vrijednosti izravno navedene u točki-zarezu (;), i u obliku linkova na opsege u kojima se nalaze. To je zadnja opcija koju ćemo koristiti u ovom primjeru.
Postavite kursor u polje "Poznate Y vrijednosti". Izvodimo stezanje lijeve tipke miša i odabiremo sadržaj kolone. "Y" stolovi. Kao što možete vidjeti, adresa navedenog polja podataka se odmah prikazuje u prozoru.
Slično popunite polje "Poznati x". Stavite kursor u ovo polje, ali ovaj put odaberite vrijednosti stupca "X".
Nakon što su svi podaci prikazani u prozoru argumenata KVPIRSONkliknite na dugme "OK"nalazi se na samom dnu.
- Kao što možete vidjeti, nakon toga program izračunava koeficijent određivanja i vraća rezultat u ćeliju koja je izabrana prije poziva Funkcioneri. U našem primeru, pokazalo se da je vrednost izračunatog indikatora 1. To znači da je prikazani model apsolutno pouzdan, to jest, eliminiše grešku.
Lekcija: Čarobnjak za funkcije u programu Microsoft Excel
Metod 2: Proračun koeficijenta određivanja u nelinearnim funkcijama
Ali gore navedena opcija izračunavanja željene vrijednosti može se primijeniti samo na linearne funkcije. Šta uraditi da bi se izračunao u nelinearnoj funkciji? U programu Excel postoji takva prilika. To se može uraditi pomoću alata. "Regresija"koji je dio paketa "Analiza podataka".
- Ali prije korištenja ovog alata, trebali biste ga sami aktivirati. "Analizni paket"koje je podrazumevano onemogućeno u Excelu. Pređite na karticu "Datoteka"i onda prođite kroz stavku "Opcije".
- U otvorenom prozoru prelazimo na odeljak. Dodaci navigacijom kroz levi vertikalni meni. Na dnu desnog okna nalazi se polje "Upravljanje". Iz popisa dostupnih podsekcija odaberite ime "Excel dodaci ..."i zatim kliknite na dugme "Idi ..."nalazi se desno od polja.
- Počinje prozor dodataka. U centralnom delu je lista dostupnih dodataka. Označite okvir pored pozicije "Analizni paket". Nakon toga kliknite na dugme. "OK" na desnoj strani prozora sučelja.
- Paket alata "Analiza podataka" u trenutnoj instanci programa Excel će biti aktivirana. Pristup je lociran na vrpci na kartici "Data". Idite na određenu karticu i kliknite na dugme. "Analiza podataka" u grupi podešavanja "Analiza".
- Aktivirani prozor "Analiza podataka" sa listom specijalizovanih alata za obradu informacija. Izaberite iz ove stavke liste "Regresija" i kliknite na dugme "OK".
- Zatim se otvara prozor alata. "Regresija". Prvi blok postavki - "Input". Ovdje u dva polja trebate specificirati adrese raspona u kojima se nalaze vrijednosti i funkcije argumenta. Stavite kursor u polje "Ulazni interval Y" i izaberite sadržaj kolone na listu "Y". Nakon što je adresa polja prikazana u prozoru "Regresija"stavite kursor u polje "Ulazni interval Y" i na isti način birajte ćelije kolone "X".
O parametrima "Tag" i "Constant-zero" polja za potvrdu nisu postavljena. Potvrdni okvir se može postaviti u blizini parametra "Nivo pouzdanosti" u suprotnom polju, označite željenu vrijednost odgovarajućeg indikatora (po defaultu 95%).
U grupi "Opcije izlaza" morate navesti u kojoj oblasti će biti prikazan rezultat izračuna. Postoje tri opcije:
- Područje na tekućem listu;
- Another sheet;
- Još jedna knjiga (novi fajl).
Zaustavimo izbor na prvoj opciji da su početni podaci i rezultati stavljeni na jedan radni list. Stavite prekidač blizu parametra "Izlazni razmak". U polje nasuprot ovoj stavci stavite kursor. Klikom na levi taster miša na prazan element na listu, koji je namenjen da postane leva gornja ćelija tabele rezultata proračuna. Adresa ovog elementa treba biti prikazana u prozoru "Regresija".
Grupe parametara "Remains" i "Normalna vjerovatnoća" ignorisati, jer nisu važni za rješavanje problema. Nakon toga kliknemo na gumb. "OK"koji se nalazi u gornjem desnom uglu prozora "Regresija".
- Program izračunava na osnovu prethodno unesenih podataka i prikazuje rezultat u navedenom opsegu. Kao što možete vidjeti, ovaj alat prikazuje na listu prilično velik broj rezultata na različitim parametrima. Ali u kontekstu trenutne lekcije zainteresovani smo za indikator "R-kvadrat". U ovom slučaju, on je jednak 0.947664, što karakteriše odabrani model kao dobar kvalitet.
Metod 3: koeficijent determinacije linije trenda
Pored gore navedenih opcija, koeficijent determinacije se može prikazati direktno za liniju trenda u grafikonu koji je izgrađen na Excel listu. Saznaćemo kako se to može uraditi sa konkretnim primerom.
- Imamo graf na osnovu tabele argumenata i vrijednosti funkcije koja je korištena za prethodni primjer. Hajde da napravimo liniju trenda. Klikom na bilo koje mjesto u građevinskom području na kojem se grafikom nalazi lijevi gumb miša. Istovremeno se na vrpci pojavljuje dodatni skup kartica - "Rad sa grafikonima". Idite na karticu "Layout". Kliknite na dugme "Trend line"koji se nalazi u bloku alata "Analiza". Pojavljuje se izbornik s izborom tipa linije trenda. Prekidamo izbor na tipu koji odgovara određenom zadatku. Za naš primer, hajde da izaberemo "Eksponencijalna aproksimacija".
- Excel gradi liniju trenda u obliku dodatne crne krivulje na samoj karti.
- Sada je naš zadatak da prikažemo sam koeficijent determinacije. Desnim klikom na liniju trenda. Aktiviran je kontekstni meni. Zaustavite izbor u njemu "Oblik trend linije ...".
Da biste izvršili prelazak u prozor formata trend linije, možete izvesti alternativnu akciju. Odaberite liniju trenda klikom na nju lijevim gumbom miša. Pređite na karticu "Layout". Kliknite na dugme "Trend line" u bloku "Analiza". Na listi koja se otvori, kliknemo na poslednju stavku na listi akcija - "Napredne opcije linije trenda ...".
- Nakon bilo koje od gore navedenih radnji pokreće se prozor formata u kojem možete napraviti dodatna podešavanja. Konkretno, za obavljanje našeg zadatka, potrebno je označiti polje pored "Stavite na grafikon vrednost tačnosti aproksimacije (R ^ 2)". Nalazi se na samom dnu prozora. Na taj način uključujemo prikaz koeficijenta određivanja na građevinskom području. Onda ne zaboravite da pritisnete dugme "Zatvori" na dnu trenutnog prozora.
- Vrednost pouzdanosti aproksimacije, odnosno vrednost koeficijenta determinacije, biće prikazana na listu u oblasti parcele. U ovom slučaju, ova vrijednost, kao što vidimo, jednaka je 0.9242, što karakterizira aproksimaciju, kao model dobre kvalitete.
- Apsolutno tačno tako možete postaviti prikaz koeficijenta određivanja za bilo koji drugi tip linije trenda. Tip linije trenda možete promijeniti tako da napravite prijelaz kroz gumb na vrpci ili kontekstni izbornik u prozoru parametara, kao što je prikazano gore. Onda već u prozoru u grupi "Izgradnja linije trenda" može se prebaciti na drugi tip. Nemojte zaboraviti da kontrolirate tako blizu tačke "Stavite na grafikon vrednost tačnosti aproksimacije" je provjeren. Nakon što završite gore navedene korake, kliknite na dugme. "Zatvori" u donjem desnom uglu prozora.
- U slučaju linearnog tipa, linija trenda već ima aproksimativnu vrijednost pouzdanosti od 0.9477, što ovaj model karakteriše još pouzdanije od eksponencijalnog tipa trend linije koju smo ranije razmatrali.
- Dakle, prebacivanjem između različitih tipova trend linija i upoređivanjem njihovih vrijednosti aproksimacije pouzdanosti (koeficijent determinacije), može se naći varijanta, čiji model najtačnije opisuje prikazani graf. Varijanta sa najvišim indeksom određivanja će biti najpouzdanija. Na osnovu toga možete napraviti najprecizniju prognozu.
Na primer, za naš slučaj, eksperimentom, uspeli smo da utvrdimo da je najviši nivo pouzdanosti polinomskog tipa linije trenda drugog stepena. Koeficijent determinacije u ovom slučaju je jednak 1. To sugerira da je ovaj model apsolutno pouzdan, što znači potpunu eliminaciju grešaka.
Ali u isto vreme, to uopšte ne znači da će ova vrsta linije trenda biti i najpouzdanija za još jedan grafikon. Optimalan izbor tipa linije trenda zavisi od tipa funkcije na osnovu koje je grafikon izgrađen. Ako korisnik nema dovoljno znanja za procjenu najkvalitetnije opcije, onda je jedini način da se odredi najbolja predviđanja samo usporedba koeficijenata određivanja, kao što je prikazano u gornjem primjeru.
Vidi i: t
Izgradnja trend linija u Excelu
Excel Approximation
U programu Excel postoje dvije glavne opcije za izračunavanje koeficijenta određivanja: pomoću operatora KVPIRSON i aplikacijski alat "Regresija" iz paketa alata "Analiza podataka". U ovom slučaju, prva od ovih opcija je namijenjena samo za obradu linearne funkcije, a druga opcija se može koristiti u gotovo svim situacijama. Osim toga, moguće je prikazati koeficijent određivanja za trend liniju grafova kao aproksimacijsku vrijednost pouzdanosti. Pomoću ovog indikatora moguće je odrediti vrstu linije trenda koja ima najviši nivo pouzdanosti za određenu funkciju.
