V Excel existuje ešte rýchlejší a pohodlnejší spôsob vykreslenia lineárnej regresie (a dokonca aj základných typov nelineárnych regresií, ako je uvedené nižšie). Dá sa to urobiť nasledovne:

1) vyberte stĺpce s údajmi X a Y(musia byť v tomto poradí!);

2) zavolajte Sprievodca grafom a vyberte si v skupine Typ – Bod a ihneď stlačte Pripravený;

3) bez vypustenia výberu z diagramu vyberte zobrazenú položku hlavného menu Diagram, v ktorom by ste mali vybrať položku Pridať trendovú čiaru;

4) v zobrazenom dialógovom okne Trendová línia v záložke Typ vybrať Lineárne;

5) v záložke možnosti spínač je možné aktivovať Zobraziť rovnicu v grafe, čo vám umožní vidieť rovnicu lineárnej regresie (4.4), v ktorej budú vypočítané koeficienty (4.5).

6) Na tej istej karte môžete aktivovať prepínač Umiestnite do diagramu približnú hodnotu spoľahlivosti (R ^ 2).... Táto veličina je druhou mocninou korelačného koeficientu (4.3) a ukazuje, ako dobre vypočítaná rovnica popisuje experimentálnu závislosť. Ak R 2 sa blíži k jednote, potom teoretická regresná rovnica dobre opisuje experimentálnu závislosť (teória dobre súhlasí s experimentom), a ak R 2 je blízka nule, potom táto rovnica nie je vhodná na popis experimentálnej závislosti (teória nesúhlasí s experimentom).

V dôsledku vykonania opísaných akcií získate diagram s regresným grafom a jeho rovnicou.

§ 4.3. Hlavné typy nelineárnej regresie

Parabolická a polynomická regresia.

Parabolický závislosť množstva Y na hodnote NS sa nazýva závislosť vyjadrená kvadratickou funkciou (parabola 2. rádu):

Táto rovnica sa nazýva parabolická regresná rovnica Y na NS... možnosti a, b, s sa volajú parabolické regresné koeficienty... Výpočet parabolických regresných koeficientov je vždy ťažkopádny, preto sa odporúča použiť na výpočty počítač.

Rovnica (4.8) parabolickej regresie je špeciálnym prípadom všeobecnejšej regresie nazývanej polynóm. Polynóm závislosť množstva Y na hodnote NS sa nazýva závislosť vyjadrená polynómom n- poradie:

kde sú čísla a ja (i=0,1,…, n) sa volajú polynomiálne regresné koeficienty.

Regresia moci.

Exponenciálny závislosť množstva Y na hodnote NS závislosť formy sa nazýva:

Táto rovnica sa nazýva mocenská regresná rovnica Y na NS... možnosti a a b sa volajú mocenské regresné koeficienty.

ln = ln a+b ln X. (4.11)

Táto rovnica opisuje priamku v rovine s logaritmickými súradnicovými osami ln X a ln. Preto je kritériom použiteľnosti mocninovej regresie požiadavka, aby body logaritmov empirických údajov ln x i a ln i boli najbližšie k priamke (4.11).

Exponenciálna regresia.

Orientačné(alebo exponenciálny) v závislosti od množstva Y na hodnote NS závislosť formy sa nazýva:

(alebo ). (4.12)

Táto rovnica sa nazýva exponenciálna rovnica(alebo exponenciálny) regresia Y na NS... možnosti a(alebo k) a b sa volajú exponenciálny(alebo exponenciálny) regresie.

Ak vezmeme logaritmus oboch strán mocninovej regresnej rovnice, dostaneme rovnicu

ln = X ln a+ ln b(alebo ln = k x+ ln b). (4.13)

Táto rovnica popisuje lineárnu závislosť logaritmu jednej veličiny ln na inej veličine X... Preto je kritériom použiteľnosti mocninovej regresie požiadavka, aby empirické dátové body rovnakého množstva x i a logaritmy inej veličiny ln i boli najbližšie k priamke (4.13).

Logaritmická regresia.

Logaritmické závislosť množstva Y na hodnote NS závislosť formy sa nazýva:

=a+b ln X. (4.14)

Táto rovnica sa nazýva logaritmická regresná rovnica Y na NS... možnosti a a b sa volajú logaritmické regresné koeficienty.

Hyperbolická regresia.

Hyperbolický závislosť množstva Y na hodnote NS závislosť formy sa nazýva:

Táto rovnica sa nazýva hyperbolická regresná rovnica Y na NS... možnosti a a b sa volajú hyperbolické regresné koeficienty a sú určené metódou najmenších štvorcov. Aplikácia tejto metódy vedie k vzorcom:

Vo vzorcoch (4.16-4.17) sa súčet vykonáva cez index i od jedného po počet pozorovaní n.

Bohužiaľ v Excel neexistuje žiadna funkcia, ktorá vypočítava koeficienty hyperbolickej regresie. V prípadoch, keď nie je vopred známe, že namerané veličiny súvisia inverznou úmernosťou, odporúča sa namiesto hyperbolickej regresnej rovnice hľadať mocninovú regresnú rovnicu, ako napr. Excel existuje postup, ako to nájsť. Ak sa predpokladá hyperbolická závislosť medzi nameranými hodnotami, potom bude potrebné vypočítať koeficienty jej regresie pomocou pomocných výpočtových tabuliek a súčtových operácií pomocou vzorcov (4.16-4.17).

Podľa mňa ako študenta je ekonometria jednou z najaplikovanejších vied zo všetkých, s ktorými som sa stihol zoznámiť v rámci múrov mojej univerzity. S jeho pomocou je skutočne možné riešiť problémy aplikovaného charakteru v meradle podniku. Ako efektívne budú tieto riešenia, je tretia otázka. Pointa je, že väčšina vedomostí zostane teóriou, ale ekonometria a regresná analýza sa stále oplatí študovať s osobitnou pozornosťou.

Čo vysvetľuje regresia?

Skôr ako začneme skúmať funkcie MS Excel, ktoré umožňujú riešenie týchto problémov, rád by som vám na prstoch vysvetlil, čo v podstate znamená regresná analýza. Uľahčí vám to zvládnutie skúšky a hlavne bude zaujímavejšie študovať predmet.

Dúfajme, že ste oboznámení s konceptom funkcie z matematiky. Funkcia je vzťah medzi dvoma premennými. Keď sa zmení jedna premenná, niečo sa stane s druhou. Zmeníme X a Y sa tiež zmení, resp. Funkcie popisujú rôzne zákony. Keď poznáme funkciu, môžeme nahradiť ľubovoľné hodnoty za X a pozrieť sa, ako to zmení Y.

Je to veľmi dôležité, keďže regresia je pokus o vysvetlenie zdanlivo nesystematických a chaotických procesov pomocou určitej funkcie. Takže je napríklad možné identifikovať vzťah medzi kurzom dolára a nezamestnanosťou v Rusku.

Ak sa tento vzor podarí zistiť, potom pomocou funkcie, ktorú sme získali v priebehu výpočtov, budeme schopní urobiť predpoveď, aká bude miera nezamestnanosti pri N-tom výmennom kurze dolára voči rubľu.
Tento vzťah sa bude nazývať korelácia. Regresná analýza zahŕňa výpočet korelačného koeficientu, ktorý vysvetlí tesnosť vzťahu medzi premennými, ktoré zvažujeme (výmenný kurz dolára a počet pracovných miest).

Tento pomer môže byť kladný alebo záporný. Jeho hodnoty sa pohybujú od -1 do 1. Podľa toho môžeme pozorovať vysokú negatívnu alebo pozitívnu koreláciu. Ak bude pozitívny, potom po zvýšení kurzu dolára bude nasledovať vznik nových pracovných miest. Ak je záporná, znamená to, že po zvýšení výmenného kurzu bude nasledovať pokles pracovných miest.

Existuje niekoľko typov regresie. Môže byť lineárny, parabolický, exponenciálny atď. Vyberáme model podľa toho, ktorá regresia bude konkrétne zodpovedať nášmu prípadu, ktorý model sa bude čo najviac približovať našej korelácii. Uvažujme to na príklade problému a vyriešme ho v MS Excel.

Lineárna regresia v MS Excel

Na vyriešenie problémov lineárnej regresie budete potrebovať funkciu Analýza údajov. Nemusí byť pre vás povolená, takže ju musíte aktivovať.

• Kliknite na tlačidlo "Súbor";
• Vyberieme položku "Parametre";
• Kliknite na predposlednú kartu „Doplnky“ na ľavej strane;

• Nižšie uvidíme nápis „Control“ a tlačidlo „Go“. Tlačíme na to;
• Začiarkneme políčko „Analýza“;
• Stlačíme "ok".

Príklad úlohy

Funkcia analýzy šarží je aktivovaná. Poďme vyriešiť nasledujúci problém. Máme vzorku údajov za niekoľko rokov o počte mimoriadnych situácií na území podniku a počte zamestnaných pracovníkov. Musíme identifikovať vzťah medzi týmito dvoma premennými. Existuje vysvetľujúca premenná X - to je počet pracovníkov a vysvetlená premenná - Y - je počet nehôd. Rozdeľme počiatočné údaje do dvoch stĺpcov.

Poďme na kartu "údaje" a vyberte "Analýza údajov"

V zobrazenom zozname vyberte možnosť „Regresia“. Vo vstupných intervaloch Y a X vyberte príslušné hodnoty.

Kliknite na „OK“. Analýza bola vykonaná a výsledky uvidíme v novom hárku.

Pre nás najvýznamnejšie hodnoty sú vyznačené na obrázku nižšie.

Násobok R je koeficient determinácie. Má zložitý vzorec výpočtu a ukazuje, do akej miery môžete dôverovať nášmu korelačnému koeficientu. Čím väčšia je teda táto hodnota, tým väčšia dôvera, tým úspešnejší je náš model ako celok.

Priesečník Y a priesečník X1 sú koeficienty našej regresie. Ako už bolo spomenuté, regresia je funkcia a má určité koeficienty. Naša funkcia teda bude vyzerať takto: Y = 0,64 * X-2,84.

čo nám to dáva? To nám umožňuje urobiť predpoveď. Povedzme, že chceme prijať 25 pracovníkov do firmy a potrebujeme si približne predstaviť, aký bude počet nehôd. Túto hodnotu dosadíme do našej funkcie a dostaneme výsledok Y = 0,64 * 25 - 2,84. U nás vznikne približne 13 mimoriadnych situácií.

Pozrime sa, ako to funguje. Pozrite sa na obrázok nižšie. Funkcia, ktorú sme získali, je nahradená skutočnými hodnotami pre zúčastnených zamestnancov. Pozrite sa, ako blízko sú hodnoty skutočným hrám.

Môžete tiež vytvoriť korelačné pole zvýraznením oblasti hráčov a xs, kliknutím na kartu „vložiť“ a výberom bodového grafu.

Bodky sú roztrúsené, ale vo všeobecnosti sa pohybujú nahor, akoby v strede v priamke. Tento riadok môžete pridať aj tak, že prejdete na kartu „Rozloženie“ v programe MS Excel a vyberiete položku „Trendová čiara“

Dvakrát kliknite na riadok, ktorý sa zobrazí, a uvidíte, čo bolo spomenuté vyššie. Typ regresie môžete zmeniť v závislosti od toho, ako vyzerá vaše korelačné pole.

Možno máte pocit, že body vykresľujú parabolu, nie priamku a je pre vás vhodnejšie zvoliť si iný typ regresie.

Záver

Dúfajme, že tento článok vám dal lepšie pochopenie toho, čo je regresná analýza a na čo slúži. To všetko má veľký praktický význam.

Regresná čiara je grafickým odrazom vzťahu medzi javmi. V Exceli môžete veľmi prehľadne zostaviť regresnú čiaru.

To si vyžaduje:

1. Otvorte program Excel

2. Vytvorte stĺpce s údajmi. V našom príklade vybudujeme regresnú líniu alebo vzťah medzi agresivitou a pochybnosťami o sebe u prvákov. Experimentu sa zúčastnilo 30 detí, údaje sú uvedené v tabuľke programu Excel:

1 stĺpec - Číslo predmetu

2 stĺpec - agresivita v bodoch

3 stĺpec - sebapochybnosť v bodoch

3.Potom musíte vybrať oba stĺpce (bez názvu stĺpca), kliknite na kartu vložiť , vybrať bod a z navrhovaných rozložení vyberte úplne prvé miesto so značkami .

4.Máme teda prázdne miesto pre regresnú priamku - tzv. bodový diagram... Ak chcete prejsť na regresnú čiaru, musíte kliknúť na výsledný obrázok a kliknúť na kartu konštruktér, nájsť na paneli rozloženia grafov a vyberte si M a ket9 , hovorí tiež f (x)

5. Máme teda regresnú priamku. V grafe je znázornená aj jeho rovnica a druhá mocnina korelačného koeficientu

6. Zostáva doplniť názov grafu, názov osí. Ak chcete, môžete tiež odstrániť legendu, znížiť počet vodorovných čiar mriežky (záložka rozloženie , potom net ). Základné zmeny a nastavenia sa vykonávajú v záložke Rozloženie

Regresná priamka bola zostavená v MS Excel. Teraz ho možno doplniť do textu práce.

28 okt

Dobré popoludnie, milí čitatelia blogu! Dnes budeme hovoriť o nelineárnych regresiách. Riešenie lineárnych regresií nájdete na ODKAZE.

Táto metóda sa používa najmä v ekonomickom modelovaní a prognózovaní. Jeho účelom je sledovať a identifikovať vzťah medzi týmito dvoma ukazovateľmi.

Hlavné typy nelineárnych regresií sú:

• polynóm (kvadratický, kubický);
• hyperbolický;
• mocenské právo;
• orientačné;
• logaritmický.

Môžu sa použiť aj rôzne kombinácie. Napríklad pre analytikov časových radov v bankovníctve, poisťovníctve a demografických štúdiách sa používa Gompzerova krivka, čo je druh logaritmickej regresie.

Pri prognózovaní pomocou nelineárnych regresií ide predovšetkým o zistenie korelačného koeficientu, ktorý nám ukáže, či medzi dvoma parametrami existuje úzky vzťah alebo nie. Spravidla, ak je korelačný koeficient blízky 1, potom existuje spojenie a predpoveď bude celkom presná. Ďalším dôležitým prvkom nelineárnych regresií je stredná relatívna chyba ( A ), ak je v intervale<8…10%, значит модель достаточно точна.

Týmto možno dokončíme teoretický blok a prejdeme k praktickým výpočtom.

Máme tabuľku predajov áut za interval 15 rokov (označíme X), počet krokov merania bude argument n, za tieto obdobia je aj tržba (označíme Y), treba predpovedať, aké budú výnosy v budúcnosti. Zostavme si nasledujúcu tabuľku:

Pre výskum potrebujeme vyriešiť rovnicu (závislosť Y na X): y = ax 2 + bx + c + e. Toto je párová kvadratická regresia. Aplikujme v tomto prípade metódu najmenších štvorcov, aby sme zistili neznáme argumenty - a, b, c. Povedie to k systému algebraických rovníc v tvare:

Na vyriešenie tohto systému použijeme napríklad Cramerovu metódu. Vidíme, že sumy zahrnuté v systéme sú koeficienty s neznámymi. Pre ich výpočet pridajte do tabuľky niekoľko stĺpcov (D, E, F, G, H) a podpíšte ich podľa významu výpočtov - v stĺpci D odmocníme x, v E v kocke, v F v 4. mocnina, v G vynásobíme exponenty x a y, v H odmocninu x a vynásobíme y.

Dostanete tabuľku formulára vyplnenú potrebnými na riešenie rovnice.

Vytvorme matricu A systém pozostávajúci z koeficientov s neznámymi na ľavej strane rovníc. Umiestnite ho do bunky A22 a nazvite ho „ A =". Postupujeme podľa sústavy rovníc, ktoré sme si zvolili na riešenie regresie.

To znamená, že do bunky B21 musíme umiestniť súčet stĺpca, kde bol exponent X zvýšený na štvrtú mocninu - F17. Pozrime sa len na bunku - "= F17". Ďalej potrebujeme súčet stĺpca, kde bolo X zvýšené na kocku - E17, potom ideme striktne podľa systému. Preto budeme musieť vyplniť celú maticu.

V súlade s Cramerovým algoritmom napíšeme maticu A1, podobnú A, do ktorej by sa namiesto prvkov prvého stĺpca mali umiestniť prvky pravých strán rovníc systému. To znamená, že súčet stĺpca X na druhú krát Y, súčet stĺpca XY a súčet stĺpca Y.

Potrebujeme tiež dve ďalšie matice – nazvime ich A2 a A3, v ktorých druhý a tretí stĺpec budú pozostávať z koeficientov pravých strán rovníc. Obrázok bude takýto.

Podľa zvoleného algoritmu budeme musieť vypočítať hodnoty determinantov (determinanty, D) výsledných matíc. Použime vzorec MOPRED. Výsledky umiestnite do buniek J21: K24.

Výpočet koeficientov rovnice podľa Kramera sa uskutoční v bunkách oproti zodpovedajúcim determinantom podľa vzorca: a(v bunke M22) - "= K22 / K21"; b(v bunke M23) - "= K23 / K21"; s(v bunke M24) - "= K24 / K21".

Získame našu požadovanú párovú kvadratickú regresnú rovnicu:

y = -0,074 x 2 + 2,151 x + 6,523

Odhadnime tesnosť lineárneho spojenia pomocou korelačného indexu.

Pre výpočet pridajte do tabuľky ďalší stĺpec J (nazvime ho y *). Výpočet bude nasledovný (podľa nami získanej regresnej rovnice) - "= $ M $ 22 * ​​​​B2 * B2 + $ M $ 23 * B2 + $ M $ 24". Umiestnite ho do bunky J2. Zostáva natiahnuť značku automatického dopĺňania nadol do bunky J16.

Na výpočet súčtu (priemer Y-Y) 2 pridajte stĺpce K a L do tabuľky so zodpovedajúcimi vzorcami. Priemer pre stĺpec Y sa vypočíta pomocou funkcie AVERAGE.

Do bunky K25 umiestnite vzorec na výpočet korelačného indexu - "= ROOT (1- (K17 / L17)".

Vidíme, že hodnota 0,959 je veľmi blízka 1, čo znamená, že medzi predajom a rokmi existuje úzky nelineárny vzťah.

Zostáva zhodnotiť kvalitu prispôsobenia získanej kvadratickej regresnej rovnice (index determinácie). Vypočíta sa pomocou vzorca druhej mocniny indexu korelácie. To znamená, že vzorec v bunke K26 bude veľmi jednoduchý - "= K25 * K25".

Faktor 0,920 je blízko 1, čo naznačuje vysokú kvalitu prispôsobenia.

Posledným krokom je výpočet relatívnej chyby. Pridajte stĺpec a pridajte tam vzorec: „= ABS ((C2-J2) / C2), ABS - modul, absolútna hodnota. Potiahnite značku nadol a v bunke M18 zobrazte priemernú hodnotu (AVERAGE), priraďte bunkám percentuálny formát. Získaný výsledok - 7,79 % je v rámci prípustných hodnôt chyby<8…10%. Значит вычисления достаточно точны.

V prípade potreby môžeme na základe získaných hodnôt zostaviť graf.

Príklad súboru je priložený - LINK!

Kategórie:/ / zo dňa 28.10.2017

Regresná analýza je štatistická výskumná metóda, ktorá vám umožňuje ukázať závislosť parametra od jednej alebo viacerých nezávislých premenných. V predpočítačovej ére bola jeho aplikácia pomerne náročná, najmä ak išlo o veľké množstvo dát. Dnes, keď ste sa naučili, ako vytvoriť regresiu v Exceli, môžete vyriešiť zložité štatistické problémy len za pár minút. Nižšie uvádzame konkrétne príklady z oblasti ekonómie.

Regresné typy

Tento pojem bol zavedený do matematiky v roku 1886. Regresia sa deje:

• lineárny;
• parabolický;
• mocenské právo;
• exponenciálny;
• hyperbolický;
• orientačné;
• logaritmický.

Príklad 1

Zamyslime sa nad problémom určenia závislosti počtu zamestnancov, ktorí ukončili prácu, od priemernej mzdy v 6 priemyselných podnikoch.

Úloha. Šesť podnikov analyzovalo priemernú mesačnú mzdu a počet zamestnancov, ktorí dobrovoľne skončili. V tabuľkovej forme máme:

		Počet odstúpených	Výplata
			30 000 rubľov
			35 000 rubľov
			40 000 rubľov
			45 000 rubľov
			50 000 rubľov
			55 000 rubľov
			60 000 rubľov

Pre problém určenia závislosti počtu odchádzajúcich zamestnancov od priemernej mzdy v 6 podnikoch má regresný model tvar rovnice Y = a 0 + a 1 x 1 + ... + akxk, kde xi sú ovplyvňujúce premenné, ai sú regresné koeficienty a ak je počet faktorov.

Pre túto úlohu je Y ukazovateľom zamestnancov, ktorí odídu, a ovplyvňujúcim faktorom je mzda, ktorú označujeme X.

Využitie možností tabuľkového procesora Excel

Regresnej analýze v Exceli musí predchádzať aplikácia vstavaných funkcií na existujúce tabuľkové údaje. Na tieto účely je však lepšie použiť veľmi užitočný doplnok „Analytický balík“. Na jeho aktiváciu potrebujete:

• na karte "Súbor" prejdite do časti "Parametre";
• v okne, ktoré sa otvorí, vyberte riadok "Doplnky";
• kliknite na tlačidlo "Prejsť" umiestnené nižšie, napravo od riadku "Ovládanie";
• zaškrtnite názov "Analýza balík" a potvrďte svoje akcie kliknutím na "OK".

Ak je všetko vykonané správne, požadované tlačidlo sa zobrazí na pravej strane karty "Údaje", ktorá sa nachádza nad pracovným hárkom "Excel".

v Exceli

Teraz, keď máme po ruke všetky potrebné virtuálne nástroje na vykonávanie ekonometrických výpočtov, môžeme začať riešiť náš problém. Pre to:

• kliknite na tlačidlo "Analýza údajov";
• v okne, ktoré sa otvorí, kliknite na tlačidlo "Regresia";
• na zobrazenej karte zadajte rozsah hodnôt pre Y (počet zamestnancov, ktorí skončili prácu) a pre X (ich platy);
• potvrdíme naše akcie stlačením tlačidla "Ok".

Výsledkom je, že program automaticky vyplní nový hárok tabuľkového procesora údajmi z regresnej analýzy. Poznámka! Excel má schopnosť nezávisle definovať umiestnenie, ktoré na tento účel uprednostňujete. Môže to byť napríklad rovnaký hárok, ktorý obsahuje hodnoty Y a X, alebo dokonca nový zošit špeciálne navrhnutý na ukladanie tohto druhu údajov.

Analýza výsledkov regresie pre R-štvorec

V Exceli sú údaje získané v priebehu spracovania údajov predmetného príkladu nasledovné:

V prvom rade by ste si mali dať pozor na hodnotu R-štvorca. Predstavuje koeficient determinácie. V tomto príklade R-štvorec = 0,755 (75,5 %), to znamená, že vypočítané parametre modelu vysvetľujú vzťah medzi uvažovanými parametrami na 75,5 %. Čím vyššia je hodnota koeficientu determinácie, tým viac sa zvolený model považuje za použiteľnejší pre konkrétnu úlohu. Predpokladá sa, že správne popisuje skutočnú situáciu, keď je hodnota R-squared nad 0,8. Ak R-štvorec<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Analýza šancí

Číslo 64,1428 ukazuje, aká bude hodnota Y, ak všetky premenné xi v modeli, ktorý uvažujeme, budú nulové. Inými slovami, možno tvrdiť, že hodnota analyzovaného parametra je ovplyvnená inými faktormi, ktoré nie sú popísané v konkrétnom modeli.

Ďalší koeficient -0,16285, ktorý sa nachádza v bunke B18, ukazuje významnosť vplyvu premennej X na Y. To znamená, že priemerná mesačná mzda zamestnancov v rámci uvažovaného modelu ovplyvňuje počet ľudí, ktorí odídu s váhou -0,16285. , teda miera jeho vplyvu vôbec malá. Znamienko „-“ znamená, že koeficient je záporný. Je to zrejmé, pretože každý vie, že čím vyšší je plat v podniku, tým menej ľudí vyjadrí želanie ukončiť pracovnú zmluvu alebo odísť.

Viacnásobná regresia

Tento pojem sa chápe ako obmedzujúca rovnica s niekoľkými nezávislými premennými tvaru:

y = f (x 1 + x 2 +… x m) + ε, kde y je efektívny ukazovateľ (závislá premenná) a x 1, x 2,… x m sú ukazovatele-faktory (nezávislé premenné).

Odhad parametrov

Pri viacnásobnej regresii (MR) sa vykonáva metódou najmenších štvorcov (OLS). Pre lineárne rovnice tvaru Y = a + b 1 x 1 +… + b m x m + ε zostrojíme sústavu normálnych rovníc (pozri nižšie)

Aby ste pochopili princíp metódy, zvážte dvojfaktorový prípad. Potom máme situáciu opísanú vzorcom

Odtiaľto dostaneme:

kde σ je rozptyl zodpovedajúceho znaku vyjadrený v indexe.

OLS sa aplikuje na rovnicu MR na štandardizovanej škále. V tomto prípade dostaneme rovnicu:

kde t y, t x 1,… t xm sú štandardizované premenné, ktorých stredné hodnoty sa rovnajú 0; β i sú štandardizované regresné koeficienty a štandardná odchýlka je 1.

Všimnite si, že všetky β i sú v tomto prípade špecifikované ako normalizované a centralizované, takže ich vzájomné porovnanie sa považuje za správne a platné. Okrem toho je zvyčajné odfiltrovať faktory a vyradiť tie s najmenšími hodnotami βi.

Problém s použitím lineárnej regresnej rovnice

Predpokladajme, že máte tabuľku dynamiky cien pre konkrétny produkt N za posledných 8 mesiacov. Je potrebné rozhodnúť o vhodnosti nákupu jeho šarže za cenu 1850 rubľov / t.

	číslo mesiaca	názov mesiaca	cena produktu N
			1750 rubľov za tonu
			1755 rubľov za tonu
			1767 rubľov za tonu
			1760 rubľov za tonu
			1770 rubľov za tonu
			1790 rubľov za tonu
			1810 rubľov za tonu
			1840 rubľov za tonu

Ak chcete vyriešiť tento problém v tabuľkovom procesore Excel, musíte použiť nástroj Analýza údajov, ktorý je už známy z vyššie uvedeného príkladu. Ďalej vyberte sekciu "Regresia" a nastavte parametre. Malo by sa pamätať na to, že v poli „Interval vstupu Y“ je potrebné zadať rozsah hodnôt pre závislú premennú (v tomto prípade cenu produktu v konkrétnych mesiacoch roka) a v poli „Vstup interval X" - pre nezávislú premennú (číslo mesiaca). Akcie potvrdíme kliknutím na „OK“. Na novom hárku (ak to bolo naznačené) dostaneme údaje pre regresiu.

Pomocou nich zostrojíme lineárnu rovnicu tvaru y = ax + b, kde pôsobia koeficienty úsečky s názvom čísla mesiaca a koeficienty a úsečky "Y-priesečník" z hárku s výsledkami regresnej analýzy. ako parametre a a b. Rovnica lineárnej regresie (SD) pre problém 3 je teda napísaná ako:

Cena produktu N = 11,714 * číslo mesiaca + 1727,54.

alebo v algebraickom zápise

y = 11,714 x + 1727,54

Analýza výsledkov

Na rozhodnutie, či je získaná lineárna regresná rovnica adekvátna, sa používajú viacnásobné korelačné a determinačné koeficienty, ako aj Fisherov test a Studentov t test. V excelovej tabuľke s výsledkami regresie sa nazývajú viacnásobná R, R-štvorcová, F-štatistika a t-štatistika.

KMC R umožňuje posúdiť blízkosť pravdepodobnostného vzťahu medzi nezávislými a závislými premennými. Jeho vysoká hodnota naznačuje pomerne silný vzťah medzi premennými „Číslo mesiaca“ a „Cena produktu N v rubľoch za tonu“. Povaha tohto spojenia však zostáva neznáma.

Druhá mocnina koeficientu determinácie R2 (RI) je číselnou charakteristikou podielu celkového rozptylu a ukazuje rozptyl tej ktorej časti experimentálnych údajov, t.j. hodnoty závislej premennej zodpovedajú lineárnej regresnej rovnici. V uvažovanom probléme je táto hodnota 84,8 %, t.j. štatistické údaje sú popísané s vysokou mierou presnosti získaným SD.

F-štatistika, nazývaná aj Fisherov test, sa používa na posúdenie významnosti lineárneho vzťahu, vyvrátenia alebo potvrdenia hypotézy o jeho existencii.

(Študentské kritérium) pomáha posúdiť významnosť koeficientu s neznámym alebo voľným členom lineárneho vzťahu. Ak je hodnota t-kritéria > t cr, potom sa hypotéza o nevýznamnosti voľného člena lineárnej rovnice zamieta.

V uvažovanej úlohe pre voľný termín pomocou nástrojov Excel sa zistilo, že t = 169,20903 a p = 2,89E-12, to znamená, že máme nulovú pravdepodobnosť, že správna hypotéza o nevýznamnosti voľného termínu bude odmietol. Pre koeficient pri neznámej hodnote t = 5,79405 a p = 0,001158. Inými slovami, pravdepodobnosť, že bude zamietnutá správna hypotéza o nevýznamnosti koeficientu s neznámou, je 0,12 %.

Dá sa teda tvrdiť, že výsledná lineárna regresná rovnica je adekvátna.

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

Viacnásobná regresia v Exceli sa vykonáva pomocou rovnakého nástroja na analýzu údajov. Uvažujme o konkrétnom aplikovanom probléme.

Manažment spoločnosti "NNN" musí rozhodnúť o vhodnosti kúpy 20% podielu v JSC "MMM". Náklady na balík (JV) sú 70 miliónov USD. Špecialisti NNN zhromaždili údaje o podobných transakciách. Bolo rozhodnuté ohodnotiť hodnotu balíka akcií takými parametrami, vyjadrenými v miliónoch amerických dolárov, ako sú:

• splatné účty (VK);
• objem ročného obratu (VO);
• pohľadávky (VD);
• obstarávacia cena fixných aktív (SOF).

Okrem toho je parametrom nedoplatok miezd podniku (V3 P) v tisícoch amerických dolárov.

Tabuľkové riešenie Excel

Najprv musíte vytvoriť tabuľku počiatočných údajov. Vyzerá to takto:

• zavolajte okno "Analýza údajov";
• vyberte sekciu "Regresia";
• rozsah hodnôt závislých premenných zo stĺpca G sa zadáva do poľa „Interval vstupu Y“;
• kliknite na ikonu s červenou šípkou napravo od okna „Interval vstupu X“ a na hárku vyberte rozsah všetkých hodnôt zo stĺpcov B, C, D, F.

Začiarknite položku "Nový pracovný hárok" a kliknite na tlačidlo "OK".

Získajte regresnú analýzu pre danú úlohu.

Štúdium výsledkov a záverov

„Zhromažďujeme“ regresnú rovnicu zo zaokrúhlených údajov uvedených vyššie v tabuľkovom hárku programu Excel:

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

V známejšej matematickej forme to možno napísať ako:

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

Údaje pre JSC "MMM" sú uvedené v tabuľke:

Po ich dosadení do regresnej rovnice je to 64,72 milióna amerických dolárov. To znamená, že akcie JSC "MMM" by sa nemali kupovať, pretože ich hodnota 70 miliónov amerických dolárov je dosť nadhodnotená.

Ako vidíte, použitie tabuľkového procesora Excel a regresnej rovnice umožnilo urobiť informované rozhodnutie o vhodnosti veľmi špecifickej transakcie.

Teraz viete, čo je regresia. Vyššie uvedené príklady v Exceli vám pomôžu vyriešiť praktické problémy v oblasti ekonometrie.