Koeficijent odlučnosti u Excelu

Jedan od pokazatelja koji opisuju kvalitetu izgrađenog modela u statistici je koeficijent određivanja (R ^ 2), koji se naziva i vrijednost točnosti aproksimacije. Uz to, možete odrediti razinu preciznog predviđanja. Naučimo kako izračunati ovaj indikator koristeći razne Excel alate.

Izračun koeficijenta određivanja

Ovisno o razini koeficijenta određivanja, uobičajeno je dijeliti 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 ukazuje na nemogućnost njegove uporabe za prognozu.

Izbor metode za izračunavanje navedene vrijednosti u Excelu ovisi o tome je li regresija linearna ili ne. U prvom slučaju možete koristiti funkciju Firson, a u drugom ćete morati koristiti poseban alat iz paketa analize.

Metoda 1: Izračun koeficijenta određivanja s linearnom funkcijom

Prije svega, saznajte kako pronaći koeficijent određivanja s linearnom funkcijom. U tom slučaju ovaj pokazatelj će biti jednak kvadratu koeficijenta korelacije. Izradit ćemo ga izračunati pomoću ugrađenog Excel funkcije na primjeru određene tablice, koja je prikazana u nastavku.

1. Mi naglašavaju ćeliju u kojoj se koeficijent određivanja ispisuje nakon izračuna i kliknite na ikonu "Insert Funkcija".



2. Učitelji funkcije počinje. Prelazimo u svoju kategoriju "Statistika" i primijetivši ime "KVGIRSON". Zatim, glina na gumb "OK".



3. Pokreće prozor Quilinson Funkcije Argumente. Ovaj operator iz statističke skupine osmišljen je kako bi izračunao kvadrat korelacijskog koeficijenta Pearson funkcije, tj. Linearnu funkciju. Kako se sjećamo, s linearnom funkcijom, koeficijent određivanja je jednako jednak kvadratu koeficijenta korelacije.

   Sintaksa ovog operatera je:

   = CVGirson (poznat_stations_y; poznat_name_x)

   Dakle, funkcija ima dva operatora, od kojih je jedan popis vrijednosti funkcije i drugi argumenti. Operateri se mogu zastupati kao izravno kao vrijednosti navedene kroz polukolon (;) iu obliku referenci na raspone gdje se nalaze. To je posljednja opcija i koristit će nas u ovom primjeru.

   Ugradite kursor u polje "poznate V vrijednosti". Izvodimo stezaljku lijeve tipke miša i proizvodnju sadržaja "y" stupca tablice. Kao što možete vidjeti, adresa navedenog polja podataka odmah se prikazuje u prozoru.

   Slično tome, ispunite "poznato x" polje. Stavili smo kursor u ovo polje, ali ovaj put dodijelite vrijednosti stupca "X".

   Nakon što su svi podaci prikazani u prozoru CVGirson argumente, glina na gumbi "OK" nalazi na dnu.



4. Kao što možete vidjeti, nakon toga, program čini izračunavanje koeficijenta određivanja i daje rezultat u ćeliji koja je istaknuta prije pozivanja čarobnjaka funkcija. U našem primjeru, vrijednost izračunatog pokazatelja pokazala se jednako 1. to znači da je prikazani model apsolutno pouzdan, odnosno eliminira pogrešku.

Lekcija: Master of Funkcije u programu Microsoft Excel

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

No, gornja opcija za izračunavanje željene vrijednosti može se koristiti samo na linearne funkcije. Što učiniti kako bi se izračun u nelinearnoj funkciji? U Excelu postoji prilika. Može se obaviti pomoću "Regresion" alat, koji je sastavni dio paketa analize podataka.

1. No prije korištenja navedenog alata, sama "paket analize" treba aktivirati, što je onemogućeno prema zadanim postavkama u Exceleu. Prelazimo u karticu "File", a zatim prođite kroz stavku "Parametri".



2. U prozoru koji se otvara, prijeđite na odjeljak "Add-in" pomoću navigacije na lijevom vertikalnom izborniku. Na dnu desno prozor nalazi se kontrolno polje. Na popisu dostupnih pododjeljaka odaberite ime "Add-in Excel ...", a zatim kliknite na gumb "Go ...", koji se nalazi na desnoj strani polja.



3. Pokreće se prozor. U središnjem dijelu nalazi se popis dostupnih dodataka. Ugradite potvrdni okvir u blizini pozicije "Paket analize". Nakon toga morate kliknuti na gumb "OK" na desnoj strani sučelja prozora.



4. Alat alata za analizu podataka u trenutnom Excel instancu će se aktivirati. Pristup tome nalazi se na kasetu na kartici podatkovni. Premjestite na određenu karticu i glinu na gumb "Analiza podataka" u grupi za postavke "Analiza".



5. Prozor "Analiza podataka" aktivira se s popisom alata za obradu informacija. Iz ovog popisa izdvojimo s ovog popisa "Regresija" i glina na gumb "OK".



6. Tada se otvara prozor "Regresion". Blokiranje prvih postavki je "ulazni podaci". Ovdje u dva polja trebate odrediti adresu raspona gdje se nalaze vrijednosti argumenta i funkcije. Pokažite kursor u polje "Ulazni interval y" i dodijelite sadržaj stupca "Y" na listu. Nakon što se adresa polja pojavljuje u regresijskom prozoru, stavljamo kursor u polje "Ulazni interval y" i dodijelite stanice kolone "X" na isti način.

   U blizini parametara "oznaka" i "konstantne nule" zastavice ne stavljaju. Potvrdni okvir se može ugraditi u blizini parametra "pouzdanosti" i u suprotnom polju odrediti željenu vrijednost odgovarajućeg indikatora (prema zadanim postavkama 95%).

   U grupi "Izlazni parametri" morate navesti u kojem području će se prikazati rezultat izračuna. Postoje tri opcije:

   • Područje na tekućem listu;
   • Drugi list;
   • Drugu knjigu (novu datoteku).

   Uspostavite svoj izbor u prvom ostvarenju tako da se izvorni podaci i rezultat nalaze na jedan radni list. Prekidali smo u blizini parametra "izlaznog intervala". U polju nasuprot ove stavke, stavljamo pokazivač. Kliknite lijevu tipku miša na praznom elementu na listu, koji je dizajniran da postane lijeva gornja stanica izlazne tablice izračuna. Adresa ovog elementa treba istaknuti u prozoru "Regresion".

   Grupe parametara "Ostaci" i "normalna vjerojatnost" ignoriraju, jer nije važno za rješavanje zadatka. Nakon toga smo glina na gumb "OK", koji se nalazi u gornjem desnom kutu prozora "regresije".



7. Program čini izračun na temelju prethodno unesenih podataka i prikazuje rezultat u određeni raspon. Kao što možete vidjeti, ovaj alat prikazuje prilično velik broj rezultata na različitim parametrima. Ali u kontekstu trenutne lekcije, zainteresirani smo za indikator r-kvadrata. U ovom slučaju, jednako je 0,947664, što karakterizira odabrani model kao model dobre kvalitete.

Metoda 3: Koeficijent određivanja za trend liniju

Osim navedenih opcija, koeficijent određivanja može se prikazati izravno za trend liniju u grafikonu ugrađenom na Excel listu. Saznajte kako se to može učiniti na određenom primjeru.

1. Imamo raspored izgrađen na temelju tablice argumenta i funkcija funkcije koja je korištena za prethodni primjer. Izgradit ćemo liniju trenda. Kliknite na bilo koje stranice područja izgradnje na kojem se raspored nalazi lijevi klik. U isto vrijeme, na vrpci se pojavljuje dodatni skup kartica - "Rad s kartama". Idite na karticu "Izgled". Glina na gumb "trend linija", koji se nalazi u alatnoj traci "Analiza". Izbornik se pojavljuje s izborom vrste trenda. Pokrenite izbor na tip koji odgovara određenom zadatku. Dopustite da odaberemo opciju "Eksponencijalna aproksimacija" za naš primjer.



2. Excel se izravno gradi na ravnini konstruiranja trenda linije u obliku dodatne crne krivulje.



3. Sada je naš zadatak prikazati stvarni koeficijent određivanja. Kliknite desnom tipkom miša uz liniju trenda. Aktivira se kontekstni izbornik. Pokrenite izbor u njemu u točki formata trenda.

   

   Da biste izvršili prijelaz na prozor format trenda, možete napraviti alternativnu akciju. Označavamo liniju trenda s lijevom tipkom miša na njemu. Premjestiti u karticu "Izgled". Glina na gumb "Trend Line" u bloku "Analiza". Na popisu Clay liste na najboljoj točki popisa akcijskih popisa - "Dodatni parametri trend linija ...".



4. Nakon bilo koje od dva gore navedena akcija, prozor formata je pokrenut u kojem možete proizvesti dodatne postavke. Konkretno, kako bi se ispunio naš zadatak potrebno je uspostaviti potvrdni okvir nasuprot stavke "mjesto vrijednosti točnosti aproksimacije (r ^ 2)". Nalazi se na dnu prozora. To jest, tako da uključujemo prikaz koeficijenta određivanja na građevinskom prostoru. Tada ne zaboravite kliknuti na gumb "Zatvori" na dnu trenutnog prozora.



5. Vrijednost točnosti aproksimacije, odnosno vrijednost koeficijenta određivanja bit će prikazana na listu u građevinskom području. U tom slučaju, ta je vrijednost vidljiva, jednaka 0,9242, koja karakterizira aproksimaciju kao model dobre kvalitete.



6. Apsolutno na taj način možete uspostaviti odlučnost koeficijent pokazati za bilo koju drugu vrstu trenda linije. Možete promijeniti vrstu linije trenda tako što ćete iznijeti prijelaz kroz gumb na vrpci ili kontekstnom izborniku u prozoru njegovih parametara, kao što je prikazano gore. Zatim u samom prozoru u grupi "Graditi trend linija", možete se prebaciti na drugi tip. Ne zaboravljamo ga kontrolirati kako bismo "stavili vrijednost točnosti aproksimacije na dijagramu vrijednosti aproksimacije". Nakon završetka gore navedenih radnji kliknite na gumb "Zatvori" u donjem desnom kutu prozora.



7. S linearnim tipom, linija trenda već ima vrijednost točnosti aproksimacije jednake 0,9477, što karakterizira ovaj model, kao još pouzdaniji od linije trenda eksponencijalnog tipa koji se smatra nas.



8. Prema tome, prebacivanje između različitih vrsta trenda linije i uspoređujući njihove aproksimacijske vrijednosti pouzdanosti (koeficijent određivanja), možete pronaći opciju da model najtočnije opisuje podneseni raspored. Opcija s najvišom stopom određivanja bit će najpouzdanija. Na temelju toga, možete izgraditi najtočniju prognozu.

   Na primjer, za naš slučaj, bilo je moguće utvrditi da najviša razina pouzdanosti ima polinomski tip drugog stupnja trenda. Koeficijent određivanja u ovom slučaju je 1. To sugerira da je navedeni model apsolutno pouzdan, što znači potpunu iznimku pogrešaka.

   

   No, u isto vrijeme to ne znači da će za još jedan graf, ova vrsta trenda također biti najpouzdaniji. Optimalan izbor vrste trenda ovisi o vrsti funkcije, na temelju kojih je izgrađen raspored. Ako korisnik nema dovoljno znanja "na oku" za procjenu najviše kvalitete opcije, onda je jedini izlaz određivanja najbolje prognoze samo usporedba koeficijenata određivanja, kao što je prikazano u gornjem primjeru.

Vidi također:

Izgradnja trenda linije u Excelu

Aproksimacija u Excelu

U Excelu postoje dvije glavne mogućnosti za izračunavanje koeficijenta određivanja: korištenje operatera FILSON-a i primjene regresijskog alata iz paketa alata za analizu podataka. U isto vrijeme, prva od ovih opcija namijenjena je samo za vrijeme obrade linearne funkcije, a druga opcija se može koristiti u gotovo svim situacijama. Osim toga, moguće je prikazati koeficijent određivanja za liniju grafikona grafova kao vrijednost točnosti aproksimacije. Ovim pokazateljem moguće je odrediti vrstu trenda, koja ima najvišu razinu pouzdanosti za određenu funkciju.