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.

Tablica podataka u programu Microsoft Excel

  1. Odaberite ćeliju u kojoj će se koeficijent određivanja izlaziti nakon izračuna i kliknite na ikonu "Umetni funkciju" .
  2. Idite na čarobnjak za funkcije u programu Microsoft Excel

  3. Pokreće se Čarobnjak za funkcije . Prelazimo u svoju kategoriju "Statistička" i označimo naziv "KVPIRSON" . Zatim kliknite gumb "OK" .
  4. Idite na prozor argumenata KVPIRSON funkcije u programu Microsoft Excel

  5. 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.

  6. Prozor argumenta KVPIRSON funkcije u programu Microsoft Excel

  7. 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.

Rezultat izračuna KVPIRSON funkcije u Microsoft Excelu

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. Idite na prozor opcija u programu Microsoft Excel

  3. 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.
  4. Idite na prozor dodatka u programu Microsoft Excel

  5. 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.
  6. Prozor dodatka u programu Microsoft Excel

  7. 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" .
  8. Pokrenite paket analize podataka u programu Microsoft Excel

  9. Prozor "Analiza podataka" aktivira se s popisom alata za obradu podataka profila. Na ovom popisu odabiremo stavku "Regresija" i kliknite na gumb "OK" .
  10. Pokretanje alata za regresiju u analizi podataka u prozoru programa Microsoft Excel

  11. 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" .

  12. Alat za regresiju Analiza paketa u Microsoft Excelu

  13. 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.

Rezultat izračuna koeficijenta određivanja pomoću alata Regression u prozoru Analiza podataka u Microsoft Excelu

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. Stvorite trend linije u programu Microsoft Excel

  3. Excel gradi liniju trendova izravno na planerskom planu u obliku dodatne crne krivulje.
  4. Trendline u Microsoft Excelu

  5. 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 ..." .

    Idite na prozor formata trend linije u programu Microsoft Excel

    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 ..." .

  6. Idite na dodatne parametre trendline pomoću gumba na vrpci u programu Microsoft Excel

  7. 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.
  8. Prozor oblika Trendline u Microsoft Excelu

  9. 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.
  10. Koeficijent određivanja trend linije u Microsoft Excelu

  11. 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.
  12. Promijenite vrstu trend linije u prozoru pravokutnog oblika u programu Microsoft Excel

  13. 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.
  14. Vrijednost točnosti približavanja za linearnu vrstu trend linije u programu Microsoft Excel

  15. 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.

    Vrijednost pouzdanosti približavanja za polinomne vrste trend linije u Microsoft Excelu

    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.