Párová lineární regrese v excelu. Regrese v Excelu

Regresní analýza je jednou z nejpopulárnějších metod statistického výzkumu. Lze jím určit míru vlivu nezávislých proměnných na závisle proměnnou. Ve funkčnosti Microsoft Excel Pro tento typ analýzy jsou k dispozici nástroje. Pojďme se podívat, jaké to jsou a jak je používat.

Připojení analytického balíčku

Abyste však mohli používat funkci, která vám umožňuje provádět regresní analýzu, musíte nejprve aktivovat analytický balíček. Teprve poté se na pásu karet Excelu objeví nástroje potřebné pro tento postup.

1. Přejděte na kartu "Soubor".
2. Přejděte do sekce "Nastavení".
3. Otevře se okno Možnosti aplikace Excel. Přejděte do podsekce "Doplňky".
4. V úplně spodní části okna, které se otevře, přeuspořádáme přepínač v bloku "Správa" do polohy "Doplňky Excelu", pokud je v jiné poloze. Klikněte na tlačítko "Go".
5. Otevře se okno doplňků aplikace Excel. Zaškrtněte políčko vedle položky „Analytický balíček“. Klikněte na tlačítko "OK".

Nyní, když přejdeme na kartu "Data", na pásu karet v bloku nástrojů "Analýza" uvidíme nové tlačítko - "Analýza dat".

Typy regresní analýzy

Existuje několik typů regresí:

• parabolický;
• Napájení;
• logaritmický;
• exponenciální;
• demonstrace;
• hyperbolický;
• lineární regrese.

O provedení posledního pohledu regresní analýza O Excelu si povíme více později.

Lineární regrese v Excelu

Níže je jako příklad uvedena tabulka, která ukazuje průměrnou denní teplotu vzduchu na ulici a počet zákazníků prodejny pro odpovídající pracovní den. Pojďme pomocí regresní analýzy zjistit, jak přesně mohou povětrnostní podmínky v podobě teploty vzduchu ovlivnit návštěvnost maloobchodní provozovny.

Obecná rovnice lineární regrese vypadá takto: Y = a0 + a1x1 + ... + axk. V tomto vzorci znamená Y proměnnou, na kterou se snažíme zkoumat vliv faktorů. V našem případě se jedná o počet kupujících. Hodnota x je různé faktory které ovlivňují proměnnou. Parametry a jsou regresní koeficienty. To znamená, že určují význam konkrétního faktoru. Index k označuje celkový počet těchto stejných faktorů.

Analýza výsledků analýzy

Výsledky regresní analýzy se zobrazí ve formě tabulky na místě určeném v nastavení.

Jedním z hlavních ukazatelů je R-kvadrát. Označuje kvalitu modelu. V našem případě daný koeficient je 0,705 nebo přibližně 70,5 %. To je přijatelná úroveň kvality. Vztah menší než 0,5 je špatný.

Další důležitý ukazatel se nachází v buňce na průsečíku čáry "Y-průsečík" a sloupce "Koeficienty". Zde je uvedeno, jakou hodnotu bude mít Y, a v našem případě je to počet kupujících, přičemž všechny ostatní faktory jsou rovné nule. V této tabulce daná hodnota rovná se 58,04.

Hodnota na průsečíku sloupce „Proměnná X1“ a „Koeficienty“ ukazuje míru závislosti Y na X. V našem případě se jedná o úroveň závislosti počtu zákazníků prodejny na teplotě. Koeficient 1,31 je považován za docela dostačující vysoké hodnocení vliv.

Jak vidíte, je poměrně snadné vytvořit tabulku regresní analýzy pomocí aplikace Microsoft Excel. S daty získanými na výstupu však může pracovat pouze vyškolený člověk a pochopit jejich podstatu.

Jsme rádi, že jsme vám mohli pomoci problém vyřešit.

Zeptejte se v komentářích a podrobně popište podstatu problému. Naši odborníci se pokusí odpovědět co nejrychleji.

Pomohl vám tento článek?

Metoda lineární regrese nám umožňuje popsat přímku, která nejlépe odpovídá řadě uspořádaných dvojic (x, y). Rovnice pro přímku, známá jako lineární rovnice, je uvedena níže:

ŷ je očekávaná hodnota y pro danou hodnotu x,

x - nezávislá proměnná,

a - segment na ose y pro přímku,

b je sklon přímky.

Na obrázku níže je tento koncept znázorněn graficky:

Obrázek výše ukazuje přímku popsanou rovnicí ŷ =2+0,5x. Úsek na ose y je průsečík přímky s osou y; v našem případě a = 2. Sklon úsečky b, poměr stoupání úsečky k délce úsečky, má hodnotu 0,5. Kladný sklon znamená, že čára stoupá zleva doprava. Pokud b = 0, čára je vodorovná, což znamená, že mezi závislými a nezávislými proměnnými není žádný vztah. Jinými slovy, změna hodnoty x neovlivní hodnotu y.

ŷ a y se často zaměňují. V grafu je zobrazeno 6 uspořádaných dvojic bodů a přímka podle dané rovnice

Tento obrázek ukazuje bod odpovídající uspořádané dvojici x = 2 a y = 4. Všimněte si, že očekávaná hodnota y podle přímky u X= 2 je ŷ. Můžeme to potvrdit následující rovnicí:

ŷ = 2 + 0,5х =2 +0,5(2) =3.

Hodnota y je skutečný bod a hodnota ŷ je očekávaná hodnota y při použití lineární rovnice pro danou hodnotu x.

Dalším krokem je určení lineární rovnice, která maximálně odpovídá množině uspořádaných dvojic, o tom jsme hovořili v minulém článku, kde jsme určovali tvar rovnice pomocí metody nejmenší čtverce.

Použití Excelu k definici lineární regrese

Chcete-li použít nástroj regresní analýzy zabudovaný do Excelu, musíte aktivovat doplněk Balíček analýzy. Najdete ji kliknutím na záložku Soubor –> Možnosti(2007+), v dialogovém okně, které se objeví Možnostivynikat přejděte na kartu Doplňky. V terénu Řízení Vybrat doplňkyvynikat a klikněte Jít. V okně, které se zobrazí, zaškrtněte políčko vedle analytický balíček, klikněte OK.

V záložce Data ve skupině Analýza objeví se nové tlačítko Analýza dat.

Abychom demonstrovali, jak doplněk funguje, použijme data z předchozího článku, kde chlap a dívka sdílejí stůl v koupelně. Zadejte údaje pro náš příklad koupelny do sloupců A a B prázdného listu.

Přejděte na kartu Data, ve skupině Analýza klikněte Analýza dat. V okně, které se objeví Analýza dat vybrat Regrese jak je znázorněno na obrázku a klikněte na OK.

V okně nastavte požadované parametry regrese Regrese, jak je vidět na obrázku:

Klikněte OK. Níže uvedený obrázek ukazuje získané výsledky:

Tyto výsledky jsou v souladu s těmi, které jsme získali nezávislými výpočty v předchozím článku.

Regresní analýza je statistická výzkumná metoda, která umožňuje ukázat závislost parametru na jedné nebo více nezávislých proměnných. V předpočítačové době bylo jeho použití poměrně obtížné, zvláště když šlo o velké objemy dat. Dnes, když jste se naučili, jak vytvořit regresi v aplikaci Excel, můžete vyřešit složité statistické problémy během několika minut. Níže jsou uvedeny konkrétní příklady z oboru ekonomie.

Typy regrese

Samotný koncept zavedl do matematiky Francis Galton v roce 1886. Regrese se děje:

• lineární;
• parabolický;
• Napájení;
• exponenciální;
• hyperbolický;
• demonstrativní;
• logaritmický.

Příklad 1

Zvažte problém stanovení závislosti počtu členů týmu v důchodu na průměrné mzdě v 6 průmyslových podnicích.

Úkol. U šesti podniků jsme analyzovali průměrnou měsíční mzdu a počet zaměstnanců, kteří odešli z důvodu vlastní vůle. V tabulkové podobě máme:

Pro problém stanovení závislosti počtu propuštěných pracovníků na průměrné mzdě v 6 podnicích má regresní model tvar rovnice Y = a0 + a1 × 1 + ... + akxk, kde хi jsou ovlivňující proměnné, ai jsou regresní koeficienty a k je počet faktorů.

U tohoto úkolu je Y ukazatel zaměstnanců, kteří odešli, a ovlivňujícím faktorem je mzda, kterou označujeme X.

Použití možností tabulky "Excel"

Regresní analýze v Excelu musí předcházet aplikace vestavěných funkcí na dostupná tabulková data. Pro tyto účely je však lepší použít velmi užitečný doplněk „Analysis Toolkit“. K jeho aktivaci potřebujete:

• na kartě "Soubor" přejděte do části "Možnosti";
• v okně, které se otevře, vyberte řádek "Doplňky";
• klikněte na tlačítko "Přejít" umístěné dole, napravo od řádku "Správa";
• zaškrtněte políčko vedle názvu „Analytický balíček“ a potvrďte své akce kliknutím na „OK“.

Pokud je vše provedeno správně, zobrazí se požadované tlačítko na pravé straně karty Data, která se nachází nad pracovním listem aplikace Excel.

Lineární regrese v Excelu

Nyní, když máme po ruce všechny potřebné virtuální nástroje pro provádění ekonometrických výpočtů, můžeme začít řešit náš problém. Pro tohle:

• klikněte na tlačítko "Analýza dat";
• v okně, které se otevře, klikněte na tlačítko "Regrese";
• na zobrazené kartě zadejte rozsah hodnot pro Y (počet zaměstnanců, kteří odešli) a pro X (jejich platy);
• Naše akce potvrdíme stisknutím tlačítka "Ok".

Výsledkem je, že program automaticky vyplní nový list tabulky daty regresní analýzy. Poznámka! Excel má možnost ručně nastavit umístění, které pro tento účel preferujete. Může to být například stejný list, kde jsou hodnoty Y a X, nebo dokonce Nová kniha, speciálně navržený pro ukládání takových dat.

Analýza výsledků regrese pro R-kvadrát

V Excelu vypadají data získaná při zpracování dat uvažovaného příkladu takto:

Nejprve byste měli věnovat pozornost hodnotě R-čtverce. Je to koeficient determinace. V tomto příkladu R-kvadrát = 0,755 (75,5 %), tj. vypočtené parametry modelu vysvětlují vztah mezi uvažovanými parametry o 75,5 %. Čím vyšší je hodnota koeficientu determinace, tím vhodnější je zvolený model pro konkrétní úlohu. Předpokládá se, že správně popisuje skutečnou situaci s hodnotou R-squared nad 0,8. Pokud je R-kvadrát tcr, pak je hypotéza o nevýznamnosti volného členu lineární rovnice zamítnuta.

V uvažované úloze pro volný člen bylo pomocí nástrojů Excelu získáno, že t = 169,20903 a p = 2,89E-12, tj. máme nulovou pravděpodobnost, že správná hypotéza o nevýznamnosti volného členu bude odmítl. Pro koeficient při neznámé hodnotě t=5,79405 a p=0,001158. Jinými slovy, pravděpodobnost, že bude zamítnuta správná hypotéza o nevýznamnosti koeficientu pro neznámou, je 0,12 %.

Lze tedy tvrdit, že výsledná lineární regresní rovnice je adekvátní.

Problém účelnosti nákupu balíku akcií

Vícenásobná regrese v Excelu se provádí pomocí stejného nástroje pro analýzu dat. Zvažte konkrétní aplikovaný problém.

Vedení NNN musí rozhodnout o vhodnosti nákupu 20% podílu v MMM SA. Cena balíčku (JV) je 70 milionů amerických dolarů. Specialisté NNN shromáždili data o podobných transakcích. Bylo rozhodnuto ohodnotit hodnotu balíku akcií podle takových parametrů, vyjádřených v milionech amerických dolarů, jako:

• závazky (VK);
• roční obrat (VO);
• pohledávky (VD);
• náklady na dlouhodobý majetek (SOF).

Kromě toho se používá parametr nedoplatky mezd podniku (V3 P) v tisících amerických dolarů.

Řešení pomocí tabulky Excel

Nejprve je třeba vytvořit tabulku počátečních dat. Vypadá to takto:

• vyvolejte okno "Analýza dat";
• vyberte sekci "Regrese";
• do pole "Interval vstupu Y" zadejte rozsah hodnot závislých proměnných ze sloupce G;
• klikněte na ikonu s červenou šipkou vpravo od pole "Interval vstupu X" a vyberte na listu rozsah všech hodnot od sloupce B,C, D, F.

Vyberte „Nový list“ a klikněte na „OK“.

Získejte regresní analýzu pro daný problém.

Přezkoumání výsledků a závěrů

„Shromažďujeme“ ze zaokrouhlených údajů uvedených výše v tabulkovém procesoru Excel, regresní rovnice:

SP \u003d 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

Ve známější matematické formě to lze napsat jako:

y = 0,103*x1 + 0,541*x2 - 0,031*x3 +0,405*x4 +0,691*x5 - 265,844

Údaje pro JSC "MMM" jsou uvedeny v tabulce:

Dosadíme-li je do regresní rovnice, dostanou číslo 64,72 milionů amerických dolarů. To znamená, že akcie JSC MMM by se neměly kupovat, protože jejich hodnota 70 milionů amerických dolarů je poněkud nadhodnocená.

Jak vidíte, použití excelové tabulky a regresní rovnice umožnilo učinit informované rozhodnutí o proveditelnosti velmi specifické transakce.

Nyní víte, co je regrese. Výše uvedené příklady v Excelu vám pomohou vyřešit praktické problémy z oblasti ekonometrie.

Je známo, že je užitečný v různých oblastech činnosti, včetně takové disciplíny, jako je ekonometrie, kde se tento softwarový nástroj používá v práci. V zásadě jsou všechny akce praktických a laboratorních cvičení prováděny v Excelu, což značně usnadňuje práci a poskytuje podrobné vysvětlení určitých akcí. Takže jeden z analytických nástrojů "Regrese" se používá k výběru grafu pro sadu pozorování pomocí metody nejmenších čtverců. Podívejme se, jaký je tento nástroj programu a jaký je jeho přínos pro uživatele. Níže je také stručný, ale srozumitelný návod na sestavení regresního modelu.

Hlavní úkoly a typy regrese

Regrese je vztah mezi danými proměnnými, díky kterému je možné určit předpověď budoucího chování těchto proměnných. Proměnné jsou různé periodické jevy, včetně lidského chování. Tato analýza Excel se používá k analýze dopadu hodnot jedné nebo více proměnných na konkrétní závislou proměnnou. Například prodej v obchodě je ovlivněn několika faktory, včetně sortimentu, cen a umístění obchodu. Pomocí regrese v Excelu můžete určit míru vlivu každého z těchto faktorů na základě výsledků stávajících prodejů a získaná data pak aplikovat na prognózu prodejů na další měsíc nebo pro jiný obchod umístěný poblíž.

Typicky je regrese prezentována jako jednoduchá rovnice, která odhaluje závislosti a sílu asociace mezi dvěma skupinami proměnných, kde jedna skupina je závislá nebo endogenní a druhá je nezávislá nebo exogenní. Pokud existuje skupina vzájemně souvisejících ukazatelů, závislá proměnná Y je určena na základě logiky uvažování a zbytek funguje jako nezávislé X-proměnné.

Hlavní úkoly vytváření regresního modelu jsou následující:

1. Výběr významných nezávislých proměnných (Х1, Х2, …, Xk).
2. Výběr typu funkce.
3. Konstrukce odhadů pro koeficienty.
4. Budova intervaly spolehlivosti a regresní funkce.
5. Kontrola významnosti vypočítaných odhadů a sestrojené regresní rovnice.

Existuje několik typů regresní analýzy:

• párová (1 závislá a 1 nezávislá proměnná);
• násobek (několik nezávislých proměnných).

Existují dva typy regresních rovnic:

1. Lineární, ilustrující striktní lineární vztah mezi proměnnými.
2. Nelineární – rovnice, které mohou zahrnovat mocniny, zlomky a goniometrické funkce.

Návod na stavbu modelu

Chcete-li provést danou konstrukci v aplikaci Excel, musíte postupovat podle pokynů:

Pro další výpočet by měla být použita funkce "Linear()", která specifikuje Y Values, X Values, Const a statistiky. Poté pomocí funkce "Trend" určete množinu bodů na regresní přímce - Y-values, X-values, New values, Const. Pomocí daných parametrů vypočítejte neznámou hodnotu koeficientů na základě daných podmínek úlohy.

Ukazuje vliv některých hodnot (nezávislých, nezávislých) na závisle proměnnou. Například, jak závisí počet ekonomicky aktivního obyvatelstva na počtu podniků, mzdách a dalších parametrech. Nebo: jak zahraniční investice, ceny energií atd. ovlivňují výši HDP.

Výsledek analýzy umožňuje stanovit priority. A na základě hlavních faktorů předvídat, plánovat rozvoj prioritních oblastí, činit manažerská rozhodnutí.

Regrese se děje:

lineární (y = a + bx);

parabolický (y = a + bx + cx 2);

exponenciální (y = a * exp(bx));

Mocnina (y = a*x^b);

hyperbolický (y = b/x + a);

logaritmické (y = b * ln(x) + a);

exponenciální (y = a * b^x).

Zvažte příklad vytvoření regresního modelu v Excelu a interpretaci výsledků. Vezměme si lineární typ regrese.

Úkol. U 6 podniků průměr měsíčně mzda a počet zaměstnanců v důchodu. Je třeba určit závislost počtu zaměstnanců v důchodu na průměrné mzdě.

Lineární regresní model má následující podobu:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

Kde a jsou regresní koeficienty, x jsou ovlivňující proměnné a k je počet faktorů.

V našem příkladu je Y indikátorem odchodu pracovníků. Ovlivňujícím faktorem jsou mzdy (x).

Excel má vestavěné funkce, které lze použít k výpočtu parametrů lineárního regresního modelu. Ale doplněk Analysis ToolPak to udělá rychleji.

Aktivujte si výkonný analytický nástroj:

1. Klikněte na tlačítko "Office" a přejděte na záložku "Excel Options". "Doplňky".

2. Níže pod rozevíracím seznamem v poli „Správa“ bude nápis „Doplňky aplikace Excel“ (pokud tam není, klikněte na zaškrtávací políčko vpravo a vyberte). A tlačítko Go. Klikněte.

3. Otevře se seznam dostupných doplňků. Vyberte "Analytický balíček" a klikněte na OK.

Po aktivaci bude doplněk dostupný na kartě Data.

Nyní se budeme zabývat přímo regresní analýzou.

1. Otevřete nabídku nástroje Analýza dat. Vyberte "Regrese".

2. Otevře se nabídka pro výběr vstupních hodnot a možností výstupu (kde zobrazit výsledek). V polích pro výchozí údaje uvádíme rozsah popisovaného parametru (Y) a faktor, který jej ovlivňuje (X). Zbytek může a nemusí být dokončen.

3. Po kliknutí na OK program zobrazí výpočty na novém listu (můžete zvolit interval zobrazení na aktuálním listu nebo přiřadit výstup do nového sešitu).

V první řadě věnujeme pozornost R-kvadrátu a koeficientům.

R-kvadrát je koeficient determinace. V našem příkladu je to 0,755 nebo 75,5 %. To znamená, že vypočtené parametry modelu vysvětlují vztah mezi studovanými parametry ze 75,5 %. Čím vyšší je koeficient determinace, tím lepší model. Dobré - nad 0,8. Špatná - méně než 0,5 (takovou analýzu lze stěží považovat za rozumnou). V našem příkladu - "není špatné".

Koeficient 64,1428 ukazuje, jaké bude Y, pokud se všechny proměnné v uvažovaném modelu rovnají 0. To znamená, že hodnotu analyzovaného parametru ovlivňují i ​​další faktory, které nejsou v modelu popsány.

Koeficient -0,16285 ukazuje váhu proměnné X na Y. To znamená, že průměrná měsíční mzda v rámci tohoto modelu ovlivňuje počet odcházejících s váhou -0,16285 (to je malá míra vlivu). Znak „-“ označuje negativní dopad: čím vyšší plat, tím méně odvykání. Což je spravedlivé.

Pomocí doplňku lze také provádět statistické zpracování dat BALÍČEK ANALÝZY(obr. 62).

Z navrhovaných položek vyberte položku " REGRESE“ a klikněte na něj levým tlačítkem myši. Dále klikněte na OK.

Okno zobrazené na Obr. 63.

Analytický nástroj « REGRESE» se používá k přizpůsobení grafu množině pozorování pomocí metody nejmenších čtverců. Regrese se používá k analýze vlivu hodnot jedné nebo více nezávislých proměnných na jednu závislou proměnnou. Například sportovní výkon sportovce je ovlivněn několika faktory, včetně věku, výšky a hmotnosti. Je možné vypočítat míru vlivu každého z těchto tří faktorů na výkon sportovce, a následně získaná data použít k predikci výkonu jiného sportovce.

Tuto funkci používá nástroj Regrese LINEST.

Dialogové okno REGRESS

Štítky Zaškrtněte toto políčko, pokud první řádek nebo první sloupec vstupního rozsahu obsahuje názvy. Pokud zde nejsou žádná záhlaví, zrušte zaškrtnutí tohoto políčka. V tomto případě se automaticky vygenerují vhodné hlavičky pro data výstupní tabulky.

Úroveň spolehlivosti Zaškrtnutím tohoto políčka zahrnete do tabulky součtů výstupů další úroveň. Do příslušného pole zadejte úroveň spolehlivosti, kterou chcete použít, kromě výchozí úrovně spolehlivosti 95 %.

Konstanta - nula Zaškrtněte políčko, aby regresní přímka procházela počátkem.

Výstupní rozsah Zadejte odkaz na levou horní buňku výstupního rozsahu. Pro výstupní tabulku výsledků přidělte alespoň sedm sloupců, které budou obsahovat: výsledky analýzy rozptylu, koeficienty, směrodatnou chybu výpočtu Y, směrodatné odchylky, počet pozorování, směrodatné chyby koeficientů.

Nový list Zaškrtnutím tohoto políčka otevřete nový list v sešitu a vložíte výsledky analýzy počínaje buňkou A1. V případě potřeby zadejte název nového listu do pole naproti příslušné pozici přepínače.

Nový sešit Zaškrtnutím tohoto políčka vytvoříte nový sešit, ve kterém budou výsledky přidány do nového listu.

Zbytky Zaškrtnutím tohoto políčka zahrnete zbytky do výstupní tabulky.

Standardizované rezidua Zaškrtnutím tohoto políčka zahrnete standardizovaná rezidua do výstupní tabulky.

Graf reziduí Zaškrtnutím tohoto políčka vykreslíte rezidua pro každou nezávislou proměnnou.

Přizpůsobit graf Zaškrtnutím tohoto políčka zobrazíte předpokládané hodnoty oproti pozorovaným hodnotám.

Normální pravděpodobnostní graf Zaškrtnutím políčka zobrazíte normální pravděpodobnost.

Funkce LINEST

Pro provedení výpočtů vybereme kurzorem buňku, ve které chceme zobrazit průměrnou hodnotu, a stiskneme klávesu = na klávesnici. Dále v poli Název zadejte například požadovanou funkci PRŮMĚRNÝ(obr. 22).

Funkce LINEST vypočítá statistiku pro řadu pomocí metody nejmenších čtverců k výpočtu přímky, která nejlépe aproximuje dostupná data, a poté vrátí pole, které popisuje výslednou přímku. Funkci můžete také kombinovat LINEST s dalšími funkcemi pro výpočet jiných druhů modelů, které jsou lineární v neznámých parametrech (jejichž neznámé parametry jsou lineární), včetně polynomiálních, logaritmických, exponenciálních a mocninných řad. Protože je vráceno pole hodnot, musí být funkce zadána jako maticový vzorec.

Rovnice pro přímku je:

y=m 1 x 1 +m 2 x 2 +…+b (v případě několika rozsahů hodnot x),

kde závislá hodnota y je funkcí nezávislé hodnoty x, hodnoty m jsou koeficienty odpovídající každé nezávisle proměnné x a b je konstanta. Všimněte si, že y, x a m mohou být vektory. Funkce LINEST vrátí pole (mn;mn-1;…;m 1 ;b). LINEST může také vrátit další regresní statistiky.

LINEST(známé_y-hodnoty; známé_x-hodnoty; konst; statistiky)

Známé_y hodnoty - sada hodnot y, které jsou již známé pro vztah y=mx+b.

Pokud má pole známé_y jeden sloupec, pak je každý sloupec pole pole známé_x interpretován jako samostatná proměnná.

Pokud má pole známé_y jeden řádek, pak je každý řádek pole pole známé_x interpretován jako samostatná proměnná.

Známé_x hodnoty - volitelná sada hodnot x, které jsou již známé pro vztah y=mx+b.

Pole známe_x může obsahovat jednu nebo více sad proměnných. Pokud je použita pouze jedna proměnná, pak pole_známé_y_hodnoty a známé_x_hodnoty mohou mít jakýkoli tvar – pokud mají stejný rozměr. Je-li použito více než jedna proměnná, pak musí být známý_y vektor (tj. jeden řádek vysoký nebo jeden sloupec široký).

Pokud je pole_známé_x vynecháno, předpokládá se, že toto pole (1;2;3;...) má stejnou velikost jako pole_známé_y.

Const je booleovská hodnota, která určuje, zda má být konstanta b 0.

Pokud je argument "const" PRAVDA nebo je vynechán, pak se konstanta b vyhodnotí normálně.

Pokud je argument "const" NEPRAVDA, pak se předpokládá, že hodnota b je 0 a hodnoty m jsou vybrány tak, aby byl splněn vztah y=mx.

Statistika je logická hodnota, která označuje, zda mají být vráceny další regresní statistiky.

Pokud je statistika PRAVDA, funkce LINREGRESE vrátí další regresní statistiku. Vrácené pole bude vypadat takto: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

Pokud je statistika NEPRAVDA nebo je vynechána, funkce LINREGRESE vrátí pouze koeficienty ma konstantu b.

Další regresní statistiky. (Tabulka 17)

Hodnota	Popis
se1,se2,...,sen	Standardní chybové hodnoty pro koeficienty m1,m2,...,mn.
seb	Standardní hodnota chyby pro konstantu b (seb = #N/A, pokud je argument 'const' NEPRAVDA).
r2	Faktor determinace. Skutečné hodnoty y se porovnávají s hodnotami získanými z rovnice přímky; na základě výsledků srovnání je vypočítán koeficient determinismu normalizovaný od 0 do 1. Pokud je roven 1, pak existuje úplná korelace s modelem, tj. mezi skutečnými a odhadovanými hodnotami není žádný rozdíl ​z r. V opačném případě, pokud je koeficient determinismu 0, nemá smysl používat regresní rovnici k predikci hodnot y. Pro získání dodatečné informace způsoby výpočtu r2 viz "Poznámky" na konci této části.
sey	Standardní chyba pro odhad y.
F	F-statistika nebo F-pozorovaná hodnota. F statistika se používá k určení, zda je pozorovaný vztah mezi závislými a nezávislými proměnnými náhodný.
df	Stupně svobody. Stupně volnosti jsou užitečné pro nalezení F-kritických hodnot ve statistické tabulce. Chcete-li určit úroveň spolehlivosti modelu, musíte porovnat hodnoty v tabulce s F-statistikou vrácenou funkcí LINREGRESE. Další informace o výpočtu df naleznete v části „Poznámky“ na konci této části. Příklad 4 níže ukazuje použití F a df.
ssreg	Regresní součet čtverců.
ssresid	Zbytkový součet čtverců. Další informace o výpočtu ssreg a ssresid naleznete v části „Poznámky“ na konci této části.

Obrázek níže ukazuje pořadí, ve kterém jsou vráceny další regresní statistiky (Obrázek 64).

Poznámky:

Jakákoli přímka může být popsána jejím sklonem a průsečíkem s osou y:

Sklon (m): pro určení sklonu přímky, obvykle označované m, je třeba vzít dva body na přímce (x 1 ,y 1) a (x 2 ,y 2); sklon bude roven (y 2 -y 1) / (x 2 -x 1).

Průsečík Y (b): Průsečík čáry y, obvykle označovaný b, je hodnota y pro bod, kde přímka protíná osu y.

Rovnice přímky má tvar y=mx+b. Pokud jsou známy hodnoty m a b, lze jakýkoli bod na přímce vypočítat dosazením hodnot y nebo x do rovnice. Můžete také použít funkci TREND.

Pokud existuje pouze jedna nezávislá proměnná x, můžete získat sklon a průsečík y přímo pomocí následujících vzorců:

Sklon: INDEX(LINEST(známé_y, známé_x); 1)

Intercept Y: INDEX(LINEST(známé_y, známé_x); 2)

Přesnost aproximace pomocí přímky vypočítané funkcí LINREGRESE závisí na míře rozptylu dat. Čím blíže jsou data k přímce, tím přesnější je model použitý funkcí LINREGRESE. Funkce LINREGRESE používá k určení nejlepšího přizpůsobení datům metodu nejmenších čtverců. Pokud existuje pouze jedna nezávislá proměnná x, m a b se vypočítají pomocí následujících vzorců:

kde x a y jsou průměry vzorku, například x = AVERAGE(známé_x) a y = AVERAGE(známé_y).

Funkce LINREGRESE a LGRFPRIBL mohou vypočítat přímou nebo exponenciální křivku, která nejlépe odpovídá datům. Neodpovídají však na otázku, který z obou výsledků je pro řešení problému vhodnější. Můžete také vypočítat funkci TREND(známé_y-hodnoty; známé_x-hodnoty) pro přímku nebo funkci RŮST (známé_y-hodnoty; známé_x-hodnoty) pro exponenciální křivku. Tyto funkce, pokud jsou vynechány z argumentu new_x_values ​​​​, vrátí pole vypočtených hodnot y pro skutečné hodnoty x podle přímky nebo křivky. Poté můžete porovnat vypočítané hodnoty se skutečnými hodnotami. Můžete také vytvářet grafy pro vizuální srovnání.

Při provádění regresní analýzy Microsoft Excel vypočítá pro každý bod druhou mocninu rozdílu mezi předpokládanou hodnotou y a skutečnou hodnotou y. Součet těchto čtverců rozdílů se nazývá zbytkový součet čtverců (ssresid). Microsoft Excel pak vypočítá celkový součet čtverců (sstotal). Pokud const = TRUE nebo pokud tento argument není zadán, celkový součet čtverců se bude rovnat součtu čtverců rozdílů skutečných hodnot y a středních hodnot y. Pokud const = FALSE, bude součet čtverců roven součtu druhých mocnin skutečných hodnot y (bez odečtení střední hodnoty y od podílu y). Poté lze regresní součet čtverců vypočítat následovně: ssreg = sstotal - ssresid. Čím menší je zbytkový součet čtverců, tím větší je hodnota koeficientu determinismu r2, což ukazuje, jak dobře rovnice získaná pomocí regresní analýzy vysvětluje vztahy mezi proměnnými. Koeficient r2 se rovná ssreg/sstotal.

V některých případech jeden nebo více sloupců X (za předpokladu, že hodnoty Y a X jsou ve sloupcích) nemá další prediktivní hodnotu v jiných sloupcích X. Jinými slovy, odstranění jednoho nebo více sloupců X může vést k hodnotám Y vypočítané se stejnou přesností. V tomto případě budou z regresního modelu vyloučeny nadbytečné sloupce X. Tento jev se nazývá "kolinearita", protože redundantní sloupce X mohou být reprezentovány jako součet několika neredundantních sloupců. Funkce LINREGRESE zkontroluje kolinearitu a pokud nějaké najde, odstraní z regresního modelu všechny nadbytečné sloupce X. Odebrané sloupce X lze ve výstupu funkce LINREGRESE identifikovat faktorem 0 a hodnotou se 0. Odstranění jednoho nebo více sloupců jako nadbytečných změní hodnotu df, protože závisí na počtu X sloupců skutečně použitých pro prediktivní účely. Další podrobnosti o výpočtu df viz příklad 4 níže. Když se df změní kvůli odstranění nadbytečných sloupců, změní se také hodnoty sey a F. Často se nedoporučuje používat kolinearitu. Mělo by se však použít, pokud některé sloupce X obsahují 0 nebo 1 jako indikátor, který označuje, zda je předmět experimentu v samostatné skupině. Pokud const = TRUE nebo pokud tento argument není zadán, funkce LINREGRESE vloží další sloupec X pro simulaci průsečíku. Pokud existuje sloupec s hodnotami 1 pro muže a 0 pro ženy a existuje sloupec s hodnotami 1 pro ženy a 0 pro muže, pak je poslední sloupec odstraněn, protože jeho hodnoty lze získat z sloupec "mužský ukazatel".

Výpočet df pro případy, kdy X sloupců není z modelu odstraněno kvůli kolinearitě, je následující: pokud existuje k známých_x sloupců a const = TRUE nebo není zadáno, pak df = n - k - 1. Pokud const = FALSE, pak df = n -k. V obou případech odstranění X sloupců kvůli kolinearitě zvýší hodnotu df o 1.

Vzorce, které vracejí pole, musí být zadány jako maticové vzorce.

Při zadávání pole konstant jako argumentu známé_x_hodnoty použijte například středník k oddělení hodnot na stejném řádku a dvojtečku k oddělení řádků. Oddělovací znaky se mohou lišit v závislosti na nastavení v okně "Jazyk a standardy" na ovládacím panelu.

Všimněte si, že hodnoty y předpovězené regresní rovnicí nemusí být správné, pokud jsou mimo rozsah hodnot y, které byly použity k definování rovnice.

Hlavní algoritmus použitý ve funkci LINEST, se liší od hlavního algoritmu funkcí NAKÝNIT A ÚSEČKA. Rozdíly mezi algoritmy mohou vést k různým výsledkům pro nejistá a kolineární data. Pokud jsou například datové body argumentu známé_y 0 a datové body argumentu známé_x jsou 1, pak:

Funkce LINEST vrátí hodnotu rovnou 0. Algoritmus funkce LINEST se používá k vrácení vhodných hodnot pro kolineární data, v takovém případě lze nalézt alespoň jednu odpověď.

Funkce SLOPE a INTERCEPT vrátí chybu #DIV/0!. Algoritmus funkcí SLOPE a INTERCEPT se používá k nalezení pouze jedné odpovědi, v tomto případě jich může být několik.

Kromě výpočtu statistik pro jiné typy regrese lze funkci LINREGRESE použít k výpočtu rozsahů pro jiné typy regrese zadáním funkcí proměnných x a y jako řady proměnných x a y pro funkci LINREGRESE. Například následující vzorec:

LINEST(y-hodnoty, x-hodnoty^COLUMN($A:$C))

pracuje s jedním sloupcem hodnot Y a jedním sloupcem hodnot X pro výpočet aproximace krychle (polynom 3. stupně) následujícího tvaru:

y=m1x+m2x2+m3x3+b

Vzorec lze upravit pro výpočty jiných typů regrese, ale v některých případech jsou nutné úpravy výstupních hodnot a další statistiky.

Balíček MS Excel vám umožňuje provést většinu práce velmi rychle při konstrukci lineární regresní rovnice. Je důležité pochopit, jak interpretovat výsledky.

K práci vyžaduje doplněk Balíček analýzy, který musí být povolen v položce nabídky Služba\Doplňky

Chcete-li v aplikaci Excel 2007 povolit Analysis Pack, klikněte na Přejít na blok Možnosti aplikace Excel kliknutím na tlačítko v levém horním rohu a poté na „ Možnosti aplikace Excel» ve spodní části okna:

Chcete-li sestavit regresní model, vyberte položku Služba\Analýza dat\Regrese. (V Excelu 2007 je tento režim v Data/analýza dat/regrese). Zobrazí se dialogové okno, které je třeba vyplnit:

1) Interval vstupu Y¾ obsahuje odkaz na buňky, které obsahují hodnoty výsledného atributu y. Hodnoty musí být ve sloupci;

2) Interval vstupu X¾ obsahuje odkaz na buňky, které obsahují hodnoty faktorů. Hodnoty musí být ve sloupcích;

3) Podepsat Tagy nastavit, pokud první buňky obsahují vysvětlující text (označení dat);

4) Úroveň spolehlivosti¾ je úroveň spolehlivosti, která je standardně považována za 95 %. Pokud vám tato hodnota nevyhovuje, musíte tuto funkci povolit a zadat požadovanou hodnotu;

5) Podepsat Nulová konstanta je zahrnut, pokud je nutné sestavit rovnici, ve které je volná proměnná ;

6) Možnosti výstupu určit, kam mají být výsledky umístěny. Výchozí režim sestavení Nový pracovní list;

7) Blokovat Zůstává umožňuje zahrnout výstup reziduí a konstrukci jejich grafů.

Výsledkem jsou informace obsahující vše nezbytné informace a seskupeny do tří bloků: Regresní statistika, Analýza rozptylu , Výběr zůstatku. Zvažme je podrobněji.

1. Regresní statistika:

násobek R je definován vzorcem ( Pearsonův korelační koeficient);

R (koeficient determinace);

Normalizované R-čtverec se vypočítá podle vzorce (používá se pro vícenásobná regrese);

standardní chyba S vypočítané podle vzorce ;

Pozorování ¾ je množství dat n.

2. Analýza rozptylu, řádek Regrese:

Parametr df rovná se m(počet sad faktorů X);

Parametr SS je určeno vzorcem ;

Parametr SLEČNA je určeno vzorcem ;

Statistika F je určeno vzorcem ;

Význam F. Pokud výsledné číslo překročí , pak je hypotéza přijata (není lineární vztah), v opačném případě je hypotéza přijata (existuje lineární vztah).

3. Analýza rozptylu, řádek Zbytek:

Parametr df rovná se ;

Parametr SS je určeno vzorcem ;

Parametr SLEČNA je určeno vzorcem .

4. Analýza rozptylu, řádek Celkový obsahuje součet prvních dvou sloupců.

5. Analýza rozptylu, řádek Y-křižovatka obsahuje hodnotu koeficientu , směrodatnou chybu a t-statistika.

P-hodnota ¾ je hodnota hladin významnosti odpovídající vypočtené t- statistici. Určeno STUDISTEM ( t-statistika; ). Li P-hodnota překročí , pak je odpovídající proměnná statisticky nevýznamná a lze ji z modelu vyloučit.

spodních 95 % A Nejlepších 95 %¾ jsou dolní a horní hranice 95procentního intervalu spolehlivosti pro koeficienty teoretické lineární regresní rovnice. Pokud v zadávání dat zablokujte hodnotu úroveň důvěry byl ve výchozím nastavení ponechán, pak poslední dva sloupce budou duplikovat ty předchozí. Pokud uživatel zadal vlastní hodnotu spolehlivosti, pak poslední dva sloupce obsahují hodnoty dolní a horní hranice pro zadanou úroveň spolehlivosti.

6. Analýza rozptylu, řádky obsahují hodnoty koeficientů, standardní chyby, t-statistik, P-hodnoty a intervaly spolehlivosti pro odpovídající .

7. Blokovat Výběr zůstatku obsahuje předpokládané hodnoty y(v našem zápisu je to ) a zbytky .