Výpočet koeficientu určení v aplikaci Microsoft Excel

Jedním z ukazatelů popisujících kvalitu konstruovaného modelu ve statistice je určovací koeficient (R ^ 2), který se také nazývá hodnota spolehlivosti aproximace. S jeho pomocí můžete určit míru přesnosti prognózy. Zjistíme, jak tento ukazatel vypočítat pomocí různých nástrojů aplikace Excel.

Výpočet koeficientu určení

V závislosti na úrovni koeficientu určení je společné rozdělit modely na tři skupiny:

• 0,8 - 1 - model dobré kvality;
• 0,5 - 0,8 - model přijatelné kvality;
• 0 - 0,5 - model špatné kvality.

V druhém případě kvalita modelu naznačuje, že nemůže být použita pro prognózu.

Volba způsobu výpočtu zadané hodnoty v aplikaci Excel závisí na tom, zda je regrese lineární nebo nikoliv. V prvním případě můžete použít funkci KVPIRSON a ve druhé budete muset použít speciální nástroj z analytického balíčku.

Metoda 1: výpočet určovacího koeficientu pro lineární funkci

Nejprve zjistěte, jak najít určovací koeficient pro lineární funkci. V tomto případě se tento indikátor rovná čtverci korelačního koeficientu. Budeme jej vypočítat pomocí vestavěné funkce Excel pomocí příkladu konkrétní tabulky, která je uvedena níže.

1. Vyberte buňku, kde bude po výpočtu vygenerován koeficient určení a klikněte na ikonu "Vložit funkci" .
2. Spustí se Průvodce funkcí . Přejdeme do jeho kategorie "Statistické" a označíme název "KVPIRSON" . Poté klikněte na tlačítko "OK" .
3. Otevře se okno argumentů funkce KVPIRSON . Tento operátor ze statistické skupiny se používá k výpočtu čtverce korelačního koeficientu funkce Pearson, tj. Lineární funkce. A jak si připomínáme, s lineární funkcí je koeficient určení přesně stejný jako čtverec korelačního koeficientu.

   Syntaxe tohoto příkazu je:

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

   Funkce má tedy dva operátory, z nichž jeden je seznam hodnot funkce a druhý z argumentů. Operátoři mohou být reprezentováni přímo ve formě hodnot uvedených středníkem ( ; ) a jako odkazy na rozsahy, kde se nacházejí. Je to druhá možnost, kterou v tomto příkladu použijeme.

   Nastavte kurzor do pole "Známé y" . Zajistíme levé tlačítko myši a vybereme obsah sloupce "Y" v tabulce. Jak můžete vidět, adresa zadaného datového pole se okamžitě zobrazí v okně.

   Podobně vyplníme pole "Známé hodnoty x" . V tomto poli umístíme kurzor, tentokrát však vybereme hodnoty sloupce "X" .

   Po zobrazení všech údajů v okně argumentu KVPIRSON klepněte na tlačítko "OK" umístěné na jeho spodní straně.

4. Jak je vidět, pak program vypočítá určující koeficient a vygeneruje výsledek do buňky přidělené před vyvoláním průvodce . V našem příkladu se hodnota vypočteného ukazatele ukázala být 1. To znamená, že prezentovaný model je naprosto spolehlivý, to znamená, že eliminuje chybu.

Lekce: Průvodce funkcí v aplikaci Microsoft Excel

Metoda 2: výpočet koeficientu určení v nelineárních funkcích

Ale výše uvedená varianta výpočtu požadované hodnoty může být použita pouze pro lineární funkce. Co můžeme udělat pro výpočet v nelineární funkci? V programu Excel je také taková příležitost. To lze provést pomocí nástroje "Regrese" , který je nedílnou součástí balíčku "Analýza dat" .

1. Ale před použitím specifikovaného nástroje byste měli aktivovat samotný balíček "Analysis" , který je ve výchozím nastavení zakázán v aplikaci Excel. Přejdeme na kartu "Soubor" a pak přejdeme na "Možnosti" .
2. V okně, které se otevře, přesučíme se do části "Doplňky" navigací levou vertikální nabídkou. Pole "Správa" se nachází v dolní části pravé části okna. Ze seznamu dostupných podsekcí vyberte název "Doplňky aplikace Excel ..." a potom klikněte na tlačítko "Přejít ..." napravo od pole.
3. Otevře se okno doplňku. Ve střední části je seznam dostupných doplňků. Zaškrtněte políčko vedle položky "Balíček analýzy" . Poté klikněte na tlačítko "OK" na pravé straně rozhraní okna.
4. Bude aktivován balíček nástrojů "Analýza dat" v aktuální instanci aplikace Excel. Přístup k němu je umístěn na pásu karet na kartě "Data" . Přesunutím na zadanou kartu a klepnutím na tlačítko "Analýza dat" ve skupině nastavení "Analýza" .
5. Okno "Analýza dat" je aktivováno se seznamem nástrojů pro zpracování profilových informací. Z tohoto seznamu vybereme položku "Regrese" a klikneme na tlačítko "OK" .
6. Pak se otevře okno Regresní nástroj. První blok nastavení je "Vstupní data" . Zde ve dvou polích musíte zadat adresy rozsahů, kde jsou umístěny hodnoty parametru a funkce. Kurzor umístěte do pole "Vstupní interval Y" a vyberte obsah sloupce "Y" na listu. Po zadání adresy pole v okně "Regrese" vložte kurzor do pole "Vstupní interval Y" a stejným způsobem vyberte buňky sloupce "X" .

   O parametrech "Label" a "Constant-zero" neurčujeme zaškrtávací políčka. Zaškrtávací políčko lze nastavit v blízkosti parametru "Úroveň spolehlivosti" av poli, které je na opačné straně, můžete zadat požadovanou hodnotu odpovídajícího indikátoru (ve výchozím nastavení 95%).

   Ve skupině "Výstupní parametry" musíte zadat, ve které oblasti se zobrazí výsledek výpočtu. Existují tři možnosti:

   • Plocha na aktuálním listu;
   • Další list;
   • Další kniha (nový soubor).

   Na první možnost se zastavíme, takže původní data a výsledek jsou umístěny na jeden pracovní list. Umístěte přepínač kolem parametru "Výstupní interval" . Na poli naproti této poloze umístíme kurzor. Klikneme levým tlačítkem myši na prázdný prvek na listu, který se má stát levou horní částí výstupní tabulky výsledků výpočtu. Adresa tohoto prvku by měla být zvýrazněna v poli "Regrese" .

   Skupiny parametrů "Zůstává" a "Normální pravděpodobnost" jsou ignorovány, protože nejsou důležité pro řešení úkolu. Poté klikněte na tlačítko "OK" , které se nachází v pravém horním rohu okna "Regrese" .

7. Program vypočítává na základě dříve zadaných dat a výsledky je výsledkem zadaného rozsahu. Jak můžete vidět, tento nástroj zobrazuje velké množství výsledků na různých parametrech na listu. Ale v souvislosti se současnou lekcí nás zajímá ukazatel "R-square" . V tomto případě je to 0,947664, které charakterizuje vybraný model jako model dobré kvality.

Metoda 3: Stanovení koeficientu trendové linie

Kromě výše uvedených možností může být koeficient určení zobrazen přímo pro trendovou čáru v grafu, který je založen na listu aplikace Excel. Zjistíme, jak to lze udělat konkrétním příkladem.

1. Máme graf na základě tabulky argumentů a hodnot funkce, která byla použita pro předchozí příklad. Postavme k tomu trendovou linii. Klepněte na libovolné místo v konstrukční oblasti, na které je graf umístěn levým tlačítkem myši. V takovém případě se na pásku objeví další sada karet - "Práce s diagramy" . Přejděte na kartu "Rozvržení" . Klepněte na tlačítko "Trend Line" , které se nachází v poli "Analysis" . Zobrazí se nabídka, ve které je uveden typ trendu. Zastavíme výběr na typu, který odpovídá konkrétnímu úkolu. Pro náš příklad si vybereme možnost "Exponenciální aproximace" .
2. Aplikace Excel vytváří trendovou přímku přímo na rovině vykreslení ve formě další černé křivky.
3. Nyní je naším úkolem ukázat skutečný koeficient určení. Klikněte pravým tlačítkem na trendovou čáru. Kontextové menu je aktivováno. Zastavíme výběr v položce "Trendline formát ..." .

   Chcete-li provést přechod do okna formátu trendové linky, můžete provést alternativní akci. Zvolte trendový řádek klepnutím na něj levým tlačítkem myši. Přejdeme na kartu "Rozvržení" . Klikneme na tlačítko "Trend Line" v bloku "Analysis" . V otevřeném seznamu klikněte na nejnovější položku v seznamu akcí - "Další parametry trendové linie ..." .

4. Po každé z výše uvedených akcí se spustí formátovací okno, ve kterém lze provést další nastavení. Zejména pro splnění našeho úkolu je třeba zaškrtnout políčko vedle "Umístěte hodnotu aproximace přesnosti (R ^ 2) na diagram" . Nachází se na spodním okraji okna. To znamená, že tímto způsobem zahrneme mapování určovacího koeficientu na konstrukční plochu. Pak nezapomeňte kliknout na tlačítko "Zavřít" ve spodní části aktuálního okna.
5. Hodnota spolehlivosti aproximace, tj. Hodnota určovacího koeficientu, bude zobrazena na listu v konstrukční oblasti. V tomto případě je tato hodnota, jak vidíme, 0,9242, která charakterizuje aproximaci, jako model dobré kvality.
6. Tímto způsobem můžete nastavit zobrazení koeficientu určení pro jakýkoli jiný typ trendu. Typ trendu můžete měnit pohybem tlačítka na pásu nebo kontextového menu v okně parametrů, jak je uvedeno výše. Poté můžete v samotném okně přepnout na jiný typ ve skupině "Trend Line" . Nezapomínáme například na to, že je zaškrtnuto políčko "Umístěte hodnotu aproximace přesnosti" na diagram . Po dokončení výše uvedených kroků klikněte na tlačítko Zavřít v pravém dolním rohu okna.
7. U lineárního typu má trendová řada již hodnotu spolehlivosti aproximace rovnající se 0,9477, která charakterizuje tento model, a je dokonce spolehlivější než trendová řada exponenciálního typu, kterou jsme považovali za dřívější.
8. Přepínání mezi různými typy trendových čar a porovnáním jejich spolehlivostních hodnot aproximace (koeficientu určení) lze nalézt variantu, jejíž model nejpřesněji popisuje prezentovaný graf. Varianta s nejvyšším ukazatelem určovacího koeficientu bude nejspolehlivější. Na jeho základě můžete sestavit nejpřesnější prognózu.

   Například pro náš případ se nám podařilo ze zkušenosti dokázat, že nejvyšší míra spolehlivosti má polynomiální druhovou řadu druhého stupně. Koeficient určení v tomto případě je 1. To znamená, že tento model je absolutně spolehlivý, což znamená úplné odstranění chyb.

   Zároveň však vůbec neznamená, že tento typ trendu je také nejspolehlivější pro další graf. Optimální volba typu trendové linie závisí na typu funkce, na jejímž základě byl graf vytvořen. Pokud uživatel nemá dostatečné znalosti k tomu, aby mohl "odhadnout" nejlepší volbu, pak jediný způsob, jak zjistit lepší předpověď, je porovnávat určovací koeficienty, jak bylo ukázáno v příkladu výše.

Čtěte také:
Vytváření trendové linie v aplikaci Excel
Přibližování v aplikaci Excel

V aplikaci Excel existují dvě hlavní možnosti výpočtu koeficientu určení: použití operátora KVPIRSON a použití nástroje "Regrese" z balíčku nástrojů "Analýza dat" . V tomto případě je první z těchto možností určena k použití pouze při zpracování lineární funkce a další volba může být použita téměř ve všech situacích. Navíc je možné zobrazit koeficient určování trendové linie grafů jako hodnotu spolehlivosti aproximace. Pomocí tohoto indikátoru lze určit typ trendové linie, která má nejvyšší úroveň spolehlivosti pro určitou funkci.

• ← Změna velikosti článků v aplikaci Microsoft Excel
• Stanovení koeficientu více koeficientů v MS Excel →