Analýza excelového regresného modelu. Regresná analýza v programe Microsoft Excel. Návod na stavbu modelov

Toto je najbežnejší spôsob, ako ukázať závislosť niektorej premennej od iných, napr úroveň HDP z hodnoty zahraničná investícia alebo z úverovej sadzby národnej banky alebo z ceny kľúčových energetických zdrojov.

Modelovanie vám umožňuje zobraziť veľkosť tejto závislosti (koeficienty), vďaka čomu môžete priamo predpovedať a na základe týchto predpovedí realizovať nejaký druh plánovania. Na základe regresnej analýzy je tiež možné robiť manažérske rozhodnutia zamerané na stimuláciu prioritných príčin ovplyvňujúcich konečný výsledok a samotný model pomôže tieto prioritné faktory zvýrazniť.

Celkový pohľad na model lineárna regresia:

Y = a 0 + a 1 x 1 +...+ak x k

kde a - parametre (koeficienty) regresie, X - ovplyvňujúce faktory, k je počet faktorov modelu.

Počiatočné údaje

Medzi počiatočnými údajmi potrebujeme určitý súbor údajov, ktorý by pozostával z niekoľkých po sebe nasledujúcich alebo vzájomne prepojených hodnôt konečného parametra Y (napríklad HDP) a rovnakého počtu hodnôt ukazovateľov, ktorých vplyv študujeme ( napríklad zahraničné investície).

Vyššie uvedený obrázok ukazuje tabuľku s rovnakými počiatočnými údajmi, Y je ukazovateľ ekonomicky aktívneho obyvateľstva a počet podnikov, výška investícií do kapitálu a príjmy obyvateľstva sú ovplyvňujúce faktory, teda x.

Z obrázku je tiež možné urobiť mylný záver, že pri modelovaní môžeme hovoriť len o dynamických radoch, teda o momentových radoch fixovaných sekvenčne v čase, ale nie je to tak, s rovnakým úspechom je možné modelovať v kontext štruktúry, napríklad hodnoty uvedené v tabuľke možno rozdeliť nie podľa rokov, ale podľa regiónov.

Stavať adekvátne lineárne modely je žiaduce, aby počiatočné údaje nemali silné poklesy alebo kolapsy, v takýchto prípadoch je žiaduce vykonať vyhladenie, ale o vyhladení si povieme nabudúce.

Analytický balík

Parametre lineárneho regresného modelu je možné vypočítať aj manuálne pomocou metódy najmenších štvorcov (OLS), čo je však časovo dosť náročné. O niečo rýchlejšie sa to dá vypočítať pomocou rovnakej metódy pomocou vzorcov v Exceli, kde program vykoná výpočty sám, ale aj tak musíte vzorce zadávať ručne.

Excel má doplnok Analytický balíkčo je pekné mocný nástroj pomôcť analytikovi. Táto sada nástrojov je okrem iného schopná vypočítať regresné parametre podľa rovnakého OLS len niekoľkými kliknutiami, v skutočnosti sa bude diskutovať o tom, ako ďalej používať tento nástroj.

Aktivácia analytického balíka

V predvolenom nastavení je tento doplnok zakázaný a nenájdete ho v ponuke kariet, poďme sa teda pozrieť krok za krokom, ako ho aktivovať.

V Exceli vľavo hore aktivujte kartu Súbor, v ponuke, ktorá sa otvorí, vyhľadajte položku možnosti a kliknite naň.

V okne, ktoré sa otvorí, vľavo vyhľadajte položku doplnky a aktivujte ho, v tejto záložke v spodnej časti bude rozbaľovací zoznam ovládacích prvkov, kde bude štandardne napísané Excel doplnky , napravo od rozbaľovacieho zoznamu bude tlačidlo Choď, musíte naň kliknúť.

Vyskakovacie okno vás vyzve na výber dostupných doplnkov, v ktorých musíte začiarknuť políčko vedľa Analytický balík a zároveň, pre každý prípad, Hľadanie riešenia(tiež užitočná vec), a následne výber potvrďte kliknutím na tlačidlo OK.

Pokyny na nájdenie parametrov lineárnej regresie pomocou analytického balíka

Po aktivácii doplnku Analysis Pack bude vždy dostupný na karte hlavného menu Údaje pod odkazom Analýza dát

V aktívnom okne nástroja Analýza dát zo zoznamu možností vyhľadajte a vyberte Regresia

Ďalej sa otvorí okno na nastavenie a výber počiatočných údajov pre výpočet parametrov regresný model. Tu musíte špecifikovať intervaly počiatočných údajov, konkrétne opísaný parameter (Y) a faktory, ktoré ho ovplyvňujú (X), ako na obrázku nižšie, zostávajúce parametre sú v zásade voliteľné.

Po výbere zdrojových údajov a kliknutí na tlačidlo OK Excel zobrazí výpočty na novom hárku aktívneho zošita (ak to nebolo v nastaveniach nastavené inak), tieto výpočty vyzerajú takto:

Vyplnené kľúčové bunky žltá práve na ne si treba dať pozor v prvom rade, dôležité sú aj ostatné parametre, no ich podrobný rozbor si vyžaduje snáď samostatný príspevok.

takže, 0,865 - toto je R2- koeficient determinácie, ktorý ukazuje, že 86,5 % vypočítaných parametrov modelu, teda modelu samotného, ​​vysvetľuje závislosť a zmeny študovaného parametra - Y zo skúmaných faktorov - X. Ak je to prehnané, tak je indikátorom kvality modelu a čím vyššie, tým lepšie. Je jasné, že nemôže byť viac ako 1 a považuje sa za dobré, keď je R2 vyššie ako 0,8, a ak je menšie ako 0,5, potom možno rozumnosť takéhoto modelu bezpečne spochybniť.

Teraz prejdime k modelové koeficienty:
2079,85 - toto je - koeficient, ktorý ukazuje, čo bude Y, ak sa všetky faktory použité v modeli rovnajú 0, rozumie sa, že ide o závislosť od iných faktorov, ktoré nie sú opísané v modeli;
-0,0056 - 1- koeficient, ktorý ukazuje váhu vplyvu faktora x 1 na Y, to znamená, že počet podnikov v rámci tohto modelu ovplyvňuje ukazovateľ ekonomicky aktívneho obyvateľstva s váhou len -0,0056 (dosť malá miera vplyvu ). Znamienko mínus ukazuje, že tento vplyv je negatívny, to znamená, že čím viac podnikov, tým menej ekonomicky aktívneho obyvateľstva, bez ohľadu na to, aký paradoxný môže byť význam;
-0,0026 - a 2- koeficient vplyvu objemu investícií do kapitálu na veľkosť ekonomicky aktívneho obyvateľstva, podľa modelu je aj tento vplyv negatívny;
0,0028 - a 3- koeficient vplyvu príjmov obyvateľstva na veľkosť ekonomicky aktívneho obyvateľstva, tu je vplyv pozitívny, to znamená, že podľa modelu zvýšenie príjmov prispeje k zvýšeniu veľkosti ekonomicky aktívneho obyvateľstva.

Vypočítané koeficienty zhromažďujeme v modeli:

Y = 2079,85 - 0,0056 x 1 - 0,0026 x 2 + 0,0028 x 3

V skutočnosti ide o lineárny regresný model, ktorý vyzerá presne takto pre počiatočné údaje použité v príklade.

Modelové odhadované hodnoty a predpoveď

Ako sme diskutovali vyššie, model je zostavený nielen na zobrazenie veľkosti závislostí študovaného parametra na ovplyvňujúcich faktoroch, ale aj na to, aby bolo možné pri znalosti týchto ovplyvňujúcich faktorov urobiť predpoveď. Aby bola táto predpoveď pomerne jednoduchá, stačí do výslednej modelovej rovnice nahradiť hodnoty ovplyvňujúcich faktorov na miesto zodpovedajúcich x. Na obrázku nižšie sú tieto výpočty vykonané v Exceli v samostatnom stĺpci.

Skutočné hodnoty (tie, ktoré sa odohrali v skutočnosti) a vypočítané hodnoty pre model na rovnakom obrázku sú zobrazené vo forme grafov, aby sa znázornil rozdiel, a teda aj chyba modelu.

Opakujem ešte raz, na vytvorenie prognózy pre model je potrebné, aby boli známe ovplyvňujúce faktory a ak rozprávame sa o časových radoch a podľa toho aj o predpovedi do budúcnosti, napríklad na ďalší rok alebo mesiac, sa ani zďaleka nie vždy podarí zistiť, aké budú ovplyvňujúce faktory práve v tejto budúcnosti. V takýchto prípadoch je tiež potrebné urobiť prognózu ovplyvňujúcich faktorov, najčastejšie sa to robí pomocou autoregresného modelu - modelu, v ktorom sú ovplyvňujúcimi faktormi skúmaný objekt a čas, teda závislosť ukazovateľa od čo to bolo v minulosti je modelované.

Ako zostaviť autoregresný model zvážime v nasledujúcom článku a teraz budeme predpokladať, že vieme, aké budú hodnoty ovplyvňujúcich faktorov v budúcom období (v príklade 2008), pričom tieto hodnoty nahradíme. do výpočtov dostaneme našu predpoveď na rok 2008.

Známy tým, že je užitočný v rôznych oblastiachčinnosti, vrátane takej disciplíny ako je ekonometria, kde sa táto práca využíva softvérová pomôcka. V podstate všetky akcie praktického a laboratórne triedy vykonávané v Exceli, čo značne zjednodušuje prácu, dáva podrobné vysvetlenia určité akcie. Takže jeden z analytických nástrojov "Regresia" sa používa na výber grafu pre súbor pozorovaní pomocou metódy najmenších štvorcov. Uvažujme, čo je tento nástroj program a aký je jeho prínos pre používateľov. Nižšie je tiež krátke ale jasný pokyn vytvorenie regresného modelu.

Hlavné úlohy a typy regresie

Regresia je vzťah medzi danými premennými, vďaka ktorému je možné určiť prognózu budúceho správania týchto premenných. Premenné sú rôzne periodické javy vrátane ľudského správania. Taká analýza Excel programy používa sa na analýzu vplyvu na konkrétneho závislého hodnotová premenná jednu alebo viac premenných. Napríklad predaj v obchode ovplyvňuje viacero faktorov, medzi ktoré patrí sortiment, ceny a umiestnenie predajne. Pomocou regresie v programe Excel môžete určiť mieru vplyvu každého z týchto faktorov na základe výsledkov existujúcich predajov a získané údaje potom použiť na prognózu predaja na ďalší mesiac alebo pre iný obchod nachádzajúci sa v blízkosti.

Typicky je regresia prezentovaná ako jednoduchá rovnica, ktorá odhaľuje závislosti a silu asociácie medzi dvoma skupinami premenných, kde jedna skupina je závislá alebo endogénna a druhá je nezávislá alebo exogénna. Ak existuje skupina vzájomne súvisiacich ukazovateľov, závislá premenná Y je určená na základe logiky uvažovania a zvyšok pôsobí ako nezávislé X-premenné.

Hlavné úlohy vytvárania regresného modelu sú nasledovné:

1. Výber významných nezávislých premenných (Х1, Х2, …, Xk).
2. Výber typu funkcie.
3. Konštrukcia odhadov pre koeficienty.
4. Konštrukcia intervalov spoľahlivosti a regresných funkcií.
5. Kontrola významnosti vypočítaných odhadov a zostrojenej regresnej rovnice.

Existuje niekoľko typov regresnej analýzy:

• párové (1 závislá a 1 nezávislá premenná);
• násobok (niekoľko nezávislých premenných).

Existujú dva typy regresných rovníc:

1. Lineárne, ilustrujúce prísne lineárne spojenie medzi premennými.
2. Nelineárne – rovnice, ktoré môžu obsahovať mocniny, zlomky a goniometrické funkcie.

Návod na stavbu modelov

Ak chcete vykonať danú konštrukciu v programe Excel, musíte postupovať podľa pokynov:

Pre ďalší výpočet by sa mala použiť funkcia "Linear()", ktorá špecifikuje hodnoty Y, hodnoty X, Const a štatistiky. Potom pomocou funkcie "Trend" určte množinu bodov na regresnej priamke - Y-values, X-values, New values, Const. S pomocou nastaviť parametre vypočítajte neznámu hodnotu koeficientov na základe dané podmienky pridelená úloha.

Zobrazuje vplyv niektorých hodnôt (nezávislých, nezávislých) na závislú premennú. Napríklad ako závisí počet ekonomicky aktívneho obyvateľstva od počtu podnikov, miezd a iných parametrov. Alebo: ako vplývajú na úroveň HDP zahraničné investície, ceny energií atď.

Výsledok analýzy vám umožňuje určiť priority. A na základe hlavných faktorov predvídať, plánovať rozvoj prioritných oblastí, prijímať manažérske rozhodnutia.

Regresia sa deje:

lineárny (y = a + bx);

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

exponenciálna (y = a * exp(bx));

mocnina (y = a*x^b);

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

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

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

Zvážte príklad vytvorenia regresného modelu v Exceli a interpretácie výsledkov. Zoberme si lineárny typ regresie.

Úloha. V 6 podnikoch sa analyzovala priemerná mesačná mzda a počet zamestnancov, ktorí odišli. Je potrebné určiť závislosť počtu zamestnancov na dôchodku od priemernej mzdy.

Lineárny regresný model má nasledujúci tvar:

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

Kde a sú regresné koeficienty, x sú ovplyvňujúce premenné a k je počet faktorov.

V našom príklade je Y indikátorom ukončenia pracovného pomeru. Ovplyvňujúci faktor je mzda (x).

Excel má vstavané funkcie, ktoré možno použiť na výpočet parametrov lineárneho regresného modelu. Ale doplnok Analysis ToolPak to urobí rýchlejšie.

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

1. Stlačte tlačidlo "Office" a prejdite na " Možnosti programu Excel". "Doplnky".

2. Nižšie pod rozbaľovacím zoznamom v poli „Správa“ bude nápis „Doplnky programu Excel“ (ak tam nie je, kliknite na začiarkavacie políčko vpravo a vyberte). A tlačidlo Go. Kliknite.

3. Otvorí sa zoznam dostupných doplnkov. Vyberte "Analytický balík" a kliknite na tlačidlo OK.

Po aktivácii bude doplnok dostupný na karte Údaje.

Teraz sa budeme zaoberať priamo regresnou analýzou.

1. Otvorte ponuku nástroja Analýza údajov. Vyberte "Regresia".

2. Otvorí sa ponuka na výber vstupných hodnôt a možností výstupu (kde sa má zobraziť výsledok). V poliach pre počiatočné údaje uvádzame rozsah opísaného parametra (Y) a faktor, ktorý ho ovplyvňuje (X). Zvyšok môže a nemusí byť dokončený.

3. Po kliknutí na OK program zobrazí výpočty na novom hárku (môžete zvoliť interval zobrazenia na aktuálnom hárku alebo priradiť výstup do nového zošita).

V prvom rade venujeme pozornosť R-štvorcu a koeficientom.

R-štvorec je koeficient determinácie. V našom príklade je to 0,755 alebo 75,5 %. To znamená, že vypočítané parametre modelu vysvetľujú vzťah medzi skúmanými parametrami na 75,5 %. Čím vyšší je koeficient determinácie, tým lepší model. Dobré - nad 0,8. Slabá - menej ako 0,5 (takúto analýzu možno len ťažko považovať za primeranú). V našom príklade - "nie je to zlé".

Koeficient 64,1428 ukazuje, aké bude Y, ak sa všetky premenné v uvažovanom modeli rovnajú 0. To znamená, že hodnotu analyzovaného parametra ovplyvňujú aj iné faktory, ktoré nie sú v modeli popísané.

Koeficient -0,16285 ukazuje váhu premennej X na Y. To znamená, že priemerná mesačná mzda v rámci tohto modelu ovplyvňuje počet odchádzajúcich s váhou -0,16285 (to je malý stupeň vplyvu). Znamienko „-“ označuje negatívny vplyv: čím vyšší plat, tým menej výpovede. Čo je spravodlivé.

Regresná a korelačná analýza - štatistické metódy výskumu. Toto sú najbežnejšie spôsoby zobrazenia závislosti parametra od jednej alebo viacerých nezávislých premenných.

Nižšie konkrétne praktické príklady Zoberme si tieto dve veľmi populárne analýzy medzi ekonómami. Uvedieme aj príklad získania výsledkov pri ich kombinácii.

Regresná analýza v Exceli

Zobrazuje vplyv niektorých hodnôt (nezávislých, nezávislých) na závislú premennú. Napríklad ako závisí počet ekonomicky aktívneho obyvateľstva od počtu podnikov, miezd a iných parametrov. Alebo: ako vplývajú na úroveň HDP zahraničné investície, ceny energií atď.

Výsledok analýzy vám umožňuje určiť priority. A na základe hlavných faktorov predvídať, plánovať rozvoj prioritných oblastí, prijímať manažérske rozhodnutia.

Regresia sa deje:

• lineárny (y = a + bx);
• parabolický (y = a + bx + cx 2);
• exponenciálna (y = a * exp(bx));
• mocnina (y = a*x^b);
• hyperbolický (y = b/x + a);
• logaritmické (y = b * ln(x) + a);
• exponenciálna (y = a * b^x).

Zvážte príklad vytvorenia regresného modelu v Exceli a interpretácie výsledkov. Zoberme si lineárny typ regresie.

Úloha. V 6 podnikoch sa analyzovala priemerná mesačná mzda a počet zamestnancov, ktorí odišli. Je potrebné určiť závislosť počtu zamestnancov na dôchodku od priemernej mzdy.

Lineárny regresný model má nasledujúci tvar:

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

Kde a sú regresné koeficienty, x sú ovplyvňujúce premenné a k je počet faktorov.

V našom príklade je Y indikátorom ukončenia pracovného pomeru. Ovplyvňujúci faktor je mzda (x).

Excel má vstavané funkcie, ktoré možno použiť na výpočet parametrov lineárneho regresného modelu. Ale doplnok Analysis ToolPak to urobí rýchlejšie.

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

Po aktivácii bude doplnok dostupný na karte Údaje.

Teraz sa budeme zaoberať priamo regresnou analýzou.

V prvom rade venujeme pozornosť R-štvorcu a koeficientom.

R-štvorec je koeficient determinácie. V našom príklade je to 0,755 alebo 75,5 %. To znamená, že vypočítané parametre modelu vysvetľujú vzťah medzi skúmanými parametrami na 75,5 %. Čím vyšší je koeficient determinácie, tým lepší je model. Dobré - nad 0,8. Slabá - menej ako 0,5 (takúto analýzu možno len ťažko považovať za primeranú). V našom príklade - "nie je to zlé".

Koeficient 64,1428 ukazuje, aké bude Y, ak sa všetky premenné v uvažovanom modeli rovnajú 0. To znamená, že hodnotu analyzovaného parametra ovplyvňujú aj iné faktory, ktoré nie sú v modeli popísané.

Koeficient -0,16285 ukazuje váhu premennej X na Y. To znamená, že priemerná mesačná mzda v rámci tohto modelu ovplyvňuje počet odchádzajúcich s váhou -0,16285 (to je malý stupeň vplyvu). Znamienko „-“ označuje negatívny vplyv: čím vyšší plat, tým menej výpovede. Čo je spravodlivé.



Korelačná analýza v Exceli

Korelačná analýza pomáha určiť, či existuje vzťah medzi ukazovateľmi v jednej alebo dvoch vzorkách. Napríklad medzi prevádzkovou dobou stroja a nákladmi na opravy, cenou zariadenia a dobou prevádzky, výškou a hmotnosťou detí atď.

Ak existuje vzťah, potom či zvýšenie jedného parametra vedie k zvýšeniu (pozitívna korelácia) alebo zníženiu (negatívnemu) druhého. Korelačná analýza pomáha analytikovi určiť, či hodnota jedného ukazovateľa môže predpovedať možnú hodnotu iného ukazovateľa.

Korelačný koeficient sa označuje r. Pohybuje sa od +1 do -1. Klasifikácia korelácií pre rôznych oblastiach bude iná. Keď je hodnota koeficientu 0, medzi vzorkami neexistuje lineárny vzťah.

Pozrime sa, ako používať Excel nástroje nájsť korelačný koeficient.

Funkcia CORREL sa používa na nájdenie párových koeficientov.

Úloha: Zistite, či existuje vzťah medzi pracovným časom sústruh a náklady na jeho údržbu.

Umiestnite kurzor do ľubovoľnej bunky a stlačte tlačidlo fx.

1. V kategórii "Štatistické" vyberte funkciu CORREL.
2. Argument "Pole 1" - prvý rozsah hodnôt - čas stroja: A2: A14.
3. Argument "Pole 2" - druhý rozsah hodnôt - náklady na opravy: B2:B14. Kliknite na tlačidlo OK.

Ak chcete určiť typ pripojenia, musíte sa pozrieť na absolútne číslo koeficientu (každá oblasť činnosti má svoju vlastnú stupnicu).

Pre korelačná analýza viacerých parametrov (viac ako 2), je vhodnejšie použiť „Analýza dát“ (doplnok „Analytický balík“). V zozname musíte vybrať koreláciu a určiť pole. Všetky.

Výsledné koeficienty sa zobrazia v korelačnej matici. Ako tento:

Korelačno-regresná analýza

V praxi sa tieto dve techniky často používajú spoločne.

Príklad:

Teraz sú viditeľné údaje z regresnej analýzy.

Metóda lineárnej regresie nám umožňuje opísať priamku, ktorá najlepšie vyhovuje sérii usporiadaných párov (x, y). Rovnica pre priamku, známa ako lineárna rovnica, je uvedený nižšie:

ŷ je očakávaná hodnota y pre danú hodnotu x,

x je nezávislá premenná,

a - segment na osi y pre priamku,

b je sklon priamky.

Na obrázku nižšie je tento koncept znázornený graficky:

Vyššie uvedený obrázok ukazuje čiaru opísanú rovnicou ŷ =2+0,5x. Úsečka na osi y je bod, kde priamka pretína os y; v našom prípade a = 2. Sklon čiary, b, pomer stúpania čiary k dĺžke čiary, má hodnotu 0,5. Pozitívny sklon znamená, že čiara stúpa zľava doprava. Ak b = 0, čiara je vodorovná, čo znamená, že medzi závislými a nezávislými premennými neexistuje žiadny vzťah. Inými slovami, zmena hodnoty x neovplyvní hodnotu y.

ŷ a y sa často zamieňajú. Graf zobrazuje 6 usporiadaných dvojíc bodov a priamku podľa danej rovnice

Tento obrázok znázorňuje bod zodpovedajúci usporiadanej dvojici x = 2 a y = 4. Všimnite si, že očakávaná hodnota y podľa čiary na X= 2 je ŷ. Môžeme to potvrdiť nasledujúcou rovnicou:

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

Hodnota y je skutočný bod a hodnota ŷ je očakávaná hodnota y pomocou lineárnej rovnice pre danú hodnotu x.

Ďalším krokom je určenie lineárnej rovnice, ktorá maximálne zodpovedá množine usporiadaných dvojíc, o tom sme hovorili v predchádzajúcom článku, kde sme tvar rovnice určili pomocou .

Použitie Excelu na definovanie lineárnej regresie

Ak chcete použiť nástroj regresnej analýzy zabudovaný do Excelu, musíte aktivovať doplnok Analytický balík. Nájdete ho kliknutím na záložku Súbor –> Možnosti(2007+), v zobrazenom dialógovom okne možnostiexcel prejdite na kartu Doplnky. V teréne Kontrola vybrať doplnkyexcel a kliknite Choď. V zobrazenom okne začiarknite políčko vedľa analytický balík, kliknite OK.

V záložke Údaje v skupine Analýza objaví sa nové tlačidlo Analýza dát.

Na ukážku, ako doplnok funguje, použijeme údaje , kde chlap a dievča zdieľajú stôl v kúpeľni. Zadajte údaje pre náš príklad kúpeľne do stĺpcov A a B prázdneho listu.

Prejdite na kartu údaje, v skupine Analýza kliknite Analýza dát. V okne, ktoré sa zobrazí Analýza dát vyberte Regresia ako je znázornené na obrázku a kliknite na tlačidlo OK.

V okne nastavte požadované parametre regresie Regresia, ako je znázornené na obrázku:

Kliknite OK. Na nasledujúcom obrázku sú uvedené získané výsledky:

Tieto výsledky sú v súlade s tými, ktoré sme získali nezávislými výpočtami v .