Izračun koeficijenta određivanja u Microsoft Excelu

Jedan od pokazatelja koji opisuje kvalitetu konstruiranog modela u statistici je koeficijent određivanja (R ^ 2), koji se naziva i vrijednost pouzdanosti aproksimacije. Pomoću nje možete odrediti razinu točnosti predviđanja. Doznajemo kako izračunati ovaj pokazatelj pomoću različitih alata programa Excel.

Izračunavanje koeficijenta određivanja

Ovisno o razini koeficijenta određivanja, zajednička je podijeliti modele u tri skupine:

• 0,8 - 1 - model dobre kvalitete;
• 0,5 - 0,8 - model prihvatljive kvalitete;
• 0 - 0,5 - model loše kvalitete.

U potonjem slučaju, kvaliteta modela pokazuje da se ne može koristiti za predviđanje.

Izbor kako izračunati navedenu vrijednost u programu Excel ovisi o tome je li regresija linearna ili ne. U prvom slučaju, možete koristiti KVPIRSON funkciju, au drugom ćete morati koristiti poseban alat iz paketa za analizu.

Metoda 1: izračun koeficijenta određivanja za linearnu funkciju

Prije svega, doznajemo kako pronaći koeficijent određivanja za linearnu funkciju. U ovom slučaju, taj će pokazatelj biti jednak kvadratu koeficijenta korelacije. Izračunat ćemo ga pomoću ugrađene funkcije programa Excel pomoću primjera određene tablice koja je prikazana u nastavku.

1. Odaberite ćeliju u kojoj će se koeficijent određivanja izlaziti nakon izračuna i kliknite na ikonu "Umetni funkciju" .
2. Pokreće se Čarobnjak za funkcije . Prelazimo u svoju kategoriju "Statistička" i označimo naziv "KVPIRSON" . Zatim kliknite gumb "OK" .
3. Pokrenut je prozor argumenata KVPIRSON funkcije. Ovaj operator iz statističke skupine koristi se za izračunavanje kvadrata korelacijskog koeficijenta Pearsonove funkcije, to jest linearne funkcije. I kao što se sjećamo, s linearnom funkcijom, koeficijent određivanja je točno jednak kvadratu koeficijenta korelacije.

   Sintaksa ove izjave je:

   =КВПИРСОН(известные_значения_y;известные_значения_x)

   Dakle, funkcija ima dva operatora, od kojih je jedan popis vrijednosti funkcije, a drugi od argumenata. Operatori se mogu prikazati kao izravno u obliku vrijednosti navedenih putem točke-zarez ( ; ) i kao reference na raspone gdje se nalaze. To je posljednja opcija koju ćemo koristiti u ovom primjeru.

   Postavite pokazivač u polje "Poznat y" . Mi stisnuti lijevu tipku miša i odabrati sadržaj stupca "Y" tablice. Kao što možete vidjeti, adresa navedenog polja podataka odmah se prikazuje u prozoru.

   Slično tome, popunjavamo polje "Poznate vrijednosti x" . Stavili smo kursor na ovo polje, ali ovaj put odabiremo vrijednosti stupca "X" .

   Nakon što su svi podaci prikazani u prozoru argumenta KVPIRSON , kliknite gumb "OK" koji se nalazi na dnu.

4. Kao što vidite, program zatim izračunava koeficijent određivanja i izlaže rezultat na ćeliju koja je bila dodijeljena prije nego što je pozvan čarobnjak za funkciju . U našem primjeru vrijednost izračunatog pokazatelja rezultirala je 1. To znači da je predstavljeni model apsolutno pouzdan, tj. Uklanja pogrešku.

Pouka: Čarobnjak za funkcije u programu Microsoft Excel

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

Ali gornja varijanta izračuna željene vrijednosti može se primijeniti samo na linearne funkcije. Što možemo učiniti kako bismo ga izračunali nelinearnom funkcijom? U programu Excel također postoji takva prilika. To se može učiniti uz pomoć alata "Regresija" , koji je sastavni dio paketa "Analiza podataka" .

1. Ali prije korištenja određenog alata, trebali biste aktivirati sam "paket analize" , koji je prema zadanim postavkama onemogućen u programu Excel. Prebacimo se na karticu "Datoteka" , a zatim idemo na "Opcije" .
2. U prozoru koji se otvori prebacujemo na odjeljak "Dodaci" tako da krenete putem lijevog okomitog izbornika. Polje "Upravljanje" nalazi se u donjem dijelu desnog dijela prozora. Na popisu dostupnih pododjeljaka odaberite naziv "Excel Add-ins ..." , a zatim kliknite gumb "Idi ..." desno od polja.
3. Pokrenut je prozor dodatka. U središnjem dijelu popisa dostupnih dodataka. Označite okvir pored stavke "Analitički paket" . Nakon toga kliknite gumb "OK" na desnoj strani sučelja prozora.
4. Aktivirati će se paket alata "Analiza podataka" u trenutačnoj instanci programa Excel. Pristup joj se nalazi na vrpci na kartici "Podaci" . Prijeđite na određenu karticu i kliknite gumb "Analiza podataka" u grupi postavki "Analiza" .
5. Prozor "Analiza podataka" aktivira se s popisom alata za obradu podataka profila. Na ovom popisu odabiremo stavku "Regresija" i kliknite na gumb "OK" .
6. Zatim se otvara prozor Alata za regresiju . Prvi blok postavki je "Podaci za unos" . Ovdje, u dva polja, morate odrediti adrese raspona gdje se nalaze vrijednosti argumenta i funkcije. Stavili smo pokazivač u polje "Interval za unos Y" i odaberite sadržaj stupca "Y" na listi. Nakon što se adresa polja prikazuje u prozoru "Regresija" , postavite pokazivač u polje "Ulazni interval Y" i na isti način odaberite ćelije stupca "X" .

   O parametrima "Label" i "Constant-zero" ne postavljamo potvrdne okvire. Potvrdni okvir može se postaviti blizu parametra "Razina pouzdanosti" i na polju nasuprot možete odrediti željenu vrijednost odgovarajućeg pokazatelja (prema zadanim postavkama 95%).

   U skupini "Izlazni parametri" morate odrediti na kojem će se području prikazati rezultat proračuna. Postoje tri opcije:

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

   Zaustavit ćemo se na prvoj opciji, tako da izvorni podaci i rezultat budu postavljeni na jedan radni list. Postavite prekidač na parametar "Izlazni interval" . Na polju nasuprot ove stavke postavljamo pokazivač. Kliknemo lijevu tipku miša na prazan element na listu, koji je namijenjen da postane lijeva gornja stanica izlazne tablice rezultata izračuna. Adresa ovog elementa treba biti istaknuta u okviru "Regresija" .

   Parametarske skupine "Remains" i "Normal Probability" se zanemaruju jer nisu važne za rješavanje zadatka. Nakon toga kliknite gumb "U redu" , koji se nalazi u gornjem desnom kutu prozora "Regresija" .

7. Program izračunava na temelju prethodno unesenih podataka i izlaže rezultat u navedeni raspon. Kao što vidite, ovaj alat prikazuje veliki broj rezultata na različitim parametrima na listu. No, u kontekstu trenutne lekcije zainteresirani smo za pokazatelj "R-kvadrat" . U ovom je slučaju 0,947664, što karakterizira odabrani model kao model dobre kvalitete.

Metoda 3: Određivanje koeficijenta za trend line

Uz navedene opcije, koeficijent određivanja može se prikazati izravno za liniju trendova na grafikonu, izgrađenoj na Excel listi. Let's saznati kako se to može učiniti s konkretnim primjerom.

1. Imamo grafikon koji se temelji na tablici argumenata i vrijednosti funkcije koja je korištena za prethodni primjer. Izradimo liniju trenda. Kliknite na bilo kojem mjestu u građevinskom području na kojem je grafikon postavljen lijevom tipkom miša. U tom se slučaju na kaseti pojavljuje dodatni skup kartica - "Rad s dijagramima" . Idite na karticu "Izgled" . Kliknemo na gumb "Trend Line" koji se nalazi u okviru alata "Analiza" . Pojavi se izbornik s prikazom vrste trend linije. Zaustavljamo odabir na tipu koji odgovara konkretnom zadatku. Izaberite opciju "Eksponencijalna aproksimacija" za naš primjer.
2. Excel gradi liniju trendova izravno na planerskom planu u obliku dodatne crne krivulje.
3. Sada nam je zadatak prikazati stvarni koeficijent određivanja. Desnom tipkom miša kliknite trend liniju. Kontekstni izbornik je aktiviran. Zaustavljamo odabir u toj stavci "Trendline format ..." .

   Da biste izvršili prijelaz na prozor formata trendline, možete izvršiti alternativnu radnju. Odaberite liniju trenda klikom na lijevu tipku miša. Prebacimo se na karticu "Izgled" . Kliknemo na gumb "Trend Line" u bloku "Analiza" . Na otvorenom popisu kliknite na najnoviju stavku na popisu akcija - "Dodatni parametri trendline ..." .

4. Nakon bilo koje od dvije gore navedene radnje pokrenut je prozor formata u kojem se mogu dodati dodatne postavke. Konkretno, da bismo ostvarili naš zadatak, trebamo provjeriti okvir pored "Postavite vrijednost točnosti približavanja (R ^ 2) na dijagramu" . Nalazi se na samom dnu prozora. Na taj način uključujemo i mapiranje koeficijenta određivanja na građevinsko područje. Zatim ne zaboravite kliknuti gumb "Zatvori" pri dnu trenutnog prozora.
5. Vrijednost pouzdanosti aproksimacije, odnosno vrijednosti koeficijenta određivanja, bit će prikazana na listi u građevinskom području. U ovom je slučaju ta vrijednost, kako vidimo, 0.9242, koja karakterizira aproksimaciju, kao model dobre kvalitete.
6. Apsolutno na taj način, možete postaviti prikaz koeficijenta određivanja za bilo koju drugu vrstu trend linije. Možete promijeniti vrstu trend linije pomicanjem gumba na vrpci ili kontekstnom izborniku u prozoru parametara, kao što je gore prikazano. Zatim se možete prebaciti na drugu vrstu u grupi "Trend Line" u samom prozoru. Ne zaboravimo, primjerice, kontrolirati da je potvrdni okvir pokraj "Postavite vrijednost točnosti približavanja" na dijagramu označen. Nakon dovršetka gore navedenih koraka, kliknite gumb "Zatvori" u donjem desnom kutu prozora.
7. Linearnom vrstom linija trendova već ima vrijednost povjerenja aproksimacije jednaku 0.9477, koja karakterizira ovaj model, čak i još pouzdanija od trenda trendova eksponencijalnog tipa koji smo ranije razmatrali.
8. Dakle, prebacivanje između različitih tipova trend linija i uspoređivanje njihovih vrijednosti pouzdanosti aproksimacije (koeficijent određivanja), može se naći varijanta čiji model najpreciznije opisuje prikazani grafikon. Varijanta s najvišim pokazateljem koeficijenta određivanja bit će najpouzdaniji. Na temelju toga, možete izgraditi najtočniju prognozu.

   Na primjer, za naš slučaj uspjeli smo utvrditi iskustvom da najviša razina pouzdanosti ima polinomični tip trenda drugog stupnja. Koeficijent određivanja u ovom slučaju je 1. To znači da je ovaj model apsolutno pouzdan, što znači potpuni uklanjanje pogrešaka.

   No, u isto vrijeme, to uopće ne znači da je ova vrsta trend linije također najpouzdanija za drugu grafikon. Optimalan odabir vrste trend linije ovisi o vrsti funkcije na temelju koje je grafikon konstruiran. Ako korisnik nema dovoljno znanja da "pogoditi" najbolju opciju, jedini način da se utvrdi bolje predviđanje jest usporediti koeficijente određivanja, kao što je prikazano u gornjem primjeru.

Pročitajte također:
Izgradnja trend linije u programu Excel
Približavanje u Excelu

U programu Excel postoje dvije glavne opcije za izračunavanje koeficijenta određivanja: korištenje operatora KVPIRSON i korištenje alata "regresija" iz paketa alata "Analiza podataka" . U ovom slučaju, prva od ovih opcija namijenjena je samo za obradu linearnih funkcija, a druga se opcija može koristiti u gotovo svim situacijama. Osim toga, moguće je prikazati koeficijent određivanja za liniju trendova grafikona kao vrijednost pouzdanosti aproksimacije. Pomoću ovog pokazatelja moguće je odrediti vrstu trend linije koja ima najvišu razinu pouzdanosti za određenu funkciju.

• ← Promjena veličine ćelija u Microsoft Excelu
• Određivanje višestrukog koeficijenta korelacije u MS Excelu →