Izračun koeficienta določanja v programu Microsoft Excel

Eden od kazalnikov, ki opisujejo kakovost konstruiranega modela v statistiki, je koeficient določanja (R ^ 2), ki se imenuje tudi vrednost zanesljivosti aproksimacije. S svojo pomočjo lahko določite stopnjo natančnosti napovedi. Ugotovimo, kako izračunati ta indikator z različnimi orodji Excel.

Izračun koeficienta določanja

Glede na stopnjo koeficienta določanja je običajno razdeliti modele v tri skupine:

• 0,8 - 1 - model dobre kakovosti;
• 0,5 - 0,8 - model sprejemljive kakovosti;
• 0 - 0,5 - model slabe kakovosti.

V slednjem primeru kakovost modela kaže, da je ni mogoče uporabiti za napovedovanje.

Izbira načina izračunavanja določene vrednosti v Excelu je odvisna od tega, ali je regresija linearna ali ne. V prvem primeru lahko uporabite funkcijo KVPIRSON , v drugem pa boste morali uporabiti posebno orodje iz paketa analize.

Metoda 1: izračun koeficienta določanja za linearno funkcijo

Najprej bomo izvedeli, kako najti koeficient določanja za linearno funkcijo. V tem primeru bo ta kazalnik enak kvadratu korelacijskega koeficienta. Izračunali jo bomo z vgrajeno funkcijo Excel z uporabo primerne tabele, ki je prikazana spodaj.

1. Izberite celico, v kateri bo koeficient določanja izpisal po izračunu in kliknite ikono »Vstavi funkcijo« .
2. Začne se čarovnik za funkcije . Premaknemo se v svojo kategorijo "Statistična" in označimo ime "KVPIRSON" . Nato kliknite gumb "OK" .
3. Začne se okno argumentov funkcije KVPIRSON . Ta operater iz statistične skupine se uporablja za izračun kvadrat korelacijskega koeficienta funkcije Pearson, to je linearne funkcije. In ko se spomnimo, z linearno funkcijo je koeficient določanja točno enak kvadratu korelacijskega koeficienta.

   Sintaksa te izjave je:

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

   Tako ima funkcija dva operaterja, med njimi tudi seznam vrednosti funkcije in drugi od argumentov. Operaterji so lahko predstavljeni neposredno v obliki vrednosti, ki so naštete prek podpičja ( ; ), in kot sklici na območja, kjer se nahajajo. To je zadnja možnost, ki jo bomo uporabili v tem primeru.

   Nastavite kazalko v polju »Znano y« . Z levim gumbom miške pritiskamo in izberemo vsebino stolpca »Y« tabele. Kot lahko vidite, se v podoknu takoj prikaže naslov določenega podatkovnega polja.

   Podobno izpolnite tudi polje »Znane vrednosti x« . Postavili smo kazalec v to polje, vendar tokrat izberemo vrednosti stolpca "X" .

   Ko so bili vsi podatki prikazani v oknu argumentov KVPIRSON , kliknite gumb "OK", ki se nahaja na samem spodnjem delu okna .

4. Kot lahko vidite, program nato izračuna koeficient določanja in rezultate izpiše v celico, ki je bila dodeljena, preden je bil pozvan čarovnik za funkcije . V našem primeru se je vrednost izračunanega kazalnika izkazala za 1. To pomeni, da je predstavljeni model povsem zanesljiv, to pomeni, da odpravi napako.

Lekcija: Čarovnik za funkcije v programu Microsoft Excel

Metoda 2: izračun koeficienta določanja v nelinearnih funkcijah

Toda zgornjo varianto izračuna želene vrednosti lahko uporabimo samo za linearne funkcije. Kaj lahko storimo, da ga izračunamo v nelinearni funkciji? V Excelu je tudi taka priložnost. To je mogoče storiti s pomočjo orodja "Regression" , ki je sestavni del paketa "Analiza podatkov" .

1. Toda pred uporabo določenega orodja morate aktivirati paket »Analiza« , ki je v Excelu onemogočen. Premaknemo se na kartico »Datoteka« in pojdimo na »Možnosti« .
2. V oknu, ki se odpre, se pomaknemo v razdelek »Dodatki«, tako da se pomaknete po levi navpični meni. Polje "Upravljanje" se nahaja v spodnjem delu desnega dela okna. Na seznamu podpodročij, ki so na voljo, izberite ime »Dodatek za Excel ...« in nato kliknite gumb »Pojdi« na desni strani polja.
3. Začelo se je dodajanje oken. V osrednjem delu je seznam razpoložljivih dodatkov. Označite polje zraven elementa »Analitični paket« . Nato kliknite gumb "OK" na desni strani okna vmesnika.
4. Aktiviran je paket orodij »Analiza podatkov« v trenutnem primeru Excel. Dostop do njega je na traku na zavihku »Podatki «. Premik na določeni zavihek in klik na gumb »Analiza podatkov« v skupini nastavitev »Analiza« .
5. Okno "Analiza podatkov" se aktivira s seznamom orodij za obdelavo profilnih informacij. Na tem seznamu izberemo postavko "Regresija" in kliknite gumb "V redu" .
6. Odpre se okno za regresijsko orodje. Prvi blok nastavitev je "Vhodni podatki" . V dveh poljih morate določiti naslove območij, kjer se nahajajo vrednosti argumenta in funkcije. Postavimo kazalko v polje "Vhodni interval Y" in na listu vstavite vsebino stolpca "Y" . Ko je naslov polja prikazan v oknu "Regresija" , postavite kazalko v polje "Input interval Y" in na enak način izberite celice stolpca "X" .

   O parametrih "Label" in "Constant-zero" ne nastavljamo potrditvenih polj. Polje lahko nastavite v bližini parametra "Zanesljivost" in v polju nasproti lahko določite želeno vrednost ustreznega kazalnika (95% privzeto).

   V skupini "Izhodni parametri" morate navesti, na katerem območju se prikaže rezultat izračuna. Obstajajo tri možnosti:

   • Območje na tekočem listu;
   • Drug list;
   • Druga knjiga (nova datoteka).

   Prvo se bomo ustavili, tako da bomo prvotne podatke in rezultat dali na en delovni list. Postavite stikalo okoli parametra "Izhodni interval" . V polju nasproti te postavke postavimo kazalko. Klik na levi gumb miške na praznem elementu na listu, ki naj bi postal leva zgornja celica izhodne tabele rezultatov izračuna. Naslov tega elementa je treba poudariti v okencu »Regresija« .

   Skupine parametrov "Remains" in "Normal Probability" se prezrejo, ker niso pomembne za rešitev naloge. Nato kliknite gumb "OK" , ki se nahaja v zgornjem desnem kotu okna "Regresija" .

7. Program izračuna na podlagi predhodno vnesenih podatkov in rezultat izračuna na določeno območje. Kot lahko vidite, to orodje prikaže veliko število rezultatov o različnih parametrih na listu. Toda v kontekstu trenutne lekcije nas zanima kazalnik "R-kvadrat" . V tem primeru je 0,947664, ki označuje izbrani model kot model dobre kakovosti.

Metoda 3: Koeficient določanja linije trendov

Poleg zgornjih možnosti je lahko koeficient določanja prikazan neposredno za linijo trendov v grafikonu, ki je zgrajen na listu Excel. Ugotovimo, kako je to mogoče storiti s konkretnim primerom.

1. Imamo graf na podlagi tabele argumentov in vrednosti funkcije, ki je bila uporabljena za prejšnji primer. Gradimo jo s trendom. Kliknite poljubno mesto na območju gradnje, na katerem je graf postavljen z levim gumbom miške. V tem primeru se na traku pojavijo dodatni nabor zavihkov - »Delo z diagrami« . Pojdite na kartico »Postavitev «. Kliknemo na gumb "Trend Line" , ki se nahaja v orodjarni "Analiza" . Pojavi se meni, ki prikazuje vrsto linije trendov. Prekinemo izbor na vrsto, ki ustreza določeni nalogi. Izbira možnosti »eksponentnega približevanja« za naš primer.
2. Excel gradi trendno linijo neposredno na ploskvi za načrtovanje v obliki dodatne črne krivulje.
3. Zdaj je naša naloga pokazati dejanski koeficient določanja. Z desno miškino tipko kliknite linijo trendov. Kontekstni meni je aktiviran. Izbor v njej ustavimo na elementu "Trendline format ..." .

   Če želite izvedeti prehod v okno oblikovanja trendne vrstice, lahko izvedete alternativno dejanje. Izberite linijo trendov, tako da jo kliknete z levim gumbom miške. Premaknemo se na kartico »Postavitev «. V bloku "Analiza" kliknite gumb "Trend Line" . Na odprtem seznamu kliknite najnovejši element na seznamu dejanj - "Dodatne parametre linije trendov ..." .

4. Po enem izmed dveh zgoraj navedenih dejanj se sproži okno oblikovanja, v katerem se lahko izvedejo dodatne nastavitve. Zlasti za izpolnitev naše naloge moramo potrditi polje zraven "Postavite vrednost približne natančnosti (R ^ 2) na diagram" . Nahaja se na samem dnu okna. To pomeni, da na ta način vključimo preslikavo koeficienta določanja na območje gradnje. Nato pozabite klikniti gumb "Zapri" na dnu trenutnega okna.
5. Vrednost zanesljivosti aproksimacije, to je vrednosti koeficienta določanja, bo prikazana na plošči v gradbenem območju. V tem primeru je ta vrednost, kot smo videli, 0,9242, ki označuje aproksimacijo kot model dobre kakovosti.
6. Absolutno na ta način lahko nastavite prikaz koeficienta določanja za katero koli drugo vrsto trendne linije. Vrsto linije trenda lahko spremenite tako, da v oknu z njegovimi parametri premikate gumb na traku ali kontekstni meni, kot je prikazano zgoraj. Nato lahko preklopite na drug tip v skupino »Trend Line« v samem oknu. Ne pozabimo, na primer, preveriti, ali je na sliki označeno potrditveno polje poleg "Postavite vrednost približne natančnosti" . Ko končate zgornje korake, v spodnjem desnem kotu okna kliknite gumb »Zapri« .
7. V linijskem vrstnem redu je trendna linija že imela vrednost zaupanja aproksimacije, ki je enaka 0,9477, kar je značilen za ta model, še bolj zanesljiv kot linija trendov eksponentne vrste, ki smo jo obravnavali prej.
8. Tako lahko preklop med različnimi vrstami trendnih linij in primerjavo njihovih zanesljivostnih vrednosti približevanja (koeficient določanja) najdemo različico, katere model najbolj natančno opisuje predstavljeni graf. Najbolj zanesljiva bo varianta z najvišjim kazalnikom koeficienta določanja. Na podlagi tega lahko zgradite najbolj natančno napoved.

   Na primer, v našem primeru smo z izkušnjami uspeli ugotoviti, da ima najvišja raven zanesljivosti polinomsko vrsto linije trendov druge stopnje. Koeficient določanja v tem primeru je 1. To pomeni, da je ta model povsem zanesljiv, kar pomeni popolno odpravo napak.

   Toda hkrati to sploh ne pomeni, da je ta vrsta linije trendov tudi najbolj zanesljiva za drugo shemo. Optimalna izbira vrste linije trenda je odvisna od vrste funkcije, na podlagi katere je bil graf grajen. Če uporabnik nima dovolj znanja, da bi "ugibal" najboljšo možnost, je edini način za določitev boljše napovedi primerjanje koeficientov določanja, kot je bilo prikazano v zgornjem primeru.

Preberite tudi:
Gradimo trendno vrstico v Excelu
Približevanje v Excelu

V programu Excel obstajajo dve glavni možnosti za izračun koeficienta določanja: uporaba operaterja KVPIRSON in uporaba orodja "Regresija" iz paketa orodij "Analiza podatkov" . V tem primeru je prva od teh možnosti namenjena le uporabi v procesu obdelave linearne funkcije, druga možnost pa je mogoče uporabiti v skoraj vseh primerih. Poleg tega je mogoče prikazati koeficient določanja linije trendov grafov kot vrednost zanesljivosti približka. S tem indikatorjem je mogoče določiti vrsto linije trendov, ki ima najvišjo stopnjo zaupanja za določeno funkcijo.

• ← Spreminjanje velikosti celic v programu Microsoft Excel
• Določitev multiple korelacijskega koeficienta v MS Excelu →