Výpočet určovacieho koeficientu v programe Microsoft Excel

Jedným z ukazovateľov, ktoré popisujú kvalitu zostrojeného modelu v štatistike, je určujúci koeficient (R ^ 2), ktorý sa tiež nazýva hodnota spoľahlivosti aproximácie. Svoju pomoc môžete určiť úroveň presnosti prognózy. Dozvieme sa, ako vypočítať tento ukazovateľ pomocou rôznych nástrojov programu Excel.

Výpočet koeficientu určenia

V závislosti od úrovne koeficientu určenia je bežné rozdeliť modely na tri skupiny:

• 0,8 - 1 - model dobrej kvality;
• 0,5 - 0,8 - model prijateľnej kvality;
• 0 - 0,5 - model nízkej kvality.

V druhom prípade kvalita modelu naznačuje, že sa nemôže použiť na prognózu.

Voľba spôsobu výpočtu špecifikovanej hodnoty v programe Excel závisí od toho, či je regresia lineárna alebo nie. V prvom prípade môžete použiť funkciu KVPIRSON a v druhej budete musieť použiť špeciálny nástroj z balíka analýzy.

Metóda 1: výpočet určovacieho koeficientu pre lineárnu funkciu

Najprv zistíme, ako nájsť koeficient určovania pre lineárnu funkciu. V tomto prípade sa tento ukazovateľ rovná štvorcu korelačného koeficientu. Vypočítame to pomocou vstavanej funkcie Excel pomocou príkladu konkrétnej tabuľky, ktorá je uvedená nižšie.

1. Vyberte bunku, kde sa po výpočte vykáže koeficient určovania a kliknite na ikonu "Vložiť funkciu" .
2. Spustí sa Sprievodca funkciami . Prejdeme do svojej kategórie "Štatistické" a označíme meno "KVPIRSON" . Potom kliknite na tlačidlo "OK" .
3. Otvorí sa okno argumentov funkcie KVPIRSON . Tento operátor zo štatistickej skupiny sa používa na výpočet štvorca korelačného koeficientu funkcie Pearson, to znamená lineárnej funkcie. A ako si spomíname, s lineárnou funkciou je koeficient určenia presne rovný štvorcu korelačného koeficientu.

   Syntax tohto vyhlásenia je:

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

   Funkcia má teda dva operátory, z ktorých jeden je zoznam hodnôt funkcie a druhý z argumentov. Operátori môžu byť reprezentovaní priamo vo forme hodnôt uvedených pomocou bodkočiarky ( ; ) a ako odkazy na rozsahy, v ktorých sa nachádzajú. Je to druhá možnosť, ktorú použijeme v tomto príklade.

   Nastavte kurzor do poľa "Známe y" . Zvierame ľavé tlačidlo myši a zvoľme obsah stĺpca "Y" tabuľky. Ako vidíte, adresa zadaného dátového poľa sa ihneď zobrazí v okne.

   Podobne vyplníme pole "Známe hodnoty x" . Kurzor sme umiestnili do tohto poľa, tentokrát však vyberieme hodnoty stĺpca "X" .

   Po zobrazení všetkých údajov v okne s argumentmi KVPIRSON kliknite na tlačidlo "OK" umiestnené na jeho najnižšej strane.

4. Ako vidíte, program potom vypočíta určujúci koeficient a výsledok sa vykáže do bunky, ktorá bola pridelená pred sprievodcom sprievodcu funkciou . V našom príklade sa hodnota vypočítaného indikátora ukázala byť 1. To znamená, že prezentovaný model je absolútne spoľahlivý, to znamená, že eliminuje chybu.

Lekcia: Sprievodca funkciami v programe Microsoft Excel

Metóda 2: výpočet určovacieho koeficientu v nelineárnych funkciách

Ale vyššie spomenutý variant výpočtu požadovanej hodnoty môže byť použitý iba pre lineárne funkcie. Čo môžeme urobiť na výpočet v nelineárnej funkcii? V programe Excel je takáto príležitosť. Môže sa to stať pomocou nástroja "Regresie" , ktorý je neoddeliteľnou súčasťou balíka "Analýza údajov" .

1. Ale pred použitím špecifikovaného nástroja by ste mali aktivovať samotný balík "Analysis package" , ktorý je predvolene v programe Excel zakázaný. Prejdeme na kartu "Súbor" a potom prejdeme na "Možnosti" .
2. V okne, ktoré sa otvorí, prejdeme do časti Doplnky pomocou navigácie v ľavej vertikálnej ponuke. Pole "Manažment" sa nachádza v dolnej časti pravého okienka. V zozname dostupných podsekcií vyberte názov "Doplnky programu Excel ..." a potom kliknite na tlačidlo "Choď ..." napravo od poľa.
3. Otvorí sa okno doplnku. V centrálnej časti je zoznam dostupných doplnkov. Začiarknite políčko vedľa položky "Analýza balíka" . Potom kliknite na tlačidlo "OK" na pravej strane okna rozhrania.
4. Bude aktivovaný balík nástrojov "Analýza údajov" v aktuálnej inštancii programu Excel. Prístup k nej je umiestnený na páse na karte "Data" . Presunutím na zadanú kartu a kliknutím na tlačidlo "Analýza údajov" v skupine nastavení "Analýza" .
5. Okno "Analýza údajov" je aktivované so zoznamom nástrojov na spracovanie profilových informácií. Z tohto zoznamu vyberieme položku "Regresie" a klikneme na tlačidlo "OK" .
6. Potom sa otvorí okno Regresný nástroj. Prvý blok nastavení je "Vstupné údaje" . Tu, v dvoch poliach, musíte určiť adresy rozsahov, kde sa nachádzajú hodnoty argumentu a funkcie. Kurzor sme umiestnili do poľa "Vstupný interval Y" a vybrali sme obsah stĺpca "Y" na hárku. Po zadaní adresy poľa v okne "Regresie" vložte kurzor do poľa "Vstupný interval Y" a rovnakým spôsobom vyberte bunky v stĺpci "X" .

   O parametroch "Label" a "Constant-zero" neurčujeme začiarkavacie políčka. Zaškrtávacie políčko sa dá nastaviť v blízkosti parametra "Úroveň spoľahlivosti" av poli opačnom môžete určiť požadovanú hodnotu príslušného indikátora (štandardne 95%).

   V skupine "Výstupné parametre" musíte zadať, v ktorej oblasti sa zobrazí výsledok výpočtu. Existujú tri možnosti:

   • Oblasť na aktuálnom hárku;
   • Iný list;
   • Ďalšia kniha (nový súbor).

   V prvej možnosti skončíme, takže pôvodné údaje a výsledok sa umiestnia na jeden pracovný hárok. Prepnite prepínač okolo parametra "Výstupný interval" . V poli oproti tejto položke umiestnime kurzor. Klikneme na ľavé tlačidlo myši na prázdny prvok na hárku, ktorý sa má stať ľavou hornou bunkou výstupnej tabuľky výsledkov výpočtu. Adresa tohto prvku by mala byť zvýraznená v poli "Regresie" .

   Skupiny parametrov "Zvyšky" a "Normálna pravdepodobnosť" sa ignorujú, pretože nie sú dôležité pre riešenie úlohy. Potom kliknite na tlačidlo "OK" , ktoré sa nachádza v pravom hornom rohu okna "Regresie" .

7. Program vypočíta na základe predtým zadaných údajov a výstupy dosiahne daný rozsah. Ako vidíte, tento nástroj zobrazuje veľké množstvo výsledkov na rôznych parametroch na hárku. Ale v kontexte súčasnej lekcie nás zaujíma indikátor "R-square" . V tomto prípade je to 0,947664, čo charakterizuje vybraný model ako model dobrej kvality.

Metóda 3: Určujúci koeficient pre trendovú čiaru

Okrem uvedených možností môže byť koeficient určenia zobrazený priamo pre trendovú čiaru v grafe postavený na hárku programu Excel. Pozrime sa, ako to možno urobiť konkrétnym príkladom.

1. Máme graf na základe tabuľky argumentov a hodnôt funkcie, ktorá bola použitá pre predchádzajúci príklad. Buďme na to postaviť trendovú líniu. Kliknite na ľubovoľné miesto v oblasti konštrukcie, na ktorej je graf umiestnený ľavým tlačidlom myši. V takom prípade sa na kazetovej karte objaví prídavná sada kariet - "Práca s diagrammi" . Prejdite na kartu Rozloženie . Klikneme na tlačidlo "Trend Line" , ktoré sa nachádza v poli "Analýza" . Zobrazí sa ponuka s typom trendu. Zastavíme výber na type, ktorý zodpovedá konkrétnej úlohe. Pre náš príklad si vyberieme možnosť "Exponenciálna aproximácia" .
2. Aplikácia Excel vytvára trendovú čiaru priamo na rovine výkresu vo forme dodatočnej čiernej krivky.
3. Našou úlohou je zobraziť skutočný koeficient určenia. Kliknite pravým tlačidlom na trendovú čiaru. Kontextové menu sa aktivuje. Zastavíme výber v položke "Trendline formát ..." .

   Ak chcete vykonať prechod na okno s formátom trendu, môžete vykonať alternatívnu akciu. Zvoľte trendový riadok kliknutím na ňu ľavým tlačidlom myši. Prejdeme na kartu "Usporiadanie" . Klikneme na tlačidlo "Trend Line" v bloku "Analysis" . V otvorenom zozname kliknite na najnovšiu položku v zozname akcií - "Ďalšie parametre trendovej čiary ..." .

4. Po každej z vyššie uvedených akcií sa spustí formátovacie okno, v ktorom je možné vykonať ďalšie nastavenia. Najmä, aby sme splnili našu úlohu, musíme začiarknuť políčko vedľa položky "Umiestnite na diagrame hodnotu presnosti aproximácie (R ^ 2)" . Nachádza sa v spodnej časti okna. To znamená, že týmto spôsobom zahrneme mapovanie určovacieho koeficientu na konštrukčnú oblasť. Potom nezabudnite kliknúť na tlačidlo "Zavrieť" v spodnej časti aktuálneho okna.
5. Hodnota spoľahlivosti aproximácie, teda hodnota určovacieho koeficientu, sa zobrazí na hárku v oblasti výstavby. V tomto prípade je táto hodnota, ako vidíme, 0,9242, ktorá charakterizuje aproximáciu ako model dobrej kvality.
6. Absolútne týmto spôsobom môžete nastaviť zobrazenie koeficientu určenia pre akýkoľvek iný typ trendu. Typ trendu môžete zmeniť tak, že prejdete tlačidlom na páse alebo kontextovou ponukou v okne parametrov, ako je uvedené vyššie. Potom môžete prepnúť na iný typ v skupine "Trend Line" v samotnom okne. Nezabudneme napríklad kontrolovať zaškrtávacie políčko vedľa položky "Umiestniť hodnotu aproximácie presnosti" na diagram . Po dokončení vyššie uvedených krokov kliknite na tlačidlo Zavrieť v pravom dolnom rohu okna.
7. Pri lineárnom type trendová čiara už má hodnotu spoľahlivosti aproximácie rovnajúcej sa 0,9477, ktorá charakterizuje tento model, a je ešte spoľahlivejšia ako trendová čiara exponenciálneho typu, ktorú sme považovali za skorší.
8. Preto prepínanie medzi rôznymi typmi trendových línií a porovnávanie ich hodnôt spoľahlivosti aproximácie (koeficient determinácie), možno nájsť variant, ktorého model najpresnejšie opisuje prezentovaný graf. Variant s najvyšším ukazovateľom určovacieho koeficientu bude najspoľahlivejší. Na základe toho môžete vytvoriť najpresnejšiu predpoveď.

   Napríklad v našom prípade sa nám podarilo zistiť, že najvyššia úroveň spoľahlivosti má polynomický druh trendu v druhom stupni. Koeficient určenia v tomto prípade je 1. To znamená, že tento model je absolútne spoľahlivý, čo znamená úplné odstránenie chýb.

   Zároveň však vôbec neznamená, že tento typ trendu je tiež najspoľahlivejší v ďalšom grafe. Optimálna voľba typu trendu závisí od typu funkcie, na základe ktorej bol graf skonštruovaný. Ak používateľ nemá dostatok vedomostí, aby "odhadol" najlepšiu voľbu, potom jediný spôsob, ako zistiť lepšiu predpoveď, je porovnať určovacie koeficienty, ako to bolo uvedené v príklade vyššie.

Prečítajte si tiež:
Vytváranie trendu v programe Excel
Aproximácia v programe Excel

V programe Excel existujú dve hlavné možnosti výpočtu koeficientu určenia: použitie operátora KVPIRSON a použitie nástroja "Regresie" z balíka nástrojov "Analýza údajov" . V takom prípade je prvá z týchto možností určená na použitie len v procese spracovania lineárnej funkcie a iná možnosť môže byť použitá takmer vo všetkých situáciách. Okrem toho je možné zobraziť koeficient určovania trendu v grafoch ako hodnotu spoľahlivosti aproximácie. Pomocou tohto indikátora je možné určiť typ trendu, ktorý má najvyššiu úroveň spoľahlivosti pre konkrétnu funkciu.

• ← Zmena veľkosti buniek v programe Microsoft Excel
• Určenie viacnásobného korelačného koeficientu v MS Excel →