Izračunavanje koeficijenta određivanja u Microsoft Excel-u

Jedan od pokazatelja koji opisuje kvalitetu izgrađenog modela u statistici je koeficijent određivanja (R ^ 2), koji se također naziva i vrijednost pouzdanosti aproksimacije. Pomoću nje možete odrediti nivo tačnosti prognoze. Doznajmo kako možete izračunati ovaj pokazatelj pomoću različitih Excelovih alata.

Proračun koeficijenta određivanja

Ovisno o razini koeficijenta određivanja, modeli su uobičajeni podijeliti u tri skupine:

• 0,8 - 1 - model dobre kvalitete;
• 0,5 - 0,8 - model prihvatljivog kvaliteta;
• 0 - 0,5 - nekvalitetni model.

U posljednjem slučaju, kvaliteta modela ukazuje na nemogućnost njegove uporabe za predviđanje.

Izbor načina izračunavanja navedene vrijednosti u Excelu ovisi o tome je li regresija linearna ili ne. U prvom slučaju, možete koristiti funkciju KVPIRSON, a u drugom morate koristiti poseban alat iz paketa analize.

Metoda 1: izračunavanje koeficijenta određivanja linearnom funkcijom

Prije svega, otkrit ćemo kako pronaći koeficijent određivanja linearne funkcije. U ovom će slučaju ovaj indikator biti jednak kvadratu koeficijenta korelacije. Izračunat ćemo ga pomoću ugrađene Excel funkcije na primjeru određene tablice, koja je dana u nastavku.

1. Odaberite ćeliju u kojoj će se nakon izračunavanja prikazati koeficijent određivanja i kliknite na ikonu "Umetanje funkcije".



2. Počinje Čarobnjak za funkcije. Prelaskom na njegovu kategoriju "Statistički" i označite ime KVPIRSON. Sljedeći klik na gumb "OK".



3. Pokreće se prozor argumenata funkcije. KVPIRSON. Ovaj operator iz statističke grupe dizajniran je za izračun kvadrata koeficijenta korelacije Pearsonove funkcije, odnosno linearne funkcije. A kao što se sjećamo, sa linearnom funkcijom koeficijent određivanja točno je jednak kvadratu koeficijenta korelacije.

   Sintaksa ove izjave je:

   = KVPIRSON (poznate_y vrijednosti_; poznate_x vrijednosti)

   Dakle, funkcija ima dva operatora, od kojih je jedan popis vrijednosti funkcija, a drugi je argument. Operatori se mogu predstaviti izravno kao vrijednosti nabrojene tačkom zarezom (;), i u obliku veza na raspone u kojima se nalaze. Upravo je ova posljednja opcija koja će se koristiti u ovom primjeru.

   Postavite kursor u polje Poznate y vrijednosti. Držimo lijevu tipku miša i odabiremo sadržaj stupca "Y" stolovi. Kao što vidite, adresa određenog niza podataka odmah se prikazuje u prozoru.

   Na isti način ispunite polje Poznate x vrijednosti. U ovo polje stavite kursor, ali ovaj put odaberite vrijednosti stupca "X".

   Nakon što su svi podaci prikazani u prozoru s argumentima KVPIRSONkliknite na gumb "OK"nalazi se na njegovom dnu.



4. Kao što vidite, nakon toga program izračunava koeficijent određivanja i prikazuje rezultat u ćeliji koja je odabrana i prije poziva Čarobnjaci za funkcije. U našem primjeru, vrijednost izračunatog pokazatelja pokazala se 1. To znači da je predstavljeni model apsolutno pouzdan, odnosno da uklanja grešku.

Lekcija: Čarobnjak za funkcije u Microsoft Excel-u

Metoda 2: izračunavanje koeficijenta određivanja u nelinearnim funkcijama

Ali gornja opcija za izračunavanje željene vrijednosti može se primijeniti samo na linearne funkcije. Što učiniti da biste ga izračunali u nelinearnoj funkciji? U Excelu postoji takva prilika. To se može učiniti pomoću alata. "Regresija"koja je deo paketa "Analiza podataka".

1. Ali prije upotrebe navedenog alata morate ga sami aktivirati Paket za analizu, koja je u programu Excel podrazumevano onemogućena. Pomicanje na karticu Datotekaa onda idite na "Opcije".



2. U prozoru koji se otvori pređite na odjeljak "Dodaci" navigacijom lijevim vertikalnim menijem. Na dnu desnog okna prozora nalazi se polje "Uprava". S popisa tamošnjih pododjeljaka odaberite ime "Excel dodaci ..."a zatim kliknite na dugme "Idi ..."smještene s desne strane polja.



3. Pokrenut je prozor dodataka. U njegovom središnjem dijelu nalazi se popis dostupnih dodataka. Postavite potvrdni okvir pored položaja Paket za analizu. Nakon toga kliknite na dugme "OK" na desnoj strani sučelja prozora.



4. Paket alata "Analiza podataka" u trenutnoj instanci Excel-a će se aktivirati. Pristup njemu nalazi se na vrpci na kartici "Podaci". Prelazimo na navedenu karticu i kliknemo na gumb "Analiza podataka" u grupi postavki "Analiza".



5. Prozor je aktiviran "Analiza podataka" sa popisom specijalizovanih alata za obradu informacija. Odaberite stavku s ove liste "Regresija" i kliknite na dugme "OK".



6. Tada se otvara prozor alata "Regresija". Prvi blok postavki je "Unos". Ovdje u dva polja trebate odrediti adrese raspona u kojima se nalaze vrijednosti argumenta i funkcije. Stavite kursor u polje "Ulazni interval Y" i odaberite sadržaj stupca na listu "Y". Nakon što se adresa prozora prikazuje u prozoru "Regresija"stavite kursor u polje "Ulazni interval Y" i odaberite ćelije stupaca na potpuno isti način "X".

   O parametrima "Oznaka" i Constant Zero ne stavljajte zastave. Potvrdni okvir može se postaviti pored parametra. "Nivo pouzdanosti" a u polju nasuprot navedite željenu vrijednost odgovarajućeg pokazatelja (95% zadano).

   U grupi Izlazne mogućnosti morate navesti u kojem će se području prikazati rezultat izračuna. Postoje tri opcije:

   • Područje na trenutnom listu;
   • Još jedan list;
   • Još jedna knjiga (nova datoteka).

   Izaberite prvu opciju tako da se izvorni podaci i rezultat postave na isti radni list. Prekidač postavljamo blizu parametra "Izlazni interval". U polje nasuprot ovoj stavci stavite kursor. Kliknite lijevim klikom na prazan element na listu, koji je dizajniran da postane gornja lijeva ćelija tablice za izračun. Adresa ovog elementa treba biti prikazana u polju prozora "Regresija".

   Grupe parametara "Ostaci" i "Normalna vjerovatnost" Zanemarite, jer nisu bitni za rješavanje zadatka. Nakon toga kliknite na gumb "OK"koji se nalazi u gornjem desnom uglu prozora "Regresija".



7. Program izračunava na temelju prethodno unesenih podataka i prikazuje rezultat u navedenom rasponu. Kao što vidite, ovaj alat prikazuje prilično velik broj rezultata o različitim parametrima na listu. Ali u kontekstu tekuće lekcije zanima nas pokazatelj R-kvadrat. U ovom je slučaju jednaka 0,947664, što karakteriše odabrani model kao model dobre kvalitete.

Metoda 3: koeficijent određivanja za liniju trenda

Pored gore navedenih opcija, koeficijent određivanja može se prikazati izravno za liniju trenda u grafikonu koji je izrađen na radnom listu u Excelu. Saznat ćemo kako se to može učiniti konkretnim primjerom.

1. Imamo graf na temelju tablice argumenata i vrijednosti funkcija koja je korištena za prethodni primjer. Izgradićemo liniju trenda do toga. Kliknemo na bilo koje mjesto građevinskog područja na kojem je postavljen grafikon, lijevim gumbom miša. Istovremeno se na vrpci pojavljuje dodatni skup kartica - "Rad sa grafikonima". Idite na karticu "Izgled". Kliknite na dugme Trend Linekoja se nalazi u bloku alata "Analiza". Pojavljuje se izbornik s izborom vrste linije trenda. Zaustavljamo se na izboru tipa koji odgovara određenom zadatku. Izaberite opciju za naš primjer "Eksponencijalna aproksimacija".



2. Excel gradi liniju trenda u obliku dodatne crne krivulje desno na grafikonu.



3. Sada je naš zadatak prikazati sam koeficijent određivanja. Desnom tipkom miša kliknite liniju trenda. Aktiviran je kontekstni meni. Izbor u njemu zaustavljamo "Format linije trenda ...".

   

   Da biste izvršili prijelaz na prozor formata trendi linije, možete izvesti alternativnu radnju. Odaberite liniju trenda klikom na nju lijevim gumbom miša. Pomicanje na karticu "Izgled". Kliknite na dugme Trend Line u bloku "Analiza". Na popisu koji se otvori kliknite na posljednju stavku na popisu radnji - "Dodatni parametri linije trenda ...".



4. Nakon bilo koje od gore navedene dvije radnje pokreće se prozor formata u kojem možete izvršiti dodatne postavke. Konkretno, kako biste dovršili naš zadatak, potrebno je potvrditi potvrdni okvir pored "Stavite približnu vrijednost pouzdanosti (R ^ 2) na dijagram". Nalazi se na samom dnu prozora. To jest, na ovaj način omogućavamo prikaz koeficijenta determinacije na građevinskoj površini. Tada ne zaboravite da kliknete na dugme Zatvori pri dnu trenutnog prozora.



5. Vrijednost pouzdanosti aproksimacije, tj. Vrijednost koeficijenta određivanja, bit će prikazana na listu u građevinskom području. U ovom slučaju ova vrijednost, kao što vidimo, iznosi 0,9242, što karakterizira aproksimaciju kao model dobre kvalitete.



6. Apsolutno tačno na ovaj način možete postaviti prikaz koeficijenta odlučnosti za bilo koju drugu vrstu linije trenda. Možete promijeniti vrstu linije trenda prelaskom preko gumba na vrpci ili kontekstnog izbornika u prozoru njegovih parametara, kao što je prikazano gore. Zatim u samom prozoru u grupi „Izgradnja linije trenda“ Možete prebaciti na drugu vrstu. Istovremeno, ne zaboravite da to kontrolirate oko točke "Stavite vrijednost dijagnostičke vrijednosti aproksimacije" potvrdni okvir. Nakon dovršetka gornjih koraka kliknite na gumb Zatvori u donjem desnom uglu prozora.



7. S linearnim tipom, linija trenda već ima vrijednost pouzdanosti približne vrijednosti jednake 0,9477, što ovaj model karakterizira kao čak pouzdaniji od linije trenda eksponencijalnog tipa koji smo razmatrali ranije.



8. Dakle, prebacivanjem između različitih vrsta linija trendova i usporedbom njihovih vrijednosti aproksimacijske pouzdanosti (koeficijent određivanja), možemo pronaći opciju čiji model najpreciznije opisuje predstavljeni graf. Opcija sa najvišim koeficijentom koeficijenta određivanja bit će najpouzdanija. Na osnovu toga možete izgraditi najtačniju prognozu.

   Na primjer, za naš slučaj je eksperimentalno bilo moguće utvrditi da polinomna vrsta trendove drugog stupnja ima najviši stupanj pouzdanosti. Koeficijent određivanja u ovom slučaju je 1. To sugerira da je ovaj model apsolutno pouzdan, što znači potpuno isključivanje grešaka.

   

   Ali, to istovremeno ne znači da će za drugi grafikon ova vrsta trend-linije biti i najpouzdanija. Optimalan izbor vrste linije trenda ovisi o vrsti funkcije na temelju koje je građen grafikon. Ako korisnik nema dovoljno znanja da bi mogao procijeniti najbolju varijantu kvalitete, tada je jedini način da se utvrdi najbolja prognoza usporedba koeficijenata određivanja, kao što je prikazano u gornjem primjeru.

Pročitajte i:
Izgradnja linije trenda u Excelu
Približavanje u Excelu

Postoje dvije glavne opcije za izračun koeficijenta određivanja u Excelu: upotreba operatera KVPIRSON i korištenje alata "Regresija" iz okvira s alatima "Analiza podataka". Štoviše, prva od ovih opcija namijenjena je upotrebi samo u obradi linearne funkcije, a druga opcija može se koristiti u gotovo svim situacijama. Pored toga, moguće je prikazati koeficijent određivanja za liniju trenda grafikona kao vrijednost pouzdanosti aproksimacije. Pomoću ovog pokazatelja moguće je odrediti vrstu linije trenda koja ima najviši stupanj pouzdanosti za određenu funkciju.