Užitočné tipy

Regresia analýzy údajov v podrobnom podrobnom popise

Pin
Send
Share
Send
Send


Regresná analýza je jednou z najpopulárnejších metód štatistického výskumu. S jeho pomocou je možné stanoviť stupeň vplyvu nezávislých veličín na závislú premennú. Funkcia Microsoft Excel obsahuje nástroje určené na vykonávanie tohto typu analýzy. Pozrime sa, čo sú a ako ich používať.

Balík na analýzu pripojenia

Aby ste však mohli používať funkciu, ktorá vám umožní vykonať regresnú analýzu, musíte najprv aktivovať analytický balík. Až potom sa na páske programu Excel objavia potrebné nástroje pre tento postup.

    Prejdite na kartu "Súbor".

Otvorí sa okno Možnosti programu Excel. Prejdite do podsekcie "Body".

V dolnej časti okna, ktoré sa otvára, prepnite spínač do bloku "Management" na svojom mieste Doplnky programu Excelak je v inej pozícii. Kliknite na tlačidlo "Jump".

Teraz, keď ideme na kartu "Data", na páse s nástrojmi "Analýza" uvidíme nové tlačidlo - „Analýza údajov“.

Druhy regresnej analýzy

Existuje niekoľko typov regresií:

  • parabolický,
  • sila,
  • logaritmickej,
  • exponenciálny,
  • Je príznačné,
  • hyperbolické,
  • lineárna regresia.

O vykonaní posledného typu regresnej analýzy budeme hovoriť v Exceli.

Excel lineárna regresia

Nižšie je ako príklad uvedená tabuľka, ktorá ukazuje priemernú dennú teplotu vzduchu na ulici a počet nakupujúcich za príslušný pracovný deň. Dozvieme sa pomocou regresnej analýzy, ako presne môžu poveternostné podmienky vo forme teploty vzduchu ovplyvniť prítomnosť obchodnej organizácie.

Všeobecná lineárna regresná rovnica je nasledovná: Y = a0 + a1x1 + ... + akhk. V tomto vzorci Y znamená premennú, vplyv faktorov, na ktoré sa snažíme študovať. V našom prípade ide o počet kupujúcich. hodnota x Sú rôzne faktory, ktoré ovplyvňujú premennú. parametre sú regresné koeficienty. To znamená, že práve oni určujú význam konkrétneho faktora. index k označuje celkový počet týchto rovnakých faktorov.

    Kliknite na tlačidlo „Analýza údajov“, Je umiestnená na záložke. "Home" v paneli nástrojov "Analýza".

Otvorí sa malé okno. V ňom vyberte položku "Regresia", Kliknite na tlačidlo «OK».

Otvorí sa okno nastavení regresie. Povinné polia v ňom sú „Vstupný interval Y“ a „Vstupný interval X“, Všetky ostatné nastavenia môžu zostať predvolene.

V teréne „Vstupný interval Y“ uveďte adresu rozsahu buniek, v ktorých sa nachádzajú variabilné údaje, vplyv faktorov, na ktoré sa snažíme nadviazať. V našom prípade to budú bunky v stĺpci „Počet zákazníkov“. Adresu je možné zadať ručne z klávesnice alebo jednoducho vybrať požadovaný stĺpec. Druhá možnosť je omnoho jednoduchšia a pohodlnejšia.

V teréne „Vstupný interval X“ zadáme adresu rozsahu buniek, kde sa nachádzajú údaje faktora, ktorého vplyv na premennú, ktorú chceme nastaviť. Ako je uvedené vyššie, musíme stanoviť vplyv teploty na počet nakupujúcich, a preto do stĺpca „Teplota“ zadajte adresu buniek. Môže sa to uskutočniť rovnakým spôsobom ako v poli „Počet zákazníkov“.

Pomocou ďalších nastavení môžete nastaviť štítky, úroveň spoľahlivosti, konštantnú nulu, zobraziť graf normálnej pravdepodobnosti a vykonať ďalšie akcie. Vo väčšine prípadov však nie je potrebné tieto nastavenia meniť. Jedinou vecou, ​​ktorej by ste mali venovať pozornosť, sú výstupné parametre. V predvolenom nastavení sú výsledky analýzy zobrazené na inom hárku, ale presunutím prepínača môžete nastaviť výstup v určenom rozsahu na rovnakom hárku ako tabuľka so zdrojovými údajmi alebo v samostatnej knihe, tj v novom súbore.

Po nastavení všetkých nastavení kliknite na tlačidlo «OK».

Analytická analýza

Výsledky regresnej analýzy sa zobrazia vo forme tabuľky na mieste určenom v nastaveniach.

Jedným z hlavných ukazovateľov je R-kvadrát, Označuje kvalitu modelu. V našom prípade je tento koeficient 0,705 alebo približne 70,5%. Je to prijateľná úroveň kvality. Závislosť menšia ako 0,5 je zlá.

Ďalší dôležitý indikátor je umiestnený v bunke na priesečníku linky. «Y-križovatka" a stĺpec "Kurz", Označuje, akú hodnotu bude mať Y, a v našom prípade je to počet zákazníkov, pričom všetky ostatné faktory sa rovnajú nule. V tejto tabuľke je táto hodnota 58,04.

Hodnota na priesečníku grafu "Premenná X1" a "Kurz" ukazuje úroveň závislosti Y na X. V našom prípade je to úroveň závislosti počtu zákazníkov v obchode na teplote. Koeficient 1,31 sa považuje za pomerne vysoký ukazovateľ vplyvu.

Ako vidíte, pomocou programu Microsoft Excel je pomerne jednoduché zostaviť tabuľku regresnej analýzy. Ale iba vyškolený človek môže pracovať s údajmi prijatými na výstupe a pochopiť ich podstatu.

Ďakujem autorovi, zdieľajte článok o sociálnych sieťach.

Druhy regresie

Samotný tento koncept bol zavedený do matematiky v roku 1886. K regresii dochádza:

  • lineárne,
  • parabolický,
  • energie,
  • exponenciálny
  • hyperbolické,
  • exponenciálny,
  • logaritmické.

Zoberme si úlohu stanovenia závislosti počtu odchádzajúcich členov tímu od priemernej mzdy v 6 priemyselných podnikoch.

Úloha. Šesť podnikov analyzovalo priemerný mesačný plat a počet zamestnancov, ktorí odišli sami. V tabuľkovej forme máme:

Pre úlohu stanovenia závislosti počtu pracovníkov, ktorí opúšťajú priemernú mzdu v 6 podnikoch, má regresný model formu rovnice Y = a 0 + a 1 x 1 + ... + a k x k, kde x i sú ovplyvňujúce premenné, a i sú regresné koeficienty a a k je počet faktorov.

Pri tejto úlohe je Y ukazovateľom zamestnancov na dôchodku a rozhodujúcim faktorom je mzda, ktorá je označená X.

Používanie funkcií procesora Excel

Pred regresnou analýzou programu Excel by malo predchádzať použitie zabudovaný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:

  • prejdite do časti „Parametre“ na karte „Súbor“,
  • v okne, ktoré sa otvorí, vyberte riadok „Doplnky“,
  • kliknite na tlačidlo „Go“ umiestnené v dolnej časti, vpravo od riadku „Management“,
  • začiarknite políčko vedľa názvu „Analytický balík“ a potvrďte akciu kliknutím na „OK“.

Ak sa všetko urobí správne, na pravej strane karty „Údaje“, ktorá sa nachádza nad pracovným listom „Excel“, sa zobrazí pravé tlačidlo.

Teraz, keď máte k dispozícii všetky potrebné virtuálne nástroje na vykonávanie ekonometrických výpočtov, môžeme začať riešiť náš problém. Postupujte takto:

  • 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 ukončených zamestnancov) a pre X (ich platy),
  • potvrďte svoje akcie kliknutím na tlačidlo „OK“.

Výsledkom je, že program automaticky vyplní nový list tabuľkového procesora údajmi o regresnej analýze. Venujte pozornosť! V programe Excel môžete nezávisle nastaviť miesto, ktoré na tento účel uprednostňujete. Napríklad to môže byť ten istý hárok, kde sú umiestnené hodnoty Y a X, alebo dokonca nová kniha špeciálne navrhnutá na ukladanie takýchto údajov.

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

V Exceli sú údaje získané počas spracovania údajov podľa tohto príkladu nasledujúce:

Najskôr by ste mali venovať pozornosť hodnote štvorca R. Predstavuje koeficient určenia. V tomto príklade R-kvadrát = 0,755 (75,5%), t.j. vypočítané parametre modelu vysvetľujú vzťah medzi uvažovanými parametrami o 75,5%. Čím vyššia je hodnota určovacieho koeficientu, zvolený model sa považuje za vhodnejší pre konkrétnu úlohu. Predpokladá sa, že správne popisuje skutočnú situáciu s hodnotou R-kvadrát nad 0,8. Ak je R-kvadrát tcr, hypotéza nevýznamnosti voľného termínu lineárnej rovnice sa odmietne.

V skúmanom probléme sa vo voľnom období, s použitím nástrojov Excelu, zistilo, že t = 169,20903 a p = 2,89E-12, to znamená, že máme nulovú pravdepodobnosť, že pravdivá hypotéza o bezvýznamnosti voľného termínu bude zamietnutá. Pre koeficient s neznámym t = 5,779405 a p = 0,001158. Inými slovami, pravdepodobnosť, že bude zamietnutá správna hypotéza o bezvýznamnosti koeficientu s neznámym, je 0,12%.

Možno teda tvrdiť, že získaná lineárna regresná rovnica je primeraná.

Úloha primeranosti nákupu bloku akcií

Viacnásobná regresia v programe Excel sa vykonáva pomocou rovnakého nástroja na analýzu údajov. Zvážte konkrétnu aplikáciu.

Vedenie NNN musí rozhodnúť o realizovateľnosti nákupu 20% podielu v MMM. Náklady na balík (SP) sú 70 miliónov dolárov. Špecialisti NNN zhromažďovali údaje o podobných transakciách. Bolo rozhodnuté vyhodnotiť hodnotu bloku akcií podľa týchto parametrov, vyjadrených v miliónoch amerických dolárov, ako:

  • splatné účty (VK),
  • ročný obrat (VO),
  • pohľadávky (VD),
  • hodnota fixných aktív (SOF).

Parameter podnikový mzdový dlh (V3 P) sa okrem toho používa v tisícoch amerických dolárov.

Riešenie pomocou tabuľkového procesora Excel

Najprv musíte vytvoriť tabuľku zdrojových údajov. Má nasledovnú formu:

  • zavolajte do okna „Analýza údajov“,
  • zvoľte si sekciu „Regresia“,
  • v okne „Vstupný interval Y“ zadajte rozsah hodnôt závislých premenných zo stĺpca G,
  • Kliknite na ikonu červenej šípky napravo od okna „Vstupný interval X“ a vyberte rozsah všetkých hodnôt zo stĺpcov B, C, D, F na hárku.

Označte položku „Nový pracovný hárok“ a kliknite na „OK“.

Získajte regresnú analýzu pre túto úlohu.

Získanie výsledkov a záverov

„Zhromažďujeme“ zo zaokrúhlených údajov uvedených vyššie v hárku procesora tabuľky Excel, regresná rovnica:

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

V známejšej matematickej podobe môže byť napísaná ako:

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

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

Nahradením do regresnej rovnice dostanú sumu 64,72 milióna dolárov. To znamená, že akcie MMM nestojí za to kupovať, pretože ich hodnota 70 miliónov dolárov je dosť predražená.

Ako vidíte, použitie procesora Excel a regresnej rovnice umožnilo urobiť informované rozhodnutie týkajúce sa uskutočniteľnosti veľmi špecifickej transakcie.

Teraz viete, čo je to regresia. Príklady v Exceli diskutované vyššie vám pomôžu pri riešení praktických problémov z oblasti ekonometrie.

Regresná analýza v programe Microsoft Excel je najkomplexnejšia príručka na používanie programu MS Excel na riešenie problémov regresnej analýzy v oblasti podnikovej analýzy. Konrad Karlberg ľahko vysvetľuje teoretické otázky, ktorých znalosť vám pomôže vyhnúť sa mnohým chybám pri vykonávaní regresnej analýzy sami, ako aj pri hodnotení výsledkov analýzy vykonanej inými ľuďmi. Celý materiál, od jednoduchých korelácií a t-testov po viacnásobnú kovariančnú analýzu, je založený na skutočných príkladoch a je sprevádzaný podrobným opisom zodpovedajúcich postupných krokov.

Kniha rozoberá funkcie a rozpory spojené s funkciami programu Excel pre prácu s regresiou, rozoberá dôsledky použitia každej z ich možností a argumentov a vysvetľuje, ako spoľahlivo aplikovať regresné metódy v rôznych oblastiach, od lekárskeho výskumu po finančnú analýzu.

Conrad Karlberg. Regresná analýza v programe Microsoft Excel. - M.: Dialectics, 2017. - 400 s.

Stiahnite si poznámku vo formáte alebo príklady vo formáte

Kapitola 1. Posúdenie variability údajov

Štatistici majú k dispozícii mnoho ukazovateľov variability (variability). Jednou z nich je súčet druhých odchýlok jednotlivých hodnôt od priemeru. V Exceli sa na to používa funkcia QUADROTKL (). Ale častejšie sa používa disperzia. Disperzia je stredná hodnota štvorcových odchýlok. Rozptyl nie je citlivý na počet hodnôt v študovanom súbore (zatiaľ čo súčet druhých odchýlok sa zvyšuje s počtom meraní).

Program Excel ponúka dve funkcie, ktoré vracajú odchýlky: DISP.G () a DISP.V ():

  • Ak hodnoty, ktoré sa majú spracovať z populácie, použite funkciu DISP.G (). To znamená, že hodnoty obsiahnuté v rozsahu sú jediné hodnoty, ktoré vás zaujímajú.
  • Funkciu DISP.B () použite, ak hodnoty, ktoré sa majú spracovať, tvoria vzorku z väčšej zbierky. Predpokladá sa, že existujú ďalšie hodnoty, ktorých rozptyl môžete tiež vyhodnotiť.

Ak sa množstvo, ako je priemer alebo korelačný koeficient, počíta na základe populácie, nazýva sa to parameter. Podobná hodnota vypočítaná na základe vzorky sa nazýva štatistika. Počítanie odchýlok z priemeru v tejto množine získate súčet druhých odchýlok menšej hodnoty, ako keby ste ich spočítali od akejkoľvek inej hodnoty. Podobné vyhlásenie platí aj pre rozptýlenie.

Čím väčšia je veľkosť vzorky, tým presnejšia je vypočítaná štatistická hodnota. Neexistuje však jediná vzorka s objemom menším ako je veľkosť bežnej populácie, s ohľadom na ktorú si môžete byť istí, že hodnota štatistiky sa zhoduje s hodnotou parametra.

Predpokladajme, že máte sadu 100 rastových hodnôt, ktorých priemer sa líši od priemeru v bežnej populácii bez ohľadu na to, aký malý je tento rozdiel. Po vypočítaní rozptylu pre vzorku získate nejakú hodnotu, povedzme 4. Táto hodnota je menšia ako akákoľvek iná, ktorú možno získať výpočtom odchýlky každej zo 100 rastových hodnôt v porovnaní s akoukoľvek inou hodnotou, ako je priemer pre vzorku, vrátane skutočného priemeru nad všeobecná populácia. Vypočítaná odchýlka sa preto bude líšiť av menšej miere od odchýlky, ktorú by ste získali, ak by ste nejako vedeli a použili nie priemernú vzorku, ale parameter populácie.

Priemerný súčet štvorcov stanovený pre vzorku poskytuje nižší odhad rozptylu populácie. Rozptyl vypočítaný týmto spôsobom sa nazýva presunula Vyhodnotenie. Ukazuje sa, že na elimináciu zaujatosti a získanie nezaujatého odhadu stačí rozdeliť súčet druhých odchýlok nie n kde n je veľkosť vzorky a ďalej n - 1 .

hodnota n - 1 nazýva sa číslo (počet) stupňov voľnosti. Existujú rôzne spôsoby výpočtu tejto hodnoty, hoci všetky z nich zahŕňajú buď odpočítanie určitého čísla od veľkosti vzorky, alebo spočítanie počtu kategórií, do ktorých spadajú pozorovania.

Podstata rozdielu medzi funkciami DISP.G () a DISP.V () je nasledovná:

  • Vo funkcii DISP.G () je súčet druhých mocnín vydelený počtom pozorovaní, a preto predstavuje skreslený odhad rozptylu, skutočný priemer.
  • Vo funkcii DISP.V () je súčet štvorcov vydelený počtom pozorovaní mínus 1, t.j. počtom stupňov voľnosti, čo poskytuje presnejší a nezaujatý odhad rozptylu populácie, z ktorej bola táto vzorka extrahovaná.

Štandardná odchýlka smerodajná odchýlka , SD) - je druhá odmocnina rozptylu:

Štvorica odchýlok na štvorec prevedie mierku na inú metriku, ktorá je štvorcom originálu: metre na meter štvorcový, doláre na doláre, atď. Štandardná odchýlka je druhá odmocnina rozptylu, a preto nás vracia k pôvodným jednotkám merania. Čo je pohodlnejšie.

Často musíte počítať štandardnú odchýlku potom, čo boli údaje podrobené určitej manipulácii. A hoci v týchto prípadoch sú výsledky nepochybne štandardnými odchýlkami, zvyčajne sa nazývajú štandardné chyby , Existuje niekoľko druhov štandardných chýb, vrátane štandardnej chyby merania, štandardnej chyby proporcie, štandardnej chyby priemeru.

Predpokladajme, že ste zhromaždili údaje o raste pre 25 náhodne vybraných dospelých mužov v každom z 50 štátov. Ďalej vypočítate priemernú výšku dospelých mužov v každom štáte. Získaných 50 priemerných hodnôt je zase možné považovať za pozorovania. Na základe toho by ste mohli vypočítať ich štandardnú odchýlku, ktorá je štandardná chyba priemeru , Obr. 1. vám umožňuje porovnať rozdelenie 1250 počiatočných individuálnych hodnôt (údaje o raste 25 mužov v každom z 50 štátov) s rozdelením priemerných hodnôt 50 štátov. Vzorec na odhad štandardnej chyby priemeru (t. J. Smerodajnej odchýlky stredných hodnôt, nie individuálnych pozorovaní):

kde je štandardná chyba priemeru, s - smerodajná odchýlka počiatočných pozorovaní, n - počet pozorovaní vo vzorke.

Obr. 1. Odchýlka priemerov medzi jednotlivými štátmi je výrazne menšia ako odchýlka vo výsledkoch jednotlivých pozorovaní.

В статистике существует соглашение относительно использования греческих и латинских букв для обозначения статистических величин. Греческими буквами принято обозначать параметры генеральной совокупности, латинскими - выборочные статистики. Preto, ak hovoríme o štandardnej odchýlke populácie, píšeme ju ako σ, ale ak vezmeme do úvahy štandardnú odchýlku vzorky, použijeme notáciu s. Pokiaľ ide o symboly na určovanie priemerov, nie sú tak dobre koordinované. Priemerný počet obyvateľov sa označuje gréckym písmenom μ. Symbol X̅ sa však tradične používa na vyjadrenie priemeru vzorky.

Z-skóre vyjadruje polohu pozorovania pri distribúcii v jednotkách štandardnej odchýlky. Napríklad z = 1,5 znamená, že pozorovanie je 1,5 štandardných odchýlok od priemeru k veľkým hodnotám. termín Z-skóre používa sa na individuálne hodnotenia, t. pre merania priradené jednotlivým vzorkovým položkám. V prípade takýchto štatistík (napr. Štátny priemer) je tento výraz hodnota z :

kde X̅ je priemerná hodnota vzorky, μ je priemerná hodnota populácie, je štandardná chyba priemeru súboru vzoriek:

kde σ je štandardná chyba populácie (jednotlivé merania), n - veľkosť vzorky.

Predpokladajme, že ste inštruktorom golfového klubu. Dokázali ste merať rozsah vplyvov už dlho a viete, že jeho priemerná hodnota je 205 yardov a štandardná odchýlka je 36 yardov. Bol vám ponúknutý nový klub s tvrdením, že zvýši váš dosah o 10 metrov. Žiadate každého z ďalších 81 návštevníkov klubu, aby vykonal testovací zásah s novým klubom a zaznamenal jeho rozsah. Ukázalo sa, že priemerný rozsah zásahov do nového klubu je 215 metrov. Aká je pravdepodobnosť, že rozdiel 10 metrov (215 - 205) je spôsobený výlučne chybou výberu? Alebo iným spôsobom: Aká je pravdepodobnosť, že vo väčšom rozsahu nový klub nebude vykazovať zvýšenie dosahu v porovnaní s existujúcim dlhodobým priemerom 205 yardov?

Môžeme to overiť vygenerovaním hodnoty z. Štandardná chyba priemeru:

Musíme zistiť pravdepodobnosť, že priemer vo vzorke bude 2,5 σ od priemeru vo všeobecnej populácii. Ak je pravdepodobnosť malá, rozdiely nie sú spôsobené náhodou, ale kvalitou nového klubu. V Exceli neexistuje žiadna pripravená funkcia na určenie pravdepodobnosti z-hodnoty. Môžete však použiť vzorec = 1-NORM.ST. RASP (hodnota z, TRUE), kde funkcia NORM.ST. RASP () vráti oblasť pod normálnou krivkou doľava od hodnoty z.

Obr. 2. Funkcia NORM.ST. RASP () vráti oblasť pod krivkou doľava od hodnoty z, aby sa zväčšil obrázok, kliknite naň pravým tlačidlom myši a vyberte Otvoriť obrázok na novej karte

Druhý argument funkcie NORM.ST. RASP () môže mať dve hodnoty: TRUE - funkcia vráti oblasť pod krivkou doľava od bodu určeného prvým argumentom, FALSE - funkcia vráti výšku krivky v bode určenom prvým argumentom.

Ak nie je známy priemer (μ) a smerodajná odchýlka (σ), použije sa hodnota t (pozri podrobnosti). Štruktúry hodnôt z- a t sa líšia v tom, že na zistenie hodnoty t sa použije štandardná odchýlka získaná z výsledkov vzorky, a nie známa hodnota parametra všeobecnej populácie σ. Normálna krivka má jediný tvar a distribučná forma t-hodnôt sa mení v závislosti od počtu stupňov voľnosti df (od Eng. stupne voľnosti ) vzorky, ktorú predstavuje. Počet stupňov voľnosti odberu vzoriek je rovný n - 1 kde n - veľkosť vzorky.

Obr. 3. Forma t-rozdelenia vznikajúca v tých prípadoch, keď parameter σ nie je známy, sa líši od formy normálneho rozdelenia

Excel má dve funkcie pre distribúciu t, tiež nazývanú Študentova distribúcia: STUDENT.RACP () vracia oblasť pod krivkou doľava od zadanej hodnoty t a STUDENT.RACP.PX () - vpravo.

Kapitola 2. Korelácia

Korelácia je miera vzťahu medzi prvkami v množine usporiadaných párov. Je charakterizovaná korelácia Pearsonove korelačné koeficienty - r. Koeficient môže mať hodnoty v rozsahu od –1,0 do +1,0.

kde S x a S y - smerodajné odchýlky premenných X a Y , S xy - kovariancia:

V tomto vzorci je kovariancia rozdelená na štandardné odchýlky premenných X a Y , čím sa odstránia efekty mierky spojené s jednotkami z kovariancie. Excel používa funkciu CORREL (). V názve tejto funkcie nie sú žiadne kvalifikačné prvky Г a В, ktoré sa používajú v názvoch takých funkcií ako STANDOTLON (), DISP () alebo COVARIATION (). Aj keď korelačný koeficient pre vzorku poskytuje skreslený odhad, dôvod odchýlky je iný ako v prípade rozptylu alebo štandardnej odchýlky.

V závislosti od rozsahu všeobecného korelačného koeficientu (často označovaného gréckym listom ρ ), korelačný koeficient r poskytuje skreslený odhad a účinok ovplyvnenia sa zvyšuje so zmenšujúcou sa veľkosťou vzorky. Napriek tomu sa nesnažíme napraviť túto zaujatosť rovnakým spôsobom, ako sme to napríklad urobili pri výpočte štandardnej odchýlky, keď sme v zodpovedajúcom vzorci nahradili počet pozorovaní, ale počet stupňov slobody. V skutočnosti počet pozorovaní použitých na výpočet kovariancie nemá žiadny vplyv na veľkosť.

Štandardný korelačný koeficient je určený na použitie s premennými spojenými lineárnym vzťahom. Prítomnosť nelinearity a / alebo chýb v údajoch (odľahlé hodnoty) vedie k nesprávnemu výpočtu korelačného koeficientu. Na diagnostikovanie problémov s údajmi sa odporúča zostaviť rozptylové grafy. Toto je jediný typ grafu v programe Excel, v ktorom sa horizontálna aj vertikálna os považujú za hodnotové osi. Lineárny graf, jeden zo stĺpcov, určuje, ako os kategórie, ktorá skresľuje dátový obrázok.

Obr. 4. Regresné čiary sa zdajú rovnaké, ale porovnajte ich rovnice

Pozorovania použité na zostavenie lineárneho diagramu sú umiestnené rovnomerne pozdĺž horizontálnej osi. Nápisy divízií pozdĺž tejto osi sú iba nápisy a nie číselné hodnoty.

Aj keď korelácia často znamená kauzálny vzťah, nemôže slúžiť ako dôkaz, že je. Štatistika sa nepoužíva na preukázanie toho, či je teória pravdivá alebo nepravdivá. Aby sa vylúčili konkurenčné vysvetlenia výsledkov pozorovania, naplánované experimenty , Štatistika sa používa na zhrnutie informácií získaných počas takýchto experimentov a na kvantifikáciu pravdepodobnosti, že prijaté rozhodnutie môže byť vzhľadom na dôkaznú základňu nesprávne.

Kapitola 3. Jednoduchá regresia

Ak sú dve premenné vzájomne prepojené, takže hodnota korelačného koeficientu prekračuje napríklad 0,5, potom je možné v tomto prípade predpovedať (s určitou presnosťou) neznámu hodnotu jednej premennej zo známej hodnoty druhej. Na získanie predpokladaných cenových hodnôt na základe údajov uvedených na obr. 5, môžete použiť niektorý z niekoľkých možných spôsobov, ale takmer určite nebudete používať spôsob znázornený na obr. 5. Napriek tomu by ste sa s tým mali oboznámiť, pretože žiadna iná metóda vám neumožňuje jasne preukázať vzťah medzi koreláciou a predpovedaním, ako je tento. Na obr. 5 v rozmedzí B2: C12 je prezentovaná náhodná vzorka desiatich domov a sú uvedené údaje o ploche každého domu (v štvorcových stopách) a jeho predajnej cene.

Obr. 5. Predpovedané hodnoty predajnej ceny tvoria priamku.

Nájdite priemer, smerodajnú odchýlku a korelačný koeficient (rozsah A14: C18). Vypočítajte odhady zón (E2: E12). Napríklad bunka ЕЗ obsahuje vzorec: = (3– $ 14 $) / $ 15 $. Vypočítajte z-odhady predpokladanej ceny (F2: F12). Napríklad bunka F3 obsahuje vzorec: = ЕЗ * $ 18 USD. Prevod z-skóre na cenu v dolároch (H2: H12). V bunke NC je vzorec: = F3 * $ C $ 15 + $ C $ 14.

Poznámka: hodnota prognózy má vždy tendenciu posúvať sa smerom k priemeru 0. Čím bližšie je korelačný koeficient na nulu, tým bližšie je predpovedané z-skóre na nulu. V našom príklade je korelačný koeficient medzi oblasťou a predajnou cenou 0,67 a predpokladaná cena je 1,0 x 0,67, t. 0.67. To zodpovedá prekročeniu hodnoty nad priemernú hodnotu, ktorá sa rovná dvom tretinám smerodajnej odchýlky. Ak by korelačný koeficient bol 0,5, predpokladaná cena by bola 1,0 x 0,5, t.j. 0.5. To zodpovedá prekročeniu hodnoty nad priemernú hodnotu, ktorá sa rovná iba polovici smerodajnej odchýlky. Kedykoľvek sa hodnota korelačného koeficientu líši od ideálu, t. väčší ako -1,0 a menší ako 1,0, odhad predpovedanej premennej by mal byť bližší k jej priemernej hodnote ako odhad predpovedanej (nezávislej) premennej k svojej vlastnej. Tento jav sa nazýva stredná regresia alebo jednoducho regresia.

Excel má niekoľko funkcií na určovanie koeficientov rovnice regresnej priamky (v Exceli sa nazýva trendová čiara) y =kx+b , Ak chcete zistiť k slúži ako funkcia

tu na Je predpovedaná premenná a x Je nezávislá premenná. Musíte prísne dodržiavať toto poradie premenných. Sklon regresnej priamky, korelačný koeficient, štandardné odchýlky premenných a kovariancia spolu úzko súvisia. Funkcia CUT () vracia hodnotu odrezanú regresnou čiarou na zvislej osi:

Obr. 6. Vzťah medzi štandardnými odchýlkami prevádza kovarianciu na korelačný koeficient a sklon regresnej priamky.

Upozorňujeme, že počet hodnôt xay poskytnutých funkcii TILT () a CUT () ako argumenty musí byť rovnaký.

V regresnej analýze sa používa ďalší dôležitý ukazovateľ - R2 (R-kvadrát) alebo koeficient určenia. Určuje príspevok k celkovej variabilite údajov uskutočňovanej vzťahom medzi regresiou x a na , V programe Excel je k dispozícii funkcia KVPIRSON (), ktorá má rovnaké argumenty ako funkcia CORREL ().

Uvádza sa, že dve premenné s nenulovým korelačným koeficientom medzi nimi vysvetľujú rozptyl alebo majú vysvetlený rozptyl. Vysvetlená odchýlka sa zvyčajne vyjadruje v percentách. tak R2 = 0,81 znamená, že je vysvetlených 81% rozptylu (rozptylu) týchto dvoch premenných. Zvyšných 19% je spôsobených náhodnými výkyvmi.

Excel má funkciu TREND, ktorá zjednodušuje výpočty. Funkcia TREND ():

  • berie známe hodnoty, ktoré poskytujete x a známe hodnoty na ,
  • vypočíta sklon regresnej priamky a konštantu (segment),
  • vráti predpokladané hodnoty na stanovené pomocou regresnej rovnice na známe hodnoty x .

Funkcia TREND () je funkcia poľa (ak ste sa predtým s takými funkciami nestretli, odporúčam to).

Obr. 7. Použitie funkcie TREND () vám umožňuje zrýchliť a zjednodušiť výpočty v porovnaní s použitím párov funkcií TILT () a CUT ().

Ak chcete do buniek G3: G12 zadať funkciu TREND () ako maticový vzorec, vyberte rozsah G3: G12, zadajte vzorec TREND (СЗ: С12, ВЗ: В12), stlačte a podržte tlačidlá a až potom stlačte kláves. Všimnite si, že vzorec je uzavretý v zátvorkách: <a>. Excel vám teda povie, že tento vzorec je vnímaný presne ako maticový vzorec. Nezadávajte zátvorky samotné: ak sa ich pokúsite zadať sami ako súčasť vzorca, Excel interpretuje vaše zadanie ako bežný textový reťazec.

Funkcia TREND () obsahuje ďalšie dva argumenty: novye_znacheniya_h a const , Prvý umožňuje zostaviť predpoveď pre budúcnosť a druhý môže spôsobiť, že regresná čiara prechádza pôvodom (hodnota TRUE hovorí Excelu, aby použil vypočítanú konštantu, hodnotu FALSE - konštanta = 0). Excel vám umožňuje nakresliť regresnú čiaru z grafu tak, aby prešla cez pôvod. Najskôr vytvorte bodový graf a potom kliknite pravým tlačidlom myši na jednu zo značiek v sérii údajov. V kontextovej ponuke, ktorá sa otvorí, vyberte položku Pridajte čiaru trendu vyberte možnosť lineárne , ak je to potrebné, prejdite nadol na panel a začiarknite políčko Nastaviť priesečník , skontrolujte, či je priradené textové pole nastavené na 0,0.

Ak máte tri premenné a chcete určiť koreláciu medzi týmito dvoma, s vylúčením vplyvu tretej, môžete použiť súkromná korelácia , Predpokladajme, že vás zaujíma vzťah medzi percentom obyvateľov mesta, ktorí ukončili vysokú školu, a počtom kníh v mestských knižniciach. Zhromaždili ste údaje o 50 mestách, ale ... Problém je, že oba tieto parametre môžu závisieť od blahobytu obyvateľov mesta. Je samozrejme veľmi ťažké vyzdvihnúť ďalších 50 miest, ktoré sa vyznačujú presne rovnakou úrovňou blahobytu obyvateľov.

Použitím štatistických metód na vylúčenie vplyvu faktoru blahobytu na finančnú podporu knižníc a dostupnosť vysokoškolského vzdelávania by ste mohli získať presnejšie kvantitatívne hodnotenie stupňa závislosti medzi záujmovými premennými, konkrétne: počet kníh a počet absolventov. Takáto podmienená korelácia medzi dvoma premennými, keď sú hodnoty ostatných premenných pevné, sa nazýva čiastočná korelácia. Jedným zo spôsobov, ako ju vypočítať, je použiť rovnicu:

kde rCB.W - korelačný koeficient medzi premennými College (College) a Books (Books) s vylúčeným vplyvom (fixná hodnota) premennej Welfare (Wealth), rCB - korelačný koeficient medzi premennými College a Books, rCW - korelačný koeficient medzi premennými College a Wealth, rBW - korelačný koeficient medzi premennými knihy a sociálnej starostlivosti.

Na druhej strane, čiastočná korelácia sa môže vypočítať na základe analýzy zvyškov, t.j. rozdiely medzi predpokladanými hodnotami a výsledkami skutočných pozorovaní, ktoré sú s nimi spojené (obe metódy sú uvedené na obrázku 8).

Obr. 8. Súkromná korelácia ako korelácia rezíduí

Na zjednodušenie výpočtu matice korelačných koeficientov (B16: E19) použite analytický balík Excel (ponuka dáta –> analýza –> Analýza údajov ). V predvolenom nastavení tento balík nie je aktívny v programe Excel. Ak ju chcete nainštalovať, prejdite cez ponuku súbor –> parametre –> Add-ons , V dolnej časti okna, ktoré sa otvorí parametrevynikať nájsť pole management vybrať Add-onsvynikať cvaknutie Choďte na , Začiarknite políčko vedľa doplnku. Analytický balík , Kliknite na tlačidlo Adolovanie dát vyberte možnosť korelácia , Ako vstupný interval zadajte $ B $ 2: $ D $ 13, začiarknite políčko Štítky v prvom riadku , zadajte ako výstupný interval $ B $ 16: $ E $ 19.

Ďalšou možnosťou je určiť semipatrikálnu koreláciu. Napríklad skúmate účinky výšky a veku na hmotnosť. Máte teda dve predikčné premenné - výšku a vek a jednu predpokladanú premennú - hmotnosť. Chcete vylúčiť vplyv jednej premennej predikcie na inú, ale nie na premennú prognózy:

kde H je výška, W je hmotnosť, A je vek a index zátvoriek používa zátvorky na označenie účinku ktorej premennej je odstránená a ktorá premenná. V tomto prípade označenie W (N.A.) naznačuje, že vplyv premennej Age sa odstráni z premennej Height, ale nie z premennej Weight.

Môže sa zdať, že diskutovaná otázka nie je významná. Nakoniec, najdôležitejšia vec je, ako presne funguje všeobecná regresná rovnica, zatiaľ čo problém relatívnych príspevkov jednotlivých premenných k vysvetlenej celkovej rozptylu je druhoradý. To však zďaleka nie je. Hneď ako začnete premýšľať o tom, či sa v mnohonásobnej regresnej rovnici použije nejaká premenná, problém sa stáva dôležitým. Môže to ovplyvniť posúdenie správnosti výberu modelu pre analýzu.

Kapitola 4. Funkcia LINE ()

Funkcia LINE () vracia 10 regresných štatistík. Funkcia LINE () je funkcia poľa. Ak ho chcete zadať, vyberte rozsah obsahujúci päť riadkov a dva stĺpce, napíšte vzorec a stlačte:

Obr. 9. Funkcia LINE (): a) vyberte rozsah D2: E6, b) zadajte vzorec zobrazený na paneli vzorcov, c) stlačte

Funkcia LINE () vracia:

  • koeficient regresie (alebo sklon, bunka D2),
  • segment (alebo konštanta, bunka E3),
  • štandardné chyby regresného koeficientu a konštanty (rozsah D3: E3),
  • určovací koeficient R2 pre regresiu (bunka D4),
  • štandardná chyba posúdenia (bunka E4),
  • F-test na úplnú regresiu (bunka D5),
  • počet stupňov voľnosti pre zvyškový súčet štvorcov (bunka E5),
  • regresný súčet štvorcov (bunka D6),
  • zvyškový súčet štvorcov (bunka E6).

Zvážte každú z týchto štatistík a ich vzájomné pôsobenie.

Štandardná chyba v našom prípade je to štandardná odchýlka vypočítaná pre chyby vzorkovania. To znamená, že ide o situáciu, keď má obyvateľstvo jednu štatistiku a vzorka inú. Vydelením regresného koeficientu štandardnou chybou získate hodnotu 2,092 / 0,818 = 2,559. Inými slovami, regresný koeficient 2,092 je dve a pol štandardnej chyby od nuly.

Ak je regresný koeficient nulový, potom najlepší

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 na počte podnikov, veľkosti miezd a ďalších parametroch. Или: как влияют иностранные инвестиции, цены на энергоресурсы и др. на уровень ВВП.

Результат анализа позволяет выделять приоритеты. A na základe hlavných faktorov, predpovedať, plánovať rozvoj prioritných oblastí, robiť rozhodnutia manažmentu.

  • lineárne (y = a + bx),
  • parabolický (y = a + bx + cx 2),
  • exponenciálny (y = a * exp (bx)),
  • mocenské právo (y = a * x ^ b),
  • hyperbolický (y = b / x + a),
  • logaritmický (y = b * 1n (x) + a),
  • exponenciálne (y = a * b ^ x).

Pozrime sa na príklad vytvorenia regresného modelu v Exceli a interpretáciu výsledkov. Vezmite lineárny typ regresie.

Úloha. V 6 podnikoch sa analyzovala priemerná mesačná mzda a počet odstúpených zamestnancov. Je potrebné určiť závislosť počtu zamestnancov, ktorí opúšťajú priemernú mzdu.

Lineárny regresný model je nasledujúci:

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

V našom príklade je Y ukazovateľom pracovníkov na dôchodku. Ovplyvňujúcim faktorom sú mzdy (x).

Excel má vstavané funkcie, ktoré sa dajú použiť na výpočet parametrov lineárneho regresného modelu. Ale rýchlejšie to vytvorí doplnok „Analytický balík“.

Aktivujeme výkonný analytický nástroj:

  1. Stlačte tlačidlo „Office“ a prejdite na kartu „Možnosti programu Excel“. "Add-ons".
  2. V dolnej časti pod rozbaľovacím zoznamom v poli „Správa“ bude nápis „Doplnky programu Excel“ (ak nie je, kliknite na začiarkavacie políčko vpravo a vyberte). A tlačidlo Ísť na. Kliknite na tlačidlo.
  3. Otvorí sa zoznam dostupných doplnkov. Vyberte položku „Analysis Package“ a kliknite na tlačidlo OK.

Po aktivácii bude doplnok k dispozícii na karte „Údaje“.

Teraz sa budeme zaoberať priamo regresnou analýzou.

  1. Otvoríme ponuku nástroja „Analýza údajov“. Vyberte položku „Regresia“.
  2. Otvorí sa ponuka na výber vstupných hodnôt a výstupných parametrov (kde sa má zobraziť výsledok). V poliach pre zdrojové údaje uveďte rozsah opísaného parametra (Y) a faktor (X), ktorý ho ovplyvňuje. Zvyšok môžete nechať prázdny.
  3. Po kliknutí na OK program zobrazí výpočty na novom liste (môžete vybrať interval, ktorý sa má zobraziť na aktuálnom liste, alebo priradiť výstup novej knihe).

Najprv venujte pozornosť R-námestiu a koeficientom.

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

Koeficient 64.1428 ukazuje, aké bude Y, ak všetky premenné v uvažovanom modeli sú 0. To znamená, že hodnotu analyzovaného parametra ovplyvňujú aj ďalšie faktory, ktoré nie sú v modeli opísané.

Koeficient -0,16285 ukazuje váhu premennej X podľa Y. To znamená, že priemerný mesačný plat v rámci tohto modelu ovplyvňuje počet tých, ktorí odchádzajú s hmotnosťou -0,16285 (toto je malý stupeň vplyvu). Znak „-“ znamená negatívny dopad: čím vyšší je plat, tým menej ľudí opúšťa. Čo je fér.

Analýza korelácie Excelu

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

Ak existuje spojenie, znamená zvýšenie jedného parametra zvýšenie (pozitívna korelácia) alebo zníženie (negatívne) druhého. Korelačná analýza pomáha analytikovi určiť, či je možné odhadnúť možnú hodnotu iného z hodnoty jedného ukazovateľa.

Korelačný koeficient je označený r. Líši sa od +1 do -1. Klasifikácia korelačných vzťahov pre rôzne oblasti sa bude líšiť. Ak je hodnota koeficientu 0, medzi vzorkami neexistuje lineárny vzťah.

Pozrime sa, ako pomocou nástrojov programu Excel nájsť korelačný koeficient.

Na nájdenie párových koeficientov sa používa funkcia CORREL.

Účel: Zistiť, či existuje vzťah medzi prevádzkovým časom sústruhu a nákladmi na jeho údržbu.

Kurzor umiestnime do akejkoľvek bunky a stlačíme tlačidlo fx.

  1. V kategórii „Štatistické“ vyberte funkciu CORREL.
  2. Argument „Array 1“ - prvý rozsah hodnôt je prevádzková doba stroja: A2: A14.
  3. Argument Array 2 je druhý rozsah hodnôt - náklady na opravu: B2: B14. Kliknite na tlačidlo OK.

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

Na korelačnú analýzu niekoľkých parametrov (viac ako 2) je vhodnejšie použiť „analýzu údajov“ (doplnok „analytický balík“). V zozname musíte vybrať koreláciu a označiť pole. To je všetko.

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

Korelačná regresná analýza

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

  1. Vytvoríme korelačné pole: „Vložiť“ - „Graf“ - „Bodový graf“ (umožňuje porovnávať páry). Rozsah hodnôt sú všetky číselné údaje tabuľky.
  2. Kliknite ľavým tlačidlom myši na ktorýkoľvek bod v diagrame. Potom správne. V ponuke, ktorá sa otvorí, vyberte možnosť Pridať riadok trendu.
  3. Priradiť parametre k riadku. Typ - „lineárny“. V dolnej časti - „Zobraziť rovnicu v diagrame.“
  4. Kliknite na tlačidlo Zavrieť.

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

Po výsledkoch júlového zasadnutia centrálna banka znížila svoju kľúčovú sadzbu o 0,25%

Adresa pre otázky a návrhy na webe: [email protected]

Copyright © 2008–2019. LLC „Spoločnosť BCS“. Moskva, Prospect Mira, d. 69, s. 1
Všetky práva vyhradené. Akékoľvek použitie materiálov stránok bez povolenia je zakázané.
Sprostredkovateľská licencia č. 154-04434-100000, vydaná Federálnou komisiou pre cenné papiere Ruskej federácie 10. januára 2001

Dáta sú informácie o výmene, ktorých vlastníkom (vlastníkom) je burza cenných papierov v Moskve PJSC. Distribúcia, vysielanie alebo iné poskytovanie informácií o výmene tretím stranám je možné iba spôsobom a za podmienok ustanovených v postupe na používanie informácií o výmene poskytovaných spoločnosťou Moskva Exchange OJSC. BrokerCreditService Company LLC, licencia č. 154-04434-100000 z 10. januára 2001 pre sprostredkovateľské činnosti. Vydané FSFM. Žiadny dátum vypršania platnosti.

Materiály uvedené v tejto časti nie sú individuálne investičné odporúčania. Finančné nástroje alebo operácie uvedené v tejto časti nemusia byť pre vás vhodné, nemusia zodpovedať vášmu investičnému profilu, finančnej situácii, investičným skúsenostiam, znalostiam, investičným cieľom, prístupu k riziku a ziskovosti. Určenie súladu finančného nástroja alebo transakcie s investičnými cieľmi, investičným horizontom a toleranciou rizika je úlohou investora. LLC „Spoločnosť BCS“ nezodpovedá za prípadné straty investora v prípade transakcií alebo investícií do finančných nástrojov uvedených v tejto časti. Informácie nemôžu byť považované za verejnú ponuku, ponuku alebo výzvu na kúpu alebo predaj akýchkoľvek cenných papierov alebo iných finančných nástrojov na uskutočnenie transakcií s nimi. Informácie nemožno v budúcnosti považovať za záruky alebo prísľuby návratnosti investícií, úrovne rizika, výšky nákladov, rentability investícií. Výsledok investovania v minulosti neurčuje budúci príjem. Nejde o reklamu cenných papierov. Pred uskutočnením investičného rozhodnutia musí investor nezávisle vyhodnotiť ekonomické riziká a výhody, daňové, právne, účtovné dôsledky transakcie, ich ochotu a schopnosť tieto riziká akceptovať. Klient znáša aj náklady na platenie sprostredkovateľských a depozitárskych služieb, zadávanie objednávok telefonicky a ďalšie výdavky, ktoré klient hradí. Úplný zoznam taríf spoločnosti BKS Company LLC je uvedený v dodatku č. 11 k nariadeniu o poskytovaní služieb na trhu s cennými papiermi spoločnosti BKS Company LLC. Pred vykonaním transakcií sa musíte oboznámiť aj s: oznámením rizík spojených s operáciami na trhu cenných papierov, informáciami o rizikách klienta spojených s transakciami s neúplným krytím, výskytom nekrytých pozícií, dočasne nekrytých pozícií, vyhlásením o rizikách spojených s vykonávanie operácií na trhu futures, forwardových zmlúv a opcií, vyhlásenie o rizikách spojených s nadobudnutím zahraničných cenných papierov.

Poskytnuté informácie a názory sú založené na verejných zdrojoch, ktoré sa považujú za spoľahlivé, ale spoločnosť BKS Company LLC nezodpovedá za správnosť poskytnutých informácií. Poskytované informácie a stanoviská tvoria rôzni odborníci, vrátane nezávislých, a stanoviská k rovnakej situácii sa môžu medzi odborníkmi BCS radikálne líšiť. Vzhľadom na vyššie uvedené by sa človek nemal spoliehať výlučne na materiál predložený na úkor nezávislej analýzy. Spoločnosť BKS Company LLC a jej pridružené spoločnosti a zamestnanci nezodpovedajú za použitie týchto informácií, za priame alebo nepriame škody spôsobené použitím týchto informácií, ani za ich presnosť.

Pin
Send
Share
Send
Send