Ako vykonať regresnú analýzu v programe Excel

Obsah:

Anonim

Regresia je analytický nástroj, ktorý používame na analýzu veľkého množstva údajov a vytváranie predpovedí a predpovedí v programe Microsoft Excel.

Chcete predpovedať budúcnosť? Nie, nebudeme sa učiť astrológiu. Sme v číslach a dnes sa v Exceli naučíme regresnú analýzu.

Aby sme predpovedali budúce odhady, budeme študovať:

  • REGISTRAČNÁ ANALÝZA S VYUŽÍVANÍM FUNKCIÍ EXCEL (RUČNÉ ZISTENIE REGRESIE)
  • REGRESNÁ ANALÝZA S POUŽITÍM NÁSTROJA EXCEL'S ANALYSIS TOOLPAK
  • REGRESNÁ SCHÉMA V EXCELE

Poďme na to…

Scenár:

Predpokladajme, že predávate nealkoholické nápoje. Aké skvelé to bude, ak dokážete predpovedať:

  • Koľko nealkoholických nápojov sa predá v budúcom roku na základe údajov za predchádzajúci rok?
  • Na ktoré polia je potrebné sa zamerať?
  • A ako môžete zvýšiť svoje tržby zmenou stratégie?

Bude to ziskovo úžasné. Nie?… Ja viem. Začnime teda.

Máte 11 záznamov o predaných predajcoch a nealkoholických nápojoch.

Teraz na základe týchto údajov chcete predpovedať počet predajcov potrebných na dosiahnutie 2 000 predajov nealkoholických nápojov.

Regresná rovnica je nástrojom na uskutočnenie takýchto blízkych odhadov. Aby sme to urobili, musíme najskôr poznať regresiu.

REGRESNÁ ANALÝZA VYUŽÍVAJÚCA FUNKCIE EXCEL (RUČNÉ NÁLEZOVÉ REGISTRÁCIE)

Táto časť vám umožní lepšie porozumieť regresii, než len hovoriť o regresnom postupe v programe Excel.

Úvod:

Jednoduchá lineárna regresia:

Štúdium vzťahu medzi dvoma premennými sa nazýva jednoduchá lineárna regresia. Kde jedna premenná závisí od druhej nezávislej premennej. Závislá premenná sa často nazýva názvami ako Riadená, Odozva a Cieľová premenná. A nezávislá premenná sa často vyslovuje ako jazdná, prediktorová alebo jednoducho nezávislá premenná. Tieto názvy ich jasne vystihujú.

Teraz to porovnajme s vašim scenárom. Chcete vedieť, koľko predajcov je potrebných na dosiahnutie 2000 tržieb. Tu teda závislou premennou je počet predajcov a nezávislou premennou sú predané nealkoholické nápoje.

Nezávislá premenná je väčšinou označovaná ako X a závislá premenná ako r.

V našom prípade sa predávajú nealkoholické nápoje X a počet predajcov je r.

Ak chceme vedieť, koľko nealkoholických nápojov sa predá, ak ich určíme 200 predavačov, potom bude scenár naopak.

Pohybujúce sa na.

„Jednoduchá“ matematika rovnice lineárnej regresie:

No nie je to jednoduché. Excel to však zjednodušil.

Potrebujeme predpovedať požadovaný počet predajcov pre všetkých 11 prípadov, aby sme získali 12. najbližšiu predpoveď.

Povedzme:

Predáva sa nealkoholický nápoj X

Číslo predajcov je r

Predpovedané r (počet predavačov) tiež volal Regresná rovnica, bolo by

x*Sklon+zachytenie (relax, mám to zakryté)

Teraz vás určite zaujíma, kde štat dostanete svah a zachytíte. Nebojte sa, Excel má pre nich funkcie. Nemusíte sa učiť, ako nájsť svah a zachytiť ho ručne.

Ak chcete, pripravím k tomu samostatný návod. Dajte mi vedieť v sekcii komentárov. Toto sú niektoré dôležité nástroje na analýzu údajov.

Teraz prejdeme k nášmu výpočtu:

Krok 1: Pripravte si tento malý stôl

Krok 2: Nájdite sklon regresnej čiary

Excel Funkcia pre svahy je

= SLOPE (známe_roky, známe_xky)

Vaše známe_vás sú v dosahu B2: B12 a známe_x sú v dosahu C2: C12

V bunke B16, napíšte vzorec nižšie

= SLOPE (B2: B12, C2: C12)

(Poznámka: Sklon sa v regresnej rovnici nazýva aj koeficient x)

Dostaneš 0.058409. Zaokrúhlite až na 2 desatinné miesta a získate 0.06.

Krok 3: Nájdite zachycovaciu čiaru regresie

Funkcia Excelu pre zachytenie je

=INTERCEPT (známe_roky, známe_xky)

Vieme, čo naše známe x a y

V bunke B17, zapíšte si tento vzorec

= INTERCEPT (B2: B12, C2: C12)

Dostanete hodnotu -1,118969. Zaokrúhlite na 2 desatinné číslice. Dostaneš -1.11.

Naša rovnica lineárnej regresie je = x*0,06 + (-1,11). Teraz môžeme ľahko predpovedať možné y v závislosti od cieľa x.

Krok 4: Do D2 napíšte nasledujúci vzorec

=C2*$ B $ 16+$ B $ 17(Regresná rovnica)

Získate hodnotu 13.55.

Vyberte D2 až D13 a stlačte CTRL+D na vyplnenie vzorca v rozsahu D2: D13

V bunke D13 máte požadovaný počet predajcov.

Preto na dosiahnutie cieľa 2000 Predaj nealkoholických nápojov potrebujete odhadom 115,71 predajcov alebo povedzme 116, pretože je nezákonné rozdeľovať ľudí na kúsky.

Teraz pomocou toho môžete ľahko vykonávať analýzu What-If v programe Excel. Stačí zmeniť počet predajov a ukáže vám, koľko predajcov bude potrebovať na dosiahnutie tohto cieľa predaja.

Hrajte sa a zistíte:

Koľko pracovnej sily potrebujete na zvýšenie tržieb?

Koľko tržieb sa zvýši, ak zvýšite svojich predajcov?

Urobte svoj odhad spoľahlivejším:

Teraz viete, že na dokončenie 2 000 predajov potrebujete 116 predajcov.

V analytike sa nič nehovorí a neverí. Pri svojom odhade musíte uviesť percento spoľahlivosti. Je to ako dať osvedčenie o svojej rovnici.

Vzorec korelačného koeficientu:

Ďalšia vec, ktorú sa vás opýtame, je, do akej miery tieto dve premenné súvisia. Zo statického hľadiska musíte povedať koeficient korelácie.

Excel funkcia pre koreláciu je

= CORREL (pole1, pole2)

Vo vašom prípade sú známe_x a Know_y bez ohľadu na to pole1 a pole2.

V B18 zadajte tento vzorec

= CORREL ((B2: B12, C2: C12)

Budete mať 0.919090. Bunku B2 naformátujte na percento. Teraz máte 92% korelácie.

Teraz, čo toto 92% prostriedky. To znamená, tam 92% šanca na predaj sa zvýši, ak zvýšite počet predajcov a 92% tržieb sa zníži, ak znížite počet predajcov. To sa nazýva Kladný korelačný koeficient.

R Squire (R^2):

Hodnota R Squire vám napovie, v akých percentách nie je vaša regresná rovnica náhoda. Nakoľko je presný podľa poskytnutých údajov.

Funkcia Excel pre R squire je RSQ.

RSQ (známe_roky, známe_x)

V našom prípade dostaneme hodnotu R squire v bunke B19.

V B19 zadajte tento vzorec

= RSQ (B2: B12, C2: C12)

Máme teda 84% hodnoty r Square. Čo je veľmi dobré vysvetlenie našej regresie. Hovorí sa, že 84% našich údajov nie je náhodných. Y (počet predajcov) veľmi závisí od X (predaj nealkoholických nápojov).

Existuje mnoho ďalších testov, ktoré môžeme urobiť s týmito údajmi, aby sme zaistili regresiu. Ale ručne to bude zložitý a zdĺhavý postup. Preto Excel poskytuje Analysis Toolpak. Pomocou tohto nástroja dokážeme vykonať túto regresnú analýzu v priebehu niekoľkých sekúnd.

REGRESIA V EXCELE POUŽÍVANÍM NÁSTROJA EXCEL'S ANALYSIS TOOLPAK

Ak už viete, čo sú regresné rovnice, a chcete len rýchle výsledky, potom je táto časť pre vás. Ak však chcete ľahko porozumieť regresným rovniciam, prejdite na položku ANRÝZA REGRESIE S POUŽITÍM EXCELOVÝCH FUNKCIÍ (RUČNÉ ZISTENIE REGRESIE).

Excel poskytuje v nástroji Analysis Toolpak celú škálu nástrojov na analýzu. Štandardne nie je k dispozícii na karte Údaje. Musíte to pridať. Poďme to teda najskôr pridať.

Pridanie balíka analytických nástrojov do programu Excel 2016

Ak neviete, kde je analýza údajov v programe Excel, postupujte podľa týchto krokov

Krok 1: Prejdite na Možnosti programu Excel: Súbor? Možnosti? Doplnky

Krok 2: Kliknite na Doplnky. Zobrazí sa zoznam dostupných doplnkov.

Vyberte položku Analysis ToolPak a v spodnej časti okna vyhľadajte správu. V správe vyberte Doplnky programu Excel a kliknite na položku GO.

Otvorí sa okno doplnkov. Tu vyberte položku Analysis ToolPak. Potom kliknite na tlačidlo ok.

Teraz máte prístup k všetkým funkciám nástroja DataPak na analýzu údajov z karty Údaje.

Použitie Analysis ToolPak na regresiu

Krok 1: Prejdite na kartu Údaje a vyhľadajte položku Analýza údajov. Potom naň kliknite.

Zobrazí sa dialógové okno.

Krok 2: V zozname Analysis Tools nájdite „Regresia“ a kliknite na tlačidlo OK.

Regresia vyskočí vstupné okno. Uvidíte niekoľko dostupných možností vstupu. Ale zatiaľ sa budeme sústrediť len na rozsahy Y a X, pričom ostatné ponecháme ako predvolené.

Krok 4: Poskytnite vstupy:

Počet predavačov je Y

Predaj nealkoholických nápojov je X

Preto

  • Rozsah Y = B2: B11

A

  • Rozsah X = C2: C11

Pre rozsah výstupu som vybral E4 na rovnakom hárku. Môžete vybrať nový pracovný hárok, aby ste získali výsledky na novom pracovnom hárku v tom istom zošite alebo v úplnom novom zošite. Keď ste so zadaním hotoví, kliknite na tlačidlo OK.

Výsledky:

Z vašich údajov vám budú poskytnuté rôzne informácie. Nenechajte sa ohromiť. Nemusíte konzumovať všetky jedlá.

Budeme sa zaoberať iba tými výsledkami, ktoré nám pomôžu odhadnúť požadovaný počet predajcov

Krok 5: Poznáme regresnú rovnicu na odhad y, to je

x*Sklon+zachytenie

Potrebujeme len nájsť Sklon a Zachytiť vo výsledkoch.

A sú tu.

Zachytávací koeficient je jasne uvedený.

Svah je napísaný ako „X premenná 1‘, Niekedy tiež uvádzané ako koeficient X. Zaokrúhlite ich a dostaneme -1,11 ako Intercept a 0,06 ako sklon.

Krok 6: Z výsledkov môžeme odvodiť regresnú rovnicu. A to by bolo

= x*(0,06) + (-1,11)

Pripravte si túto tabuľku v Exceli.

Na Teraz, X je 2000, ktorý je v bunke E2.

Do bunky F2 zadajte tento vzorec

= E2*F21+F20

Dostanete výsledok 115.7052757.

Zaokrúhľovanie nám to dá 116 povinných predajcov.

Naučili sme sa teda vytvárať regresnú rovnicu ručne a pomocou Analysis ToolPak. Ako môžete použiť túto rovnicu na odhad budúcich štatistík?

Teraz porozumieme regresnému výstupu poskytovanému nástrojom Analysis Toolpak.

Pochopenie regresného výstupu:

Neexistuje žiadny prínos, ak robíte regresnú analýzu pomocou balíka analytických nástrojov v programe Excel a nemôžete interpretovať jej význam.

Súhrnná časť:

Ako naznačuje názov, je to súhrn údajov.

    1. Multiple R: Udáva, ako je regresná rovnica vhodná pre údaje. Hovorí sa mu tiež korelačný koeficient.

V našom prípade je 0.919090619 alebo 0.92 (zaokrúhlenie). To znamená, že ak zvýšime počet našich predajcov, existuje 92% pravdepodobnosť zvýšenia tržieb.

    1. Štvorec R: Hovorí o spoľahlivosti nájdenej regresie. Hovorí nám, koľko pozorovaní je súčasťou našej regresnej línie. V našom prípade je to 0,844727566 alebo 0,85. To znamená, že naša regresia je vhodná na 85%.
    2. Upravený štvorec R: Theadjusted square je len osvedčenejšou verziou R square. Je obzvlášť užitočný pri viacnásobnej regresnej analýze.
    3. Štandardná chyba: Zatiaľ čo R. Squire vám hovorí, koľko dátových bodov sa nachádza v blízkosti regresnej čiary, štandardná chyba vám hovorí, ako ďaleko môže dátový bod ísť od regresnej čiary.

V našom prípade je 6.74.

  1. Pozorovanie: Toto je jednoducho počet pozorovaní, ktoré je v našom prípade 11.

Sekcia Anova:

Táto časť sa takmer nepoužíva v lineárnej regresii.

  1. df. Je to určitý stupeň slobody. Používa sa pri ručnom výpočte regresie.
  2. SS. Súčet štvorcov. Je to len súčet druhých mocnín odchýlok. Slúži na nájdenie hodnôt R squire.
  3. PANI. To znamená štvorcovú hodnotu.
  4. A 5. F a význam F. Ak je významnosť F (p-hodnota sklonu) menšia ako F-test, môžete nulovú hypotézu zahodiť a svoju hypotézu dokázať. V jednoduchom jazyku môžete dospieť k záveru, že pri zmene existuje určitý vplyv x na y.

V našom prípade F je 48,96264 a význam F je 0,000063. To znamená, že naša regresia vyhovuje údajom.

Regresná sekcia:

V tejto časti uvádzame dve najdôležitejšie hodnoty pre našu regresnú rovnicu.

  1. Intercept: Máme tu intercept, ktorý hovorí, kde x zachytáva x na Y. Toto je dôležitá časť regresnej rovnice. V našom prípade je -1,11.
  2. X premenná 1 (Sklon). Tiež sa nazýva koeficient x. Definuje tangens regresnej čiary.

REGRESNÁ SCHÉMA V EXCELE

V programe Excel je ľahké vykresliť regresný graf. Postupujte podľa týchto krokov. Ak chcete pridať graf regresie v programe Excel 2016, 2013 a 2010, postupujte podľa týchto jednoduchých krokov.

Krok 1. V prvom stĺpci uveďte svoje známe x a v druhom stĺpci y.

V našom prípade vieme, že Known_ x’s sú nealkoholické nápoje predávané. A known_y's sú predajcovia.

Krok 2. Vyberte svoje známe rozsahy x a y.

Krok 3: Prejdite na kartu Vložiť a kliknite na bodový graf.

Budete mať graf, ktorý vyzerá takto.

Krok 4. Pridajte trendovú čiaru: Prejdite na rozloženie a vyhľadajte možnosť trendovej čiary v sekcii analýzy.

Pod možnosťou Trendová čiara kliknite na Lineárna trendová čiara.

Váš graf bude vyzerať takto.

Toto je váš regresný graf.

Ak teraz pridáte údaje nižšie a zvolené údaje rozšírite. Vo svojom grafe uvidíte zmenu.

V našom prípade sme napríklad k predanému nealkoholickému nápoju pridali 2 000 a predavačov sme nechali prázdne. A keď rozšírime rozsah grafu, bude to tak.

Poskytne požadovaný počet predajcov na 2000 predajov nealkoholických nápojov v grafickej forme. Čo je v grafe mierne pod 120. A z našej regresnej rovnice vieme, že je 116.

V tomto článku som sa pokúsil pokryť všetko v rámci regresnej analýzy programu Excel. Vysvetlil som regresiu v programe Excel 2016. Regresia v programe Excel 2010 a Excel 2013 je rovnaká ako v programe Excel 2016.

Ak máte ďalšie otázky týkajúce sa tejto témy, použite sekciu komentárov. Položte otázku, vyjadrite svoj názor alebo len spomeňte svoje gramatické chyby. Všetko je vítané. Neváhajte použiť sekciu komentárov.

Ako vypočítať funkciu REŽIM v programe Excel

Ako vypočítať priemernú funkciu v programe Excel

Ako vytvoriť graf štandardnej odchýlky

Popisná štatistika v programe Microsoft Excel 2016

Ako používať funkciu Excel NORMDIST

Ako používať Paretovu tabuľku a analýzu

Populárne články:

50 Skratka pre Excel, ktorá zvýši vašu produktivitu

Ako používať funkciu VLOOKUP v programe Excel

Ako používať funkciu COUNTIF v programe Excel 2016

Ako používať funkciu SUMIF v programe Excel